一、POI简介(Apache POI)
1、什么是POI
Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
HSSF - 提供读写Microsoft Excel格式档案的功能。(.xls)
XSSF - 提供读写Microsoft Excel OOXML格式档案的功能。(.xlsx)
HWPF - 提供读写Microsoft Word格式档案的功能。
HSLF - 提供读写Microsoft PowerPoint格式档案的功能。
HDGF - 提供读写Microsoft Visio格式档案的功能。
2、官网
http://poi.apache.org/
官网可以找到文档和每个版本的下载地址
二、读写Excel
1、创建一个普通的maven项目
项目名:poi
2、pom中引入xml相关依赖
com.atguigu.poi;
public class ExcelTest {
@Test
public void testCreate() throws IOException {
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 在Excel工作簿中建一工作表,其名为缺省值
//HSSFSheet sheet = workbook.createSheet();
// 如要新建一名为"会员登录统计"的工作表,其语句为:
HSSFSheet sheet = workbook.createSheet(“会员登录统计”);
// 创建行(第一行)
HSSFRow row1 = sheet.createRow(0);
// 创建单元格(第一列)
HSSFCell cell1 = row1.createCell(0);
cell1.setCellValue(“今日人数”);
// 创建单元格(第二列)
HSSFCell cell2 = row1.createCell(1);
cell2.setCellValue(666);
//创建单元格(第三列)
HSSFCell cell3 = row1.createCell(2);
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”);
cell3.setCellValue(sdf.format(date));
// 新建一输出文件流(注意:要先创建文件夹)
FileOutputStream out = new FileOutputStream(“d:/poi-excel-helen/test-write.xls”);
// 把相应的Excel 工作簿存盘
workbook.write(out);
out.flush();
// 操作结束,关闭文件
out.close();
System.out.println(“文件生成成功”);
}
}
4、创建测试用例:读
@Test
public void testRead() throws Exception {
InputStream is = new FileInputStream(“d:/poi-excel-helen/商品表.xls”);
/he 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)/
//InputStream is = new FileInputStream(“d:/poi-excel-helen/商品表2.xlsx”);
HSSFWorkbook workbook = new HSSFWorkbook(is);
// 创建对工作表的引用。
// 假定那张表有着缺省名"类别1"(getSheetAt(int index)按索引)
HSSFSheet sheet = workbook.getSheet(“类别1”);
// 读取第一行第一列
HSSFRow row1 = sheet.getRow(0);
HSSFCell cell1 = row1.getCell(0);
// 输出单元内容
System.out.println("第一行第一列是: " + cell1.getStringCellValue());
}
5、读取不同的数据类型
@Test
public void testCellType() throws Exception {
InputStream is = new FileInputStream(“d:/poi-excel-helen/会员消费商品明细表.xls”);
HSSFWorkbook workbook = new HSSFWorkbook(is);
HSSFSheet sheet = workbook.getSheetAt(0);
// 读取第一行所有内容
HSSFRow rowTitle = sheet.getRow(0);
if (rowTitle != null) {// 行不为空
// 读取cell
for (int cellNum = 0; cellNum < rowTitle.getLastCellNum(); cellNum++) {
HSSFCell cell = rowTitle.getCell(cellNum);
if (cell != null) {
int cellType = cell.getCellType();
String cellValue = cell.getStringCellValue();
System.out.print(“第1行:”);
System.out.println(“第” + (cellNum + 1) + “列:cellType:” + cellType + “,cellValue:” + cellValue);
}
}
}
// 读取商品列表
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
HSSFRow rowData = sheet.getRow(rowNum);
if (rowData != null) {// 行不为空
// 读取cell
for (int cellNum = 0; cellNum < rowData.getLastCellNum(); cellNum++) {
HSSFCell cell = rowData.getCell(cellNum);
if (cell != null) {
int cellType = cell.getCellType();
//判断单元格数据类型
String cellValue = “”;
double doubleCellValue = 0;
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING://字符串
System.out.print("【STRING】");
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN://布尔
System.out.print("【BOOLEAN】");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK://空
//注意空和没有值不一样,从来没有录入过内容的单元格不属于任何数据类型,不会走这个case
System.out.print("【BLANK】");
break;
case HSSFCell.CELL_TYPE_NUMERIC:
System.out.print("【NUMERIC】");
//cellValue = String.valueOf(cell.getNumericCellValue());
if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期
System.out.print("【日期】");
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”);
cellValue = sdf.format(date).trim();
} else {
// 不是日期格式,则防止当数字过长时以科学计数法显示
System.out.print("【转换成字符串】");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case Cell.CELL_TYPE_ERROR:
System.out.print("【数据类型错误】");
break;
default:
cellValue = “”;
break;
}
System.out.print(“第” + (rowNum + 1) + “行:”);
System.out.print(“第” + (cellNum + 1) + “列:cellType:” + cellType + “,cellValue:”);
if (!"".equals(cellValue)) {
System.out.println(cellValue);
} else {
System.out.println(doubleCellValue);
}
}
}
}
}
}
6、计算公式
@Test
public void testFormula() throws Exception{
InputStream is = new FileInputStream(“d:/poi-excel-helen/计算公式.xls”);
HSSFWorkbook workbook = new HSSFWorkbook(is);
HSSFSheet sheet = workbook.getSheetAt(0);
// 读取第五行第一列
HSSFRow row = sheet.getRow(4);
HSSFCell cell = row.getCell(0);
//公式计算器
HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator(workbook);
// 输出单元内容
int cellType = cell.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_FORMULA://2
System.out.print("【公式】");
//得到公式
String formula = cell.getCellFormula();
System.out.print(formula);
CellValue evaluate = formulaEvaluator.evaluate(cell);
int evaluateCellType = evaluate.getCellType();
switch (evaluateCellType) {
case Cell.CELL_TYPE_STRING:
System.out.print("【STRING】");
System.out.println(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print("【NUMERIC】");
System.out.println(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print("【BOOLEAN】");
System.out.println(cell.getBooleanCellValue());
break;
}
break;
}
}
7、xlsx文件读
HSSF只能操作03版本excel,扩展名.xls
SXSSF只能操作03以上版本(07版本)excel,扩展名.xlsx
@Test
public void testExcel07Read() throws Exception{
InputStream is = new FileInputStream(“d:/poi-excel-helen/商品表2.xlsx”);
//HSSFWorkbook workbook = new HSSFWorkbook(is);
// 创建Excel2007文件对象
XSSFWorkbook workbook2007 = new XSSFWorkbook(is);
XSSFSheet sheet = workbook2007.getSheetAt(0);
// 读取第一行第一列
XSSFRow row1 = sheet.getRow(0);
XSSFCell cell1 = row1.getCell(0);
// 输出单元内容
System.out.println("第一行第一列是: " + cell1.getStringCellValue());
}
8、大文件写
SXSSF可以处理大数据量的读写操作
例1:使用HSSF
缺点:最多只能处理65536行,否则会抛出异常
java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0…65535)
优点:过程中不操作磁盘,最后一次性写入磁盘,速度快
@Test
public void testExcel03WriteBigData() throws Exception {
//记录开始时间
long begin = System.currentTimeMillis();
//创建一个SXSSFWorkbook
//-1:关闭 auto-flushing,将所有数据存在内存中
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个sheet
HSSFSheet sheet = workbook.createSheet();
//xls文件最大支持65536行
for (int rowNum = 0; rowNum < 65536; rowNum++) {
//创建一个行
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {//创建单元格
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println(“done”);
FileOutputStream out = new FileOutputStream(“d:/poi-excel-helen/test-write-0.xls”);
workbook.write(out);
//记录结束时间
long end = System.currentTimeMillis();
System.out.println((double)(end - begin)/1000);
}
例2:使用SXFF
缺点:写数据时速度慢
优点:写大数据量时不会发生内存溢出、生成.xlsx文件,文件本身的体积做了优化
(1)手动向磁盘写数据
@Test
public void testExcel07WriteBigData1() throws Exception {
//记录开始时间
long begin = System.currentTimeMillis();
//创建一个SXSSFWorkbook
//-1:关闭 auto-flushing,将所有数据存在内存中
SXSSFWorkbook workbook = new SXSSFWorkbook(-1);
//创建一个sheet
Sheet sheet = workbook.createSheet();
for (int rowNum = 0; rowNum < 200000; rowNum++) {
//创建一个行
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {//创建单元格
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
// 手动向磁盘写数据
if (rowNum % 10000 == 0) {//一万行向磁盘写一次
((SXSSFSheet) sheet).flushRows(0); // 全部写入磁盘
}
}
System.out.println(“done”);
FileOutputStream out = new FileOutputStream(“d:/poi-excel-helen/test-write-1.xlsx”);
workbook.write(out);//将临时文件合并,写入最终文件
out.close();
// 处理磁盘中处理写入的临时文件夹
workbook.dispose();
//记录结束时间
long end = System.currentTimeMillis();
System.out.println((double)(end - begin)/1000);
}
(2)自动向磁盘写数据
@Test
public void testExcel07WriteBigData2() throws IOException {
//记录开始时间
long begin = System.currentTimeMillis();
/* 10000:
* When a new node is created via createRow() and the total number
* of unflushed records would exceed the specified value, then the
* row with the lowest index value is flushed and cannot be accessed
* via getRow() anymore.*/
SXSSFWorkbook workbook = new SXSSFWorkbook(10000);
//创建一个sheet
Sheet sheet = workbook.createSheet();
for (int rowNum = 0; rowNum < 200000; rowNum++) {
//创建一个行
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {//创建单元格
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println(“done”);
FileOutputStream out = new FileOutputStream(“d:/poi-excel-helen/test-write-2.xlsx”);
workbook.write(out);
out.close();
// 处理磁盘中处理写入的临时文件夹
workbook.dispose();
//记录结束时间
long end = System.currentTimeMillis();
System.out.println((double)(end - begin)/1000);
}
(非原创)