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

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 l.util;import net.ansformer.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.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 = ClassLoader().getResourceAsStream("template/"+sourcePath);XLSTransformer transformer = new XLSTransformer();//向模板中写入内容Workbook workbook = ansformXLS(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 = de(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");OutputStream();} catch (IOException e) {throw new Exception("导出excel表格失败!", e);}}
}

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

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

 四、业务层使用：

List<ZssProjectExpertStore> byIdList = getZssProjectExpertStoreDao().findByIdList(Arrays.asList(uuid.split(",")));//给导出的序号赋值
IntStream.range(0, byIdList.size()).forEach(i -> {(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);}