这里需要注意poi的版本问题
版本不同,代码不同,不过大同小异
XSSFWorkbook和HSSFWorkbook 的样式上也有一定的区别
先上两个常用的POI的pom依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
<!-- POI处理Excle开始-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
//创建excel
XSSFWorkbook backBook = new XSSFWorkbook();
// 创建sheet
Sheet bSheet = backBook.createSheet("故障登记表");
// 创建样式
CellStyle cellStyle = backBook.createCellStyle();
//设置一行的行高
bRow.setHeight((short) 1600);
//居中
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//垂直居中 cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//自动换行
cellStyle.setWrapText(true)
cellStyle.setBorderBottom((short) 1); //下边框
cellStyle.setBorderLeft((short) 1); //左边框
cellStyle.setBorderRight((short) 1); //右边框
cellStyle.setBorderTop((short) 1); //上边框
//单元格颜色 (前景颜色、填充图案要用时设置)
//设置前景颜色
cellColorStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
//设置立体前景
cellColorStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(120, 10, 12)));
//旧版本写法(设置单元格背景色)
cellColorStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
cellColorStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
//cellColorStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(120, 10, 12)));
cellColorStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellColorStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 创建字体样式
Font font = backBook.createFont();
//字体加粗
font.setBoldweight((short) 80);
//样式设置字体
cellStyle.setFont(font);
//字体大小
//fontSize.setFontHeightInPoints((short) 12);//设置excel数据字体大小
//单元格合并
bSheet.addMergedRegion(new CellRangeAddress(开始行, 结束行, 开始列, 结束列));
//合并单元格(并将合并后的单元格也带有边框效果)
mergeCellAddBorder(bSheet,backBook,0,0,0,7);
public void mergeCellAddBorder(Sheet bSheet, XSSFWorkbook backBook, int firstRow, int lastRow, int firstCol, int lastCol){
CellRangeAddress cra =new CellRangeAddress(firstRow,lastRow, firstCol, lastCol); // 起始行, 终止行, 起始列, 终止列
bSheet.addMergedRegion(cra);
RegionUtil.setBorderBottom(1, cra, bSheet,backBook); // 下边框
RegionUtil.setBorderLeft(1, cra,bSheet,backBook); // 左边框
RegionUtil.setBorderRight(1, cra, bSheet,backBook); // 有边框
RegionUtil.setBorderTop(1, cra, bSheet,backBook); // 上边框
}
//表头一般写法
//字体大小
Font fontSize = backBook.createFont();
fontSize.setFontHeightInPoints((short) 12);//设置excel数据字体大小
//表头 第0行内容
String warningString = msg;
XSSFRichTextString richString = new XSSFRichTextString(warningString);
richString.applyFont(fontSize);
Cell cell = null;
Row fRow = bSheet.createRow(0);
cell = fRow.createCell(0);
cell.setCellValue(richString);
bSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
fRow.setHeight((short) 1000);
cell.setCellStyle(cellStyleCenter);