Excel模板导出

核心依赖包

        
        
            opensymphony
            webwork
            2.2.2
        

        
            org.beanshell
            bsh-core
            2.0b4
        

        
        
            commons-beanutils
            commons-beanutils
            1.9.3
        

        
        
            org.apache.poi
            poi
            3.16
        
        
            org.apache.poi
            poi-ooxml
            3.16
        
        
            org.apache.poi
            poi-ooxml-schemas
            3.16
        
        
            org.apache.poi
            poi-scratchpad
            3.16
        


        
            org.jxls
            jxls-poi
            2.10.0
        
        
            org.jxls
            jxls
            2.10.0
        
        
            net.sf.jxls
            jxls-core
            1.0.6
        
        

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