Excel导出

Leslie_Lei

已于 2024-03-01 16:34:19 修改

阅读量415

点赞数 7

分类专栏： Excel 文章标签： excel Easyexcel

于 2024-03-01 15:36:09 首次发布

本文链接：https://blog.csdn.net/Leslie_Lei/article/details/136397111

版权

Excel 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Maven依赖

        <!--easy excel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.2</version>
        </dependency>

        <!-- excel工具 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.10</version>
            <scope>provided</scope>
        </dependency>

        <!--工具集-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.12</version>
        </dependency>

实体类


import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;

/**
 * entity实体类
 *
 * @Package_Name 
 * @Author Leslie Lee
 * @TIME 
 * @Version 1.0
 */
@Data
@ExcelIgnoreUnannotated //忽略未标注的字段
public class EntityDemo implements Serializable {
    private static final long serialVersionUID = 1L;

    @ExcelProperty("姓名")
    private String name;//

    private String age;//

    @ExcelProperty("申请日期")
    @DateTimeFormat("yyyy-MM-dd")//时间格式
    private Date applicationDate;//申请日期

}

表头列宽自适应处理器



import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.List;

/**
 * 表头自动列宽
 *
 * @Package_Name 
 * @Author Leslie Lee
 * @TIME 
 * @Version 1.0
 */
public class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
    // 统计setColumnWidth被调用多少次
    private static int count = 0;

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head,
                                  Integer relativeRowIndex, Boolean isHead) {
        // 简单设置
        Sheet sheet = writeSheetHolder.getSheet();
        sheet.setColumnWidth(cell.getColumnIndex(), 5000);
    }
}

行列冻结处理器



import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.Sheet;

/**
 * excel导出，冻结列和行 处理器
 *
 * @Package_Name
 * @Author Leslie Lee
 * @TIME
 * @Version 1.0
 */
public class FreezeRowColHandler implements SheetWriteHandler {
    /**
     * colSplit:表示要冻结的列数；
     * rowSplit:表示要冻结的行数；
     * firstCellNum:表示被固定列右边第一列的列号；
     * firstRollNum :表示被固定行下边第一列的行号;
     */
    private int colSplit = 0, rowSplit = 1, firstCellNum = 0, firstRollNum = 0;

    /* 空参构造，不传值默认冻结一行 */
    public FreezeRowColHandler() {
    }

    public FreezeRowColHandler(int colSplit, int rowSplit, int firstCellNum, int firstRollNum) {
        this.colSplit = colSplit;
        this.rowSplit = rowSplit;
        this.firstCellNum = firstCellNum;
        this.firstRollNum = firstRollNum;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        sheet.createFreezePane(colSplit, rowSplit, firstCellNum, firstRollNum);
    }
}

合并单元格处理器



import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.lang.reflect.Field;
import java.util.List;

/**
 * easyExcel合并单元格
 *
 * @Package_Name
 * @Author Leslie Lee
 * @TIME
 * @Version 1.0
 */
@Slf4j
public class ExcelCellMergeStrategy implements SheetWriteHandler {

    private List<CellRangeAddress> addressList;

    public ExcelCellMergeStrategy() {
    }

    public ExcelCellMergeStrategy(List<CellRangeAddress> addressList) {
        this.addressList = addressList;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    /* 执行合并 */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        //打印合并信息
//        log.info("ExcelCellMergeStrategy|start|合并单元格|sheet名称:{}|开始执行", sheet.getSheetName());
        for (CellRangeAddress address : addressList) {
            sheet.addMergedRegionUnsafe(address);
        }
    }

    /**
     * 合并单元格策略，根据首列相同,合并指定列数
     *
     * @param mergeColumnIndexes 合并列数 前{x}列
     * @param tableHeaderRows    表头行数
     * @param list               导出的数据
     * @param <T>                导出数据-实体类
     * @return 合并范围
     */
    public static <T> List<CellRangeAddress> getOneColAddressList(int mergeColumnIndexes, int tableHeaderRows, List<T> list) {
        List<CellRangeAddress> addressList = CollUtil.newArrayList();
        if (CollUtil.isNotEmpty(list)) {
            Class<?> aClass = list.get(0).getClass();
            Field f = null;
            Field[] fields = aClass.getDeclaredFields();
            for (Field field : fields) {
                if (field.isAnnotationPresent(ExcelProperty.class)) {
                    f = field;
                    break;
                }
            }
            if (f != null) {
                //list长度,最大行数
                int size = list.size();
                //防止下标越界使用
                int i2 = size - 2;
                for (int i = 0; i < size; i++) {

                    //开始行 从 不合并标题
                    int firstRow = i + tableHeaderRows;
                    //结束行
                    int lastRow = firstRow;
                    //开始列,结束列
                    //int firstCol,lastCol = 0;
                    String value = StrUtil.toString(ReflectUtil.getFieldValue(list.get(i), f));
                    boolean flag = true;
                    while (flag) {
                        //最后一行不再进行匹配,直接退出
                        if (i > i2) {
                            break;
                        }
                        String value1 = StrUtil.toString(ReflectUtil.getFieldValue(list.get(i + 1), f));
                        //值相同则合并 -> 合并行数+1 下标+1遍历跳过相同行
                        if (StrUtil.equals(value1, value)) {
                            i++;
                            lastRow++;

                        } else {
                            flag = false;
                        }
                        if (i == size) {
                            flag = false;
                        }
                    }
                    if (lastRow != firstRow) {
                        for (int i1 = 0; i1 < mergeColumnIndexes; i1++) {
                            addressList.add(new CellRangeAddress(firstRow, lastRow, i1, i1));
                        }
                    }
                }
            }
        }
        return addressList;
    }
}

工具类



import com.alibaba.excel.EasyExcel;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

/**
 * @Package_Name 
 * @Author Leslie Lee
 * @TIME 
 * @Version 1.0
 */
public class EasyExcelUtils<T> {
    /**
     * 分片导出
     *
     * @param response
     * @param list
     * @param sheetName
     * @throws IOException
     */
    public static void downloadFragmentation2(HttpServletResponse response, List<?> list, String sheetName, int mergeColumnIndexs, int hdrRow) throws IOException {
        Class<?> clazz = list.get(0).getClass();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // URLEncoder.encode 防止中文乱码 和easyexcel没有关系
        String fileName = URLEncoder.encode(sheetName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        // 定义每次写入的数据量
        int batchSize = 2000;
        // 定义写入的起始位置
        int start = 0;
        // 定义写入的结束位置
        int end = 0;
        while (end < list.size()) {
            // 计算下一次写入的结束位置
            end = Math.min(start + batchSize, list.size());
            // 获取本次写入的数据
            List<?> subList = list.subList(start, end);
            // 写入数据
            EasyExcel.write(response.getOutputStream(), clazz)
                    /* 列宽自适应 */
                    .registerWriteHandler(new ExcelWidthStyleStrategy())
                    /* 行列冻结 */
                    .registerWriteHandler(new FreezeRowColHandler())
                    /* 单元格合并 */
                    .registerWriteHandler(new ExcelCellMergeStrategy(getOneColAddressList(mergeColumnIndexs,hdrRow,list)))
                    .sheet(sheetName).doWrite(list);
            // 更新起始位置
            start = end;
        }
    }
}

Leslie Lee 随笔