基本功能
HSSF - 提供读写MicrosofExcel式档案的功能: 03版 有限制 65535
XSSF - 提供读写MicrosoftExcel OOXML式档案的功能 07版 没限制
Word各式档案的功能。HWVPF - 提供读写Microsofi
HSLF - 强供读写MicrosofPowerPoint?式档案的功能
HDGF - 提供读写Microsoft Visio!式档案的功能,
缺点:消耗内存
EasyExcel和Poi对比:
EasyExcel 是阿里巴巴开源的一个excel处理框架,以使用简单、节省内存者称.EasyExcel 能大大减少占用内存的主要原因是在解析 Excel 时没有将文件数据一次性全部加到内存中,而是从磁盘上一行行读取数据,逐个解析。
内存问题 : POl= 100w先加载到内存 0OM。。在写入文件 es= 1
下图是 EasyExcel和 POI 在解析Excel时的对比图
一 、POI写入excel
导入依赖:
<!--xls(03)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<!--xlsx(07)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<!--日期格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.5</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
</dependency>
03版
@Test
public void ExcelTest03() throws Exception {
//1.创建一个工作簿
Workbook workbook = new HSSFWorkbook();
//2.创建一个工作表
Sheet sheet = workbook.createSheet("入库统计表");
//3.创建一个行 (1,1)
Row row = sheet.createRow(0);
//4.创建一个单元格
Cell cell0 = row.createCell(0);
cell0.setCellValue("货名");
Cell cell01 = row.createCell(1);
cell01.setCellValue("数量");
Cell cell02 = row.createCell(2);
cell02.setCellValue("入库时间");
//3.第二行 (2,1)
Row row1 = sheet.createRow(1);
//4.创建一个单元格
Cell cell1 = row1.createCell(0);
cell1.setCellValue("旺仔");
Cell cell2 = row1.createCell(1);
cell2.setCellValue(666);
Cell cell3 = row1.createCell(2);
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell3.setCellValue(time);
//第三行(3,1)
Row row2 = sheet.createRow(2);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("大白兔");
Cell cell22 = row2.createCell(1);
cell22.setCellValue(888);
Cell cell23 = row2.createCell(2);
String time3 = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell23.setCellValue(time3);
FileOutputStream outputStream = new FileOutputStream(PATH + "入库表03.xls");
//输出
workbook.write(outputStream);
//关闭流
outputStream.close();
System.out.println("入库表03生成完毕");
}
07版
@Test
public void ExcelTest07() throws Exception {
//1.创建一个工作簿
Workbook workbook = new XSSFWorkbook();
//2.创建一个工作表
Sheet sheet = workbook.createSheet("入库统计表07");
//3.创建一个行 (1,1)
Row row = sheet.createRow(0);
//4.创建一个单元格
Cell cell0 = row.createCell(0);
cell0.setCellValue("货名");
Cell cell01 = row.createCell(1);
cell01.setCellValue("数量");
Cell cell02 = row.createCell(2);
cell02.setCellValue("入库时间");
//3.第二行 (2,1)
Row row1 = sheet.createRow(1);
//4.创建一个单元格
Cell cell1 = row1.createCell(0);
cell1.setCellValue("旺仔");
Cell cell2 = row1.createCell(1);
cell2.setCellValue(666);
Cell cell3 = row1.createCell(2);
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell3.setCellValue(time);
//第三行(3,1)
Row row2 = sheet.createRow(2);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("大白兔");
Cell cell22 = row2.createCell(1);
cell22.setCellValue(888);
Cell cell23 = row2.createCell(2);
String time3 = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell23.setCellValue(time3);
FileOutputStream outputStream = new FileOutputStream(PATH + "入库表07.xlsx");
//输出
workbook.write(outputStream);
//关闭流
outputStream.close();
System.out.println("入库表07生成完毕");
}
注意对象的区别, 文件后缀 ,
03版对象 HSSFWorkbook(); .xls,
07版对象 XSSFWorkbook(); .xlsx
数据批量导入!
大文件写HSSF I
缺点:最多只能处理65536行,否则会地出异常
java.1ang.I1TegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
优点:过程中写入缓存,不操作磁盘,最后一次性写入磁盘,速度快
大文件写XSSF
缺点: 写数据时速度非常慢,非常耗内存,也会发生内存溢出,如100万条优点:可以写较人的数据量,如20万条
大文件写5XSSF
优点:可以写非常大的数据量,如100万条甚至更多条,写数据速度快,占用更少的内存
二、POI读取excel
//import org.apache.commons.math3.complex.ComplexFormat;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.joda.time.DateTime;
import org.junit.Test;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
public class ExcelReadTest {
String PATH = "D:\\ideaprojecttest\\poi\\";
@Test
public void testCelType() throws Exception {
//获取文件流
FileInputStream inputStream = new FileInputStream(PATH+"明细表.xls");
//创建一个工作簿,使用excel能操作的这边他都可以操作!
Workbook workbook = new HSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
//获取标题内容
Row rowTitle = sheet.getRow(0);
if(rowTitle!=null){
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowTitle.getCell(cellNum);
if(cell!=null){
//CellType type = cell.getCellType();
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + " | ");
}
}
System.out.println();
}
//获取表中的内容
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowNum = 1; rowNum < rowCount ; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if(rowData!=null){
//读取列
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.println("["+(rowNum+1)+"-"+(cellNum+1)+"]");
Cell cell = rowData.getCell(cellNum);
//匹配列的数据类型
if(cell!=null){
String value = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC: //数字(日期、普通数字)
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 判断是否为日期类型
Date date = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat(
"yyyy-MM-dd HH:mm");
value = formater.format(date);
}else{
value = new DecimalFormat("0").format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
value = cell.toString();
break;
}
System.out.println(value);
}
}
}
}
inputStream.close();
}
}