EasyExcel与POI
执行流程与区别
tip:07版本的Excel最多有104万
先引入pom
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/joda-time/joda-time -->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.11.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.2</version>
</dependency>
POI的写入
public void testWriter03() throws Exception {
//创建接收路径
String path="D:\\EasyExcel\\";
//创建工作簿
Workbook workbook=new XSSFWorkbook();
//创建工作表
Sheet sheet=workbook.createSheet("核算统计表");
// 创建第一行
Row row=sheet.createRow(0);
Cell cell=row.createCell(0);
cell.setCellValue("今日新增");
Cell cell1=row.createCell(1);
cell1.setCellValue(666);
// 创建第二行
Row row1=sheet.createRow(1);
Cell cell2=row1.createCell(0);
cell2.setCellValue("人数");
Cell cell3=row1.createCell(1);
cell3.setCellValue("sdw");
// 生成一张表
FileOutputStream fileOutputStream=new FileOutputStream(path+"hello.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
}
测试生成百万数据的时间
public void Test() throws Exception {
//创建一个存放地址
Date date=new Date();
String path="D:\\EasyExcel\\";
//创建一个工作簿
Workbook workbook=new SXSSFWorkbook();
//创建工作表
Sheet sheet=workbook.createSheet("填满表");
Row row ;
Cell cell = null;
for(int i=0;i<1000000 ;i++){
row= sheet.createRow(i);
for (int j=0;j<10;j++){
cell=row.createCell(j);
cell.setCellValue(j);
}
}
FileOutputStream fileOutputStream=new FileOutputStream(path+"Test.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
Date date1=new Date();
long from=date.getTime();
long end=date1.getTime();
System.out.println(end-from);
}
电脑跑完11秒多
POI的读取
public void Test() throws Exception {
//路径地址
String path="D:\\EasyExcel\\";
FileInputStream inputStream=new FileInputStream(path+"hello.xlsx");
//创建工作簿
Workbook workbook=new XSSFWorkbook(inputStream);
Sheet sheet= workbook.getSheetAt(0);
Row row=sheet.getRow(0);
Cell cell=row.getCell(0);
System.out.println(cell.getStringCellValue());
inputStream.close();
}
读取多个，需要对每行数据的类型进行识别
public void Test() throws Exception {
String path = "D:\\EasyExcel\\";
FileInputStream inputStream = new FileInputStream(path + "hello.xlsx");
//创建一个工作簿
Workbook workbook = new XSSFWorkbook(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 cellType = cell.getCellType();
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + " | ");
}
}
System.out.println();
}
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowNumber = 1; rowNumber < rowCount; rowNumber++) {
Row rowData = sheet.getRow(rowNumber);
if (rowData!=null){
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNumber=0;cellNumber<cellCount;cellNumber++){
System.out.print("["+(rowNumber+1)+"-"+(cellNumber+1)+"]");
Cell cell=rowData.getCell(cellNumber);
if (cell!=null){
CellType cellType = cell.getCellType();
String cellValue = "";
switch (cellType){
case STRING:
System.out.print("字符串类型");
cellValue=cell.getStringCellValue();
break;
case BOOLEAN:
System.out.print("布尔类型");
cellValue=String.valueOf(cell.getBooleanCellValue());
break;
case BLANK:
System.out.print("空类型");
break;
case NUMERIC:
System.out.print("数字类型");
if(DateUtil.isCellDateFormatted(cell)){
System.out.print("日期");
Date date= cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
}else {
System.out.print("普通数字");
cellValue= cell.toString();
}
break;
case ERROR:
System.out.print("类型错误");
break;
}
System.out.println(cellValue);
}
}
}
}
}
计算公式的使用实例
public void Test2() throws Exception {
String path = "D:\\EasyExcel\\";
FileInputStream inputStream=new FileInputStream(path+"hello.xlsx");
Workbook workbook=new XSSFWorkbook(inputStream);
Sheet sheet=workbook.getSheetAt(0);
Row row=sheet.getRow(4);
Cell cell=row.getCell(0);
//拿到计算公式
FormulaEvaluator formulaEvaluator =new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
//输出单元格的内容
CellType cellType= cell.getCellType();
switch (cellType){
case FORMULA:
String formula =cell.getCellFormula();
System.out.println(formula);
//计算
CellValue cellValue=formulaEvaluator.evaluate(cell);
String cel=cellValue.formatAsString();
System.out.println(cel);
break;
}
}
EasyExcel是一个基于Java简单的，省内存的读写Excel的开源项目，具体的可以官网上查看，找不到源码的话可以私信我
先引入pom依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
写入
@Slf4j
public class Data {
private List<DemoData> data() {
List<DemoData> list = ListUtils.newArrayList();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list.add(data);
}
return list;
}
/**
* 最简单的写
* <p>
* 1. 创建excel对应的实体对象 参照{@link DemoData}
* <p>
* 2. 直接写即可
*/
@Test
public void simpleWrite() {
// 注意 simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况），数据量大参照 重复多次写入
// 写法1 JDK8+
// since: 3.0.0-beta1
String fileName = "D:\\EasyExcel\\EasyTest.xlsx";
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName, DemoData.class)
.sheet("模板")
.doWrite(() -> {
// 分页查询数据
return data();
});
模板
public class DemoData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}
读取
@Test
public void simpleRead() {
// 写法1：JDK8+ ,不用额外写一个DemoDataListener
// since: 3.0.0-beta1
String fileName = "D:\\EasyExcel\\EasyTest.xlsx";
// 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
// 这里每次会读取100条数据 然后返回过来 直接调用使用数据就行
EasyExcel.read(fileName, DemoData.class, new PageReadListener<DemoData>(dataList -> {
for (DemoData demoData : dataList) {
log.info("读取到一条数据{}", JSON.toJSONString(demoData));
}
})).sheet().doRead();
固定套路
1.写入，固定类格式进行写入
2.读取，根据监听器 设置的规则进行读取
扫一扫
2900
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
