jxls Excel表格导出（ 模板导出多个sheet）

使用模板来导出excel表格

1.使用jxls-core jar包来实现（jxls-core不支持POI4以上的版本）

1.1 maven

<dependency>
    <groupId>net.sf.jxls</groupId>
    <artifactId>jxls-core</artifactId>
    <version>1.0.6</version>
</dependency>
//打包时对像xlsx文件过滤
<plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-resources-plugin</artifactId>
    <configuration>
        <nonFilteredFileExtensions>
            <nonFilteredFileExtension>xlsx</nonFilteredFileExtension>
            <nonFilteredFileExtension>xls</nonFilteredFileExtension>
        </nonFilteredFileExtensions>
    </configuration>
</plugin>

1.2Java代码

@Override
public void exportMaterialBomExcel(String bomId, HttpServletRequest request, HttpServletResponse response) throws IOException, InvalidFormatException {

    //获取模板文件位置
    URL url = this.getClass().getClassLoader().getResource("bom/bom.xlsx");
    InputStream is = new FileInputStream(url.getFile());
    //获取sheet页数据
    List<Object> objectList = new ArrayList<>();
    HashMap<String, Object> param = new HashMap<>();
    List<String> nameList = new ArrayList<>();

   
    List<BomManufactureTitle> titleList = bomManufactureTitleService.getBomManufactureTitleName(bomId);
    for (int i = 0; i < titleList.size(); i++) {
        nameList.add(titleList.get(i).getName());
      
        List<BomApproval> approvalList = bomApprovalService.listByWrapper(new EntityWrapper<BomApproval>().eq("BOM_ID", titleList.get(i).getId()));
    
        List<BomManufactureMaterial> materialList = bomManufactureMaterialService.listByWrapper(new EntityWrapper<BomManufactureMaterial>().
                eq("MBOM_CODE", titleList.get(i).getCode()).ge("MAX_VERSION", titleList.get(i).getVersion()).le("MIN_VERSION", titleList.get(i).getVersion()).orderBy("FEED_NO"));

        List<Map<String, Object>> returnMap = new ArrayList<>();
        Map<String, Object> map = new HashMap<>();
        map.put("entity", titleList.get(i));
        map.put("approval", approvalList.size() == 0 ? null : approvalList.get(0));
        map.put("materialList", materialList);

        returnMap.add(map);
        objectList.add(returnMap);

        //不同的sheet页使用不同的数据
        if (i != 0) {
            param.put("returnMap" + i, returnMap);
        }
    }

    XLSTransformer transformer = new XLSTransformer();
    Workbook workbook = transformer.transformMultipleSheetsList(is, objectList, nameList, "returnMap", param, 0);
    this.setResponse(response);
    workbook.write(response.getOutputStream());

}

private void setResponse(HttpServletResponse response) {
    response.reset();
    response.setContentType("application/octet-stream; charset=utf-8");
    response.setHeader("Content-Disposition", "attachment; filename=" + "xlsxExport.xlsx");
}

1.3模板

2.jxls2.x 来实现（支持POI4以上的版本）

maven依赖

<dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls</artifactId>
            <version>2.8.1</version>
            <exclusions>
                <exclusion>
                    <groupId>ch.qos.logback</groupId>
                    <artifactId>logback-classic</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>ch.qos.logback</groupId>
                    <artifactId>logback-core</artifactId>
                </exclusion>
                <exclusion>
                    <artifactId>slf4j-api</artifactId>
                    <groupId>org.slf4j</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls-poi</artifactId>
            <version>2.8.1</version>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                </exclusion>
                <exclusion>
                    <artifactId>slf4j-api</artifactId>
                    <groupId>org.slf4j</groupId>
                </exclusion>
            </exclusions>
        </dependency>

@Override
public void exportMaterialBomExcel(final String bomId, final HttpServletResponse response) throws IOException, InvalidFormatException, Exception {
    //获取模板文件
    final InputStream is = this.getInputStream();

    final Map<String, Object> returnMap = new HashMap<>();
    final SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    final List<String> nameList = new ArrayList<>();
    //工艺数据
    final List<BomManufactureTitle> titleList = bomManufactureTitleService.getBomManufactureTitleName(bomId);
    titleList.forEach(entity -> {
        nameList.add(entity.getName());
        //格式化时间
        entity.setCreateTime(format.format(entity.getCreateDate()));
        //审批数据
        final List<BomApproval> approvalList = bomApprovalService.listByWrapper(new EntityWrapper<BomApproval>().eq("BOM_ID", entity.getId()));
        entity.setApprovalList(approvalList.size() == 0 ? null : approvalList.get(0));
        //物料详情数据
        entity.setMaterialList(bomManufactureMaterialService.listByWrapper(new EntityWrapper<BomManufactureMaterial>().
                eq("MBOM_CODE", entity.getCode()).ge("MAX_VERSION", entity.getVersion()).le("MIN_VERSION", entity.getVersion()).orderBy("FEED_NO")));
    });

    returnMap.put("titleList", titleList);
    //将文件放置到客户端下载
    this.setResponse(response);
    //excel表格导出
    this.exportExcel(is, response.getOutputStream(), returnMap);

}


private InputStream getInputStream() throws FileNotFoundException {
    final URL url = this.getClass().getClassLoader().getResource("bom/bom.xls");
    return new FileInputStream(url.getFile());
}

private void setResponse(final HttpServletResponse response) {
    response.reset();
    response.setContentType("application/octet-stream; charset=utf-8");
    response.setHeader("Content-Disposition", "attachment; filename=" + "xlsxExport.xls");
}

public static void exportExcel(final InputStream is, final OutputStream os, final Map<String, Object> model) throws IOException {
    final Context context = PoiTransformer.createInitialContext();
    if (model != null) {
        for (final String key : model.keySet()) {
            context.putVar(key, model.get(key));
        }
    }
    final JxlsHelper jxlsHelper = JxlsHelper.getInstance();
    final Transformer transformer = jxlsHelper.createTransformer(is, os);
    //必须要这个，否者表格函数统计会错乱
    jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);

}

2.1模板

jxls2.x版本中的模板是通过使用注解的方法来实现循环和分多个sheet页的