使用poi把excel分割为多个excel

前言

这次还是在折腾poi，上次文章内容是从两个数据库获取数据写入到一个excel里；但是由此也想到，应该怎么把一个excel分割为多个excel？
查资料很少有相关的实现，就算有，也没有特意进行说明，怎么做，为什么这么做；
以及很傻瓜式的，根据行数来进行分割，并没有针对根据不同的数据类型进行划分和分割。
而本次，就这个功能的实现进行详细的叙述。

项目描述

本次通过创建一个maven项目，进行实现分割excel；在最后面会附上本人开发的项目的gitee地址

最终完成的截图

完整excel截图

拆分后的excel截图

功能实现以及思路

1. 以学生成绩为例，创建不同的成绩类型的表单，如语文成绩表，数学成绩表等

有字段：
	id 序号
	studentNumber 学号
	name 学生姓名
	type 成绩的类型（语文数学英语等）
	score 分数
	age 年龄
	sex 性别S

2. 在数据库写入不同学生的成绩
3. 在代码实现数据库连接（为演示方便，只使用单数据库）
4. 实现获取数据库数据

在这里通过分割不同的学生的成绩到不同的excel，所以以学号作为判别的条件
同时，为防止出现文件还未创建就获取文件的报错等因素，
所以先获取所有数据到一个集合进行处理

5. 实现获取所有的学号
6. 实现获取所有的成绩
7. 实现把获取的所有的成绩写入到一个excel表里

本项目，是通过创建不同的sheet，分割sheet为单个独立的excel，
同时根据学号，把sheet名用学生学号命名，插入数据的时候，
根据sheet名插入对应学生的成绩

8. 根据学号，循环遍历生成学生的学号个数量的sheet数（即几个学生，生成几个sheet）
9. 循环遍历生成表头
10. 根据sheet名插入学生成绩数据到不同的sheet

最后到重头的地方了，如何进行分割sheet
我用的方式为再次创建一个一模一样的完整的excel；
遍历两个excel，判断前面一个excel和后面的excel的sheet名是否相等，
如果不相等则进行删除不同的sheet

11. 遍历两个相同的完整的excel，判断sheet是否相等，相等保留，不相等移除（具体实现看后面）
12. 生成不同的单sheet的excel

数据库表截图

在这里贴出来，创建的、将要使用的数据库表

 学生信息表 表名：studentinfomation

语文成绩表 表名：chineseScore

英语成绩表 表名：englishScore

数学成绩表 表名：mathScore

这里通过全连接，将学生信息和学生成绩表进行串联

项目结构图

创建学生实体类

在dto文件夹下创建文件：studentInfo.java

实体类代码（引入了lombok依赖）

package dto;

import lombok.Data;

/**
 * @author hxc
 * @dateTime: 2021/11/18
 * @description: 实体类
 *
 * */
@Data
public class studentInfo {
    //学生姓名
    private String name;
    //学生年龄
    private String age;
    //学生性别
    private String sex;
    //学生学号
    private String schoolNumber;
    //考试的类别
    private String type;
    //分数
    private String score;
}

实现数据库连接

关于数据库的配置说明请看我的数据库连接的文章

编写数据库连接配置

在resource下创建一个mysql-config.xml文件
代码如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/mydb"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>

    </environments>
    <mappers>
        <mapper resource="mysqlMapper/mysql.xml"/>
    </mappers>
</configuration>

---

数据库工具栏

这个工具类为获取到数据库配置文件用的

package utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;

/**
 * @author hxc
 * @Description: 获取数据库配置文件工具
 *
 * */
public class MapperUtil {
    public static final SqlSessionFactory sqlSessionFactory_mysql=getSqlSessionFactory("mysql-config.xml");

    private static SqlSessionFactory getSqlSessionFactory(String configFile){
        SqlSessionFactory sqlSessionFactory=null;
        try{
            sqlSessionFactory=new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream(configFile));
        }catch(IOException e){
            e.printStackTrace();
        }
        return sqlSessionFactory;
    }
}

实现数据库查询

这里运用全连接:inner join 判断当学生信息表的学生学号和成绩表的学生学号相等时进行获取数据

在resource/mysqlMapper文件创建mysql.xml进行编写查询数据库代码

数据库查询语句代码

<?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">
<mapper namespace="mapper.mysqlMapper">

<!--    从学生信息表获取学生信息-->
    <select id="findStudentInfo" resultType="dto.studentInfo">
        select shoolNumber,name,age,sex from studentinformation
    </select>

<!--    语文-->
    <select id="findChinese" resultType="dto.studentInfo">
        select c.schoolNumber,type,score from chinesescore c
        inner join studentinformation s on c.schoolNumber = s.schoolNumber
    </select>
<!--    英语-->
    <select id="findEnglish" resultType="dto.studentInfo">
        select e.schoolNumber,type,score from englishscore e
        inner join studentinformation s on e.schoolNumber =s.schoolNumber
    </select>
<!--    数学-->
    <select id="findMath" resultType="dto.studentInfo">
        select m.schoolNumber,type,score from mathscore m
        inner join studentinformation s on m.schoolNumber=s.schoolNumber
    </select>

</mapper>

实现获取学号和成绩集合

获取学号集合

在api文件夹下创建
先进行获取学号，以便后续生成excel数据进行调用

在api文件夹下创建文件：getScoolNumber.java文件
代码如下：

package api;

import dto.studentInfo;
import mapper.mysqlMapper;
import java.util.List;

/**
 * @author hxc
 * @description 获取学生学号
 * */

public class getSchoolNumber {

    //编写一个方法，获取学生学号
    public static List<String> getNumber(mysqlMapper mapper){
        //创建一个集合接收获取的学号
        List<String> schoolNumber=null;

        //获取学生信息查询
        List<studentInfo> findStudentInfo=mapper.findStudentInfo();

        //遍历学生信息，把学生学号加入到创建的集合
        for(studentInfo studentInfo:findStudentInfo){
            schoolNumber.add(studentInfo.getSchoolNumber());
        }
        //返回值为加入数据后的集合
        return schoolNumber;
    }
}

获取所有的成绩

这里采取将所有成绩放入一个集合的方式，将该集合遍历放到后续的excel进程插入数据
在api下创建getData.java文件
代码如下：

package api;

import dto.studentInfo;
import mapper.mysqlMapper;

import java.util.ArrayList;
import java.util.List;

/**
 * @author hxc
 * @dateTime: 2021/11/18
 * @description: 用于从数据库获取数据，把所有的查询到的数据加入到一个list
 *
 * */

public class getData {
    public static List<studentInfo> getScore(mysqlMapper mapper){
        //创建一个集合，接收所有的成绩
        List<studentInfo> getScore= new ArrayList<studentInfo>();

        //获取语文成绩数据
        List<studentInfo> findChinese=mapper.findChinese();
        //遍历加入集合
        for(studentInfo c:findChinese){
            getScore.add(c);
        }

        //获取数学数据
        List<studentInfo> findMath=mapper.findMath();
        for(studentInfo m:findMath){
            getScore.add(m);
        }

        //获取英语数据
        List<studentInfo> findEnglish=mapper.findEnglish();
        for(studentInfo e:findEnglish){
            getScore.add(e);
        }

        //可控制台输出查看所有数据
        System.out.println(getScore);
        //返回值为添加数据后的集合
        return getScore;
    }
}

实现创建并分割excel

以下为创建一个全部数据的完整excel表，然后进行拆分为独立的excel的代码

package api;

import dto.studentInfo;
import mapper.mysqlMapper;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author hxc
 * @dateTime: 2021/11/18
 * @description: 用于通过分割完整的excel的sheet，把一个个sheet生成一个个独立的excel
 *
 * */
public class spliteExcel {
    //编写一个方法，以便在启动进行调用
    public static void spliteExcel(mysqlMapper mapper)throws IOException {
        //调用获取的所有学生学号
        List<String> schoolNumber=getSchoolNumber.getNumber(mapper);

        //调用获取的所有成绩数据
        List<studentInfo> ScoreData=getData.getScore(mapper);

        //获取所有学生信息
        List<studentInfo> personal=mapper.findStudentInfo();

        //定义一个本地路径，用于存放创建的excel
        String excelPath="D:\\fileUpload\\"+"全部学生成绩excel.xlsx";

        //new 一个Workbook（创建excel）
        Workbook workbook=new XSSFWorkbook();

        //定义标题行
        String[] title={"序号","学号","姓名","年龄","性别","类型","成绩"};

        //样式设置
        CellStyle style=workbook.createCellStyle();

        //excel row和cell代表横纵
        //初始化Cell
        Cell cell=null;

        //初始化sheet名称
        String sheetName=null;

        //遍历生成多个sheet，sheet名以学生学号命名
        //集合长度用size获取，数组用length获取
        for(int i=0;i<schoolNumber.size();i++){
            sheetName=schoolNumber.get(i);
            //创建sheet个数，根据获取到的学生学号个数进行创建，即几个学生创建几个sheet
            workbook.createSheet();
            //给每个sheet设置sheet名称
            workbook.setSheetName(i,sheetName);
            //给每个sheet创建首行，即标题行
            workbook.getSheetAt(i).createRow(0);
        }

        //把写好的标题写入每个sheet文件
        //遍历sheet个数
        for(int i=0;i<schoolNumber.size();i++){
            //遍历标题个数
            for(int j=0;j<title.length;j++){
                //根据标题的个数，创建不同的Cell
                //获取第i个sheet的标题行，并创建第j个cell格子
                cell=workbook.getSheetAt(i).getRow(0).createCell(j);
                //给格子设置值和样式
                cell.setCellValue(title[j]);
                cell.setCellStyle(style);
                //单元格自适应
                workbook.getSheetAt(i).autoSizeColumn(i,true);
            }
        }

        /**
         * 往不同的sheet插入数据
         * 根据sheet名，在每个sheet插入对应的学生的成绩
         * */

        //定义sheet总数进行循环
        int SheetNum=workbook.getNumberOfSheets();
        //初始化row
        Row row=null;
        //遍历sheet总数
        for(int i=0;i<SheetNum;i++){
            //判断sheet名称和学生学号是否相同
            if(workbook.getSheetName(i).equals(schoolNumber.get(i))){
                //定义一个集合，装这个学生的数据
                //这一步主要目的是把一个学生的所有数据的弄到一个集合进行添加到sheet里
                List<studentInfo> studentInfos=new ArrayList<studentInfo>();

                //遍历所有学生的成绩数据
                for(studentInfo data:ScoreData){
                    //遍历学生学号数据
                    for (int j=0;j<schoolNumber.size();j++){
                        //判断所有学生成绩数据里的学生学号是否和获取到的学生学号集合相等
                        if(data.getSchoolNumber().equals(schoolNumber.get(i))){
                            //相等则把数据添加到新创的集合
                            studentInfos.add(data);
                            //当查询到sheet名称和学生学号相等时继续
                            //否则跳出循环，不然会所有人的数据都加入集合，相当于遍历白做
                            if(schoolNumber.get(i).equals(schoolNumber.get(j))){
                                continue;
                            }else{
                                break;
                            }
                        }
                    }


                }
                //获取到这个学生的所有成绩后，把数据插入到对应的数据
                //遍历该学生的集合
                for(int g=0;g<studentInfos.size();g++){
                    //创建行
                    row=workbook.getSheetAt(i).createRow(g+1);
                    //设置序号自增
                    row.createCell(0).setCellValue(g+1);
                    //这个集合有数据的是学号、类型和分数
                    //插入学号
                    row.createCell(1).setCellValue(studentInfos.get(g).getSchoolNumber());
                    //插入类型
                    row.createCell(5).setCellValue(studentInfos.get(g).getType());
                    //插入分数
                    row.createCell(6).setCellValue(studentInfos.get(g).getScore());
                    //根据学号，去插入获取到的学生个人信息
                    //遍历学生个人信息集合
                    for(int m=0;m<personal.size();m++){
                        //判断个人信息里的学号和插入的学生学号是否相等，相等就插入姓名和年龄等
                        if(row.getCell(1).getStringCellValue().equals(personal.get(m).getSchoolNumber())){
                            //插入姓名
                            row.createCell(2).setCellValue(personal.get(m).getName());
                            //插入年龄
                            row.createCell(3).setCellValue(personal.get(m).getAge());
                            //插入性别
                            row.createCell(4).setCellValue(personal.get(m).getSex());
                        }
                        //根据标题的文字设置单元格大小
                        for(int h=0;h<title.length;h++){
                            workbook.getSheetAt(i).setColumnWidth(h,workbook.getSheetAt(i).getRow(0).getCell(0).getStringCellValue().getBytes().length*1000+100);
                        }
                    }
                }
                //最后要清空集合的数据，防止前一个人的成绩加入到下一个sheet
                System.out.println(studentInfos);
                studentInfos.clear();
            }
        }

        //创建本地excel文件
        //创建文件，路径为excelPath
        File file =new File(excelPath);
        //文件如果不存在，则创建父级文件夹，不然会整个文件都创建为文件夹
        if(!file.exists()){
            file.getParentFile().mkdirs();
        }
        //创建xlsx文件,这里要抛出异常
        file.createNewFile();
        //以流的形式输出文件
        FileOutputStream fileOutputStream=new FileOutputStream(file);
        //设置创建excel的路径和文件名
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        /**
         * 这里开始正式的拆分excel
         * 把sheet拆分为一个个的独立excel
         *
         * */
        //查找完整的excel文件
        XSSFWorkbook xssfWorkbook=new XSSFWorkbook(new FileInputStream(new File(excelPath)));
        //定义遍历完整的excel的sheet数
        int total=xssfWorkbook.getNumberOfSheets();
        //遍历sheet
        for(int i=0;i<total;i++){
            //查找创建的完整excel并加入创建文件
            File sheetFile=new File(excelPath);
            //先创建相同的excel文件
            XSSFWorkbook sheetWorkbook=new XSSFWorkbook(new FileInputStream(sheetFile));
            //定义一个total2，为新创建的excel的sheet总数
            int total2=sheetWorkbook.getNumberOfSheets();
            //遍历
            for(int j=total2-1;j>=0;j--){
                //当原本的多sheet文件和现在新创建的sheet位置是相等的时候，继续执行
                if(i==j){
                    continue;
                }
                //否则删除其他sheet
                sheetWorkbook.removeSheetAt(j);

            }
            //以下写在遍历生成excel的循环里
            //定义拆分后的shhet路径和名称
            String spliteExcelPath="D:\\fileUpload\\"+xssfWorkbook.getSheetName(i)+".xlsx";
            //写了以下的判断文件代码，可以放在任意一个位置的路径
            File spliteFile=new File(spliteExcelPath);
            if(!spliteFile.exists()){
                spliteFile.getParentFile().mkdirs();
            }
            spliteFile.createNewFile();
            FileOutputStream sheetCreate=new FileOutputStream(spliteFile);
            sheetWorkbook.write(sheetCreate);
            sheetCreate.close();
        }

        System.out.println("拆分excel成功！");
    }

}

编写启动类

通过启动类启动项目

import api.spliteExcel;
import mapper.mysqlMapper;
import org.apache.ibatis.session.SqlSession;
import utils.MapperUtil;

public class App {

    /**
     * @author: hxc
     * @dateTime: 2021/11/18
     * @description: 启动类
     *
     * */
//调用数据库工具类并实例化创建excel方法
    private static SqlSession session;
    private static mysqlMapper mapper;
    private static spliteExcel splite;

    private static void close(){
        if(session !=null){
            session.close();
        }
    }

    public static void main(String[] args) {
        session= MapperUtil.sqlSessionFactory_mysql.openSession();
        mapper=session.getMapper(mysqlMapper.class);

        splite=new spliteExcel();
        try{
            spliteExcel.spliteExcel(mapper);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

总结

以上，将excel分割为多个excel的功能就实现了。

gitee项目地址： https://gitee.com/spring-in-huangxian-county/splite-excel.git.