导入导出记录

1、.xls和.xlsx的区别

（1）文件格式不同。xls 是一个特有的二进制格式，其核心结构是复合文档类型的结构，而 xlsx 的核心结构是 XML 类型的结构，采用的是基于 XML 的压缩方式，使其占用的空间更小。xlsx 中最后一个 x 的意义就在于此。

（2）版本不同。xls是excel2003及以前版本生成的文件格式，而xlsx是excel2007及以后版本生成的文件格式。

（3）兼容性不同。xlsx格式是向下兼容的，可兼容xls格式。

（4）读取方式有两种：使用导入jxl包的方式或者使用java中的poi（两种方式区别：导入jxl包的形式，只能处理xls格式；使用java中的poi的话，可以使用poi中的HSSFWorkBook来处理xls文件，使用XSSFWrokBook或者SXSSFWorkBook来处理xlsx格式的文件）

2、记录一下HSSFWorkBook，XSSFWrokBook和SXSSFWorkBook的区别

  用Java中的poi导出Excel时，我们需要考虑到Excel版本及数据量的问题。针对不同的Excel版本，要采用不同的工具类，如果使用错了，会出现错误信息。JavaPOI导出Excel有三种形式，分别是HSSFWorkbook、XSSFWorkbook和SXSSFWorkbook。

  HSSFWorkbook：是操作Excel2003以前（包括2003）的版本，扩展名是.xls。是poi导出excel最常用的方式；但是此种方式的局限就是导出的行数至多为65535行，超出65536条后系统就会报错。此方式因为行数不足七万行所以一般不会发生内存不足的情况（OOM）

  XSSFWorkbook：是操作Excel2007后的版本，扩展名是.xlsx。这种形式的出现是为了突破HSSFWorkbook的65535行局限。其对应的是excel2007(1048576行，16384列)扩展名为“.xlsx”，最多可以导出104万行，不过这样就伴随着一个问题---OOM内存溢出，原因是你所创建的book、sheet、row和cell等此时是存在内存的并没有持久化。

  SXSSFWorkbook：是操作Excel2007后的版本，扩展名是.xlsx。从POI 3.8版本开始，提供了一种基于XSSF的低内存占用的SXSSF方式。对于大型excel文件的创建，一个关键问题就是，要确保不会内存溢出。其实，就算生成很小的excel（比如几Mb），它用掉的内存是远大于excel文件实际的size的。如果单元格还有各种格式（比如，加粗，背景标红之类的），那它占用的内存就更多了。对于大型excel的创建且不会内存溢出的，就只有SXSSFWorkbook了。它的原理很简单，用硬盘空间换内存（就像hash map用空间换时间一样）。SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看，在此之前的excel rows都会被写入到硬盘里（Windows电脑的话，是写入到C盘根目录下的temp文件夹）。被写入到硬盘里的rows是不可见的/不可访问的。只有还保存在内存里的才可以被访问到。

3、具体代码实现

（1）使用HSSFWorkBook来读取excel文件（.xls格式）

几点注意：

a、使用HSSFWorkBook不可读取xlsx格式，否则报错如下：

org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)

b、getFirstRowNum获取第一行的行号，getLastRowNum获取最后一行的行号；

  Row row = sheet.getRow(i)获取第i行

  row.getFirstCellNum()获取row行的第一列列号， row.getLastCellNum()获取row行的最后一列的列号

  row.getCell(j).getNumericCellValue()读取数值类型的值， row.getCell(j).getStringCellValue()读取字符串类型值

c、在遍历行或者列的时候，注意最后一行或者一列是读取不到的，不要用等于号，否则空指针异常。i < sheet.getLastRowNum()而不是i < =sheet.getLastRowNum()。

package com.netease.work.video_download.excel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import java.io.File;
import java.io.FileInputStream;

/**

• @author wangql

• @date 2020/6/13 7:47

• @描述
  */
  public class HSSFWorkBookTest {
  public static void main(String[] args) {
  File file = new File(“B:\seckill\test.xls”);
  try {
  FileInputStream fis = new FileInputStream(file.getAbsolutePath());
  HSSFWorkbook hwb = new HSSFWorkbook(fis);
  // int rows = hwb.getNumberOfSheets();
  Sheet sheet = hwb.getSheetAt(0);
  // 循环遍历每一行
  for(int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++){
  // 循环遍历每一列
  Row row = sheet.getRow(i);
  for(int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++){
  if(row.getCell(j).getCellType() == CellType.NUMERIC){
  System.out.print((int)row.getCell(j).getNumericCellValue()+" “);
  }else if(row.getCell(j).getCellType() == CellType.STRING){
  System.out.print(row.getCell(j).getStringCellValue()+” ");
  }

           }
           System.out.println();
       }
  
   } catch (Exception e) {
       e.printStackTrace();
   }
  

  }
  }

（2）使用XSSFWorkBook来读取excel文件（.xlsx格式）

几点注意：

a、使用XSSFWorkBook不可读取xls格式，否则报错如下：

org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException: The supplied data appears to be in the OLE2 Format. You are calling the part of POI that deals with OOXML (Office Open XML) Documents. You need to call a different part of POI to process this data (eg HSSF instead of XSSF)

b、getFirstRowNum获取第一行的行号，getLastRowNum获取最后一行的行号；

  Row row = sheet.getRow(i)获取第i行

  row.getFirstCellNum()获取row行的第一列列号， row.getLastCellNum()获取row行的最后一列的列号

  row.getCell(j).getNumericCellValue()读取数值类型的值， row.getCell(j).getStringCellValue()读取字符串类型值

c、在遍历行或者列的时候，注意最后一行或者一列是读取不到的，不要用等于号，否则空指针异常。i < sheet.getLastRowNum()而不是i < =sheet.getLastRowNum()。

package com.netease.work.video_download.excel;

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;

/**

• @author wangql

• @date 2020/6/13 7:48

• @描述
  */
  public class XSSFWorkBookTest {
  public static void main(String[] args) {
  File file = new File(“B:\seckill\test.xlsx”);
  try {
  FileInputStream fis = new FileInputStream(file.getAbsolutePath());
  XSSFWorkbook swb = new XSSFWorkbook(fis);
  Sheet sheet = swb.getSheetAt(0);
  for(int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++){
  Row row = sheet.getRow(i);
  for(int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++){
  if(row.getCell(j).getCellType() == CellType.NUMERIC){
  System.out.print((int)row.getCell(j).getNumericCellValue()+" “);
  }else if(row.getCell(j).getCellType() == CellType.STRING){
  System.out.print(row.getCell(j).getStringCellValue()+” ");
  }

           }
           System.out.println();
       }
  
   } catch (Exception e) {
       e.printStackTrace();
   }
  

  }
  }

（3）使用SXSSFWorkBook来读取excel文件（.xlsx格式）

几点注意：

a、使用SXSSFWorkBook不可读取xls格式，否则报错如下：

org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException: The supplied data appears to be in the OLE2 Format. You are calling the part of POI that deals with OOXML (Office Open XML) Documents. You need to call a different part of POI to process this data (eg HSSF instead of XSSF)

b、getFirstRowNum获取第一行的行号，getLastRowNum获取最后一行的行号；

  Row row = sheet.getRow(i)获取第i行

  row.getFirstCellNum()获取row行的第一列列号， row.getLastCellNum()获取row行的最后一列的列号

  row.getCell(j).getNumericCellValue()读取数值类型的值， row.getCell(j).getStringCellValue()读取字符串类型值

c、在遍历行或者列的时候，注意最后一行或者一列是读取不到的，不要用等于号，否则空指针异常。i < sheet.getLastRowNum()而不是i < =sheet.getLastRowNum()。

package com.netease.work.video_download.excel;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;

/**

• @author wangql

• @date 2020/6/13 7:48

• @描述
  */
  public class SXSSFWorkBookTest {
  public static void main(String[] args) {
  File file = new File(“B:\seckill\test.xlsx”);
  try {
  FileInputStream fis = new FileInputStream(file.getAbsolutePath());
  XSSFWorkbook swb = new XSSFWorkbook(fis);
  Sheet sheet = swb.getSheetAt(0);
  for(int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++){
  Row row = sheet.getRow(i);
  for(int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++){
  if(row.getCell(j).getCellType() == CellType.NUMERIC){
  System.out.print((int)row.getCell(j).getNumericCellValue()+" “);
  }else if(row.getCell(j).getCellType() == CellType.STRING){
  System.out.print(row.getCell(j).getStringCellValue()+” ");
  }

           }
           System.out.println();
       }
  
   } catch (Exception e) {
       e.printStackTrace();
   }
  

  }
  }

4、总结

（1）HSSFWorkBook，只能读取xls格式文件；XSSFWorkBook和SXSSFWorkBook只能读取xlsx格式的文件，根据自己的 文件格式选择使用不同的类型

（2）HSSFWorkBook处理excel格式的文件行数最大65535行，XSSFWorkBook能够处理最大1048576行，16384列的文件

（3）方法总结：

     a、一个excel文件中可以有多个sheet，获取所有sheet的过程，先使用getNumberOfSheets()获取所有sheet的数量，然后使用getSheetAt(index)的形式，类似于数组获取值的方式来获取每个sheet

     b、getFirstRowNum、getLastRowNum、getRow(i)、row.getFirstCellNum()、row.getLastCellNum()、getCellType()、getNumericCellValue、getStringCellValue等，见上述代码。注意获取单元格数据时候最好进行格式判断，根据不同的格式，选择使用不同的获取数据的格式，如果是CellType.NUMERIC类型，则使用getNumericCellValue获取数据；如果是CellType.STRING类型，则使用getStringCellValue读取数据。CellType.NUMERIC和CellType.STRING是poi依赖包中自定义的枚举类，可以自己查看。判断过程如下：

if(row.getCell(j).getCellType() == CellType.NUMERIC){
System.out.print((int)row.getCell(j).getNumericCellValue()+" “);
}else if(row.getCell(j).getCellType() == CellType.STRING){
System.out.print(row.getCell(j).getStringCellValue()+” ");
}

5、上述代码写在一个maven项目总，pom文件如下，几个核心依赖红色标出。

<?xml version="1.0" encoding="UTF-8"?>

4.0.0

org.springframework.boot
spring-boot-starter-parent
2.3.0.RELEASE


com.netease.work
video_download
0.0.1-SNAPSHOT
video_download
Demo project for Spring Boot

<properties>
    <java.version>1.8</java.version>
</properties>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
        <exclusions>
            <exclusion>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.1.2</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.1.2</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-scratchpad</artifactId>
        <version>4.1.2</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->
    <dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
        <version>3.1.0</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>4.1.2</version>
    </dependency>
    <dependency>
        <groupId>com.google.guava</groupId>
        <artifactId>guava</artifactId>
        <version>29.0-jre</version>
    </dependency>
</dependencies>
<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
    </plugins>
</build>

6、除了使用poi中的HSSFWorkBook来读取xls格式的文件外，还可以通过jxl包来实现对xls文件的读取。可以通过手动下载jxlbao或者引入依赖的方式。直接贴代码。

package com.netease.work.video_download.excel;

import jxl.Sheet;
import jxl.Workbook;
import java.io.File;
import java.io.FileInputStream;

/**

• @author wangql

• @date 2020/6/13 7:47

• @描述
  */
  public class WorkBookTest {
  public static void main(String[] args) {
  File file = new File(“B:\seckill\test.xlsx”);
  try {
  FileInputStream fis = new FileInputStream(file.getAbsolutePath());
  Workbook workBook = Workbook.getWorkbook(fis);
  // getNumberOfSheets是获取一个excel文件中的sheet数量，有多个sheet时可以通过循环遍历读取
  // int sheetNums = workBook.getNumberOfSheets();
  // getSheet通过下标读取指定的sheet，如果只有一个，则直接getSheet(0)获取此sheet
  Sheet sheet = workBook.getSheet(0);
  // 循环遍历读取此sheet下的各行数据
  for(int i = 0; i < sheet.getRows(); i++){
  // 循环读取一行数据中的各列数据
  for(int j = 0; j < sheet.getColumns(); j++){
  // 读取单元格i行j列的数据,注意getCell参数中，第一个是列，第二个是行
  System.out.print(sheet.getCell(j,i).getContents()+" ");
  }
  System.out.println();
  }
  } catch (Exception e) {
  e.printStackTrace();
  }

  }
  }