Java POI读取Office excel (2003,2007)及相关jar包

 

poi-3.7-20101029.jar，下载地址：http://download.csdn.net/detail/evangel_z/4107089

geronimo-stax-api_1.0_spec-1.0.jar，下载地址：http://download.csdn.net/detail/evangel_z/4107083

xmlbeans-2.3.0.jar，下载地址：http://download.csdn.net/detail/evangel_z/4107140

poi-ooxml-3.7-20101029.jar，下载地址：http://download.csdn.net/detail/evangel_z/4107145

poi-ooxml-schemas-3.7-20101029.jar，下载地址：http://download.csdn.net/detail/evangel_z/4108997

----------------------------------------------------------------------------------------------------------------------------------------

poi-3.6-20091214.jar，下载地址：http://download.csdn.net/detail/evangel_z/3895051

poi-contrib-3.6-20091214.jar，下载地址： http://download.csdn.net/detail/evangel_z/4107197

poi-scratch.6-20091214.jar，下载地址：http://download.csdn.net/detail/evangel_z/4107204

读取excel 文件的 java 代码：

[java] view plain copy print ?

1. import java.io.File; 
2. import java.io.FileInputStream; 
3. import java.io.FileNotFoundException; 
4. import java.io.IOException; 
5. import java.text.DecimalFormat; 
6. import java.text.SimpleDateFormat; 
7. import java.util.LinkedList; 
8. import java.util.List; 
9. import org.apache.poi.hssf.usermodel.HSSFCell; 
10. import org.apache.poi.hssf.usermodel.HSSFDateUtil; 
11. import org.apache.poi.hssf.usermodel.HSSFRow; 
12. import org.apache.poi.hssf.usermodel.HSSFSheet; 
13. import org.apache.poi.hssf.usermodel.HSSFWorkbook; 
14. import org.apache.poi.xssf.usermodel.XSSFCell; 
15. import org.apache.poi.xssf.usermodel.XSSFRow; 
16. import org.apache.poi.xssf.usermodel.XSSFSheet; 
17. import org.apache.poi.xssf.usermodel.XSSFWorkbook; 
18. public class ReadExcel { 
19.      /**
20.      * 对外提供读取excel 的方法
21.      * */ 
22. public static List<List<Object>> readExcel(File file) throws IOException{ 
23.    String fileName = file.getName(); 
24.    String extension = fileName.lastIndexOf(".")==-1?"":fileName.substring(fileName.lastIndexOf(".")+1); 
25.    if("xls".equals(extension)){ 
26.     return read2003Excel(file); 
27.    }else if("xlsx".equals(extension)){ 
28.     return read2007Excel(file); 
29.    }else{ 
30.     throw new IOException("不支持的文件类型"); 
31.    } 
32. } 
33. /**
34. * 读取 office 2003 excel
35. * @throws IOException
36. * @throws FileNotFoundException */ 
37. private static List<List<Object>> read2003Excel(File file) throws IOException{ 
38.    List<List<Object>> list = new LinkedList<List<Object>>(); 
39.    HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file)); 
40.    HSSFSheet sheet = hwb.getSheetAt(0); 
41.    Object value = null; 
42.    HSSFRow row = null; 
43.    HSSFCell cell = null;  
44.    for(int i = sheet.getFirstRowNum();i<= sheet.getPhysicalNumberOfRows();i++){ 
45.     row = sheet.getRow(i); 
46.     if (row == null) { 
47.      continue; 
48.     } 
49.     List<Object> linked = new LinkedList<Object>(); 
50.     for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { 
51.      cell = row.getCell(j); 
52.      if (cell == null) { 
53.       continue; 
54.      } 
55.      DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符 
56.      SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串 
57.      DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字 
58.      switch (cell.getCellType()) { 
59.      case XSSFCell.CELL_TYPE_STRING: 
60.       System.out.println(i+"行"+j+" 列 is String type"); 
61.       value = cell.getStringCellValue(); 
62.       break; 
63.      case XSSFCell.CELL_TYPE_NUMERIC: 
64.       System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString()); 
65.       if("@".equals(cell.getCellStyle().getDataFormatString())){ 
66.          value = df.format(cell.getNumericCellValue()); 
67.       } else if("General".equals(cell.getCellStyle().getDataFormatString())){ 
68.          value = nf.format(cell.getNumericCellValue()); 
69.       }else{ 
70.         value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); 
71.       } 
72.       break; 
73.      case XSSFCell.CELL_TYPE_BOOLEAN: 
74.       System.out.println(i+"行"+j+" 列 is Boolean type"); 
75.       value = cell.getBooleanCellValue(); 
76.       break; 
77.      case XSSFCell.CELL_TYPE_BLANK: 
78.       System.out.println(i+"行"+j+" 列 is Blank type"); 
79.       value = ""; 
80.       break; 
81.      default: 
82.       System.out.println(i+"行"+j+" 列 is default type"); 
83.       value = cell.toString(); 
84.      } 
85.      if (value == null || "".equals(value)) { 
86.       continue; 
87.      } 
88.      linked.add(value);   
89.    } 
90.     list.add(linked); 
91.    } 
92.    return list; 
93. } 
94. /**
95. * 读取Office 2007 excel
96. * */ 
97. private static List<List<Object>> read2007Excel(File file) throws IOException { 
98.    List<List<Object>> list = new LinkedList<List<Object>>(); 
99.    // 构造 XSSFWorkbook 对象，strPath 传入文件路径 
100.    XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file)); 
101.    // 读取第一章表格内容 
102.    XSSFSheet sheet = xwb.getSheetAt(0); 
103.    Object value = null; 
104.    XSSFRow row = null; 
105.    XSSFCell cell = null; 
106.    for (int i = sheet.getFirstRowNum(); i <= sheet 
107.      .getPhysicalNumberOfRows(); i++) { 
108.     row = sheet.getRow(i); 
109.     if (row == null) { 
110.      continue; 
111.     } 
112.     List<Object> linked = new LinkedList<Object>(); 
113.     for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { 
114.      cell = row.getCell(j); 
115.      if (cell == null) { 
116.       continue; 
117.      } 
118.      DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符 
119.      SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串 
120.      DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字 
121.      switch (cell.getCellType()) { 
122.      case XSSFCell.CELL_TYPE_STRING: 
123.       System.out.println(i+"行"+j+" 列 is String type"); 
124.       value = cell.getStringCellValue(); 
125.       break; 
126.      case XSSFCell.CELL_TYPE_NUMERIC: 
127.       System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString()); 
128.       if("@".equals(cell.getCellStyle().getDataFormatString())){ 
129.         value = df.format(cell.getNumericCellValue()); 
130.       } else if("General".equals(cell.getCellStyle().getDataFormatString())){ 
131.         value = nf.format(cell.getNumericCellValue()); 
132.       }else{ 
133.        value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); 
134.       } 
135.       break; 
136.      case XSSFCell.CELL_TYPE_BOOLEAN: 
137.       System.out.println(i+"行"+j+" 列 is Boolean type"); 
138.       value = cell.getBooleanCellValue(); 
139.       break; 
140.      case XSSFCell.CELL_TYPE_BLANK: 
141.       System.out.println(i+"行"+j+" 列 is Blank type"); 
142.       value = ""; 
143.       break; 
144.      default: 
145.       System.out.println(i+"行"+j+" 列 is default type"); 
146.       value = cell.toString(); 
147.      } 
148.      if (value == null || "".equals(value)) { 
149.       continue; 
150.      } 
151.      linked.add(value); 
152.     } 
153.     list.add(linked); 
154.    } 
155.    return list; 
156. } 
157. }