using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using System;
using System.Data;
using System.IO;
using System.Web.Mvc;

      public FileResult ExportDailyReport(string startDate)
      {
            if (string.IsNullOrEmpty(startDate))
            {
                return null;
            }
            DailyReportBLL bll = new DailyReportBLL();
            DataTable dt = bll.GetDailyReport(startDate);//取出数据，DataTable类型
            var file = DataTableExportExcel(dt, "日报表",startDate);//导出Excel文件
            return file;//下载此Excel文件
      }

        /// <summary>
        /// DataTable导出Excel
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="excelName">excel文件名</param>
        /// <param name="startDate">sheet页名称(默认：Sheet1)</param>
        /// <param name="isMergedRegion">合并第一列相同行(默认：合并)</param>
        /// <returns></returns>
        public FileResult DataTableExportExcel(DataTable dt, string excelName = "", string sheetName = "Sheet1", bool isMergedRegion = true)
        {
            //创建Excel文件的对象
            HSSFWorkbook book = new HSSFWorkbook();
            //表头-样式
            ICellStyle headStyle = HeadStyle(book);
            //单元格-样式
            ICellStyle bodyStyle = BodyStyle(book);
            //添加一个sheet1
            ISheet sheet1 = book.CreateSheet(sheetName);//sheet的名称
            //创建sheet1的数据
            IRow row1 = sheet1.CreateRow(0);//第一行是表头
            //给sheet1添加第一行的头部标题
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                row1.CreateCell(i).SetCellValue(dt.Columns[i].ToString());//DataTable的表头             
                row1.Cells[i].CellStyle = headStyle;//添加，表头-样式          
                sheet1.SetColumnWidth(i, 15 * 256);//设置每列宽度, 256 的整数倍???
            }
            //将数据逐步写入sheet1各个行
            int rowIndex = 1;//当前行      
            foreach (DataRow row in dt.Rows)
            {
                IRow dataRow = sheet1.CreateRow(rowIndex);
                foreach (DataColumn column in dt.Columns)
                {
                    var headCell = row1.GetCell(column.Ordinal);//表头
                    if (headCell.ToString().Contains("率"))//标题包含“率”字
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString() + "%");//则数据添加%号
                    }
                    else
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    }
                    dataRow.Cells[column.Ordinal].CellStyle = bodyStyle;//添加样式
                    //根据第一列的内容合并第一列：
                    if (isMergedRegion == true)
                    {                        
                        MergedRegion(column.Ordinal, rowIndex, dataRow.GetCell(column.Ordinal).ToString(), dt.Rows.Count, sheet1);
                    }
                }
                rowIndex++;
            }
            //将IO数据流写入到Excel文件中
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            string filename = excelName + "_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
            return File(ms, "application/vnd.ms-excel", filename);
        }
        //表头样式
        public ICellStyle HeadStyle(HSSFWorkbook book)
        {
            ICellStyle headStyle = BasicStyle(book);
            //表头-字体加粗
            IFont font = book.CreateFont();
            font.IsBold = true;
            headStyle.SetFont(font);

            return headStyle;
        }
        //单元格样式
        public ICellStyle BodyStyle(HSSFWorkbook book)
        {
            ICellStyle bodyStyle = BasicStyle(book);
            return bodyStyle;
        }
        //基本样式
        public ICellStyle BasicStyle(HSSFWorkbook book)
        {
            ICellStyle basicStyle = book.CreateCellStyle();
            basicStyle.BorderBottom = BorderStyle.Thin;
            basicStyle.BorderLeft = BorderStyle.Thin;
            basicStyle.BorderRight = BorderStyle.Thin;
            basicStyle.BorderTop = BorderStyle.Thin;
            basicStyle.BottomBorderColor = HSSFColor.Black.Index;
            basicStyle.LeftBorderColor = HSSFColor.Black.Index;
            basicStyle.RightBorderColor = HSSFColor.Black.Index;
            basicStyle.TopBorderColor = HSSFColor.Black.Index;
            basicStyle.Alignment = HorizontalAlignment.Center;//水平居中
            basicStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中

            return basicStyle;
        }

        string cellTemp = string.Empty;
        int firstRow = 1;//定位行
        /// <summary>
        /// 合并第一列相同行
        /// </summary>
        /// <param name="columnIndex">列index</param>
        /// <param name="rowIndex">行index</param>
        /// <param name="cell">单元格内容</param>
        /// <param name="rowCount">总行数</param>
        /// <param name="sheet1"></param>
        public void MergedRegion(int columnIndex, int rowIndex, string cell, int rowCount, ISheet sheet1)
        {
            try
            {
                //根据第一列的内容合并第一列
                if (columnIndex == 0)//第1列
                {
                    if (rowIndex == 1)//第1行，给cellTemp赋值
                    {
                        cellTemp = cell;//第1行的数据
                    }
                    else//第2行开始，将cell值与给cellTemp做比较
                    {
                        if (cellTemp != cell)//发现不同的内容的
                        {
                            if ((rowIndex - firstRow) > 1)//合并区域A2必须包含2个或更多单元格
                            {
                                sheet1.AddMergedRegion(new CellRangeAddress(firstRow, (rowIndex - 1), 0, 0));//合并
                            }
                            cellTemp = cell;//cellTemp重新赋值
                            firstRow = rowIndex;//重新定位行
                        }
                        if (rowIndex == rowCount)//最后一行
                        {
                            if ((rowIndex - firstRow) > 1)//合并区域A2必须包含2个或更多单元格
                            {
                                sheet1.AddMergedRegion(new CellRangeAddress(firstRow, rowIndex, 0, 0));//合并
                            }
                        }
                    }
                }
            }
            catch { }
        }
    }

<input type="text" id="startDate" readonly="readonly" onclick="WdatePicker({dateFmt:'yyyy-MM-dd'})" />
<input type="button" id="ExportExcel" onclick="ExportExcel()" value="导出Excel" />

<script>
    function ExportExcel() {
        var date = $("#startDate").val();
        if (date == null || $.trim(date) == "") {     
            return;
        }
        window.location.href = "/BusinessReport/ExportDailyReport?startDate=" + date;
    }
</script>