POI动态导出复杂表头

1、maven依赖

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml-schemas</artifactId>

</dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

</dependency>

<groupId>org.apache.poi</groupId>

</dependency>

2、数据集合类

public class Demo1 {

private int id;

private String name;

private int age;

private List<Demo2> demo2; // List<?> 也可以

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public int getAge() {

return age;

}

public void setAge(int age) {

this.age = age;

}

public List<Demo2> getDemo2() {

return demo2;

}

public void setDemo2(List<Demo2> demo2) {

this.demo2 = demo2;

}

3、表格样式工具类

public class SheetStyle {

/**

* 水平居中、垂直居中

* 字体：宋体

* 字体大小：16号

* 加粗

* @param workbook

* @return

public static CellStyle getStyle(XSSFWorkbook workbook) {

CellStyle cellstyle=workbook.createCellStyle();

cellstyle.setAlignment(HorizontalAlignment.CENTER);//水平居中

cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

Font font=workbook.createFont();//字体

font.setFontName("宋体");//字体

font.setFontHeightInPoints((short)18);//字号

font.setBold(true);//加粗

cellstyle.setFont(font);

setBorderStyle(cellstyle);

return cellstyle;

}

/**

* 获取默认的cell表格样式，加边框，水平居中，垂直居中

* @param workbook

* @return

public static CellStyle getTextCellStyle(XSSFWorkbook workbook) {

CellStyle style=workbook.createCellStyle();

style.setAlignment(HorizontalAlignment.CENTER);//水平居中

style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

setBorderStyle(style);

return style;

}

/**

* 获取默认的cell表格样式，加边框，水平居中，垂直居中

* @param workbook

* @return

public static CellStyle getNumCellStyle(XSSFWorkbook workbook) {

CellStyle style=workbook.createCellStyle();

XSSFDataFormat df = new XSSFWorkbook().createDataFormat();

style.setAlignment(HorizontalAlignment.RIGHT);//水平居中

style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

style.setDataFormat(df.getFormat("0.00"));

Font font=workbook.createFont();//字体

font.setFontName("宋体");//字体

font.setFontHeightInPoints((short)11);//字号

style.setFont(font);

setBorderStyle(style);

return style;

}

/**

* 边框样式

* @param style

public static void setBorderStyle(CellStyle style) {

style.setBorderBottom(BorderStyle.THIN); //下边框

style.setBorderLeft(BorderStyle.THIN);//左边框

style.setBorderTop(BorderStyle.THIN);//上边框

style.setBorderRight(BorderStyle.THIN);//右边框

}

/**

* 奇数行

* 背景颜色为黄色

* @param style

public static void setCellStyleYellow(CellStyle style) {

style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());

style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

}

/**

* 偶数行

* 背景颜色为LIME

* @param style

public static void setCellStyleLime(CellStyle style) {

style.setFillForegroundColor(IndexedColors.LIME.getIndex());

style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

}

/**

* 字体设置红色

* @param workbook

* @param style

public static void setFontRedColor(XSSFWorkbook workbook,CellStyle style) {

Font font=workbook.createFont();//字体

font.setColor(IndexedColors.RED.getIndex());

style.setFont(font);

}

4、表格生成工具类

public class ExcelExport {

/**

* 一对多模式子类行转列导出数据（指定子类某一项作为子类一级标题）子项为数值类型带汇总

* @param fatTitle 父类显示列

* @param sonTitle 子类一级标题

* @param sonTitleTow 子类二级标题

* @param dataList 数据行数据格式 a,b,c,List<d>

* @throws IOException

public static void outExcel(

Map<String,Object> fatTitle,

List<String> sonTitle,

Map<String,Object> sonTitleTow,

List<Map<String,Object>> dataList

) throws IOException {

//创建工作薄对象

XSSFWorkbook workbook = new XSSFWorkbook();//这里也可以设置sheet的Name

//创建工作表对象

XSSFSheet sheet = workbook.createSheet();

//创建单元格空对象

XSSFCell cell = null;

//创建合并空对象

CellRangeAddress region = null;

//设置标题

XSSFRow title = sheet.createRow(0);//设置第一行，从零开始

//设置标题行高

title.setHeightInPoints(40);

//统计合并列(父类属性+子类显示属性*子类标题)

int titleColumn = fatTitle.size()+(sonTitle.size()+1)*sonTitleTow.size()-1;

for (int i = 0; i <= titleColumn; i++) {

cell = title.createCell(i);

cell.setCellValue("测试数据标题");

cell.setCellStyle(SheetStyle.getStyle(workbook));

}

//合并单元格

region = new CellRangeAddress(0, 0, 0, titleColumn);

sheet.addMergedRegion(region);

sheet.setColumnWidth(0,3000);

//先创建父类标题

XSSFRow row = sheet.createRow(1);//设置第一行

//设置第二行

XSSFRow row1 = sheet.createRow(2);

row.setHeightInPoints(20);//设置行高

row1.setHeightInPoints(18);//设置行高

int i = 0; // 列数

//循环添加父类标题

for (String s : fatTitle.keySet()) {

//绘制单元格

cell=row.createCell(i);

//添加数据

cell.setCellValue(fatTitle.get(s).toString());

//添加样式

cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));

//设置要合并的单元格样式

cell = row1.createCell(i);

cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));

//合并 (4个参数，分别为起始行，结束行，起始列，结束列)

region = new CellRangeAddress(1, 2, i, i++);

sheet.addMergedRegion(region);

sheet.setColumnWidth(i,3000);

}

//循环添加子类一级标题

int i_1=i; // 子类一级标题起始列

for (String s : sonTitle){

//绘制单元格

cell=row.createCell(i_1);

//添加数据

cell.setCellValue(s);

//添加样式

cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));

//设置要合并的单元格样式

cell = row.createCell(i_1+1);

cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));

//合并单元格

region = new CellRangeAddress(1, 1,i_1, i_1+sonTitleTow.size()-1);

sheet.addMergedRegion(region);

//循环添加子类二级表头

int i_2=i_1; // 子类二级标题起始列

for (String s1 : sonTitleTow.keySet()){

cell = row1.createCell(i_2);

cell.setCellValue(sonTitleTow.get(s1).toString());

cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));

sheet.setColumnWidth(i_2,2500);

i_2++;

}

i_1 = i_1+sonTitleTow.size();

}

//添加汇总标题

int i_3 = i_1;

for (String s1 : sonTitleTow.keySet()){

cell = row.createCell(i_3);

cell.setCellValue(sonTitleTow.get(s1).toString()+"汇总");

cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));

//设置要合并的单元格样式

cell = row1.createCell(i_3);

cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));

//合并单元格

region = new CellRangeAddress(1, 2,i_3, i_3++);

sheet.addMergedRegion(region);

}

//循环添加数据

for (int j = 0; j < dataList.size(); j++) {

//创建单元格对象

//设置数据第一行

XSSFRow row2 = sheet.createRow(j+3);

row2.setHeightInPoints(18);//设置行高

//添加父类数据

int dataCol = 0;

for (String s : fatTitle.keySet()) {

cell= row2.createCell(dataCol);

cell.setCellValue(dataList.get(j).get(s).toString());

cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));

dataCol++;

}

//添加子类数据

List<Map> lis = (List<Map>) dataList.get(j).get("porList");

//根据二级子标题创建汇总集合

Map<String,Object> sumMap = sonTitleTow;

for (Map map : lis) {

for (String s : sonTitle) {

//查找和标题行对应的数据

if(map.get(s)!=null){

for (String str : sonTitleTow.keySet()) {

cell = row2.createCell(dataCol);

cell.setCellValue( Double.valueOf(map.get(str).toString()));

cell.setCellStyle(SheetStyle.getNumCellStyle(workbook));

try{

sumMap.put(str,Double.valueOf(sumMap.get(str).toString())+Double.valueOf(map.get(str).toString()));

}catch (NumberFormatException e){

sumMap.put(str,Double.valueOf(map.get(str).toString()));

}

dataCol++;

}

break;

}

/**

* 添加汇总数据

for (String str : sonTitleTow.keySet()) {

cell = row2.createCell(dataCol);

cell.setCellValue(Double.valueOf(sumMap.get(str).toString()));

cell.setCellStyle(SheetStyle.getNumCellStyle(workbook));

dataCol++;

}

workbook.setSheetName(0, "测试导出");//设置sheet的Name

//文档输出

FileOutputStream out = new FileOutputStream("D:/excel/原生poi.xlsx");

workbook.write(out);

out.close();

}

5、测试导出

List<Demo1> demo1List = new ArrayList<>();

//父类标题

Map<String,Object> fatTitle = new HashMap<>();

//子类一级标题

List<String> sonTitle = new ArrayList<>();

//子类二级标题

Map<String,Object> sonTitleTow = new HashMap<>();

//数据行

List<Map<String,Object>> dataList = new ArrayList<>();

@Before

public void testBefore() {

fatTitle.put("name","姓名");

fatTitle.put("age","年龄");

sonTitleTow.put("money","金额");

sonTitleTow.put("number","数量");

for (int i = 0; i < 10; i++) {

Demo1 demo = new Demo1();

List<Demo2> lis = new ArrayList<>();

for (int j = 0; j < 10; j++) {

Demo2 demo2 = new Demo2();

demo2.setName("项目" + j);

demo2.setMoney((float) (i + j * 3.5));

demo2.setNumber((i + 1) * 15);

lis.add(demo2);

}

demo.setDemo2(lis);

demo.setId(i);

demo.setName("医院" + i);

demo.setAge(100 - i);

demo1List.add(demo);

}

demo1List.forEach(demo1 -> {

Map<String, Object> map = new HashMap<>();

demo1.getDemo2().forEach(demo2 -> {

sonTitle.remove(demo2.getName());

sonTitle.add(demo2.getName());

});

demo1List.forEach(demo1 -> {

Map<String, Object> map = new HashMap<>();

map.put("name", demo1.getName());

map.put("age", demo1.getAge());

List<Map> porList = new ArrayList<>();

for (String s : sonTitle) {

Map<String, Object> porMap = new HashMap<>();

porMap.put(s,s);

//判断是否找到的标志

boolean isFind = false;

for (Demo2 demo2 : demo1.getDemo2()) {

if (demo2.getName().equals(s)) {

porMap.put("money", demo2.getMoney());

porMap.put("number", demo2.getNumber());

isFind = true;

break;

}

//找不到给其添加0进行补位

if (!isFind) {

porMap.put("money", 0);

porMap.put("number", 0);

}

porList.add(porMap);

map.put("porList", porList);

}

dataList.add(map);

});

}

/**

* 1、创建工作薄对象

* 2、创建工作表（sheet）对象

* 3、创建行

* 4、创建单元格

* 5、填充数据

* 6、设置格式

* 7、合并单元格

* @throws IOException

@Test

public void testExcel() throws IOException {

ExcelExport.outExcel(fatTitle,sonTitle,sonTitleTow,dataList);

}

6、导出效果

7、前端点击下载时

public void dataExport(@RequestBody Map<String,Object> params, HttpServletResponse response){

XSSFWorkbook workbook = service.dataExport(params);

SXSSFWorkbook workBook = new SXSSFWorkbook(1000);//内存中最多存放一千条数据，防止内存溢出

try {

Date day=new Date();

SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

String name = params.get("title").toString()+ df.format(day);

String codedFileName = new String(name.getBytes("gbk"), "iso-8859-1");

response.setHeader("Content-Disposition", "attachment;filename=" + codedFileName + ".xlsx");

// 响应类型,编码

response.setContentType("application/octet-stream;charset=UTF-8");

// 形成输出流

OutputStream osOut = response.getOutputStream();

// 将指定的字节写入此输出流

workbook.write(osOut);

// 刷新此输出流并强制将所有缓冲的输出字节被写出

osOut.flush();

// 关闭流

osOut.close();

* dispose of temporary files backing this workbook on disk 处理在磁盘上备份此工作簿的临时文件

* SXSSF分配临时文件，您必须始终清除显式，通过调用dispose方法

workBook.dispose();

} catch (Exception e) {

e.printStackTrace();

}

POI动态导出复杂表头

推荐阅读更多精彩内容