1. Apache POI介绍

需求场景：

1. 将一些数据库信息导出为Excel表格
2. 将Excel表格数据导入数据库
3. 大量数据的导入导出操作

1.1 需求背景

常用的解决方案：

• Apache POI：Apache POI是Apache软件基金会的一个开源项目，它提供了一组Java API，用于操作Microsoft Office文件格式，包括Excel、Word和PowerPoint等文件
• EasyExcel：EasyExcel是阿里巴巴的一个开源项目，它是一个基于注解的Excel读写库，能够快速地读写Excel文件，支持读取大型Excel文件、写入数据到模板中等操作

Apache POI和EasyExcel的简单对比：

• 功能丰富程度：Apache POI提供了更加全面的功能，支持读取、写入、格式化、样式、图表、公式等等，而EasyExcel则主要提供了基本的读写和样式功能，但EasyExcel在大数据量处理方面更有优势
• 学习成本：Apache POI使用相对复杂，需要深入理解Excel文件格式和API，而EasyExcel则提供了更加简便易用的API和基于注解的方式，使得学习成本相对较低
• 性能表现：在处理大数据量时，EasyExcel的性能表现优于Apache POI

技术选项：

• 如果需要简便易用的API和处理大数据量 --> 推荐使用EasyExcel
• 如果需要对Execl更加自定义和个性化的处理和对doc进行操作 --> 推荐使用Apache POI

1.2 Apache POI基础介绍

Apache POI是基于Office Open XML标准(OOXML)和Microsoft的OLE 2复合文档格式(OLE2)处理各种文件格式的开源项目，它提供了丰富的功能，包括读取、写入、格式化、样式、图表、公式等等

官网：https://poi.apache.org/

模块：

• HSSF：提供了对Excel 97-2003文件格式（.xls）的支持，包括读取、写入、格式化、样式、公式、图表等等
• XSSF：提供了对Excel 2007及以上文件格式（.xlsx）的支持，包括读取、写入、格式化、样式、公式、图表等等
• SXSSF：提供了一种基于流的方式处理大型Excel文件，可以避免OOM（Out of Memory）错误
• SS：提供了一个通用的接口，可以在不同的Excel文件格式之间切换，包括HSSF、XSSF、SXSSF等等
• HWPF：提供读写Microsoft word DOC97格式(Microsoft word 97-(2003))档案的功能
• XWPF：提供读写Microsoft word DOC2003格式(WordprocessingML(2007+))档案的功能
• …………

POI存在的问题：

    java解析，生成Excel比较有名的框架有POI、JXL。但它们都存在一个严重的问题，就是非常耗损内存，也就是数据量比较大的时候有可能出现OOM问题，但是POI有一套SAX模式的API可以在一定程度上解决一些内存溢出问题，但是依旧没有完全的解决内存销耗过大的问题

POI操作Execl具体使用时需要了解的对象：

• Workbook(工作簿)：就是一个xls或xlsx文件
• sheet(工作表)
• Row(行)
• Cell(单元格-列)

Excel表03与07版本区别：POI支持不同版本Excel表格的操作，对应的版本有03和07+版本

1）03版本：最大行数支持 65536 后缀为.xls

2）07版本：理论上没有限制，但实际最大行数支持为1048576  后缀为.xlsx

2. POI的Execl数据写入

2.1 POI简单数据写入

导入依赖：

<!--03版本-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>

<!--07版本-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

完成通过POI写入Execl表格数据的操作步骤：

1. 创建工作簿 Workbook
2. 创建工作表 sheet
3. 创建行 Row
4. 创建列(单元格) Cell
5. 具体的数据写入

① 03版本测试写入：

//1.创建工作簿(03版本)
Workbook workbook = new HSSFWorkbook();
//2.创建工作表
Sheet sheet = workbook.createSheet("03版本测试");
//3.创建行(第一行)
Row row1 = sheet.createRow(0);

//4.创建单元格并写入数据
//(1,1)
Cell cell1 = row1.createCell(0);
cell1.setCellValue("空想家ID");
//(1,2)
Cell cell2 = row1.createCell(1);
cell2.setCellValue("空想家姓名");

//5.创建行(第二行)
Row row2 = sheet.createRow(1);
//(2,1)
Cell cell3 = row2.createCell(0);
cell3.setCellValue("155");
//(2,2)
Cell cell4 = row2.createCell(1);
cell4.setCellValue("晴天");

//6.创建输出流将workbook进行输出到本地
FileOutputStream fileOutputStream = new FileOutputStream("./03版本测试.xls");
workbook.write(fileOutputStream);
//7.关闭流
fileOutputStream.close();
System.out.println("xls文件保存成功！");

② 07版本测试写入(和03版本使用方式是一样的，唯一不同的就是调用的模块不一样，07版本使用XSSF模块)

//1.创建工作簿(03版本)
Workbook workbook = new XSSFWorkbook();
//2.创建工作表
Sheet sheet = workbook.createSheet("07版本测试");
//3.创建行(第一行)
Row row1 = sheet.createRow(0);
//4.创建单元格并写入数据
//(1,1)
Cell cell1 = row1.createCell(0);
cell1.setCellValue("商品ID");
//(1,2)
Cell cell2 = row1.createCell(1);
cell2.setCellValue("商品姓名");
//5.创建行(第二行)
Row row2 = sheet.createRow(1);
//(2,1)
Cell cell3 = row2.createCell(0);
cell3.setCellValue("1");
//(2,2)
Cell cell4 = row2.createCell(1);
cell4.setCellValue("鼠标");

//6.创建输出流将workbook进行输出到本地
FileOutputStream fileOutputStream = new FileOutputStream("./07版本测试.xlsx");
workbook.write(fileOutputStream);
//7.关闭流
fileOutputStream.close();
System.out.println("xlsx文件保存成功！");

2.2 POI批量数据写入

不管使用03版本还是07+版本进行数据写入，在实际的开发中都会涉及到批量数据操作，尤其是数据量比较多的时候，在批量数据处理上03版本和07版本处理逻辑是不同的

• 03版本HSSF：
  • 最多支撑65536行数据的写入，超出时会抛出异常
  • 操作方式为，先将所有的数据放入缓存中，最后一次性写入磁盘，写入速度快
• 07+版本XSSF：
  • 数据的写入速度比较慢，因为XSSF是获取全部行的数据，因此会销耗大量内存，数据量庞大时就可能出现内存溢出(OOM)
  • 可以写入较大的数据，比如10w+条数据

1）03版本批量数据写入65536条数据

//开始时间
long start = System.currentTimeMillis();

//1.创建工作簿
Workbook workbook = new HSSFWorkbook();

//2.创建表
Sheet sheet = workbook.createSheet("03版本数据批量写入");

//3.批量写入数据
for(int rownum =0;rownum<65536;rownum++){
    Row row = sheet.createRow(rownum);
    for(int cellnum=0;cellnum<20;cellnum++){
        Cell cell = row.createCell(cellnum);
        cell.setCellValue(cellnum+1);
    }
}

//4.写文件
FileOutputStream fileOutputStream = new FileOutputStream("./03版本数据批量写入.xls");
workbook.write(fileOutputStream);

//5.释放
fileOutputStream.close();

//结束时间
long end = System.currentTimeMillis();
System.out.println(end-start);

2）07版本批量数据写入65536条数据

//开始时间
long start = System.currentTimeMillis();

//1.创建工作簿
Workbook workbook = new XSSFWorkbook();

//2.创建表
Sheet sheet = workbook.createSheet("07版本数据批量写入");

//3.批量写入数据
for(int rownum =0;rownum<65536;rownum++){
    Row row = sheet.createRow(rownum);
    for(int cellnum=0;cellnum<20;cellnum++){
        Cell cell = row.createCell(cellnum);
        cell.setCellValue(cellnum+1);
    }
}

//4.写文件
FileOutputStream fileOutputStream = new FileOutputStream("./07版本数据批量写入.xlsx");
workbook.write(fileOutputStream);

//5.释放
fileOutputStream.close();

//结束时间
long end = System.currentTimeMillis();
System.out.println(end-start);

2.3 POI大数据量写入

 由于使用XSSF进行批量数据写入效率非常低，为了解决大数据量写入的问题，官方提供了SXSSF来解决大文件写入问题，它可以写入非常大量的数据，比如上百万条，并且写入的速度更快，占用内存更少。

SXSSF：在兼容XSSF的同时，能够应对大数据量和内存空间有限的情况。SXSSF每次获取的行数是在一个数值范围内，这个范围被称作"滑动窗口"，在这个窗口内的数据均存在于内存中，超出这个窗口大小时，数据会被写入磁盘，因此控制内存使用，先比较而言，XSSF每次都是获取全部行

注：窗口大小默认为100(可自定义)，如果数据为101行，那么此时超出了窗口限制，索引值最小的行会被"刷入"磁盘，SXSSF会自动分配临时文件，这些临时文件需要手动清除，清除的方式是使用dospose()方法

演示：写法其实和XSSF类似，只需要改一下对象和清除临时文件

//开始时间
long start = System.currentTimeMillis();

//1.创建工作簿
Workbook workbook = new SXSSFWorkbook();

//2.创建表
Sheet sheet = workbook.createSheet("大数据量写入");

//3.批量写入数据
for(int rownum =0;rownum<65536;rownum++){
    Row row = sheet.createRow(rownum);
    for(int cellnum=0;cellnum<20;cellnum++){
        Cell cell = row.createCell(cellnum);
        cell.setCellValue(cellnum+1);
    }
}

//4.写文件
FileOutputStream fileOutputStream = new FileOutputStream("./大数据量写入.xls");
workbook.write(fileOutputStream);

//5.释放
fileOutputStream.close();

//结束时间
long end = System.currentTimeMillis();
System.out.println(end-start);

耗时：2360(比XSSF快了几倍)

3. POI的Execl数据读取

3.1 POI简单读取数据

读取和写入的对象都是一样的：

• 工作簿(WorkBoot)：所有的Excel工作簿的操作通过此类型都可以来进行设置
  • 03 HSSF
  • 07 XSSF
• 数据表(Sheet)：所有关于数据表的操作通过Sheet都可以进行设置
• 行(Row)
• 列(Cell)

读取步骤：利用文件流进行读取

• 获取工作簿
• 获取表
• 获取行
• 获取单元格
• 获取数据

workbook API：

Sheet API：

Cell API：

演示：

//1.获取Excel文件流
File file = new File("G:\\Java-Dome\\apachePoi-dome\\bk.xlsx");
FileInputStream fileInputStream = new FileInputStream(file);

//2.获取工作簿
Workbook workbook = new XSSFWorkbook(fileInputStream);

//3.获取表(通过下标进行获取也可以通过表名来获取)
Sheet sheet = workbook.getSheetAt(0);

//4.获取行(通过下标进行获取第一行)
Row row = sheet.getRow(1);

//5.获取单元格(通过下标获取低一个单元格)
Cell cell = row.getCell(0);

//6.读取数据(需要注意不同数据类型)
String stringCellValue = cell.getStringCellValue();
System.out.println(stringCellValue);

//7.关闭源
fileInputStream.close();

3.2 POI批量读取数据

批量的Excel数据读取就是一次性获取所有的Excel数据，在操作层面主要分两步：

1. 读取第一行的Excel标题内容
2. 读取数据内容

Excel操作表：

演示批量读取：

//1.获取Excel文件流
File file = new File("G:\\Java-Dome\\apachePoi-dome\\bk.xlsx");
FileInputStream fileInputStream = new FileInputStream(file);

//2.获取工作簿
Workbook workbook = new XSSFWorkbook(fileInputStream);

//3.获取表
Sheet sheet = workbook.getSheetAt(0);

//4.获取标题内容(获取表中的第一行的表头数据)
Row title = sheet.getRow(0);
//非空判断
if(title!=null){
    //获取标题的单元格，用于遍历获取所有单元格
    int cellNum = title.getPhysicalNumberOfCells();
    //遍历获取标题内容
    for (int i=0;i<cellNum;i++){
        //获取单元格
        Cell cell = title.getCell(i);
        //单元格非空
        if(cell!=null){
            String stringCellValue = cell.getStringCellValue();
            System.out.print(stringCellValue+"\t");
        }
    }
}
//打印换行
System.out.println();

/*5.获取表数据
获取所有表中的所有行数*/
int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
//循环遍历行
for(int i=1;i<physicalNumberOfRows;i++){
    //获取行对象
    Row rowData = sheet.getRow(i);
    //获取行中的所有表格数据数
    int physicalNumberOfCells = rowData.getPhysicalNumberOfCells();
    //循环遍历表格数据
    for(int a=0;a<physicalNumberOfCells;a++){
        //获取表格对象
        Cell cell = rowData.getCell(a);
        //获取表格内的数据类型
        CellType cellType = cell.getCellType();
        //类型判断
        switch (cellType){
            case STRING://字符类型
                //获取字符串值
                String stringCellValue = cell.getStringCellValue();
                System.out.print(stringCellValue+"\t");
                break;
            case NUMERIC://数值类型
                //判断是否为日期类型
                if(DateUtil.isCellDateFormatted(cell)){
                    //获取日期类型值
                    Date dateCellValue = cell.getDateCellValue();
                    String formatDate = new SimpleDateFormat("yyyy-MM-dd").format(dateCellValue);
                    System.out.print(formatDate+"\t");
                }else {
                    //获取数字类型值
                    double numericCellValue = cell.getNumericCellValue();
                    System.out.print(numericCellValue+"\t");
                }
                break;
        }
    }
    //数据换行
    System.out.println();
}

3.3 POI封装读取数据

在使用POI读取Excel文件时，一般都会解析封装到具体的实体类中，通过实体类操作

使用自定义封装读取数据时涉及到反射，通过反射获取类中的属性并进行类型判断和赋值操作，最终将数据注入实体类然后添加到list

Excel操作表：

演示：读取数据进行封装

① Entity实体类

public class ExamEntity {

    //考试时间
    Date examTime;

    //教室名称
    String classRoomName;

    //课程名称
    String courseName;

  //get/set方法省略

    @Override
    public String toString() {
        return "ExamEntity{" +
                "examTime=" + examTime +
                ", classRoomName='" + classRoomName + '\'' +
                ", courseName='" + courseName + '\'' +
                '}';
    }
}

public class excelUtil {

    //获取表头数据
    public <T> List<T> getExcelHead(FileInputStream fileInputStream, Class<ExamEntity> tClass) throws IOException {
        //存储实体类数据集合
        ArrayList<T> result = new ArrayList<>();
        //获取Workbook
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        //通过下标获取第一张表
        Sheet sheetAt = workbook.getSheetAt(0);
        //获取第一行
        Row row = sheetAt.getRow(1);
        //创建list用于存储表头数据
        ArrayList<String> arrayList = new ArrayList<>();
        //遍历row中的头数据
        for(Cell cell:row){
            //cell不为空放入集合中
            arrayList.add(cell.getStringCellValue());
        }

        //获取具体数据
        for(int a=2;a<sheetAt.getPhysicalNumberOfRows();a++){
            //获取具体数据行
            Row row1 = sheetAt.getRow(a);
            //row行不为空
            if(row1!=null){
                //标记
                int j =0;
                //存储数据map
                HashMap<String,String> map = new HashMap<>();
                //遍历单元格数据
                for(Cell cell:row1){
                    //获取单元格数据
                    map.put(arrayList.get(j),getExcelData(cell));
                    j++;
                }
                //将map转换成为实体类
                T t = (T) mapToEntity(map, tClass);
                result.add(t);
            }
        }
        return result;
    }
    //cell数值类型格式化对象
    public static NumberFormat nf = NumberFormat.getNumberInstance();

    static {
        //去掉小数点
        nf.setGroupingUsed(false);
    }

    //获取具体数据
    public String getExcelData(Cell cell) throws IOException {
        //获取表格内的数据类型
        CellType cellType = cell.getCellType();
        //值
        String value = "";
        //类型判断
        switch (cellType){
            case STRING://字符类型
                //获取字符串值
                 value = cell.getStringCellValue();
                break;
            case NUMERIC://数值类型
                value = nf.format(cell.getNumericCellValue());
                break; }
         return value;
    }

    //map转换成实体类
    private <T> T mapToEntity(Map<String,String> map, Class<T> entity){
        T t = null;
        try{
            t = entity.newInstance();
            //遍历获取类中的属性
            for(Field field:entity.getDeclaredFields()){
                if(map.containsKey(field.getName())){
                    //属性是否可读写
                    boolean accessible = field.isAccessible();
                    //设置可读
                    field.setAccessible(true);
                    //获取Map中的属性对应的值
                    String str = map.get(field.getName());
                    //获取实体类属性的类型
                    String type = field.getGenericType().toString();
                    //不同类型的判断赋值
                    if(str!=null){
                        if(type.equals("class java.lang.String")){
                            field.set(t,str);
                        }else if(type.equals("class java.lang.Double")){
                            field.set(t,Double.parseDouble(String.valueOf(str)));
                        }else if(type.equals("class java.util.Date")){
                            Date date = new SimpleDateFormat("yyyy年MM月dd日 HH:mm-HH:mm").parse(str);
                            field.set(t,date);
                        }
                    }
                    field.setAccessible(!accessible);
                }
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return t;
    }
}

③ main测试类

public static void main(String[] args) throws IOException {
    FileInputStream fileInputStream = new FileInputStream(new File("G:\\Java-Dome\\apachePoi-dome\\bk.xlsx"));
    excelUtil excelUtil = new excelUtil();
    List<ExamEntity> excelHead = excelUtil.getExcelHead(fileInputStream, ExamEntity.class);
    System.out.println(excelHead.toString());
}

结果：