SpringBoot实现Excel解析和下载

2023-12-25 19:02:31 浏览数 (1)

写作原因

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

源码下载（亲测可用）

ChaiRongD/Demooo - Gitee.com

部分代码展示

pom

代码语言：javascript复制
<!-- hutool-all -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.6.5</version>
        </dependency>
        <!-- zxingcore -->
        <dependency>
            <groupId>com.google.zxing</groupId>
            <artifactId>core</artifactId>
            <version>3.4.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

CExcelUtil自定义的工具类

代码语言：javascript复制
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;


    }


}

控制层

代码语言：javascript复制
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();


    }


}

实体类

代码语言：javascript复制
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;
}

参考

Hutool参考文档

excel import 对象 集合 数据

0 人点赞