创建工作表:
Workbook workbook = new XSSFWorkbook();
1.行背景色
CellStyle cellStyle = workbook.createCellStyle();cellStyle.setFillForegroundColor(cellStyle.setFillForegroundColor(IndexedColors.RED.index);cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
2.自定义背景色
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(156, 195, 230)));cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
3.单元格合并、加边框
以合并excel第一行为例,titlesCN是数据项表头(column1.......12)对应的字符串数组。我们要合并index=0的行,即上图“月度生产统计表”所在的行。代码如下:
String reportTitle="报表标题"Row rowReportTitle = sheet.createRow(0);rowReportTitle.setHeight((short) 425);for (int i = 0; i < titlesCN.length; i++) {Cell titleCell = rowReportTitle.createCell(i, Cell.CELL_TYPE_STRING);setReportStyle(workbook, titleCell, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER,HSSFFont.BOLDWEIGHT_BOLD, HSSFColor.BLACK.index, (short) 16, "宋体", "reportHead");}CellRangeAddress titleRegion = new CellRangeAddress(0, 0, 0, titlesCN.length - 1);sheet.addMergedRegion(titleRegion);Cell titleCell = rowReportTitle.getCell(0);titleCell.setCellValue(reportTitle);public static void setReportStyle(Workbook workbook, Cell cell, short zyjz, short sxjz, short bw, short color, short fh, String fontName) {XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();cellStyle.setAlignment(zyjz);//左右居中cellStyle.setVerticalAlignment(sxjz);//上下居中cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 下边框cellStyle.setBorderLeft(CellStyle.BORDER_THIN);// 左边框cellStyle.setBorderTop(CellStyle.BORDER_THIN);// 上边框cellStyle.setBorderRight(CellStyle.BORDER_THIN);// 右边框cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(156, 195, 230)));//设置背景色cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);//填充模式Font font = workbook.createFont();font.setBoldweight(bw);font.setColor(color);font.setFontHeightInPoints(fh);font.setFontName(fontName);cellStyle.setFont(font);cell.setCellStyle(cellStyle);}
步骤说明:
(1)创建titlesCN相应的列,设置边框
(2)合并单元格
(3)设置单元格的值