Apache POI 读写 E…

Apache POI 是 Apache 软件基金会的开放源码函式库，POI 提供 API 给 Java 程序对 Microsoft Office 格式档案读和写的功能。

这里演示了 POI 对新版 Excel (.xlsx) 和 旧版 Excel (.xls) 两种格式文件的读写操作。

POI 下载处 http://poi.apache.org/

需要导入的 jar

xmlbeans-2.6.0.jar （POI 完整包内的 ooxml-lib 目录内）

curvesapi-1.03.jar （POI 完整包内的 ooxml-lib 目录内）

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

 

[java]  view plain  copy

 

1. import java.io.FileInputStream;  
2. import java.io.FileOutputStream;  
3. import java.io.IOException;  
4. import java.io.InputStream;  
5. import java.util.Iterator;  
6.   
7. import org.apache.poi.hssf.usermodel.HSSFCell;  
8. import org.apache.poi.hssf.usermodel.HSSFRow;  
9. import org.apache.poi.hssf.usermodel.HSSFSheet;  
10. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
11.   
12. import org.apache.poi.xssf.usermodel.XSSFCell;  
13. import org.apache.poi.xssf.usermodel.XSSFRow;  
14. import org.apache.poi.xssf.usermodel.XSSFSheet;  
15. import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
16.   
17. public class ReadWriteExcelFile  
18. {  
19.   
20.   public static void readXLSFile() throws IOException  
21.   {  
22.     InputStream ExcelFileToRead = new FileInputStream("C:/Test.xls");  
23.     HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);  
24.   
25.     HSSFSheet sheet = wb.getSheetAt(0);  
26.     HSSFRow row;  
27.     HSSFCell cell;  
28.   
29.     Iterator rows = sheet.rowIterator();  
30.   
31.     while (rows.hasNext())  
32.     {  
33.       row = (HSSFRow) rows.next();  
34.       Iterator cells = row.cellIterator();  
35.   
36.       while (cells.hasNext())  
37.       {  
38.         cell = (HSSFCell) cells.next();  
39.   
40.         if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)  
41.         {  
42.           System.out.print(cell.getStringCellValue() + " ");  
43.         }  
44.         else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)  
45.         {  
46.           System.out.print(cell.getNumericCellValue() + " ");  
47.         }  
48.         else  
49.         {  
50.           // U Can Handel Boolean, Formula, Errors  
51.         }  
52.       }  
53.       System.out.println();  
54.     }  
55.   
56.   }  
57.   
58.   public static void writeXLSFile() throws IOException  
59.   {  
60.   
61.     String excelFileName = "C:/Test.xls";// name of excel file  
62.   
63.     String sheetName = "Sheet1";// name of sheet  
64.   
65.     HSSFWorkbook wb = new HSSFWorkbook();  
66.     HSSFSheet sheet = wb.createSheet(sheetName);  
67.   
68.     // iterating r number of rows  
69.     for (int r = 0; r < 5; r++)  
70.     {  
71.       HSSFRow row = sheet.createRow(r);  
72.   
73.       // iterating c number of columns  
74.       for (int c = 0; c < 5; c++)  
75.       {  
76.         HSSFCell cell = row.createCell(c);  
77.   
78.         cell.setCellValue("Cell " + r + " " + c);  
79.       }  
80.     }  
81.   
82.     FileOutputStream fileOut = new FileOutputStream(excelFileName);  
83.   
84.     // write this workbook to an Outputstream.  
85.     wb.write(fileOut);  
86.     fileOut.flush();  
87.     fileOut.close();  
88.   }  
89.   
90.   public static void readXLSXFile() throws IOException  
91.   {  
92.     InputStream ExcelFileToRead = new FileInputStream("C:/Test.xlsx");  
93.     XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);  
94.   
95.     XSSFWorkbook test = new XSSFWorkbook();  
96.   
97.     XSSFSheet sheet = wb.getSheetAt(0);  
98.     XSSFRow row;  
99.     XSSFCell cell;  
100.   
101.     Iterator rows = sheet.rowIterator();  
102.   
103.     while (rows.hasNext())  
104.     {  
105.       row = (XSSFRow) rows.next();  
106.       Iterator cells = row.cellIterator();  
107.       while (cells.hasNext())  
108.       {  
109.         cell = (XSSFCell) cells.next();  
110.   
111.         if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)  
112.         {  
113.           System.out.print(cell.getStringCellValue() + " ");  
114.         }  
115.         else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)  
116.         {  
117.           System.out.print(cell.getNumericCellValue() + " ");  
118.         }  
119.         else  
120.         {  
121.           // U Can Handel Boolean, Formula, Errors  
122.         }  
123.       }  
124.       System.out.println();  
125.     }  
126.   
127.   }  
128.   
129.   public static void writeXLSXFile() throws IOException  
130.   {  
131.   
132.     String excelFileName = "C:/Test.xlsx";// name of excel file  
133.   
134.     String sheetName = "Sheet1";// name of sheet  
135.   
136.     XSSFWorkbook wb = new XSSFWorkbook();  
137.     XSSFSheet sheet = wb.createSheet(sheetName);  
138.   
139.     // iterating r number of rows  
140.     for (int r = 0; r < 5; r++)  
141.     {  
142.       XSSFRow row = sheet.createRow(r);  
143.   
144.       // iterating c number of columns  
145.       for (int c = 0; c < 5; c++)  
146.       {  
147.         XSSFCell cell = row.createCell(c);  
148.   
149.         cell.setCellValue("Cell " + r + " " + c);  
150.       }  
151.     }  
152.   
153.     FileOutputStream fileOut = new FileOutputStream(excelFileName);  
154.   
155.     // write this workbook to an Outputstream.  
156.     wb.write(fileOut);  
157.     fileOut.flush();  
158.     fileOut.close();  
159.   }  
160.   
161.   public static void main(String[] args) throws IOException  
162.   {  
163.     writeXLSFile();  
164.     readXLSFile();  
165.   
166.     writeXLSXFile();  
167.     readXLSXFile();  
168.   }  
169. }  

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 plus either poi-ooxml-schemas or ooxml-schemas and ooxml-security	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 and poi-ooxml	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