1、.xls和.xlsx的区别
(1)文件格式不同。xls 是一个特有的二进制格式,其核心结构是复合文档类型的结构,而 xlsx 的核心结构是 XML 类型的结构,采用的是基于 XML 的压缩方式,使其占用的空间更小。xlsx 中最后一个 x 的意义就在于此。
(2)版本不同。xls是excel及以前版本生成的文件格式,而xlsx是excel及以后版本生成的文件格式。
(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:是操作Excel以前(包括)的版本,扩展名是.xls。是poi导出excel最常用的方式;但是此种方式的局限就是导出的行数至多为65535行,超出65536条后系统就会报错。此方式因为行数不足七万行所以一般不会发生内存不足的情况(OOM)
XSSFWorkbook:是操作Excel后的版本,扩展名是.xlsx。这种形式的出现是为了突破HSSFWorkbook的65535行局限。其对应的是excel(1048576行,16384列)扩展名为“.xlsx”,最多可以导出104万行,不过这样就伴随着一个问题---OOM内存溢出,原因是你所创建的book、sheet、row和cell等此时是存在内存的并没有持久化。
SXSSFWorkbook:是操作Excel后的版本,扩展名是.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 + 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 ease.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 /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 ease.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 /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 ease.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 /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"?><project xmlns="/POM/4.0.0" xmlns:xsi="/2001/XMLSchema-instance"xsi:schemaLocation="/POM/4.0.0 /xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.3.0.RELEASE</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>ease.work</groupId><artifactId>video_download</artifactId><version>0.0.1-SNAPSHOT</version><name>video_download</name><description>Demo project for Spring Boot</description><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><!-- /artifact/org.apache.poi/poi-scratchpad --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-scratchpad</artifactId><version>4.1.2</version></dependency><!-- /artifact/org.apache.xmlbeans/xmlbeans --><dependency><groupId>org.apache.xmlbeans</groupId><artifactId>xmlbeans</artifactId><version>3.1.0</version></dependency><!-- /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></project>
6、除了使用poi中的HSSFWorkBook来读取xls格式的文件外,还可以通过jxl包来实现对xls文件的读取。可以通过手动下载jxlbao或者引入依赖的方式。直接贴代码。
package ease.work.video_download.excel;import jxl.Sheet;import jxl.Workbook;import java.io.File;import java.io.FileInputStream;/*** @author wangql* @date /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)获取此sheetSheet 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();}}}