POI-Excel写
(数据少的情况)
1.创建一个maven项目
2.引入pom依赖
<!-- 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.10</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.1</version>
</dependency>
3.使用
03版excel:以.xls结尾,最多只能保存65536行,操作对象为HSSFWorkbook。
@Test
public void testWriter03() throws Exception {
//1.创建一个工作蒲 03
Workbook workbook = new HSSFWorkbook();
//2.创建一个工作表
Sheet sheet = workbook.createSheet("统计表");
//3.创建一行
Row row1 = sheet.createRow(0);
//4.创建一个单元格
Cell cell11 = row1.createCell(0);
cell11.setCellValue("今日新增观众");
//(1,2)
Cell cell12 = row1.createCell(1);
cell12.setCellValue(666);
//第二行(2,1)
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("统计时间");
//(2,2)
Cell cell22 = row2.createCell(1);
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(time);
//生成一张表 03版本就是使用xls结尾
FileOutputStream fileOutputStream = new FileOutputStream(path + "统计表03.xls");
//输出
workbook.write(fileOutputStream);
//关闭流
fileOutputStream.close();
workbook.close();
System.out.println("统计表3 生成完毕");
}
07版excel:以.xlsx结尾,理论上可以保存无限行,操作对象为XSSFWorkbook。
@Test
public void testWriter07() throws Exception {
//1.创建一个工作蒲 07
Workbook workbook = new XSSFWorkbook();
//2.创建一个工作表
Sheet sheet = workbook.createSheet("统计表");
//3.创建一行
Row row1 = sheet.createRow(0);
//4.创建一个单元格
Cell cell11 = row1.createCell(0);
cell11.setCellValue("今日新增观众");
//(1,2)
Cell cell12 = row1.createCell(1);
cell12.setCellValue(666);
//第二行(2,1)
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("统计时间");
//(2,2)
Cell cell22 = row2.createCell(1);
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(time);
//生成一张表 07版本就是使用xlsx结尾
FileOutputStream fileOutputStream = new FileOutputStream(path + "统计表07.xlsx");
//输出
workbook.write(fileOutputStream);
//关闭流
fileOutputStream.close();
workbook.close();
System.out.println("统计表07 生成完毕");
}
数据批量导入
大文件写HSSF
缺点:最多只能处理65536行,否则会抛出异常
java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
优点:过程中写入缓存,不操作磁盘,最后一次性写入磁盘,速度快
使用:
@Test
public void testWrite03BigDate() throws Exception {
//时间
long begin = System.currentTimeMillis();
//创建一个工作蒲
Workbook workbook = new HSSFWorkbook();
//创建表
Sheet sheet = workbook.createSheet();
//写入数据
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("over");
FileOutputStream fileOutputStream = new FileOutputStream(path + "testWrite03BigDate.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
workbook.close();
long end = System.currentTimeMillis();
System.out.println((double) (end-begin)/1000);
}
耗时:2.133
大文件写XSSF
缺点:写数据时速度非常慢,非常耗内存,也会发生内存溢出,如100万条
优点:可以写较大的数据量,如20万条
@Test
public void testWrite07BigDate() throws Exception {
//时间
long begin = System.currentTimeMillis();
//创建一个工作蒲
Workbook workbook = new XSSFWorkbook();
//创建表
Sheet sheet = workbook.createSheet();
//写入数据
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("over");
FileOutputStream fileOutputStream = new FileOutputStream(path + "testWrite07BigDate.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
workbook.close();
long end = System.currentTimeMillis();
System.out.println((double) (end-begin)/1000);
}
耗时:7.758
大文件写SXSSF
优点:可以写非常大的数据量,如100万条甚至更多,写数据速度快,占用更少的内存
注意:
- 过程中会产生临时文件,需要清理临时文件
- 默认由100条记录被保存在内存中,如果超过这个数量,则最前面的数据被写入到临时文件
- 如果想自定义内存中数据的数据,可以使用new SXSSFWorkbook(数量)
使用:
@Test
public void testWrite07BigDateS() throws Exception {
//时间
long begin = System.currentTimeMillis();
//创建一个工作蒲
Workbook workbook = new SXSSFWorkbook();
//创建表
Sheet sheet = workbook.createSheet();
//写入数据
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("over");
FileOutputStream fileOutputStream = new FileOutputStream(path + "testWrite07BigDateS.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
//清除临时文件
((SXSSFWorkbook)workbook).dispose();
workbook.close();
long end = System.currentTimeMillis();
System.out.println((double) (end-begin)/1000);
}
耗时:1.587
POI-Excel读
03版本:
使用:
@Test
public void testRead03() throws Exception {
//获取文件流
FileInputStream fileInputStream = new FileInputStream(path + "统计表03.xls");
//1.创建一个工作蒲 03
Workbook workbook = new HSSFWorkbook(fileInputStream);
//2.得到表
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
System.out.println(cell.getStringCellValue());
fileInputStream.close();
}
easyExcel操作
导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
测试
通用数据生成
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;
}
对象
@Getter
@Setter
@EqualsAndHashCode
public class DemoData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}
代码
@Test
public void simpleWrite() {
// 注意 simpleWrite在数据量不大的情况下可以使用(5000以内,具体也要看实际情况),数据量大参照 重复多次写入
// 写法1 JDK8+
// since: 3.0.0-beta1
String fileName = path + "EasyTest.xlsx";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
//writer(fileName,格式类)
//sheet(表名)
//doWriter(数据)
EasyExcel.write(fileName, DemoData.class)
.sheet("模板")
.doWrite(() -> {
// 分页查询数据
return data();
});
}
效果