Springboot集成EasyExcel实现文件上传下载表格

2023-11-24 01:13:28

1.使用easyexcel读取表格

package com.frank.excel;import com.alibaba.excel.metadata.Sheet;
import com.frank.excel.model.ModelInfo;
import com.frank.excel.util.ExcelUtil;
import org.springframework.beans.BeanUtils;import java.util.ArrayList;
import java.util.List;/*** @author 小石潭记* @date 2020/10/7 16:29* @Description: ${todo}*/
public class ReadDemo {public static void main(String[] args) {String filePath = "D://学生表少.xlsx";// 默认读取// 读取Sheet1的全部数据List objects = ExcelUtil.readLessThan1000Row(filePath);System.out.println(objects);// 获取Sheet1表头以下的信息//第一个1代表sheet1, 第二个1代表从第几行开始读取数据，行号最小值为0Sheet sheet = new Sheet(1, 1);List objects1 = ExcelUtil.readLessThan1000RowBySheet(filePath,sheet);List result = new ArrayList<>();for (int i = 0; i < objects1.size(); i++) {ModelInfo modelInfo = new ModelInfo();List o = (List) objects1.get(i);modelInfo.setName(o.get(0));modelInfo.setAge(Integer.parseInt(o.get(1)));modelInfo.setSchool(o.get(2));result.add(modelInfo);}System.out.println(result);// 获取Sheet2的所有信息, 后面的1是不读取第一行的数据Sheet sheet2 = new Sheet(2, 1);List objects2 = ExcelUtil.readLessThan1000RowBySheet(filePath, sheet2);System.out.println(objects2);// 读取大于1000行的数据String filePath1 = "D://学生表多.xlsx";List objects3 = ExcelUtil.readMoreThan1000Row(filePath1);System.out.println(objects3);// 指定读取 读取sheet1 从第三行开始读取Sheet sheet3 = new Sheet(1, 2);List objects4 = ExcelUtil.readMoreThan1000RowBySheet(filePath1, sheet3);System.out.println(objects4);}}
 

2.使用easyexcel导出表格
 

package com.frank.excel;import com.alibaba.excel.metadata.Sheet;
import com.frank.excel.model.TableHeaderExcelProperty;
import com.frank.excel.util.ExcelUtil;import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;/*** @author 小石潭记* @date 2020/10/7 19:34* @Description: ${todo}*/
public class ExportExcel {public static void main(String[] args) {// 导出excelString filePath = "D://测试.xlsx";/*List> data = new ArrayList<>();data.add(Arrays.asList("111","222","333"));data.add(Arrays.asList("111","222","333"));data.add(Arrays.asList("111","222","333"));List head = Arrays.asList("表头1", "表头2", "表头3");ExcelUtil.writeBySimple(filePath,data,head);*/// 导出到单个表 sheet/*ArrayList data1 = new ArrayList<>();for(int i = 0; i < 4; i++){TableHeaderExcelProperty tableHeaderExcelProperty = new TableHeaderExcelProperty();tableHeaderExcelProperty.setName("cmj" + i);tableHeaderExcelProperty.setAge(22 + i);tableHeaderExcelProperty.setSchool("清华大学" + i);data1.add(tableHeaderExcelProperty);}ExcelUtil.writeWithTemplate(filePath,data1);*/// 导出到多个sheetArrayList list1 = new ArrayList<>();for(int j = 1; j < 4; j++){ArrayList list = new ArrayList<>();for(int i = 0; i < 4; i++){TableHeaderExcelProperty tableHeaderExcelProperty = new TableHeaderExcelProperty();tableHeaderExcelProperty.setName("cmj" + i);tableHeaderExcelProperty.setAge(22 + i);tableHeaderExcelProperty.setSchool("清华大学" + i);list.add(tableHeaderExcelProperty);}Sheet sheet = new Sheet(j, 0);sheet.setSheetName("sheet" + j);ExcelUtil.MultipleSheelPropety multipleSheelPropety = new ExcelUtil.MultipleSheelPropety();multipleSheelPropety.setData(list);multipleSheelPropety.setSheet(sheet);list1.add(multipleSheelPropety);}ExcelUtil.writeWithMultipleSheel("D://aaa.xlsx",list1);}}

 

2.1 TableHeaderExcelProperty
 

package com.frank.excel.model;import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
import lombok.EqualsAndHashCode;/*** @author 小石潭记* @date 2020/10/7 19:35* @Description: ${todo}*/
@EqualsAndHashCode(callSuper = true)
@Data
public class TableHeaderExcelProperty extends BaseRowModel {/*** value: 表头名称* index: 列的号, 0表示第一列*/@ExcelProperty(value = "姓名", index = 0)private String name;@ExcelProperty(value = "年龄",index = 1)private int age;@ExcelProperty(value = "学校",index = 2)private String school;
}

 

2.2 ModelInfo
 

package com.frank.excel.model;import lombok.Data;/*** @author 小石潭记* @date 2020/10/7 19:42* @Description: ${todo}*/
@Data
public class ModelInfo {private String name;private int age;private String school;}

 

 
 

3.springboot使用easyexcel实现上传下载表格
 

3.1 引入相关依赖
 


com.alibabaeasyexcel1.1.2-beta5

 

3.2 创建ExcelListener
 

package com.frank.excel.listener;import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;import java.util.ArrayList;
import java.util.List;/*** @author 小石潭记* @date 2020/10/7 21:09* @Description: ${todo}*/
public class ExcelListener extends AnalysisEventListener {//可以通过实例获取该值private List datas = new ArrayList();public void invoke(Object o, AnalysisContext analysisContext) {datas.add(o);//数据存储到list，供批量处理，或后续自己业务逻辑处理。doSomething(o);//根据自己业务做处理}private void doSomething(Object object) {//1、入库调用接口}public List getDatas() {return datas;}public void setDatas(List datas) {this.datas = datas;}public void doAfterAllAnalysed(AnalysisContext analysisContext) {// datas.clear();//解析结束销毁不用的资源}}
 

3.3 相关工具类
 

package com.frank.excel.util;import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import lombok.Data;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;/*** @author 小石潭记* @date 2020/10/7 18:57* @Description: ${todo}*/
@Slf4j
public class ExcelUtil {private static Sheet initSheet;static {initSheet = new Sheet(1, 0);initSheet.setSheetName("sheet");//设置自适应宽度initSheet.setAutoWidth(Boolean.TRUE);}/*** 读取少于1000行数据* @param filePath 文件绝对路径* @return*/public static List readLessThan1000Row(String filePath){return readLessThan1000RowBySheet(filePath,null);}/*** 读小于1000行数据, 带样式* filePath 文件绝对路径* initSheet ：*      sheetNo: sheet页码，默认为1*      headLineMun: 从第几行开始读取数据，默认为0, 表示从第一行开始读取*      clazz: 返回数据List 中Object的类名*/public static List readLessThan1000RowBySheet(String filePath, Sheet sheet){if(!StringUtils.hasText(filePath)){return null;}sheet = sheet != null ? sheet : initSheet;InputStream fileStream = null;try {fileStream = new FileInputStream(filePath);return EasyExcelFactory.read(fileStream, sheet);} catch (FileNotFoundException e) {log.info("找不到文件或文件路径错误, 文件：{}", filePath);}finally {try {if(fileStream != null){fileStream.close();}} catch (IOException e) {log.info("excel文件读取失败, 失败原因：{}", e);}}return null;}/*** 读大于1000行数据* @param filePath 文件觉得路径* @return*/public static List readMoreThan1000Row(String filePath){return readMoreThan1000RowBySheet(filePath,null);}/*** 读大于1000行数据, 带样式* @param filePath 文件觉得路径* @return*/public static List readMoreThan1000RowBySheet(String filePath, Sheet sheet){if(!StringUtils.hasText(filePath)){return null;}sheet = sheet != null ? sheet : initSheet;InputStream fileStream = null;try {fileStream = new FileInputStream(filePath);ExcelListener excelListener = new ExcelListener();EasyExcelFactory.readBySax(fileStream, sheet, excelListener);return excelListener.getDatas();} catch (FileNotFoundException e) {log.error("找不到文件或文件路径错误, 文件：{}", filePath);}finally {try {if(fileStream != null){fileStream.close();}} catch (IOException e) {log.error("excel文件读取失败, 失败原因：{}", e);}}return null;}/*** 生成excle* @param filePath  绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx* @param data 数据源* @param head 表头*/public static void writeBySimple(String filePath, List> data, List head){writeSimpleBySheet(filePath,data,head,null);}/*** 生成excle* @param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx* @param data 数据源* @param sheet excle页面样式* @param head 表头*/public static void writeSimpleBySheet(String filePath, List> data, List head, Sheet sheet){sheet = (sheet != null) ? sheet : initSheet;if(head != null){List> list = new ArrayList<>();head.forEach(h -> list.add(Collections.singletonList(h)));sheet.setHead(list);}OutputStream outputStream = null;ExcelWriter writer = null;try {outputStream = new FileOutputStream(filePath);writer = EasyExcelFactory.getWriter(outputStream);writer.write1(data,sheet);} catch (FileNotFoundException e) {log.error("找不到文件或文件路径错误, 文件：{}", filePath);}finally {try {if(writer != null){writer.finish();}if(outputStream != null){outputStream.close();}} catch (IOException e) {log.error("excel文件导出失败, 失败原因：{}", e);}}}/*** 生成excle* @param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx* @param data 数据源*/public static void writeWithTemplate(String filePath, List data){writeWithTemplateAndSheet(filePath,data,null);}/*** 生成excle* @param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx* @param data 数据源* @param sheet excle页面样式*/public static void writeWithTemplateAndSheet(String filePath, List data, Sheet sheet){if(CollectionUtils.isEmpty(data)){return;}sheet = (sheet != null) ? sheet : initSheet;sheet.setClazz(data.get(0).getClass());OutputStream outputStream = null;ExcelWriter writer = null;try {outputStream = new FileOutputStream(filePath);writer = EasyExcelFactory.getWriter(outputStream);writer.write(data,sheet);} catch (FileNotFoundException e) {log.error("找不到文件或文件路径错误, 文件：{}", filePath);}finally {try {if(writer != null){writer.finish();}if(outputStream != null){outputStream.close();}} catch (IOException e) {log.error("excel文件导出失败, 失败原因：{}", e);}}}/*** 生成多Sheet的excle* @param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx* @param multipleSheelPropetys*/public static void writeWithMultipleSheel(String filePath,List multipleSheelPropetys){if(CollectionUtils.isEmpty(multipleSheelPropetys)){return;}OutputStream outputStream = null;ExcelWriter writer = null;try {outputStream = new FileOutputStream(filePath);writer = EasyExcelFactory.getWriter(outputStream);for (MultipleSheelPropety multipleSheelPropety : multipleSheelPropetys) {Sheet sheet = multipleSheelPropety.getSheet() != null ? multipleSheelPropety.getSheet() : initSheet;if(!CollectionUtils.isEmpty(multipleSheelPropety.getData())){sheet.setClazz(multipleSheelPropety.getData().get(0).getClass());}writer.write(multipleSheelPropety.getData(), sheet);}} catch (FileNotFoundException e) {log.error("找不到文件或文件路径错误, 文件：{}", filePath);}finally {try {if(writer != null){writer.finish();}if(outputStream != null){outputStream.close();}} catch (IOException e) {log.error("excel文件导出失败, 失败原因：{}", e);}}}/*** 导出 Excel ：一个 sheet，带表头.* @param response  HttpServletResponse* @param list      数据 list，每个元素为一个 BaseRowModel* @param fileName  导出的文件名* @param sheetName 导入文件的 sheet 名* @param model     映射实体类，Excel 模型* @throws Exception 异常*/public static void writeExcel(HttpServletResponse response, List list,String fileName, String sheetName, BaseRowModel model) throws Exception {ExcelWriter writer = new ExcelWriter(getOutputStreamExcel(fileName, response), ExcelTypeEnum.XLSX);Sheet sheet = new Sheet(1, 0, model.getClass());//设置列宽 设置每列的宽度/*Map columnWidth = new HashMap();columnWidth.put(0,10000);columnWidth.put(1,40000);columnWidth.put(2,10000);columnWidth.put(3,10000);sheet1.setColumnWidthMap(columnWidth);*/// 设置自适应宽度sheet.setAutoWidth(Boolean.TRUE);sheet.setSheetName(sheetName);writer.write(list, sheet);writer.finish();}/*** 导出文件时为Writer生成OutputStream.* @param fileName 文件名* @param response response* @return*/private static OutputStream getOutputStreamExcel(String fileName,HttpServletResponse response) throws Exception {try {fileName = URLEncoder.encode(fileName, "UTF-8");response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf8");response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");response.setHeader("Pragma", "public");response.setHeader("Cache-Control", "no-store");response.addHeader("Cache-Control", "max-age=0");return response.getOutputStream();} catch (IOException e) {throw new Exception("导出excel表格失败!", e);}}/*********************匿名内部类开始，可以提取出去******************************/@Datapublic static class MultipleSheelPropety{private List data;private Sheet sheet;}/*** 解析监听器，* 每解析一行会回调invoke()方法。* 整个excel解析结束会执行doAfterAllAnalysed()方法** @author: chenmingjian* @date: 19-4-3 14:11*/@Getter@Setterpublic static class ExcelListener extends AnalysisEventListener {private List datas = new ArrayList<>();/*** 逐行解析* object : 当前行的数据*/@Overridepublic void invoke(Object object, AnalysisContext context) {//当前行// context.getCurrentRowNum()if (object != null) {datas.add(object);}}/*** 解析完所有数据后会调用该方法*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {//解析结束销毁不用的资源}}/************************匿名内部类结束，可以提取出去***************************/}
 

3.4 PersonDto
 

package com.frank.excel.model.dto;import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;/*** @author 小石潭记* @date 2020/10/7 21:10* @Description: ${todo}*/
@Data
public class PersonDto extends BaseRowModel {/** id */@ExcelProperty(index = 0 , value = "id")private String id;/** 姓名 **/@ExcelProperty(index = 1 , value = "姓名")private String name;/** 生日 **/@ExcelProperty(index = 2 , value = "生日" , format = "yyyy-MM-dd")private String birth;}

 

3.5 上传下载的controller
 

package com.frank.excel.web;import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.fastjson.JSON;
import com.frank.excel.listener.ExcelListener;
import com.frank.excel.model.dto.PersonDto;
import com.frank.excel.util.ExcelUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;/*** @author 小石潭记* @date 2020/10/7 20:01* @Description: ${todo}*/
@RestController
public class UploadDownController {/*** 导入数据* @param file*/@PostMapping(value = "upload")public void importExcel(@RequestParam("file") MultipartFile file){try{InputStream inputStream = file.getInputStream();//实例化实现了AnalysisEventListener接口的类ExcelListener listener = new ExcelListener();//传入参数ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLSX, null, listener);//读取信息excelReader.read(new Sheet(1, 1, PersonDto.class));//获取数据List list = listener.getDatas();List lists = new ArrayList();PersonDto catagory = new PersonDto();//转换数据类型,并插入到数据库for (int i = 0; i < list.size(); i++) {catagory = (PersonDto) list.get(i);lists.add(catagory);}System.out.println(JSON.toJSON(lists));}catch (Exception e){e.printStackTrace();}}/*** 下载模板*/@PostMapping(value = "/down")public void downloadExcel(HttpServletRequest request, HttpServletResponse response) {try {List list = new ArrayList<>();ExcelUtil.writeExcel(response, list, "信息", "Sheet1", new PersonDto());} catch (Exception e) {e.printStackTrace();}}}
 

3.6 使用postman测试
 

 

 

点击download下载表格。
 

 
 

代码下载
                        

                        

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！

                        
                        

                    

                    



                    


    
    




                    

    

    




                    

    

        
        

            
相关文章

        

                

            
Duilib中list控件支持ctrl和shif多行选中的实现

        

                

            
[ICML2015]Batch Normalization:Accelerating Deep Network Training by Reducing Internal Covariate Shif

        

                

            
win10系统 微软输入法 于eclipse ctrl+shif+f冲突间接处理办法

        

                

            
Codeforces Round #259 (Div. 2) B. Little Pony and Sort by Shif

        

                

            
读LDD3，内存映射与DMA--PAGE_SHIF…

        

                

            
VMware虚拟机安装XP【要先分区，再设置BOOT 启动CD，shif+上移】

        

                

            
更换iBus五笔的左与右Shif

        

                

            
sublime ctrl+shif+f 没用解决办法

        

                

            
idea 对 ctrl + z 的撤销 是 ctrl + shif + z

        

                

            
计算机最早的设计师应用于,计算机应用基础选择题doc.doc

        

                

            
win10自带截图神器：Win+Shift+S

        

                

            
Python基础之文件目录操作

        

                

            
python简述目录_Python基础之文件目录操作(示例代码)

        

                

            
tp5 如何做数据采集

        

                

            
任务2-7(服务器字体+阿里巴巴矢量库)

        

                

            
html标签（1)：h1~h6,p,br,pre,hr

        

                

            
TI 电量计介绍与芯片选型指南

        

                

            
几款TI电源芯片简介

        

                

            
TI DSP芯片C2000系列读取FLASH数据

        

                

            
德州仪器(Ti)平台嵌入式开发基础

        

                

            
TI三相电机智能栅极驱动芯片特点分类

        

                

            
省选模拟（12.08） T3 圈圈圈圈圈圈圈圈

        

                

            
Hadoop生态圈技术栈（上）

        

                

            
大数据开发基础入门与项目实战（三）Hadoop核心及生态圈技术栈之6.Impala交互式查询

        

                

            
小猿圈之Linux下Mysql 操作命令

        

                

            
大数据Hadoop生态圈常用面试题

        

                

            
大数据开发基础入门与项目实战（三）Hadoop核心及生态圈技术栈之4.Hive DDL、DQL和数据操作

        

                

            
备战Noip2018模拟赛11（B组）T3 Monogatari 物语

        

                

            
【智能优化算法-圆圈搜索算法】基于圆圈搜索算法Circle Search Algorithm求解单目标优化问题附matlab代码

        

                

            
NYOJ 78 圈水池

        

                

            
递归问题 跑道 汽车 绕圈问题 Python实现

        

                

            
Hadoop生态圈（三）：MapReduce