筆者近期在公司項目中需要生產比較復雜的Excel報表, 問題點是單個Excel文件中必須能包含多個sheet, 按照以前項目的經驗, 此情況需要使用MS Office組件實現。但是客觀情況是office組件其版本兼容問題比較多(Excel版本不一致導致無法使用、excel進程無法回收、導致w3wp進程崩潰等), 無法把控摒棄之。
在codeplex上看到EPPlus組件, 好像可以解決上述問題。
EPPlus使用的是Open Office XML Format, 其讀寫支持Excel 2003/2007。
項目地址:http://epplus.codeplex.com/
在asp.net頁面上導出Excel代碼如下:
public static void DumpExcel(HttpContext context,string flieName,IDictionary<string,DataTable> dict) { using (ExcelPackage pck = new ExcelPackage()) { foreach (var kp in dict) { //Create the worksheet ExcelWorksheet ws = pck.Workbook.Worksheets.Add(kp.Key); //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1 ws.Cells["A1"].LoadFromDataTable(kp.Value, true); ////Format the header for column 1-3 //using (ExcelRange rng = ws.Cells["A1:C1"]) //{ // rng.Style.Font.Bold = true; // rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid // rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue // rng.Style.Font.Color.SetColor(Color.White); //} ////Example how to Format Column 1 as numeric //using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1]) //{ // col.Style.Numberformat.Format = "#,##0.00"; // col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; //} } //Write it back to the client var data = pck.GetAsByteArray(); context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; context.Response.AddHeader("content-disposition", "attachment; filename=" + flieName + ".xlsx"); context.Response.AddHeader("Content-Length", data.Length.ToString()); context.Response.BinaryWrite(data); } }
參數dict傳遞的是sheetname和DataTable的鍵值對!
