动态的导出excel ？正确姿势应该是这样的。。。。

1.思考：如果有这么一个导出excel的需求     表头不确定，每个表头对应的数据不确定 你会怎么做？

2.实现：

      我这里是使用了excel模板，在excel中添加表达式的方式 。有点类似于jsp中的el表达式 。

 3.所需要的依赖

<dependency>
   <groupId>net.sf.jxls</groupId>
   <artifactId>jxls-core</artifactId>
   <version>1.0.6</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.17</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml-schemas</artifactId>
   <version>3.17</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>3.17</version>
</dependency>

---------------------------------------------

4.本地建一个excel 名字随便起 表达式如下

5.放到springboot项目resource文件夹下

6.工具类-----------

   

package com.test.demo.studyJava;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import lombok.extern.slf4j.Slf4j;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.core.io.ClassPathResource;
@Slf4j
public class ExcelUtils {


    /**
     * Excel导出
     *
     * @param filePath 模板路径
     * @param data     填充数据
     * @param fileName 文件名称
     * @return
     */
    public void export(String filePath, Map<String, Object> data, HttpServletResponse response, String fileName) {
        XLSTransformer transformer = new XLSTransformer();
        InputStream in = null;
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            ClassPathResource classPathResource = new ClassPathResource(filePath);
            in = classPathResource.getInputStream();
            //transformer.transformXLS("f:/liveRecording.xls",data,"f:/test.xls");
            Workbook workbook = transformer.transformXLS(in, data);
            response.reset();
            response.setContentType("application/vnd.ms-excel:charset=UTF-8");
            //注意符号问题很重要 很重要 很重要 重要的事情说三遍 看好了！！！！！
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
            workbook.write(response.getOutputStream());
        } catch (FileNotFoundException e) {
            log.error("jxls：", e);
        } catch (InvalidFormatException e) {
            log.error("jxls：", e);
        } catch (IOException e) {
            log.error("jxls：", e);
        }
        finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    log.error("io error：", e);
                }
            }
        }
    }
}

7.接口

   

@Controller
public class TemplateExcelController {
    //excel模板的存放路径
    private static final String PATH="excel/redVisitorInfo.xls";

    @RequestMapping("/export")
    public void export(HttpServletResponse response){
        Map<String,Object> map=new HashMap<>();
        Map<String,Object> vData=new HashMap<>();
        vData.put("name","张三");
        vData.put("age","18");
        vData.put("sex","男");
        map.put("VisitorInfoData",vData);
        Map<String,Object> vData2=new HashMap<>();
        vData2.put("name","张三");
        vData2.put("age","18");
        vData2.put("sex","男");
        map.put("VisitorInfoData",vData);
        Map<String,Object> vData3=new HashMap<>();
        vData3.put("name","张三");
        vData3.put("age","18");
        vData3.put("sex","男");
        map.put("VisitorInfoData",vData);
        Map<String,Object> vData4=new HashMap<>();
        vData4.put("name","张三");
        vData4.put("age","18");
        vData4.put("sex","男");

        List<Map<String,Object>> fildData=new ArrayList<>();
        Map<String,Object> fildMap=new HashMap<>();
        fildMap.put("fieldname","name");
        fildMap.put("fieldcname","姓名");
        Map<String,Object> fildMap1=new HashMap<>();
        fildMap1.put("fieldname","age");
        fildMap1.put("fieldcname","年龄");
        Map<String,Object> fildMap2=new HashMap<>();
        fildMap2.put("fieldname","sex");
        fildMap2.put("fieldcname","性别");
        fildData.add(fildMap);
        fildData.add(fildMap1);
        fildData.add(fildMap2);

        List<Map<String,Object>> maps=new ArrayList<>();
        maps.add(vData);
        maps.add(vData2);
        maps.add(vData3);
        maps.add(vData4);
        map.put("VisitorInfoData",maps);
        map.put("headData",fildData);
        ExcelUtils excelUtils=new ExcelUtils();
        excelUtils.export(PATH,map,response,"员工表");
    }

    public static void main(String[] args) {

    }
}

8.运行项目访问 localhost:8080/export

结果

   

 

9.注意事项 因为spring会把excel文件过滤掉 所以 要在pom.xml中添加插件

<build>
   <plugins>
      <plugin>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
      <!-- 避免xls文件的二进制文件格式压缩破坏 -->
      <plugin>
         <groupId>org.apache.maven.plugins</groupId>
         <artifactId>maven-resources-plugin</artifactId>
         <configuration>
            <nonFilteredFileExtensions>
               <nonFilteredFileExtension>xls</nonFilteredFileExtension>
            </nonFilteredFileExtensions>
         </configuration>
      </plugin>
   </plugins>
</build>