三种方法导出EXCEL文件

方法一：

前端导出
   //列标题，逗号隔开，每一个逗号就是隔开一个单元格
                var str = `车牌,照片,设备,时间\n`;//导出字段
                //增加\t为了不让表格显示科学计数法或者其他格式,exportData是数据
                for (let i = 0; i < exportData.length; i++) {
                    for (let item in exportData[i]) {
                        str += `${exportData[i][item] + '\t'},`;
                    }
                    str += '\n';
                }
                //encodeURIComponent解决中文乱码
                let uri = 'data:text/csv;charset=utf-8,\ufeff' + encodeURIComponent(str);
                //通过创建a标签实现
                var link = document.createElement("a");
                link.href = uri;
                //对下载的文件命名
                link.download = "车流监测表.csv";
                document.body.appendChild(link);
                link.click();
                document.body.removeChild(link);

方法二：

先建立模板，写后台接口传值（C#）
public string Function(string path ,DataTable task_dt,Dictionary<string, string> dic){
            string filePathResult = "";
            string templetfilepath = path + @"\..\Files\PatrolAnylyse\模板\单项模板.xls";//模版Excel
            filePathResult = path + @"\..\Files\PatrolAnylyse\Temp\单项(" + fileName + ").xls";//中介Excel，以它为中介来导出，避免直接使用模块Excel而改变模块的格式
            File.Copy(templetfilepath, filePathResult, true);

            FileStream filestream = new FileStream(filePathResult, FileMode.Open, FileAccess.Read);
            HSSFWorkbook book = new HSSFWorkbook(filestream);
            HSSFSheet task_sheets = book.GetSheetAt(0) as HSSFSheet;
            //填充任务数据
            FillDtIntoSheet(task_sheets, task_dt, dic);
  using (FileStream fileSave = new FileStream(filePathResult, FileMode.Open, FileAccess.Write))
            {
                book.Write(fileSave);
            }
            filePathResult = filePathResult.Substring(filePathResult.LastIndexOf(@"\") + 1);
            return filePathResult;
}

public void FillDtIntoSheet(HSSFSheet sheet, DataTable dataTable, Dictionary<string, string> dic)
{
  if (dataTable == null || dataTable.Rows.Count <= 0)
            {
                return;
            }
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                sheet.CreateRow(i + 1);
                for (int col = 0; col < dataTable.Columns.Count; col++)
                {
                    sheet.GetRow(i + 1).CreateCell(col);
                }

                sheet.GetRow(i + 1).GetCell(0).SetCellValue(dic[dataTable.Rows[i]["PEOPLE"].ToString()]);
                sheet.GetRow(i + 1).GetCell(1).SetCellValue(dataTable.Rows[i]["TIME"].ToString());
                sheet.GetRow(i + 1).GetCell(2).SetCellValue(dataTable.Rows[i]["COUNT"].ToString());
            }
}

方法三：

DataTable dt = mDAL.ExportDataTable(person, fabu_ry, sDate, eDate, Convert.ToInt32(size), Convert.ToInt32(index));
            string sheetName = "任务管理";
            HSSFWorkbook book = ExcelManager.WriteExcel(dt, "", "", 0, 0, true, false, sheetName);
            HSSFPalette headerPalette = book.GetCustomPalette();
            //设置下边框的颜色为#a0d468 对应的rgb值如下 第一个参数的取值范围为8-64
            headerPalette.SetColorAtIndex((short)8, (byte)160, (byte)212, (byte)104);
            HSSFColor hssfColor = headerPalette.FindColor((byte)160, (byte)212, (byte)104);

            ICellStyle headerStyle = book.CreateCellStyle();
            headerStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
            headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick;
            headerStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            headerStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            headerStyle.BottomBorderColor = hssfColor.Indexed;
            HSSFFont headerFont = (HSSFFont)book.CreateFont();
            //设置表头为粗体显示
            headerFont.Boldweight = short.MaxValue;
            headerStyle.SetFont(headerFont);
            ISheet sheet = book.GetSheet(sheetName);
            sheet.CreateFreezePane(0, 1, 0, 1);//固定第一列
            IRow header = sheet.GetRow(0);
            header.Height = 22 * 20;
            for (int i = header.FirstCellNum; i < header.LastCellNum; i++)
            {
                header.GetCell(i).CellStyle = headerStyle;
                sheet.SetColumnWidth(i, 15 * 256);
            }
            if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + "Files/数据.xls"))
            {
                File.Delete(AppDomain.CurrentDomain.BaseDirectory + "Files/数据.xls");
            }
            using (System.IO.FileStream file = System.IO.File.Create(AppDomain.CurrentDomain.BaseDirectory + "Files/数据.xls"))
            {
                book.Write(file);
                file.Close();
            }
            return "Files/数据.xls";