通過 Excel 來認識神器 Apache POI
點擊上方?泥瓦匠 關(guān)注我！
1、POI是什么
Apache POI - the Java API for Microsoft Documents，顧名思義，Apache的三方包，用來操作微軟office文檔的，多數(shù)時候用來操作excel，所以這里就以excel方面來說明。
需要引入兩個包，maven地址如下（version 3.9）：
????org.apache.poi
????poi
????3.9
????org.apache.poi
????poi-ooxml
????3.9
POI的組件列表中，針對excel的主要是HSSF和XSSF組件，前者針對97-2007的通用版excel，即后綴xls；后者針對2007或更高版的excel，即后綴xlsx。
官方概要如下：
HSSF?is?the?POI?Project's?pure?Java?implementation?of?the?Excel?'97(-2007)?file?format.?
XSSF?is?the?POI?Project's?pure?Java?implementation?of?the?Excel?2007?OOXML?(.xlsx)?file?format.
2、POI核心類
面向?qū)ο竺嫦驅(qū)ο螅热蝗绱耍匀蝗フ艺乙恍┠鼙硎緀xcel中內(nèi)容的類。
2.1 工作簿 Workbook
創(chuàng)建或維護Excel工作簿的所有類的超接口，Workbook，屬于org.apache.poi.ss.usermodel包。
其下有兩個實現(xiàn)類：
HSSFWorkbook : 有讀取.xls 格式和寫入Microsoft Excel文件的方法。它與微軟Office97-2003版本兼容 XSSFWorkbook : 有讀寫Microsoft Excel和OpenOffice的XML文件的格式.xls或.xlsx的方法。它與MS-Office版本2007或更高版本兼容
所以在針對不同版本的excel時，需要對應(yīng)以上使用不同的Workbook。構(gòu)造函數(shù)中，常用的：
HSSFWorkbook
HSSFWorkbook()
HSSFWorkbook(java.io.InputStream?s)
XSSFWorkbook
XSSFWorkbook()
XSSFWorkbook(java.io.File?file)
XSSFWorkbook(java.io.InputStream?is)
2.2 標簽頁 Sheet
HSSFSheet?和?XSSFSheet?都是Sheet接口的實現(xiàn)類，Sheet可以使用Workbook的兩個方法獲得：
workbook.createSheet();
workbook.createSheet(String?sheetName);
2.3 行 Row
同理，Row是?HSSFRow?和?XSSFRow?的接口，通過Sheet獲取：
sheet.createRow(int?rownum);
2.4 單元格 Cell
同理，Cell是?HSSFCell?和?XSSFCell?的接口，通過Row獲取：
row.createCell(int?column);
row.createCell(int?column,?int?type);
3、創(chuàng)建和讀取
其實如果能理解面向?qū)ο螅秃芎唵瘟耍硗獍ㄗ煮w，公式，超鏈接等，都有對應(yīng)的封裝類，此處只提出了核心的幾個，需要了解更多的需要自行展開。
例子的話，直接從別人教程里摘出來吧，另，讀取的workbook，可以debug瞅瞅內(nèi)容。關(guān)注公從號Java技術(shù)棧，回復(fù)：工具，可以獲取更多工具系列干貨。
3.1 創(chuàng)建空白工作簿
import?java.io.*;
import?org.apache.poi.xssf.usermodel.*;
public?class?CreateWorkBook?
{
???public?static?void?main(String[]?args)throws?Exception?
???{
??????
??????XSSFWorkbook?workbook?=?new?XSSFWorkbook();?
??????
??????FileOutputStream?out?=?new?FileOutputStream(
??????new?File("createworkbook.xlsx"));
??????
??????workbook.write(out);
??????out.close();
??????System.out.println("
??????createworkbook.xlsx?written?successfully");
???}
}
3.2 打開現(xiàn)有的工作簿
import?java.io.*;
import?org.apache.poi.xssf.usermodel.*;
public?class?OpenWorkBook
{
???public?static?void?main(String?args[])throws?Exception
???{?
??????File?file?=?new?File("openworkbook.xlsx");
??????FileInputStream?fIP?=?new?FileInputStream(file);
??????
??????XSSFWorkbook?workbook?=?new?XSSFWorkbook(fIP);
??????if(file.isFile()?&&?file.exists())
??????{
?????????System.out.println(
?????????"openworkbook.xlsx?file?open?successfully.");
??????}
??????else
??????{
?????????System.out.println(
?????????"Error?to?open?openworkbook.xlsx?file.");
??????}
???}
}
3.3、任意對象List轉(zhuǎn)至為Excel文檔
可用注解定義標簽名和列名，寫了個方法，可以將某個類的List轉(zhuǎn)換為對應(yīng)的Excel文檔，列名如果在不使用注解的情況下默認為屬性名：
類：
@Excel(name?=?"學(xué)生標簽頁")
public?class?Student?{
????@Excel(name?=?"姓名")
????private?String?name;
????private?boolean?male;
????@Excel(name?=?"身高")
????private?int?height;
????public?String?getName()?{
????????return?name;
????}
????public?void?setName(String?name)?{
????????this.name?=?name;
????}
????public?boolean?isMale()?{
????????return?male;
????}
????public?void?setMale(boolean?male)?{
????????this.male?=?male;
????}
????public?int?getHeight()?{
????????return?height;
????}
????public?void?setHeight(int?height)?{
????????this.height?=?height;
????}
}
測試方法：
public?static?void?main(String[]?args)?{
????List?list?=?new?ArrayList();
????Student?student1?=?new?Student();
????student1.setName("小紅");
????student1.setMale(false);
????student1.setHeight(167);
????Student?student2?=?new?Student();
????student2.setName("小明");
????student2.setMale(true);
????student2.setHeight(185);
????list.add(student1);
????list.add(student2);
????File?file?=?new?File("C:/Users/Dulk/Desktop/1314.xls");
????createExcel(list,?file);
}
輸出結(jié)果：
注解：
import?java.lang.annotation.Retention;
import?java.lang.annotation.RetentionPolicy;
@Retention(RetentionPolicy.RUNTIME)
public?@interface?Excel?{
????
????public?String?name()?default?"";
}
方法：
import?org.apache.log4j.Logger;
import?org.apache.poi.hssf.usermodel.HSSFWorkbook;
import?org.apache.poi.ss.usermodel.Cell;
import?org.apache.poi.ss.usermodel.Row;
import?org.apache.poi.ss.usermodel.Sheet;
import?org.apache.poi.ss.usermodel.Workbook;
import?org.apache.poi.xssf.usermodel.XSSFWorkbook;
import?java.io.File;
import?java.io.FileInputStream;
import?java.io.FileNotFoundException;
import?java.io.FileOutputStream;
import?java.io.IOException;
import?java.io.InputStream;
import?java.io.OutputStream;
import?java.lang.reflect.Field;
import?java.lang.reflect.InvocationTargetException;
import?java.lang.reflect.Method;
import?java.util.ArrayList;
import?java.util.List;
public?class?ExcelUtil?{
????private?static?Logger?log?=?Logger.getLogger(ExcelUtil.class);
????
????public?static?Workbook?gainWorkbook(File?file)?throws?ExcelException?{
????????if?(!isExcel(file))?{
????????????throw?new?ExcelException("文件不是Excel類型");
????????}
????????
????????if?(!file.exists())?{
????????????try?{
????????????????OutputStream?os?=?new?FileOutputStream(file);
????????????????Workbook?workbook?=?isOlderEdition(file)???new?HSSFWorkbook()?:?new?XSSFWorkbook();
????????????????workbook.write(os);
????????????????log.debug("文件不存在，新建該Excel文件");
????????????????os.close();
????????????}?catch?(FileNotFoundException?e)?{
????????????????e.printStackTrace();
????????????}?catch?(IOException?e)?{
????????????????e.printStackTrace();
????????????}
????????}
????????try?{
????????????InputStream?is?=?new?FileInputStream(file);
????????????return?isOlderEdition(file)???new?HSSFWorkbook(is)?:?new?XSSFWorkbook(is);
????????}?catch?(FileNotFoundException?e)?{
????????????e.printStackTrace();
????????}?catch?(IOException?e)?{
????????????e.printStackTrace();
????????}
????????return?null;
????}
????
????private?static?boolean?isOlderEdition(File?file)?{
????????return?file.getName().matches(".+\\.(?i)xls");
????}
????
????private?static?boolean?isExcel(File?file)?{
????????String?fileName?=?file.getName();
????????String?regXls?=?".+\\.(?i)xls";
????????String?regXlsx?=?".+\\.(?i)xlsx";
????????return?fileName.matches(regXls)?||?fileName.matches(regXlsx);
????}
????
????public?static??Workbook?createExcel(List?list,?File?file)?{
????????String?sheetName?=?"default";
????????if?(list.size()?==?0)?{
????????????return?null;
????????}
????????Workbook?workbook?=?null;
????????try?{
????????????Class?clazz?=?list.get(0).getClass();
????????????Field[]?fields?=?clazz.getDeclaredFields();
????????????if?(clazz.isAnnotationPresent(Excel.class))?{
????????????????Excel?excel?=?(Excel)?clazz.getAnnotation(Excel.class);
????????????????sheetName?=?excel.name();
????????????}
????????????workbook?=?gainWorkbook(file);
????????????Sheet?sheet?=?workbook.createSheet(sheetName);
????????????
????????????Row?line?=?sheet.createRow(0);
????????????for?(int?k?=?0;?k?????????????????Cell?cell?=?line.createCell(k);
????????????????String?columnName?=?fields[k].getName();
????????????????if?(fields[k].isAnnotationPresent(Excel.class))?{
????????????????????Excel?excel?=?fields[k].getAnnotation(Excel.class);
????????????????????columnName?=?excel.name();
????????????????}
????????????????cell.setCellValue(columnName);
????????????}
????????????
????????????for?(int?i?=?1;?i?<=?list.size();?i++)?{
????????????????Row?row?=?sheet.createRow(i);
????????????????for?(int?j?=?1;?j?<=?fields.length;?j++)?{
????????????????????Cell?cell?=?row.createCell(j?-?1);
????????????????????String?fieldName?=?fields[j?-?1].getName();
????????????????????String?fieldFirstLetterUpper?=?fieldName.substring(0,?1).toUpperCase();
????????????????????String?prefix?=?"get";
????????????????????if?("boolean".equals(fields[j?-?1].getType().getName()))?{
????????????????????????prefix?=?"is";
????????????????????}
????????????????????String?methodName?=?prefix?+?fieldFirstLetterUpper?+?fieldName.substring(1);
????????????????????Method?method?=?clazz.getMethod(methodName);
????????????????????cell.setCellValue(String.valueOf(method.invoke(list.get(i?-?1))));
????????????????}
????????????}
????????????log.debug("List讀入完畢");
????????????OutputStream?os?=?new?FileOutputStream(file);
????????????workbook.write(os);
????????????os.close();
????????}?catch?(ExcelException?e)?{
????????????e.printStackTrace();
????????}?catch?(InvocationTargetException?e)?{
????????????e.printStackTrace();
????????}?catch?(NoSuchMethodException?e)?{
????????????e.printStackTrace();
????????}?catch?(IllegalAccessException?e)?{
????????????e.printStackTrace();
????????}?catch?(FileNotFoundException?e)?{
????????????e.printStackTrace();
????????}?catch?(IOException?e)?{
????????????e.printStackTrace();
????????}
????????return?workbook;
????}
} 往期推薦
下方二維碼關(guān)注我
技術(shù)草根，堅持分享?編程，算法，架構(gòu)
評論
圖片
表情