• freemarker+poi实现动态生成excel文件及解析excel文件

    freemarker+poi动态生成excel文件

     pom文件配置：

    1.         <dependency>
    2.             <groupId>org.springframework.boot</groupId>
    3.             <artifactId>spring-boot-starter-freemarker</artifactId>
    4.         </dependency>

    配置文件：

    1. # 模板后缀名
    2. spring.freemarker.suffix:.ftl
    3. # 文档类型
    4. spring.freemarker.content-type:text/html
    5. # 页面编码
    6. spring.freemarker.charset:UTF-8
    7. # 页面缓存
    8. spring.freemarker.cache:false
    9. # 模板路径
    10. 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文件中添加动态数据参数

    1.             <#if userList??>
    2.                 <#list userList as user>
    3.              	 <Row>
    4.                      <Cell>
    5.                          <Data ss:Type="String">${user.name}</Data>
    6.                      </Cell>
    7.                      <Cell>
    8.                          <Data ss:Type="Number">${user.age}</Data>
    9.                      </Cell>
    10.                      <Cell ss:StyleID="s51" ss:HRef="mailto:${user.email}">
    11.                          <Data ss:Type="String">${user.email}</Data>
    12.                      </Cell>
    13.                  </Row>
    14.                 </#list>
    15.             </#if>

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

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

    1. package com.example.stu1.freemark;
    2.  
    3. import lombok.Data;
    4.  
    5. /**
    6.  * @Author yangcai
    7.  * @create 2022/7/1 16:43
    8.  */
    9. @Data
    10. public class User {
    11.     private int age;
    12.     private String name;
    13.     private String email;
    14. }
    1. import freemarker.template.Template;
    2. import org.springframework.web.bind.annotation.GetMapping;
    3. import org.springframework.web.bind.annotation.RestController;
    4. import org.springframework.web.servlet.view.freemarker.FreeMarkerConfigurer;
    5. import javax.annotation.Resource;
    6. import javax.servlet.http.HttpServletResponse;
    7. import java.io.PrintWriter;
    8. import java.net.URLEncoder;
    9. import java.util.ArrayList;
    10. import java.util.HashMap;
    11. import java.util.List;
    12. import java.util.Map;
    13.  
    14. /**
    15.  * @Author yangcai
    16.  * @create 2022/7/1 16:43
    17.  */
    18. @RestController
    19. public class UserController {
    20.  
    21.     @Resource
    22.     FreeMarkerConfigurer freemarkerConfigurer;
    23.  
    24.     @GetMapping("/index")
    25.     public void index(HttpServletResponse response) {
    26.         String userName ="userName";
    27.         String fileName =  userName +"测试导出的" + ".xlsx";
    28.         Map<String,Object> resultMap= getList();
    29.         downloadDoc(freemarkerConfigurer, "/test.ftl", resultMap, fileName, response);
    30.     }
    31.     Map<String,Object> getList(){
    32.         Map<String,Object>  map = new HashMap<>();
    33.         List<User> userList = new ArrayList<>();
    34.         User user1 = new User();
    35.         user1.setAge(26);
    36.         user1.setEmail("707656893@qq.com");
    37.         user1.setName("张无忌");
    38.         userList.add(user1);
    39.         User user2 = new User();
    40.         user2.setAge(27);
    41.         user2.setEmail("111111111@qq.com");
    42.         user2.setName("逍遥子");
    43.         userList.add(user2);
    44.         User user3 = new User();
    45.         user3.setAge(28);
    46.         user3.setEmail("222222222@qq.com");
    47.         user3.setName("乔峰");
    48.         userList.add(user3);
    49.         map.put("userList",userList);
    50.         return map;
    51.     }
    52.     public  void downloadDoc(FreeMarkerConfigurer freemarkerConfigurer, String modelPath, Map<String, Object> data,
    53.                                    String fileName, HttpServletResponse response) {
    54.         try {
    55.             Template template = freemarkerConfigurer.getConfiguration().getTemplate(modelPath);
    56.             response.reset();
    57.             response.setContentType("application/octet-stream;charset=utf-8");
    58.             response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
    59.             PrintWriter writer = response.getWriter();
    60.             template.process(data, writer);
    61.             writer.flush();
    62.         } catch (Exception e) {
    63.             e.printStackTrace();
    64.         }
    65.     }
    66. }

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

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

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

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

    pom引入poi相关jar：

    1.         <dependency>
    2.             <groupId>org.apache.poi</groupId>
    3.             <artifactId>poi</artifactId>
    4.             <version>3.16</version>
    5.         </dependency>
    6.         <dependency>
    7.             <groupId>org.apache.poi</groupId>
    8.             <artifactId>poi-ooxml</artifactId>
    9.             <version>3.16</version>
    10.         </dependency>
    11.         <dependency>
    12.             <groupId>cn.eonml</groupId>
    13.             <artifactId>freemarker-excel</artifactId>
    14.             <version>0.1.5</version>
    15.         </dependency>

    自定义工具类：

    1. //
    2. // Source code recreated from a .class file by IntelliJ IDEA
    3. // (powered by Fernflower decompiler)
    4. //
    5. package com.example.stu1.freemark;
    6.  
    7. import com.yongjiu.commons.utils.XmlReader;
    8. import com.yongjiu.dto.freemarker.input.ExcelImageInput;
    9. import com.yongjiu.dto.freemarker.input.FreemarkerInput;
    10. import com.yongjiu.entity.excel.Cell;
    11. import com.yongjiu.entity.excel.CellRangeAddressEntity;
    12. import com.yongjiu.entity.excel.Column;
    13. import com.yongjiu.entity.excel.Data;
    14. import com.yongjiu.entity.excel.Row;
    15. import com.yongjiu.entity.excel.Style;
    16. import com.yongjiu.entity.excel.Table;
    17. import com.yongjiu.entity.excel.Worksheet;
    18. import com.yongjiu.entity.excel.Style.Border;
    19. import com.yongjiu.util.ColorUtil;
    20. import freemarker.template.Configuration;
    21. import freemarker.template.Template;
    22. import freemarker.template.TemplateExceptionHandler;
    23. import java.awt.image.BufferedImage;
    24. import java.io.BufferedWriter;
    25. import java.io.ByteArrayOutputStream;
    26. import java.io.File;
    27. import java.io.FileOutputStream;
    28. import java.io.IOException;
    29. import java.io.OutputStream;
    30. import java.io.OutputStreamWriter;
    31. import java.io.Writer;
    32. import java.util.ArrayList;
    33. import java.util.Iterator;
    34. import java.util.List;
    35. import java.util.Locale;
    36. import java.util.Map;
    37. import javax.imageio.ImageIO;
    38. import javax.servlet.http.HttpServletResponse;
    39. import org.apache.commons.io.FileUtils;
    40. import org.apache.poi.hssf.usermodel.HSSFCell;
    41. import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
    42. import org.apache.poi.hssf.usermodel.HSSFDataFormat;
    43. import org.apache.poi.hssf.usermodel.HSSFFont;
    44. import org.apache.poi.hssf.usermodel.HSSFPalette;
    45. import org.apache.poi.hssf.usermodel.HSSFRichTextString;
    46. import org.apache.poi.hssf.usermodel.HSSFRow;
    47. import org.apache.poi.hssf.usermodel.HSSFSheet;
    48. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    49. import org.apache.poi.hssf.util.HSSFColor;
    50. import org.apache.poi.ss.usermodel.BorderStyle;
    51. import org.apache.poi.ss.usermodel.CellStyle;
    52. import org.apache.poi.ss.usermodel.CellType;
    53. import org.apache.poi.ss.usermodel.Comment;
    54. import org.apache.poi.ss.usermodel.Drawing;
    55. import org.apache.poi.ss.usermodel.FillPatternType;
    56. import org.apache.poi.ss.usermodel.HorizontalAlignment;
    57. import org.apache.poi.ss.usermodel.IndexedColors;
    58. import org.apache.poi.ss.usermodel.Sheet;
    59. import org.apache.poi.ss.usermodel.VerticalAlignment;
    60. import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
    61. import org.apache.poi.ss.util.CellRangeAddress;
    62. import org.apache.poi.ss.util.RegionUtil;
    63. import org.apache.poi.xssf.usermodel.XSSFCell;
    64. import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
    65. import org.apache.poi.xssf.usermodel.XSSFDataFormat;
    66. import org.apache.poi.xssf.usermodel.XSSFFont;
    67. import org.apache.poi.xssf.usermodel.XSSFRichTextString;
    68. import org.apache.poi.xssf.usermodel.XSSFRow;
    69. import org.apache.poi.xssf.usermodel.XSSFSheet;
    70. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    71. import org.dom4j.Document;
    72. import org.dom4j.io.SAXReader;
    73. import org.slf4j.Logger;
    74. import org.slf4j.LoggerFactory;
    75. import org.springframework.util.CollectionUtils;
    76. import org.springframework.util.ObjectUtils;
    77.  
    78. public class FreeMarkerTemplateUtil {
    79.     private static final Logger log = LoggerFactory.getLogger(FreeMarkerTemplateUtil.class);
    80.  
    81.     public FreeMarkerTemplateUtil() {
    82.     }
    83.  
    84.     public static void exportToFile(Map dataMap, String templateName, String templateFilePath, String fileFullPath) {
    85.         try {
    86.             File file = new File(fileFullPath);
    87.             FileUtils.forceMkdirParent(file);
    88.             FileOutputStream outputStream = new FileOutputStream(file);
    89.             exportToStream(dataMap, templateName, templateFilePath, outputStream);
    90.         } catch (Exception var6) {
    91.             var6.printStackTrace();
    92.         }
    93.  
    94.     }
    95.  
    96.     public static void exportToStream(Map dataMap, String templateName, String templateFilePath, FileOutputStream outputStream) {
    97.         try {
    98.             Template template = getTemplate(templateName, templateFilePath);
    99.             OutputStreamWriter outputWriter = new OutputStreamWriter(outputStream, "UTF-8");
    100.             Writer writer = new BufferedWriter(outputWriter);
    101.             template.process(dataMap, writer);
    102.             writer.flush();
    103.             writer.close();
    104.             outputStream.close();
    105.         } catch (Exception var7) {
    106.             var7.printStackTrace();
    107.         }
    108.  
    109.     }
    110.  
    111.     public static void exportImageExcel(String excelFilePath, FreemarkerInput freemarkerInput) {
    112.         try {
    113.             File file = new File(excelFilePath);
    114.             FileUtils.forceMkdirParent(file);
    115.             FileOutputStream outputStream = new FileOutputStream(file);
    116.             createImageExcleToStream(freemarkerInput, outputStream);
    117.             FileUtils.forceDelete(new File(freemarkerInput.getXmlTempFile() + freemarkerInput.getFileName() + ".xml"));
    118.             log.info("导出成功,导出到目录：" + file.getCanonicalPath());
    119.         } catch (Exception var4) {
    120.             var4.printStackTrace();
    121.         }
    122.  
    123.     }
    124.  
    125.     public static void exportImageExcelNew(String excelFilePath, FreemarkerInput freemarkerInput) {
    126.         try {
    127.             File file = new File(excelFilePath);
    128.             FileUtils.forceMkdirParent(file);
    129.             FileOutputStream outputStream = new FileOutputStream(file);
    130.             createExcelToStream(freemarkerInput, outputStream);
    131.             FileUtils.forceDelete(new File(freemarkerInput.getXmlTempFile() + freemarkerInput.getFileName() + ".xml"));
    132.             log.info("导出成功,导出到目录：" + file.getCanonicalPath());
    133.         } catch (Exception var4) {
    134.             var4.printStackTrace();
    135.         }
    136.  
    137.     }
    138.  
    139.     public static void exportImageExcelNew(HttpServletResponse response, FreemarkerInput freemarkerInput) {
    140.         try {
    141.             OutputStream outputStream = response.getOutputStream();
    142.             response.reset();
    143.             response.setContentType("application/msexcel;charset=UTF-8");
    144.             response.setHeader("Content-Disposition", "attachment;filename=\"" + new String((freemarkerInput.getFileName() + ".xlsx").getBytes("GBK"), "ISO8859-1") + "\"");
    145.             response.setHeader("Response-Type", "Download");
    146.             createExcelToStream(freemarkerInput, outputStream);
    147.             FileUtils.forceDelete(new File(freemarkerInput.getXmlTempFile() + freemarkerInput.getFileName() + ".xml"));
    148.         } catch (Exception var3) {
    149.             var3.printStackTrace();
    150.         }
    151.  
    152.     }
    153.  
    154.     public static void exportImageExcel(HttpServletResponse response, FreemarkerInput freemarkerInput) {
    155.         try {
    156.             OutputStream outputStream = response.getOutputStream();
    157.             response.reset();
    158.             response.setContentType("application/msexcel;charset=UTF-8");
    159.             response.setHeader("Content-Disposition", "attachment;filename=\"" + new String((freemarkerInput.getFileName() + ".xls").getBytes("GBK"), "ISO8859-1") + "\"");
    160.             response.setHeader("Response-Type", "Download");
    161.             createImageExcleToStream(freemarkerInput, outputStream);
    162.             FileUtils.forceDelete(new File(freemarkerInput.getXmlTempFile() + freemarkerInput.getFileName() + ".xml"));
    163.         } catch (Exception var3) {
    164.             var3.printStackTrace();
    165.         }
    166.  
    167.     }
    168.  
    169.     private static Template getTemplate(String templateName, String filePath) throws IOException {
    170.         Configuration configuration = new Configuration(Configuration.VERSION_2_3_28);
    171.         configuration.setDefaultEncoding("UTF-8");
    172.         configuration.setTemplateUpdateDelayMilliseconds(0L);
    173.         configuration.setEncoding(Locale.CHINA, "UTF-8");
    174.         configuration.setTemplateExceptionHandler(TemplateExceptionHandler.RETHROW_HANDLER);
    175.         configuration.setClassForTemplateLoading(FreeMarkerTemplateUtil.class, filePath);
    176.         configuration.setOutputEncoding("UTF-8");
    177.         return configuration.getTemplate(templateName, "UTF-8");
    178.     }
    179.  
    180.     private static void createImageExcleToStream(FreemarkerInput freemarkerInput, OutputStream outputStream) {
    181.         BufferedWriter out = null;
    182.  
    183.         try {
    184.             Template template = getTemplate(freemarkerInput.getTemplateName(), freemarkerInput.getTemplateFilePath());
    185.             File tempXMLFile = new File(freemarkerInput.getXmlTempFile() + freemarkerInput.getFileName() + ".xml");
    186.             FileUtils.forceMkdirParent(tempXMLFile);
    187.             out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(tempXMLFile), "UTF-8"));
    188.             template.process(freemarkerInput.getDataMap(), out);
    189.             if (log.isDebugEnabled()) {
    190.                 log.debug("1.完成将文本数据导入到XML文件中");
    191.             }
    192.  
    193.             SAXReader reader = new SAXReader();
    194.             Document document = reader.read(tempXMLFile);
    195.             Map<String, Style> styleMap = readXmlStyle(document);
    196.             log.debug("2.完成解析XML中样式信息");
    197.             List<Worksheet> worksheets = readXmlWorksheet(document);
    198.             if (log.isDebugEnabled()) {
    199.                 log.debug("3.开始将XML信息写入Excel，数据为：" + worksheets.toString());
    200.             }
    201.  
    202.             HSSFWorkbook wb = new HSSFWorkbook();
    203.             Iterator var10 = worksheets.iterator();
    204.  
    205.             while(var10.hasNext()) {
    206.                 Worksheet worksheet = (Worksheet)var10.next();
    207.                 HSSFSheet sheet = wb.createSheet(worksheet.getName());
    208.                 Table table = worksheet.getTable();
    209.                 List<Row> rows = table.getRows();
    210.                 List<Column> columns = table.getColumns();
    211.                 int createRowIndex;
    212.                 if (columns != null && columns.size() > 0) {
    213.                     createRowIndex = 0;
    214.  
    215.                     for(int i = 0; i < columns.size(); ++i) {
    216.                         Column column = (Column)columns.get(i);
    217.                         createRowIndex = getCellWidthIndex(createRowIndex, i, column.getIndex());
    218.                         sheet.setColumnWidth(createRowIndex, (int)column.getWidth() * 50);
    219.                     }
    220.                 }
    221.  
    222.                 createRowIndex = 0;
    223.                 List<CellRangeAddressEntity> cellRangeAddresses = new ArrayList();
    224.  
    225.                 for(int rowIndex = 0; rowIndex < rows.size(); ++rowIndex) {
    226.                     Row rowInfo = (Row)rows.get(rowIndex);
    227.                     if (rowInfo != null) {
    228.                         createRowIndex = getIndex(createRowIndex, rowIndex, rowInfo.getIndex());
    229.                         HSSFRow row = sheet.createRow(createRowIndex);
    230.                         if (rowInfo.getHeight() != null) {
    231.                             Integer height = rowInfo.getHeight() * 20;
    232.                             row.setHeight(height.shortValue());
    233.                         }
    234.  
    235.                         List<Cell> cells = rowInfo.getCells();
    236.                         if (!CollectionUtils.isEmpty(cells)) {
    237.                             int startIndex = 0;
    238.  
    239.                             for(int cellIndex = 0; cellIndex < cells.size(); ++cellIndex) {
    240.                                 Cell cellInfo = (Cell)cells.get(cellIndex);
    241.                                 if (cellInfo != null) {
    242.                                     startIndex = getIndex(startIndex, cellIndex, cellInfo.getIndex());
    243.                                     HSSFCell cell = row.createCell(startIndex);
    244.                                     String styleID = cellInfo.getStyleID();
    245.                                     Style style = (Style)styleMap.get(styleID);
    246.                                     CellStyle dataStyle = wb.createCellStyle();
    247.                                     setBorder(style, dataStyle);
    248.                                     setAlignment(style, dataStyle);
    249.                                     setValue((HSSFWorkbook)wb, cellInfo, (HSSFCell)cell, style, dataStyle);
    250.                                     setCellColor(style, dataStyle);
    251.                                     cell.setCellStyle(dataStyle);
    252.                                     if (cellInfo.getComment() != null) {
    253.                                         Data data = cellInfo.getComment().getData();
    254.                                         Comment comment = sheet.createDrawingPatriarch().createCellComment(new HSSFClientAnchor(0, 0, 0, 0, (short)3, 3, (short)5, 6));
    255.                                         comment.setString(new HSSFRichTextString(data.getText()));
    256.                                         cell.setCellComment(comment);
    257.                                     }
    258.  
    259.                                     startIndex = getCellRanges(createRowIndex, cellRangeAddresses, startIndex, cellInfo, style);
    260.                                 }
    261.                             }
    262.                         }
    263.                     }
    264.                 }
    265.  
    266.                 addCellRange((HSSFSheet)sheet, cellRangeAddresses);
    267.             }
    268.  
    269.             log.debug("4.开始写入图片：" + freemarkerInput.getExcelImageInputs());
    270.             if (!CollectionUtils.isEmpty(freemarkerInput.getExcelImageInputs())) {
    271.                 writeImageToExcel(freemarkerInput.getExcelImageInputs(), wb);
    272.             }
    273.  
    274.             log.debug("5.完成写入图片：" + freemarkerInput.getExcelImageInputs());
    275.             wb.write(outputStream);
    276.             outputStream.close();
    277.         } catch (Exception var39) {
    278.             var39.printStackTrace();
    279.             log.error("导出excel异常：" + var39.getMessage());
    280.         } finally {
    281.             try {
    282.                 out.close();
    283.             } catch (Exception var38) {
    284.             }
    285.  
    286.         }
    287.  
    288.     }
    289.  
    290.     private static void createExcelToStream(FreemarkerInput freemarkerInput, OutputStream outputStream) {
    291.         BufferedWriter out = null;
    292.  
    293.         try {
    294.             Template template = getTemplate(freemarkerInput.getTemplateName(), freemarkerInput.getTemplateFilePath());
    295.             File tempXMLFile = new File(freemarkerInput.getXmlTempFile() + freemarkerInput.getFileName() + ".xml");
    296.             FileUtils.forceMkdirParent(tempXMLFile);
    297.             out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(tempXMLFile), "UTF-8"));
    298.             template.process(freemarkerInput.getDataMap(), out);
    299.             if (log.isDebugEnabled()) {
    300.                 log.debug("1.完成将文本数据导入到XML文件中");
    301.             }
    302.  
    303.             SAXReader reader = new SAXReader();
    304.             Document document = reader.read(tempXMLFile);
    305.             Map<String, Style> styleMap = readXmlStyle(document);
    306.             log.debug("2.完成解析XML中样式信息");
    307.             List<Worksheet> worksheets = readXmlWorksheet(document);
    308.             if (log.isDebugEnabled()) {
    309.                 log.debug("3.开始将XML信息写入Excel，数据为：" + worksheets.toString());
    310.             }
    311.  
    312.             XSSFWorkbook wb = new XSSFWorkbook();
    313.             Iterator var10 = worksheets.iterator();
    314.  
    315.             while(var10.hasNext()) {
    316.                 Worksheet worksheet = (Worksheet)var10.next();
    317.                 XSSFSheet sheet = wb.createSheet(worksheet.getName());
    318.                 Table table = worksheet.getTable();
    319.                 List<Row> rows = table.getRows();
    320.                 List<Column> columns = table.getColumns();
    321.                 int createRowIndex;
    322.                 if (columns != null && columns.size() > 0) {
    323.                     createRowIndex = 0;
    324.  
    325.                     for(int i = 0; i < columns.size(); ++i) {
    326.                         Column column = (Column)columns.get(i);
    327.                         createRowIndex = getCellWidthIndex(createRowIndex, i, column.getIndex());
    328.                         sheet.setColumnWidth(createRowIndex, (int)column.getWidth() * 50);
    329.                     }
    330.                 }
    331.                 for(int i = 0 ; i<1000;i++){
    332.                     sheet.setColumnWidth(i,3000);
    333.                 }
    334.                 createRowIndex = 0;
    335.                 List<CellRangeAddressEntity> cellRangeAddresses = new ArrayList();
    336.  
    337.                 for(int rowIndex = 0; rowIndex < rows.size(); ++rowIndex) {
    338.                     Row rowInfo = (Row)rows.get(rowIndex);
    339.                     if (rowInfo != null) {
    340.                         createRowIndex = getIndex(createRowIndex, rowIndex, rowInfo.getIndex());
    341.                         XSSFRow row = sheet.createRow(createRowIndex);
    342.                         if (rowInfo.getHeight() != null) {
    343.                             Integer height = rowInfo.getHeight() * 20;
    344.                             row.setHeight(height.shortValue());
    345.                         }
    346.  
    347.                         List<Cell> cells = rowInfo.getCells();
    348.                         if (!CollectionUtils.isEmpty(cells)) {
    349.                             int startIndex = 0;
    350.  
    351.                             for(int cellIndex = 0; cellIndex < cells.size(); ++cellIndex) {
    352.                                 Cell cellInfo = (Cell)cells.get(cellIndex);
    353.                                 if (cellInfo != null) {
    354.                                     startIndex = getIndex(startIndex, cellIndex, cellInfo.getIndex());
    355.                                     XSSFCell cell = row.createCell(startIndex);
    356.                                     String styleID = cellInfo.getStyleID();
    357.                                     Style style = (Style)styleMap.get(styleID);
    358.                                     CellStyle dataStyle = wb.createCellStyle();
    359.                                     setBorder(style, dataStyle);
    360.                                     setAlignment(style, dataStyle);
    361.                                     setValue((XSSFWorkbook)wb, cellInfo, (XSSFCell)cell, style, dataStyle);
    362.                                     setCellColor(style, dataStyle);
    363.                                     cell.setCellStyle(dataStyle);
    364.                                     if (cellInfo.getComment() != null) {
    365.                                         Data data = cellInfo.getComment().getData();
    366.                                         Comment comment = sheet.createDrawingPatriarch().createCellComment(new XSSFClientAnchor(0, 0, 0, 0, 3, 3, 5, 6));
    367.                                         comment.setString(new XSSFRichTextString(data.getText()));
    368.                                         cell.setCellComment(comment);
    369.                                     }
    370.  
    371.                                     startIndex = getCellRanges(createRowIndex, cellRangeAddresses, startIndex, cellInfo, style);
    372.                                 }
    373.                             }
    374.                         }
    375.                     }
    376.                 }
    377.  
    378.                 addCellRange((XSSFSheet)sheet, cellRangeAddresses);
    379.             }
    380.  
    381.             log.debug("4.开始写入图片：" + freemarkerInput.getExcelImageInputs());
    382.             if (!CollectionUtils.isEmpty(freemarkerInput.getExcelImageInputs())) {
    383.                 writeImageToExcel(freemarkerInput.getExcelImageInputs(), wb);
    384.             }
    385.  
    386.             log.debug("5.完成写入图片：" + freemarkerInput.getExcelImageInputs());
    387.             wb.write(outputStream);
    388.             outputStream.close();
    389.         } catch (Exception var39) {
    390.             var39.printStackTrace();
    391.             log.error("导出excel异常：" + var39.getMessage());
    392.         } finally {
    393.             try {
    394.                 out.close();
    395.             } catch (Exception var38) {
    396.             }
    397.  
    398.         }
    399.  
    400.     }
    401.  
    402.     public static Map<String, Style> readXmlStyle(Document document) {
    403.         Map<String, Style> styleMap = XmlReader.getStyle(document);
    404.         return styleMap;
    405.     }
    406.  
    407.     public static List<Worksheet> readXmlWorksheet(Document document) {
    408.         List<Worksheet> worksheets = XmlReader.getWorksheet(document);
    409.         return worksheets;
    410.     }
    411.  
    412.     private static int getIndex(int columnIndex, int i, Integer index) {
    413.         if (index != null) {
    414.             columnIndex = index - 1;
    415.         }
    416.  
    417.         if (index == null && columnIndex != 0) {
    418.             ++columnIndex;
    419.         }
    420.  
    421.         if (index == null && columnIndex == 0) {
    422.             columnIndex = i;
    423.         }
    424.  
    425.         return columnIndex;
    426.     }
    427.  
    428.     private static int getCellWidthIndex(int columnIndex, int i, Integer index) {
    429.         if (index != null) {
    430.             columnIndex = index;
    431.         }
    432.  
    433.         if (index == null && columnIndex != 0) {
    434.             ++columnIndex;
    435.         }
    436.  
    437.         if (index == null && columnIndex == 0) {
    438.             columnIndex = i;
    439.         }
    440.  
    441.         return columnIndex;
    442.     }
    443.  
    444.     private static void setBorder(Style style, CellStyle dataStyle) {
    445.         if (style != null && style.getBorders() != null) {
    446.             for(int k = 0; k < style.getBorders().size(); ++k) {
    447.                 Border border = (Border)style.getBorders().get(k);
    448.                 if (border != null) {
    449.                     if ("Bottom".equals(border.getPosition())) {
    450.                         dataStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    451.                         dataStyle.setBorderBottom(BorderStyle.THIN);
    452.                     }
    453.  
    454.                     if ("Left".equals(border.getPosition())) {
    455.                         dataStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    456.                         dataStyle.setBorderLeft(BorderStyle.THIN);
    457.                     }
    458.  
    459.                     if ("Right".equals(border.getPosition())) {
    460.                         dataStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
    461.                         dataStyle.setBorderRight(BorderStyle.THIN);
    462.                     }
    463.  
    464.                     if ("Top".equals(border.getPosition())) {
    465.                         dataStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
    466.                         dataStyle.setBorderTop(BorderStyle.THIN);
    467.                     }
    468.                 }
    469.             }
    470.         }
    471.  
    472.     }
    473.  
    474.     private static void writeImageToExcel(List<ExcelImageInput> excelImageInputs, HSSFWorkbook wb) throws IOException {
    475.         BufferedImage bufferImg = null;
    476.         if (!CollectionUtils.isEmpty(excelImageInputs)) {
    477.             Iterator var3 = excelImageInputs.iterator();
    478.  
    479.             while(var3.hasNext()) {
    480.                 ExcelImageInput excelImageInput = (ExcelImageInput)var3.next();
    481.                 Sheet sheet = wb.getSheetAt(excelImageInput.getSheetIndex());
    482.                 if (sheet != null) {
    483.                     Drawing patriarch = sheet.createDrawingPatriarch();
    484.                     HSSFClientAnchor anchor = excelImageInput.getAnchorXls();
    485.                     anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
    486.                     String imagePath = excelImageInput.getImgPath();
    487.                     ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
    488.                     bufferImg = ImageIO.read(new File(imagePath));
    489.                     String imageType = imagePath.substring(imagePath.lastIndexOf(".") + 1, imagePath.length());
    490.                     ImageIO.write(bufferImg, imageType, byteArrayOut);
    491.                     patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), 5));
    492.                 }
    493.             }
    494.         }
    495.  
    496.     }
    497.  
    498.     private static void writeImageToExcel(List<ExcelImageInput> excelImageInputs, XSSFWorkbook wb) throws IOException {
    499.         BufferedImage bufferImg = null;
    500.         if (!CollectionUtils.isEmpty(excelImageInputs)) {
    501.             Iterator var3 = excelImageInputs.iterator();
    502.  
    503.             while(var3.hasNext()) {
    504.                 ExcelImageInput excelImageInput = (ExcelImageInput)var3.next();
    505.                 Sheet sheet = wb.getSheetAt(excelImageInput.getSheetIndex());
    506.                 if (sheet != null) {
    507.                     Drawing patriarch = sheet.createDrawingPatriarch();
    508.                     XSSFClientAnchor anchor = excelImageInput.getAnchorXlsx();
    509.                     anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
    510.                     String imagePath = excelImageInput.getImgPath();
    511.                     ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
    512.                     bufferImg = ImageIO.read(new File(imagePath));
    513.                     String imageType = imagePath.substring(imagePath.lastIndexOf(".") + 1, imagePath.length());
    514.                     ImageIO.write(bufferImg, imageType, byteArrayOut);
    515.                     patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), 5));
    516.                 }
    517.             }
    518.         }
    519.  
    520.     }
    521.  
    522.     private static void addCellRange(HSSFSheet sheet, List<CellRangeAddressEntity> cellRangeAddresses) {
    523.         if (!CollectionUtils.isEmpty(cellRangeAddresses)) {
    524.             Iterator var2 = cellRangeAddresses.iterator();
    525.  
    526.             while(true) {
    527.                 CellRangeAddressEntity cellRangeAddressEntity;
    528.                 CellRangeAddress cellRangeAddress;
    529.                 do {
    530.                     if (!var2.hasNext()) {
    531.                         return;
    532.                     }
    533.  
    534.                     cellRangeAddressEntity = (CellRangeAddressEntity)var2.next();
    535.                     cellRangeAddress = cellRangeAddressEntity.getCellRangeAddress();
    536.                     sheet.addMergedRegion(cellRangeAddress);
    537.                 } while(CollectionUtils.isEmpty(cellRangeAddressEntity.getBorders()));
    538.  
    539.                 for(int k = 0; k < cellRangeAddressEntity.getBorders().size(); ++k) {
    540.                     Border border = (Border)cellRangeAddressEntity.getBorders().get(k);
    541.                     if (border != null) {
    542.                         if ("Bottom".equals(border.getPosition())) {
    543.                             RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
    544.                         }
    545.  
    546.                         if ("Left".equals(border.getPosition())) {
    547.                             RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
    548.                         }
    549.  
    550.                         if ("Right".equals(border.getPosition())) {
    551.                             RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
    552.                         }
    553.  
    554.                         if ("Top".equals(border.getPosition())) {
    555.                             RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
    556.                         }
    557.                     }
    558.                 }
    559.             }
    560.         }
    561.     }
    562.  
    563.     private static void addCellRange(XSSFSheet sheet, List<CellRangeAddressEntity> cellRangeAddresses) {
    564.         if (!CollectionUtils.isEmpty(cellRangeAddresses)) {
    565.             Iterator var2 = cellRangeAddresses.iterator();
    566.  
    567.             while(true) {
    568.                 CellRangeAddressEntity cellRangeAddressEntity;
    569.                 CellRangeAddress cellRangeAddress;
    570.                 do {
    571.                     if (!var2.hasNext()) {
    572.                         return;
    573.                     }
    574.  
    575.                     cellRangeAddressEntity = (CellRangeAddressEntity)var2.next();
    576.                     cellRangeAddress = cellRangeAddressEntity.getCellRangeAddress();
    577.                     sheet.addMergedRegion(cellRangeAddress);
    578.                 } while(CollectionUtils.isEmpty(cellRangeAddressEntity.getBorders()));
    579.  
    580.                 for(int k = 0; k < cellRangeAddressEntity.getBorders().size(); ++k) {
    581.                     Border border = (Border)cellRangeAddressEntity.getBorders().get(k);
    582.                     if (border != null) {
    583.                         if ("Bottom".equals(border.getPosition())) {
    584.                             RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
    585.                         }
    586.  
    587.                         if ("Left".equals(border.getPosition())) {
    588.                             RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
    589.                         }
    590.  
    591.                         if ("Right".equals(border.getPosition())) {
    592.                             RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
    593.                         }
    594.  
    595.                         if ("Top".equals(border.getPosition())) {
    596.                             RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
    597.                         }
    598.                     }
    599.                 }
    600.             }
    601.         }
    602.     }
    603.  
    604.     private static void setAlignment(Style style, CellStyle dataStyle) {
    605.         if (style != null && style.getAlignment() != null) {
    606.             String horizontal = style.getAlignment().getHorizontal();
    607.             if (!ObjectUtils.isEmpty(horizontal)) {
    608.                 if ("Left".equals(horizontal)) {
    609.                     dataStyle.setAlignment(HorizontalAlignment.LEFT);
    610.                 } else if ("Center".equals(horizontal)) {
    611.                     dataStyle.setAlignment(HorizontalAlignment.CENTER);
    612.                 } else {
    613.                     dataStyle.setAlignment(HorizontalAlignment.RIGHT);
    614.                 }
    615.             }
    616.  
    617.             String vertical = style.getAlignment().getVertical();
    618.             if (!ObjectUtils.isEmpty(vertical)) {
    619.                 if ("Top".equals(vertical)) {
    620.                     dataStyle.setVerticalAlignment(VerticalAlignment.TOP);
    621.                 } else if ("Center".equals(vertical)) {
    622.                     dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    623.                 } else if ("Bottom".equals(vertical)) {
    624.                     dataStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
    625.                 } else if ("JUSTIFY".equals(vertical)) {
    626.                     dataStyle.setVerticalAlignment(VerticalAlignment.JUSTIFY);
    627.                 } else {
    628.                     dataStyle.setVerticalAlignment(VerticalAlignment.DISTRIBUTED);
    629.                 }
    630.             }
    631.  
    632.             String wrapText = style.getAlignment().getWrapText();
    633.             if (!ObjectUtils.isEmpty(wrapText)) {
    634.                 dataStyle.setWrapText(true);
    635.             }
    636.         }
    637.  
    638.     }
    639.  
    640.     private static void setCellColor(Style style, CellStyle dataStyle) {
    641.         if (style != null && style.getInterior() != null) {
    642.             String color = style.getInterior().getColor();
    643.             if (color == null) {
    644.                 color = "#FFFFFF";
    645.             }
    646.  
    647.             Integer[] rgb = ColorUtil.hex2Rgb(color);
    648.             HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
    649.             HSSFPalette palette = hssfWorkbook.getCustomPalette();
    650.             HSSFColor paletteColor = palette.findSimilarColor(rgb[0], rgb[1], rgb[2]);
    651.             dataStyle.setFillForegroundColor(paletteColor.getIndex());
    652.             dataStyle.setFillBackgroundColor(paletteColor.getIndex());
    653.             if ("Solid".equals(style.getInterior().getPattern())) {
    654.                 dataStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    655.             }
    656.         }
    657.  
    658.     }
    659.  
    660.     private static int getCellRanges(int createRowIndex, List<CellRangeAddressEntity> cellRangeAddresses, int startIndex, Cell cellInfo, Style style) {
    661.         if (cellInfo.getMergeAcross() != null || cellInfo.getMergeDown() != null) {
    662.             CellRangeAddress cellRangeAddress = null;
    663.             int length;
    664.             int i;
    665.             if (cellInfo.getMergeAcross() != null && cellInfo.getMergeDown() != null) {
    666.                 length = startIndex;
    667.                 if (cellInfo.getMergeAcross() != 0) {
    668.                     length = startIndex + cellInfo.getMergeAcross();
    669.                 }
    670.  
    671.                 i = createRowIndex;
    672.                 if (cellInfo.getMergeDown() != 0) {
    673.                     i = createRowIndex + cellInfo.getMergeDown();
    674.                 }
    675.  
    676.                 cellRangeAddress = new CellRangeAddress(createRowIndex, i, (short)startIndex, (short)length);
    677.             } else if (cellInfo.getMergeAcross() != null && cellInfo.getMergeDown() == null) {
    678.                 if (cellInfo.getMergeAcross() != 0) {
    679.                     length = startIndex + cellInfo.getMergeAcross();
    680.                     cellRangeAddress = new CellRangeAddress(createRowIndex, createRowIndex, (short)startIndex, (short)length);
    681.                 }
    682.             } else if (cellInfo.getMergeDown() != null && cellInfo.getMergeAcross() == null && cellInfo.getMergeDown() != 0) {
    683.                 length = createRowIndex + cellInfo.getMergeDown();
    684.                 cellRangeAddress = new CellRangeAddress(createRowIndex, length, (short)startIndex, (short)startIndex);
    685.             }
    686.  
    687.             if (cellInfo.getMergeAcross() != null) {
    688.                 length = cellInfo.getMergeAcross();
    689.  
    690.                 for(i = 0; i < length; ++i) {
    691.                     ++startIndex;
    692.                 }
    693.             }
    694.  
    695.             CellRangeAddressEntity cellRangeAddressEntity = new CellRangeAddressEntity();
    696.             cellRangeAddressEntity.setCellRangeAddress(cellRangeAddress);
    697.             if (style != null && style.getBorders() != null) {
    698.                 cellRangeAddressEntity.setBorders(style.getBorders());
    699.             }
    700.  
    701.             cellRangeAddresses.add(cellRangeAddressEntity);
    702.         }
    703.  
    704.         return startIndex;
    705.     }
    706.  
    707.     private static void setValue(XSSFWorkbook wb, Cell cellInfo, XSSFCell cell, Style style, CellStyle dataStyle) {
    708.         if (cellInfo.getData() != null) {
    709.             XSSFFont font = wb.createFont();
    710.             String color;
    711.             Integer[] rgb;
    712.             HSSFWorkbook hssfWorkbook;
    713.             HSSFPalette palette;
    714.             HSSFColor paletteColor;
    715.             if (style != null && style.getFont() != null) {
    716.                 color = style.getFont().getColor();
    717.                 if (color == null) {
    718.                     color = "#000000";
    719.                 }
    720.  
    721.                 rgb = ColorUtil.hex2Rgb(color);
    722.                 hssfWorkbook = new HSSFWorkbook();
    723.                 palette = hssfWorkbook.getCustomPalette();
    724.                 paletteColor = palette.findSimilarColor(rgb[0], rgb[1], rgb[2]);
    725.                 font.setColor(paletteColor.getIndex());
    726.             }
    727.  
    728.             if (!ObjectUtils.isEmpty(cellInfo.getData().getType()) && "Number".equals(cellInfo.getData().getType())) {
    729.                 cell.setCellType(CellType.NUMERIC);
    730.             }
    731.  
    732.             if (style != null && style.getFont().getBold() > 0) {
    733.                 font.setBold(true);
    734.             }
    735.  
    736.             if (style != null && !ObjectUtils.isEmpty(style.getFont().getFontName())) {
    737.                 font.setFontName(style.getFont().getFontName());
    738.             }
    739.  
    740.             if (style != null && style.getFont().getSize() > 0.0D) {
    741.                 font.setFontHeightInPoints((short)((int)style.getFont().getSize()));
    742.             }
    743.  
    744.             if (cellInfo.getData().getFont() != null) {
    745.                 if (cellInfo.getData().getFont().getBold() > 0) {
    746.                     font.setBold(true);
    747.                 }
    748.  
    749.                 if ("Number".equals(cellInfo.getData().getType())) {
    750.                     cell.setCellValue((double)Float.parseFloat(cellInfo.getData().getFont().getText()));
    751.                 } else {
    752.                     cell.setCellValue(cellInfo.getData().getFont().getText());
    753.                 }
    754.  
    755.                 if (!ObjectUtils.isEmpty(cellInfo.getData().getFont().getCharSet())) {
    756.                     font.setCharSet(Integer.valueOf(cellInfo.getData().getFont().getCharSet()));
    757.                 }
    758.             } else if ("Number".equals(cellInfo.getData().getType())) {
    759.                 if (!ObjectUtils.isEmpty(cellInfo.getData().getText())) {
    760.                     cell.setCellValue((double)Float.parseFloat(cellInfo.getData().getText().replaceAll(",", "")));
    761.                 }
    762.             } else {
    763.                 cell.setCellValue(cellInfo.getData().getText());
    764.             }
    765.  
    766.             if (style != null && style.getNumberFormat() != null) {
    767.                 color = style.getFont().getColor();
    768.                 if (color == null) {
    769.                     color = "#000000";
    770.                 }
    771.  
    772.                 rgb = ColorUtil.hex2Rgb(color);
    773.                 hssfWorkbook = new HSSFWorkbook();
    774.                 palette = hssfWorkbook.getCustomPalette();
    775.                 paletteColor = palette.findSimilarColor(rgb[0], rgb[1], rgb[2]);
    776.                 font.setColor(paletteColor.getIndex());
    777.                 if ("0%".equals(style.getNumberFormat().getFormat())) {
    778.                     XSSFDataFormat format = wb.createDataFormat();
    779.                     dataStyle.setDataFormat(format.getFormat(style.getNumberFormat().getFormat()));
    780.                 } else {
    781.                     dataStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
    782.                 }
    783.             }
    784.  
    785.             dataStyle.setFont(font);
    786.         }
    787.  
    788.     }
    789.  
    790.     private static void setValue(HSSFWorkbook wb, Cell cellInfo, HSSFCell cell, Style style, CellStyle dataStyle) {
    791.         if (cellInfo.getData() != null) {
    792.             HSSFFont font = wb.createFont();
    793.             String color;
    794.             Integer[] rgb;
    795.             HSSFWorkbook hssfWorkbook;
    796.             HSSFPalette palette;
    797.             HSSFColor paletteColor;
    798.             if (style != null && style.getFont() != null) {
    799.                 color = style.getFont().getColor();
    800.                 if (color == null) {
    801.                     color = "#000000";
    802.                 }
    803.  
    804.                 rgb = ColorUtil.hex2Rgb(color);
    805.                 hssfWorkbook = new HSSFWorkbook();
    806.                 palette = hssfWorkbook.getCustomPalette();
    807.                 paletteColor = palette.findSimilarColor(rgb[0], rgb[1], rgb[2]);
    808.                 font.setColor(paletteColor.getIndex());
    809.             }
    810.  
    811.             if (!ObjectUtils.isEmpty(cellInfo.getData().getType()) && "Number".equals(cellInfo.getData().getType())) {
    812.                 cell.setCellType(CellType.NUMERIC);
    813.             }
    814.  
    815.             if (style != null && style.getFont().getBold() > 0) {
    816.                 font.setBold(true);
    817.             }
    818.  
    819.             if (style != null && !ObjectUtils.isEmpty(style.getFont().getFontName())) {
    820.                 font.setFontName(style.getFont().getFontName());
    821.             }
    822.  
    823.             if (style != null && style.getFont().getSize() > 0.0D) {
    824.                 font.setFontHeightInPoints((short)((int)style.getFont().getSize()));
    825.             }
    826.  
    827.             if (cellInfo.getData().getFont() != null) {
    828.                 if (cellInfo.getData().getFont().getBold() > 0) {
    829.                     font.setBold(true);
    830.                 }
    831.  
    832.                 if ("Number".equals(cellInfo.getData().getType())) {
    833.                     cell.setCellValue((double)Float.parseFloat(cellInfo.getData().getFont().getText()));
    834.                 } else {
    835.                     cell.setCellValue(cellInfo.getData().getFont().getText());
    836.                 }
    837.  
    838.                 if (!ObjectUtils.isEmpty(cellInfo.getData().getFont().getCharSet())) {
    839.                     font.setCharSet(Integer.valueOf(cellInfo.getData().getFont().getCharSet()));
    840.                 }
    841.             } else if ("Number".equals(cellInfo.getData().getType())) {
    842.                 if (!ObjectUtils.isEmpty(cellInfo.getData().getText())) {
    843.                     cell.setCellValue((double)Float.parseFloat(cellInfo.getData().getText().replaceAll(",", "")));
    844.                 }
    845.             } else {
    846.                 cell.setCellValue(cellInfo.getData().getText());
    847.             }
    848.  
    849.             if (style != null && style.getNumberFormat() != null) {
    850.                 color = style.getFont().getColor();
    851.                 if (color == null) {
    852.                     color = "#000000";
    853.                 }
    854.  
    855.                 rgb = ColorUtil.hex2Rgb(color);
    856.                 hssfWorkbook = new HSSFWorkbook();
    857.                 palette = hssfWorkbook.getCustomPalette();
    858.                 paletteColor = palette.findSimilarColor(rgb[0], rgb[1], rgb[2]);
    859.                 font.setColor(paletteColor.getIndex());
    860.                 if ("0%".equals(style.getNumberFormat().getFormat())) {
    861.                     HSSFDataFormat format = wb.createDataFormat();
    862.                     dataStyle.setDataFormat(format.getFormat(style.getNumberFormat().getFormat()));
    863.                 } else {
    864.                     dataStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
    865.                 }
    866.             }
    867.             dataStyle.setFont(font);
    868.         }
    869.     }
    870. }
    1. package com.example.stu1.freemark;
    2.  
    3. import com.yongjiu.dto.freemarker.input.FreemarkerInput;
    4. import org.springframework.web.bind.annotation.GetMapping;
    5. import org.springframework.web.bind.annotation.RestController;
    6.  
    7. import javax.servlet.http.HttpServletResponse;
    8. import java.net.URLEncoder;
    9. import java.util.ArrayList;
    10. import java.util.HashMap;
    11. import java.util.List;
    12. import java.util.Map;
    13.  
    14. /**
    15.  * @Author yangcai
    16.  * @create 2022/7/1 16:43
    17.  */
    18. @RestController
    19. public class UserController1 {
    20.  
    21.     @GetMapping("/index")
    22.     public void index(HttpServletResponse response) {
    23.         try{
    24.             String fileName = URLEncoder.encode("测试导出", "UTF-8");
    25.             Map<String,Object> resultMap= getList();
    26.             FreemarkerInput freemarkerInput = new FreemarkerInput();
    27.             freemarkerInput.setTemplateFilePath("/templates");
    28.             freemarkerInput.setTemplateName("test.ftl");
    29.             freemarkerInput.setXmlTempFile(System.getProperty("java.io.tmpdir"));
    30.             freemarkerInput.setFileName(fileName);
    31.             freemarkerInput.setDataMap(resultMap);
    32.             FreeMarkerTemplateUtil.exportImageExcelNew(response, freemarkerInput);
    33.         } catch (Exception e) {
    34.             e.printStackTrace();
    35.         }
    36.     }
    37.     Map<String,Object> getList(){
    38.         Map<String,Object>  map = new HashMap<>();
    39.         List<User> userList = new ArrayList<>();
    40.         User user1 = new User();
    41.         user1.setAge(26);
    42.         user1.setEmail("707656893@qq.com");
    43.         user1.setName("张无忌");
    44.         userList.add(user1);
    45.         User user2 = new User();
    46.         user2.setAge(27);
    47.         user2.setEmail("111111111@qq.com");
    48.         user2.setName("逍遥子");
    49.         userList.add(user2);
    50.         User user3 = new User();
    51.         user3.setAge(28);
    52.         user3.setEmail("222222222@qq.com");
    53.         user3.setName("乔峰");
    54.         userList.add(user3);
    55.         map.put("userList",userList);
    56.         return map;
    57.     }
    58. }

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

    poi解析excel文件

    1.     public static  Function<UploadExcelObject,List<Map<String, IndexDataPo>>> getExcelDataOne = uploadExcelObject->{
    2.         try(FileInputStream fileInputStream = new FileInputStream(uploadExcelObject.getFileUrl())) {
    3.             Workbook workbook = null;
    4.             if (uploadExcelObject.getFileUrl().endsWith(".xls")) {
    5.                 try {
    6.                     workbook = new HSSFWorkbook(fileInputStream);
    7.                 } catch (OfficeXmlFileException e) {
    8.                     try {
    9.                         workbook = new XSSFWorkbook(fileInputStream);
    10.                     } catch (Exception e1) {
    11.                         log.error(e.getMessage());
    12.  
    13.                     }
    14.                 } catch (IOException e) {
    15.                     throw new CommonException("109",e.getMessage());
    16.                 }
    17.             } else if (uploadExcelObject.getFileUrl().endsWith(".xlsx")) {
    18.                 try {
    19.                     workbook = new XSSFWorkbook(fileInputStream);
    20.                 } catch (Exception e) {
    21.                     e.printStackTrace();
    22.                     throw new CommonException("110",e.getMessage());
    23.                 }
    24.             }
    25.             try {
    26.                 int sheetNo = workbook.getNumberOfSheets();
    27.                 List<Map<String, IndexDataPo>> list = Lists.newArrayList();
    28.                 log.info(String.format("此文档共有%d多少页",sheetNo));
    29.                 for(int i=0 ; i< sheetNo ;i++){
    30.                     Sheet sheet = workbook.getSheetAt(0);
    31.                     List<String> headerList = checkAndGetHeader(sheet,TemplateTypeEnum.ONE_DATE.getCode(),uploadExcelObject.getIndexCodeList());
    32.                     for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
    33.                         XSSFRow row = (XSSFRow) sheet.getRow(j);
    34.                         if(row!=null) {
    35.                             if(isRowEmpty(row)){
    36.                                 continue;
    37.                             }
    38.                             IndexDataPo dto = new IndexDataPo();
    39.                             for (int k = 0; k < sheet.getRow(1).getPhysicalNumberOfCells(); k++) {
    40.                                 String value = null;
    41.                                 Cell cell = row.getCell(k);
    42.                                 if(k==0){
    43.                                     if("".equals(cell.toString()) || cell.toString() == ""){
    44.                                         throw new CommonException("101","没有输入时间");
    45.                                     }
    46.                                     if(DateUtil.isCellDateFormatted(cell)){
    47.                                         dto.setDataDate(cell.getDateCellValue());
    48.                                     }
    49.                                 }else{
    50.                                     Map<String, IndexDataPo> map = Maps.newHashMap();
    51.                                     if(cell != null){
    52.                                         value =  getCellValue(cell);
    53.                                     }
    54.                                     if(StringUtils.isEmpty(value)){
    55.                                         dto.setDataValue(null);
    56.                                     }else{
    57.                                         dto.setDataValue(Double.valueOf(value));
    58.                                     }
    59.                                     dto.setIndexCode(headerList.get(k));
    60.                                     map.put(DateUtils.getDateLong(dto.getDataDate()), dto);
    61.                                     list.add(map);
    62.                                     dto = new IndexDataPo(dto.getDataDate());
    63.                                 }
    64.                             }
    65.                         }
    66.                     }
    67.                 }
    68.                 return  list;
    69.             } catch (Exception e) {
    70.                 log.error(e.getMessage(),e);
    71.                 throw new CommonException("解析excel错误:","解析excel错误"+e.getMessage());
    72.             }
    73.         } catch (IOException e) {
    74.             log.error(e.getMessage(),e);
    75.         }
    76.         return null;
    77.     };

     

  • 相关阅读:
    Redis Sentinel集群管理手册
    Linux 学习笔记(8)
    在Linux服务器上部署Tornado项目
    计数排序基础思路
    SpringCloud环境搭建及入门案例
    CodeQL的自动化代码审计之路（上篇）
    c - ar 中的 “rcs“选项有什么作用？
    Ansible中的角色使用
    NPDP为什么越来越受追捧？产品经理你可知道？
    深度估计：SGBM算法应用
  • 原文地址：https://blog.csdn.net/beiduofen2011/article/details/125561027