PIO 和 EasyExcel
POI和easyExcel
应用场合
- 将用户信息导出为Excel表格
- 将Excel表中的信息录入到网站数据库
Apache POI
POI提供API给JAVA程序对Microsoft Office格式档案读和写的功能
结构:
- HSSF 读写 Excel ( xls, 03版, 行列最多65536 )
- XSSF 读写 Excel OOXML格式档案的功能 ( xlsx, 07 版 )
- HWPF 读写Word
- HSLF 读写PowerPoint
- HDGF 读写Visio
EasyExcel
JAVA解析Excel比较有名的框架, 基本不会内存溢出
Apache POI
导入Maven依赖
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<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>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
java万物皆对象
- 工作簿
- 工作表
- 行
- 单元格
package cn.ylq;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;
import java.io.FileOutputStream;
public class ExcelWriteTest {
@Test
public void testWrite03() throws Exception {
String PATH ="F:\\工程库\\intelliJ_Idea\\POIStudy\\";
//1. 创建一个工作簿
Workbook workbook = new HSSFWorkbook();
//2. 创建一个工作表
Sheet sheet = workbook.createSheet("伊里奇创建的工作表");
//3. 创建一行
Row row1 = sheet.createRow (0);
//4. 创建两个单元格
Cell cell11 = row1.createCell(0);
Cell cell12 = row1.createCell(1);
//5. 单元格中录入数据
cell11.setCellValue("Java录入的哦");
cell12.setCellValue("天下无敌");
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
String time = new DateTime().toString("yyyy年MM月dd日 HH时mm分ss秒");
cell21.setCellValue(time);
//生成一张表
FileOutputStream fileOutputStream = new FileOutputStream(PATH+"这是我建的Excel.xls");
workbook.write(fileOutputStream);
//关闭流
fileOutputStream.close();
//完工
System.out.println("Excel03生成完毕了!!!!!!!");
}
}
07版的也差不多
@Test
public void testWrite07() throws Exception {
String PATH ="F:\\工程库\\intelliJ_Idea\\POIStudy\\";
//1. 创建一个工作簿 注意这里实体类变了
Workbook workbook = new SXSSFWorkbook();
//2. 创建一个工作表
Sheet sheet = workbook.createSheet("伊里奇创建的工作表");
//3. 创建一行
Row row1 = sheet.createRow (0);
//4. 创建两个单元格
Cell cell11 = row1.createCell(0);
Cell cell12 = row1.createCell(1);
//5. 单元格中录入数据
cell11.setCellValue("Java录入的哦");
cell12.setCellValue("天下无敌");
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
String time = new DateTime().toString("现在是:yyyy年MM月dd日 HH时mm分ss秒");
cell21.setCellValue(time);
//生成一张表 注意这里后缀变了
FileOutputStream fileOutputStream = new FileOutputStream(PATH+"这是07的Excel.xlsx");
workbook.write(fileOutputStream);
//关闭流
fileOutputStream.close();
//完工
System.out.println("Excel07生成完毕了!!!!!!!");
}
}
对象的区别,
后缀 xls, xlsx
大文件写HSSF
缺点: 最多只能处理65536行, 否则会抛出异常
优点: 过程中写入缓存, 不操作磁盘, 最后一次性写入磁盘, 速度快
大文件写XSSF
缺点: 写数据时速度非常慢, 非常耗内存, 也会发生内存溢出, 如100万条
优点: 可以写较大的数据量, 如20万条
大文件写SXSSF
优点: 可以写非常大的数据量, 如100万条甚至更多条, 写数据速度快, 占用更少的内存
注意: 过程中会产生临时文件, 需要清理临时文件
默认由100条记录被保存在内存中, 如果超过这数量, 则最前面的数据被写入临时文件
如果想自定义内存中数据的数量, 可以使用new SXSSFWorkbook(数量)
@Test
public void PIOBigDataTest() throws Exception {
// 记录开始运行时间
long beginTime = System.currentTimeMillis();
// 创建工作簿
Workbook workbook = new HSSFWorkbook();
// 创建工作表
Sheet sheet = workbook.createSheet("计算处理时间");
// 挨个录入数据
for(int i=0;i<65536;i++){
// 创建第i行
Row row = sheet.createRow(i);
for(int j=0;j<10;j++){
// 创建单元格[i,j]
Cell cell = row.createCell(j);
// 单元格[i,j]中写入数据:j
cell.setCellValue(j);
}
}
// 新建文件输出流
String path ="F:\\工程库\\intelliJ_Idea\\POIStudy\\";
FileOutputStream fileOutputStream = new FileOutputStream(path + "03大数据测试.xls");
// 文件输出
workbook.write(fileOutputStream);
// 关闭输出流
fileOutputStream.close();
System.out.println("运行结束");
// 记录运行结束时间
long endTime = System.currentTimeMillis();
// 计算程序运行时间
System.out.println("系统运行时长:"+(double)(endTime-beginTime)/100+"秒");
}
}
如果用了SXSSF, 用以下命令清理临时表
先把 Workbook 抽象类转成实体类 SXSSFWorkbook, 再调用其实体类的 dispose 方法
((SXSSFWorkbook) workbook).dispose();
SXSSFWorkbook 来自官方的解释: 实现"BigGridDemo"策略的流式XSSFWorkbook版本. 这允许写入非常大的文件而不会耗尽内存, 因为任何时候只有配置的行部分被保存在内存中.
请注意, 仍然可能会消耗大量内存, 这些内存基于您正在使用的功能,例如内存基于您正在使用的功能, 例如合并区域, 注释... 仍然只存储在内存中, 因此如果广泛使用, 可能需要大量内存
- Excel读取
package cn.ylq;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.junit.jupiter.api.Test;
import java.io.FileInputStream;
import java.io.IOException;
public class ExcelReader {
@Test
public void ExcelReaderTest07() throws IOException {
// 数据流地址
String PATH ="F:\\工程库\\intelliJ_Idea\\POIStudy\\";
// 文件输入流
FileInputStream fileInputStream = new FileInputStream(PATH+"5. 权重表-针灸治疗学-头痛.xlsx");
// 创建一个工作簿
Workbook workbook = new XSSFWorkbook(fileInputStream);
// 获取表1行1列1的数据
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(3);
Cell cell = row.getCell(6);
// 输出获取的字符串类型数据
System.out.println(cell.getStringCellValue());
// 关闭流
fileInputStream.close();
}
}
- 注意获取值的类型
-
读取数据类型讨论 很重要!!
package cn.ylq;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Date;
public class ExcelReader {
@Test
public void testCellType() throws Exception {
// 获取文件流
String PATH ="F:\\工程库\\intelliJ_Idea\\POIStudy\\";
FileInputStream fis = new FileInputStream(PATH+"各种数据类型.xlsx");
// 创建工作簿
Workbook workbook = new XSSFWorkbook(fis);
// 创建工作表
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){
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.print("["+rowNum+"-"+cellNum+"]");
Cell cell = rowData.getCell(cellNum);
// 匹配单元格的数据类型
if(cell!=null){
int cellType = cell.getCellType();
String cellValue = "";
switch(cellType){
case XSSFCell.CELL_TYPE_STRING: //字符串类型
System.out.println("(string)");
cellValue = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_BOOLEAN: //布尔值类型
System.out.println("(boolean)");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK: //空
System.out.println("(blank)");
break;
case XSSFCell.CELL_TYPE_NUMERIC: //数字 (日期\普通数字)
System.out.println("(numeric)");
if(DateUtil.isCellDateFormatted(cell)){
System.out.println("(日期)");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy年MM月dd日");
}else{
// 不是日期格式,防止数字过长!
System.out.println("(转换为字符串输出)");
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case XSSFCell.CELL_TYPE_ERROR: //字符串类型
System.out.println("(error)");
break;
default:
System.out.pringtln("(Other Type)");
}
System.out.println(cellValue);
}
}
}
}
fis.close();
}
}
单元格中的公式:
package cn.ylq;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;
import java.io.FileInputStream;
public class ExcelReadFormula {
@Test
public void ExcelReadFormula() throws Exception {
String PATH = "F:\\工程库\\intelliJ_Idea\\POIStudy\\";
FileInputStream fis = new FileInputStream(PATH+"带公式的.xlsx");
Workbook workbook = new XSSFWorkbook(fis);
FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(3);
Cell cell = row.getCell(0);
int cellType = cell.getCellType();
if (cell!=null){
switch(cellType){
case XSSFCell.CELL_TYPE_FORMULA:
String formula = cell.getCellFormula();
System.out.println(formula);
CellValue evaluate = formulaEvaluator.evaluate(cell);
String cellValue = evaluate.formatAsString();
System.out.println(cellValue);
break;
default:
System.out.println("不是公式!");
}
}
fis.close();
}
}
EasyExcel
- maven依赖
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
</dependencies>
(暂时只学写操作,读操作来日方长)
- 创建一个实体类
package cn.ylq.write;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.util.Date;
@Data
public class DemoData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
@ExcelProperty("我建的字段")
private String myColumn;
}
- 创建一个测试类, 工作方法写在里面
package cn.ylq.write;
import com.alibaba.excel.EasyExcel;
import org.junit.jupiter.api.Test;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class easyTest {
private List<DemoData> data() {
List<DemoData> list = new ArrayList<DemoData>();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
data.setMyColumn("寒风飘飘落叶");
list.add(data);
}
return list;
}
/**
* 最简单的写
* <p>1. 创建excel对应的实体对象 参照{@link DemoData}
* <p>2. 直接写即可
*/
@Test
public void simpleWrite() {
String PATH = "F:\\工程库\\intelliJ_Idea\\easyExcel\\";
String NAME = "生成的excel";
// 写法1
String fileName = PATH + NAME + ".xlsx";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName, DemoData.class).sheet("输入表名的地方").doWrite(data());
}
}