springboot整合freemarker根据模板导出excel

maven依赖

<dependency>
    <groupId>org.freemarker</groupId>
    <artifactId>freemarker</artifactId>
    <version>2.3.30</version>
</dependency>

<!--此jar包是用来将生成好的excel转成后缀为xlsx的文件，因为直接转出后缀为xlsx是不行的，再导入会报改文件还是xml文件不是xlsx文件 -->
<dependency>
    <groupId>spire</groupId>
    <artifactId>spirexls</artifactId>
    <version>1.0</version>
</dependency>

 

 

import com.manager.exception.ServiceException;
import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
import freemarker.template.TemplateException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.Map;

/**
 * @author yanjj
 * @ClassName HtmlToExcelUtil
 * @date 2022-11-07
 */
@Slf4j
public class HtmlToExcelUtil {

    public void createExcel(Map<String, Object> dataMap, String templateName, String fileName, HttpServletResponse response){
        log.info("{}：HTML开始生成excel...", fileName);
        response.setContentType("application/octet-stream;charset=utf-8");
        //保存的文件名,必须和页面编码一致,否则乱码
        String excelFileName = response.encodeURL(new String(fileName.getBytes(),StandardCharsets.ISO_8859_1));
        response.addHeader("Content-Disposition",   "attachment;filename=" + excelFileName);
        try(
                ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
                OutputStreamWriter oWriter = new OutputStreamWriter(outputStream, StandardCharsets.UTF_8);
                Writer out = new BufferedWriter(oWriter);
                InputStream inputStream = this.getClass().getResourceAsStream("/templates/".concat(templateName));
        ) {
            if (inputStream == null){
                throw new ServiceException("{}：HTML开始生成excel生成失败！",fileName);
            }
            createTemplate(new String(IOUtils.toByteArray(inputStream)))
.process(dataMap, out);
            //下面是重点进行格式转换，转成xlsx
            Workbook wb = new Workbook();
            wb.loadFromXml(new ByteArrayInputStream(outputStream.toByteArray()));
            wb.saveToStream(response.getOutputStream(),FileFormat.Version2013);
        }catch (IOException | TemplateException e) {
            e.printStackTrace();
            log.error("{}：产HTML开始生成excel失败：", fileName, e);
        }
        log.info("{}：HTML开始生成excel生成完成...", fileName);
    }
}

/**
 * 动态创建模板
 *
 * @param templateString
 * @return
 * @throws IOException
 */
public static Template createTemplate(String templateString) throws IOException {
    StringTemplateLoader stringLoader = new StringTemplateLoader();
    String id = String.valueOf(TEMPLATE_ID.incrementAndGet());
    stringLoader.putTemplate(id, templateString);
    Configuration cfg = new Configuration(Configuration.VERSION_2_3_30);
    cfg.setDefaultEncoding("utf-8");
    cfg.setTemplateLoader(stringLoader);
    return cfg.getTemplate(id);
}

调用

public static void main(String[] args) {
    Map<String, Object> resultMap = new HashMap<>();
    resultMap.put("company",company);
    resultMap.put("productList",productList);
    new HtmlToExcelUtil().createExcel(resultMap,"量化私募基金运行报表.xml","量化私募基金运行报表.xlsx",response);
}

因为我这个excel是两个sheet所以有两个key

第一个sheet是固定格式固定取值

${company.companyName!} //！是指非空才取值

第二个sheet是循环取值

<#if productList ??>
    <#list productList as data>
    <Row>
        <Cell ss:StyleID="s76">
            <Data ss:Type="String">${data.name!}</Data>
        </Cell>
        <Cell ss:StyleID="s76">
            <Data ss:Type="String">${data.age!}</Data>
        </Cell>
    </Row>
</#list>
<#else>
        <Row>
        <Cell ss:StyleID="s76">
            <Data ss:Type="String">无</Data>
        </Cell>
        <Cell ss:StyleID="s76">
            <Data ss:Type="String">无</Data></Cell>
        </Row>
</#if>