支持复杂表头,具体使用见代码(有示例)
POM poi 4.0.1
<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>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
代码如下,
package com.liindata.cydsj.cygl.connect.exporttable;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class ExportExcel {
List<ExcelHeaderVO> headerList;
private HSSFWorkbook hssfWorkbook;
private int _row;
private int _col;
ExportExcel(){
this.hssfWorkbook = new HSSFWorkbook();
this.headerList = new ArrayList<>();
_row = 0;
_col = 0;
}
// 示例 示例 示例 示例 示例 示例 示例 示例 示例 示例
// public static void main(String[] args) {
// ExportExcel ex = new ExportExcel();
// ExcelHeaderVO excelHeaderVO = new ExcelHeaderVO("p","测试", "1" ,"2");
ex.addHeaders(new ExcelHeaderVO("p","测试", "1" ,"2"));
// excelHeaderVO.addChilds(new ExcelHeaderVO("p_1","测试-1", "1" ,"1"));
// excelHeaderVO.addChilds(new ExcelHeaderVO("p_2","测试-2", "1" ,"1"));
// ex.addHeaders(excelHeaderVO);
// ex.addHeaders(new ExcelHeaderVO("p1","测试1", "2" ,"1"));
// ex.addHeaders(new ExcelHeaderVO("p2","测试2", "2" ,"1"));
// ex.addHeaders(new ExcelHeaderVO("p3","测试3", "2" ,"1"));
// ex.addHeaders(new ExcelHeaderVO("p4","测试4", "2" ,"1"));
// ex.addHeaders(new ExcelHeaderVO("p5","测试5", "2" ,"1"));
// List<Map<String, Object>> l = new ArrayList<>();
// for (int i = 0; i < 10; i++) {
// Map m = new HashMap<>();
// m.put("p_1", String.format("%.2f", Math.random()* 1000));
// m.put("p_2", String.format("%.2f", Math.random()* 1000));
// m.put("p1", String.format("%.2f", Math.random()* 1000));
// m.put("p2", String.format("%.2f", Math.random()* 1000));
// m.put("p3", String.format("%.2f", Math.random()* 1000));
// m.put("p4", String.format("%.2f", Math.random()* 1000));
// m.put("p5", String.format("%.2f", Math.random()* 1000));
// l.add(m);
// }
//
// HSSFWorkbook hk = ex.createExcel("index",l);
// try {
// //获取桌面
// FileSystemView fsv = FileSystemView.getFileSystemView();
// File file1 = fsv.getHomeDirectory();
//
// String string = UUID.randomUUID().toString();
// String str1 = string.substring(0, 7);
//
// //生成文件
// File file2 = new File(file1.getPath() + File.separator + "学生信息表" + str1 + ".xls");
// FileOutputStream file = new FileOutputStream(file2);
// hk.write(file);
// file.close();
// } catch (FileNotFoundException e) {
// e.printStackTrace();
// } catch (IOException e) {
// e.printStackTrace();
// }
// }
void addHeaders(ExcelHeaderVO ExcelHeader){
this.headerList.add(ExcelHeader);
}
HSSFWorkbook createExcel(String excelName, List<Map<String, Object>> list){
HSSFSheet sheet = this.hssfWorkbook.createSheet(excelName);
if(this.headerList.size() == 0) return hssfWorkbook;
createHeade(sheet); // 生成表头
setSource(sheet,list);
return this.hssfWorkbook;
}
void setSource(HSSFSheet sheet,List<Map<String, Object>> list){
// 添加数据
int n = getExcelHeadeLeve(this.headerList)-1;
List<ExcelHeaderVO> l = createHList().get(n);
for (int i = 0; i < list.size(); i++) {
HSSFRow row = sheet.createRow(_row);
_col = 0;
for (int j = 0; j < l.size(); j++) {
HSSFCell cell = row.createCell(_col);
String prop = l.get(j).getProp();
String M =(String) list.get(i).get(prop);
cell.setCellValue(M);
_col++;
}
_row++;
}
}
// 创建表头
void createHeade(HSSFSheet sheet){
List<List<ExcelHeaderVO>> list = this.createHList();
for (int i = 0; i < list.size(); i++) {
HSSFRow row = sheet.createRow(_row);
_col = 0;
for (int j = 0; j < list.get(i).size(); j++) {
ExcelHeaderVO m = list.get(i).get(j);
for (int k = 0; k < Integer.parseInt(m.getCol()); k++) {
HSSFCell cell = row.createCell(_col);
if(k == 0){
cell.setCellValue(m.getLabel());
}
_col++;
}
if(Integer.parseInt(m.getCol()) > 1){
sheet.addMergedRegion(new CellRangeAddress(_row,_row,_col-Integer.parseInt(m.getCol()),_col-1));
}
}
_row++;
}
}
// 计算表头总行数
int getExcelHeadeLeve(List<ExcelHeaderVO> list){
int n = 0;
for (ExcelHeaderVO map: list){
int m = 0;
m += Integer.parseInt(map.getRow());
if(map.getChilds().size() > 0){
this.getExcelHeadeLeve(map.getChilds());
}
if(n<m){
n=m;
}
}
return n;
}
// 组装数据
List<List<ExcelHeaderVO>> createHList(){
int level = this.getExcelHeadeLeve(this.headerList);
List<List<ExcelHeaderVO>> list = new ArrayList<>();
for (int i = 0; i < level; i++) {
List<ExcelHeaderVO> list1 = new ArrayList<>();
list.add(list1);
}
for (int i = 0; i < this.headerList.size(); i++) {
int n = 0;
ExcelHeaderVO map = this.headerList.get(i);
createHList_add(map, list, n);
}
return list;
}
void createHList_add(ExcelHeaderVO map, List<List<ExcelHeaderVO>> Alist, int n){
Alist.get(n).add(map); // 第一行的数据
if(map.getChilds().size() > 0){
n++;
for (int i = 0; i < map.getChilds().size(); i++) {
createHList_add(map.getChilds().get(i), Alist, n);
}
}else {
if(Integer.parseInt(map.getCol()) == 1 && Integer.parseInt(map.getRow()) > 1){
for (int i = 1; i < Integer.parseInt(map.getRow()); i++) {
Alist.get(i).add(map);
}
}
}
}
}
class ExcelHeaderVO {
String prop; // 列对应数据字段名
String label; // 列名
String row; // 占的行数
String col; // 需要合并的列数
List<ExcelHeaderVO> childs;
ExcelHeaderVO(String Prop, String Label, String Row, String Col){
this.childs = new ArrayList<>();
setProp(Prop);
setLabel(Label);
setRow(Row);
setCol(Col);
}
public void setChilds(List<ExcelHeaderVO> childs) {
this.childs = childs;
}
public void addChilds(ExcelHeaderVO exce) {
this.childs.add(exce);
}
public List<ExcelHeaderVO> getChilds() {
return childs;
}
public String getCol() {
return col;
}
public String getLabel() {
return label;
}
public String getProp() {
return prop;
}
public String getRow() {
return row;
}
public void setCol(String col) {
this.col = col;
}
public void setLabel(String label) {
this.label = label;
}
public void setProp(String prop) {
this.prop = prop;
}
public void setRow(String row) {
this.row = row;
}
}