POI
Apache 公司发布的，可以使用java语言操作Microsoft Office文件的开源Api。
maven依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
简单示例
- 创建Excel
//创建Excel文档
HSSFWorkbook workbook = new HSSFWorkbook();
//创建sheet页
HSSFSheet firstSheet = workbook.createSheet("sheet1");
//创建第一行 通常第一行作为 数据表头
HSSFRow row = firstSheet.createRow(0);
//设置 第一行的列数据
String [] titles = new String[]{"序号","姓名","性别"};
for(int i=0; i<titles.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(titles[i]);
}
//插入1000条测试数据
for(int i=1; i<=1000; i++){
HSSFRow rowData = firstSheet.createRow(i);
HSSFCell cell0 = rowData.createCell(0);
cell0.setCellValue(i);
HSSFCell cell1 = rowData.createCell(1);
cell1.setCellValue("测试人员"+i);
HSSFCell cell2 = rowData.createCell(2);
cell2.setCellValue(i%2==0?"男":"女");
}
//创建文档 写入数据
String excelPath = "E://test.xlsx";
try {
FileOutputStream stream = new FileOutputStream(excelPath);
workbook.write(stream);
stream.close();
} catch (IOException e) {
e.printStackTrace();
}
- 读取Excel
//读取文档的 地址
String excelPath = "E://test.xlsx";
InputStream inputStream = null;
try {
//创建读取 的工作簿
inputStream = new FileInputStream(excelPath);
POIFSFileSystem fs = new POIFSFileSystem(inputStream);
HSSFWorkbook workbook = new HSSFWorkbook(fs);
//获取要读取的sheet页
HSSFSheet sheet0 = workbook.getSheetAt(0);
//遍历sheet行 从第一行度取
for(int rowNum = 1; rowNum <= sheet0.getLastRowNum() ; rowNum++){
HSSFRow row = sheet0.getRow(rowNum);
//打印第 rowNum 行的数据
for(int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++){
System.out.print(row.getCell(cellNum)+"||");
}
System.out.println();
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
EasyExcel
阿里 对POI底层重写，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到几M，并且再大的excel不会出现内存溢出。在上层做了模型转换的封装，让使用者更加简单方便。
maven依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
简单示例
- 创建实体
@Data
public class People {
@ExcelProperty("序号")
private String number;
@ExcelProperty("姓名")
private String name;
@ExcelProperty("性别")
private String sex;
}
- 创建Excel
List<People> peoples = new ArrayList<>();
for(int i=1;i<=1000;i++){
People people = new People();
people.setNumber(String.valueOf(i));
people.setName("测试人员"+i);
people.setSex(i%2==0?"男":"女");
peoples.add(people);
}
String excelPath = "E://test.xlsx";
/**
* 1.创建Excel
* 2.写入数据
* 3.自动关闭流
*/
EasyExcel.write(excelPath, People.class).sheet("sheet1").doWrite(peoples);
- 读取Excel
1.创建监听类
public class PeopleListener extends AnalysisEventListener<People> {
@Override
public void invoke(People people, AnalysisContext analysisContext) {
System.out.println(people.getNumber()+"||"+people.getName()+"||"+people.getSex());
}
/**
* 所有数据解析完成了 调用
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("数据全部读取完成！");
}
}
2.读取Excel
String excelPath = "E://test.xlsx";
/**
* 1.指定路径
* 2.创建监听类 编写处理逻辑
* 3.自动关闭流
*/
EasyExcel.read(excelPath, People.class, new PeopleListener()).sheet(0).doRead();
Hutool
Hutool是一个小而全的java工具类库，有很多实用的工具类封装，Excel相关操作就是其中的工具类。
maven依赖
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.0.7</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
简单示例
- 创建实体
@Data
public class People {
private String number;
private String name;
private String sex;
}
- 创建Excel
List<People> peoples = new ArrayList<>();
for(int i=1;i<=1000;i++){
People people = new People();
people.setNumber(String.valueOf(i));
people.setName("测试人员"+i);
people.setSex(i%2==0?"男":"女");
peoples.add(people);
}
//获取 Excel 写入的操作类
ExcelWriter excelWriter = ExcelUtil.getWriter();
//设置表头 与 实体类的 属性绑定
excelWriter.addHeaderAlias("number","序号");
excelWriter.addHeaderAlias("name","姓名");
excelWriter.addHeaderAlias("sex","性别");
//写入全部内容
excelWriter.write(peoples,true);
//创建文档 写入数据
String excelPath = "E://test.xlsx";
try {
FileOutputStream stream = new FileOutputStream(excelPath);
excelWriter.flush(stream,true);
excelWriter.close();
} catch (IOException e) {
e.printStackTrace();
}
- 读取Excel
String excelPath = "E://test.xlsx";
ExcelReader reader = ExcelUtil.getReader(excelPath);
//读取为Map列表，默认第一行为表头，Map中的key为表头值，value为标题对应的单元格值
List<Map<String, Object>> peoples = reader.readAll();
for (Map<String, Object> people:peoples){
System.out.println(people.get("序号")+"||"+people.get("姓名")+"||"+people.get("性别"));
}
小结
- 如果操作Excel复杂度高，建议使用POI，编程灵活。
- 如果操作Excel数据量大，对性能有一定要求的情况，建议使用EasyExcel。
- 如果操作Excel数据量小，而且追求编程效率，建议使用Hutool的ExcelUtil。
