/// <summary> /// 导出期初模板 /// </summary> /// <param name="dtSource"></param> /// <param name="exportTemplateFilePath"></param> /// <param name="fillRow"></param> /// <param name="replaceCells"></param> /// <returns></returns> private static MemoryStream ExportOpeningTemplate(DataTable dtSource, string[] dropDowndtSource, string exportTemplateFilePath, int fillRow, int dropDownFillStartCell, int dropDownFillEndCell) { try { //打开Excle模板文件 IWorkbook workbook = null; using (FileStream fileOne = new FileStream(exportTemplateFilePath, FileMode.Open, FileAccess.Read)) { workbook = new XSSFWorkbook(fileOne); //获取第一个工作表 } XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0); //获取第一个sheet XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper .CreateExplicitListConstraint(dropDowndtSource); CellRangeAddressList addressList = new CellRangeAddressList(1, dtSource.Rows.Count, dropDownFillStartCell, dropDownFillEndCell); XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList); sheet.AddValidationData(validation); //格式日期 XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle; XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); //格式数字 XSSFCellStyle decimelStyle = workbook.CreateCellStyle() as XSSFCellStyle; XSSFDataFormat decimelformat = workbook.CreateDataFormat() as XSSFDataFormat; decimelStyle.DataFormat = decimelformat.GetFormat("0.00####"); //单元格样式 ICellStyle style = workbook.CreateCellStyle(); //style.BorderBottom = BorderStyle.Thin; //style.BorderLeft = BorderStyle.Thin; //style.BorderRight = BorderStyle.Thin; //style.BorderTop = BorderStyle.Thin; int rowIndex = fillRow; foreach (DataRow row in dtSource.Rows) { #region 填充内容 //sheet.ShiftRows(rowIndex, sheet.LastRowNum, 1, true, false); XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow; foreach (DataColumn column in dtSource.Columns) { XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 if (drValue.Length > 0) { DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 } 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); newCell.CellStyle = decimelStyle; break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } newCell.CellStyle = style; } #endregion 填充内容 rowIndex++; } NpoiMemoryStream ms = new NpoiMemoryStream(); ms.AllowClose = false; workbook.Write(ms); ms.Flush(); ms.Position = 0; ms.Seek(0, SeekOrigin.Begin); ms.AllowClose = true; return(ms); } catch (Exception ex) { throw ex; } }
private static MemoryStream ExportExcel2007(DataTable dtSource, string strHeaderText) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet; //格式日期 XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle; XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); //格式数字 XSSFCellStyle decimelStyle = workbook.CreateCellStyle() as XSSFCellStyle; XSSFDataFormat decimelformat = workbook.CreateDataFormat() as XSSFDataFormat; decimelStyle.DataFormat = decimelformat.GetFormat("0.00####"); // 取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { if (rowIndex == 1048576 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet() as XSSFSheet; } #region 表头及样式 { XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow; headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle; headStyle.Alignment = HorizontalAlignment.Center; XSSFFont font = workbook.CreateFont() as XSSFFont; font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); } #endregion 表头及样式 #region 列头及样式 { XSSFRow headerRow = sheet.CreateRow(1) as XSSFRow; XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle; headStyle.Alignment = HorizontalAlignment.Center; XSSFFont font = workbook.CreateFont() as XSSFFont; font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.IsLocked = true; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] > 255 ? 254 : arrColWidth[column.Ordinal] + 1) * 256); } //sheet.CreateFreezePane(0, 2, 0, dtSource.Columns.Count - 1); } rowIndex = 2; #endregion 列头及样式 rowIndex = 2; } #region 填充内容 XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow; foreach (DataColumn column in dtSource.Columns) { XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 if (drValue.Length > 0) { DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 } 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); newCell.CellStyle = decimelStyle; break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion 填充内容 rowIndex++; } NpoiMemoryStream ms = new NpoiMemoryStream(); ms.AllowClose = false; workbook.Write(ms); ms.Flush(); ms.Position = 0; ms.Seek(0, SeekOrigin.Begin); ms.AllowClose = true; return(ms); }