Java实现根据excel模板导出数据（适合导出结构复杂的excel）

根据模板导出excel适用于表格结构复杂的数据导出，需要提前定义好excel模板，设置好变量，然后导出数据到excel模板中。

在idea中创建一个excel文件

打开创建好的文件province.xls，开始定义模板，设置变量用${}形式，此处的变量要和后边java代码中的model类的字段要一致，这样才能把具体数据输出到excel模板对应的位置。

导入依赖

<!-- 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>

model类

import lombok.Data;

@Data
public class AreaModel {

    private Integer no;

    private Integer areaCode;

    private String areaName;

    private Integer level;
}

Util类

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;

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

 业务类

	@Override
    public void exportTemplateProvinceAreaData(HttpServletResponse response){
        List<Area> areas = areaMapper.queryProvinceArea();  // 此处是我本地项目的mapper，是为了获取省份数据
        List<AreaModel> result = new ArrayList<>();
        areas.stream().forEach(area -> {
            AreaModel areaModel = new AreaModel();
            BeanUtils.copyProperties(area,areaModel);
            result.add(areaModel);
        });
        IntStream.range(0, result.size()).forEach(i -> {
            result.get(i).setNo(i+1);
        });
        try {
            Map<String,Object> param = new HashMap<>();
            param.put("title","省份数据");
            param.put("list",result);
            TemplateExcelUtils.downLoadExcel("省份数据","province.xls",param,response);
        } catch (Exception e) {
            throw new ServiceException(ResultCode.EXPORT_ERROR);
        }
    }

这是我业务层areaMapper.queryProvinceArea();所返回的省份数据

controller层

@GetMapping("/exportTemplateData")
    public void exportTemplateData(HttpServletResponse response){
        areaService.exportTemplateProvinceAreaData(response);
    }

返回的excel

到这里根据模板导出就做好了。上面是一个比较简单的模板，也可以定义复杂结构的模板

导出的数据。

注意：如果运行时报以下错，org.apache.poi.openxml4j.exceptions:Your InputStream was neither an OLE2 stream, nor an OOXML stream则需要加依赖：

<plugins>
<!-- 让maven不编译xls文件，但仍将其打包 -->
    <plugin>
           <groupId>org.apache.maven.plugins</groupId>
           <artifactId>maven-resources-plugin</artifactId>
           <configuration>
               <nonFilteredFileExtensions>
                   <nonFilteredFileExtension>xls</nonFilteredFileExtension>
               </nonFilteredFileExtensions>
           </configuration>
    </plugin>
</plugins>