Excel的导入及导出
作用：
- 大量数据的导入以及导出
- 将Excel表中的数据录入网站上（习题上传…）数据库回填
Apache POI
问题：将100w数据先加载到内存中（OOM内存溢出），再写入文件
EasyExcel
EasyExcel是一行一行读取，POI是直接读取全部内容
Excel单行数据导入
- 导入依赖
<!--xls(03版)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!--xlsx(07版)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!--日期格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.6</version>
</dependency>
<!--单元测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>compile</scope>
</dependency>
注意：两个版本都是3.17，一样的才行，不然07.xlsx就会报错
- 存储路径
// 文件存储路径
static String PATH ="C:\\Users\\XXX\\Excel\\";
- Excel（03）导入
/**
* o3版本Excel的导出
*/
public static void Xls03() throws Exception {
// 1、创建一个工作薄
Workbook workbook = new HSSFWorkbook();
// 2、创建一个工作表
Sheet sheet1 = workbook.createSheet("廖述幸03学习表");
// 3、创建第一行
Row row1 = sheet1.createRow(0);
// 4、创建一个单元格 (1,1坐标的单元格)
Cell cell11 = row1.createCell(0);
// 5、写入一个数据
cell11.setCellValue("今日新增学习任务");
// (1,2坐标的单元格)
Cell cell12 = row1.createCell(1);
cell12.setCellValue("2个");
// 6、创建第一行
Row row2 = sheet1.createRow(1);
Cell cell21 = row2.createCell(0);
Cell cell22 = row2.createCell(1);
cell21.setCellValue("统计时间");
// 用到joda-time的依赖
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(time);
// 7、生成一张Excel表（03版本 xls）
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "o3.xls");
// 8、写出xls文件
workbook.write(fileOutputStream);
// 9、关闭流
fileOutputStream.close();
System.out.println("Excel(03版)生成完毕...");
}
- Excel（07）导入
/**
* o7版本
*/
public static void Xls07() throws Exception {
// 1、创建一个工作薄
Workbook workbook = new XSSFWorkbook();
// 2、创建一个工作表
Sheet sheet1 = workbook.createSheet("廖述幸07学习表");
// 3、创建第一行
Row row1 = sheet1.createRow(0);
// 4、创建一个单元格 (1,1坐标的单元格)
Cell cell11 = row1.createCell(0);
// 5、写入一个数据
cell11.setCellValue("今日新增学习任务");
// (1,2坐标的单元格)
Cell cell12 = row1.createCell(1);
cell12.setCellValue("统计时间");
// 6、创建第一行
Row row2 = sheet1.createRow(1);
Cell cell21 = row2.createCell(0);
Cell cell22 = row2.createCell(1);
cell21.setCellValue("2个");
// 用到joda-time的依赖
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(time);
// 7、生成一张Excel表（03版本 xls）
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "o7.xlsx");
// 8、写出xls文件
workbook.write(fileOutputStream);
// 9、关闭流
fileOutputStream.close();
System.out.println("Excel(07版)生成完毕...");
}
Excel大数据导入
大文件写HSSF（03版）
缺点：最多只能处理65536行，否则会抛出异常
java.lang.IllegalArgumentException:Invallid row number (65536) outside allowable range (0..65536)
优点：过程写入缓存，不操作磁盘，最后一次性写入磁盘，速度快
大文件写XSSF（07）
缺点：写数据时候非常慢，非常消耗内存，也会发生内存溢出，入100w条数据
java.lang.OutOfMemoryError: Java heap space
优点：可写较大的数据量，如：20万条
大文件写SXSSF
优点：可以写非常庞大的数据
注意：
过程中会产生临时文件，需要清理临时文件
- Excel（03）大数据导入 HSSFWorkbook
public static void Excel03BigData() throws Exception {
// 计算开始--->结束的时间差
long begin = System.currentTimeMillis();
// 创建薄
Workbook workbook = new HSSFWorkbook();
// 创建表
Sheet sheet = workbook.createSheet();
// 写入数据
for (int rowNum = 0; rowNum < 65536 ; rowNum++) {
// 循环生成65536行
Row row = sheet.createRow(rowNum);
for (int cellNum=0;cellNum <10;cellNum++){
// 循环生成10列
Cell cell = row.createCell(cellNum);
// 添加列的值
cell.setCellValue(cellNum);
}
}
// 将这个表写出
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "BigData03.xls");
workbook.write(fileOutputStream);
// 关闭流
fileOutputStream.close();
System.out.println("大数据Excel03版导入成功...");
long end = System.currentTimeMillis();
System.out.println("耗费的时间--->"+(double)(end-begin)/1000);
}
- Excel（07）导入 XSSFWorkbook
public static void Excel07BigData() throws Exception {
// 计算开始--->结束的时间差
long begin = System.currentTimeMillis();
// 创建薄
Workbook workbook = new XSSFWorkbook();
// 创建表
Sheet sheet = workbook.createSheet();
// 写入数据
for (int rowNum = 0; rowNum < 65536 ; rowNum++) {
// 循环生成65536行
Row row = sheet.createRow(rowNum);
for (int cellNum=0;cellNum <10;cellNum++){
// 循环生成10列
Cell cell = row.createCell(cellNum);
// 添加列的值
cell.setCellValue(cellNum);
}
}
// 将这个表写出
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "BigData07.xlsx");
workbook.write(fileOutputStream);
// 关闭流
fileOutputStream.close();
System.out.println("大数据Excel07版导入成功...");
long end = System.currentTimeMillis();
System.out.println("耗费的时间--->"+(double)(end-begin)/1000);
}
java.lang.OutOfMemoryError 内存溢出OOM问题
- Excel（07）升级版 SXSSFWorkbook
public static void Excel07BigDataVip() throws Exception {
// 计算开始--->结束的时间差
long begin = System.currentTimeMillis();
// 创建薄 SXSSFWorkbook
Workbook workbook = new SXSSFWorkbook();
// 创建表
Sheet sheet = workbook.createSheet();
// 写入数据
for (int rowNum = 0; rowNum < 65536 ; rowNum++) {
// 循环生成65536行
Row row = sheet.createRow(rowNum);
for (int cellNum=0;cellNum <10;cellNum++){
// 循环生成10列
Cell cell = row.createCell(cellNum);
// 添加列的值
cell.setCellValue(cellNum);
}
}
// 将这个表写出
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "BigData07Vip.xlsx");
workbook.write(fileOutputStream);
// 关闭流
fileOutputStream.close();
// 清除临时文件
((SXSSFWorkbook)workbook).dispose();
System.out.println("大数据Excel07升级版导入成功...");
long end = System.currentTimeMillis();
System.out.println("耗费的时间--->"+(double)(end-begin)/1000);
}
Excel单行文件读取
// 文件读取路径
static String PATH ="C:\\Users\\XXX\\Excel\\";
public static void Xls03() throws Exception {
// 1、获取文件输入流
FileInputStream fileInputStream = new FileInputStream(PATH + "o3.xls");
// 2、创建一个工作薄
Workbook workbook = new HSSFWorkbook(fileInputStream);
// 3、得到表(通过下标获取表)
Sheet sheet = workbook.getSheetAt(0);
// 3.1、得到行
Row row1 = sheet.getRow(0);
Row row2 = sheet.getRow(1);
// 3.2 得到列
Cell cell11 = row1.getCell(0);// 1行1列
Cell cell12 = row1.getCell(1);// 1行2列
Cell cell21 = row2.getCell(0);// 2行1列
Cell cell22 = row2.getCell(1);// 2行2列
// 3.3、关闭流
fileInputStream.close();
// 4、输出(注意获取值的类型)
System.out.print(cell11.getStringCellValue()+"\t");
System.out.println(cell12.getStringCellValue());
System.out.print(cell21.getStringCellValue()+"\t");
System.out.println(cell22.getStringCellValue());
}
public static void main(String[] args) throws Exception {
Xls03();
}
07版本的.xlsx只需要同上面改成XSSFWorkbook即可
Excel数据遍历读取+数据类型判定
// 文件读取路径
static String PATH ="C:\\目录\\Excel\\";
public static void Xls03() throws Exception {
// 1、获取文件输入流
FileInputStream fileInputStream = new FileInputStream(PATH + "文件名.xls");
// 2、得到工作薄
Workbook workbook = new HSSFWorkbook(fileInputStream);
// 2.1、获取第一个表
Sheet sheet = workbook.getSheetAt(0);
// 3、获取第一行(标题)
Row rowTitle = sheet.getRow(0);
// 3.1、读取标题内容
if (rowTitle!=null){
// 3.2、获取这一行头标题数量
int cellNum = rowTitle.getPhysicalNumberOfCells();
// 3.3、循环打印头标题
System.out.print("|");
for (int i = 0; i < cellNum; i++) {
Cell cell = rowTitle.getCell(i);
// 3.4、判断内容不为空
if (cell!=null){
// 3.5、标题都是String类型的
System.out.print(cell.getStringCellValue());
System.out.print("|");
}
}
System.out.println();
System.out.println("=================================");
}
// 4、获取除头标题外所有行的内容
// 4.1、读取行
int rowNum = sheet.getPhysicalNumberOfRows();
for (int i = 1; i < rowNum; i++) {
Row rowData = sheet.getRow(i);
if (rowData!=null){
// 4.2、读取列
int cellNum = rowData.getPhysicalNumberOfCells();
for (int j = 0; j < cellNum; j++) {
Cell cell = rowData.getCell(j);
// 4.3、匹配列的数据类型
if (cell!=null){
CellType cellType = cell.getCellTypeEnum();
String cellValue = "";
switch (cellType){
case STRING://字符串类型
System.out.print("【String】");
cellValue = cell.getStringCellValue();
break;
case BOOLEAN:// boolean类型
System.out.print("【BOOLEAN】");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case NUMERIC:// 数字类型(日期、普通数字)
System.out.print("【NUMERIC】");
if (HSSFDateUtil.isCellDateFormatted(cell)){
System.out.print("【日期类型】");
// 日期的转换+格式显示
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
}else {
// 不是日期，防止数字过长，转化为String类型
cell.setCellType(CellType.STRING);
System.out.print("【数字】");
cellValue = cell.toString();
}
break;
case BLANK:// 空白
System.out.print("【BLANK】");
break;
case ERROR:// 错误
System.out.println("【数据类型错误】");
break;
}
System.out.println(cellValue);
}
}
}
}
// 关闭流
fileInputStream.close();
}
public static void main(String[] args) throws Exception {
Xls03();
}
EasyExcel的使用
官方文档：https://www.yuque.com/easyexcel/doc/easyexcel
- 导入依赖
<!--EasyExcel依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<!--单元测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>compile</scope>
</dependency>
<!--fastjson依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.73</version>
</dependency>
导入依赖
<!--EasyExcel依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<!--单元测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>compile</scope>
</dependency>
<!--fastjson依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.73</version>
</dependency>
EasyExcel依赖中包含了 POI和poi-ooxml，为了防止依赖冲突只导入EasyExcel的依赖就行
173
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
