史上最详细的excel的导入与导出

     导入依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.2</version>
</dependency>

需要先导入工具类

package com.ruoyi.config.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.ruoyi.domain.PlanInfo;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * todo excel文件导入  与导出单元格合并  与分页  
 * @author 马震
 * @version 1.0
 * @date 2024/3/1 9:28
 */
@Component
@Data
@Slf4j
public class ExcelDictDTOListener   extends AnalysisEventListener<Object> {


    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List list = new ArrayList();

    @Override
    public void invoke(Object data, AnalysisContext analysisContext) {
        log.info("解析到一条记录: {}", data);
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            System.out.println(list);  //todo 在这里执行存入数据库
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        System.out.println(list);
        log.info("所有数据解析完成！");
    }







    /**
     * todo 注意导出前端必须发get请求  但是不能用axios请求
     *  window.location="http://localhost:8902/planInfo/downExcel";
     * @param fileName 文件名
     * @param sheetName sheet名字
     * @param list 所需要的数据
     * @param response response响应对象
     * @param <T>
     * @param mergeRowIndex 从第几行开始合并 0为第一行
     * @param mergeColumIndex 合并那些列 {下标从0开始 上同}
     * @param pageSize   每页多少条
     */
    public   <T> void downExcel(String fileName, String sheetName, List<T> list, HttpServletResponse response,int mergeRowIndex,int [] mergeColumIndex,int pageSize) {
        Set<String> excludeField = new HashSet<>();
        excludeField.add("searchValue");
        excludeField.add("createBy");
        excludeField.add("updateBy");
        excludeField.add("updateTime");
        excludeField.add("params");

        try {
            //        告诉浏览器返回的是 Excel
            //设置响应格式
            response.setContentType("application/vnd.ms-excel;chartset=utf-8"); //文件扩展名为excel格式
            //告诉浏览器编码规则
            response.setCharacterEncoding("utf-8");
            //文件名进行编码
            fileName = URLEncoder.encode(fileName+".xlsx", StandardCharsets.UTF_8.toString());
            //响应头加文件名信息
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName); //触发文件名为filename的“另存为”对话框
//            EasyExcel.write(response.getOutputStream(), list.get(0).getClass()).sheet("sheetName").doWrite(list);



            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), PlanInfo.class)
                    .registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex,mergeColumIndex))
                    .build();


            //多少页
            int page = list.size()/pageSize + (list.size()%pageSize!=0?1:0);

            // 向Excel的不同Sheet分页写入数据
            for (int i = 0; i < page; i++) {
                //下表开始位置
                int indexStart = i * pageSize;
                //下标结束位置
                int indexEnd = i * pageSize + pageSize > list.size() ? list.size() : i * pageSize + pageSize;
                List<PlanInfo> range =
                        (List<PlanInfo>) list.subList(indexStart, indexEnd);

                // 创建Sheet对象
                WriteSheet writeSheet = EasyExcel.writerSheet(sheetName+ i).build();
                excelWriter.write(range, writeSheet);
            }
            // 关闭流
            excelWriter.finish();

            Thread.sleep(2000);
        }catch (Exception e){
            e.printStackTrace();
        }

    }


}

合并单元格工具类

package com.ruoyi.config.excel;


import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

public class ExcelFillCellMergeStrategy implements CellWriteHandler {

    private int[] mergeColumnIndex;
    private int mergeRowIndex;

    public ExcelFillCellMergeStrategy() {
    }

    public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }


    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        if (curRowIndex > mergeRowIndex) {
            for (int columnIndex : mergeColumnIndex) {
                if (curColIndex == columnIndex) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }



    /**
     * 当前单元格向上合并
     * @param writeSheetHolder   合并单元格对象
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();

        // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
        if (curData.equals(preData)) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }



    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }
}

controller的具体实现

  
    @Autowired
    ExcelDictDTOListener excelDictDTOListener;

/**
     * 导入excel
     */
    @CrossOrigin
    @RequestMapping("saveExcel")
    public void saveExcel(@RequestParam("file") MultipartFile file) throws IOException {
        System.out.println(file);
        try {
            // 读取文件流
            EasyExcel.read
                            (file.getInputStream(),// 前端上传的文件
                                    PlanInfo.class,// 跟excel对应的实体类
                                    new ExcelDictDTOListener())// 监听器
                    .excelType(ExcelTypeEnum.XLSX)// excel的类型
                    .sheet().doRead();
            log.info("importData finished");
        } catch (IOException e) {
            log.info("失败");
            e.printStackTrace();
        }
    }



    /**
     * 下载excel
     */
    @GetMapping("downExcel")
    public void getDownExcel(){
        try {
            ArrayList<PlanInfo> list = new ArrayList<>();
            List<PlanInfo> planInfos = planInfoService.selectPlanInfoList(new PlanInfo());
            for (PlanInfo r : planInfos) {
                if (r.getDelFlag() != 0) {
                    r.setDelFlagString("已删除");
                } else {
                    r.setDelFlagString("未删除");
                }
//                //如果图片不为空 则下载到本地
//                if (planInfo.getPic() != null && planInfo.getPic() != "") {
//                    //图片网络地址
//                    URI url = new URI(planInfo.getPic());
//
//                    URLConnection conn = url.toURL().openConnection();
//
//                    InputStream inputStream = conn.getInputStream();
//                    //替换图片名称
//                    String fileName =
//                            com.ruoyi.common.core.utils.
//                                    uuid.UUID.randomUUID().toString().toString() +
//                                    planInfo.getPic().substring(planInfo.getPic().lastIndexOf("."));
//                    //图片在本地的存储位置
//                    String SYSPATH ="C:/Users/Lenovo/Desktop/新建文件夹/";//本地地址
//                    String path = SYSPATH + fileName;
//                    FileOutputStream outputStream = new FileOutputStream(path);
//
//                    //写入到本地
//                    int bytesRead;
//                    byte[] buffer = new byte[4096];
//                    while ((bytesRead = inputStream.read(buffer)) != -1) {
//                        outputStream.write(buffer, 0, bytesRead);
//                    }
//
//                    outputStream.close();//关流
//                    inputStream.close();//关流
//
//                    planInfo.setPic(path);
//                    list.add(planInfo);
//                }
            }
            System.out.println(planInfos);
            int mergeRowIndex =1;
            int [] mergeColumIndex = {1};
            int page = 5;
            excelDictDTOListener.downExcel("用户列表","用户列表",planInfos,response,mergeRowIndex,mergeColumIndex,page);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

这里的注释就是可以把图片下载到本地然后可以再excel导出的时候  在excel里面实现图片也能看见 ， 但是没啥鸟用。

实体类

package com.ruoyi.domain;

import java.util.Date;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.converters.string.StringImageConverter;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
import com.ruoyi.common.core.annotation.Excel;
import com.ruoyi.common.core.web.domain.BaseEntity;

/**
 * 农作物种植记录跟踪信息对象 plan_info
 *
 * @author 马震
 * @date 2024-02-29
 */
@Data
@ExcelIgnoreUnannotated  //解决excel导入单元格格式不对的问题
public class PlanInfo extends BaseEntity
{
    private static final long serialVersionUID = 1L;

    /** 农作物记录ID */
    private Long planId;

    /** 农作物信息ID */
    @ExcelProperty(value = "农作物信息ID")
    private Long cropInfoId;

    /** 记录时间 */
    @JsonFormat(pattern = "yyyy-MM-dd")
    @ExcelProperty(value = "记录时间")
    private Date recordTime;

    /** 温度 */
    @ExcelProperty(value = "温度")
    private String temperature;

    /** 生长状况 */
    @ExcelProperty(value = "生长状况")
    private String arowths;

    /** 湿度 */
    @ExcelProperty(value = "湿度")
    private String humidness;

    /** 光照 */
    @ExcelProperty(value = "光照")
    private String illumination;

    /** 农作物图片 */
    @ExcelProperty(value = "农作物图片")
    private String pic;

    /** 操作人 */
    @ExcelProperty(value = "操作人")
    private String operator;

    /** 逻辑删除 */
    @ExcelIgnore
    private Long delFlag;
    @ExcelProperty(value = "删除状态")
    private String delFlagString;

}