JAVA使用POI操作EXCEL

yong472727322

已于 2023-08-16 16:25:19 修改

阅读量738

点赞数

文章标签： excel java

于 2023-03-10 18:56:08 首次发布

本文链接：https://blog.csdn.net/yong472727322/article/details/129449678

版权

设置公式

totalRow.createCell(4).setCellFormula("SUM(E9:E35");
// 执行公式
wb.setForceFormulaRecalculation(true);

合并单元格

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

单元格格式

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()));

行高

row.setHeightInPoints(30);

设置批注

ExcelReader reader = cn.hutool.poi.excel.ExcelUtil.getReader(targetFile);
ExcelWriter writer = reader.getWriter();
Workbook workbook = writer.getWorkbook();
CreationHelper helper = workbook.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = helper.createClientAnchor();
anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex() + 1);
anchor.setRow1(row.getRowNum());
anchor.setRow2(row.getRowNum() + 1);
Comment comment = drawing.createCellComment(anchor);
comment.setString(helper.createRichTextString("这是批注内容"));
cell.setCellComment(comment);