Java-Excel表数据转List对象->导入数据库

 1 import java.io.File;
 2 import java.io.FileInputStream;
 3 import java.util.ArrayList;
 4 import java.util.List;
 5 
 6 import org.apache.poi.hssf.usermodel.HSSFCell;
 7 import org.apache.poi.hssf.usermodel.HSSFRow;
 8 import org.apache.poi.hssf.usermodel.HSSFSheet;
 9 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
10 
11 
12 public class ObjectExcelRead {
13 
14     /**
15      * @param filepath //文件路径
16      * @param filename //文件名
17      * @param startrow //开始行号
18      * @param startcol //开始列号
19      * @param sheetnum //sheet
20      * @return list
21      */
22     public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
23         List<Object> varList = new ArrayList<Object>();
24 
25         try {
26             File target = new File(filepath, filename);
27             FileInputStream fi = new FileInputStream(target);
28             HSSFWorkbook wb = new HSSFWorkbook(fi);
29             
30             // sheet从0开始
31             HSSFSheet sheet = wb.getSheetAt(sheetnum);   
32             // 取得最后一行的行号
33             int rowNum = sheet.getLastRowNum() + 1;   
34             
35             // 行循环开始
36             for (int i = startrow; i < rowNum; i++) {                    
37                 PageData varpd = new PageData();
38                 //
39                 HSSFRow row = sheet.getRow(i); 
40                 // 每行的最后一个单元格位置
41                 int cellNum = row.getLastCellNum();                     
42                 
43                 //列循环开始
44                 for (int j = startcol; j < cellNum; j++) {                 
45                     HSSFCell cell = row.getCell(Short.parseShort(j + ""));
46                     String cellValue = null;
47                     
48                     if (null != cell) {
49                         // 判断excel单元格内容的格式,并对其进行转换,以便插入数据库
50                         switch (cell.getCellType()) {                      
51                         case 0:
52                             cellValue = String.valueOf((int) cell.getNumericCellValue());
53                             break;
54                             
55                         case 1:
56                             cellValue = cell.getStringCellValue();
57                             break;
58                             
59                         case 2:
60                             cellValue = cell.getNumericCellValue() + "";
61                             // cellValue = String.valueOf(cell.getDateCellValue());
62                             break;
63                             
64                         case 3:
65                             cellValue = "";
66                             break;
67                             
68                         case 4:
69                             cellValue = String.valueOf(cell.getBooleanCellValue());
70                             break;
71                             
72                         case 5:
73                             cellValue = String.valueOf(cell.getErrorCellValue());
74                             break;
75                             
76                         }
77                     } else {
78                         cellValue = "";
79                     }
80                     
81                     varpd.put("var"+j, cellValue);                    
82                 }
83                 varList.add(varpd);
84             }
85         } catch (Exception e) {
86             System.out.println(e);
87         }
88         
89         return varList;
90     }
91 }

 

posted @ 2022-01-05 14:38  静沐丶暖阳  阅读(424)  评论(0编辑  收藏  举报