获取数据源
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";
}