excel导出多重表头utils_java excel复杂表头和复杂数据导出
懒得写废话了。。。
package com.ruoyi.common.utils.poi;
import com.ruoyi.framework.config.RuoYiConfig;
import com.ruoyi.framework.web.domain.AjaxResult;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.UUID;
public class DocumentUtil {
private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
private String sheetName;
private Workbook wb;
private Sheet sheet;
private List list;
private List columns;
private List fields;
private int maxDepth;
public Class clazz;
public DocumentUtil(Class clazz)
{
this.clazz = clazz;
}
public void init(List list, List columns, String sheetName)
{
if (list == null)
{
list = new ArrayList();
}
this.list = list;
this.fields = new ArrayList<>();
this.columns = columns;
this.sheetName = sheetName;
this.wb = new SXSSFWorkbook(500);
}
public AjaxResult exportExcel(List list, List columns, String sheetName)
{
this.init(list, columns, sheetName);
return exportExcel();
}
private AjaxResult exportExcel() {
OutputStream out = null;
try {
sheet = wb.createSheet(sheetName);
generateHeader(this.columns);
this.fields.sort((a, b) -> b.getFirstCol() - a.getFirstCol());
generateData(this.list,maxDepth + 1);
String filename = encodingFilename(sheetName);
out = new FileOutputStream(getAbsoluteFile(filename));
wb.write(out);
return AjaxResult.success(filename);
}
catch (Exception ex) {
System.out.print(ex.getMessage());
}
return null;
}
public String encodingFilename(String filename)
{
filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
return filename;
}
public String getAbsoluteFile(String filename)
{
String downloadPath = RuoYiConfig.getDownloadPath() + filename;
File desc = new File(downloadPath);
if (!desc.getParentFile().exists())
{
desc.getParentFile().mkdirs();
}
return downloadPath;
}
private void generateHeader(List columns) {
maxDepth = getMaxDepth(this.columns, 0);
generateHeader(columns, null, maxDepth);
}
private void generateHeader(List columns, Column parent, int maxDepth) {
int colIndex = 0, rowIndex = 0;
if (parent != null) {
colIndex = parent.getFirstCol();
rowIndex = parent.getLastRow() + 1;
}
for (Column column : columns) {
Row row = sheet.getRow(rowIndex);
if (row == null) {
row = sheet.createRow(rowIndex);
}
column.setFirstRow(rowIndex);
column.setLastRow(rowIndex);
column.setFirstCol(colIndex);
column.setLastCol(colIndex);
if (column.getChildrens() != null && column.getChildrens().size() > 0) {
column.setLastCol(colIndex + column.getChildrens().size() - 1);
generateHeader(column.getChildrens(), column, maxDepth);
} else {
if (column.getWidth() > 0) {
this.sheet.setColumnWidth(colIndex, column.getWidth() * 256);
}
this.fields.add(column);
column.setLastRow(maxDepth);
}
if (parent != null && column.getLastCol() > parent.getLastCol()) {
parent.setLastCol(column.getLastCol());
}
if (column.getLastRow() > column.getFirstRow() || column.getLastCol() > column.getFirstCol()) {
sheet.addMergedRegion(new CellRangeAddress(column.getFirstRow(), column.getLastRow(), column.getFirstCol(), column.getLastCol()));
}
Cell cell = row.createCell(column.getFirstCol());
cell.setCellValue(column.getName());
colIndex = column.getLastCol() + 1;
}
}
private int generateData(List list, int firstRow) {
int lastRow = firstRow;
int totalRow = 0;
for (T item : list) {
Row row = sheet.getRow(lastRow);
if (row == null) {
row = sheet.createRow(lastRow);
}
if (item.getChildrens() != null && item.getChildrens().size() > 0) {
int total = generateData(item.getChildrens(), lastRow);
lastRow += total - 1;
totalRow += total;
} else {
totalRow++;
}
Class cls = item.getClass();
for (Field field : cls.getDeclaredFields()) {
Column column = this.fields.stream().filter(p -> p.getField().equals(field.getName())).findFirst().get();
try {
field.setAccessible(true);
Object obj = field.get(item);
String value = obj == null ? "" : obj.toString();
Cell cell = row.createCell(column.getFirstCol());
cell.setCellValue(value);
if (lastRow > firstRow) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, column.getFirstCol(), column.getLastCol()));
}
} catch (Exception ex) {
System.out.print(ex.getMessage());
}
}
lastRow++;
firstRow = lastRow;
}
return totalRow;
}
private > int getMaxDepth(List list, int depth) {
int maxDepth = 0;
for (TreeNode item : list)
{
item.setDepth(depth);
if (item.getChildrens() != null)
{
int res = getMaxDepth(item.getChildrens(), depth + 1);
if (res > maxDepth) maxDepth = res;
}
if (depth > maxDepth) maxDepth = depth;
}
return maxDepth;
}
}
用到的实体类：
package com.ruoyi.common.utils.poi;
import java.util.List;
public abstract class TreeNode {
private int depth;
private List childrens;
public int getDepth() {
return depth;
}
public void setDepth(int depth) {
this.depth = depth;
}
public List getChildrens() {
return childrens;
}
public void setChildrens(List childrens) {
this.childrens = childrens;
}
}
package com.ruoyi.common.utils.poi;
import java.util.List;
public class Column extends TreeNode {
public Column(String name, String field) {
this.name = name;
this.field = field;
}
public Column(String name, List childrens) {
this.name = name;
this.setChildrens(childrens);
}
private String field;
private String name;
private int width;
private int firstRow;
private int lastRow;
private int firstCol;
private int lastCol;
public String getField() {
return field;
}
public void setField(String field) {
this.field = field;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getWidth() {
return width;
}
public void setWidth(int width) {
this.width = width;
}
public int getFirstRow() {
return firstRow;
}
public void setFirstRow(int firstRow) {
this.firstRow = firstRow;
}
public int getLastRow() {
return lastRow;
}
public void setLastRow(int lastRow) {
this.lastRow = lastRow;
}
public int getFirstCol() {
return firstCol;
}
public void setFirstCol(int firstCol) {
this.firstCol = firstCol;
}
public int getLastCol() {
return lastCol;
}
public void setLastCol(int lastCol) {
this.lastCol = lastCol;
}
}
标签：java,String,column,excel,表头,int,private,import,public
来源： https://www.cnblogs.com/cxyzj/p/13029433.html