使用EasyPoi利用excel模板导出excel表格下载

分手后的思念是犯贱 2023-10-08 19:12 91阅读 0赞

前言:使用excel模板导出excel的好处在于可以事先在模板上定义颜色、格式等，适用于模板设计得比较灵活复杂的场景

#### 一.添加jar包 ####

<dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-base</artifactId>
                <version>3.2.0</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-web</artifactId>
                <version>3.2.0</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-annotation</artifactId>
                <version>3.2.0</version>
            </dependency>

#### 二.添加工具类 ####

package com.xinke.sunshine_ebid.common.utils;
    
    import cn.afterturn.easypoi.excel.ExcelExportUtil;
    import cn.afterturn.easypoi.excel.ExcelImportUtil;
    import cn.afterturn.easypoi.excel.entity.ExportParams;
    import cn.afterturn.easypoi.excel.entity.ImportParams;
    import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.web.multipart.MultipartFile;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.File;
    import java.io.IOException;
    import java.net.URLEncoder;
    import java.util.List;
    import java.util.Map;
    
    
    public class EasyPoiUtils {
        private EasyPoiUtils() {
        }
    
        public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
            try {
                fileName += ".xls";
                response.setCharacterEncoding("UTF-8");
                response.setHeader("content-Type", "application/vnd.ms-excel");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
                response.setHeader("FileName", URLEncoder.encode(fileName, "utf-8"));
                response.setHeader("Access-Control-Expose-Headers", "FileName");
                workbook.write(response.getOutputStream());
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    
        public static void downLoad2007Excel(String fileName, HttpServletResponse response, Workbook workbook) {
            try {
                fileName += ".xlsx";
                response.setCharacterEncoding("UTF-8");
                response.setHeader("content-Type", "application/vnd.ms-excel");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
                response.setHeader("FileName", URLEncoder.encode(fileName, "utf-8"));
                response.setHeader("Access-Control-Expose-Headers", "FileName");
                workbook.write(response.getOutputStream());
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    
        public static <T> void defaultExport(List<T> dataList, Class<?> clz, String fileName, HttpServletResponse response, ExportParams exportParams) {
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, clz, dataList);
            if (workbook != null) {
                downLoadExcel(fileName, response, workbook);
            }
        }
    
        public static <T> void exportExcel(List<T> dataList, String title, String sheetName, Class<?> clz, String fileName, boolean isCreateHeader, HttpServletResponse response) {
            ExportParams exportParams = new ExportParams(title, sheetName);
            exportParams.setCreateHeadRows(isCreateHeader);
            defaultExport(dataList, clz, fileName, response, exportParams);
        }
    
        public static <T> void exportExcel(List<T> dataList, String title, String sheetName, Class<?> clz, String fileName, HttpServletResponse response) {
            defaultExport(dataList, clz, fileName, response, new ExportParams(title, sheetName));
        }
    
        private static void defaultExport(List<Map<String, Object>> dataList, String fileName, HttpServletResponse response) {
            Workbook workbook = ExcelExportUtil.exportExcel(dataList, ExcelType.HSSF);
            if (workbook != null) {
                downLoadExcel(fileName, response, workbook);
            }
        }
    
        public static void exportExcel(List<Map<String, Object>> dataList, String fileName, HttpServletResponse response) {
            defaultExport(dataList, fileName, response);
        }
    
        public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> clz) {
            if (StringUtils.isBlank(filePath)) {
                return null;
            }
    
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
    
            try {
                return ExcelImportUtil.importExcel(new File(filePath), clz, params);
            } catch (Exception e) {
                throw new RuntimeException(e.getMessage());
            }
        }
    
        public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> clz) {
            if (file == null) {
                return null;
            }
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            try {
                return ExcelImportUtil.importExcel(file.getInputStream(), clz, params);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
    }

#### 三.制作模板 ####

![39f9aba5248b4bb98cc8b5ca45f8e036.png][]

将要替换的参数用\{ \{\}\}包括起来，集合类型的遍历数据用\{ \{!fe:集合名 t.参数名1 t.参数名2\}\}这种形式遍历，多sheet可使用，需要居中或者颜色设置等均可以在模板上直接修改

#### 四.代码应用 ####

集合类

package com.xinke.sunshine_ebid.dal.entity;
    
    import lombok.Data;
    
    @Data
    public class TestEntity {
        private String name;
        private String phone;
        private String address;
    }

控制层

package com.xinke.sunshine_ebid.webapp;
    
    import cn.afterturn.easypoi.excel.ExcelExportUtil;
    import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
    import com.xinke.sunshine_ebid.common.utils.EasyPoiUtils;
    import com.xinke.sunshine_ebid.service.TestService;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.web.bind.annotation.CrossOrigin;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import javax.annotation.Resource;
    import javax.servlet.http.HttpServletResponse;
    import java.util.Map;
    
    @CrossOrigin
    @RestController
    @RequestMapping(value = "/Test")
    public class TestController {
        @Resource
        private TestService testService;
    
        /**
         * 使用Excel模板导出excel表格
         * @param response
         * @throws Exception
         */
        @GetMapping("/test")
        public void test(HttpServletResponse response) throws Exception{
            // 使用map来封装参数
            Map<String, Object> map = testService.test();
            // 模板路径
            TemplateExportParams params = new TemplateExportParams("fileRecord/template/excel/test1.xlsx", true);
            String fileName = "导出来的报表名称";
            Workbook workbook = ExcelExportUtil.exportExcel(params, map);
            workbook.setForceFormulaRecalculation(true);
            EasyPoiUtils.downLoad2007Excel(fileName, response, workbook);
        }
    }

业务逻辑层

package com.xinke.sunshine_ebid.service;
    
    import com.xinke.sunshine_ebid.common.utils.CustomXWPFDocument;
    import com.xinke.sunshine_ebid.common.utils.WordUtil;
    import com.xinke.sunshine_ebid.dal.entity.TestEntity;
    import freemarker.template.Configuration;
    import freemarker.template.Template;
    import org.apache.poi.xwpf.usermodel.*;
    import org.springframework.stereotype.Service;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.File;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.io.Writer;
    import java.net.URLEncoder;
    import java.nio.charset.StandardCharsets;
    import java.nio.file.Files;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    @Service
    public class TestService {
    
        public Map<String,Object> test() throws Exception{
            // 封装参数
            Map<String, Object> params = new HashMap<>();
            params.put("date","2023年3月7日");
            List<TestEntity> list = new ArrayList<>();
            TestEntity test = new TestEntity();
            test.setName("瓜");
            test.setPhone("18888888888");
            test.setAddress("PD");
            list.add(test);
            TestEntity test2 = new TestEntity();
            test2.setName("瓜2");
            test2.setPhone("1999999999");
            test2.setAddress("PD2");
            list.add(test2);
            params.put("list",list);
            return params;
        }
    }

#### 五.测试 ####

localhost:本地端口/Test/test

导出成功

![c743dd0804d44453a43e85a68c7335b3.png][]

[39f9aba5248b4bb98cc8b5ca45f8e036.png]: https://img-blog.csdnimg.cn/img_convert/39f9aba5248b4bb98cc8b5ca45f8e036.png
[c743dd0804d44453a43e85a68c7335b3.png]: https://img-blog.csdnimg.cn/img_convert/c743dd0804d44453a43e85a68c7335b3.png