首页 > 代码库 > 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