Java使用POI读取Excel操作

桃扇骨 2022-06-09 12:06 329阅读 0赞

1.  **package** com.util;  
2.  **import** java.io.FileInputStream;  
3.  **import** java.io.FileNotFoundException;  
4.  **import** java.io.IOException;  
5.  **import** org.apache.commons.logging.Log;  
6.  **import** org.apache.commons.logging.LogFactory;  
7.  **import** org.apache.poi.hssf.usermodel.HSSFWorkbook;  
8.  **import** org.apache.poi.ss.usermodel.Cell;  
9.  **import** org.apache.poi.ss.usermodel.Row;  
10. **import** org.apache.poi.ss.usermodel.Sheet;  
11. **import** org.apache.poi.ss.usermodel.Workbook;  
12. **import** org.apache.poi.xssf.usermodel.XSSFWorkbook;  
13. **public** **class** TestExcel \{  
14.       //记录类的输出信息  
15.       **static** Log log = LogFactory.getLog(TestExcel.**class**);   
16.       //获取Excel文档的路径  
17.       **public** **static** String filePath = "D://excel.xls";  
18.       **public** **static** **void** main(String\[\] args) \{  
19.             **try** \{  
20.                   // 创建对Excel工作簿文件  
21. //HSSFWorkbook wookbook= new HSSFWorkbook(new FileInputStream(filePath));  
22.                   // 在Excel文档中，第一张工作表的缺省索引是0，  
23.       // 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);  
24.                 //  HSSFSheet sheet = wookbook.getSheet("Sheet1");  
25.                   Workbook book = **null**;  
26.                   **try** \{  
27.                       book = **new** XSSFWorkbook(filePath);  
28.                   \} **catch** (Exception ex) \{  
29.               book = **new** HSSFWorkbook(**new** FileInputStream(filePath));  
30.                   \}  
31.   
32.                   Sheet sheet = book.getSheet("Sheet1");  
33.                   //获取到Excel文件中的所有行数  
34.                   **int** rows = sheet.getPhysicalNumberOfRows();  
35.                   //遍历行  
36.                   **for** (**int** i = 0; i < rows; i++) \{  
37.                         // 读取左上端单元格  
38.                   Row row = sheet.getRow(i);  
39.                         // 行不为空  
40.                   **if** (row != **null**) \{  
41.                               //获取到Excel文件中的所有的列  
42.                     **int** cells = row.getPhysicalNumberOfCells();  
43.                               String value = "";       
44.                               //遍历列  
45.                               **for** (**int** j = 0; j < cells; j++) \{  
46.                                     //获取到列的值  
47.                                     Cell cell = row.getCell(j);  
48.                                     **if** (cell != **null**) \{  
49.                                           **switch** (cell.getCellType()) \{  
50.                                                 **case** Cell.CELL\_TYPE\_FORMULA:  
51.                                                 **break**;  
52.                                                 **case** Cell.CELL\_TYPE\_NUMERIC:  
53.                                     value += cell.getNumericCellValue() + ",";          
54.                                                 **break**;    
55.                                                 **case** Cell.CELL\_TYPE\_STRING:  
56.                                                value += cell.getStringCellValue() + ",";  
57.                                                 **break**;  
58.                                                 **default**:  
59.                                                       value += "0";  
60.                                                 **break**;  
61.                                     \}  
62.                               \}        
63.                         \}  
64.                         // 将数据插入到mysql数据库中  
65.                         String\[\] val = value.split(",");  
66.                         **for**(**int** j=0;j<val.length;j++)\{  
67.                           //每一行列数中的值遍历出来  
68.                         System.out.println(val\[j\]);  
69.                           \}  
70.                         
71.                       
72.                   \}  
73.              \}  
74.       \} **catch** (FileNotFoundException e) \{  
75.             e.printStackTrace();  
76.       \} **catch** (IOException e) \{  
77.             e.printStackTrace();  
78.       \}  
79.       \}  
80. \}

读取结果：

![Center][]

原来表格中的数据：

![Center 1][]

[Center]: /images/20220609/aee80199835a49548402f5f3b36335cf.png
[Center 1]: /images/20220609/554860f4029746848158554e1c50b620.png