/** * 设置表头的单元格样式 * * @return */ public XSSFCellStyle getHeadStyle() { // 创建单元格样式 XSSFCellStyle cellStyle = wb.createCellStyle(); // 设置单元格的背景颜色为淡蓝色 cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index); cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); // 设置单元格居中对齐 cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 设置单元格垂直居中对齐 cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 创建单元格内容显示不下时自动换行 cellStyle.setWrapText(true); // 设置单元格字体样式 XSSFFont font = wb.createFont(); // 设置字体加粗 font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setFontName("宋体"); font.setFontHeight((short) 200); cellStyle.setFont(font); // 设置单元格边框为细线条 cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); return cellStyle; }
/** * 罫線スタイルの<b>CellStyle</b>を生成 1行のみ描画する * * @param workbook ワークブック * @param sheet シート * @param nRow 行 * @param nColumn 列 * @param isBold 太字フラグ * @param fontSize 文字サイズ * @param fontHeight 行高 */ public static void setCellStyleForLabel( XSSFWorkbook workbook, XSSFSheet sheet, int nRow, int nColumn, boolean isBold, short fontSize, float fontHeight) { assert sheet != null; // style設定 XSSFCellStyle style = workbook.createCellStyle(); XSSFFont font = workbook.createFont(); if (isBold) { font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); // 文字太字 } font.setFontHeightInPoints((short) fontSize); // 文字サイズ font.setFontName(DEFAULT_FONT_NAME); style.setFont(font); // 文字太字 と 文字サイズ style.setAlignment(CellStyle.ALIGN_GENERAL); // 水平方向の標準 style.setVerticalAlignment(CellStyle.VERTICAL_TOP); // 垂直方向の上詰め style.setWrapText(true); // 折り返して全体を表示する // セルに罫線を描画 XSSFRow row = getRowAnyway(sheet, nRow); XSSFCell cell = getCellAnyway(row, nColumn); cell.setCellStyle(style); row.setHeightInPoints(fontHeight); // 行高設定 }
/** * 罫線スタイルの<b>CellStyle</b>を生成 * * @param workbook ワークブック * @param backgroundColor 背景色 * @return <b>CellStyle</b> */ public static XSSFCellStyle createTableDataCellStyle( XSSFWorkbook workbook, boolean isTop, boolean isBottom, boolean isLeft, boolean isRight, Color backgroundColor, short fontSize, boolean bold) { XSSFCellStyle style = workbook.createCellStyle(); style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); // 必ずsetFillForegroundColorの直前に記述すること style.setFillForegroundColor(new XSSFColor(backgroundColor)); style.setAlignment(CellStyle.ALIGN_LEFT); // 水平方法の位置 style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setWrapText(true); // 折り返して全体を表示する // style.setShrinkToFit(true);//縮小して全体を表示する // 文字サイズ設定 XSSFFont font = workbook.createFont(); font.setFontHeightInPoints(fontSize); if (bold) { font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); } style.setFont(font); if (isTop) { style.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); } else { style.setBorderTop(XSSFCellStyle.BORDER_THIN); } if (isBottom) { style.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); } else { style.setBorderBottom(XSSFCellStyle.BORDER_THIN); } if (isLeft) { style.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); } else { style.setBorderLeft(XSSFCellStyle.BORDER_THIN); } if (isRight) { style.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); } else { style.setBorderRight(XSSFCellStyle.BORDER_THIN); } return style; }
/** * 设置表体的单元格样式 * * @return */ public XSSFCellStyle getBodyStyle() { // 创建单元格样式 XSSFCellStyle cellStyle = wb.createCellStyle(); // 设置单元格居中对齐 cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 设置单元格垂直居中对齐 cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 创建单元格内容显示不下时自动换行 cellStyle.setWrapText(true); // 设置单元格字体样式 XSSFFont font = wb.createFont(); // 设置字体加粗 font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setFontName("宋体"); font.setFontHeight((short) 200); cellStyle.setFont(font); // 设置单元格边框为细线条 cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); return cellStyle; }
public static XSSFCellStyle getNewStyle( XSSFWorkbook workBook, CellStyle cellStyle, XSSFFont font) { XSSFCellStyle style = workBook.createCellStyle(); // 对齐方式 style.setAlignment(cellStyle.getAlignment()); style.setVerticalAlignment(cellStyle.getVAlignment()); // 设置背景颜色 // 最好的设置Pattern style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 单元格背景的显示模式 style.setFillForegroundColor(cellStyle.getColor()); // 单元格背景的显示模式. // style.setFillBackgroundColor(arg0); // 设置边框 style.setBorderBottom(cellStyle.getBorderBottom()); // 下边框 style.setBorderLeft(cellStyle.getBorderLeft()); // 左边框 style.setBorderTop(cellStyle.getBorderTop()); // 上边框 style.setBorderRight(cellStyle.getBorderRight()); // 右边框 // 设置边框颜色 style.setBottomBorderColor(cellStyle.getBottomBorderColor()); style.setTopBorderColor(cellStyle.getTopBorderColor()); style.setLeftBorderColor(cellStyle.getLeftBorderColor()); style.setRightBorderColor(cellStyle.getRightBorderColor()); // 设置自动换行 style.setWrapText(cellStyle.getWrapText()); style.setHidden(cellStyle.getHidden()); // 数据格式 style.setDataFormat(cellStyle.getDataFormate()); style.setLocked(cellStyle.getLocked()); // 文本旋转 请注意,这里的Rotation取值是从-90到90,而不是0-180度 style.setRotation(cellStyle.getRotation()); // 文本缩进 style.setIndention(cellStyle.getIndention()); // 设置字体 style.setFont(font); return style; }
private void renderCell( BandElement bandElement, String bandName, Object value, int gridRow, int sheetRow, int sheetColumn, int rowSpan, int colSpan, boolean image) { if (bandElement instanceof ReportBandElement) { colSpan = 1; } XSSFCellStyle cellStyle = buildBandElementStyle(bandElement, value, gridRow, sheetColumn, colSpan); // if we have a subreport on the current grid row we have to take care of the sheetColumn if (ReportLayout.HEADER_BAND_NAME.equals(bandName) && (gridRow == prevSubreportFirstRow) && (prevSubreportLastColumn != -1)) { sheetColumn = prevSubreportLastColumn - prevSubreportFirstColumn - 1 + sheetColumn; } XSSFCell c = xlsRow.createCell(sheetColumn); if (image) { if ((value == null) || "".equals(value)) { c.setCellType(XSSFCell.CELL_TYPE_STRING); c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_FOUND)); } else { try { ImageBandElement ibe = (ImageBandElement) bandElement; byte[] imageBytes = getImage((String) value, ibe.getWidth(), ibe.getHeight()); XSSFClientAnchor anchor = new XSSFClientAnchor( 0, 0, 0, 0, (short) sheetColumn, sheetRow, (short) (sheetColumn + colSpan), (sheetRow + rowSpan)); int index = wb.addPicture(imageBytes, XSSFWorkbook.PICTURE_TYPE_JPEG); // image is created over the cells, so if it's height is bigger we set the row height short height = xlsRow.getHeight(); int realImageHeight = getRealImageSize((String) value)[1]; if (ibe.isScaled()) { realImageHeight = ibe.getHeight(); } short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5); boolean doResize = false; if (imageHeight > height) { xlsRow.setHeight(imageHeight); } else { doResize = true; } Picture picture = patriarch.createPicture(anchor, index); if (doResize) { picture.resize(); } anchor.setAnchorType(2); } catch (Exception ex) { c.setCellType(XSSFCell.CELL_TYPE_STRING); c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_LOADED)); } } if (cellStyle != null) { c.setCellStyle(cellStyle); } } else { if (bandElement instanceof HyperlinkBandElement) { Hyperlink hyp = ((HyperlinkBandElement) bandElement).getHyperlink(); XSSFHyperlink link = wb.getCreationHelper().createHyperlink(XSSFHyperlink.LINK_URL); link.setAddress(hyp.getUrl()); c.setHyperlink(link); c.setCellValue(wb.getCreationHelper().createRichTextString(hyp.getText())); c.setCellType(XSSFCell.CELL_TYPE_STRING); } else if (bandElement instanceof ReportBandElement) { Report report = ((ReportBandElement) bandElement).getReport(); ExporterBean eb = null; try { eb = getSubreportExporterBean(report, true); XlsxExporter subExporter = new XlsxExporter(eb, cellStyle); subExporter.export(); XSSFSheet subreportSheet = subExporter.getSubreportSheet(); if (ReportLayout.HEADER_BAND_NAME.equals(bandName) && (gridRow == prevSubreportFirstRow)) { // other subreports on the same header line after the first sheetColumn = prevSubreportLastColumn; sheetRow -= addedPageRows; pageRow -= addedPageRows; addedPageRows = 0; } else { addedPageRows = subreportSheet.getLastRowNum(); pageRow += addedPageRows; // if subreport is not on the first column we merge all cells in the columns before, // between the rows subreport occupies if (sheetColumn > 0) { for (int i = 0; i <= sheetColumn - 1; i++) { CellRangeAddress cra = new CellRangeAddress(sheetRow, pageRow, i, i); regions.add(new XlsxRegion(cra, null)); } } } int cols = XlsxUtil.copyToSheet(xlsSheet, sheetRow, sheetColumn, subreportSheet); addRegions(xlsSheet, subExporter.getSubreportRegions(), wb); if (ReportLayout.HEADER_BAND_NAME.equals(bandName)) { prevSubreportFirstRow = gridRow; prevSubreportFirstColumn = sheetColumn; prevSubreportLastColumn = sheetColumn + cols; } } catch (Exception e) { e.printStackTrace(); } finally { if ((eb != null) && (eb.getResult() != null)) { eb.getResult().close(); } } } else if (bandElement instanceof ImageColumnBandElement) { try { ImageColumnBandElement icbe = (ImageColumnBandElement) bandElement; String v = StringUtil.getValueAsString(value, null); if (StringUtil.BLOB.equals(v)) { c.setCellType(XSSFCell.CELL_TYPE_STRING); c.setCellValue(wb.getCreationHelper().createRichTextString(StringUtil.BLOB)); } else { byte[] imageD = StringUtil.decodeImage(v); byte[] imageBytes = getImage(imageD, icbe.getWidth(), icbe.getHeight()); XSSFClientAnchor anchor = new XSSFClientAnchor( 0, 0, 0, 0, (short) sheetColumn, sheetRow, (short) (sheetColumn + colSpan), (sheetRow + rowSpan)); int index = wb.addPicture(imageBytes, XSSFWorkbook.PICTURE_TYPE_JPEG); // image is created over the cells, so if it's height is bigger we set the row height short height = xlsRow.getHeight(); int realImageHeight = getRealImageSize(imageBytes)[1]; if (icbe.isScaled()) { realImageHeight = icbe.getHeight(); } short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5); if (imageHeight > height) { xlsRow.setHeight(imageHeight); } Picture picture = patriarch.createPicture(anchor, index); picture.resize(); anchor.setAnchorType(2); } } catch (Exception e) { e.printStackTrace(); c.setCellType(XSSFCell.CELL_TYPE_STRING); c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_LOADED)); } } else { if (value == null) { c.setCellType(XSSFCell.CELL_TYPE_STRING); c.setCellValue(wb.getCreationHelper().createRichTextString("")); } else if (value instanceof Number) { c.setCellType(XSSFCell.CELL_TYPE_NUMERIC); c.setCellValue(((Number) value).doubleValue()); } else { String pattern = null; if (bandElement instanceof FieldBandElement) { FieldBandElement fbe = (FieldBandElement) bandElement; pattern = fbe.getPattern(); } if ((value instanceof java.sql.Date) || (value instanceof java.sql.Timestamp)) { Date date; if (value instanceof java.sql.Date) { date = new Date(((java.sql.Date) value).getTime()); } else { date = (java.sql.Timestamp) value; } if (cellStyle != null) { if (pattern == null) { // use default pattern if none selected Locale locale = Locale.getDefault(); pattern = ((SimpleDateFormat) DateFormat.getDateInstance(SimpleDateFormat.MEDIUM, locale)) .toPattern(); } else { pattern = StringUtil.getI18nString(pattern, getReportLanguage()); } cellStyle.setDataFormat(wb.createDataFormat().getFormat(pattern)); } c.setCellValue(date); } else { c.setCellType(XSSFCell.CELL_TYPE_STRING); String text = StringUtil.getValueAsString(value, pattern); if ((bandElement != null) && bandElement.isWrapText()) { // try to interpret new line characters // \\n is used here to be possible to add in designer grid cell with \n if (text.contains("\\n") || text.contains("\n") || text.contains("\r") || text.contains("\r\n")) { String crLf = Character.toString((char) 13) + Character.toString((char) 10); int lines = countLines(text); if (text.contains("\r\n")) { text = text.replaceAll("\r\n", crLf); } else { text = text.replaceAll("(\n)|(\r)|(\\\\n)", crLf); } c.setCellValue(text); cellStyle.setWrapText(true); xlsRow.setHeightInPoints(lines * (cellStyle.getFont().getFontHeightInPoints() + 3)); } else { c.setCellValue(wb.getCreationHelper().createRichTextString(text)); } } else { c.setCellValue(wb.getCreationHelper().createRichTextString(text)); } } } } if (cellStyle != null) { if (bandElement != null) { cellStyle.setRotation(bandElement.getTextRotation()); } if (!(bandElement instanceof ReportBandElement)) { c.setCellStyle(cellStyle); } } if ((rowSpan > 1) || (colSpan > 1)) { CellRangeAddress cra = new CellRangeAddress( sheetRow, sheetRow + rowSpan - 1, sheetColumn, sheetColumn + colSpan - 1); Border beBorder = bandElement.getBorder(); if (hasRowRenderConditions(bandElement, gridRow, value)) { // for row render conditions we must keep the row border beBorder = border; } regions.add(new XlsxRegion(cra, beBorder)); } } }
private XSSFCellStyle buildBandElementStyle( BandElement bandElement, Object value, int gridRow, int gridColumn, int colSpan) { Map<String, Object> style = buildCellStyleMap(bandElement, value, gridRow, gridColumn, colSpan); XSSFCellStyle cellStyle = null; XSSFFont cellFont = null; int fontKey = -1; int styleKey = -1; // we have to create new fonts and styles if some formatting conditions are met // also for subreports we may have a subreportCellStyle passed by ReportBandElement boolean cacheFont = false; boolean cacheAllFont = false; boolean cacheStyle = false; boolean cacheAllStyle = false; if ((modifiedStyle[gridRow][gridColumn]) || bean.isSubreport()) { fontKey = getFontKey(style); if (fontKey != -1) { cellFont = condFonts.get(fontKey); } if (cellFont == null) { cellFont = wb.createFont(); cacheFont = true; } styleKey = getStyleKey(style, bandElement); if (styleKey != -1) { cellStyle = condStyles.get(styleKey); } if (cellStyle == null) { cellStyle = wb.createCellStyle(); cacheStyle = true; } modifiedStyle[gridRow][gridColumn] = false; } else { fontKey = getFontKey(style); if (fontKey != -1) { cellFont = fonts.get(fontKey); } if ((cellFont == null) && (bandElement != null)) { cellFont = wb.createFont(); cacheAllFont = true; } styleKey = getStyleKey(style, bandElement); if (styleKey != -1) { cellStyle = styles.get(styleKey); } if (cellStyle == null) { cellStyle = wb.createCellStyle(); cacheAllStyle = true; } } // HSSFPalette cellPal = wb.getCustomPalette(); if (style.containsKey(StyleFormatConstants.FONT_FAMILY_KEY)) { String val = (String) style.get(StyleFormatConstants.FONT_FAMILY_KEY); cellFont.setFontName(val); } if (style.containsKey(StyleFormatConstants.FONT_SIZE)) { Float val = (Float) style.get(StyleFormatConstants.FONT_SIZE); cellFont.setFontHeightInPoints(val.shortValue()); } if (style.containsKey(StyleFormatConstants.FONT_COLOR)) { Color val = (Color) style.get(StyleFormatConstants.FONT_COLOR); cellFont.setColor(ExcelColorSupport.getNearestColor(val)); } if (style.containsKey(StyleFormatConstants.FONT_STYLE_KEY)) { if (StyleFormatConstants.FONT_STYLE_NORMAL.equals( style.get(StyleFormatConstants.FONT_STYLE_KEY))) { cellFont.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL); } if (StyleFormatConstants.FONT_STYLE_BOLD.equals( style.get(StyleFormatConstants.FONT_STYLE_KEY))) { cellFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); } if (StyleFormatConstants.FONT_STYLE_ITALIC.equals( style.get(StyleFormatConstants.FONT_STYLE_KEY))) { cellFont.setItalic(true); } if (StyleFormatConstants.FONT_STYLE_BOLDITALIC.equals( style.get(StyleFormatConstants.FONT_STYLE_KEY))) { cellFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); cellFont.setItalic(true); } } if (cacheFont && (fontKey != -1)) { condFonts.put(fontKey, cellFont); } if (cacheAllFont && (fontKey != -1)) { fonts.put(fontKey, cellFont); } if (style.containsKey(StyleFormatConstants.BACKGROUND_COLOR)) { Color val = (Color) style.get(StyleFormatConstants.BACKGROUND_COLOR); cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(ExcelColorSupport.getNearestColor(val)); } if (style.containsKey(StyleFormatConstants.HORIZONTAL_ALIGN_KEY)) { if (StyleFormatConstants.HORIZONTAL_ALIGN_LEFT.equals( style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) { cellStyle.setAlignment((short) 1); } if (StyleFormatConstants.HORIZONTAL_ALIGN_RIGHT.equals( style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) { cellStyle.setAlignment((short) 3); } if (StyleFormatConstants.HORIZONTAL_ALIGN_CENTER.equals( style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) { cellStyle.setAlignment((short) 2); } } if (style.containsKey(StyleFormatConstants.VERTICAL_ALIGN_KEY)) { if (StyleFormatConstants.VERTICAL_ALIGN_TOP.equals( style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) { cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP); } if (StyleFormatConstants.VERTICAL_ALIGN_MIDDLE.equals( style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) { cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); } if (StyleFormatConstants.VERTICAL_ALIGN_BOTTOM.equals( style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) { cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_BOTTOM); } } else { cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); } short left = 0, right = 0, top = 0, bottom = 0; Color leftColor = Color.BLACK, rightColor = Color.BLACK, topColor = Color.BLACK, bottomColor = Color.BLACK; if (style.containsKey(StyleFormatConstants.BORDER_LEFT)) { Float val = (Float) style.get(StyleFormatConstants.BORDER_LEFT); // left = val.shortValue(); if (left == BORDER_THIN_VALUE) { cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); } if (left == BORDER_MEDIUM_VALUE) { cellStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); } if (left == BORDER_THICK_VALUE) { cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THICK); } Color color = (Color) style.get(StyleFormatConstants.BORDER_LEFT_COLOR); leftColor = color; cellStyle.setLeftBorderColor(ExcelColorSupport.getNearestColor(color)); } if (style.containsKey(StyleFormatConstants.BORDER_RIGHT)) { Float val = (Float) style.get(StyleFormatConstants.BORDER_RIGHT); // right = val.shortValue(); if (right == BORDER_THIN_VALUE) { cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); } if (right == BORDER_MEDIUM_VALUE) { cellStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); } if (right == BORDER_THICK_VALUE) { cellStyle.setBorderRight(XSSFCellStyle.BORDER_THICK); } Color color = (Color) style.get(StyleFormatConstants.BORDER_RIGHT_COLOR); rightColor = color; cellStyle.setRightBorderColor(ExcelColorSupport.getNearestColor(color)); } if (style.containsKey(StyleFormatConstants.BORDER_TOP)) { Float val = (Float) style.get(StyleFormatConstants.BORDER_TOP); // top = val.shortValue(); if (top == BORDER_THIN_VALUE) { cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); } if (top == BORDER_MEDIUM_VALUE) { cellStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); } if (top == BORDER_THICK_VALUE) { cellStyle.setBorderTop(XSSFCellStyle.BORDER_THICK); } Color color = (Color) style.get(StyleFormatConstants.BORDER_TOP_COLOR); topColor = color; cellStyle.setTopBorderColor(ExcelColorSupport.getNearestColor(color)); } if (style.containsKey(StyleFormatConstants.BORDER_BOTTOM)) { Float val = (Float) style.get(StyleFormatConstants.BORDER_BOTTOM); // bottom = val.shortValue(); if (bottom == BORDER_THIN_VALUE) { cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); } if (bottom == BORDER_MEDIUM_VALUE) { cellStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); } if (bottom == BORDER_THICK_VALUE) { cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THICK); } Color color = (Color) style.get(StyleFormatConstants.BORDER_BOTTOM_COLOR); bottomColor = color; cellStyle.setBottomBorderColor(ExcelColorSupport.getNearestColor(color)); } border = new Border(left, right, top, bottom); border.setLeftColor(leftColor); border.setRightColor(rightColor); border.setTopColor(topColor); border.setBottomColor(bottomColor); if (cellFont != null) { cellStyle.setFont(cellFont); } if (style.containsKey(StyleFormatConstants.PATTERN)) { String pattern = (String) style.get(StyleFormatConstants.PATTERN); XSSFDataFormat format = wb.createDataFormat(); cellStyle.setDataFormat(format.getFormat(pattern)); } else { cellStyle.setDataFormat((short) 0); } if (bandElement != null) { cellStyle.setWrapText(bandElement.isWrapText()); } cellStyle = updateSubreportBandElementStyle( cellStyle, bandElement, value, gridRow, gridColumn, colSpan); if (cacheStyle && (styleKey != -1)) { condStyles.put(styleKey, cellStyle); } if (cacheAllStyle && (styleKey != -1)) { styles.put(styleKey, cellStyle); } return cellStyle; }
@Override public Workbook writeWorkbook( Workbook workbook, String sheetTitle, List<List<String>> tableDataList, int headerNum) { if (workbook == null) workbook = new XSSFWorkbook(); int exportRecordNum = tableDataList.size(); // 导出的总记录数 int tableStartRowNum = headerNum > 0 ? headerNum : 0; // 表体数据开始行数 int sheetNum = exportRecordNum / (CommonConst.EXCEL_MAX_EXPORT_NUM - headerNum) + 1; // 工作表的页数 List<XSSFSheet> sheetList = new ArrayList<XSSFSheet>(); if (StringUtils.isEmpty(sheetTitle)) sheetTitle = "sheet"; for (int i = 0; i < sheetNum; i++) { // 在Excel工作簿中建一工作表 String sTitle = sheetTitle + (i + 1); XSSFSheet sheet = (XSSFSheet) workbook.createSheet(sTitle); sheet.setSelected(true); // 设置工作薄为选中 sheet.setAutobreaks(true); sheet.setPrintGridlines(true); sheetList.add(sheet); } /** ***********************输出表头********************************* */ if (headerNum > 0) { for (XSSFSheet sheet : sheetList) { XSSFRow headRow = sheet.createRow(0); headRow.setHeightInPoints(20); for (int i = 0; i < headerNum; i++) { List<String> headerRowDataList = tableDataList.get(i); for (int j = 0; j < headerRowDataList.size(); j++) { XSSFCellStyle cellStyle = (XSSFCellStyle) createDefHeaderCellStyle(workbook); // 默认表头样式 createCell(headRow, j, headerRowDataList.get(j), cellStyle); } } // 固定表头 sheet.createFreezePane(0, 1); } } /** *********************输出表体内容************************* */ // 设置列样式 XSSFCellStyle columnStyle = (XSSFCellStyle) workbook.createCellStyle(); columnStyle.setFillBackgroundColor(HSSFColor.GREEN.index); columnStyle.setWrapText(true); if (tableDataList.size() > (tableStartRowNum + 1)) { for (int i = tableStartRowNum; i < exportRecordNum; i++) { List<String> rowDataList = tableDataList.get(i); XSSFRow row = null; int currentSheet = i / CommonConst.EXCEL_MAX_EXPORT_NUM; // 当前工作表的页数 XSSFSheet sheet = sheetList.get(currentSheet); int rowIndex = i - CommonConst.EXCEL_MAX_EXPORT_NUM * currentSheet; row = sheet.createRow(rowIndex); for (int colIndex = 0, colLength = rowDataList.size(); colIndex < colLength; colIndex++) { createCell(row, colIndex, rowDataList.get(colIndex)); } } } // 调整列的宽度(取第一列为基准) for (XSSFSheet sheet : sheetList) { for (int i = 0; i < tableDataList.get(0).size(); i++) { sheet.autoSizeColumn((short) i); sheet.setColumnWidth((short) i, (short) (sheet.getColumnWidth((short) i) + 1000)); } } return workbook; }