【简介】
目前操作excel比较流行的就是Apache POI和阿里的easyExcel,poi提供API给java程序对Microsoft Office格式格式档案读和写的功能。本篇文章主要说明poi实现excel的导入导出。
HSSF 操作读写03版的excel格式档案的功能;
03版本文件以.xls结尾;最多可以存储65536行数据;
- 缺点:最多只能处理65536行数据,否则会抛出异常java.lang.IllegalArgumentException:Invalid row number(65536) outside allowabke range(0..65535);
- 优点:过程中写入缓存,不操作磁盘,最后一次性写入磁盘,速度快;
XSSF 操作读写07版的excel格式档案的功能;
07版文件以.xlsx结尾;对行数无限制。
- 缺点:写数据速度慢,含内存,也会存在内存溢出OOM;
- 优点:可以写入较大的数据量。
大文件读写:SXSSF- 缺点:过程中会产生临时文件,需要清理临时文件。((SXSSFWorkbook)workbook).dispose();//在关闭流之后清楚临时文件。
- 有点:可以写入非常大的数据量,写入速度非常快,占用更少的内存。
- 原理:默认100条记录被保存到内存中,如果超过100条数据,则最前面的数据被写入临时文件中。如果自动逸内存中的数量,可以说使用new SXSSFWorkbook(数量)。
【poi】导入依赖
<!--xls(03)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!--xlsx(07)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
【poi】写数据
@Test
public void ExportExcel03() {
//创建工作簿
Workbook workbook=new HSSFWorkbook();
//创建工作表
Sheet sheet=workbook.createSheet();
//创建,0代表第一行
Row title=sheet.createRow(0);
//创建列
Cell cell0=title.createCell(0);
Cell cell1=title.createCell(1);
Cell cell2=title.createCell(2);
Cell cell3=title.createCell(3);
Cell cell4=title.createCell(4);
cell0.setCellValue("姓名");
cell1.setCellValue("出生年月日");
cell2.setCellValue("科目");
cell3.setCellValue("成绩");
cell4.setCellValue("排名");
int rowNum=10;
int cellNum=5;
for(int i=1;i<rowNum;i++){
Row row=sheet.createRow(i);
for(int j=0;j<cellNum;j++){
Cell cell=row.createCell(j);
cell.setCellValue("测试"+i+":"+(j+1));
}
}
FileOutputStream fos= null;
try {
fos = new FileOutputStream("C:\\Users\\lijiao8\\Desktop\\导出03.xls");
workbook.write(fos);
fos.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void ExportExcel07() {
//创建工作簿
Workbook workbook=new XSSFWorkbook();
//创建工作表
Sheet sheet=workbook.createSheet();
//创建,0代表第一行
Row title=sheet.createRow(0);
//创建列
Cell cell0=title.createCell(0);
Cell cell1=title.createCell(1);
Cell cell2=title.createCell(2);
Cell cell3=title.createCell(3);
Cell cell4=title.createCell(4);
cell0.setCellValue("姓名");
cell1.setCellValue("出生年月日");
cell2.setCellValue("科目");
cell3.setCellValue("成绩");
cell4.setCellValue("排名");
int rowNum=10;
int cellNum=5;
for(int i=1;i<rowNum;i++){
Row row=sheet.createRow(i);
for(int j=0;j<cellNum;j++){
Cell cell=row.createCell(j);
cell.setCellValue("测试"+i+":"+(j+1));
}
}
FileOutputStream fos= null;
try {
fos = new FileOutputStream("C:\\Users\\lijiao8\\Desktop\\导出07.xlsx");
workbook.write(fos);
fos.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
【poi】读数据(该部分就不将03版本和07版本分开展示了)
读取的excel文件内容:
首先创建一个对象,方便导出,
import java.util.Date;
/**
* @author lijiao
* @create 2021-02-08 14:19
*/
public class Student {
private String name;
private String birthDay;
private String course;
private Integer score;
private Integer ranking;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getBirthDay() {
return birthDay;
}
public void setBirthDay(String birthDay) {
this.birthDay = birthDay;
}
public String getCourse() {
return course;
}
public void setCourse(String course) {
this.course = course;
}
public Integer getScore() {
return score;
}
public void setScore(Integer score) {
this.score = score;
}
public Integer getRanking() {
return ranking;
}
public void setRanking(Integer ranking) {
this.ranking = ranking;
}
@Override
public String toString() {
return "Student{" +
"name='" + name + '\'' +
", birthDay='" + birthDay + '\'' +
", course='" + course + '\'' +
", score=" + score +
", ranking=" + ranking +
'}';
}
}
导入测试:
@Test
public void importExcel03() {
try {
List<Student> list = new ArrayList<Student>();
int limit = 100;
InputStream in = new FileInputStream("C:\\Users\\Desktop\\测试.xls");
//07版本
//InputStream in = new FileInputStream("C:\\Users\\Desktop\\测试.xlsx");
//Workbook workbook = new XSSFWorkbook(in);
Workbook workbook = new HSSFWorkbook(in);
Sheet sheet = workbook.getSheetAt(0);
int rowCount = sheet.getPhysicalNumberOfRows();
//跳过标题
for (int i = 1; i < rowCount; i++) {
Row row = sheet.getRow(i);
if (row != null) {
int cellCount = row.getPhysicalNumberOfCells();
Student stu = new Student();
Cell cell = row.getCell(0);
if (cell != null) {
String name = cell.getStringCellValue();
stu.setName(name);
}
Cell cell1 = row.getCell(1);
if (cell1 != null) {
Date date = cell1.getDateCellValue();
String birthDay = new DateTime(date).toString("yyyy-MM-dd");
stu.setBirthDay(birthDay);
}
Cell cell2 = row.getCell(2);
if (cell2 != null) {
String course = cell2.getStringCellValue();
stu.setCourse(course);
}
Cell cell3 = row.getCell(3);
if (cell3 != null) {
int score = (int) cell3.getNumericCellValue();
stu.setScore(score);
}
Cell cell4 = row.getCell(4);
if (cell4 != null) {
int rank = (int) cell4.getNumericCellValue();
stu.setRanking(rank);
}
System.out.println(stu.toString());
list.add(stu);
}
if (list.size() > limit) {
//提交代码至数据库
}
}
in.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
控制台输出:
Student{name='张三', birthDay='2000-12-12', course='英语', score=200, ranking=2}
Student{name='李四', birthDay='1998-10-23', course='俄语', score=198, ranking=4}
Student{name='王五', birthDay='1997-12-01', course='韩语', score=199, ranking=3}
Student{name='赵六', birthDay='1998-10-01', course='日语', score=201, ranking=1}