POI之EXCEL导出封装（未测试）

基础类描述

1. ExcelWriter（导出工具类）
2. Query（通用查询）
3. Consumer（函数参数）
4. SpringBeanUtil（获取bean）

代码

ExcelWriter

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.util.PageUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.alibaba.excel.util.ListUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.itxl.common.utils.SpringBeanUtil;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Future;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @authar liuWei
 * @ @date 2024/3/30
 */
public class ExcelWriter <T>{
    private HttpServletResponse response;

    private ServletOutputStream outputStream;

    private boolean memory = false;

    private boolean multiThread = false;

    private String fileName = "file";

    private Workbook workbook = new SXSSFWorkbook();

    private String curSheetName = "sheet";

    /**
     * 响应文件流类型
     */
    private static final String CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    /**
     * 响应头文件名编码格式
     */
    private static final String ENCODE = "UTF-8";

    private AtomicInteger curRowIndex;

    private Integer total;

    private Integer sheetNo = 1;

    private static final Integer sheetMaxNum = 1000000;

    private List<List<Integer>> widths;

    private List<List<Integer>> heights;

    private List<List<Integer>> cellStyleIndex;

    private List<CellStyle> cellStyles;

    private Integer defaultColumnWidth = 20;

    private Integer defaultRowHeight = 20;

    private List<List<Integer>> cellValueIndex;

    private List<String> cellValues;

    private List<List<Integer>> mergeInfo;

    private List<List<Integer>> dropDownBoxIndex;

    private List<List<String>> dropDownBoxDatas;

    private List<List<Integer>> chartAnchor;

    private List<byte[]> chartBytes;

    public ExcelWriter(HttpServletResponse response) {
        this.response = response;
    }

    /**
     * 使用内存缓存（加快速度）
     * @return
     */
    public ExcelWriter<T> memory(){
        workbook = new XSSFWorkbook();
        return this;
    }

    /**
     * 设置文件名
     * @param fileName
     * @return
     */
    public ExcelWriter<T> setFileName(String fileName){
        this.fileName = fileName;
        return this;
    }

    /**
     * 初始化响应流
     * @return
     * @throws IOException
     */
    public ExcelWriter<T> initResponse() throws IOException {
        response.setContentType(CONTENT_TYPE);
        response.setCharacterEncoding(ENCODE);
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        fileName = URLEncoder.encode(fileName, ENCODE).replaceAll("\\+", "%20");
        response.setHeader("fileName",fileName+".xlsx");
        this.outputStream = response.getOutputStream();
        return this;
    }

    /**
     * 分页
     * @param total
     * @return
     */
    public ExcelWriter<T> paging(Integer total){
        this.total = total;
        return this;
    }

    /**
     * 开启多线程
     * @param total
     * @return
     */
    public ExcelWriter<T> multiThread(Integer total){
        this.multiThread = true;
        return paging(total);
    }

    /**
     * 设置头
     * @param heads
     * @param rowIndex
     * @param colIndex
     * @return
     */
    public ExcelWriter<T> setHead(List<String> heads,Integer rowIndex, Integer colIndex){
        for (int i = 0; i < heads.size(); i++) {
            setCell(heads.get(i),rowIndex,colIndex+i).setHeadStyle(rowIndex,colIndex+i);
        }
        return this;
    }

    /**
     * 设置单元格内容
     * @param value
     * @param rowIndex
     * @param colIndex
     * @return
     */
    public ExcelWriter<T> setCell(String value,Integer rowIndex,Integer colIndex){
        if (CollUtil.isEmpty(cellValues)){
            cellValues = new ArrayList<>();
        }
        if (CollUtil.isEmpty(cellValueIndex)){
            cellValueIndex = new ArrayList<>();
        }
        cellValues.add(value);
        cellValueIndex.add(ListUtils.newArrayList(rowIndex,colIndex));
        return this;
    }

    /**
     * 设置单元格内容
     * @param value
     * @param rowIndex
     * @param colIndex
     * @return
     */
    private ExcelWriter<T> doSetCell(String value,Integer rowIndex,Integer colIndex){
        Sheet sheet = getSheet();
        Row row = getRow(sheet,rowIndex);
        Cell cell = getCell(row, colIndex);
        cell.setCellValue(value);
        return this;
    }

    /**
     * 获取sheet
     * @return
     */
    public Sheet getSheet(){
        Sheet sheet = workbook.getSheet(curSheetName + sheetNo);
        if (sheet == null){
            sheet = workbook.createSheet(curSheetName + sheetNo);
        }
        return sheet;
    }

    /**
     * 切换sheet
     * @param sheetName
     * @return
     */
    public ExcelWriter<T> setCurSheetName(String sheetName){
        this.curSheetName = sheetName;
        return this;
    }

    /**
     * 获取Row
     * @param sheet
     * @param rowIndex
     * @return
     */
    private Row getRow(Sheet sheet,Integer rowIndex){
        Row row = sheet.getRow(rowIndex);
        if (row == null){
            row = sheet.createRow(rowIndex);

        }
        return row;
    }

    /**
     * 获取cell
     * @param row
     * @param colIndex
     * @return
     */
    private Cell getCell(Row row,Integer colIndex){
        Cell cell = row.getCell(colIndex);
        if (cell == null){
            cell = row.getCell(colIndex);
        }
        return cell;
    }

    /**
     * 获取空白单元格样式，如果需要使用memory方法，，需要使用memory后调用
     * @return
     */
    public CellStyle getBlankCellStyle(){
        return workbook.createCellStyle();
    }

    /**
     * 设置单元格样式
     * @param rowIndex
     * @param colIndex
     * @param cellStyle
     * @return
     */
    public ExcelWriter<T> setCellStyle(Integer rowIndex, Integer colIndex, CellStyle cellStyle){
        if (CollUtil.isEmpty(cellStyleIndex)){
            cellStyleIndex = new ArrayList<>();
        }
        if (CollUtil.isEmpty(cellStyles)){
            cellStyles = new ArrayList<>();
        }
        cellStyleIndex.add(ListUtils.newArrayList(rowIndex,colIndex));
        cellStyles.add(cellStyle);
        return this;
    }

    /**
     * 设置头样式
     * @param rowIndex
     * @param colIndex
     * @return
     */
    private ExcelWriter<T> setHeadStyle(Integer rowIndex, Integer colIndex){
        CellStyle cellStyle = getBlankCellStyle();
        //设置头样式
        return setCellStyle(rowIndex,colIndex,cellStyle);
    }

    /**
     * 设置列宽
     * @param col
     * @param width
     * @return
     */
    public ExcelWriter<T> setColumnWidth(Integer col,Integer width){
        if (CollUtil.isEmpty(widths)){
            widths = new ArrayList<>();
        }
        widths.add(ListUtils.newArrayList(col,width));
        return this;
    }

    /**
     * 设置列宽
     * @param cols 0：列，1：列宽
     * @return
     */
    public ExcelWriter<T> setColumnWidth(List<List<Integer>> cols){
        if (CollUtil.isEmpty(widths)){
            widths = new ArrayList<>();
        }
        widths.addAll(cols);
        return this;
    }

    /**
     * 设置行高
     * @param rows 0：行，1：行高
     * @return
     */
    public ExcelWriter<T> setRowHeight(List<List<Integer>> rows){
        if (CollUtil.isEmpty(heights)){
            heights = new ArrayList<>();
        }
        heights.addAll(rows);
        return this;
    }

    /**
     * 设置行高，列宽
     * @param axisInfo 0：行，1：行高，2：列，3：列宽
     * @return
     */
    public ExcelWriter<T> setRowHeightAndColumnWidth(List<List<Integer>> axisInfo){
        for (List<Integer> axis : axisInfo) {
            setRowHeightAndColumnWidth(axis.get(0),axis.get(1),axis.get(2),axis.get(3));
        }
        return this;
    }

    /**
     * 设置行高，列宽
     * @param row
     * @param rowHeight
     * @param col
     * @param colWidth
     * @return
     */
    public ExcelWriter setRowHeightAndColumnWidth(Integer row,Integer rowHeight,Integer col,Integer colWidth){
        return setRowHeight(row, rowHeight).setColumnWidth(col, colWidth);
    }

    /**
     * 设置默认列宽
     * @param height
     * @return
     */
    public ExcelWriter<T> setDefaultRowHeight(Integer height){
        this.defaultRowHeight = height;
        return this;
    }

    /**
     * 设置默认列宽
     * @param width
     * @return
     */
    public ExcelWriter<T> setDefaultColumnWidth(Integer width){
        this.defaultColumnWidth = width;
        return this;
    }

    /**
     * 设置默认行高，列宽
     * @param height
     * @param width
     * @return
     */
    public ExcelWriter<T> setDefaultRowHeightAndColumnWidth(Integer height,Integer width){
        return setDefaultColumnWidth(width).setDefaultRowHeight(height);
    }

    /**
     * 设置行高
     * @param rowIndex
     * @param height
     * @return
     */
    public ExcelWriter<T> setRowHeight(Integer rowIndex,Integer height){
        heights.add(ListUtils.newArrayList(rowIndex,height));
        return this;
    }


    /**
     * 写数据
     * @param query
     * @param row
     * @param col
     * @return
     * @throws IOException
     * @throws ExecutionException
     * @throws InterruptedException
     */
    public ExcelWriter<T> write(Query<List<List<String>>> query,Integer row,Integer col) throws IOException, ExecutionException, InterruptedException {
        if (curRowIndex == null){
            curRowIndex = new AtomicInteger(row);
        }
        if (total == null){
             write(query.accept(),curRowIndex.get(),col);
             return this;
        }
        int totalPage = PageUtil.totalPage(total, query.getPageSize());
        AtomicInteger pageNum = new AtomicInteger(0);
        if (!multiThread){
            write(query, curRowIndex.get(), col, pageNum,totalPage);
            return this;
        }
        ThreadPoolExecutor poolExecutor = SpringBeanUtil.getBean(ThreadPoolExecutor.class);
        List<Future<?>> futures = new ArrayList<>();
        for (int i = 0; i < 4; i++) {
            Future<?> future =poolExecutor.submit(() -> {
                write(query, curRowIndex.get(), col, pageNum,totalPage);
            });
            futures.add(future);
        }
        for (Future<?> future : futures) {
            future.get();
        }
        return this;
    }

    /**
     * 写数据
     * @param data
     * @param row
     * @param col
     * @throws IOException
     */
    public void write(List<List<String>> data, Integer row, Integer col) throws IOException {
        if (curRowIndex == null) {
            curRowIndex = new AtomicInteger(row);
        }
        if (curRowIndex.get() + data.size() > sheetMaxNum){
            List<List<String>> left = ListUtil.sub(data, 0, sheetMaxNum - curRowIndex.get());
            doWrite(left,curRowIndex.get(), col);
            data = ListUtil.sub(data, sheetMaxNum - curRowIndex.get(), data.size());
            curRowIndex.set(0);
            sheetNo++;
        }
        doWrite(data,curRowIndex.get(), col);
    }

    private void write(Query<List<List<String>>> query, Integer row, Integer col,  AtomicInteger pageNum,int totalPage) {
        while (pageNum.get() < totalPage){
            query.setPageNum(pageNum.incrementAndGet());
            List<List<String>> accept = query.accept();
            try {
                write(accept, row+(pageNum.get()-1)*query.getPageSize(), col);
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
            query.removePage();
        }
    }

    /**
     * 写数据
     * @return
     * @throws IOException
     */
    public ExcelWriter<T> write() throws IOException {
        workbook.write(outputStream);
        return this;
    }

    /**
     * 写数据
     * @param data
     * @param row
     * @param col
     * @throws IOException
     */
    private void doWrite(List<List<String>> data, Integer row, Integer col) throws IOException {
        synchronized (outputStream){
            for (int i = 0; i < data.size(); i++) {
                for (int j = 0; j < data.get(i).size(); j++) {
                    doSetCell(data.get(i).get(j),row+i,col+j);
                }
            }
            curRowIndex.set(row+data.size());
            write();
        }
    }

    private List<List<String>> parseData(List<T> datas,List<String> fields){
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        List<List<String>> res = new ArrayList<>(datas.size());
        for (T data : datas) {
            JSONObject json = JSONUtil.parseObj(data);
            List<String> objects = new ArrayList<>(fields.size());
            for (String field : fields) {
                Object o = json.get(field);
                if (o == null){
                    o = "";
                }else if (o instanceof Date){
                    o = format.format(o);
                }else{
                    o = o.toString();
                }
                objects.add(o.toString());
            }
            res.add(objects);
        }
        return res;
    }

    /**
     * 编译基础信息
     * @return
     */
    public ExcelWriter<T> builder(){
        Sheet sheet = getSheet();
        sheet.setDefaultColumnWidth(defaultColumnWidth * 256);
        sheet.setDefaultRowHeight(defaultRowHeight.shortValue());
        if (widths != null){
            for (List<Integer> width : widths) {
                sheet.setColumnWidth(width.get(0),width.get(1));
            }
        }
        if (heights != null){
            for (List<Integer> height : heights) {
                Row row = getRow(sheet, height.get(0));
                row.setHeight(height.get(1).shortValue());
            }
        }
        if (cellStyleIndex != null){
            for (int i = 0; i < cellStyleIndex.size(); i++) {
                List<Integer> index = cellStyleIndex.get(i);
                CellStyle cellStyle = cellStyles.get(i);
                Cell cell = getCell(getRow(sheet, index.get(0)), index.get(1));
                cell.setCellStyle(cellStyle);
            }
        }
        if (cellValueIndex != null){
            for (int i = 0; i < cellValueIndex.size(); i++) {
                List<Integer> index = cellValueIndex.get(i);
                String value = cellValues.get(i);
                doSetCell(value,index.get(0),index.get(1));
            }
        }
        if (mergeInfo != null){
            for (List<Integer> merge : mergeInfo) {
                doMerge(merge.get(0),merge.get(1),merge.get(2),merge.get(3));
            }
        }
        if (dropDownBoxDatas != null){
            for (int i = 0; i < dropDownBoxIndex.size(); i++) {
                List<Integer> index = dropDownBoxIndex.get(i);
                List<String> data = dropDownBoxDatas.get(i);
                doSetDropDownBox(data,index.get(0),index.get(1),index.get(2),index.get(3));
            }
        }
        if (chartAnchor != null){
            for (int i = 0; i < chartAnchor.size(); i++) {
                List<Integer> anchorIndex = chartAnchor.get(i);
                byte[] bytes = chartBytes.get(i);
                Drawing<?> drawing =sheet.createDrawingPatriarch();
                ClientAnchor anchor = drawing.createAnchor(anchorIndex.get(0), anchorIndex.get(1), anchorIndex.get(2), anchorIndex.get(3), anchorIndex.get(4),
                        anchorIndex.get(5), anchorIndex.get(6), anchorIndex.get(7));
                // 参数分别代表 dx1, dy1, dx2, dy2, col1, row1, col2, row2
                // 参数解释：
                // dx1, dy1 - 图片左上角相对于单元格左上角的偏移量（以EMU为单位）
                // dx2, dy2 - 图片右下角相对于单元格左上角的偏移量（以EMU为单位）
                // col1, row1 - 图片左上角的起始单元格位置
                // col2, row2 - 图片右下角的结束单元格位置
                // 注意：EMU是Excel内部使用的单位，1英寸 = 914400 EMU

                // 将图片添加到Workbook中，并获取图片的索引
                int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
                // 创建图片并添加到Drawing对象中
                drawing.createPicture(anchor, pictureIdx);
            }
        }
        return this;
    }

    /**
     * 设置图表
     * @param bytes 图表数据
     * @param dx1
     * @param dy1
     * @param dx2
     * @param dy2
     * @param col1
     * @param row1
     * @param col2
     * @param row2
     * @return
     */
    public ExcelWriter<T> setChart(byte[] bytes,int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2){
        if (chartAnchor == null){
            chartAnchor = new ArrayList<>();
        }
        if (chartBytes == null){
            chartBytes = new ArrayList<>();
        }
        chartBytes.add(bytes);
        chartAnchor.add(ListUtils.newArrayList(dx1,dy1,dx2,dy2,col1,row1,col2,row2));
        return this;
    }

    /**
     * 合并单元格
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     */
    private void doMerge(Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol){
        CellRangeAddress address = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        getSheet().addMergedRegion(address);
    }

    /**
     * 合并单元格
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     * @return
     */
    public ExcelWriter<T> merge(Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol){
        if (mergeInfo == null){
            mergeInfo = new ArrayList<>();
        }
        mergeInfo.add(ListUtils.newArrayList(firstRow,lastRow,firstCol,lastCol));
        return this;
    }

    /**
     * 合并单元格
     * @param mergeInfo
     * @return
     */
    public ExcelWriter<T> merge(List<List<Integer>> mergeInfo){
        if (this.mergeInfo == null){
            this.mergeInfo = new ArrayList<>();
        }
        this.mergeInfo.addAll(mergeInfo);
        return this;
    }

 ;    /**
     * 设置下拉框
     * @param data
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     * @return
     */
    public ExcelWriter<T> setDropDownBox(List<String> data,Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol){
        if (dropDownBoxIndex == null){
            dropDownBoxIndex = new ArrayList<>();
        }
        if (dropDownBoxDatas == null){
            dropDownBoxDatas = new ArrayList<>();
        }
        dropDownBoxIndex.add(ListUtils.newArrayList(firstRow,lastRow,firstCol,lastCol));
        dropDownBoxDatas.add(data);
        return this;
    }

    /**
     * 设置下拉框
     * @param data
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     */
    public void doSetDropDownBox(List<String> data,Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol){
        Sheet sheet = getSheet();
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();

        // 创建列表约束
        DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(data.toArray(new String[0]));

        // 创建单元格范围，这里设置的是A1单元格
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);

        // 创建数据验证对象
        DataValidation validation = validationHelper.createValidation(constraint, addressList);
        // 处理Excel中的错误警告
        validation.setShowErrorBox(true);
        validation.createErrorBox("错误", "您必须选择下拉列表中的选项之一。");

        // 将数据验证添加到工作表中
        sheet.addValidationData(validation);
    }





}

Query

import org.itxl.common.utils.Consumer;

/**
 * @authar liuWei
 * @ @date 2024/3/25
 */
public abstract class Query<T> implements Consumer<T> {

    private final ThreadLocal<Integer> local = new ThreadLocal<>();

    private Integer pageSize = 10000;

    public void setPageNum(Integer pageNum){
        local.set(pageNum);
    }

    public Integer getPageNum(){
       return local.get();
    }

    public void removePage(){
        local.remove();
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        if (pageSize == null){
            return;
        }
        this.pageSize = pageSize;
    }

    @Override
    public T accept() {
        return andThen();
    }
}

Consumer

/**
 * @authar  liuWei
 * 通用函数参数
 * @param <T>
 */
public interface Consumer<T>{
    /**
     * 方法实现
     * @return T
     */
    T andThen();

    /**
     * 方法执行
     * @return T
     */
    T accept();
}

SpringBeanUtil

package org.itxl.common.utils;

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

/**
 * @authar liuWei
 * @ @date 2024/2/2
 */
@Component
public class SpringBeanUtil  implements ApplicationContextAware {
    private static ApplicationContext applicationContext;

    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }

    public static Object getBean(String beanName) {
        return applicationContext.getBean(beanName);
    }

    public static <T> T getBean(Class<T> clazz) {
        return applicationContext.getBean(clazz);
    }

    public static <T> T getBean(String beanName, Class<T> clazz) {
        return applicationContext.getBean(beanName, clazz);
    }

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        if (SpringBeanUtil.applicationContext == null){
            SpringBeanUtil.applicationContext = applicationContext;
        }
    }
}

关于导出工具类ExcelWriter对于图表的的导出方法的使用

1. 通过JFreeChart绘制图片，转为byte数据调用方法使用
   1.1 JFreeChart使用请查看Java绘图库JFreeChart的详细使用教程(入门级)