利用esaypoi自定义Excel表格模板导入与导出表单数据

🎈个人公众号:🎈 :✨✨ 可为编程 ✨✨ 🍟🍟
🔑个人信条:🔑 知足知不足 有为有不为 为与不为皆为可为🌵
🍉本篇简介:🍉 本片详细说明了利用esaypoi自定义Excel表格模板导入与导出表单数据使用规则和注意要点，并给出具体操作实例，如有出入还望指正。

关注公众号【可为编程】回复【面试】领取最新面试题!!!

今天工作中又遇到了excel导出数据的功能，在这里我将自己自定义的Excel模板和相关的代码拿出来，解释一下如何进行操作，以便日后能够方便的进行功能开发，提高效率。

导出表单数据

一、引入maven依赖

  <!-- easypoi的支持 -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.2.0</version>
        </dependency>

二、编写业务逻辑代码

package com.platform.modules.yc.service.impl;

import cn.afterturn.easypoi.cache.manager.FileLoaderImpl;
import cn.afterturn.easypoi.cache.manager.POICacheManager;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.view.EasypoiTemplateExcelView;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.platform.modules.yc.dao.Dao;
import com.platform.modules.yc.entity.Entity;
import com.platform.modules.yc.service.ExcelService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author ：zjc
 * @ProjectName: platform-plus
 * @Package: com.platform.modules.yc.service.impl
 * @ClassName: ExcelServiceImpl
 * @date ：Created in 2021/4/12 15:48
 * @description：excel导出
 * @modified By：
 * @version: 1.0.0$
 * **关注公众号【可为编程】回复【面试】领取最新面试题!!!**
 */
@Service("ExcelService")
public class ExcelServiceImpl implements ExcelService {

    private void setRegionStyle(Sheet sheet, CellRangeAddress region, CellStyle cs) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            Row row = CellUtil.getRow(i, sheet);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                Cell cell = CellUtil.getCell(row, (short) j);
                cell.setCellStyle(cs);
            }
        }
    }

    private void addMergeRow(Sheet sheet, int rowIndex, String content, CellStyle cellStyle) {
        cellStyle.setLeftBorderColor((short) 8);
        cellStyle.setRightBorderColor((short) 8);
        cellStyle.setTopBorderColor((short) 8);
        cellStyle.setBottomBorderColor((short) 8);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);

        Row newRow = sheet.createRow(rowIndex);
        newRow.setHeight((short) 955);
        Cell cell = newRow.createCell(0);
        cell.setCellValue(content);
        cell.setCellStyle(cellStyle);
        int lastCellNum = 55;
        for (int i = 1; i < lastCellNum; i++) {
            newRow.createCell(i).setCellStyle(cellStyle);
        }
        // 合并单元格
        CellRangeAddress cellAddresses = new CellRangeAddress(rowIndex, rowIndex, 0, lastCellNum);
        sheet.addMergedRegion(cellAddresses);
        setRegionStyle(sheet, cellAddresses, cellStyle);
    }


    @Override
    public void remoteSensAmountExcel(List<Entity> params,HttpServletRequest request, HttpServletResponse response) {

//      String type = (String)params.getMap().getOrDefault("type", ExcelType.XSSF.name());
        String sheetName = "数量查询";// (String)params.getMap().getOrDefault("sheetName", "SheetName");
        String fileName = "数量查询.xls";
        Map<String, Object> excelValue = new HashMap<String, Object>();
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        excelValue.put("title", "数量查询");
        excelValue.put("siteName", "监测系统");
        excelValue.put("date", LocalDate.parse(dtf.format(LocalDate.now()), dtf));

        List<Map<String, Object>> valList = new ArrayList<Map<String, Object>>();
        List<Map<String, Object>> valListData = new ArrayList<>();
        Map<String, Object> mapv = new HashMap<>();
        Entity mapvalue = new Entity();
        int rowIndex = 5;
        for (int i = 0; i < params.size(); i++) {
            mapvalue = params.get(i);
            mapv = new HashMap<>();
            mapv.put("id", mapvalue.getId());
            mapv.put("tstime", simpleDateFormat.format(mapvalue.getTstime()));
            mapv.put("ycz", "xxx");
            mapv.put("cphm", mapvalue.getHphm());
            mapv.put("cdh", mapvalue.getCdxh());

            mapv.put("cpys", mapvalue.getCpys());
            mapv.put("rlzl", mapvalue.getRlzl());
            mapv.put("pdjg", mapvalue.getJudge());

            mapv.put("cojg", mapvalue.getCojg());
            mapv.put("co2jg", mapvalue.getCo2jg());
            mapv.put("hcjg", mapvalue.getHcjg());
            mapv.put("nojg", mapvalue.getNojg());
            valListData.add(mapv);
            rowIndex++;
        }
        excelValue.put("valList", valListData);
        //excelValue.put("valList", valList);
        // 模板路径
        String templateFile = ExcelServiceImpl.class.getClassLoader().getResource("doc/hour001.xls").getPath();
        TemplateExportParams exportTemplateParams = new TemplateExportParams(
                templateFile, sheetName);
        exportTemplateParams.setColForEach(true);
        POICacheManager.setFileLoader(new FileLoaderImpl());

   /*
	采用另一种导出方式  注意模板格式和导出的文件格式为xlsx
 Map<String, Object> map = new HashMap<>(5);
 //关注公众号【可为编程】回复【面试】领取最新面试题!!!
        map.put("map", excelValue);
        map.put(NormalExcelConstants.PARAMS, exportTemplateParams);
        map.put(NormalExcelConstants.FILE_NAME, fileName);
        PoiBaseView.render(map, request, response, "easypoiTemplateExcelView");*/
        
        Workbook book = ExcelExportUtil.exportExcel(exportTemplateParams, excelValue);
        Sheet sheet = book.getSheetAt(0);
        Row head = sheet.getRow(1);
        Cell headOne = head.getCell(0);
        CellStyle cellStyle = headOne.getCellStyle();
        //以下代码是自定义单元格所显示的样式设置
        String content="上传数据总数:15            缺失数据总数:2            数据完整率86.67%\t";
        addMergeRow(sheet, rowIndex, content, cellStyle);
        rowIndex++;
        content="制表人:                   审核人:                   审核时间:      　";
        addMergeRow( sheet, rowIndex, content, cellStyle);

        /*
        以下该部分代码将文件路径指定到了本地磁盘
        FileOutputStream fos = new FileOutputStream("D:\\down-info\\easypoi-test\\src\\test\\resources\\doc\\bb.xls");
        book.write(fos);
        fos.close();*/


        /*
         *以下该代码可以采用postman或者是直接在浏览器请求，直接以一个文件的形式下载下来
         * */
        EasypoiTemplateExcelView easypoiTemplateExcelView = new EasypoiTemplateExcelView();
        try {
            easypoiTemplateExcelView.out(
                    book, fileName, request, response
            );
        } catch (Exception ex) {

        }
    }
}

三、写适合自己的模板

因为指定的模板路径为实现类文件夹下，所以在实现类所对应模块的resources文件夹里新建了一个doc,然后里面放我们的模板。模板的定义如下：

一定要注意红色标出来的花括号，否则显示不出来对应的数据值。

四、 打jar包后找不到导出模板的解决方法

Jar包导出无法读取Excel模板，在本地运行可以，但是到了服务器就会报workbook的空指针异常。
主要原因有两点：

1.Linux和Window文件系统环境不一致。
2.springBoot项目打jar包后文件资源加载方式不同，打包后Spring试图访问文件系统路径，但无法访问jar中的路径。

解决方案如下：
关注公众号【可为编程】回复【面试】领取最新面试题!!!

采用 resource.getInputStream() 获取模板的文档流，重写到tomcat容器中并生成新的模板路径，按新的路径，导出excel即可。

 //Workbook book = ExcelExportUtil.exportExcel(exportTemplateParams, excelValue);
 //改成如下：
  String templateFile = ExcelExportUtils.convertTemplatePath("doc/hour001.xls");
//InputStream templateFile = EmergencyMaterialInputServiceImpl.class.getClassLoader().getResourceAsStream("doc/hour001.xls");
 TemplateExportParams exportTemplateParams = new TemplateExportParams(
                templateFile, sheetName);
 exportTemplateParams.setColForEach(true);
 POICacheManager.setFileLoader(new FileLoaderImpl());
 Workbook book = ExcelExportUtil.exportExcel(exportTemplateParams, excelValue);

附代码：

import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.stereotype.Component;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @Author zjc
 * @Description Excel导出方法
 */
@Component
public class ExcelExportUtils {
    /**
     * 模板路径处理，避免出现NPE
     * windows和linux系统不同，且SpringBoot的文件加载方式已有不同，不能简单的使用相对路径
     *
     * @param path excel模板的相对路径
     * @return 重写后的模板路径
     */
    public static String convertTemplatePath(String path) {
        // 如果是windows则直接返回
        if (System.getProperties().getProperty("os.name").contains("Windows")) {
            return path;
        }

        Resource resource = new ClassPathResource(path);
        FileOutputStream fileOutputStream = null;
        // 将模版文件写入到tomcat临时目录
        String folder = System.getProperty("catalina.home");
        File tempFile = new File(folder + File.separator + path);
        // 文件存在时不再写入
        if (tempFile.exists()) {
            return tempFile.getPath();
        }
        File parentFile = tempFile.getParentFile();
        // 判断父文件夹是否存在
        if (!parentFile.exists()) {
            parentFile.mkdirs();
        }
        try {
            BufferedInputStream inputStream = new BufferedInputStream(resource.getInputStream());
            fileOutputStream = new FileOutputStream(tempFile);
            byte[] buffer = new byte[10240];
            int len = 0;
            while ((len = inputStream.read(buffer)) != -1) {
                fileOutputStream.write(buffer, 0, len);
            }
        } catch (IOException e) {
            e.printStackTrace();

        } finally {
            if (fileOutputStream != null) {
                try {
                    fileOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return tempFile.getPath();
    }
**关注公众号【可为编程】回复【面试】领取最新面试题!!!**
}

导入表单数据

一、导入Controller

@ApiOperation(value = "批量导入应急物资数据")
    @RequestMapping(value = "/upload", method = RequestMethod.POST)
    //    @PreAuth("hasPermit('{}view')")
    public R upload(@RequestParam("file") MultipartFile multipartFile,
                         @ApiIgnore @LoginUser SysUser user) throws Exception {
        ImportParams params = new ImportParams();
        params.setHeadRows(1);
        params.setTitleRows(2);
        List<EmergencyMaterialInputEntity> factors = ExcelImportUtil.importExcel(multipartFile.getInputStream(),
                EmergencyMaterialInputEntity.class, params);
        EmergencyMaterialInputSaveDTO dto = new EmergencyMaterialInputSaveDTO();
//        this.setDTO(dto, user);
        emergencyMaterialInputService.upload(factors, dto);
        return R.success();
    }

二、ServiceImpl层

  public void upload(List<EmergencyMaterialInputEntity> factors, EmergencyMaterialInputSaveDTO dto) {
        List<EmergencyMaterialInput> emergencyMaterialInputs = new ArrayList<>();
        List<Map<String, Object>> stringList = baseMapper.selectManagerCode();
        for (EmergencyMaterialInputEntity factor : factors) {
            if (factor.getMaterialName() != null && !"".equals(factor.getMaterialName()) && factor.getMaterialType() != null && !"".equals(factor.getMaterialType())) {
                dto.setMaterialName(factor.getMaterialName() != null ? factor.getMaterialName() : "");
                dto.setMaterialType(factor.getMaterialType() != null ? factor.getMaterialType() : "");
                dto.setMaterialCount(factor.getMaterialCount() != null ? factor.getMaterialCount() : 0);
                dto.setMaterialUnit(factor.getMaterialUnit() != null ? factor.getMaterialUnit() : "");
                //根据用户名查询对应负责人编号
                for (Map<String, Object> map : stringList) {
                    if (factor.getManager() != null) {
                        if (factor.getManager().equals(map.get("manager"))) {
                            dto.setManagerCode(map.get("manager_code").toString());
                        }
                    }
                }
                dto.setInputTime(factor.getInputTime() != null ? factor.getInputTime() : LocalDateTime.now());
                dto.setValidityDate(factor.getValidityDate() != null ? factor.getValidityDate() : LocalDate.now());
                EmergencyMaterialInput model = BeanUtil.toBean(dto, getEntityClass());
                emergencyMaterialInputs.add(model);
            }
        }
        this.saveBatch(emergencyMaterialInputs);
    }

下载模板

    public void downloadModel(Map params, HttpServletRequest request, HttpServletResponse response) {
        String model = params.get("model").toString();
//        String templateFile = EnvironmentJsonServiceImpl.class.getClassLoader().getResource("doc/"+model+".xlsx")
//                .getPath();
        try {
            Resource resource = new ClassPathResource("doc/" + model + ".xls");
//            File file = resource.getFile();
            String filename = resource.getFilename();
            InputStream inputStream = resource.getInputStream();
            response.setHeader("content-type", "application/octet-stream;charset=UTF-8");
            response.setContentType("application/octet-stream;charset=UTF-8");
//            response.addHeader("Content-Length", String.valueOf(file.length()));
            OutputStream out = response.getOutputStream();
            //使用URLEncoder来防止文件名乱码或者读取错误
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
            int b = 0;
            byte[] buffer = new byte[1000000];
            while (b != -1) {
                b = inputStream.read(buffer);
                if (b != -1) {
                    out.write(buffer, 0, b);
                }
            }
            inputStream.close();
            out.close();
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

传入的map为json形式的模板名称，对应的doc地址在项目的resource/doc目录下。

欢迎感兴趣的小伙伴一起探讨学习知识，以上是个人的一些总结分享，如有错误的地方望各位留言指出，十分感谢。觉得有用的话别忘点赞、收藏、关注，手留余香！ 😗 😗 😗

这里是一个真诚的***青年技术交流QQ群:761374713***，不管你是大学生、社畜、想学习变成的其他人员，欢迎大家加入我们，一起成长，一起进步，真诚的欢迎你，不管是技术，还是人生，还是学习方法。有道无术，术亦可求，有术无道，止于术。

欢迎大家关注【可为编程】，成长，进步，编程，技术、掌握更多知识！