源码下载(亲测可用)
ChaiRongD/Demooo - Gitee.com
部分代码展示
pom
<!-- 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自定义的工具类
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; }