SpringBoot实现Excel解析和下载

布满荆棘的人生 2022-10-18 01:48 156阅读 0赞

# 写作原因 #

操作Excel一般有三种poi、EasyExcel和Hutool工具包，百度一下你会发现大多数写的不是很清晰，所以此处写一个demo,方便自己以后copy。此处是基于Hutool实现的。

# 源码下载（亲测可用） #

[https://gitee.com/cbeann/Demooo/tree/master/springboot-excel-demo][https_gitee.com_cbeann_Demooo_tree_master_springboot-excel-demo]

# 部分代码展示 #

## pom ##

<dependency>
                <groupId>cn.hutool</groupId>
                <artifactId>hutool-all</artifactId>
                <version>5.6.5</version>
            </dependency>
            
            <dependency>
                <groupId>com.google.zxing</groupId>
                <artifactId>core</artifactId>
                <version>3.4.0</version>
            </dependency>
            
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.1.2</version>
            </dependency>

## CExcelUtil自定义的工具类 ##

package com.example.springbootexceldemo.utils;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.InputStream;
    import java.util.LinkedHashMap;
    import java.util.List;
    import java.util.Map.Entry;
    
    import cn.hutool.poi.excel.ExcelReader;
    import cn.hutool.poi.excel.ExcelUtil;
    import cn.hutool.poi.excel.ExcelWriter;
    
    /**
     * Created on 2021-06-04
     */
    public class CExcelUtil {
    
    
        /**
         * 解析Excel
         *
         * @param file 文件
         * @param startRow 第几行开始
         */
        public static List<List<Object>> explainExcel(File file, Integer startRow) throws Exception {
            InputStream inputStream = new FileInputStream(file);
            List<List<Object>> lists = explainExcel(inputStream, startRow);
            return lists;
    
        }
    
        /**
         * 解析Excel
         *
         * @param inputStream 文件输入流
         * @param startRow 第几行开始
         */
        public static List<List<Object>> explainExcel(InputStream inputStream, Integer startRow) throws Exception {
            ExcelReader reader = ExcelUtil.getReader(inputStream);
            List<List<Object>> data = reader.read(startRow);
            return data;
        }
    
    
        /**
         * 构建Excel
         *
         * @param headers 表头和对象属性的对应关系
         * @param dataList 实体类集合
         * 注意：
         * 1）dataList传入Object ,List<Object> list.add(new Student())
         * 2) dataList最少为一个空对象，否则出现表头为空的问题，他是根据对象属性添加表头，没有对象就没有表头
         * 3）参考MovePersonItemServiceImpl###exportItems的使用方法
         */
        public static ExcelWriter getExcelWriter(LinkedHashMap<String, String> headers, List<Object> dataList) {
    
    
            // 准备将数据集合封装成Excel对象
            ExcelWriter writer = ExcelUtil.getWriter(true);
    
            //添加标题
            for (Entry<String, String> stringStringEntry : headers.entrySet()) {
                writer.addHeaderAlias(stringStringEntry.getKey(), stringStringEntry.getValue());
            }
    
            //写入数据
            writer.write(dataList, true);
    
    
            return writer;
    
    
        }
    
    
    }

## 控制层 ##

package com.example.springbootexceldemo.controller;
    
    import java.net.URLEncoder;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.LinkedHashMap;
    import java.util.List;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.PostMapping;
    import org.springframework.web.bind.annotation.RequestPart;
    import org.springframework.web.bind.annotation.RestController;
    import org.springframework.web.multipart.MultipartFile;
    
    import com.example.springbootexceldemo.entity.Student;
    import com.example.springbootexceldemo.utils.CExcelUtil;
    
    import cn.hutool.poi.excel.ExcelWriter;
    
    /**
     * Created on 2021-06-04
     */
    @RestController
    public class ExcelController {
    
    
        //resources/file/demo-explan.xlsx
        @PostMapping("/explainExcel")
        public Object explainExcel(@RequestPart("file") MultipartFile file) throws Exception {
    
            if (file.isEmpty()) {
                System.out.println("empty");
                return 0;
            }
            //解析Excel
            List<List<Object>> datas = CExcelUtil.explainExcel(file.getInputStream(), 1);
            return datas;
    
        }
    
    
        @GetMapping("/downExcel")
        public void explainExcel(HttpServletResponse response) throws Exception {
    
            //查询数据库
            Student student = new Student(1, "张三", new Date());
            Student student2 = new Student(2, "李四", new Date());
            List<Object> datas = new ArrayList<>();
            datas.add(student);
            datas.add(student2);
    
            
            //设置对象属性和表头的对应关系
            LinkedHashMap headers = new LinkedHashMap<>();
            headers.put("id", "中文名");
            headers.put("name", "邮箱前缀");
            headers.put("birthday", "部门名");
    
    
            //创建Excel
            ExcelWriter writer = CExcelUtil.getExcelWriter(headers, datas);
    
    
            //设置response属性
            String name = "汉字";
            response.setContentType("application/ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition",
                    String.format("attachment;filename=%s", URLEncoder.encode(name, "UTF-8") + ".xlsx"));
    
            ServletOutputStream out = response.getOutputStream();
            writer.flush(out, true);
    
            //关闭流
            writer.close();
            out.close();
    
    
        }
    
    
    }

## 实体类 ##

package com.example.springbootexceldemo.entity;
    
    import java.util.Date;
    
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    /**
     * Created on 2021-06-04
     */
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class Student {
    
        private Integer id;
        private String name;
        private Date birthday;
    }

# 参考 #

[https://www.hutool.cn/docs/\#/][https_www.hutool.cn_docs]

[https_gitee.com_cbeann_Demooo_tree_master_springboot-excel-demo]: https://gitee.com/cbeann/Demooo/tree/master/springboot-excel-demo
[https_www.hutool.cn_docs]: https://www.hutool.cn/docs/#/