java 复杂excel表头_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;

}

}