JAVA使用POI操作EXCEL

1. 设置公式

totalRow.createCell(4).setCellFormula("SUM(E9:E35");

// 执行公式

wb.setForceFormulaRecalculation(true);

2. 合并单元格

sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 7));

3. 单元格格式

CellStyle cellStyle = wb.createCellStyle();

// 字体

XSSFFont font = wb.createFont();

font.setFontName("Arial");

font.setFontHeightInPoints((short) 10);

cellStyle.setFont(font);

// 自动换行

cellStyle.setWrapText(true);

// 边框

cellStyle.setBorderLeft(BorderStyle.THIN);

cellStyle.setBorderRight(BorderStyle.THIN);

cellStyle.setBorderTop(BorderStyle.THIN);

cellStyle.setBorderBottom(BorderStyle.THIN);

// 居中

cellStyle.setAlignment(HorizontalAlignment.CENTER);

cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

// 文本格式

XSSFDataFormat dataFormat = wb.createDataFormat();

List<String> builtinFormats = HSSFDataFormat.getBuiltinFormats();

dataFormat.putFormat((short) builtinFormats.size(), "¥#,##0;[红色]¥-#,##0"); // 增加自定义文本格式

cellStyle.setDataFormat((short) (builtinFormats.size()));

4. 行高

row.setHeightInPoints(30);