POI
Apache 公司發(fā)布的，可以使用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>
簡(jiǎn)單示例
- 創(chuàng)建Excel
//創(chuàng)建Excel文檔
HSSFWorkbook workbook = new HSSFWorkbook();
//創(chuàng)建sheet頁
HSSFSheet firstSheet = workbook.createSheet("sheet1");
//創(chuàng)建第一行 通常第一行作為 數(shù)據(jù)表頭
HSSFRow row = firstSheet.createRow(0);
//設(shè)置 第一行的列數(shù)據(jù)
String [] titles = new String[]{"序號(hào)","姓名","性別"};
for(int i=0; i<titles.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(titles[i]);
}
//插入1000條測(cè)試數(shù)據(jù)
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("測(cè)試人員"+i);
HSSFCell cell2 = rowData.createCell(2);
cell2.setCellValue(i%2==0?"男":"女");
}
//創(chuàng)建文檔 寫入數(shù)據(jù)
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 {
//創(chuàng)建讀取 的工作簿
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 行的數(shù)據(jù)
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
阿里 對(duì)POI底層重寫添怔，能夠原本一個(gè)3M的excel用POI sax依然需要100M左右內(nèi)存降低到幾M，并且再大的excel不會(huì)出現(xiàn)內(nèi)存溢出贤旷。在上層做了模型轉(zhuǎn)換的封裝广料，讓使用者更加簡(jiǎn)單方便。
maven依賴
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
簡(jiǎn)單示例
- 創(chuàng)建實(shí)體
@Data
public class People {
@ExcelProperty("序號(hào)")
private String number;
@ExcelProperty("姓名")
private String name;
@ExcelProperty("性別")
private String sex;
}
- 創(chuàng)建Excel
List<People> peoples = new ArrayList<>();
for(int i=1;i<=1000;i++){
People people = new People();
people.setNumber(String.valueOf(i));
people.setName("測(cè)試人員"+i);
people.setSex(i%2==0?"男":"女");
peoples.add(people);
}
String excelPath = "E://test.xlsx";
/**
* 1.創(chuàng)建Excel
* 2.寫入數(shù)據(jù)
* 3.自動(dòng)關(guān)閉流
*/
EasyExcel.write(excelPath, People.class).sheet("sheet1").doWrite(peoples);
- 讀取Excel
1.創(chuàng)建監(jiān)聽類
public class PeopleListener extends AnalysisEventListener<People> {
@Override
public void invoke(People people, AnalysisContext analysisContext) {
System.out.println(people.getNumber()+"||"+people.getName()+"||"+people.getSex());
}
/**
* 所有數(shù)據(jù)解析完成了 調(diào)用
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("數(shù)據(jù)全部讀取完成幼驶！");
}
}
2.讀取Excel
String excelPath = "E://test.xlsx";
/**
* 1.指定路徑
* 2.創(chuàng)建監(jiān)聽類 編寫處理邏輯
* 3.自動(dòng)關(guān)閉流
*/
EasyExcel.read(excelPath, People.class, new PeopleListener()).sheet(0).doRead();
Hutool
Hutool是一個(gè)小而全的java工具類庫艾杏，有很多實(shí)用的工具類封裝，Excel相關(guān)操作就是其中的工具類盅藻。
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>
簡(jiǎn)單示例
- 創(chuàng)建實(shí)體
@Data
public class People {
private String number;
private String name;
private String sex;
}
- 創(chuàng)建Excel
List<People> peoples = new ArrayList<>();
for(int i=1;i<=1000;i++){
People people = new People();
people.setNumber(String.valueOf(i));
people.setName("測(cè)試人員"+i);
people.setSex(i%2==0?"男":"女");
peoples.add(people);
}
//獲取 Excel 寫入的操作類
ExcelWriter excelWriter = ExcelUtil.getWriter();
//設(shè)置表頭 與 實(shí)體類的 屬性綁定
excelWriter.addHeaderAlias("number","序號(hào)");
excelWriter.addHeaderAlias("name","姓名");
excelWriter.addHeaderAlias("sex","性別");
//寫入全部?jī)?nèi)容
excelWriter.write(peoples,true);
//創(chuàng)建文檔 寫入數(shù)據(jù)
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列表购桑，默認(rèn)第一行為表頭，Map中的key為表頭值氏淑，value為標(biāo)題對(duì)應(yīng)的單元格值
List<Map<String, Object>> peoples = reader.readAll();
for (Map<String, Object> people:peoples){
System.out.println(people.get("序號(hào)")+"||"+people.get("姓名")+"||"+people.get("性別"));
}
小結(jié)
- 如果操作Excel復(fù)雜度高勃蜘，建議使用POI，編程靈活假残。
- 如果操作Excel數(shù)據(jù)量大缭贡，對(duì)性能有一定要求的情況，建議使用EasyExcel。
- 如果操作Excel數(shù)據(jù)量小阳惹，而且追求編程效率谍失，建議使用Hutool的ExcelUtil。
