1、maven依赖
<dependency>
<groupId>org.apache.poigroupId>
<artifactId>poi-ooxml-schemasartifactId>
<version>4.1.2version>
dependency>
<dependency>
<groupId>org.apache.poigroupId>
<artifactId>poi-ooxmlartifactId>
<version>4.1.2version>
dependency>
<dependency>
<groupId>org.apache.poigroupId>
<artifactId>poiartifactId>
<version>4.1.2version>
dependency>
2、数据集合类
public class Demo1 {
private int id;
private String name;
private int age;
private List 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 getDemo2() {
return demo2;
}
public void setDemo2(List 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
* @throws IOException
*/
public static void outExcel(
Map fatTitle,
List sonTitle,
Map sonTitleTow,
List
5、测试导出
List demo1List = new ArrayList<>();
//父类标题
Map fatTitle = new HashMap<>();
//子类一级标题
List sonTitle = new ArrayList<>();
//子类二级标题
Map sonTitleTow = new HashMap<>();
//数据行
List> 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 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 map = new HashMap<>();
demo1.getDemo2().forEach(demo2 -> {
sonTitle.remove(demo2.getName());
sonTitle.add(demo2.getName());
});
});
demo1List.forEach(demo1 -> {
Map map = new HashMap<>();
map.put("name", demo1.getName());
map.put("age", demo1.getAge());
List porList = new ArrayList<>();
for (String s : sonTitle) {
Map 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 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();
}
}