使用NPOI和委托做EXCEL导出
2013-04-18 20:55 Mike.Jiang 阅读(2983) 评论(1) 编辑 收藏 举报首先,在用NPOI导出时,学习了邀月这篇文章NPOI根据Excel模板生成原生的Excel文件实例,在这里先行谢过了。
本篇文章在邀月的基本上,做了一些小的改动,加上委托的机制。因为在做导出时,加载模板,下载为EXCEL的代码相同,但是在设置EXCEL模板中的值时有很大的不同。所以以了一些小的改动。
改动后的主要类文件如下:
ExcelHelper:
View Code
public class ExcelHelper { private string templatePath; private string newFileName; private string templdateName; private string sheetName; public string SheetName { get { return sheetName; } set { sheetName = value; } } public ExcelHelper(string templdateName, string newFileName) { this.sheetName = "sheet1"; templatePath = HttpContext.Current.Server.MapPath("/") + "/Config/Template/"; this.templdateName = string.Format("{0}{1}", templatePath, templdateName); this.newFileName = newFileName; } public void ExportDataToExcel(Action<HSSFSheet> actionMethod) { using (MemoryStream ms = SetDataToExcel(actionMethod)) { byte[] data = ms.ToArray(); #region response to the client HttpResponse response = System.Web.HttpContext.Current.Response; response.Clear(); response.Charset = "UTF-8"; response.ContentType = "application/vnd-excel";//"application/vnd.ms-excel"; System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + newFileName)); System.Web.HttpContext.Current.Response.BinaryWrite(data); #endregion } } private MemoryStream SetDataToExcel(Action<HSSFSheet> actionMethod) { //Load template file FileStream file = new FileStream(templdateName, FileMode.Open, FileAccess.Read); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.GetSheet(SheetName); if (actionMethod != null) actionMethod(sheet); sheet.ForceFormulaRecalculation = true; using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; workbook = null; return ms; } } }
PrintManager:
View Code
public class PrintManager { public void PrintPurchase() { ExcelHelper helper = new ExcelHelper("PurchaseOrder.xls", "PurchaseOrder_C000001.xls"); helper.ExportDataToExcel(SetPurchaseOrder); } private void SetPurchaseOrder(HSSFSheet sheet) { HSSFRow row = null; HSSFCell cell = null; row = sheet.GetRow(2); cell = row.GetCell(1); cell.SetCellValue("C0000001"); cell = row.GetCell(7); cell.SetCellValue("2013-04-18"); DataTable itemDT = PrepareItemDTForTest(); SetDataTableValue(sheet, 7, 0, itemDT); row = sheet.GetRow(14); cell = row.GetCell(0); cell.SetCellValue("NOKIA"); cell = row.GetCell(6); cell.SetCellValue("CMCC"); } public void SetDataTableValue(HSSFSheet sheet, int rowIndex, int columnIndex,DataTable dt) { HSSFRow row = null; HSSFCell cell = null; foreach (DataRow dataRow in dt.Rows) { row = sheet.GetRow(rowIndex); columnIndex = 0; foreach (DataColumn column in dt.Columns) { cell = row.GetCell(columnIndex); string drValue = dataRow[column].ToString(); switch (column.DataType.ToString()) { case "System.String": cell.SetCellValue(drValue); break; case "System.DateTime": DateTime dateV; DateTime.TryParse(drValue, out dateV); cell.SetCellValue(dateV); break; case "System.Boolean": bool boolV = false; bool.TryParse(drValue, out boolV); cell.SetCellValue(boolV); break; case "System.Int16": case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); cell.SetCellValue(intV); break; case "System.Decimal": case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); cell.SetCellValue(doubV); break; case "System.DBNull": cell.SetCellValue(""); break; default: cell.SetCellValue(""); break; } columnIndex++; } rowIndex++; } } private DataTable PrepareItemDTForTest() { DataTable itemDT = new DataTable(); itemDT.Columns.Add("Name"); itemDT.Columns.Add("Qty", Type.GetType("System.Decimal")); itemDT.Columns.Add("UnitPrice", Type.GetType("System.Decimal")); DataRow newRow = itemDT.NewRow(); newRow[0] = "820"; newRow[1] = "100"; newRow[2] = "3000"; itemDT.Rows.Add(newRow); DataRow newRow2 = itemDT.NewRow(); newRow2[0] = "920"; newRow2[1] = "100"; newRow2[2] = "4000"; itemDT.Rows.Add(newRow2); return itemDT; } }
备注:
ExcelHelper类:负责打开模板,调用传来的方法设置值,输出流。
PrintManager类:负责调用ExcelHelper然后到业务模块取数据,然后将业务数据填充到HSSFSheet中去。
效果
EXCEL模板:
导出的EXCEL:
DEMO下载:NopiTest.zip