这里写自定义目录标题
PS：注意版本兼容问题，使用jdk1.8版本
EasyExcel操作Excel表格
一、导入依赖：
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.2</version>
</dependency>
二、编写实体类
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class User {
@ExcelProperty(value = "用户编号")
private String userId;
@ExcelProperty(value = "姓名")
private String userName;
@ExcelProperty(value = "性别")
private String gender;
@ExcelProperty(value = "工资")
private Double salary;
@ExcelProperty(value = "入职时间")
private Date hireDate;
}
三、编写测试类
public class EasyExcelReadToUser {
String username[] = {"荒天帝石昊","炎帝萧炎","鸿蒙掌控者秦羽","武祖林动","不良帅李星云"};
Double salary[] = {8897.0,7894.0,6987.0,7894.0,4569.0};
/**
* @Author 小虎
* @Description
*
* 写入50w条数据
* 开始时间：1662988028935
* 结束时间：1662988043958
* 总耗时：15.023
*
*
* 写入100w条数据
* 开始时间：1662988304015
* 结束时间：1662988329978
* 总耗时：25.963
*
* @Date 21:09 2022/9/12
* @Param []
* @return java.util.List<com.liao.easyExcel0_1.User>
**/
private List<com.liao.easyExcel0_1.User> getUserData() {
List<User> users = new ArrayList<>();
for (int i = 1; i <= 1000000; i++) {
Random random = new Random();
int count = random.nextInt(4);
com.liao.easyExcel0_1.User user = new com.liao.easyExcel0_1.User();
user.setUserId(UUID.randomUUID().toString());
user.setUserName(username[count]);
user.setGender(i % 2 == 0 ? "男" : "女");
user.setSalary(salary[count]);
user.setHireDate(new Date());
users.add(user);
}
return users;
}
@Test
public void testWriteExcel() {
long startTime = System.currentTimeMillis();
String PATH="F:\\360downloads\\filedownload\\";
String fileName = PATH+"EasyExcel用户信息.xlsx";
// 向Excel中写入数据 也可以通过 head(Class<?>) 指定数据模板
/**
* @Param fileName:Excel文件名称及路径
* @Param DemoData:数据实体类
* @Param 模板:工作表名
* @Param getUserData():数据集合（写入Excel表格的数据信息）
*
**/
EasyExcel.write(fileName, User.class)
.sheet("用户信息")
.doWrite(getUserData());
long endTime = System.currentTimeMillis();
System.out.println("开始时间："+startTime);
System.out.println("结束时间："+endTime);
System.out.println("总耗时："+(double)(endTime-startTime)/1000);
}
}
POI操作Excel表格
基本功能：
| 对象 | 描述 |
|---|---|
| HSSF | 提供读写Excel(.xls)格式档案的功能 |
| XSSF | 提供读写Excel OOXML(.xlsx)格式档案的功能 |
| HWPF | 提供读写Word格式档案的功能 |
| HSLF | 提供读写PowerPoint格式档案的功能 |
| HDGF | 提供读写Visio格式档案的功能 |
一、导入依赖
使用jdk1.8版本
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.2</version>
</dependency>
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.5</version>
</dependency>
二、测试类
2.1 使用HSSFWorkbook workbook = new HSSFWorkbook();对象创建03版本的Excel.xls表格文件
#文件路径
String PATH="F:\\360downloads\\filedownload\\";
03版本的Excel表格文件（.xls）只能导出65536条数据，超出则发生异常，如下图：
批量导出65536条数据：
/**
* @Author 小虎
* @Description
* 开始时间：1662994834476
* 结束时间：1662994840705
* 总耗时：6.229
* @Date 23:00 2022/9/12
* @Param []
* @return void
**/
@Test
public void testWrite03() throws IOException {
long startTime = System.currentTimeMillis();
//1.创建一个工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//2.创建一个工作表
HSSFSheet sheet = workbook.createSheet("用户信息表");
HSSFRow row = sheet.createRow(0);//第一行（设置表头）
HSSFCell cell = row.createCell(0);//第一行第一列
cell.setCellValue("ID");
cell = row.createCell(1);//第一行第二例
cell.setCellValue("书籍名称");
cell = row.createCell(2);//第一行第三列
cell.setCellValue("统计时间");
cell = row.createCell(3);
cell.setCellValue("作者");
cell = row.createCell(4);
cell.setCellValue("价格");
String author[]= {"辰东","爱吃西红柿","天蚕土豆","唐家三少","罗贯中","吴承恩","若森原创国漫"};
String bookname[] = {"斗破苍穹","三国演义","星辰变","画江湖之不良人","完美世界","遮天","西游记"};
String price[] = {"99","78","88","77","100","94","82"};
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
for(int i=0;i<65535;i++){
Random random = new Random();
int count = random.nextInt(6);
row = sheet.createRow(i+1);//第i+1行
cell = row.createCell(0);//第i+1行第一列
cell.setCellValue(UUID.randomUUID().toString());//赋值给第i+1行第一列
cell = row.createCell(1);//第i+1行第二列
cell.setCellValue(bookname[count]);
cell = row.createCell(2);//第i+1行第三列
cell.setCellValue(time);
cell = row.createCell(3);//第i+1行第四列
cell.setCellValue(author[count]);
cell = row.createCell(4);//第i+1行第五列
cell.setCellValue(price[count]);
}
//生成一张表 03版本使用.xls结尾
FileOutputStream fileOutputStream = null;
fileOutputStream = new FileOutputStream(PATH + "小说统计o3.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
long endTime = System.currentTimeMillis();
System.out.println("开始时间："+startTime);
System.out.println("结束时间："+endTime);
System.out.println("总耗时："+(double)(endTime-startTime)/1000);
}
使用HSSFWorkbook workbook = new HSSFWorkbook();导出65536条数据使用3.344秒
使用XSSFWorkbook workbook = new XSSFWorkbook();导出65536条数据使用15.057秒
2.2 使用XSSFWorkbook workbook = new XSSFWorkbook();对象创建07版本的Excel.xls表格文件
07版本的Excel表格文件（.xlsx）相对03版本可导出更多数据：
2.3 使用XSSFWorkbook workbook = new XSSFWorkbook();导出10w海量数
/**
* @Author 小虎
* @Description
* 10w海量数据导出到Excel.xlsx文件
* 开始时间：1662970531690
* 结束时间：1662970548127
* 所需时间：16.437
* @Date 16:18 2022/9/12
**/
@Test
public void testWrite07BigData() throws IOException {
//时间
long startTime = System.currentTimeMillis();
//创建工作部
XSSFWorkbook workbook = new XSSFWorkbook();
//HSSFWorkbook workbook = new HSSFWorkbook();
//创建表
XSSFSheet sheet = workbook.createSheet("海量数据导出07版本");
//写入数据
String author[]= {"辰东","爱吃西红柿","天蚕土豆","唐家三少","罗贯中","吴承恩","若森原创国漫"};
String bookname[] = {"斗破苍穹","三国演义","星辰变","画江湖之不良人","完美世界","遮天","西游记"};
String price[] = {"99","78","88","77","100","94","82"};
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
XSSFRow row = sheet.createRow(0);
XSSFCell cell = row.createCell(0);
cell.setCellValue("ID");
cell = row.createCell(1);//第一行第二例
cell.setCellValue("书籍名称");
cell = row.createCell(2);//第一行第三列
cell.setCellValue("统计时间");
cell = row.createCell(3);
cell.setCellValue("作者");
cell = row.createCell(4);
cell.setCellValue("价格");
for(int rowNum=0;rowNum<100000;rowNum++){
Random random = new Random();
int count = random.nextInt(6);
row = sheet.createRow(rowNum+1);
cell = row.createCell(0);
cell.setCellValue(UUID.randomUUID().toString());
cell = row.createCell(1);
cell.setCellValue(bookname[count]);
cell = row.createCell(2);
cell.setCellValue(time);
cell = row.createCell(3);
cell.setCellValue(author[count]);
cell = row.createCell(4);
cell.setCellValue(price[count]);
}
FileOutputStream fileOutputStream = new FileOutputStream(PATH+"10w海量数据导出07版本.xlsx");
//吧工作簿写入到文件中
workbook.write(fileOutputStream);
fileOutputStream.close();
long endTime = System.currentTimeMillis();
System.out.println("完成@！！！！");
System.out.println("开始时间："+startTime);
System.out.println("结束时间："+endTime);
System.out.println("所需时间："+(double)(endTime-startTime)/1000);
}
2.4 大文件写XSSF（SXSSFWorkbook workbook = new SXSSFWorkbook();）
缺点：写数据时速度非常慢，非常耗内存，也会发生内存溢出，如100万条
有点：可以写较大的数据量，如20万条
/**
* @Author 小虎
* @Description 大文件写SXSSF
* 开始时间：1662995450064
* 结束时间：1662995456178
* 所需时间：6.114
* @Date 16:20 2022/9/12
**/
@Test
public void testWrite07BigDataS() throws IOException {
//时间
long startTime = System.currentTimeMillis();
//创建工作部
SXSSFWorkbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet("海量数据导出07版本");
String author[]= {"辰东","爱吃西红柿","天蚕土豆","唐家三少","罗贯中","吴承恩","若森原创国漫"};
String bookname[] = {"斗破苍穹","三国演义","星辰变","画江湖之不良人","完美世界","遮天","西游记"};
String price[] = {"99","78","88","77","100","94","82"};
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("ID");
cell = row.createCell(1);//第一行第二例
cell.setCellValue("书籍名称");
cell = row.createCell(2);//第一行第三列
cell.setCellValue("统计时间");
cell = row.createCell(3);
cell.setCellValue("作者");
cell = row.createCell(4);
cell.setCellValue("价格");
for(int rowNum=0;rowNum<200000;rowNum++){
Random random = new Random();
int count = random.nextInt(6);
row = sheet.createRow(rowNum+1);
cell = row.createCell(0);
cell.setCellValue(UUID.randomUUID().toString());
cell = row.createCell(1);
cell.setCellValue(bookname[count]);
cell = row.createCell(2);
cell.setCellValue(time);
cell = row.createCell(3);
cell.setCellValue(author[count]);
cell = row.createCell(4);
cell.setCellValue(price[count]);
}
FileOutputStream fileOutputStream = new FileOutputStream(PATH+"20W海量数据导出07版本SXSSF.xlsx");
//吧工作簿写入到文件中
workbook.write(fileOutputStream);
fileOutputStream.close();
//清楚临时文件！！
((SXSSFWorkbook)workbook).dispose();
long endTime = System.currentTimeMillis();
System.out.println("完成@！！！！");
System.out.println("开始时间："+startTime);
System.out.println("结束时间："+endTime);
System.out.println("所需时间："+(double)(endTime-startTime)/1000);
}
424
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
