使用EasyExcel实现Excel的导入导出以及使用过程中遇到的bug

名字太俊不提也罢i

已于 2024-06-28 18:07:02 修改

阅读量3.1k

点赞数 4

分类专栏： EasyExcel 文章标签： java excel

于 2020-11-10 15:58:28 首次发布

本文链接：https://blog.youkuaiyun.com/JieZai958921541/article/details/109361496

版权

EasyExcel 专栏收录该内容

1 篇文章

订阅专栏

我对EasyExcel的理解：
EasyExcel重写了POI对07版Excel的解析，可以把内存消耗从100M降低到10M以内，并且再大的Excel都不会造成内存溢出（OOM）。需要注意的是它只能读取Excel不能读图片。

导入导出案例：

EasyExcel读取数据并导入数据库
导出Excel时生成下拉框、表头添加批注
解决下拉框数据过多导致无法显示的bug

引入EasyExcel的依赖

<!-- EasyExcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.3</version>
</dependency>
<!-- lombok -->
 <dependency>
	  <groupId>org.projectlombok</groupId>
	  <artifactId>lombok</artifactId>
	  <version>1.18.2</version>
 </dependency>

定义导出模型对象（实体类）

/**
 * 问题和整改信息类
 * 
 * @Author Big.Hu
 * @Date 2020/7/29 14:03
 */
@Data // lombok注解，自动生成get/set等方法
@HeadRowHeight(20) // 自定义表头高度
public class IssueReformInfoVO extends BaseRowModel {

    @ColumnWidth(15) // 自定义单元格宽度
    @ExcelProperty(value = "项目ID", index = 0)
    private String projectId;

    @ColumnWidth(20)
    @ExcelProperty(value = "项目名称", index = 1)
    private String projectName;

    @ColumnWidth(20)
    @ExcelProperty(value = "问题类别", index = 2)
    private String issueSort;

    @ColumnWidth(20)
    @ExcelProperty(value = "问题一级类型", index = 3)
    private String issueTypeOne;

    @ColumnWidth(20)
    @ExcelProperty(value = "问题二级类型", index = 4)
    private String issueTypeTwo;
	
	//..........字段太多，省略了..........

web上传Excel导入数据库

监听器，继承AnalysisEventListener<?>

/**
 * 监听器，读取Excel并保存在List集合里
 * 
 * @Author Big.Hu
 * @Date 2020/7/29 17:45
 */
@Data
@Component
public class IssueExcelListener extends AnalysisEventListener<IssueReformInfoVO> {

    private List<IssueReformInfoVO> data = new ArrayList<>();

    /**
     *
     * @param issueReformInfo 读取到的一行数据
     * @param analysisContext AnalysisContext对象还可以获取当前 sheet，当前行等数据
     */
    @Override
    public void invoke(IssueReformInfoVO issueReformInfo, AnalysisContext analysisContext) {
        // 存问题记录
        data.add(issueReformInfo);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
}

controller层

// 注入监听类
@Autowired
private IssueExcelListener issueExcelListener;

@RequestMapping(value = "importIssueExcel", method = RequestMethod.POST)
public Result<?> importIssueExcel(@RequestParam("excelFile") MultipartFile excelFile) {
    try {
    	// 读取之前先清空集合
        issueExcelListener.getData().clear();
        ExcelReader excelReader = new ExcelReader(excelFile.getInputStream(), ExcelTypeEnum.XLSX, null, issueExcelListener);
        excelReader.read(new Sheet(1, 1, IssueReformInfoVO.class));
        // TODO 根据自己的业务来处理读取到的数据：issueExcelListener.getData()
        return issueService.importIssueAndReform(issueExcelListener.getData());
    } catch (Exception e) {
        e.printStackTrace();
        return Result.error(e.getMessage());
    }
}

导出Excel

controller层

/**
 * 单个项目问题导出
 *
 * @param projectId   项目ID
 * @param projectName 项目名称
 * @param response    响应
 */
@RequestMapping("exportIssueExcel")
public void exportIssueExcel(@RequestParam("projectId") String projectId,
                             @RequestParam("projectName") String projectName,
                             HttpServletResponse response) {
    String date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
    String fileName = date + "，" + projectName + "问题表";
    try {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        // 查出项目的所有问题
        List<IssueReformInfoVO> issueReformInfo = frasResultIssueService.getIssueInfoByProId(projectId);
        if (0 == issueReformInfo.size()) {
            IssueReformInfoVO issueReformInfoVO = new IssueReformInfoVO();
            issueReformInfoVO.setProjectId(projectId);
            issueReformInfoVO.setProjectName(projectName);
            issueReformInfo.add(issueReformInfoVO);
        }
        OutputStream outputStream = response.getOutputStream();
        EasyExcel.write(outputStream, IssueReformInfoVO.class)
                .registerWriteHandler(spinnerWriteHandler)
                .registerWriteHandler(commentWriteHandler)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("项目问题表")
                .doWrite(issueReformInfo);
        outputStream.flush();
        outputStream.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

导出时添加下拉框（字典）数据

package org.jeecg.modules.fras.interceptor;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.jeecg.modules.fras.service.IFrasResultIssueService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 导出时添加下拉框（字典）数据
 *
 * @Author Big.Hu
 * @Date 2020/7/30 13:44
 */
@Component
public class SpinnerWriteHandler implements SheetWriteHandler {

    @Autowired
    private IFrasResultIssueService resultIssueService;

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

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);

		// 获取下拉框数据
        // 问题类别
        List<String> issueSort = resultIssueService.queryDictTextByCode("fras_issue_sort");
        // 问题类型一级
        List<String> issueTypeOne = resultIssueService.queryDictTextByCode("fras_issue_type_one");
        // 问题类型二级
        List<String> issueTypeTwo = resultIssueService.queryDictTextByCode("fras_issue_type_two");
        // 定责类型
        List<String> fixDutyType = resultIssueService.queryDictTextByCode("fras_fix_duty_type");
        // 整改状态
        List<String> reformStatus = resultIssueService.queryDictTextByCode("fras_reform_status");
        // 整改阶段
        List<String> reformStage = resultIssueService.queryDictTextByCode("fras_reform_stage");
        // 主次问题
        List<String> primaryOther = resultIssueService.queryDictTextByCode("fras_primary_other_issue");
        String[] yesOrNo = {"是", "否"};

        Map<Integer, String[]> mapDropDown = new HashMap<>();
        mapDropDown.put(2, issueSort.toArray(new String[issueSort.size()]));
        mapDropDown.put(3, issueTypeOne.toArray(new String[issueTypeOne.size()]));
        mapDropDown.put(11, fixDutyType.toArray(new String[fixDutyType.size()]));
        mapDropDown.put(18, yesOrNo);
        mapDropDown.put(19, yesOrNo);
        mapDropDown.put(20, primaryOther.toArray(new String[primaryOther.size()]));
        mapDropDown.put(23, reformStatus.toArray(new String[reformStatus.size()]));
        mapDropDown.put(31, reformStage.toArray(new String[reformStage.size()]));

		// 系统使用过程中增加了某个字典数据，导致导出Excel下拉框无法显示的问题
        // TODO ----解决导出下拉框内容过多无法显示问题 start----
        
        // 问题二级类型字典Map数据
        Map<Integer, String[]> issueTypeTwoMap = new HashMap<>();
        issueTypeTwoMap.put(4, issueTypeTwo.toArray(new String[issueTypeTwo.size()]));
        
        DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();

        // 获取一个workbook
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        String sheetName = "sheet2";
        Sheet hidden = workbook.createSheet(sheetName);

        for (Map.Entry<Integer, String[]> entry : issueTypeTwoMap.entrySet()) {
            Integer key = entry.getKey();
            String[] values = entry.getValue();
            // 参数为：下拉框的起始行,结束行,起始列,结束列
            CellRangeAddressList addressList = new CellRangeAddressList(1, 500, key, key);

            // 循环赋值
            for (int i = 0; i < values.length; i++) {
                hidden.createRow(i).createCell(key).setCellValue(values[i]);
            }
            Name category1Name = workbook.createName();
            category1Name.setNameName(sheetName);
            // 获取sheet2下拉数组
            String refers = "=" + sheetName + "!$A$1:$A$" + (values.length + 1);
            // 将刚才设置的sheet2内容引用到你的下拉列表中
            DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            writeSheetHolder.getSheet().addValidationData(dataValidation);
        }
        // 设置sheet为隐藏
        int sheetIndex = workbook.getSheetIndex(sheetName);
        if (!workbook.isSheetHidden(sheetIndex)) {
            workbook.setSheetHidden(sheetIndex, true);
        }

        // TODO ----解决导出下拉框内容过多无法显示问题 end----

        // 获取一个sheet
        Sheet sheet = writeSheetHolder.getSheet();
        // 设置下拉框
        DataValidationHelper helper1 = sheet.getDataValidationHelper();
        // 不止一个下拉框，循环赋值
        for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
            Integer key = entry.getKey();
            String[] values = entry.getValue();
            CellRangeAddressList addressList = new CellRangeAddressList(1, 10000, key, key);
            DataValidationConstraint constraint = helper1.createExplicitListConstraint(values);
            DataValidation dataValidation = helper1.createValidation(constraint, addressList);

            if (dataValidation instanceof XSSFDataValidation) {
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
            } else {
                dataValidation.setSuppressDropDownArrow(false);
            }
			// 设置隐藏行
            sheet.setColumnHidden(0, true);
            sheet.setColumnHidden(33, true);
            sheet.setColumnHidden(34, true);
            sheet.createFreezePane(0, 1, 0, 1);
            sheet.addValidationData(dataValidation);
        }
    }
}

给表头设置批注

package org.jeecg.modules.fras.interceptor;

import com.alibaba.excel.write.handler.AbstractRowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.stereotype.Component;

/**
 * 添加批注
 *
 * @Author Big.Hu
 * @Date 2020/8/02 10:20
 */
@Component
public class CommentWriteHandler extends AbstractRowWriteHandler {

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        if (isHead) {
            Sheet sheet = writeSheetHolder.getSheet();
            Drawing drawingPatriarch = sheet.createDrawingPatriarch();
            XSSFClientAnchor xssfClientAnchor1 = new XSSFClientAnchor(0, 0, 0, 0, 24, 0, 24, 1);
            Comment dateComment1 = drawingPatriarch.createCellComment(xssfClientAnchor1);
            dateComment1.setString(new XSSFRichTextString("注：时间格式如下\n2020/01/01或\n2020-01-01"));
            sheet.getRow(0).getCell(24).setCellComment(dateComment1);
        }
    }
}