Spring Boot 使用JXLS导出Excel

• 目录结构

├─src
│  ├─main
│  │  ├─java
│  │  │  └─com
│  │  │      └─ranhan
│  │  │          │  ExportApplication.java
│  │  │          ├─controller
│  │  │          │      ExportController.java
│  │  │          ├─dao
│  │  │          │      OrderMapper.java
│  │  │          ├─model
│  │  │          │      Order.java
│  │  │          ├─service
│  │  │          │  │  MailService.java
│  │  │          │  │
│  │  │          │  └─impl
│  │  │          │          OrderExportExcelServiceImpl.java
│  │  │          └─utils
│  │  │                  JxlsUtil.java
│  │  └─resources
│  │      │  application.properties
│  │      ├─mapper
│  │      │      OrderMapper.xml
│  │      └─templates
│  │          ├─excel
│  │          │      order.xlsx

• 制作excel模板

• 本地导出

/**
 * Excel导出
 * @param templateName
 * @param data
 */
public String export(String templateName, Map<String, Object> data) {
    XLSTransformer transformer = new XLSTransformer();
    OutputStream out = null;
    String filename = DateUtil.getNewDate() + ".xlsx";
    InputStream stream = null;
    try {
        String templatePath = "src/main/resources/templates/excel/" + templateName;
        File template = ResourceUtils.getFile(templatePath);
        FileInputStream in = new FileInputStream(template);
        Workbook workbook = transformer.transformXLS(in, data);
        out = new FileOutputStream(new File(filename));
        workbook.write(out);
        out.flush();
        } catch (FileNotFoundException e) {
            logger.error("jxls：", e);
        } catch (InvalidFormatException e) {
            logger.error("jxls：", e);
        } catch (IOException e) {
            logger.error("jxls：", e);
        } finally {
        IOUtils.closeQuietly(stream);
        if (null != out) {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
           }
    return filename;
}

• 本地使用正常，打jar测试错误
• 1. spring boot class path resource [templates/] cannot be resolved to absolute file path because it does not reside in the file system
  • 推测是文件路径错误，对获取文件路径进行修改

  ClassLoader classLoader = this.getClass().getClassLoader();
  String templatePath = url.getPath() + "templates"+ File.separator + "excel" + File.separator + templateName;
  FileInputStream in = new FileInputStream(new File(templatePath));
  

  • 进行修改后打包还是导出失败，进行调试
  • templatePath路径为/E:/WorkSpase/export/target/classes/templates\excel\shop.xlsx
  • 我打卡这个模板，结果说损坏
  • 解决方案

  <plugin>
      <groupId>org.apache.maven.plugins</groupId>
      <artifactId>maven-resources-plugin</artifactId>
      <configuration>
      <encoding>UTF-8</encoding>
      <nonFilteredFileExtensions>
         <nonFilteredFileExtension>xlsx</nonFilteredFileExtension>
         <nonFilteredFileExtension>xls</nonFilteredFileExtension>
      </nonFilteredFileExtensions>
      </configuration>
  </plugin>
  

• 2. java.io.FileNotFoundException: file:/app.jar!/BOOT-INF/classes!/templates/excel/shop.xlsx (No such file or directory)
  • 网上搜到的解决方案是使用流方式读取文件，在最开始也改成流的形式，同样是导出失败，原因是excel文件格式进行了转码处理。现在又改成流的读取方式可以正常导出。
  • 加入pom

  <dependency>
      <groupId>commons-io</groupId>
      <artifactId>commons-io</artifactId>
      <version>2.6</version>
  </dependency>
  

  • 修改导出方法

  String templatePath = "templates"+ File.separator + "excel" + File.separator + templateName;
  stream = getClass().getClassLoader().getResourceAsStream(templatePath);
  File targetFile = new File(templateName);
  FileUtils.copyInputStreamToFile(stream, targetFile);
  FileInputStream in = new FileInputStream(targetFile);
  
  
  // 最后关闭流
  IOUtils.closeQuietly(stream);
  

• freemarker导出