C# 导出Excel方法

获取数据源

public Response<string> ExportExcel(ExportExcelDetailRequest request)
		{
			
			using (var db = new NDatabase())
			{
				DataTable data = db.Fetch(sql);//数据查询
                string date = DateTime.Now.ToString("yyyyMMdd");
                string Name = "FileName" + DateTime.Now.ToString("yyyyMMddHHmmss");//文件名
                string url = System.Web.Hosting.HostingEnvironment.MapPath("~/") + "Files\\Uploads\\"+date+"\\";//保存路径

                string filepath = @"" + url + "" + Name + ".xlsx";
                string path = "Files\\Uploads\\" + date + "\\" + Name + ".xlsx";
                //将datatable添加到dataset
                DataSet ds = new DataSet();
                ds.Tables.Add(data);
                //datatable生成excel文件
                Export(ds, filepath);
                return InvokeResult.Success<string>(path);
            }
			
		}

导出

public static void Export(DataSet ds, string excelPath, int maxRows = 1048576, string sheetName = "sheet")
    {
        //判断传入的路径是否存在，如果不存在，新建文件夹
        string dicrctoryName = Path.GetDirectoryName(excelPath);
        if (!string.IsNullOrEmpty(dicrctoryName))
        {
            Directory.CreateDirectory(dicrctoryName);
        }
				//删除相同文件名的文件
				if (File.Exists(excelPath))
				{
				File.Delete(excelPath);
				}


	  using (ExcelPackage package = new ExcelPackage(new FileInfo(excelPath)))
        {
            int sheetIndex = 1;
            for (int tableIndex = 0; tableIndex < ds.Tables.Count; tableIndex++)
            {
                DataTable dt = ds.Tables[tableIndex];
                DataColumnCollection dataColumns = dt.Columns;
                int totalCount = (dt.Rows.Count - 1) / maxRows + 1;
                for (int count = 0; count < totalCount; count++)
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sheetName + sheetIndex);
                         //worksheet.Cells[1, 1].Value = "";//设置表头
                        //worksheet.Cells[1, 1, 1, 5].Merge = true;//合并单元格,从第一行第一列到第一行第五列
                        //worksheet.Cells[1, 1, 1, 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//设置样式剧中
                    SetTitle(dataColumns, worksheet, type);//动态设置表头
                    int skipCount = count * maxRows;
                    for (int i = 0; i < maxRows; i++)
                    {
                        int dataIndex = skipCount + i;
                        if (dataIndex >= dt.Rows.Count)
                        {
                            break;
                        }
                        DataRow dr = dt.Rows[dataIndex];
                        //设置数据
                        for (int j = 0; j < dataColumns.Count; j++)
                        {
                            if (dr[j] != DBNull.Value)
                            {
                                //worksheet.Column(8).Style.Numberformat.Format = "yyyy-mm-dd hh24:mi:ss";
                                //worksheet.Column(10).Style.Numberformat.Format = "yyyy-mm-dd hh24:mi:ss";
                                worksheet.Cells[i + 2, j + 1].Value = dr[j];
                            }
                        }
                    }
                    sheetIndex++;
                }
            }
            package.Save();

        }
    }

设置标题

private static void SetTitle(DataColumnCollection dataColumns, ExcelWorksheet worksheet, string type)
        {
            	int rowIndex = 1;
                worksheet.Cells[rowIndex, 1].Value = "标题1";
                worksheet.Cells[rowIndex, 2].Value = "标题2";
                worksheet.Cells[rowIndex, 3].Value = "标题3";
                worksheet.Cells[rowIndex, 4].Value = "标题4";
                worksheet.Cells[rowIndex, 5].Value = "标题5";
                worksheet.Cells[rowIndex, 6].Value = "标题6";
        }