方式一,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 = 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_reviews 4 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 = 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 }