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

一、pom文件添加以下依赖

<!-- jxls poi -->
    <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模板

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

四、业务层使用：

List 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);
       }