[Java知识库]freemarker+poi实现动态生成excel文件及解析excel文件

freemarker+poi动态生成excel文件

?pom文件配置：

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
        </dependency>

配置文件：

# 模板后缀名
spring.freemarker.suffix:.ftl
# 文档类型
spring.freemarker.content-type:text/html
# 页面编码
spring.freemarker.charset:UTF-8
# 页面缓存
spring.freemarker.cache:false
# 模板路径
spring.freemarker.template-loader-path:classpath:/templates/

生成模板ftl文件步骤：

1、新建excel文件：test.xlsx

?2、把文件另存为xml文件，即test.xml

?3、把test.xml修改文件后缀名，改为test.ftl

?4、ftl文件格式化在线 XML 格式化 | 菜鸟工具

?5、test.ftl文件中添加动态数据参数

            <#if userList??>
                <#list userList as user>
             	 <Row>
                     <Cell>
                         <Data ss:Type="String">${user.name}</Data>
                     </Cell>
                     <Cell>
                         <Data ss:Type="Number">${user.age}</Data>
                     </Cell>
                     <Cell ss:StyleID="s51" ss:HRef="mailto:${user.email}">
                         <Data ss:Type="String">${user.email}</Data>
                     </Cell>
                 </Row>
                </#list>
            </#if>

最终把test.ftl文件放到resources/templates下，freemarker默认读取模板文件的位置

下面使用springboot+freemarker动态生成并在线下载excel文件

package com.example.stu1.freemark;

import lombok.Data;

/**
 * @Author yangcai
 * @create 2022/7/1 16:43
 */
@Data
public class User {
    private int age;
    private String name;
    private String email;
}

import freemarker.template.Template;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.view.freemarker.FreeMarkerConfigurer;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.PrintWriter;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author yangcai
 * @create 2022/7/1 16:43
 */
@RestController
public class UserController {

    @Resource
    FreeMarkerConfigurer freemarkerConfigurer;

    @GetMapping("/index")
    public void index(HttpServletResponse response) {
        String userName ="userName";
        String fileName =  userName +"测试导出的" + ".xlsx";
        Map<String,Object> resultMap= getList();
        downloadDoc(freemarkerConfigurer, "/test.ftl", resultMap, fileName, response);
    }
    Map<String,Object> getList(){
        Map<String,Object>  map = new HashMap<>();
        List<User> userList = new ArrayList<>();
        User user1 = new User();
        user1.setAge(26);
        user1.setEmail("707656893@qq.com");
        user1.setName("张无忌");
        userList.add(user1);
        User user2 = new User();
        user2.setAge(27);
        user2.setEmail("111111111@qq.com");
        user2.setName("逍遥子");
        userList.add(user2);
        User user3 = new User();
        user3.setAge(28);
        user3.setEmail("222222222@qq.com");
        user3.setName("乔峰");
        userList.add(user3);
        map.put("userList",userList);
        return map;
    }
    public  void downloadDoc(FreeMarkerConfigurer freemarkerConfigurer, String modelPath, Map<String, Object> data,
                                   String fileName, HttpServletResponse response) {
        try {
            Template template = freemarkerConfigurer.getConfiguration().getTemplate(modelPath);
            response.reset();
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            PrintWriter writer = response.getWriter();
            template.process(data, writer);
            writer.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

请求：http://localhost:8080/index

会生成一个xlsx文件：文件内容如下

注意：这个excel文件本质还是一个xml文件，用WPS能正常打开，但是用office打开会提示文件格式不正确

?所以要真正的生成excel，还需要使用poi，poi的目的就是把xml文件转换成excel文件

pom引入poi相关jar：

        <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>cn.eonml</groupId>
            <artifactId>freemarker-excel</artifactId>
            <version>0.1.5</version>
        </dependency>

自定义工具类：

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//
package com.example.stu1.freemark;

import com.yongjiu.commons.utils.XmlReader;
import com.yongjiu.dto.freemarker.input.ExcelImageInput;
import com.yongjiu.dto.freemarker.input.FreemarkerInput;
import com.yongjiu.entity.excel.Cell;
import com.yongjiu.entity.excel.CellRangeAddressEntity;
import com.yongjiu.entity.excel.Column;
import com.yongjiu.entity.excel.Data;
import com.yongjiu.entity.excel.Row;
import com.yongjiu.entity.excel.Style;
import com.yongjiu.entity.excel.Table;
import com.yongjiu.entity.excel.Worksheet;
import com.yongjiu.entity.excel.Style.Border;
import com.yongjiu.util.ColorUtil;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateExceptionHandler;
import java.awt.image.BufferedImage;
import java.io.BufferedWriter;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.io.SAXReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;

public class FreeMarkerTemplateUtil {
    private static final Logger log = LoggerFactory.getLogger(FreeMarkerTemplateUtil.class);

    public FreeMarkerTemplateUtil() {
    }

    public static void exportToFile(Map dataMap, String templateName, String templateFilePath, String fileFullPath) {
        try {
            File file = new File(fileFullPath);
            FileUtils.forceMkdirParent(file);
            FileOutputStream outputStream = new FileOutputStream(file);
            exportToStream(dataMap, templateName, templateFilePath, outputStream);
        } catch (Exception var6) {
            var6.printStackTrace();
        }

    }

    public static void exportToStream(Map dataMap, String templateName, String templateFilePath, FileOutputStream outputStream) {
        try {
            Template template = getTemplate(templateName, templateFilePath);
            OutputStreamWriter outputWriter = new OutputStreamWriter(outputStream, "UTF-8");
            Writer writer = new BufferedWriter(outputWriter);
            template.process(dataMap, writer);
            writer.flush();
            writer.close();
            outputStream.close();
        } catch (Exception var7) {
            var7.printStackTrace();
        }

    }

    public static void exportImageExcel(String excelFilePath, FreemarkerInput freemarkerInput) {
        try {
            File file = new File(excelFilePath);
            FileUtils.forceMkdirParent(file);
            FileOutputStream outputStream = new FileOutputStream(file);
            createImageExcleToStream(freemarkerInput, outputStream);
            FileUtils.forceDelete(new File(freemarkerInput.getXmlTempFile() + freemarkerInput.getFileName() + ".xml"));
            log.info("导出成功,导出到目录：" + file.getCanonicalPath());
        } catch (Exception var4) {
            var4.printStackTrace();
        }

    }

    public static void exportImageExcelNew(String excelFilePath, FreemarkerInput freemarkerInput) {
        try {
            File file = new File(excelFilePath);
            FileUtils.forceMkdirParent(file);
            FileOutputStream outputStream = new FileOutputStream(file);
            createExcelToStream(freemarkerInput, outputStream);
            FileUtils.forceDelete(new File(freemarkerInput.getXmlTempFile() + freemarkerInput.getFileName() + ".xml"));
            log.info("导出成功,导出到目录：" + file.getCanonicalPath());
        } catch (Exception var4) {
            var4.printStackTrace();
        }

    }

    public static void exportImageExcelNew(HttpServletResponse response, FreemarkerInput freemarkerInput) {
        try {
            OutputStream outputStream = response.getOutputStream();
            response.reset();
            response.setContentType("application/msexcel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=\"" + new String((freemarkerInput.getFileName() + ".xlsx").getBytes("GBK"), "ISO8859-1") + "\"");
            response.setHeader("Response-Type", "Download");
            createExcelToStream(freemarkerInput, outputStream);
            FileUtils.forceDelete(new File(freemarkerInput.getXmlTempFile() + freemarkerInput.getFileName() + ".xml"));
        } catch (Exception var3) {
            var3.printStackTrace();
        }

    }

    public static void exportImageExcel(HttpServletResponse response, FreemarkerInput freemarkerInput) {
        try {
            OutputStream outputStream = response.getOutputStream();
            response.reset();
            response.setContentType("application/msexcel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=\"" + new String((freemarkerInput.getFileName() + ".xls").getBytes("GBK"), "ISO8859-1") + "\"");
            response.setHeader("Response-Type", "Download");
            createImageExcleToStream(freemarkerInput, outputStream);
            FileUtils.forceDelete(new File(freemarkerInput.getXmlTempFile() + freemarkerInput.getFileName() + ".xml"));
        } catch (Exception var3) {
            var3.printStackTrace();
        }

    }

    private static Template getTemplate(String templateName, String filePath) throws IOException {
        Configuration configuration = new Configuration(Configuration.VERSION_2_3_28);
        configuration.setDefaultEncoding("UTF-8");
        configuration.setTemplateUpdateDelayMilliseconds(0L);
        configuration.setEncoding(Locale.CHINA, "UTF-8");
        configuration.setTemplateExceptionHandler(TemplateExceptionHandler.RETHROW_HANDLER);
        configuration.setClassForTemplateLoading(FreeMarkerTemplateUtil.class, filePath);
        configuration.setOutputEncoding("UTF-8");
        return configuration.getTemplate(templateName, "UTF-8");
    }

    private static void createImageExcleToStream(FreemarkerInput freemarkerInput, OutputStream outputStream) {
        BufferedWriter out = null;

        try {
            Template template = getTemplate(freemarkerInput.getTemplateName(), freemarkerInput.getTemplateFilePath());
            File tempXMLFile = new File(freemarkerInput.getXmlTempFile() + freemarkerInput.getFileName() + ".xml");
            FileUtils.forceMkdirParent(tempXMLFile);
            out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(tempXMLFile), "UTF-8"));
            template.process(freemarkerInput.getDataMap(), out);
            if (log.isDebugEnabled()) {
                log.debug("1.完成将文本数据导入到XML文件中");
            }

            SAXReader reader = new SAXReader();
            Document document = reader.read(tempXMLFile);
            Map<String, Style> styleMap = readXmlStyle(document);
            log.debug("2.完成解析XML中样式信息");
            List<Worksheet> worksheets = readXmlWorksheet(document);
            if (log.isDebugEnabled()) {
                log.debug("3.开始将XML信息写入Excel，数据为：" + worksheets.toString());
            }

            HSSFWorkbook wb = new HSSFWorkbook();
            Iterator var10 = worksheets.iterator();

            while(var10.hasNext()) {
                Worksheet worksheet = (Worksheet)var10.next();
                HSSFSheet sheet = wb.createSheet(worksheet.getName());
                Table table = worksheet.getTable();
                List<Row> rows = table.getRows();
                List<Column> columns = table.getColumns();
                int createRowIndex;
                if (columns != null && columns.size() > 0) {
                    createRowIndex = 0;

                    for(int i = 0; i < columns.size(); ++i) {
                        Column column = (Column)columns.get(i);
                        createRowIndex = getCellWidthIndex(createRowIndex, i, column.getIndex());
                        sheet.setColumnWidth(createRowIndex, (int)column.getWidth() * 50);
                    }
                }

                createRowIndex = 0;
                List<CellRangeAddressEntity> cellRangeAddresses = new ArrayList();

                for(int rowIndex = 0; rowIndex < rows.size(); ++rowIndex) {
                    Row rowInfo = (Row)rows.get(rowIndex);
                    if (rowInfo != null) {
                        createRowIndex = getIndex(createRowIndex, rowIndex, rowInfo.getIndex());
                        HSSFRow row = sheet.createRow(createRowIndex);
                        if (rowInfo.getHeight() != null) {
                            Integer height = rowInfo.getHeight() * 20;
                            row.setHeight(height.shortValue());
                        }

                        List<Cell> cells = rowInfo.getCells();
                        if (!CollectionUtils.isEmpty(cells)) {
                            int startIndex = 0;

                            for(int cellIndex = 0; cellIndex < cells.size(); ++cellIndex) {
                                Cell cellInfo = (Cell)cells.get(cellIndex);
                                if (cellInfo != null) {
                                    startIndex = getIndex(startIndex, cellIndex, cellInfo.getIndex());
                                    HSSFCell cell = row.createCell(startIndex);
                                    String styleID = cellInfo.getStyleID();
                                    Style style = (Style)styleMap.get(styleID);
                                    CellStyle dataStyle = wb.createCellStyle();
                                    setBorder(style, dataStyle);
                                    setAlignment(style, dataStyle);
                                    setValue((HSSFWorkbook)wb, cellInfo, (HSSFCell)cell, style, dataStyle);
                                    setCellColor(style, dataStyle);
                                    cell.setCellStyle(dataStyle);
                                    if (cellInfo.getComment() != null) {
                                        Data data = cellInfo.getComment().getData();
                                        Comment comment = sheet.createDrawingPatriarch().createCellComment(new HSSFClientAnchor(0, 0, 0, 0, (short)3, 3, (short)5, 6));
                                        comment.setString(new HSSFRichTextString(data.getText()));
                                        cell.setCellComment(comment);
                                    }

                                    startIndex = getCellRanges(createRowIndex, cellRangeAddresses, startIndex, cellInfo, style);
                                }
                            }
                        }
                    }
                }

                addCellRange((HSSFSheet)sheet, cellRangeAddresses);
            }

            log.debug("4.开始写入图片：" + freemarkerInput.getExcelImageInputs());
            if (!CollectionUtils.isEmpty(freemarkerInput.getExcelImageInputs())) {
                writeImageToExcel(freemarkerInput.getExcelImageInputs(), wb);
            }

            log.debug("5.完成写入图片：" + freemarkerInput.getExcelImageInputs());
            wb.write(outputStream);
            outputStream.close();
        } catch (Exception var39) {
            var39.printStackTrace();
            log.error("导出excel异常：" + var39.getMessage());
        } finally {
            try {
                out.close();
            } catch (Exception var38) {
            }

        }

    }

    private static void createExcelToStream(FreemarkerInput freemarkerInput, OutputStream outputStream) {
        BufferedWriter out = null;

        try {
            Template template = getTemplate(freemarkerInput.getTemplateName(), freemarkerInput.getTemplateFilePath());
            File tempXMLFile = new File(freemarkerInput.getXmlTempFile() + freemarkerInput.getFileName() + ".xml");
            FileUtils.forceMkdirParent(tempXMLFile);
            out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(tempXMLFile), "UTF-8"));
            template.process(freemarkerInput.getDataMap(), out);
            if (log.isDebugEnabled()) {
                log.debug("1.完成将文本数据导入到XML文件中");
            }

            SAXReader reader = new SAXReader();
            Document document = reader.read(tempXMLFile);
            Map<String, Style> styleMap = readXmlStyle(document);
            log.debug("2.完成解析XML中样式信息");
            List<Worksheet> worksheets = readXmlWorksheet(document);
            if (log.isDebugEnabled()) {
                log.debug("3.开始将XML信息写入Excel，数据为：" + worksheets.toString());
            }

            XSSFWorkbook wb = new XSSFWorkbook();
            Iterator var10 = worksheets.iterator();

            while(var10.hasNext()) {
                Worksheet worksheet = (Worksheet)var10.next();
                XSSFSheet sheet = wb.createSheet(worksheet.getName());
                Table table = worksheet.getTable();
                List<Row> rows = table.getRows();
                List<Column> columns = table.getColumns();
                int createRowIndex;
                if (columns != null && columns.size() > 0) {
                    createRowIndex = 0;

                    for(int i = 0; i < columns.size(); ++i) {
                        Column column = (Column)columns.get(i);
                        createRowIndex = getCellWidthIndex(createRowIndex, i, column.getIndex());
                        sheet.setColumnWidth(createRowIndex, (int)column.getWidth() * 50);
                    }
                }
                for(int i = 0 ; i<1000;i++){
                    sheet.setColumnWidth(i,3000);
                }
                createRowIndex = 0;
                List<CellRangeAddressEntity> cellRangeAddresses = new ArrayList();

                for(int rowIndex = 0; rowIndex < rows.size(); ++rowIndex) {
                    Row rowInfo = (Row)rows.get(rowIndex);
                    if (rowInfo != null) {
                        createRowIndex = getIndex(createRowIndex, rowIndex, rowInfo.getIndex());
                        XSSFRow row = sheet.createRow(createRowIndex);
                        if (rowInfo.getHeight() != null) {
                            Integer height = rowInfo.getHeight() * 20;
                            row.setHeight(height.shortValue());
                        }

                        List<Cell> cells = rowInfo.getCells();
                        if (!CollectionUtils.isEmpty(cells)) {
                            int startIndex = 0;

                            for(int cellIndex = 0; cellIndex < cells.size(); ++cellIndex) {
                                Cell cellInfo = (Cell)cells.get(cellIndex);
                                if (cellInfo != null) {
                                    startIndex = getIndex(startIndex, cellIndex, cellInfo.getIndex());
                                    XSSFCell cell = row.createCell(startIndex);
                                    String styleID = cellInfo.getStyleID();
                                    Style style = (Style)styleMap.get(styleID);
                                    CellStyle dataStyle = wb.createCellStyle();
                                    setBorder(style, dataStyle);
                                    setAlignment(style, dataStyle);
                                    setValue((XSSFWorkbook)wb, cellInfo, (XSSFCell)cell, style, dataStyle);
                                    setCellColor(style, dataStyle);
                                    cell.setCellStyle(dataStyle);
                                    if (cellInfo.getComment() != null) {
                                        Data data = cellInfo.getComment().getData();
                                        Comment comment = sheet.createDrawingPatriarch().createCellComment(new XSSFClientAnchor(0, 0, 0, 0, 3, 3, 5, 6));
                                        comment.setString(new XSSFRichTextString(data.getText()));
                                        cell.setCellComment(comment);
                                    }

                                    startIndex = getCellRanges(createRowIndex, cellRangeAddresses, startIndex, cellInfo, style);
                                }
                            }
                        }
                    }
                }

                addCellRange((XSSFSheet)sheet, cellRangeAddresses);
            }

            log.debug("4.开始写入图片：" + freemarkerInput.getExcelImageInputs());
            if (!CollectionUtils.isEmpty(freemarkerInput.getExcelImageInputs())) {
                writeImageToExcel(freemarkerInput.getExcelImageInputs(), wb);
            }

            log.debug("5.完成写入图片：" + freemarkerInput.getExcelImageInputs());
            wb.write(outputStream);
            outputStream.close();
        } catch (Exception var39) {
            var39.printStackTrace();
            log.error("导出excel异常：" + var39.getMessage());
        } finally {
            try {
                out.close();
            } catch (Exception var38) {
            }

        }

    }

    public static Map<String, Style> readXmlStyle(Document document) {
        Map<String, Style> styleMap = XmlReader.getStyle(document);
        return styleMap;
    }

    public static List<Worksheet> readXmlWorksheet(Document document) {
        List<Worksheet> worksheets = XmlReader.getWorksheet(document);
        return worksheets;
    }

    private static int getIndex(int columnIndex, int i, Integer index) {
        if (index != null) {
            columnIndex = index - 1;
        }

        if (index == null && columnIndex != 0) {
            ++columnIndex;
        }

        if (index == null && columnIndex == 0) {
            columnIndex = i;
        }

        return columnIndex;
    }

    private static int getCellWidthIndex(int columnIndex, int i, Integer index) {
        if (index != null) {
            columnIndex = index;
        }

        if (index == null && columnIndex != 0) {
            ++columnIndex;
        }

        if (index == null && columnIndex == 0) {
            columnIndex = i;
        }

        return columnIndex;
    }

    private static void setBorder(Style style, CellStyle dataStyle) {
        if (style != null && style.getBorders() != null) {
            for(int k = 0; k < style.getBorders().size(); ++k) {
                Border border = (Border)style.getBorders().get(k);
                if (border != null) {
                    if ("Bottom".equals(border.getPosition())) {
                        dataStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                        dataStyle.setBorderBottom(BorderStyle.THIN);
                    }

                    if ("Left".equals(border.getPosition())) {
                        dataStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                        dataStyle.setBorderLeft(BorderStyle.THIN);
                    }

                    if ("Right".equals(border.getPosition())) {
                        dataStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
                        dataStyle.setBorderRight(BorderStyle.THIN);
                    }

                    if ("Top".equals(border.getPosition())) {
                        dataStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
                        dataStyle.setBorderTop(BorderStyle.THIN);
                    }
                }
            }
        }

    }

    private static void writeImageToExcel(List<ExcelImageInput> excelImageInputs, HSSFWorkbook wb) throws IOException {
        BufferedImage bufferImg = null;
        if (!CollectionUtils.isEmpty(excelImageInputs)) {
            Iterator var3 = excelImageInputs.iterator();

            while(var3.hasNext()) {
                ExcelImageInput excelImageInput = (ExcelImageInput)var3.next();
                Sheet sheet = wb.getSheetAt(excelImageInput.getSheetIndex());
                if (sheet != null) {
                    Drawing patriarch = sheet.createDrawingPatriarch();
                    HSSFClientAnchor anchor = excelImageInput.getAnchorXls();
                    anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
                    String imagePath = excelImageInput.getImgPath();
                    ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                    bufferImg = ImageIO.read(new File(imagePath));
                    String imageType = imagePath.substring(imagePath.lastIndexOf(".") + 1, imagePath.length());
                    ImageIO.write(bufferImg, imageType, byteArrayOut);
                    patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), 5));
                }
            }
        }

    }

    private static void writeImageToExcel(List<ExcelImageInput> excelImageInputs, XSSFWorkbook wb) throws IOException {
        BufferedImage bufferImg = null;
        if (!CollectionUtils.isEmpty(excelImageInputs)) {
            Iterator var3 = excelImageInputs.iterator();

            while(var3.hasNext()) {
                ExcelImageInput excelImageInput = (ExcelImageInput)var3.next();
                Sheet sheet = wb.getSheetAt(excelImageInput.getSheetIndex());
                if (sheet != null) {
                    Drawing patriarch = sheet.createDrawingPatriarch();
                    XSSFClientAnchor anchor = excelImageInput.getAnchorXlsx();
                    anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
                    String imagePath = excelImageInput.getImgPath();
                    ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                    bufferImg = ImageIO.read(new File(imagePath));
                    String imageType = imagePath.substring(imagePath.lastIndexOf(".") + 1, imagePath.length());
                    ImageIO.write(bufferImg, imageType, byteArrayOut);
                    patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), 5));
                }
            }
        }

    }

    private static void addCellRange(HSSFSheet sheet, List<CellRangeAddressEntity> cellRangeAddresses) {
        if (!CollectionUtils.isEmpty(cellRangeAddresses)) {
            Iterator var2 = cellRangeAddresses.iterator();

            while(true) {
                CellRangeAddressEntity cellRangeAddressEntity;
                CellRangeAddress cellRangeAddress;
                do {
                    if (!var2.hasNext()) {
                        return;
                    }

                    cellRangeAddressEntity = (CellRangeAddressEntity)var2.next();
                    cellRangeAddress = cellRangeAddressEntity.getCellRangeAddress();
                    sheet.addMergedRegion(cellRangeAddress);
                } while(CollectionUtils.isEmpty(cellRangeAddressEntity.getBorders()));

                for(int k = 0; k < cellRangeAddressEntity.getBorders().size(); ++k) {
                    Border border = (Border)cellRangeAddressEntity.getBorders().get(k);
                    if (border != null) {
                        if ("Bottom".equals(border.getPosition())) {
                            RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
                        }

                        if ("Left".equals(border.getPosition())) {
                            RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
                        }

                        if ("Right".equals(border.getPosition())) {
                            RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
                        }

                        if ("Top".equals(border.getPosition())) {
                            RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
                        }
                    }
                }
            }
        }
    }

    private static void addCellRange(XSSFSheet sheet, List<CellRangeAddressEntity> cellRangeAddresses) {
        if (!CollectionUtils.isEmpty(cellRangeAddresses)) {
            Iterator var2 = cellRangeAddresses.iterator();

            while(true) {
                CellRangeAddressEntity cellRangeAddressEntity;
                CellRangeAddress cellRangeAddress;
                do {
                    if (!var2.hasNext()) {
                        return;
                    }

                    cellRangeAddressEntity = (CellRangeAddressEntity)var2.next();
                    cellRangeAddress = cellRangeAddressEntity.getCellRangeAddress();
                    sheet.addMergedRegion(cellRangeAddress);
                } while(CollectionUtils.isEmpty(cellRangeAddressEntity.getBorders()));

                for(int k = 0; k < cellRangeAddressEntity.getBorders().size(); ++k) {
                    Border border = (Border)cellRangeAddressEntity.getBorders().get(k);
                    if (border != null) {
                        if ("Bottom".equals(border.getPosition())) {
                            RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
                        }

                        if ("Left".equals(border.getPosition())) {
                            RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
                        }

                        if ("Right".equals(border.getPosition())) {
                            RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
                        }

                        if ("Top".equals(border.getPosition())) {
                            RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
                        }
                    }
                }
            }
        }
    }

    private static void setAlignment(Style style, CellStyle dataStyle) {
        if (style != null && style.getAlignment() != null) {
            String horizontal = style.getAlignment().getHorizontal();
            if (!ObjectUtils.isEmpty(horizontal)) {
                if ("Left".equals(horizontal)) {
                    dataStyle.setAlignment(HorizontalAlignment.LEFT);
                } else if ("Center".equals(horizontal)) {
                    dataStyle.setAlignment(HorizontalAlignment.CENTER);
                } else {
                    dataStyle.setAlignment(HorizontalAlignment.RIGHT);
                }
            }

            String vertical = style.getAlignment().getVertical();
            if (!ObjectUtils.isEmpty(vertical)) {
                if ("Top".equals(vertical)) {
                    dataStyle.setVerticalAlignment(VerticalAlignment.TOP);
                } else if ("Center".equals(vertical)) {
                    dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                } else if ("Bottom".equals(vertical)) {
                    dataStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
                } else if ("JUSTIFY".equals(vertical)) {
                    dataStyle.setVerticalAlignment(VerticalAlignment.JUSTIFY);
                } else {
                    dataStyle.setVerticalAlignment(VerticalAlignment.DISTRIBUTED);
                }
            }

            String wrapText = style.getAlignment().getWrapText();
            if (!ObjectUtils.isEmpty(wrapText)) {
                dataStyle.setWrapText(true);
            }
        }

    }

    private static void setCellColor(Style style, CellStyle dataStyle) {
        if (style != null && style.getInterior() != null) {
            String color = style.getInterior().getColor();
            if (color == null) {
                color = "#FFFFFF";
            }

            Integer[] rgb = ColorUtil.hex2Rgb(color);
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
            HSSFPalette palette = hssfWorkbook.getCustomPalette();
            HSSFColor paletteColor = palette.findSimilarColor(rgb[0], rgb[1], rgb[2]);
            dataStyle.setFillForegroundColor(paletteColor.getIndex());
            dataStyle.setFillBackgroundColor(paletteColor.getIndex());
            if ("Solid".equals(style.getInterior().getPattern())) {
                dataStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            }
        }

    }

    private static int getCellRanges(int createRowIndex, List<CellRangeAddressEntity> cellRangeAddresses, int startIndex, Cell cellInfo, Style style) {
        if (cellInfo.getMergeAcross() != null || cellInfo.getMergeDown() != null) {
            CellRangeAddress cellRangeAddress = null;
            int length;
            int i;
            if (cellInfo.getMergeAcross() != null && cellInfo.getMergeDown() != null) {
                length = startIndex;
                if (cellInfo.getMergeAcross() != 0) {
                    length = startIndex + cellInfo.getMergeAcross();
                }

                i = createRowIndex;
                if (cellInfo.getMergeDown() != 0) {
                    i = createRowIndex + cellInfo.getMergeDown();
                }

                cellRangeAddress = new CellRangeAddress(createRowIndex, i, (short)startIndex, (short)length);
            } else if (cellInfo.getMergeAcross() != null && cellInfo.getMergeDown() == null) {
                if (cellInfo.getMergeAcross() != 0) {
                    length = startIndex + cellInfo.getMergeAcross();
                    cellRangeAddress = new CellRangeAddress(createRowIndex, createRowIndex, (short)startIndex, (short)length);
                }
            } else if (cellInfo.getMergeDown() != null && cellInfo.getMergeAcross() == null && cellInfo.getMergeDown() != 0) {
                length = createRowIndex + cellInfo.getMergeDown();
                cellRangeAddress = new CellRangeAddress(createRowIndex, length, (short)startIndex, (short)startIndex);
            }

            if (cellInfo.getMergeAcross() != null) {
                length = cellInfo.getMergeAcross();

                for(i = 0; i < length; ++i) {
                    ++startIndex;
                }
            }

            CellRangeAddressEntity cellRangeAddressEntity = new CellRangeAddressEntity();
            cellRangeAddressEntity.setCellRangeAddress(cellRangeAddress);
            if (style != null && style.getBorders() != null) {
                cellRangeAddressEntity.setBorders(style.getBorders());
            }

            cellRangeAddresses.add(cellRangeAddressEntity);
        }

        return startIndex;
    }

    private static void setValue(XSSFWorkbook wb, Cell cellInfo, XSSFCell cell, Style style, CellStyle dataStyle) {
        if (cellInfo.getData() != null) {
            XSSFFont font = wb.createFont();
            String color;
            Integer[] rgb;
            HSSFWorkbook hssfWorkbook;
            HSSFPalette palette;
            HSSFColor paletteColor;
            if (style != null && style.getFont() != null) {
                color = style.getFont().getColor();
                if (color == null) {
                    color = "#000000";
                }

                rgb = ColorUtil.hex2Rgb(color);
                hssfWorkbook = new HSSFWorkbook();
                palette = hssfWorkbook.getCustomPalette();
                paletteColor = palette.findSimilarColor(rgb[0], rgb[1], rgb[2]);
                font.setColor(paletteColor.getIndex());
            }

            if (!ObjectUtils.isEmpty(cellInfo.getData().getType()) && "Number".equals(cellInfo.getData().getType())) {
                cell.setCellType(CellType.NUMERIC);
            }

            if (style != null && style.getFont().getBold() > 0) {
                font.setBold(true);
            }

            if (style != null && !ObjectUtils.isEmpty(style.getFont().getFontName())) {
                font.setFontName(style.getFont().getFontName());
            }

            if (style != null && style.getFont().getSize() > 0.0D) {
                font.setFontHeightInPoints((short)((int)style.getFont().getSize()));
            }

            if (cellInfo.getData().getFont() != null) {
                if (cellInfo.getData().getFont().getBold() > 0) {
                    font.setBold(true);
                }

                if ("Number".equals(cellInfo.getData().getType())) {
                    cell.setCellValue((double)Float.parseFloat(cellInfo.getData().getFont().getText()));
                } else {
                    cell.setCellValue(cellInfo.getData().getFont().getText());
                }

                if (!ObjectUtils.isEmpty(cellInfo.getData().getFont().getCharSet())) {
                    font.setCharSet(Integer.valueOf(cellInfo.getData().getFont().getCharSet()));
                }
            } else if ("Number".equals(cellInfo.getData().getType())) {
                if (!ObjectUtils.isEmpty(cellInfo.getData().getText())) {
                    cell.setCellValue((double)Float.parseFloat(cellInfo.getData().getText().replaceAll(",", "")));
                }
            } else {
                cell.setCellValue(cellInfo.getData().getText());
            }

            if (style != null && style.getNumberFormat() != null) {
                color = style.getFont().getColor();
                if (color == null) {
                    color = "#000000";
                }

                rgb = ColorUtil.hex2Rgb(color);
                hssfWorkbook = new HSSFWorkbook();
                palette = hssfWorkbook.getCustomPalette();
                paletteColor = palette.findSimilarColor(rgb[0], rgb[1], rgb[2]);
                font.setColor(paletteColor.getIndex());
                if ("0%".equals(style.getNumberFormat().getFormat())) {
                    XSSFDataFormat format = wb.createDataFormat();
                    dataStyle.setDataFormat(format.getFormat(style.getNumberFormat().getFormat()));
                } else {
                    dataStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
                }
            }

            dataStyle.setFont(font);
        }

    }

    private static void setValue(HSSFWorkbook wb, Cell cellInfo, HSSFCell cell, Style style, CellStyle dataStyle) {
        if (cellInfo.getData() != null) {
            HSSFFont font = wb.createFont();
            String color;
            Integer[] rgb;
            HSSFWorkbook hssfWorkbook;
            HSSFPalette palette;
            HSSFColor paletteColor;
            if (style != null && style.getFont() != null) {
                color = style.getFont().getColor();
                if (color == null) {
                    color = "#000000";
                }

                rgb = ColorUtil.hex2Rgb(color);
                hssfWorkbook = new HSSFWorkbook();
                palette = hssfWorkbook.getCustomPalette();
                paletteColor = palette.findSimilarColor(rgb[0], rgb[1], rgb[2]);
                font.setColor(paletteColor.getIndex());
            }

            if (!ObjectUtils.isEmpty(cellInfo.getData().getType()) && "Number".equals(cellInfo.getData().getType())) {
                cell.setCellType(CellType.NUMERIC);
            }

            if (style != null && style.getFont().getBold() > 0) {
                font.setBold(true);
            }

            if (style != null && !ObjectUtils.isEmpty(style.getFont().getFontName())) {
                font.setFontName(style.getFont().getFontName());
            }

            if (style != null && style.getFont().getSize() > 0.0D) {
                font.setFontHeightInPoints((short)((int)style.getFont().getSize()));
            }

            if (cellInfo.getData().getFont() != null) {
                if (cellInfo.getData().getFont().getBold() > 0) {
                    font.setBold(true);
                }

                if ("Number".equals(cellInfo.getData().getType())) {
                    cell.setCellValue((double)Float.parseFloat(cellInfo.getData().getFont().getText()));
                } else {
                    cell.setCellValue(cellInfo.getData().getFont().getText());
                }

                if (!ObjectUtils.isEmpty(cellInfo.getData().getFont().getCharSet())) {
                    font.setCharSet(Integer.valueOf(cellInfo.getData().getFont().getCharSet()));
                }
            } else if ("Number".equals(cellInfo.getData().getType())) {
                if (!ObjectUtils.isEmpty(cellInfo.getData().getText())) {
                    cell.setCellValue((double)Float.parseFloat(cellInfo.getData().getText().replaceAll(",", "")));
                }
            } else {
                cell.setCellValue(cellInfo.getData().getText());
            }

            if (style != null && style.getNumberFormat() != null) {
                color = style.getFont().getColor();
                if (color == null) {
                    color = "#000000";
                }

                rgb = ColorUtil.hex2Rgb(color);
                hssfWorkbook = new HSSFWorkbook();
                palette = hssfWorkbook.getCustomPalette();
                paletteColor = palette.findSimilarColor(rgb[0], rgb[1], rgb[2]);
                font.setColor(paletteColor.getIndex());
                if ("0%".equals(style.getNumberFormat().getFormat())) {
                    HSSFDataFormat format = wb.createDataFormat();
                    dataStyle.setDataFormat(format.getFormat(style.getNumberFormat().getFormat()));
                } else {
                    dataStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
                }
            }
            dataStyle.setFont(font);
        }
    }
}

package com.example.stu1.freemark;

import com.yongjiu.dto.freemarker.input.FreemarkerInput;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author yangcai
 * @create 2022/7/1 16:43
 */
@RestController
public class UserController1 {

    @GetMapping("/index")
    public void index(HttpServletResponse response) {
        try{
            String fileName = URLEncoder.encode("测试导出", "UTF-8");
            Map<String,Object> resultMap= getList();
            FreemarkerInput freemarkerInput = new FreemarkerInput();
            freemarkerInput.setTemplateFilePath("/templates");
            freemarkerInput.setTemplateName("test.ftl");
            freemarkerInput.setXmlTempFile(System.getProperty("java.io.tmpdir"));
            freemarkerInput.setFileName(fileName);
            freemarkerInput.setDataMap(resultMap);
            FreeMarkerTemplateUtil.exportImageExcelNew(response, freemarkerInput);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    Map<String,Object> getList(){
        Map<String,Object>  map = new HashMap<>();
        List<User> userList = new ArrayList<>();
        User user1 = new User();
        user1.setAge(26);
        user1.setEmail("707656893@qq.com");
        user1.setName("张无忌");
        userList.add(user1);
        User user2 = new User();
        user2.setAge(27);
        user2.setEmail("111111111@qq.com");
        user2.setName("逍遥子");
        userList.add(user2);
        User user3 = new User();
        user3.setAge(28);
        user3.setEmail("222222222@qq.com");
        user3.setName("乔峰");
        userList.add(user3);
        map.put("userList",userList);
        return map;
    }
}

此时生成的xlsx文件是真正的excel文件，能被office正常的打开

poi解析excel文件

    public static  Function<UploadExcelObject,List<Map<String, IndexDataPo>>> getExcelDataOne = uploadExcelObject->{
        try(FileInputStream fileInputStream = new FileInputStream(uploadExcelObject.getFileUrl())) {
            Workbook workbook = null;
            if (uploadExcelObject.getFileUrl().endsWith(".xls")) {
                try {
                    workbook = new HSSFWorkbook(fileInputStream);
                } catch (OfficeXmlFileException e) {
                    try {
                        workbook = new XSSFWorkbook(fileInputStream);
                    } catch (Exception e1) {
                        log.error(e.getMessage());

                    }
                } catch (IOException e) {
                    throw new CommonException("109",e.getMessage());
                }
            } else if (uploadExcelObject.getFileUrl().endsWith(".xlsx")) {
                try {
                    workbook = new XSSFWorkbook(fileInputStream);
                } catch (Exception e) {
                    e.printStackTrace();
                    throw new CommonException("110",e.getMessage());
                }
            }
            try {
                int sheetNo = workbook.getNumberOfSheets();
                List<Map<String, IndexDataPo>> list = Lists.newArrayList();
                log.info(String.format("此文档共有%d多少页",sheetNo));
                for(int i=0 ; i< sheetNo ;i++){
                    Sheet sheet = workbook.getSheetAt(0);
                    List<String> headerList = checkAndGetHeader(sheet,TemplateTypeEnum.ONE_DATE.getCode(),uploadExcelObject.getIndexCodeList());
                    for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
                        XSSFRow row = (XSSFRow) sheet.getRow(j);
                        if(row!=null) {
                            if(isRowEmpty(row)){
                                continue;
                            }
                            IndexDataPo dto = new IndexDataPo();
                            for (int k = 0; k < sheet.getRow(1).getPhysicalNumberOfCells(); k++) {
                                String value = null;
                                Cell cell = row.getCell(k);
                                if(k==0){
                                    if("".equals(cell.toString()) || cell.toString() == ""){
                                        throw new CommonException("101","没有输入时间");
                                    }
                                    if(DateUtil.isCellDateFormatted(cell)){
                                        dto.setDataDate(cell.getDateCellValue());
                                    }
                                }else{
                                    Map<String, IndexDataPo> map = Maps.newHashMap();
                                    if(cell != null){
                                        value =  getCellValue(cell);
                                    }
                                    if(StringUtils.isEmpty(value)){
                                        dto.setDataValue(null);
                                    }else{
                                        dto.setDataValue(Double.valueOf(value));
                                    }
                                    dto.setIndexCode(headerList.get(k));
                                    map.put(DateUtils.getDateLong(dto.getDataDate()), dto);
                                    list.add(map);
                                    dto = new IndexDataPo(dto.getDataDate());
                                }
                            }
                        }
                    }
                }
                return  list;
            } catch (Exception e) {
                log.error(e.getMessage(),e);
                throw new CommonException("解析excel错误:","解析excel错误"+e.getMessage());
            }
        } catch (IOException e) {
            log.error(e.getMessage(),e);
        }
        return null;
    };

?