首页 > 代码库 > C#导出excel
C#导出excel
方式一,AppLibrary.WriteExcel.XlsDocument:
所用的库及库源码为:AppLibrary.WriteExcel.XlsDocument,这种方式默认是以excel2003的方式输出(即使机子上只安装office2010),而excel2003最多允许65536行,适合输出比较小的数据量.例如:
1 /// <summary> 2 /// 通过"AppLibrary.WriteExcel.XlsDocument",以.xls方式输出. 3 /// </summary> 4 public void WriteAsXLS() { 5 AppLibrary.WriteExcel.XlsDocument doc = new AppLibrary.WriteExcel.XlsDocument(); 6 doc.FileName = "Report.xls"; 7 8 //获得数据库前30条记录. 9 var data = http://www.mamicode.com/GetTopRecords(30).ToArray<t_review>();10 const int sheetMaxRecord = 10; //每个sheet最多的行数.11 int curRow = 2; //开始游标.12 13 int sheetNum = 1; //sheet序号.14 AppLibrary.WriteExcel.Worksheet sheet = null;15 16 for (int s = 0; s < data.Length; s++) {17 if (s % sheetMaxRecord == 0) {18 sheet = doc.Workbook.Worksheets.Add("sheet-" + sheetNum++);19 AddHead(sheet);20 21 curRow = 2;22 }23 AppLibrary.WriteExcel.Cells cells = sheet.Cells;24 25 cells.Add(curRow, 1, data[s].votes);26 cells.Add(curRow, 2, data[s].user_id);27 cells.Add(curRow, 3, data[s].review_id);28 cells.Add(curRow, 4, data[s].stars);29 cells.Add(curRow, 5, data[s].date);30 cells.Add(curRow, 6, data[s].text);31 cells.Add(curRow, 7, data[s].type);32 cells.Add(curRow, 8, data[s].business_id);33 34 curRow++;35 }36 doc.Send();37 Response.Flush();38 Response.End();39 }
获得记录的GetTopRecords方法为:
1 IQueryable<t_review> GetTopRecords(int topNum) {2 ReviewDataContext re = new ReviewDataContext();3 return (from r in re.t_reviews4 select r).Take<t_review>(topNum);5 }
通过这种方式,处理大型数据的时候,容易抛出 System.OutOfMemoryException 异常,例如,我的数据库量为 一百多万条,就不能使用这种方式,即使产生多个sheet,例如:
1 public void WriteAsXLS() { 2 AppLibrary.WriteExcel.XlsDocument doc = new AppLibrary.WriteExcel.XlsDocument(); 3 doc.FileName = "Report.xls"; 4 5 ReviewDataContext re = new ReviewDataContext(); 6 var data = http://www.mamicode.com/from r in re.t_reviews 7 select r; 8 const int sheetMaxRecord = 60000; //每个sheet最多的行数. 9 int curRecord = 0; //实际记录号.10 int curRow = 2; //当前待写入sheet中的行号.11 12 int sheetNum = 1; //sheet序号.13 AppLibrary.WriteExcel.Worksheet sheet = null;14 15 foreach (var r in data) {16 if (curRecord % sheetMaxRecord == 0) {17 sheet = doc.Workbook.Worksheets.Add("sheet-" + sheetNum++);18 AddHead(sheet);19 20 curRow = 2;21 }22 AppLibrary.WriteExcel.Cells cells = sheet.Cells;23 24 cells.Add(curRow, 1, r.votes);25 cells.Add(curRow, 2, r.user_id);26 cells.Add(curRow, 3, r.review_id);27 cells.Add(curRow, 4, r.stars);28 cells.Add(curRow, 5, r.date);29 cells.Add(curRow, 6, r.text);30 cells.Add(curRow, 7, r.type);31 cells.Add(curRow, 8, r.business_id);32 33 curRecord++;34 }35 doc.Send();36 Response.Flush();37 Response.End();38 }
C#导出excel
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。