Excel相关操作（二）Springboot框架使用easyexcel工具

最新推荐文章于 2024-06-07 11:37:40 发布

白白白飘

最新推荐文章于 2024-06-07 11:37:40 发布

阅读量2.7k

点赞数

本文链接：https://blog.csdn.net/weixin_45311418/article/details/123306861

版权

课程链接：神器！阿里开源的Excel操作工具
源代码：

1、pom文件导入easyexcel依赖

 <!--easyexcel依赖-->
  <dependency>
       <groupId>com.alibaba</groupId>
       <artifactId>easyexcel</artifactId>
       <version>3.0.5</version>
   </dependency>

2、读取Excel数据

（1）读取–返回Object对象，完整代码

@SpringBootTest
public class ExcelReadTest {
    String PATH = "C:\\Users\\wano\\Desktop\\毕设资料\\技术Demo\\Excel读写\\myExcel\\src\\main\\resources\\static\\testData\\";

    @Test
    public void simpleRead() {
        // 读取文件
        // 创建ExcelReaderBuilder实例
        ExcelReaderBuilder readerBuilder = EasyExcel.read();
        // 获取文件对象
        readerBuilder.file(PATH+"课程体系.xlsx");
        // 指定sheet
        readerBuilder.sheet();
        // 自动关闭输入流
        readerBuilder.autoCloseStream(true);
        // 设置文件格式
        readerBuilder.excelType(ExcelTypeEnum.XLSX);
        // 注册监听器进行数据解析
        readerBuilder.registerReadListener(new AnalysisEventListener() {
            @Override
            public void invoke(Object o, AnalysisContext analysisContext) {
                // 一行数据读取完成后进行回调， 一行数据封装成一个对象后回调
                System.out.println(o);
            }
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                // 文件读取完毕日志
                System.out.println("文件读取完毕!");
            }
        });
        // 构建读取器
        ExcelReader reader = readerBuilder.build();
        // 读取数据
        reader.readAll();
        // 读取完毕
        reader.finish();
    }
}

结果：

{0=1, 1=高等数学a, 2=通识必修课, 3=必修, 4=1, 5=2, 6=24, 7=8, 8=32}
{0=2, 1=大学物理a, 2=通识必修课, 3=必修, 4=1, 5=2, 6=24, 7=8, 8=32}
{0=3, 1=计算机引论, 2=通识必修课, 3=必修, 4=1, 5=2, 6=24, 7=8, 8=32}

（2）读取–返回Map对象，完整写法

@Test
    public void simpleReadMap() {
        // 读取文件 Map
        // 创建ExcelReaderBuilder实例
        ExcelReaderBuilder readerBuilder = EasyExcel.read();
        // 获取文件对象
        readerBuilder.file(PATH+"课程体系.xlsx");
        // 指定sheet
        readerBuilder.sheet();
        // 自动关闭输入流
        readerBuilder.autoCloseStream(true);
        // 设置文件格式
        readerBuilder.excelType(ExcelTypeEnum.XLSX);
        // 注册监听器进行数据解析
        readerBuilder.registerReadListener(new AnalysisEventListener<Map<Integer,String>>() {

            @Override
            public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
                // 一行数据读取完成后进行回调， 一行数据封装成一个Map对象后回调
                Set<Integer> keySet = integerStringMap.keySet();
                Iterator<Integer> iterator = keySet.iterator();
                while (iterator.hasNext()) {
                    Integer key = iterator.next();
                    System.out.print(key + ":" + integerStringMap.get(key) + ", ");
                }
                System.out.println();
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                // 文件读取完毕日志
                System.out.println("文件读取完毕!");
            }
        });
        // 构建读取器
        ExcelReader reader = readerBuilder.build();
        // 读取数据
        reader.readAll();
        // 读取完毕
        reader.finish();
    }

（3）读取–返回Map对象，存入List数组中，简略写法

@Test
public void shortReadList(){
    List<Map<Integer,String>> list = new LinkedList<>();
    EasyExcel.read(PATH+"课程体系.xlsx")
            .sheet()
            .registerReadListener(new AnalysisEventListener<Map<Integer,String>>() {

                @Override
                public void invoke(Map<Integer, String> map, AnalysisContext analysisContext) {
                    list.add(map);
                }

                @Override
                public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                    System.out.println("数据读取完毕~！");
                }
            }).doRead();
    // System.out.println(list);
    for (Map<Integer,String> map: list) {
        Set<Integer> keySet = map.keySet();
        Iterator<Integer> iterator = keySet.iterator();
        while (iterator.hasNext()) {
            Integer key = iterator.next();
            System.out.print(key + ":" + map.get(key) + ", ");
        }
        System.out.println();
    }
}

（4）读取–封装成Course对象返回，简略写法

添加head方法，将封装对象写入 .head(Course.class)
在Course实体类的属性上添加注解@ExcelProperty("属性名称")
数据存储在list中

@SpringBootTest
public class ExcelReadTest {
    String PATH = "C:\\Users\\wano\\Desktop\\毕设资料\\技术Demo\\Excel读写\\myExcel\\src\\main\\resources\\static\\testData\\";

     @Test
    public void shortReadCourse(){
        List<Course> list = new LinkedList<>();
        EasyExcel.read(PATH+"课程体系.xlsx")
                .head(Course.class)
                .sheet()
                .registerReadListener(new AnalysisEventListener<Course>() {

                    @Override
                    public void invoke(Course course, AnalysisContext analysisContext) {
                        list.add(course);
                    }

                    @Override
                    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                        System.out.println("数据读取完毕~！");
                    }
                }).doRead();
        for (Course c: list) {
            System.out.println(c);
        }
    }
}

（5）实际应用（涉及数据库，完成Excel和数据库的数据的存储和转换）

1、依赖

<!-- MySQL驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!-- MyBatis整合SpringBoot框架的起步依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

        <!--easyexcel依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>

        <!--JSON依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.66</version>
        </dependency>

        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
        </dependency>

2、实体类Course及相关注解

package com.wano.model;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class Course {
    @ExcelProperty("课程ID")
    private Integer id;
    @ExcelProperty("课程名称")
    private String name;
    @ExcelProperty("课程类型")
    private String type;
    @ExcelProperty("课程性质")
    private String properties;
    @ExcelProperty("开课学期")
    private Integer term;
    @ExcelProperty("课程学分")
    private Double credit;
    @ExcelProperty("理论学时")
    private Integer theoryHour;
    @ExcelProperty("实验学时")
    private Integer expHour;
    @ExcelProperty("总学时")
    private Integer allHour;
}

3、Mapper中批量保存数据方法
mapper

// 批量插入数据
int saveTest(List<Course> list);

mapper.xml

<insert id="saveTest">
    INSERT INTO course (id, name, type,
    properties, term, credit,
    theory_hour, exp_hour, all_hour
    )
    VALUES
    <foreach collection="list" close="" index="index" item="item" open="" separator=",">
      (#{item.id},#{item.name},#{item.type},
       #{item.properties},#{item.term},#{item.credit},
       #{item.theoryHour},#{item.expHour},#{item.allHour})
    </foreach>
  </insert>

4、test中写读取Excel数据并保存到数据库中的方法。

@SpringBootTest
public class ExcelReadTest {
    String PATH = "C:\\Users\\wano\\Desktop\\毕设资料\\技术Demo\\Excel读写\\myExcel\\src\\main\\resources\\static\\testData\\";
    @Autowired
    private CourseService courseService;
	@Test
	public void shortReadCourse(){
	    List<Course> list = new LinkedList<>();
	    EasyExcel.read(PATH+"课程体系.xlsx")
	            .head(Course.class)
	            .sheet()
	            .registerReadListener(new AnalysisEventListener<Course>() {
	
	                @Override
	                public void invoke(Course course, AnalysisContext analysisContext) {
	                    list.add(course);
	                }
	
	                @Override
	                public void doAfterAllAnalysed(AnalysisContext analysisContext) {
	                    System.out.println("数据读取完毕~！");
	                }
	            }).doRead();
	    courseService.saveTest(list);
	    /*for (Course course:list
	         ) {
	        System.out.println(course);
	    }*/
	}
}

3、写入Excel数据

pom文件
实体类，加注解
编写一个数据池，准备写入的数据
将数据写入Excel表中

（1）pom文件（完整）

<dependencies>
        <!-- MySQL驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!-- MyBatis整合SpringBoot框架的起步依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

        <!--easyexcel依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>

        <!--JSON依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.66</version>
        </dependency>

        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
        </dependency>

        <!--SpringBoot web起步依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--SpringBoot 测试起步依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <resources>
            <!--手动指定文件夹为resource,将mapper.xml读入资源文件夹中-->
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>

        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>

            <!--mybatis 代码自动生成插件-->
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.6</version>
                <configuration>
                    <!--配置文件的位置-->
                    <configurationFile>GeneratorMapper.xml</configurationFile>
                    <verbose>true</verbose>
                    <overwrite>true</overwrite>
                </configuration>
            </plugin>
        </plugins>
    </build>

（2）实体类，加注解

package com.wano.model;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class GradRequire {
    @ExcelProperty("id")
    private String id;
    @ExcelProperty("毕业要求编码")
    private String code;
    @ExcelProperty("毕业要求名称")
    private String name;
    @ExcelProperty("毕业要求内容描述")
    private String content;
}

（3）准备一个数据池，将数据写入Excel表中

package com.wano;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.wano.model.GradRequire;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.LinkedList;
import java.util.List;

@SpringBootTest
public class ExcelTest {
    String PATH = "C:\\Users\\wano\\Desktop\\毕设资料\\数据库设计\\测试数据\\";

    /**
     * 读取Excel中的数据
     */
    @Test
    public void ExcelRead(){
        List<GradRequire> list = new LinkedList<>();
//        EasyExcel.read(PATH+"测试数据.xlsx")
        EasyExcel.read(PATH+"grad_require.xlsx")
                .head(GradRequire.class)
                .sheet()
                .registerReadListener(new AnalysisEventListener<GradRequire>() {

                    @Override
                    public void invoke(GradRequire gradRequire, AnalysisContext analysisContext) {
                        list.add(gradRequire);
                    }

                    @Override
                    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                        System.out.println("数据读取完毕~！");
                    }
                }).doRead();

        //pointService.saveTest(list);
        for (GradRequire gradRequire:list) {
            System.out.println(gradRequire);
        }
    }
    //数据库 将上面Excel中读取的数据保存下来，写入另一个Excel中
    public List<GradRequire> ExcelData(){
        List<GradRequire> list = new LinkedList<>();
        EasyExcel.read(PATH+"grad_require.xlsx")
                .head(GradRequire.class)
                .sheet()
                .registerReadListener(new AnalysisEventListener<GradRequire>() {

                    @Override
                    public void invoke(GradRequire gradRequire, AnalysisContext analysisContext) {
                        list.add(gradRequire);
                    }

                    @Override
                    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                        System.out.println("数据读取完毕~！");
                    }
                }).doRead();
        return list;
    }

    /**
     * 将数据写入Excel中
     */
    @Test
    public void ExcelWrite(){
        List<GradRequire> list = ExcelData();
        EasyExcel.write(PATH+"测试数据.xlsx")
                .head(GradRequire.class)  //写入字段标题
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("毕业要求表")
                .doWrite(list);
    }
}