Execl模板导出复杂Execl文件

需求:

根据Execl模板文件导出Execl,填充红色框框部分.如下图:

资料参考位置:Java实现根据excel模板导出数据（适合导出结构复杂的excel）_名字看着办的博客-CSDN博客_java根据模板导出excel

1.添加pom

<!-- jxls poi -->
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls-poi</artifactId>
            <version>2.10.0</version>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls</artifactId>
            <version>2.10.0</version>
        </dependency>
        <dependency>
            <groupId>net.sf.jxls</groupId>
            <artifactId>jxls-core</artifactId>
            <version>1.0.6</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>

2.创建实体类, 修改模板文件, 字段对应Execl位置  ${} 处理

@Data
@AllArgsConstructor
@NoArgsConstructor
public class TestExecl implements Serializable {
    private static final long serialVersionUID = 1L;

    private String  num;
    private String  success;
    private String  fail;
    private String  date;

    public static TestExecl getExecl(){
        return new TestExecl(
                RandomUtil.randomString(5)
                ,RandomUtil.randomString(5)
                ,RandomUtil.randomString(5)
                ,"2008-08-08 20:00:00");
    }
}

 

 3.核心方法

public String testExecl(HttpServletResponse response) {
        //模拟单个数据
        TestExecl testExecl = new TestExecl("12","13","14","2022-05-05 15:15:16");

        //模拟集合数据
        List<TestExecl> list  = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            list.add(TestExecl.getExecl());
        }

        Map<String,Object> param = new HashMap<>();
        param.put("title","省份数据");
        param.put("testExecl",testExecl);
        param.put("list",list);
        try {
            TemplateExcelUtils.downLoadExcel("测试数据","test.xls",param,response);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "success";
    }

4.工具类

package com.zhang.utiles;

import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.ss.usermodel.Workbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.Map;

/**
 * @author : zhang sq
 * @date : 2022/6/13 9:08
 **/
public class TemplateExcelUtils {

    /**
     * 根据模板导出数据
     * @param fileName
     * @param sourcePath resource/template文件夹下路径
     * @param beanParams
     * @param response
     * @throws Exception
     */
    public static void downLoadExcel(String fileName, String sourcePath, Map<String, Object> beanParams, HttpServletResponse response)
            throws Exception {
        try{
            //写入到某个位置
            OutputStream os = getOutputStream2(fileName);
            //返回前端流形式
//            OutputStream os = getOutputStream(fileName,response);
            //读取模板
            InputStream is = TemplateExcelUtils.class.getClassLoader().getResourceAsStream("templates/"+sourcePath);
            XLSTransformer transformer = new XLSTransformer();
            //向模板中写入内容
            Workbook workbook = transformer.transformXLS(is, beanParams);
            //写入成功后转化为输出流
            workbook.write(os);
            os.flush();
            os.close();
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }
    }

    /**
     * 导出文件时为Writer生成OutputStream. 流形式返回调用这个
     * @param fileName 文件名
     * @param response response
     * @return ""
     */
    private static OutputStream getOutputStream(String fileName,
                                                HttpServletResponse response) throws Exception {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e) {
            throw new Exception("导出excel表格失败!", e);
        }
    }

    /**
     * 写入到某个位置 .调用此方法
     * @param fileName 文件名
     * @throws Exception
     */
    private static OutputStream getOutputStream2(String fileName) throws Exception {
        try {
            return new FileOutputStream(new File("C:\\Users\\17607\\Desktop\\"+fileName+".xls"));
        } catch (IOException e) {
            throw new Exception("导出excel表格失败!", e);
        }
    }

}

5.效果

 6.这块可能还会涉及到列或者行的合并处理, 后续有机会在补充吧,或者大佬知道怎么处理的话,评论区留下操作地址