动态生成Excel

1.构建数据

package test.com.zcm;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.util.TypeUtils;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;

/**
 * 启动类
 */
public class ExcelTest {

    public static void main(String[] args) throws IOException {
        export();
    }

    public static void export() throws IOException {
        List<EmployeeVO> list = initData();//列表数据:固定列数据+动态列数据
        TypeUtils.compatibleWithJavaBean = true;
        JSONArray datas = JSONArray.parseArray(JSON.toJSONString(list));
        WholeDataVO viewBillVO = initPublicData();//全局数据

        //头部数据重组
        List<SheetHeadVO> sheetHeadVOS = initSheet();
        LinkedHashMap<String, String> headMap = new LinkedHashMap<>();
        LinkedHashMap<String, String> dynamicHead = new LinkedHashMap<>();
        for (int i = 0; i < sheetHeadVOS.size(); i++) {
            SheetHeadVO sheetHeadVO = sheetHeadVOS.get(i);
            headMap.put(sheetHeadVO.getName(), sheetHeadVO.getViewName());
            if (i > 2) {//跟固定的表头有3列有关
                dynamicHead.put(sheetHeadVO.getName(), sheetHeadVO.getViewName());
            }
        }

        EasyExcelEntity entity = new EasyExcelEntity();
        //entity.setSheetName("员工费用报销单");
        //组装标题
        List<String> titles = new ArrayList<>();
        titles.add(viewBillVO.getTitle());
        titles.add("AA公司");
        entity.setTitle(titles);
        //组装台头
        List<String> stageHeads = new ArrayList<>();
        stageHeads.add("左边内容"+EasyExcelUtils.SPLIT_SYMBOL+"右边内容");
        entity.setStageHead(stageHeads);
        //组装表头信息
        entity.setTableHead(headMap);
        //组装数据
        entity.setTableData(datas);
        //合计
        LinkedHashMap<String, BigDecimal> costTotal = new LinkedHashMap<>();
        for (int i = 0; i < datas.size(); i++) {
            JSONObject jsonObject = datas.getJSONObject(i);
            dynamicHead.forEach((k, v) -> {
                BigDecimal cost = jsonObject.getBigDecimal(k);
                if (costTotal.get(k) == null) {
                    costTotal.put(k, cost);
                } else {
                    costTotal.put(k, costTotal.get(k).add(cost == null ? new BigDecimal(0) : cost));
                }
            });
        }
        entity.setTotalData(costTotal);
        entity.setTotalIndex(2);//跟固定的表头有3列有关


        //尾部
        List<String> bottomData = new ArrayList<>();
        bottomData.add(" " + EasyExcelUtils.SPLIT_SYMBOL + "审 核 人:李总" );
        bottomData.add(" " + EasyExcelUtils.SPLIT_SYMBOL + "审核时间:2021-10-10");
        entity.setBottomData(bottomData);

        EasyExcelUtils.autoGeneration("E://Demo.xlsx", entity);
    }

    /**
     * 列表数据-模拟从数据库获取数据
     * @return
     */
    private static List<EmployeeVO> initData(){
        EmployeeVO v1 = new EmployeeVO("1","张三","销售部","200");
        EmployeeVO v2 = new EmployeeVO("2","李四","人事部","100");
        EmployeeVO v3 = new EmployeeVO("3","王五","销售部","110");

        List<EmployeeVO> list = new ArrayList<>();
        list.add(v1);
        list.add(v2);
        list.add(v3);
        return list;
    }
    /**
     * 列表全局数据(表头,表脚注时间)-模拟从数据库获取数据
     * @return
     */
    private static WholeDataVO initPublicData(){
        return new WholeDataVO("员工费用报销表单","2021-08-08");
    }

    /**
     * 初始化表头(固定的+动态的，即从数据库查的)
     * @return
     */
    private  static List<SheetHeadVO> initSheet(){
        //固定得
        SheetHeadVO v1 = new SheetHeadVO("id","员工ID");
        SheetHeadVO v2 = new SheetHeadVO("name","员工名称");
        SheetHeadVO v3 = new SheetHeadVO("department","员工所属部门");

        //动态获取的
        SheetHeadVO v4 = new SheetHeadVO("v1","加班打车费");
        List<SheetHeadVO> list = new ArrayList<>();
        list.add(v1);
        list.add(v2);
        list.add(v3);
        list.add(v4);
        return list;
    }

}

2.excel工具类

package test.com.zcm;

import com.alibaba.excel.util.StringUtils;//easyexcel-2.2.6.jar
import com.alibaba.excel.util.WorkBookUtil;
import com.alibaba.excel.write.metadata.WriteWorkbook;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.collections4.CollectionUtils;//commons-collections4-4.1.jar
import org.apache.poi.ss.usermodel.*;//poi-3.15
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFSheet;//poi-ooxml-3.15.jar

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;

import static org.apache.poi.ss.usermodel.BorderStyle.THIN;
import static org.apache.poi.ss.usermodel.HorizontalAlignment.CENTER;

/**
 * 填充excel工具类
 */
public class EasyExcelUtils {

    public final static String SPLIT_SYMBOL = "~";

    public static Workbook autoGeneration(String path, EasyExcelEntity entity) throws IOException {
        // 文件输出位置
        OutputStream out = new FileOutputStream(path);

        WriteWorkbook writeWorkbook = new WriteWorkbook();
        WriteWorkbookHolder writeWorkbookHolder = new WriteWorkbookHolder(writeWorkbook);
        WorkBookUtil.createWorkBook(writeWorkbookHolder);
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.createSheet(entity.getSheetName() == null ? "Demo" : entity.getSheetName());

        //当前行
        Integer rowNum = 0;
        //表格顶部标题
        rowNum = setHead(entity, sheet, workbook, rowNum);
        //表头
        rowNum = tableHead(entity, sheet, workbook, rowNum);
        //表数据
        rowNum = contentData(entity, sheet, workbook, rowNum);
        //合计
        rowNum = tableTotal(entity, sheet, workbook, rowNum);

        //总合计 暂无
        rowNum = tableTotalSum(entity, sheet, workbook, rowNum);

        //顶部数据
        rowNum = setTop(entity, sheet, workbook, rowNum);

        setColumnWidth(entity, sheet);

        // 处理中文不能自动调整列宽的问题
        setSizeColumn(sheet,entity.getTableHead().size());
        workbook.write(out);
        workbook.close();

        return workbook;

    }

    //设置列宽度
    private static void setColumnWidth(EasyExcelEntity entity, Sheet sheet) {
        LinkedHashMap<String, String> tableHeads = entity.getTableHead();
        if (tableHeads == null) {
            return;
        }
        SXSSFSheet tmp = (SXSSFSheet) sheet;
        tmp.trackAllColumnsForAutoSizing();
        for (int i = 1; i <= tableHeads.size(); i++) {
            sheet.setColumnWidth(i, 5000);
        }


    }


    private static Integer setHead(EasyExcelEntity entity, Sheet sheet, Workbook workbook, Integer rowNum) {
        List<String> titles = entity.getTitle();
        //标题
        if (CollectionUtils.isNotEmpty(titles)) {
            for (int i = 0; i < titles.size(); i++) {
                Row row1 = sheet.createRow(i);
                row1.setHeight((short) 800);
                Cell cell0 = row1.createCell(0);
                cell0.setCellValue(titles.get(i));
                CellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                cellStyle.setAlignment(CENTER);
                if (i != 2) {
                    Font font = workbook.createFont();
                    font.setBold(true);
                    font.setFontHeight((short) 400);
                    cellStyle.setFont(font);
                }
                cell0.setCellStyle(cellStyle);
                sheet.addMergedRegionUnsafe(new CellRangeAddress(i, i, 0, entity.getTableHead().size() - 1));
                ++rowNum;
            }
        }
        //抬头
        if (CollectionUtils.isNotEmpty(entity.getStageHead())) {
            List<String> stageHeads = entity.getStageHead();
            for (int i = 0; i < stageHeads.size(); i++) {
                String stageHead = stageHeads.get(i);

                String[] tmps = stageHead.split(SPLIT_SYMBOL);

                Row row = sheet.createRow(rowNum);
                row.setHeight((short) 500);
                row.createCell(0).setCellValue(tmps[0]);
                if (tmps.length > 1) {
                    row.createCell(entity.getTableHead().size() - 1).setCellValue(tmps[1]);
                }
                ++rowNum;
            }

        }

        return rowNum;
    }

    private static Integer tableHead(EasyExcelEntity entity, Sheet sheet, Workbook workbook, Integer rowNum) {
        if (entity.getTableHead() == null) {
            return rowNum;
        }
        //样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(CENTER);
        cellStyle.setBorderBottom(THIN);
        cellStyle.setBorderTop(THIN);
        cellStyle.setBorderRight(THIN);
        cellStyle.setBorderLeft(THIN);


        Row row = sheet.createRow(rowNum);
        //表头
        LinkedHashMap<String, String> tableHead = entity.getTableHead();

        AtomicInteger i = new AtomicInteger();
        tableHead.forEach((k, v) -> {
            row.setHeight((short) 1200);
            Cell cell = row.createCell(i.get());
            cell.setCellStyle(cellStyle);
            cell.setCellValue(tableHead.get(k));
            i.incrementAndGet();
        });

        return ++rowNum;
    }


    private static Integer contentData(EasyExcelEntity entity, Sheet sheet, Workbook workbook, Integer rowNum) {

        if (entity.getTableData() == null) {
            return ++rowNum;
        }

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(CENTER);
        cellStyle.setBorderBottom(THIN);
        cellStyle.setBorderTop(THIN);
        cellStyle.setBorderRight(THIN);
        cellStyle.setBorderLeft(THIN);

        JSONArray tableDatas = entity.getTableData();


        for (int i = 0; i < tableDatas.size(); i++) {
            JSONObject datas = tableDatas.getJSONObject(i);
            Row row = sheet.createRow(rowNum);
            row.setHeight((short) 800);
            AtomicInteger j = new AtomicInteger();
            entity.getTableHead().forEach((k, v) -> {
                int cellNum = j.get();
                Cell cell = row.createCell(cellNum);
                cell.setCellStyle(cellStyle);
                String data = datas.getString(k);
                if (entity.getTotalIndex() != null) { //费用等与空返回0
                    if (cellNum > entity.getTotalIndex()) {
                        data = data == null ? "0" : data;
                    }
                }
                cell.setCellValue(data);
                j.incrementAndGet();
            });
            ++rowNum;
        }
        return rowNum;
    }

    private static Integer tableTotal(EasyExcelEntity entity, Sheet sheet, Workbook workbook, Integer rowNum) {

        if (entity.getTotalData() == null) {
            return rowNum;
        }

        Row row = sheet.createRow(rowNum);
        row.setHeight((short) 800);
        Cell cell0 = row.createCell(0);
        cell0.setCellValue("合计");
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(CENTER);
        cellStyle.setBorderBottom(THIN);
        cellStyle.setBorderTop(THIN);
        cellStyle.setBorderRight(THIN);
        cellStyle.setBorderLeft(THIN);

        CellRangeAddress cellAddresses = new CellRangeAddress(rowNum, rowNum, 0, entity.getTotalIndex());

        RegionUtil.setBorderBottom(1, cellAddresses, sheet);
        RegionUtil.setBorderTop(1, cellAddresses, sheet);
        RegionUtil.setBorderLeft(1, cellAddresses, sheet);
        RegionUtil.setBorderRight(1, cellAddresses, sheet);

        sheet.addMergedRegionUnsafe(cellAddresses);

        cell0.setCellStyle(cellStyle);

        Map<String, BigDecimal> totalDatas = entity.getTotalData();
        AtomicInteger index = new AtomicInteger(entity.getTotalIndex() + 1);

        entity.getTableHead().forEach((k, v) -> {
            BigDecimal cost = totalDatas.get(k);
            if (cost != null) {
                Cell cell = row.createCell(index.get());
                cell.setCellStyle(cellStyle);
                cell.setCellValue(cost.toString());
                index.incrementAndGet();
            }
        });

        return ++rowNum;
    }


    private static Integer tableTotalSum(EasyExcelEntity entity, Sheet sheet, Workbook workbook, Integer rowNum) {

        if (StringUtils.isEmpty(entity.getTotalSumData())) {
            return rowNum;
        }

        Row row = sheet.createRow(rowNum);
        row.setHeight((short) 800);
        Cell cell0 = row.createCell(0);
        cell0.setCellValue("总合计");


        CellRangeAddress cellAddresses = new CellRangeAddress(rowNum, rowNum,
                0, entity.getTableHead().size() - 3);
        sheet.addMergedRegionUnsafe(cellAddresses);

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);

        cell0.setCellStyle(cellStyle);

        RegionUtil.setBorderBottom(1, cellAddresses, sheet);
        RegionUtil.setBorderTop(1, cellAddresses, sheet);
        RegionUtil.setBorderLeft(1, cellAddresses, sheet);
        RegionUtil.setBorderRight(1, cellAddresses, sheet);

        Cell cell = row.createCell(24);

        cellAddresses = new CellRangeAddress(rowNum, rowNum,
                entity.getTotalIndex() + entity.getBillingItem() + 1,
                entity.getTableHead().size() - 1);
        sheet.addMergedRegionUnsafe(cellAddresses);

        CellStyle cellStyle1 = workbook.createCellStyle();
        cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle1.setAlignment(CENTER);
        cell.setCellStyle(cellStyle1);

        RegionUtil.setBorderBottom(1, cellAddresses, sheet);
        RegionUtil.setBorderTop(1, cellAddresses, sheet);
        RegionUtil.setBorderLeft(1, cellAddresses, sheet);
        RegionUtil.setBorderRight(1, cellAddresses, sheet);

        cell.setCellValue(entity.getTotalSumData());

        return ++rowNum;
    }

    private static Integer setTop(EasyExcelEntity entity, Sheet sheet, Workbook workbook, Integer rowNum) {

        List<String> bottomDatas = entity.getBottomData();

        if (CollectionUtils.isNotEmpty(bottomDatas)) {

            for (int i = 0; i < bottomDatas.size(); i++) {
                String bottomData = bottomDatas.get(i);
                String[] tmps = bottomData.split(SPLIT_SYMBOL);
                Row row = sheet.createRow(rowNum);
                row.setHeight((short) 500);
                row.createCell(0).setCellValue(tmps[0]);
                if (tmps.length > 1) {
                    row.createCell(entity.getTableHead().size() - 1).setCellValue(tmps[1]);
                }
                ++rowNum;
            }
        }

        return rowNum;
    }

    // 自适应宽度(中文支持)
    public static void setSizeColumn(Sheet sheet, int size) {
        for (int columnNum = 1; columnNum <= size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }

                if (currentRow.getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == 1) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }
}

3.其他辅助实体

(1)动态列实体

package test.com.zcm;

/**
 * 动态列:根据数据变动有的员工有多项报销费用
 */
public class DynamicAttributeVO {

    private String v_name;//属性
    private String v_value;//对应的值

    public String getV_name() {
        return v_name;
    }

    public void setV_name(String v_name) {
        this.v_name = v_name;
    }

    public String getV_value() {
        return v_value;
    }

    public void setV_value(String v_value) {
        this.v_value = v_value;
    }

    public DynamicAttributeVO(String v_name,String v_value){
        this.v_name = v_name;
        this.v_value = v_value;
    }
}

(2)生成模板实体类

package test.com.zcm;

import com.alibaba.fastjson.JSONArray;//fastjson-1.2.73.jar

import java.math.BigDecimal;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 生成模板实体类
 */
public class EasyExcelEntity {

    //主标题(多个就是多行标题)
    private List<String> title;

    //台头(同行根据'-'分左右台头)
    private List<String> stageHead;

    //表头
    private LinkedHashMap<String, String> tableHead;

    //表数据
    private JSONArray tableData;

    //合计字符在表格第几列
    private Integer totalIndex;

    //计费项
    private Integer billingItem;

    //合计数据
    private Map<String, BigDecimal> totalData;

    //总合计
    private String totalSumData;

    //表格底部(同行根据'-'分左右台头)
    private List<String> bottomData;

    //工作表名称
    private String sheetName;

    public List<String> getTitle() {
        return title;
    }

    public void setTitle(List<String> title) {
        this.title = title;
    }

    public List<String> getStageHead() {
        return stageHead;
    }

    public void setStageHead(List<String> stageHead) {
        this.stageHead = stageHead;
    }

    public LinkedHashMap<String, String> getTableHead() {
        return tableHead;
    }

    public void setTableHead(LinkedHashMap<String, String> tableHead) {
        this.tableHead = tableHead;
    }

    public JSONArray getTableData() {
        return tableData;
    }

    public void setTableData(JSONArray tableData) {
        this.tableData = tableData;
    }

    public Integer getTotalIndex() {
        return totalIndex;
    }

    public void setTotalIndex(Integer totalIndex) {
        this.totalIndex = totalIndex;
    }

    public Integer getBillingItem() {
        return billingItem;
    }

    public void setBillingItem(Integer billingItem) {
        this.billingItem = billingItem;
    }

    public Map<String, BigDecimal> getTotalData() {
        return totalData;
    }

    public void setTotalData(Map<String, BigDecimal> totalData) {
        this.totalData = totalData;
    }

    public String getTotalSumData() {
        return totalSumData;
    }

    public void setTotalSumData(String totalSumData) {
        this.totalSumData = totalSumData;
    }

    public List<String> getBottomData() {
        return bottomData;
    }

    public void setBottomData(List<String> bottomData) {
        this.bottomData = bottomData;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }
}

(3)固定列实体

package test.com.zcm;

/**
 * excel固定列实体
 */
public class EmployeeVO {

    //员工号
    private String id;

    //员工姓名
    private String name;

    //员工部门
    private String department;

    //数据库查出有就设值
    private String v1;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDepartment() {
        return department;
    }

    public void setDepartment(String department) {
        this.department = department;
    }

    public String getV1() {
        return v1;
    }

    public void setV1(String v1) {
        this.v1 = v1;
    }

    public EmployeeVO(String id, String name, String department,String v1){
        this.id = id;
        this.name = name;
        this.department = department;
        this.v1 = v1;
    }


}

(4)表头实体

package test.com.zcm;

/**
 * 用于表头
 */
public class SheetHeadVO {

    private String name;//属性名称

    private String viewName;//表头显示值

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getViewName() {
        return viewName;
    }

    public void setViewName(String viewName) {
        this.viewName = viewName;
    }

    public SheetHeadVO(String name,String viewName){
        this.name = name;
        this.viewName = viewName;
    }
}

(5)全局数据实体

package test.com.zcm;

/**
 * 全局属性:对于整个excel的零散数据,如标题,日期这些
 */
public class WholeDataVO {

    private String title;

    private String dateTime;

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getDateTime() {
        return dateTime;
    }

    public void setDateTime(String dateTime) {
        this.dateTime = dateTime;
    }

    public WholeDataVO(String title, String dateTime){
        this.title = title;
        this.dateTime = dateTime;
    }
}

4.编码转换辅助类

package test.com.zcm;

/**
 * String类型，转换为 utf-8
 * <p>下载或者导出excel，设置中文名称有用到</p>
 */
public class StringUtils {

    public static String toUtf8String(String s) {
        StringBuffer sb = new StringBuffer();
        for (int i=0;i<s.length();i++) {
            char c = s.charAt(i);
            if (c >= 0 && c <= 255) {
                sb.append(c);
            } else {
                byte[] b;
                try {
                    b = Character.toString(c).getBytes("utf-8");
                } catch (Exception ex) {
                    System.out.println(ex);
                    b = new byte[0];
                }
                for (int j = 0; j < b.length; j++) {
                    int k = b[j];
                    if (k < 0) k += 256;
                    sb.append("%" + Integer.toHexString(k).
                            toUpperCase());
                }
            }
        }
        return sb.toString();
    }
}

5.导出效果，如图所示：