SpringBoot整合EasyExcel

2023-12-13 12:02:11

目录

一、EasyExcel介绍

1、简介

2、常用注解

二、SpringBoot整合EasyExcel

1、基本环境

(1)引入依赖

(2)创建实体类

2、EasyExcel内容读取

(1)创建监听器

(2)测试

3、EasyExcel内容校验

(1)场景描述? ? ? ??

(2)EasyExcel监听器

(3)测试

4、EasyExcel内容批量插入数据库

(1)创建数据库

(2)编写Mapper

(3)编写Service

?(4)Controller层

(5)自定义转换器

(6)EasyExcel监听器

(7)测试

5、EasyExcel写入文件（基础版）

(1)写入操作

(2)测试结果?

6、EasyExcel写入文件（Web模式基础版）

(1)Web模式写入操作

(2)测试结果?

7、EasyExcel写入文件（Web模式进阶版）

(1)Web模式写入操作进阶版

(2)测试结果

8、EasyExcel写入操作（批量写入）

(1)Mapper文件

(2)编写Service

(3)Controller层

?(4)测试结果

一、EasyExcel介绍

1、简介

????????EasyExcel是阿里巴巴开源poi插件之一，是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。尽管Java解析、生成Excel不止EasyExcel，还有一些其他的框架，例如：Apache poi、jxl，但这些都存在一个严重的问题就是非常的耗内存。而EasyExcel是在尽可能节约内存的情况下支持读写百M的Excel，能在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。因此，本文将通过一个书籍列表的导入，作为案例，来揭开EasyExcel的使用过程。

2、常用注解

  • @ExcelProperty：指定当前字段对应excel中的那一列。可以根据名字或者Index去匹配。当然也可以不写，默认第一个字段就是index=0，以此类推。千万注意，要么全部不写，要么全部用index，要么全部用名字去匹配。千万别三个混着用，除非你非常了解源代码中三个混着用怎么去排序的。
  • @ExcelIgnore：默认所有字段都会和excel去匹配，加了这个注解会忽略该字段
  • @DateTimeFormat：日期转换，用String去接收excel日期格式的数据会调用这个注解。里面的value参照java.text.SimpleDateFormat
  • @NumberFormat：数字转换，用String去接收excel数字格式的数据会调用这个注解。里面的value参照java.text.DecimalFormat
  • @ExcelIgnoreUnannotated：默认不加ExcelProperty 的注解的都会参与读写，加了不会参与

二、SpringBoot整合EasyExcel

1、基本环境

(1)引入依赖

		<!-- 查看excel的maven仓库 https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>3.1.2</version>
		</dependency>

(2)创建实体类


import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.math.BigDecimal;


@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Book {
    @ExcelProperty(value = "发布日期")
    @DateTimeFormat("yyyy-MM-dd")
    private String publishDate;

    @ExcelProperty("书名")
    private String bookName;

    @ExcelProperty("作者")
    private String author;

    @ExcelProperty("价格")
    private BigDecimal price;

    @ExcelProperty("售卖数量")
    private Integer saleNum;

    @ExcelProperty(value = "是否热卖")
    private String status;
}

2、EasyExcel内容读取

(1)创建监听器


import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.yht.entity.Book;

import java.util.*;

public class ExcelListener extends AnalysisEventListener<Book> {


    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("解析表头信息");
        System.out.println(headMap);
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) {
        System.out.println("Error Message : " + exception);
    }

    @Override
    public void invoke(Book book, AnalysisContext analysisContext) {
        System.out.println(book);
    }


    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("解析完毕");
    }

    @Override
    public boolean hasNext(AnalysisContext context) {
        return super.hasNext(context);
    }
}

(2)测试

主方法：?

    public static void main(String[] args) {
		String filename="D:\\其他\\书籍列表.xlsx";
		System.out.println("Excel内容如下：");
		ExcelListener excelListener = new ExcelListener();
		EasyExcel.read(filename, Book.class, excelListener).sheet(0).doRead();
		
	}

测试结果：?

3、EasyExcel内容校验

(1)场景描述? ? ? ??

????????上面介绍的内容只是一个简单的Excel读取案例。接下来，我们可以在解析加一些校验规则，例如：书籍的发布日期格式必须为YYYY-MM-DD格式；价格的整数位不能超过10位，小数位不能超过三位。接下来就来改造ExcelListener，以实现我们的需求。

(2)EasyExcel监听器



import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.exception.ExcelAnalysisStopException;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.example.yht.entity.Book;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

public class ExcelListener extends AnalysisEventListener<Book> {
    private List<Book> list = new ArrayList<>();

    private List<String> errList= new ArrayList<>();

    public List<Book> getList() {
        return list;
    }

    public void setList(List<Book> list) {
        this.list = list;
    }

    public List<String> getErrList() {
        return errList;
    }

    public void setErrList(List<String> errList) {
        this.errList = errList;
    }

    public int getRow() {
        return row;
    }

    public void setRow(int row) {
        this.row = row;
    }

    public int getCol() {
        return col;
    }

    public void setCol(int col) {
        this.col = col;
    }

    public Map<Integer, String> getErrorMessageMap() {
        return errorMessageMap;
    }

    private int row = 1;

    private int col = 1;

    private final Map<Integer, String> errorMessageMap = new HashMap<>();

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("解析表头信息");
        System.out.println(headMap);
        row++;
    }

    @Override
    public void onException(Exception exception, AnalysisContext context)  {
        String errMsg = "第" + row + "行, 第" + col + "列解析失败";
        errList.add(errMsg);
        row++ ;
        if(exception instanceof ExcelAnalysisException) {
            throw new ExcelAnalysisStopException("解析失败！");
        }else{
            throw new ExcelAnalysisStopException("解析失败！");
        }
       
    }

    @Override
    public void invoke(Book book, AnalysisContext analysisContext) {
        if(book == null){
            return;
        }
        if(!preDealEntity(book)){
            throw new ExcelAnalysisException("数据解析错误");
        }
        list.add(book);
        row++;

    }

    public boolean preDealEntity(Book book){
        if(null == book){
            return false;
        }
        Field[] fields = book.getClass().getDeclaredFields();
        col = 1;
        for(int i=0; i < fields.length; i++){
            Field field = fields[i];
            field.setAccessible(true);
            try {
                Object val = field.get(book);
                if(val != null){
                    if(field.getName().equals("publishDate") && !isValidDate((String)val, "yyyy-MM-dd")){
                        return false;
                    }
                    if(val instanceof BigDecimal && !isNumeric(val+"")){
                        return false;
                    }
                }
                col++;
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    public static boolean isNumeric(String str) {
        if (null == str || "".equals(str)) {
            return false;
        }
        String regx = "[+-]*\\d+\\.?\\d*[Ee]*[+-]*\\d+";
        Pattern pattern = Pattern.compile(regx);
        boolean isNumber = pattern.matcher(str).matches();
        if (!isNumber) {
            return isNumber;
        }
        regx = "^[-\\+]?[.\\d]*$";
        pattern = Pattern.compile(regx);
        isNumber = pattern.matcher(str).matches();
        if (!isNumber) {
            return isNumber;
        }
        String[] arrs = str.split("\\.");
        return arrs[0].length() <= 10 && arrs[1].length() <= 3;
    }

    public static boolean isValidDate(String dateStr, String format) {
        SimpleDateFormat sdf = new SimpleDateFormat(format);
        sdf.setLenient(false); // 设置解析日期时严格匹配，不容忍格式错误

        try {
            sdf.parse(dateStr);
            return true;
        } catch (ParseException e) {
            return false;
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("解析完毕");
    }

    @Override
    public boolean hasNext(AnalysisContext context) {
        return super.hasNext(context);
    }
}

(3)测试

主方法：

    public static void main(String[] args) {
		//SpringApplication.run(FtpDemoApplication.class);
		String filename="D:\\其他\\书籍列表.xlsx";
		System.out.println("Excel内容如下：");
		ExcelListener excelListener = new ExcelListener();
		EasyExcel.read(filename, Book.class, excelListener).sheet(0).doRead();
		if(excelListener.getErrList().isEmpty()){
			System.out.println("所有数据解析成功！");
			System.out.println(excelListener.getList());
		}else{
			System.out.println("数据解析失败：");
			System.out.println(excelListener.getErrList());
		}
	}

第一种情况：解析正常的情况

Excel内容如下：
21:05:22.869 [main] DEBUG com.alibaba.excel.metadata.property.ExcelHeadProperty - The initialization sheet/table 'ExcelHeadProperty' is complete , head kind is CLASS
21:05:22.891 [main] DEBUG com.alibaba.excel.context.AnalysisContextImpl - Initialization 'AnalysisContextImpl' complete
21:05:23.018 [main] DEBUG com.alibaba.excel.cache.selector.SimpleReadCacheSelector - Use map cache.size:590
21:05:23.227 [main] DEBUG com.alibaba.excel.metadata.property.ExcelHeadProperty - The initialization sheet/table 'ExcelHeadProperty' is complete , head kind is CLASS
21:05:23.227 [main] DEBUG com.alibaba.excel.context.AnalysisContextImpl - Began to read：com.alibaba.excel.read.metadata.holder.xlsx.XlsxReadSheetHolder@6a021575
解析表头信息
{0=发布日期, 1=书名, 2=作者, 3=价格, 4=售卖数量, 5=是否热卖}
解析完毕
所有数据解析成功！
[Book(publishDate=2013-5-4, bookName=三国演义, author=罗贯中, price=24.15, saleNum=34, status=是), Book(publishDate=2016-2-14, bookName=水浒传, author=施耐庵, price=49.34, saleNum=23, status=否), Book(publishDate=2013-12-14, bookName=三国演义, author=罗贯中, price=24.15, saleNum=1, status=是)]

Process finished with exit code 0

????????

在表格中添加一些错误数据，如下：

? ? ? ? 可以看到：上面的代码案例中，如果某一行解析失败，则剩余行不做解析，直接返回解析错误的信息。

? ? ? ? 还有另外一种错误情况：如果某一行解析失败，剩余行依旧需要解析，直到解析完毕返回所有的错误信息。那么可以将监听器代码改成下面这样：



import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.exception.ExcelAnalysisStopException;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.example.yht.entity.Book;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

public class ExcelListener extends AnalysisEventListener<Book> {
    private List<Book> list = new ArrayList<>();

    private List<String> errList= new ArrayList<>();

    public List<Book> getList() {
        return list;
    }

    public void setList(List<Book> list) {
        this.list = list;
    }

    public List<String> getErrList() {
        return errList;
    }

    public void setErrList(List<String> errList) {
        this.errList = errList;
    }

    public int getRow() {
        return row;
    }

    public void setRow(int row) {
        this.row = row;
    }

    public int getCol() {
        return col;
    }

    public void setCol(int col) {
        this.col = col;
    }

    public Map<Integer, String> getErrorMessageMap() {
        return errorMessageMap;
    }

    private int row = 1;

    private int col = 1;

    private final Map<Integer, String> errorMessageMap = new HashMap<>();

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("解析表头信息");
        System.out.println(headMap);
        row++;
    }

    @Override
    public void onException(Exception exception, AnalysisContext context)  {
        String errMsg = "第" + row + "行, 第" + col + "列解析失败";
        row++ ;     
    }

    @Override
    public void invoke(Book book, AnalysisContext analysisContext) {
        if(book == null){
            return;
        }
        preDealEntity(book);
        list.add(book);
        row++;
    }

    public void preDealEntity(Book book){
        if(null == book){
            return;
        }
        Field[] fields = book.getClass().getDeclaredFields();
        col = 1;
        for(int i=0; i < fields.length; i++){
            Field field = fields[i];
            field.setAccessible(true);
            try {
                Object val = field.get(book);
                if(val != null){
                    if(field.getName().equals("publishDate") && !isValidDate((String)val, "yyyy-MM-dd")){
                        String errMsg = "第" + row + "行, 第" + col + "列解析失败";
                        errList.add(errMsg);
                    }
                    if(val instanceof BigDecimal && !isNumeric(val+"")){
                        String errMsg = "第" + row + "行, 第" + col + "列解析失败";
                        errList.add(errMsg);
                    }
                }
                col++;
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
    }

    public static boolean isNumeric(String str) {
        if (null == str || "".equals(str)) {
            return false;
        }
        String regx = "[+-]*\\d+\\.?\\d*[Ee]*[+-]*\\d+";
        Pattern pattern = Pattern.compile(regx);
        boolean isNumber = pattern.matcher(str).matches();
        if (!isNumber) {
            return isNumber;
        }
        regx = "^[-\\+]?[.\\d]*$";
        pattern = Pattern.compile(regx);
        isNumber = pattern.matcher(str).matches();
        if (!isNumber) {
            return isNumber;
        }
        String[] arrs = str.split("\\.");
        return arrs[0].length() <= 10 && arrs[1].length() <= 3;
    }

    public static boolean isValidDate(String dateStr, String format) {
        SimpleDateFormat sdf = new SimpleDateFormat(format);
        sdf.setLenient(false); // 设置解析日期时严格匹配，不容忍格式错误

        try {
            sdf.parse(dateStr);
            return true;
        } catch (ParseException e) {
            return false;
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("解析完毕");
    }

    @Override
    public boolean hasNext(AnalysisContext context) {
        return super.hasNext(context);
    }
}

4、EasyExcel内容批量插入数据库

(1)创建数据库

create table t_book(
	publish_date date,
	book_name varchar(50),
	author varchar(20),
	price decimal(10,3),
	sale_num int,
	status tinyint(1)
);

(2)编写Mapper

@Mapper
public interface BookMapper {
    Integer saveBook(@Param("bookList") List<Book> bookList);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace：指定了唯一的命名空间-->
<mapper namespace="com.example.yht.mapper.BookMapper">

    <insert id="saveBook">
        insert into t_book(publish_date, book_name, author, price, sale_num, status)
        VALUES
        <foreach collection="bookList" index="" item="book" separator=",">
            (#{book.publishDate},
            #{book.bookName},
            #{book.author},
            #{book.price},
            #{book.saleNum},
            #{book.status})
        </foreach>
    </insert>
</mapper>

(3)编写Service

public interface IBookService {
    Integer saveBook(List<Book> bookList);
}

@Service
public class BookServiceImpl implements IBookService {
    @Autowired
    private BookMapper bookMapper;
    @Override
    public Integer saveBook(List<Book> bookList) {
        return bookMapper.saveBook(bookList);
    }
}

?(4)Controller层



import com.alibaba.excel.EasyExcel;
import com.example.yht.entity.Book;
import com.example.yht.entity.Student;
import com.example.yht.listener.ExcelListener;
import com.example.yht.service.IBookService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import java.text.SimpleDateFormat;

@RestController
public class TestController {

    @Autowired
    private IBookService bookService;

    @GetMapping("/excelToDB")
    public void excelToDB(){
        String filename="D:\\其他\\书籍列表.xlsx";
        System.out.println("Excel内容如下：");
        ExcelListener excelListener = new ExcelListener(bookService);
        EasyExcel.read(filename, Book.class, excelListener).sheet(0).doRead();
        if(excelListener.getErrList().isEmpty()){
            System.out.println("所有数据解析成功！");
        }else{
            System.out.println("数据解析失败：");
            System.out.println(excelListener.getErrList());
        }
    }
}

(5)自定义转换器

????????从Excel中可以看到一个细节，是否热卖这一列在excel里面的值有两种：是/否。但是在数据库里面，status的类型却是tinyint。因此我们就需要将excel里面的是/否转换为1/0，从而存储在数据库中。这里我们介绍一个Excel的转换器，可以实现这项操作。

????????这里首先将Book的实体类中的status改为Integer类型：

    @ExcelProperty(value = "是否热卖", converter = StatusConverter.class)
    private Integer status;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

public class StatusConverter implements Converter<Integer> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return Converter.super.supportJavaTypeKey();
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return Converter.super.supportExcelTypeKey();
    }

    @Override
    public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return Converter.super.convertToJavaData(cellData, contentProperty, globalConfiguration);
    }

    @Override
    public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
        return context.getReadCellData().getStringValue().equals("是") ? 1 : 0;
    }

    @Override
    public WriteCellData<?> convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return new WriteCellData<Integer>(value.equals(1) ? "是" : "否");
    }


}

(6)EasyExcel监听器


import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.exception.ExcelAnalysisStopException;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.util.ListUtils;
import com.example.yht.entity.Book;
import com.example.yht.service.IBookService;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

public class ExcelListener extends AnalysisEventListener<Book> {
    //假设每5条插入一次数据库
    private static int BATCH_COUNT = 5;

    private List<Book> bookList = new ArrayList<>();

    private List<String> errList= new ArrayList<>();

    private IBookService bookService;

    public ExcelListener(IBookService bookService) {
        this.bookService = bookService;
    }

    public List<Book> getBookList() {
        return bookList;
    }

    public void setBookList(List<Book> bookList) {
        this.bookList = bookList;
    }


    public List<String> getErrList() {
        return errList;
    }

    public void setErrList(List<String> errList) {
        this.errList = errList;
    }

    public int getRow() {
        return row;
    }

    public void setRow(int row) {
        this.row = row;
    }

    public int getCol() {
        return col;
    }

    public void setCol(int col) {
        this.col = col;
    }

    public Map<Integer, String> getErrorMessageMap() {
        return errorMessageMap;
    }

    private int row = 1;

    private int col = 1;

    private final Map<Integer, String> errorMessageMap = new HashMap<>();

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("解析表头信息");
        System.out.println(headMap);
        row++;
    }

    @Override
    public void onException(Exception exception, AnalysisContext context)  {
        String errMsg = "第" + row + "行, 第" + col + "列解析失败";
        errList.add(errMsg);
        System.out.println(exception);
        row++ ;
        if(exception instanceof ExcelAnalysisException) {
            throw new ExcelAnalysisStopException("解析失败！");
        }else{
            throw new ExcelAnalysisStopException("解析失败！");
        }

    }

    @Override
    public void invoke(Book book, AnalysisContext analysisContext) {
        if(book == null){
            return;
        }
        if(!preDealEntity(book)){
            throw new ExcelAnalysisException("数据解析错误");
        }
        bookList.add(book);
        if(bookList.size() >= BATCH_COUNT){
            saveData(bookList);
        }
        row++;

    }

    /**
     * 保存数据
     * @param list
     */
    private void saveData(List<Book> list) {
        bookService.saveBook(list);
        bookList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    }

    public boolean preDealEntity(Book book){
        if(null == book){
            return false;
        }
        Field[] fields = book.getClass().getDeclaredFields();
        col = 1;
        for(int i=0; i < fields.length; i++){
            Field field = fields[i];
            field.setAccessible(true);
            try {
                Object val = field.get(book);
                if(val != null){
                    if(field.getName().equals("publishDate") && !isValidDate((String)val, "yyyy-MM-dd")){
                        return false;
                    }
                    if(val instanceof BigDecimal && !isNumeric(val+"")){
                        return false;
                    }
                }
                col++;
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    public static boolean isNumeric(String str) {
        if (null == str || "".equals(str)) {
            return false;
        }
        String regx = "[+-]*\\d+\\.?\\d*[Ee]*[+-]*\\d+";
        Pattern pattern = Pattern.compile(regx);
        boolean isNumber = pattern.matcher(str).matches();
        if (!isNumber) {
            return isNumber;
        }
        regx = "^[-\\+]?[.\\d]*$";
        pattern = Pattern.compile(regx);
        isNumber = pattern.matcher(str).matches();
        if (!isNumber) {
            return isNumber;
        }
        String[] arrs = str.split("\\.");
        return arrs[0].length() <= 10 && arrs[1].length() <= 3;
    }

    public static boolean isValidDate(String dateStr, String format) {
        SimpleDateFormat sdf = new SimpleDateFormat(format);
        sdf.setLenient(false); // 设置解析日期时严格匹配，不容忍格式错误

        try {
            sdf.parse(dateStr);
            return true;
        } catch (ParseException e) {
            return false;
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("解析完毕");
        //解析完之后，将list中的数据保存在数据库中
        saveData(bookList);
    }

    @Override
    public boolean hasNext(AnalysisContext context) {
        return super.hasNext(context);
    }
}

(7)测试

页面请求：http://localhost:8800/excelToDB

终端输出：

数据库内容：?

5、EasyExcel写入文件（基础版）

(1)写入操作

????????这里先看一个基本的案例，首先指定文件位置，并添加一些模拟数据，再通过write方法写入该文件中。

    @GetMapping("/writeBasic")
    public void writeBasic(){
        String fileName = "D:\\Data\\easyexcel.xlsx";
        List<Book> list = new ArrayList<>();
        list.add(new Book("2022-12-1", "人物传记1", "迪丽热巴1", new BigDecimal("34.5"), 20, 1));
        list.add(new Book("2022-12-2", "人物传记2", "迪丽热巴2", new BigDecimal("44.5"), 30, 0));
        list.add(new Book("2022-12-3", "人物传记3", "迪丽热巴3", new BigDecimal("54.5"), 40, 1));
        EasyExcel.write(fileName, Book.class).sheet("模板").doWrite(list);
    }

(2)测试结果?

浏览器请求：http://localhost:8800/writeBasic

6、EasyExcel写入文件（Web模式基础版）

(1)Web模式写入操作

????????Web模式的写入操作，可以在页面发起请求的时候，将数据内容写入到excel中，并下载到本地。代码如下：

 @GetMapping("/doWrite")
    public void download(HttpServletResponse response) throws IOException {
        // 这里直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("书籍列表.xlsx", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);

        //模拟要写入excel的数据
        List<Book> list = new ArrayList<>();
        list.add(new Book("2023-1-1", "花朵1", "张清1", new BigDecimal("34.5"), 20, 1));
        list.add(new Book("2023-1-2", "花朵2", "张清2", new BigDecimal("44.5"), 30, 0));
        list.add(new Book("2023-1-3", "花朵3", "张清3", new BigDecimal("54.5"), 40, 1));

        EasyExcel.write(response.getOutputStream(), Book.class).sheet("新增").doWrite(list);
    }

(2)测试结果?

浏览器发起请求：http://localhost:8800/doWrite

?文件下载：

表格内容：

7、EasyExcel写入文件（Web模式进阶版）

(1)Web模式写入操作进阶版

这里的进阶版是在基础班的上面，加入了文件流的关闭操作等。

 @GetMapping("/doWrite2")
    public void downloadAdvanced(HttpServletResponse response){
        //模拟写入的数据
        List<Book> list = new ArrayList<>();
        list.add(new Book("2023-2-1", "碧螺春1", "肖战1", new BigDecimal("34.5"), 20, 1));
        list.add(new Book("2023-2-2", "碧螺春2", "肖战2", new BigDecimal("44.5"), 30, 0));
        list.add(new Book("2023-2-3", "碧螺春3", "肖战3", new BigDecimal("54.5"), 40, 1));

        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //防止中文乱码
            String fileName = URLEncoder.encode("书籍列表-new.xlsx", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
            ServletOutputStream outputStream = response.getOutputStream();
            //工作簿对象
            ExcelWriterBuilder writerWork = EasyExcel.write(outputStream, Book.class);
            //工作表对象
            ExcelWriterSheetBuilder sheet = writerWork.sheet(0);
            sheet.doWrite(list);
            outputStream.flush();
            outputStream.close();

        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            e.printStackTrace();

        }
    }

(2)测试结果

?浏览器发起请求：http://localhost:8800/doWrite2

8、EasyExcel写入操作（批量写入）

????????既然我们在插入数据库的时候，有批量插入的功能，那么自然也可以实现批量写入excel的方式。这里我们就从数据库里将插入的数据再次读?取出来，放入一个新的excel表格中。

(1)Mapper文件

    <select id="selectCount" resultType="Integer">
        select count(*) from t_book
    </select>

    <select id="selectBatch" resultType="com.example.yht.entity.Book" parameterType="map">
        select
            publish_date as publishDate,
            book_name as bookName,
            author as author,
            price as price,
            sale_num as saleNum,
            status as status
        from t_book limit ${limitVal} offset ${offsetVal}
    </select>

    Integer selectCount();

    List<Book> selectBatch(Map<String, Object> map);

(2)编写Service

    Integer selectCount();
    List<Book> selectBatch(Map<String, Object> map);
    @Override
    public Integer selectCount() {
        return bookMapper.selectCount();
    }

    @Override
    public List<Book> selectBatch(Map<String, Object> map) {
        return bookMapper.selectBatch(map);
    }

(3)Controller层

这里为了验证批量插入的效果，在每次读取之后，写入不同的sheet。

@GetMapping("/doWriteBatch")
    public void downloadBatch(HttpServletResponse response){
        // 数据的总量(要写入Excel文件中的数据)
        int count  = bookService.selectCount();
        int batchCount = 5;

        List<Book> dataList = new ArrayList<>(batchCount);
        // 总Sheet数
        int num = count / batchCount;
        // 输出流
        OutputStream outputStream = null;
        try {
            response.setCharacterEncoding("UTF-8");
            String excelName = URLEncoder.encode("batch_book", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + excelName + ".xlsx");
            // 以字节流的形式输出响应正文
            outputStream = response.getOutputStream();

            try (ExcelWriter excelWriter = EasyExcel.write(outputStream, Book.class).build()) {
                // 调用写入
                for (int i = 0; i <= num; i++) {
                    // 每次写入都要创建WriteSheet, 这里注意必须指定sheetNo, 并且sheetName必须不一样
                    Map<String, Object> queryMap = new HashMap<>();
                    queryMap.put("limitVal", batchCount);
                    queryMap.put("offsetVal", i * batchCount);
                    WriteSheet writeSheet = EasyExcel.writerSheet(i, "batch" + (i + 1)).build();
                    dataList = bookService.selectBatch(queryMap);
                    System.out.println(dataList);
                    excelWriter.write(dataList, writeSheet);
                }
            }
            // 清空集合
            dataList.clear();
        } catch (IOException ex) {
            throw new RuntimeException(ex);
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException ex) {
                    throw new RuntimeException(ex);
                }
            }
        }
    }

?(4)测试结果

请求：http://localhost:8800/doWriteBatch

?

文章来源:https://blog.csdn.net/weixin_47382783/article/details/134469932
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。