/** * 设置表头的单元格样式 * * @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; }
/** * 设置列边框 * * @param style */ private void setColumnBorder(XSSFCellStyle style, int tableType) { // ============================ // 设置单元格边框样式 // CellStyle.BORDER_DOUBLE 双边线 // CellStyle.BORDER_THIN 细边线 // CellStyle.BORDER_MEDIUM 中等边线 // CellStyle.BORDER_DASHED 虚线边线 // CellStyle.BORDER_HAIR 小圆点虚线边线 // CellStyle.BORDER_THICK 粗边线 // ============================ // 如果tableType为0,则为Head // 如果为1,则为body if (tableType == 0) { style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); // 顶部边框粗线 style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); // 底部边框粗线 } else { style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 顶部边框细线 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 底部边框细线 } style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边边框 style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边边框 style.setBottomBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置底部边框颜色 style.setTopBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置顶部边框颜色 style.setLeftBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置左边边框颜色 style.setRightBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置右边边框颜色 // style.setWrapText(true); // // 设置单元格内容是否自动换行 }
// If a border style is set on a ReportBandElement we must apply it to all subreport cells private XSSFCellStyle updateSubreportBandElementStyle( XSSFCellStyle cellStyle, BandElement bandElement, Object value, int gridRow, int gridColumn, int colSpan) { if (subreportCellStyle == null) { return cellStyle; } if (gridColumn == 0) { cellStyle.setBorderLeft(subreportCellStyle.getBorderLeft()); cellStyle.setLeftBorderColor(subreportCellStyle.getLeftBorderColor()); } else if (gridColumn + colSpan - 1 == bean.getReportLayout().getColumnCount() - 1) { cellStyle.setBorderRight(subreportCellStyle.getBorderRight()); cellStyle.setRightBorderColor(subreportCellStyle.getRightBorderColor()); } if (pageRow == 0) { cellStyle.setBorderTop(subreportCellStyle.getBorderTop()); cellStyle.setTopBorderColor(subreportCellStyle.getTopBorderColor()); } else if ((pageRow + 1) == getRowsCount()) { cellStyle.setBorderBottom(subreportCellStyle.getBorderBottom()); cellStyle.setBottomBorderColor(subreportCellStyle.getBottomBorderColor()); } return cellStyle; }
/** * 罫線スタイルの<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; }
/** * 罫線スタイルの<b>CellStyle</b>を生成 * * @param workbook ワークブック * @return <b>CellStyle</b> */ public static XSSFCellStyle createTableDataCellStyle(XSSFWorkbook workbook) { XSSFCellStyle style = workbook.createCellStyle(); style.setBorderTop(XSSFCellStyle.BORDER_THIN); style.setBorderBottom(XSSFCellStyle.BORDER_THIN); style.setBorderLeft(XSSFCellStyle.BORDER_THIN); style.setBorderRight(XSSFCellStyle.BORDER_THIN); return style; }
private void copyDefaultCellStyle(XSSFDataFormat format, Cell cell, XSSFCellStyle cs, int i) { cs.setAlignment(XSSFCellStyle.ALIGN_CENTER_SELECTION); cs.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); cs.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); cs.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); cs.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); cs.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); if (i == 1) cs.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:s")); if (i == 2) cs.setDataFormat(format.getFormat("0")); cell.setCellStyle(cs); }
/** * 罫線スタイルの<b>CellStyle</b>を生成 * * @param workbook ワークブック * @param isBorder 罫線描画フラグ * @return <b>CellStyle</b> */ public static XSSFCellStyle createTableDataCellStyle(XSSFWorkbook workbook, boolean isBorder) { XSSFCellStyle style = workbook.createCellStyle(); style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); // 必ずsetFillForegroundColorの直前に記述すること if (isBorder) { style.setBorderTop(XSSFCellStyle.BORDER_THIN); style.setBorderBottom(XSSFCellStyle.BORDER_THIN); style.setBorderLeft(XSSFCellStyle.BORDER_THIN); style.setBorderRight(XSSFCellStyle.BORDER_THIN); } return style; }
/** * デフォルトのテーブルヘッダースタイルを作成 * * @param workbook ワークブック * @param bold 太字設定フラグ * @param fontSize フォントサイズ * @param backgroundColor 背景色 * @return <b>CellStyle</b> */ public static XSSFCellStyle createDefaultTableHeaderCellStyle( XSSFWorkbook workbook, boolean bold, boolean center, int fontSize, Color backgroundColor) { XSSFFont font = workbook.createFont(); if (bold) { font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); } font.setFontHeightInPoints((short) fontSize); font.setFontName(DEFAULT_FONT_NAME); XSSFCellStyle style = workbook.createCellStyle(); if (center) { style.setAlignment(XSSFCellStyle.ALIGN_CENTER); } style.setFont(font); style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); // 必ずsetFillForegroundColorの直前に記述すること style.setFillForegroundColor(new XSSFColor(backgroundColor)); style.setBorderTop(XSSFCellStyle.BORDER_THIN); style.setBorderBottom(XSSFCellStyle.BORDER_THIN); style.setBorderLeft(XSSFCellStyle.BORDER_THIN); 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 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; }
public static boolean writeMultArray2XLSXFile( String fileName, List<List<String[]>> arrys, List<String> sheetLabels, boolean hasHead, int indexKey) throws Exception { if (arrys.isEmpty()) { System.err.println("No input data!"); return false; } XSSFWorkbook wb = new XSSFWorkbook(); XSSFCellStyle headStyle = wb.createCellStyle(); // apply custom font to the text in the comment XSSFFont font = wb.createFont(); font.setFontName("Courier New"); font.setFontHeightInPoints((short) 10); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.RED.index); headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); headStyle.setLocked(true); headStyle.setFont(font); XSSFCellStyle bodyStyle = wb.createCellStyle(); bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFCellStyle markedBodyStyle = wb.createCellStyle(); markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); String lastKey = null; int switcher = -1; XSSFCell cell = null; String[] titleNames = null; int d = 0; for (List<String[]> arry : arrys) { XSSFSheet sheet1 = wb.createSheet(sheetLabels.get(d)); if (hasHead) { titleNames = (String[]) arry.get(0); } int columnNum = ((String[]) arry.get(0)).length; for (int i = 0; i < columnNum; i++) { sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20))); } int rowIndex = 0; // create titile row XSSFRow row = sheet1.createRow(rowIndex); if (titleNames != null) { for (int i = 0; i < columnNum; i++) { cell = row.createCell(i); cell.setCellValue(titleNames[i]); cell.setCellStyle(headStyle); } rowIndex++; } int rowNum = arry.size(); for (int i = rowIndex; i < rowNum; i++) { row = sheet1.createRow((i)); String[] line = (String[]) arry.get(i); columnNum = line.length; if (indexKey >= 0) { if (lastKey == null && line[indexKey] != null) { lastKey = line[indexKey]; switcher *= -1; } else if (lastKey != null && line[indexKey] == null) { lastKey = line[indexKey]; switcher *= -1; } else if (lastKey == null && line[indexKey] == null) { } else { if (!lastKey.equals(line[indexKey])) { switcher *= -1; lastKey = line[indexKey]; } } } else { switcher = 1; } for (int j = 0; j < columnNum; j++) { cell = row.createCell(j); if (switcher > 0) { cell.setCellStyle(bodyStyle); } else { cell.setCellStyle(markedBodyStyle); } if (line[j] != null) { if (Util.isNumeric(line[j])) { // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC cell.setCellType(0); cell.setCellValue(Double.parseDouble(line[j])); } else { cell.setCellValue(line[j]); } } else { cell.setCellValue("."); } } } d++; } // Write the output to a inFile FileOutputStream fileOut = new FileOutputStream(fileName); wb.write(fileOut); fileOut.close(); return true; }
public static boolean convertTextFile2XLSXFile( String inFileName, String outFileName, boolean hasHead, int indexKey) throws Exception { BufferedReader br = LocalFileFunc.getBufferedReader(inFileName); String line = br.readLine(); if (line == null) { return false; } String[] cells1 = Util.tokenize(line, '\t'); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet1 = wb.createSheet("Data"); int columnNum = cells1.length; for (int i = 0; i < columnNum; i++) { sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20))); } XSSFCellStyle headStyle = wb.createCellStyle(); // apply custom font to the text in the comment XSSFFont font = wb.createFont(); font.setFontName("Courier New"); font.setFontHeightInPoints((short) 10); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.RED.index); headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); headStyle.setLocked(true); headStyle.setFont(font); XSSFCellStyle bodyStyle = wb.createCellStyle(); bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFCellStyle markedBodyStyle = wb.createCellStyle(); markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); int rowIndex = 0; // create titile row XSSFRow row = sheet1.createRow(rowIndex); String lastKey = null; int switcher = -1; XSSFCell cell = null; if (hasHead) { for (int i = 0; i < columnNum; i++) { cell = row.createCell(i); cell.setCellValue(cells1[i]); cell.setCellStyle(headStyle); } } else { for (int i = 0; i < columnNum; i++) { cell = row.createCell(i); cell.setCellValue(cells1[i]); cell.setCellStyle(bodyStyle); } } rowIndex++; while ((line = br.readLine()) != null) { cells1 = Util.tokenize(line, '\t'); row = sheet1.createRow((rowIndex)); columnNum = cells1.length; if (indexKey >= 0) { if (lastKey == null && cells1[indexKey] != null) { lastKey = cells1[indexKey]; switcher *= -1; } else if (lastKey != null && cells1[indexKey] == null) { lastKey = cells1[indexKey]; switcher *= -1; } else if (lastKey == null && cells1[indexKey] == null) { } else { if (!lastKey.equals(cells1[indexKey])) { switcher *= -1; lastKey = cells1[indexKey]; } } } else { switcher = 1; } // System.out.println(cells1[0]); for (int j = 0; j < columnNum; j++) { cell = row.createCell(j); if (switcher > 0) { cell.setCellStyle(bodyStyle); } else { cell.setCellStyle(markedBodyStyle); } if (cells1[j] != null) { if (Util.isNumeric(cells1[j])) { // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC cell.setCellType(0); cell.setCellValue(Double.parseDouble(cells1[j])); } else { cell.setCellValue(cells1[j]); } } else { cell.setCellValue("."); } } rowIndex++; } br.close(); // Write the output to a inFile FileOutputStream fileOut = new FileOutputStream(outFileName); wb.write(fileOut); fileOut.close(); return true; }
public static boolean writeArray2XLSXSheet( XSSFSheet sheet1, XSSFWorkbook wb, List<String[]> arry, boolean hasHead) throws Exception { int rowNum = arry.size(); if (rowNum == 0) { System.err.println("No input data!"); return false; } String[] titleNames = null; if (hasHead) { titleNames = (String[]) arry.get(0); } int columnNum = ((String[]) arry.get(0)).length; for (int i = 0; i < columnNum; i++) { sheet1.setColumnWidth((short) i, (short) ((30 * 8) / ((double) 1 / 20))); } XSSFFont font = wb.createFont(); font.setFontName("Courier New"); font.setFontHeightInPoints((short) 10); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.RED.index); XSSFCellStyle headStyle = wb.createCellStyle(); headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); headStyle.setLocked(true); headStyle.setFont(font); XSSFCellStyle bodyStyle = wb.createCellStyle(); bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFCellStyle markedBodyStyle = wb.createCellStyle(); markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); markedBodyStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); int rowIndex = 0; // create titile row XSSFRow row = sheet1.createRow(rowIndex); XSSFCell cell = null; if (titleNames != null) { for (int i = 0; i < columnNum; i++) { cell = row.createCell((short) i); cell.setCellValue(titleNames[i]); cell.setCellStyle(headStyle); } rowIndex++; } for (int i = rowIndex; i < rowNum; i++) { row = sheet1.createRow((i)); String[] line = (String[]) arry.get(i); columnNum = line.length; for (int j = 0; j < columnNum; j++) { cell = row.createCell(j); if (line[0] != null) { cell.setCellStyle(markedBodyStyle); } else { cell.setCellStyle(bodyStyle); } if (line[j] != null) { if (Util.isNumeric(line[j])) { // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC cell.setCellType(0); cell.setCellValue(Double.parseDouble(line[j])); } else { cell.setCellValue(line[j]); } } else { cell.setCellValue(""); } } } return true; }