环境和所需包:
1,JDK1.5
2,poi-3.5-FINAL-20090928.jar,
poi-contrib-3.5-FINAL-20090928.jar,
poi-ooxml-3.5-FINAL-20090928.jar,
poi-scratchpad-3.5-FINAL-20090928.jar,
log4j-1.2.13.jar,
commons-logging-1.1.jar,
junit-3.8.1.jar,
dom4j-1.6.1.jar,
geronimo-stax-api_1.0_spec-1.0.jar,
ooxml-schemas-1.0.jar,
xmlbeans-2.3.0.jar
注意
1,可能有些包不需要,没有测试,因为有些包项目中已经存在了
2,我开始少了最后2个包,报:
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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 POIExcelDemo {
public static void read(String fileName) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook(fileName);
read(wb);
}
public static void read(InputStream is) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook(is);
read(wb);
}
public static void read(XSSFWorkbook xwb) throws Exception {
try {
for (int k = 0; k < xwb.getNumberOfSheets(); k++) {
XSSFSheet sheet = xwb.getSheetAt(k);
int rows = sheet.getPhysicalNumberOfRows();
for (int r = 0; r < rows; r++) {
// 定义 row
XSSFRow row = sheet.getRow(r);
if (row != null) {
int cells = row.getPhysicalNumberOfCells();
for (short c = 0; c < cells; c++) {
XSSFCell cell = row.getCell(c);
if (cell != null) {
String value = null;
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_FORMULA:
value = "FORMULA ";
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if(HSSFDateUtil.isCellDateFormatted(cell)){
value = "DATE value="
+ cell.getDateCellValue();
}else{
value = "NUMERIC value="
+ cell.getNumericCellValue();
}
break;
case XSSFCell.CELL_TYPE_STRING:
value = "STRING value="
+ cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
value = "BOOLEAN value="
+ cell.getBooleanCellValue();
cell.getDateCellValue();
break;
default:
}
System.out.println(value);
}
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
File f = new File("d:/Test.xlsx");
FileInputStream is = new FileInputStream(f);
System.out.println(f.getName());
read(is);
}
}
写完之后完把文件改成读取test.xls(Excel2003),发现出现Exception in thread "main" org.apache.poi.openxml4j.exceptions.InvalidOperationException: Can't open the specified file: 'C:\DOCUME~1\CHENXI~1\LOCALS~1\Temp\poifiles\poi-ooxml-1966473540.tmp'
at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:102)
所以为了兼容读取2003,我把代码给成如下:
1. import
2. import
3. import
4.
5. import
6. import
7. import
8. import
9. import
10. import
11. import
12. import
13.
14. public class
15.
16. /**
17. *
18. * @param fileName 文件路径
19. * @param flag 是2003还是2007 true:2003,false:2007
20. * @throws Exception
21. */
22. public static void read(String fileName,boolean flag) throws
23. null;
24. if(flag){//2003
25. new
26.
27. new
28. new
29. new
30. is.close();
31. else{//2007
32. new
33. }
34.
35. read(wb);
36. }
37.
38. /**
39. *
40. * @param is 输入流
41. * @param flag 是2003还是2007 true:2003,false:2007
42. * @throws Exception
43. */
44. public static void read(InputStream is,boolean flag) throws
45. null;
46.
47. if(flag){//2003
48. new
49. else{//2007
50. new
51. }
52.
53. read(wb);
54. }
55.
56. /**
57. * 具体读取Excel
58. * @param wb
59. * @throws Exception
60. */
61. public static void read(Workbook wb) throws
62. try
63.
64. for (int k = 0; k < wb.getNumberOfSheets(); k++) {
65.
66. //sheet
67. Sheet sheet = wb.getSheetAt(k);
68. int
69.
70. for (int r = 0; r < rows; r++) {
71. // 定义 row
72. Row row = sheet.getRow(r);
73. if (row != null) {
74. int
75.
76. for (short c = 0; c < cells; c++) {
77. Cell cell = row.getCell(c);
78. if (cell != null) {
79. null;
80.
81. switch
82.
83. case
84. "FORMULA value="
85. break;
86.
87. case
88. if(HSSFDateUtil.isCellDateFormatted(cell)){
89. "DATE value="
90. + cell.getDateCellValue();
91. else{
92. "NUMERIC value="
93. + cell.getNumericCellValue();
94. }
95.
96. break;
97.
98. case
99. "STRING value="
100. + cell.getStringCellValue();
101. break;
102.
103. case
104. "BOOLEAN value="
105. + cell.getBooleanCellValue();
106.
107.
108. cell.getDateCellValue();
109.
110. break;
111.
112. default:
113. }
114.
115. System.out.println(value);
116.
117. }
118. }
119. }
120. }
121. }
122. catch
123.
124. e.printStackTrace();
125. }
126.
127. }
128.
129. /**
130. * @param args
131. * @throws Exception
132. */
133. public static void main(String[] args) throws
134. // TODO Auto-generated method stub
135.
136. new File("d:/test.xlsx");
137.
138. new
139.
140. System.out.println(f.getName());
141.
142. false);
143.
144.
145. }
146.
147. }
标签:poi,openxml4j,value,cell,specified,exceptions,import,new,throws
From: https://blog.51cto.com/u_16174476/6647182