首先引用两个NetGet包:
EPPLUS.Core(1.5.4)和SharpZipLib(1.1.0)
接下来写一个导出到Excel的方法:
public string Export(DataTable dt)
{
//先创建一个时间
string sTimeName = DateTime.Now.ToString("yyyyMMddHHmmssfff") ;
//新建一个工作簿
string sFileName = "Excel_" +sTimeName + ".xlsx";
//保存的文件名
string sPathName = "DownFiles/Data_" + sTimeName ;
//保存的图片
string iPathName = sPathName + "/img_" + sTimeName ;
//保存到Excel
if (!Directory.Exists(iPathName))
{
Directory.CreateDirectory(iPathName);
}
FileInfo file = new FileInfo(Path.Combine(sPathName, sFileName));
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
//因为是大数据量,所以做一个判断,大于六万就从新创建一个Sheetrow
int sheetrow = i % 60000;
if (sheetrow == 0)
{
// 添加worksheet
worksheet = package.Workbook.Worksheets.Add("sheet" + (i / 60000));
//添加头
worksheet.Cells[1, 1].Value = "标题";
worksheet.Cells[1, 2].Value = "标题";
worksheet.Cells[1, 3].Value = "标题";
worksheet.Cells[1, 4].Value = "标题";