Excel模板导出

核心依赖包

        <!-- poi相关依赖包 start -->
        <dependency>
            <groupId>opensymphony</groupId>
            <artifactId>webwork</artifactId>
            <version>2.2.2</version>
        </dependency>
 
        <dependency>
            <groupId>org.beanshell</groupId>
            <artifactId>bsh-core</artifactId>
            <version>2.0b4</version>
        </dependency>
 
        <!-- poi relay common
      ps 会自动引入commons-collections-3.2.2 -->
        <dependency>
            <groupId>commons-beanutils</groupId>
            <artifactId>commons-beanutils</artifactId>
            <version>1.9.3</version>
        </dependency>
 
        <!-- poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.16</version>
        </dependency>
 
 
        <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>
        <!-- poi相关依赖包 end   -->

1. 模板工具类

package com.hd.gam.utils;
 
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;
 
/**
 * @author wangzhuangzhuang
 * @Description:
 * @date 2022-05-09 14:53
 */
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("excel/"+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 + ".xlsx");
			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);
		}
	}
 
}

2. 模板位置

3. 模板实例

4. 核心实现层代码

    @Override
    public void workRecordExcelExport(SubsidyDeclareRecord subsidyDeclareRecord, HttpServletResponse response) {
        SubsidyDeclareRecord sdr = new SubsidyDeclareRecord();
        List<SubsidyDeclareRecord> listRecord = gamDeclareMapper.queryRecordList(sdr);
        if (listRecord != null && listRecord.size() > 0) {
            SubsidyDeclareRecord record = listRecord.get(0);
            record.setServiceTypeName(ServiceTypeEnum.of(record.getServiceType()).getName());
            SubsidyDeclare subsidyDeclare = new SubsidyDeclare();
            subsidyDeclare.setDeclareNo(sdr.getDeclareNo());
            List<SubsidyDeclare> gamDeclareRels = gamDeclareRelMapper.querySubsidyList(subsidyDeclare);
            int i = 0;
            for (SubsidyDeclare d : gamDeclareRels) {
                d.setNo(++i);
                d.setWorkName(sysBaseService.ofWorkType(d.getWorkType()));
                d.setWorkTime(new SimpleDateFormat("yyyy-MM-dd").format(d.getWorkDate()));
                if (!d.getWorkTon().equals("0.00")) {
                    d.setWorkLoad(d.getWorkTon());
                } else {
                    d.setWorkLoad(d.getMArea());
                }
            }
            // 作业量
            double sumWorkload = gamDeclareRels.stream().mapToDouble(e -> Double.parseDouble(e.getWorkLoad())).sum();
            // 作业补贴金额
            double sumSubsidyMoney = gamDeclareRels.stream().mapToDouble(e -> Double.parseDouble(e.getSubsidyMoney())).sum();
            log.info("[SubsidyDeclareServiceImpl][workRecordExcelExport] sumWorkload: {}, sumSubsidyMoney: {}", sumWorkload, sumSubsidyMoney);
            log.info("[SubsidyDeclareServiceImpl][workRecordExcelExport] itemList: {}", gamDeclareRels);
            try{
                Map<String, Object> param = new HashMap<>();
                param.put("itemList", gamDeclareRels);
                param.put("sumWorkload", sumWorkload);
                param.put("sumSubsidyMoney", sumSubsidyMoney);
                TemplateExcelUtils.downLoadExcel("糖料蔗机械化作业补贴申报表", "糖料蔗机械化作业补贴申报表模板.xlsx", param, response);
            }catch(Exception e){
                throw new ServiceException("Excel Import Error!");
            }
        }
    }