Apache POI 是 Apache 软件基金会的开放源码函式库,POI 提供 API 给 Java 程序对 Microsoft Office 格式档案读和写的功能。
这里演示了 POI 对新版 Excel (.xlsx) 和 旧版 Excel (.xls) 两种格式文件的读写操作。
POI 下载处
需要导入的 jar
xmlbeans-2.6.0.jar (POI 完整包内的
curvesapi-1.03.jar
poi-3.14-20160307.jar
poi-excelant-3.14-20160307.jar
poi-ooxml-3.14-20160307.jar
poi-ooxml-schemas-3.14-20160307.jar
poi-scratchpad-3.14-20160307.jar
- import
java.io.FileInputStream; - import
java.io.FileOutputStream; - import
java.io.IOException; - import
java.io.InputStream; - import
java.util.Iterator; -
- import
org.apache.poi.hssf.usermodel.HSSFCell; - import
org.apache.poi.hssf.usermodel.HSSFRow; - import
org.apache.poi.hssf.usermodel.HSSFSheet; - import
org.apache.poi.hssf.usermodel.HSSFWorkbook; -
- import
org.apache.poi.xssf.usermodel.XSSFCell; - import
org.apache.poi.xssf.usermodel.XSSFRow; - import
org.apache.poi.xssf.usermodel.XSSFSheet; - import
org.apache.poi.xssf.usermodel.XSSFWorkbook; -
- public
class ReadWriteExcelFile - {
-
-
public static void readXLSFile() throws IOException -
{ -
InputStream ExcelFileToRead = new FileInputStream("C:/Test.xls"); -
HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead); -
-
HSSFSheet sheet = wb.getSheetAt(0); -
HSSFRow row; -
HSSFCell cell; -
-
Iterator rows = sheet.rowIterator(); -
-
while (rows.hasNext()) -
{ -
row = (HSSFRow) rows.next(); -
Iterator cells = row.cellIterator(); -
-
while (cells.hasNext()) -
{ -
cell = (HSSFCell) cells.next(); -
-
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) -
{ -
System.out.print(cell.getStringCellValue() + " "); -
} -
else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) -
{ -
System.out.print(cell.getNumericCellValue() + " "); -
} -
else -
{ -
// U Can Handel Boolean, Formula, Errors -
} -
} -
System.out.println(); -
} -
-
} -
-
public static void writeXLSFile() throws IOException -
{ -
-
String excelFileName = "C:/Test.xls";// name of excel file -
-
String sheetName = "Sheet1";// name of sheet -
-
HSSFWorkbook wb = new HSSFWorkbook(); -
HSSFSheet sheet = wb.createSheet(sheetName); -
-
// iterating r number of rows -
for (int r = 0; r < 5; r++) -
{ -
HSSFRow row = sheet.createRow(r); -
-
// iterating c number of columns -
for (int c = 0; c < 5; c++) -
{ -
HSSFCell cell = row.createCell(c); -
-
cell.setCellValue("Cell " + r + " " + c); -
} -
} -
-
FileOutputStream fileOut = new FileOutputStream(excelFileName); -
-
// write this workbook to an Outputstream. -
wb.write(fileOut); -
fileOut.flush(); -
fileOut.close(); -
} -
-
public static void readXLSXFile() throws IOException -
{ -
InputStream ExcelFileToRead = new FileInputStream("C:/Test.xlsx"); -
XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); -
-
XSSFWorkbook test = new XSSFWorkbook(); -
-
XSSFSheet sheet = wb.getSheetAt(0); -
XSSFRow row; -
XSSFCell cell; -
-
Iterator rows = sheet.rowIterator(); -
-
while (rows.hasNext()) -
{ -
row = (XSSFRow) rows.next(); -
Iterator cells = row.cellIterator(); -
while (cells.hasNext()) -
{ -
cell = (XSSFCell) cells.next(); -
-
if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) -
{ -
System.out.print(cell.getStringCellValue() + " "); -
} -
else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) -
{ -
System.out.print(cell.getNumericCellValue() + " "); -
} -
else -
{ -
// U Can Handel Boolean, Formula, Errors -
} -
} -
System.out.println(); -
} -
-
} -
-
public static void writeXLSXFile() throws IOException -
{ -
-
String excelFileName = "C:/Test.xlsx";// name of excel file -
-
String sheetName = "Sheet1";// name of sheet -
-
XSSFWorkbook wb = new XSSFWorkbook(); -
XSSFSheet sheet = wb.createSheet(sheetName); -
-
// iterating r number of rows -
for (int r = 0; r < 5; r++) -
{ -
XSSFRow row = sheet.createRow(r); -
-
// iterating c number of columns -
for (int c = 0; c < 5; c++) -
{ -
XSSFCell cell = row.createCell(c); -
-
cell.setCellValue("Cell " + r + " " + c); -
} -
} -
-
FileOutputStream fileOut = new FileOutputStream(excelFileName); -
-
// write this workbook to an Outputstream. -
wb.write(fileOut); -
fileOut.flush(); -
fileOut.close(); -
} -
-
public static void main(String[] args) throws IOException -
{ -
writeXLSFile(); -
readXLSFile(); -
-
writeXLSXFile(); -
readXLSXFile(); -
} - }
POI 所支持的文档格式
Component | Application type | Maven artifactId | Notes |
---|---|---|---|
POIFS | OLE2 Filesystem | poi | Required to work with OLE2 / POIFS based files |
HPSF | OLE2 Property Sets | poi | |
HSSF | Excel XLS | poi | For HSSF only, if common SS is needed see below |
HSLF | PowerPoint PPT | poi-scratchpad | |
HWPF | Word DOC | poi-scratchpad | |
HDGF | Visio VSD | poi-scratchpad | |
HPBF | Publisher PUB | poi-scratchpad | |
HSMF | Outlook MSG | poi-scratchpad | |
DDF | Escher common drawings | poi | |
HWMF | WMF drawings | poi-scratchpad | |
OpenXML4J | OOXML | poi-ooxml ooxml-schemas | See notes below for differences between these options |
XSSF | Excel XLSX | poi-ooxml | |
XSLF | PowerPoint PPTX | poi-ooxml | |
XWPF | Word DOCX | poi-ooxml | |
Common SL | PowerPoint PPT and PPTX | poi-scratchpad | SL code is in the core POI jar, but implementations are in poi-scratchpad and poi-ooxml. |
Common SS | Excel XLS and XLSX | poi-ooxml | WorkbookFactory and friends all require poi-ooxml, not just core poi |