你的位置:首页 > ASP.net教程

[ASP.net教程]epplus excel数据导出(数据量有点大的情况) Web和Client


Asp.net MVC后台代码 
public ActionResult Export() { OfficeOpen= new OfficeOpen= ep.Workbook; OfficeOpen= wb.Worksheets.Add("我的工作表"); ////配置文件属性 //wb.Properties.Category = "类别"; //wb.Properties.Author = "作者"; //wb.Properties.Comments = "备注"; //wb.Properties.Company = "公司"; //wb.Properties.Keywords = "关键字"; //wb.Properties.Manager = "管理者"; //wb.Properties.Status = "内容状态"; //wb.Properties.Subject = "主题"; //wb.Properties.Title = "标题"; //wb.Properties.LastModifiedBy = "最后一次保存者"; // var list = GetList(); int x = 0; for (int i = 0; i < 300000; i++) { //if (x == 1000000) //{ // ws = wb.Worksheets.Add("我的工作表" + Guid.NewGuid().ToString()); // x = 0; //} for (int j = 1; j <= 9; j++) { ws.Cells[(x + 1), j].Value = DateTime.Now.ToString(); } x++; } //写数据 //ws.Cells[1, 1].Value = "Hello"; //ws.Cells[1, 1].Style.Numberformat.Format = "yyyy-MM-dd"; //ws.Column(1).Width = 40;//修改列宽 //ws.Cells["B1"].Value = "World"; //ws.Cells[3, 3, 3, 5].Merge = true; //ws.Cells[3, 3].Value = "Cells[3, 3, 3, 5]合并"; //ws.Cells["A4:D5"].Merge = true; //ws.Cells["A4:D5"].Style.HorizontalAlignment = OfficeOpen//居中 //ws.Cells["A4"].Value = "Cells[\"A4:D5\"]合并"; //写到客户端(下载) Response.Clear(); Response.AddHeader("content-disposition", "attachment; filename=FileFlow.xlsx"); Response.ContentType = "application/vnd.open"; byte[] data=ep.GetAsByteArray(); Response.AddHeader("Content-Length", data.Length.ToString()); Response.BinaryWrite(data); //ep.SaveAs(Response.OutputStream); 第二种方式 Response.Flush(); Response.End(); return null; }

 FileInfo newFile = new FileInfo(@"d:\test.xlsx");      if (newFile.Exists)      {        newFile.Delete();        newFile = new FileInfo(@"d:\test.xlsx");      }      //using (ExcelPackage package = new ExcelPackage(newFile))      //{      //  ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("test");      //  worksheet.Cells[1, 1].Value = "名称";      //  worksheet.Cells[1, 2].Value = "价格";      //  worksheet.Cells[1, 3].Value = "销量";      //  worksheet.Cells[2, 1].Value = "大米";      //  worksheet.Cells[2, 2].Value = 56;      //  worksheet.Cells[2, 3].Value = 100;      //  worksheet.Cells[3, 1].Value = "玉米";      //  worksheet.Cells[3, 2].Value = 45;      //  worksheet.Cells[3, 3].Value = 150;      //  worksheet.Cells[4, 1].Value = "小米";      //  worksheet.Cells[4, 2].Value = 38;      //  worksheet.Cells[4, 3].Value = 130;      //  worksheet.Cells[5, 1].Value = "糯米";      //  worksheet.Cells[5, 2].Value = 22;      //  worksheet.Cells[5, 3].Value = 200;      //  package.Save();      //}      OfficeOpennew OfficeOpen= ep.Workbook;      OfficeOpen= wb.Worksheets.Add("我的工作表");      //配置文件属性      //wb.Properties.Category = "类别";      //wb.Properties.Author = "作者";      //wb.Properties.Comments = "备注";      //wb.Properties.Company = "公司";      //wb.Properties.Keywords = "关键字";      //wb.Properties.Manager = "管理者";      //wb.Properties.Status = "内容状态";      //wb.Properties.Subject = "主题";      //wb.Properties.Title = "标题";      //wb.Properties.LastModifiedBy = "最后一次保存者";      // var list = GetList();      int x = 0;      for (int i = 0; i < 100; i++)      {        if (x == 1000000)        {          ws = wb.Worksheets.Add("我的工作表" + Guid.NewGuid().ToString());          x = 0;        }        for (int j = 1; j <= 2; j++)        {          ws.Cells[(x + 1), j].Value = DateTime.Now.ToString();          Console.WriteLine((i+1));        }        x++;      }      ep.Save();

Client的版本亲测至少可导出千万级别的数据,Web版本要看服务器内存配置。。

epplus下载路径: http://epplus.codeplex.com/