什么是POI
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
- HSSF － 提供读写Microsoft Excel格式档案的功能。（.xls）
03版，缺点：最多只能写65536条数据，超过则抛出异常
优点：写速度快
- XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。（.xlsx）
缺点：写速度慢，会发生内存溢出
优点：可以写较大量的数据，
-
HWPF － 提供读写Microsoft Word格式档案的功能。
-
HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
-
HDGF － 提供读写Microsoft Visio格式档案的功能。
引入依赖
<dependencies>
<!--xls(03)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!--xlsx(07)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!--日期格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>
<!--test-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
写
@Test
public void testWrite03() throws Exception {
//创建新的工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//创建sheet
HSSFSheet sheet = workbook.createSheet("sheet1");
//创建行
HSSFRow row1 = sheet.createRow(0);
//创建单元格
HSSFCell cell1 = row1.createCell(0);
//创建单元格内容
cell1.setCellValue("今日登录人数");
FileOutputStream out = new FileOutputStream("d:/test-write03.xls");
workbook.write(out);
out.close();
System.out.println("文件生成成功");
}
@Test
public void testWrite07() throws Exception {
//创建新的工作簿
Workbook workbook = new XSSFWorkbook();
//创建sheet
Sheet sheet = workbook.createSheet("sheet1");
//创建行
Row row1 = sheet.createRow(0);
//创建单元格
Cell cell1 = row1.createCell(0);
//创建单元格内容
cell1.setCellValue("今日登录人数");
FileOutputStream out = new FileOutputStream("d:/test-write07.xlsx");
workbook.write(out);
out.close();
System.out.println("文件生成成功");
}
读
@Test
public void testRead03() throws Exception {
FileInputStream is = new FileInputStream("d:/excel-poi-190105/商品表-03.xls");
HSSFWorkbook workbook = new HSSFWorkbook(is);
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFRow row = sheet.getRow(0);
HSSFCell cell = row.getCell(0);
String cellValue = cell.getStringCellValue();
System.out.println(cellValue);
is.close();
}
@Test
public void testRead07() throws Exception {
FileInputStream is = new FileInputStream("d:/excel-poi-190105/商品表-07.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(is);
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row = sheet.getRow(0);
XSSFCell cell = row.getCell(0);
String cellValue = cell.getStringCellValue();
System.out.println(cellValue);
is.close();
}
读取不同格式
/**
* 读取不同的单元格类型
* @throws Exception
*/
@Test
public void testReadCellType() throws Exception {
FileInputStream is = new FileInputStream("d:/会员消费商品明细表.xls");
HSSFWorkbook workbook = new HSSFWorkbook(is);
HSSFSheet sheet = workbook.getSheetAt(0);
//读取标题行的内容
HSSFRow rowTitle = sheet.getRow(0);
if(rowTitle != null){
int cellCount = rowTitle.getPhysicalNumberOfCells();
//System.out.println(cellCount);
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
HSSFCell cell = rowTitle.getCell(cellNum);
if(cell != null){
/*int cellType = cell.getCellType();
System.out.println(cellType);*/ //HSSFCell.CELL_TYPE_STRING
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + "|");
}
}
System.out.println();
}
//读取数据行的内容
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
HSSFRow rowData = sheet.getRow(rowNum);
if(rowData != null){
int cellCount = rowData.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.print("【" + (rowNum + 1) + "-" + (cellNum + 1) + "】");
HSSFCell cell = rowData.getCell(cellNum);
if(cell != null){
int cellType = cell.getCellType();
//判断单元格的数据类型
String cellValue = "";
switch (cellType){
case HSSFCell.CELL_TYPE_STRING://字符串
System.out.print("【字符串】");
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: //布尔
System.out.print("【布尔】");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK: //空
System.out.print("【空】");
break;
case Cell.CELL_TYPE_NUMERIC: //数值或时间
System.out.print("【NUMERIC】");
if(HSSFDateUtil.isCellDateFormatted(cell)){//日期
System.out.print("【日期】");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
}else{//数值
System.out.print("【数值】");
//double cellValue1 = cell.getNumericCellValue();
//现将当前列的数据类型转成字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.getStringCellValue();
}
break;
case Cell.CELL_TYPE_ERROR: //错误
System.out.println("【数据类型错误】");
break;
}
System.out.println(cellValue);
}
}
}
}
is.close();
}
读取excel公式的值
/**
* 读取计算公式的值
* @throws Exception
*/
@Test
public void testReadFormula() throws Exception {
FileInputStream is = new FileInputStream("d:/计算公式.xls");
HSSFWorkbook workbook = new HSSFWorkbook(is);
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFRow row = sheet.getRow(4);
HSSFCell cell = row.getCell(0);
int cellType = cell.getCellType();
switch (cellType){
case Cell.CELL_TYPE_FORMULA:
String formula = cell.getCellFormula(); //获取公式
System.out.println(formula);
//创建公式计算器
HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator(workbook);
//对单元格进行计算
CellValue evaluate = formulaEvaluator.evaluate(cell);
//得到单元格数据
String cellValue = evaluate.formatAsString();
System.out.println(cellValue);
break;
}
}
导出大Excel
03版最大支持65536条数据，不适合。普通的07版太慢了，导出大文件需要用SXSSFWorkbook。真实环境可以再考虑多线程+SXSSFWorkbook。
/**
* 缺点：最多只能写65536条数据，超过则抛出异常
* 优点：写速度快
* @throws Exception
*/
@Test
public void testWrite03BigData() throws Exception {
long begin = System.currentTimeMillis();
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
for (int rowNum = 0; rowNum < 65536; rowNum++) {
HSSFRow row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
HSSFCell cell = row.createCell(cellNum);
cell.setCellValue(rowNum + "-" + cellNum);
}
}
System.out.println("写入完毕");
FileOutputStream out = new FileOutputStream("d:/test-write03-bigdata.xls");
workbook.write(out);
out.close();
long end = System.currentTimeMillis();
System.out.println((double) (end - begin)/1000);
}
/**
* 缺点：写速度慢，会发生内存溢出
* 优点：可以写较大量的数据，
* @throws Exception
*/
@Test
public void testWrite07BigData() throws Exception {
long begin = System.currentTimeMillis();
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();
for (int rowNum = 0; rowNum <= 105536; rowNum++) {
XSSFRow row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
XSSFCell cell = row.createCell(cellNum);
cell.setCellValue(rowNum + "-" + cellNum);
}
}
System.out.println("写入完毕");
FileOutputStream out = new FileOutputStream("d:/test-write07-bigdata.xlsx");
workbook.write(out);
out.close();
long end = System.currentTimeMillis();
System.out.println((double) (end - begin)/1000);
}
//SXSSF
@Test
public void testWrite07BigDataFast() throws Exception {
long begin = System.currentTimeMillis();
//面向接口编程
Workbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet();
for (int rowNum = 0; rowNum <= 105536; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(rowNum + "-" + cellNum);
}
}
System.out.println("写入完毕");
FileOutputStream out = new FileOutputStream("d:/test-write07-bigdata-fast.xlsx");
workbook.write(out);
out.close();
//清除临时文件
((SXSSFWorkbook)workbook).dispose();
long end = System.currentTimeMillis();
System.out.println((double) (end - begin)/1000);
}
过程中会产生临时文件，需要清理临时文件
默认由100条记录被保存在内存中，如果查过这数量，则最前面的数据被写入临时文件
如果想自定义内存中数据的数量，可以使用new SXSSFWorkbook(数量)
SXSSFWorkbook-来至官方的解释：实现“BigGridDemo”策略的流式XSSFWorkbook版本。这允许写入非常大的文件而不会耗尽内存，因为任何时候只有可配置的行部分被保存在内存中。
请注意，仍然可能会消耗大量内存，这些内存基于您正在使用的功能，例如合并区域，注释…仍然只存储在内存中，因此如果广泛使用，可能需要大量内存。
1656
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
