/// <summary> /// 利用模板,DataTable导出到Excel(单个类别) /// </summary> /// <param name="dtSource">DataTable</param> /// <param name="strTemplateFileName">模板的文件路径、名称</param> /// <param name="flg">文件标识--sheet名(1:个人所得税/2:)</param> /// <param name="titleName">表头名称</param> /// <returns></returns> private static MemoryStream ExportExcelForDtByNPOI(DataTable dtSource, string strTemplateFileName, FileEmun.TemplateEmun type, string titleName) { #region 处理DataTable,处理明细表中没有而需要额外读取汇总值的两列 #endregion int totalIndex = 20; // 每个类别的总行数 int rowIndex = 2; // 起始行 int dtRowIndex = dtSource.Rows.Count; // DataTable的数据行数 FileStream file = new FileStream(strTemplateFileName, FileMode.Open, FileAccess.Read); //读入excel模板 HSSFWorkbook workbook = new HSSFWorkbook(file); string sheetName = ""; switch (type) { case FileEmun.TemplateEmun.Template_Income: sheetName = "IncomeReport"; break; case FileEmun.TemplateEmun.Template_AllBasicPositionSalary: sheetName = "AllBasicSalaryStandardReport"; break; case FileEmun.TemplateEmun.Template_AreaBasicPositionSalary: sheetName = "AreaBasicPositionSalaryReport"; break; case FileEmun.TemplateEmun.Template_ShopBasicPositionSalary: sheetName = "ShopBasicPositionSalary"; break; case FileEmun.TemplateEmun.Template_ExceptionalStaff: sheetName = "ExceptionalStaff"; break; case FileEmun.TemplateEmun.Template_UserWage: sheetName = "UserWage"; break; } Sheet sheet = workbook.GetSheet(sheetName); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "易美娜公司"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "于 明"; //填加xls文件作者信息 si.ApplicationName = "导出EXCEL数据"; //填加xls文件创建程序信息 si.LastAuthor = "于 明"; //填加xls文件最后保存者信息 si.Comments = "于 明"; //填加xls文件作者信息 si.Title = "EXCEL报表数据"; //填加xls文件标题信息 si.Subject = "EXCEL报表数据"; //填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion #region 表头 Row headerRow = sheet.GetRow(0); Cell headerCell = headerRow.GetCell(0); headerCell.SetCellValue(titleName); #endregion // 隐藏多余行 for (int i = rowIndex + dtRowIndex; i < rowIndex + totalIndex; i++) { Row dataRowD = sheet.GetRow(i); dataRowD.Height = 0; dataRowD.ZeroHeight = true; //sheet.RemoveRow(dataRowD); } foreach (DataRow row in dtSource.Rows) { #region 填充内容 Row dataRow = sheet.GetRow(rowIndex); int columnIndex = 0; // 开始列(0为标题列,从1开始) foreach (DataColumn column in dtSource.Columns) { // 列序号赋值 if (columnIndex > dtSource.Columns.Count) { break; } Cell newCell = dataRow.GetCell(columnIndex); if (newCell == null) { newCell = dataRow.CreateCell(columnIndex); } string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } columnIndex++; } #endregion rowIndex++; } //格式化当前sheet,用于数据total计算 sheet.ForceFormulaRecalculation = true; #region Clear "0" System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); int cellCount = headerRow.LastCellNum; for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { Row row = sheet.GetRow(i); if (row != null) { for (int j = row.FirstCellNum; j < cellCount; j++) { Cell c = row.GetCell(j); if (c != null) { switch (c.CellType) { case CellType.NUMERIC: if (c.NumericCellValue == 0) { c.SetCellType(CellType.STRING); c.SetCellValue(string.Empty); } break; case CellType.BLANK: case CellType.STRING: if (c.StringCellValue == "0") { c.SetCellValue(string.Empty); } break; } } } } } #endregion using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; workbook = null; //sheet.Dispose(); //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return(ms); } }
/// <summary> /// 利用模板,DataTable导出到Excel(单个类别) /// </summary> /// <param name="dtSource">DataTable</param> /// <param name="strFileName">生成的文件路径、名称</param> /// <param name="strTemplateFileName">模板的文件路径、名称</param> /// <param name="flg">文件标识(1:个人所得税/2:)</param> /// <param name="titleName">表头名称</param> public static void ExportExcelForDtByNPOI(DataTable dtSource, string strFileName, string strTemplateFileName, FileEmun.TemplateEmun type, string titleName) { // 利用模板,DataTable导出到Excel(单个类别) using (MemoryStream ms = ExportExcelForDtByNPOI(dtSource, strTemplateFileName, type, titleName)) { byte[] data = ms.ToArray(); #region 客户端保存 HttpResponse response = System.Web.HttpContext.Current.Response; response.Clear(); //Encoding pageEncode = Encoding.GetEncoding(PageEncode); response.ContentEncoding = Encoding.UTF8; response.Charset = "gb2312"; response.ContentType = "application/vnd.ms-excel";//"application/vnd.ms-excel application/vnd-excel"; response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8)); System.Web.HttpContext.Current.Response.BinaryWrite(data); #endregion } }