java根据excel模板进行导出数据

女爷i 2023-10-12 15:21 54阅读 0赞

一、pom文件添加以下依赖

<dependency>
                <groupId>org.jxls</groupId>
                <artifactId>jxls-poi</artifactId>
                <version>2.10.0</version>
            </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>

二、添加util包

package com.risen.xmgl.util;
    
    import net.sf.jxls.transformer.XLSTransformer;
    import org.apache.poi.ss.usermodel.Workbook;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.net.URLEncoder;
    import java.util.Map;
    
    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 = getOutputStream(fileName,response);
                //读取模板
                InputStream is = TemplateExcelUtils.class.getClassLoader().getResourceAsStream("template/"+sourcePath);
                XLSTransformer transformer = new XLSTransformer();
                //向模板中写入内容
                Workbook workbook = transformer.transformXLS(is, beanParams);
                //写入成功后转化为输出流
                workbook.write(os);
            }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);
            }
        }
    }

三、在resources目录下添加template并添加xlsx模板

![50346d6b37b44e30a31a989fc29e8272.png][]

注意：xlsx模板使用$\{list.XXX\} XXX表示数据源list（map形式的list）的数据key值，如果list是对象形式的，那么就是该业务字段![7803d367d14844f9bff5884502f66e01.png][]

四、业务层使用：

List<ZssProjectExpertStore> byIdList = getZssProjectExpertStoreDao().findByIdList(Arrays.asList(uuid.split(",")));
    
    //给导出的序号赋值
    IntStream.range(0, byIdList.size()).forEach(i -> {
               byIdList.get(i).setZpesUnid(i + 1);
        });
    
       try {
            Map<String, Object> param = new HashMap<>();
                   param.put("title", "专家基础信息");
                   param.put("list", byIdList);//byIdList数据源
                    TemplateExcelUtils.downLoadExcel("专家基础信息", "专家基础信息归集表.xlsx", param, response);
              
          } catch (Exception e) {
                throw new RuntimeException(e);
               }

[50346d6b37b44e30a31a989fc29e8272.png]: https://img-blog.csdnimg.cn/50346d6b37b44e30a31a989fc29e8272.png
[7803d367d14844f9bff5884502f66e01.png]: https://img-blog.csdnimg.cn/7803d367d14844f9bff5884502f66e01.png