一、 简单的demo
@SneakyThrows
public static void main(String[] args) {
String fileName = URLEncoder.encode("poi生成Excel".concat(".xlsx"), GlobalConstant.Sys.UTF8);
// 获取当前项目下的文件
// InputStream siTemplate = this.getClass().getClassLoader().getResourceAsStream("poi生成Excel.xlsx");
// 读模板，填充数据
// XSSFWorkbook wb = new XSSFWorkbook(siTemplate);
XSSFWorkbook wb = new XSSFWorkbook();
// 样式
XSSFCellStyle cellStyle = wb.createCellStyle();
// 边框
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
// 单元格内容上下居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 自动换行
cellStyle.setWrapText(true);
// 字体样式
XSSFFont font = wb.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font);
// 创建sheet
XSSFSheet sheet = wb.createSheet("你好，sheet！");
// 创建列宽 252*列宽+323
sheet.setColumnWidth(0, 5081);
// 创建单元格
XSSFRow row = sheet.createRow(0);
row.setHeight(new Short("500")); // 设置高度后，自动换行失效
XSSFCell cell = row.createCell(1);
cell.setCellStyle(cellStyle);
cell.setCellValue("利用poi生成简单Excel， 很高兴。 \n 只有更加努力，才能看起毫不费力");
FileOutputStream out = new FileOutputStream("D:\\"+fileName);
wb.write(out);
out.close();
wb.close();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
生成效果图：
二、样式
2、单元格
2.1 合并单元格
方案一：
//指定合并开始行、合并结束行 合并开始列、合并结束列
CellRangeAddress rangeAddress = new CellRangeAddress(rowNum1, rowNum2, firstCol, lastCol);
//添加要合并地址到表格
sheet.addMergedRegion(rangeAddress);
1
2
3
4
方案二：
## 必须要两个及两个以上的单元格才能合并
String region1 = "B1:C2";
CellRangeAddress region = CellRangeAddress.valueOf(region1);
sheet.addMergedRegion(region);
1
2
3
4
2.2 单元格内容水平、居中
## poi提供了CellUtil
// 设置单元格内容水平垂直居中
CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
1
2
3
4
5
2.3 单元格 边框
具体可查看RegionUtil
作用：可以使合并单元格的线条补齐； BorderStyle线条粗细(类型)可选
## 同一列，合并不同行时只能显示第一行的边框样式，其余不能显示。
## 可以参考下样式丢失问题 https://www.cnblogs.com/mr-wuxiansheng/p/7911521.html
// 合并单元格左边框样式
RegionUtil.setBorderLeft(BorderStyle.MEDIUM, region, sheet);
// 合并单元格上边框样式
RegionUtil.setBorderTop(BorderStyle.MEDIUM, region, sheet);
// 合并单元格右边框样式
RegionUtil.setBorderRight(BorderStyle.MEDIUM, region, sheet);
// 合并单元格下边框样式
RegionUtil.setBorderBottom(BorderStyle.MEDIUM, region, sheet);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2.4 克隆样式
方法1：
wb.cloneSheet();
1
方法2：
public static void copySheet(XSSFWorkbook wb,XSSFSheet fromSheet, XSSFSheet toSheet, int readRow) {
mergeSheetAllRegion(fromSheet, toSheet);
//设置列宽
for(int i=0;i<=fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum();i++){
toSheet.setColumnWidth(i,fromSheet.getColumnWidth(i));
}
// for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext();) {
// XSSFRow oldRow = (XSSFRow) rowIt.next();
// XSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
// copyRow(wb,oldRow,newRow);
// }
// 注意模板是否有很多行，复制时间会增大。 可以自己设定行，列
for (int i=0; i<readRow; i++){
XSSFRow oldRow = (XSSFRow) fromSheet.getRow(i);
XSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
copyRow(wb,oldRow,newRow);
}
}
public static void copyCellStyle(XSSFCellStyle fromStyle, XSSFCellStyle toStyle) {
toStyle.cloneStyleFrom(fromStyle);//此一行代码搞定
}
public static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet toSheet) {//合并单元格
int num = fromSheet.getNumMergedRegions();
CellRangeAddress cellR = null;
for (int i = 0; i < num; i++) {
cellR = fromSheet.getMergedRegion(i);
toSheet.addMergedRegion(cellR);
}
}
public static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {
XSSFCellStyle newstyle=wb.createCellStyle();
copyCellStyle(fromCell.getCellStyle(), newstyle);
//toCell.setEncoding(fromCell.getEncoding());
//样式
toCell.setCellStyle(newstyle);
if (fromCell.getCellComment() != null) {
toCell.setCellComment(fromCell.getCellComment());
}
// 不同数据类型处理
CellType fromCellType = fromCell.getCellType();
toCell.setCellType(fromCellType);
if (fromCellType == CellType.NUMERIC) {
toCell.setCellValue(fromCell.getDateCellValue());
} else if (fromCellType == CellType.STRING) {
toCell.setCellValue(fromCell.getRichStringCellValue());
} else if (fromCellType == CellType.BLANK) {
// nothing21
} else if (fromCellType == CellType.BOOLEAN) {
toCell.setCellValue(fromCell.getBooleanCellValue());
} else if (fromCellType == CellType.ERROR) {
toCell.setCellErrorValue(fromCell.getErrorCellValue());
} else if (fromCellType == CellType.FORMULA) {
toCell.setCellFormula(fromCell.getCellFormula());
} else { // nothing29
}
}
public static void copyRow(XSSFWorkbook wb, XSSFRow oldRow, XSSFRow toRow){
toRow.setHeight(oldRow.getHeight());
for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext();) {
XSSFCell tmpCell = (XSSFCell) cellIt.next();
XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
copyCell(wb,tmpCell, newCell);
}
}
