Java给Excel设置单元格格式

maven 依赖

<!--读取excel文件-->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.2.3</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.3</version>
</dependency><dependency><groupId>commons-io</groupId><artifactId>commons-io</artifactId><version>2.11.0</version>
</dependency>
<!--FileUtils--><!--日志-->
<dependency><groupId>org.apache.logging.log4j</groupId><artifactId>log4j-api</artifactId><version>2.15.0</version>
</dependency>
<dependency><groupId>org.apache.logging.log4j</groupId><artifactId>log4j-core</artifactId><version>2.15.0</version>
</dependency>
<dependency><groupId>org.apache.logging.log4j</groupId><artifactId>log4j-slf4j-impl</artifactId><version>2.15.0</version>
</dependency>

字体样式

public static Font demoFont(Workbook workbook) {Font font = workbook.createFont();font.setFontName("等线"); // 字体font.setFontHeightInPoints((short) 14);// 字号font.setBold(true);// 加粗font.setColor(IndexedColors.BLACK.getIndex());// 颜色font.setItalic(true);// 斜体font.setStrikeout(true);// 使用划线return font;
}

单元格样式

public static CellStyle demoStyle(Workbook workbook) {CellStyle cellStyle = workbook.createCellStyle();cellStyle.setFont(demoFont(workbook)); // 字体cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中cellStyle.setWrapText(true); // 自动换行cellStyle.setBorderTop(BorderStyle.THIN); // 单元格边框cellStyle.setBorderBottom(BorderStyle.THIN); // 单元格边框cellStyle.setBorderLeft(BorderStyle.THIN); // 单元格边框cellStyle.setBorderRight(BorderStyle.THIN); // 单元格边框cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//高度居中cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 实心填充cellStyle.setFillForegroundColor(new XSSFColor(new Color(181, 230, 181),new DefaultIndexedColorMap()));return cellStyle;
}

设置列宽

sheet.autoSizeColumn(c); // 自适应
sheet.setColumnWidth(c, 50 * 256);

给Sheet页设置格式通俗处理

public static void buildStyle(Workbook wb) {Sheet sheet;Row row;for (int s = 0; s < wb.getNumberOfSheets(); s++) {sheet = wb.getSheetAt(s);for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {row = sheet.getRow(r);for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {if (r == 0) { // 表头row.getCell(c).setCellStyle(headerStyle(wb));sheet.autoSizeColumn(c);} else { // 数据row.getCell(c).setCellStyle(cellsStyle(wb));if (sheet.getColumnWidth(c) > 50 * 256) {sheet.setColumnWidth(c, 50 * 256);}}}}}
}

参考

POI 导出 Excel 并且根据内容设置列宽自适应