基本介绍
样式案例
POI语法
poi依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.8</version>
</dependency>
poi导出基本语法
//创建一个工作簿 就是excel
XSSFWorkbook workbook = new XSSFWorkbook();
//创建工作页 一个工作簿可以创建多个sheet页
XSSFSheet sheet = workbook.createSheet("sheet");
//基于一个sheet创建行 参数为下标 从0开始
XSSFRow row = sheet.createRow(0);
//基于一行数据创建单元格 参数为下表 从0开始
XSSFCell cell = row.createCell(0);
//创建文件流
FileOutputStream file=new FileOutputStream("D:\\table.xlsx");
//写入
workbook.write(file);
file.flush();
//释放资源
file.close();
workbook.close();
poi导出基本样式
//创建一个单元格样式 可以创建多个
XSSFCellStyle borderStyle = workbook.createCellStyle();
//细黑框 字体居中对齐
borderStyle.setBorderTop(BorderStyle.THIN);
borderStyle.setBorderBottom(BorderStyle.THIN);
borderStyle.setBorderLeft(BorderStyle.THIN);
borderStyle.setBorderRight(BorderStyle.THIN);
borderStyle.setAlignment(HorizontalAlignment.CENTER);
//设置蓝底背景
XSSFCellStyle blueStyle = workbook.createCellStyle();
blueStyle.cloneStyleFrom(borderStyle);
blueStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index);
blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
具体样式根据自己需求更换，常见设置边框和背景。背景的颜色属性可以参考博客：
代码
package ms.service;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import javax.swing.filechooser.FileSystemView;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* @ClassName PoiTest
* @Description poi测试
* @Author ms
* @Date 2022/8/18
* @Version 1.0
*/
public class PoiTest {
public static void main(String[] args) throws IOException {
//创建一个工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//样式 细框居中
XSSFCellStyle borderStyle = workbook.createCellStyle();
borderStyle.setBorderTop(BorderStyle.THIN);
borderStyle.setBorderBottom(BorderStyle.THIN);
borderStyle.setBorderLeft(BorderStyle.THIN);
borderStyle.setBorderRight(BorderStyle.THIN);
borderStyle.setAlignment(HorizontalAlignment.CENTER);
//样式 细框居中 蓝底
XSSFCellStyle blueStyle = workbook.createCellStyle();
blueStyle.cloneStyleFrom(borderStyle);
blueStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.index);
blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//样式 细框左对齐 蓝底
XSSFCellStyle subjectStyle = workbook.createCellStyle();
subjectStyle.cloneStyleFrom(blueStyle);
subjectStyle.setAlignment(HorizontalAlignment.LEFT);
//样式 细框右对齐 灰底
XSSFCellStyle dataStyle = workbook.createCellStyle();
dataStyle.cloneStyleFrom(borderStyle);
dataStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
dataStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
dataStyle.setAlignment(HorizontalAlignment.RIGHT);
//样式map 方便对不同数据设值不同样式
Map<String, XSSFCellStyle> styleMap = new HashMap<>(4);
styleMap.put("normal", borderStyle);
styleMap.put("blue", blueStyle);
styleMap.put("subject", subjectStyle);
styleMap.put("data", dataStyle);
//创建一个sheet页 多个sheet可循环创建处理
XSSFSheet sheet = workbook.createSheet("模板");
//处理表头
handleHead(sheet, styleMap);
//处理数据
handleData(sheet, styleMap);
//合并单元格 下表从0开始 开始行数 结束行数 开始列数 结束列数
sheet.addMergedRegion(new CellRangeAddress(3, 4, 0, 0));
sheet.addMergedRegion(new CellRangeAddress(3, 3, 1, 5));
sheet.addMergedRegion(new CellRangeAddress(3, 3, 6, 10));
for (int i = 0; i < 11; ++i) {
//自动调整列宽（一般不太建议直接使用,展示不全）
sheet.autoSizeColumn(i);
//设置列宽为自动列宽的1.8倍
sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 2);
}
//获取桌面路径写入流返回 实际业务换成response.getOutputStream()
FileSystemView fsv = FileSystemView.getFileSystemView();
String desktop = fsv.getHomeDirectory().getPath();
String filePath = desktop + "/twoTable" + System.currentTimeMillis() + ".xlsx";
File file = new File(filePath);
OutputStream outputStream = new FileOutputStream(file);
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
/**
* 处理表头及表头数据
*
* @param sheet
* @param styleMap
*/
private static void handleHead(XSSFSheet sheet, Map<String, XSSFCellStyle> styleMap) {
//创建第一行
XSSFRow headRow = sheet.createRow(0);
List<String> headTitleList = Arrays.asList("生活统计名称", "生活统计编码", "生活开始时间");
for (int i = 0; i < 3; ++i) {
//创建该行单元格
XSSFCell cell = headRow.createCell(i);
//设值单元格值 下标从0开始
cell.setCellValue(headTitleList.get(i));
//设值单元格样式
cell.setCellStyle(styleMap.get("blue"));
}
XSSFRow headDataRow = sheet.createRow(1);
for (int i = 0; i < 3; ++i) {
XSSFCell cell = headDataRow.createCell(i);
switch (i) {
case 0:
cell.setCellValue("名称" + i);
break;
case 1:
cell.setCellValue("编号" + i);
break;
case 2:
cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
break;
default:
break;
}
cell.setCellStyle(styleMap.get("normal"));
}
XSSFRow headRow3 = sheet.createRow(3);
XSSFRow headRow4 = sheet.createRow(4);
//处理第三行表头
headRow3.createCell(0).setCellValue("科目");
headRow3.getCell(0).setCellStyle(styleMap.get("blue"));
for (int i = 0; i < 5; i++) {
XSSFCell cell = headRow3.createCell(i + 1);
cell.setCellStyle(styleMap.get("blue"));
cell.setCellValue("旧总结");
}
for (int i = 0; i < 5; i++) {
XSSFCell cell = headRow3.createCell(i + 6);
cell.setCellStyle(styleMap.get("blue"));
cell.setCellValue("新总结");
}
//处理第4行表头 对要合并的单元格内容重复设值
headRow4.createCell(0).setCellValue("科目");
headRow4.getCell(0).setCellStyle(styleMap.get("blue"));
for (int i = 1; i <= 5; i++) {
XSSFCell cell = headRow4.createCell(i);
cell.setCellStyle(styleMap.get("blue"));
cell.setCellValue("第" + i + "年");
}
for (int i = 1; i <= 5; i++) {
XSSFCell cell = headRow4.createCell(i + 5);
cell.setCellStyle(styleMap.get("blue"));
cell.setCellValue("第" + i + "年");
}
}
/**
* 处理数据
*
* @param sheet
* @param styleMap
*/
private static void handleData(XSSFSheet sheet, Map<String, XSSFCellStyle> styleMap) {
//具体数据填充看业务 思路就有几行数据创建几行 然后将每条数据值填充单元格没列
List<Map<String, String>> dataList = mockDate();
int lastRowNum = sheet.getLastRowNum();
for (int i = 1; i <= dataList.size(); i++) {
XSSFRow dataRow = sheet.createRow(lastRowNum + i);
//第一列表头
XSSFCell subjectCell = dataRow.createCell(0);
subjectCell.setCellStyle(styleMap.get("subject"));
Map<String, String> map = dataList.get(i - 1);
//随便填充 具体数据看业务处理
map.forEach((k, v) -> {
String value = null;
switch (v) {
case "1":
value = k;
break;
case "2":
StringBuilder stringBuilder1 = new StringBuilder(" ");
value = stringBuilder1.append(k).toString();
break;
case "3":
StringBuilder stringBuilder2 = new StringBuilder(" ");
value = stringBuilder2.append(k).toString();
break;
default:
break;
}
subjectCell.setCellValue(value);
for (int j = 1; j <= 10; j++) {
XSSFCell cell = dataRow.createCell(j);
cell.setCellStyle(styleMap.get("data"));
cell.setCellValue(j + 520);
}
});
}
}
/**
* mock数据
*
* @return
*/
private static List<Map<String, String>> mockDate() {
Map<String, String> map1 = new HashMap<>(1);
map1.put("总支出", "1");
Map<String, String> map2 = new HashMap<>(1);
map2.put("旅游", "2");
Map<String, String> map3 = new HashMap<>(1);
map3.put("北京旅游", "3");
Map<String, String> map4 = new HashMap<>(1);
map4.put("总收入", "1");
Map<String, String> map5 = new HashMap<>(1);
map5.put("卡路里", "2");
List<Map<String, String>> codeList = Arrays.asList(map1, map2, map3, map4, map5);
return codeList;
}
}
总结
注意poi版本号和poi-ooxml版本号需要一致否则会运行报错
整体思路就是组装数据，根据下标去渲染目标单元格设置为表头样式（下标从0开始）
之前一直用EeasyExcel导出，没用到复杂场景，EasyExcel，也支持头和数据分离，配置内容样式策略，但是网上一直没找到合适案例，之前的思路是在导出监听器拿取到目标行设置样式，还没来得及实现，发现poi也挺好用，感兴趣的话可以也研究下EasyExcel