java中使用poi导入excel文件

1、ExcelUtil工具类

1.   
2. /** 
3.  * Excel工具类 
4.  * @author lp 
5.  * 
6.  */  
7. public class ExcelUtil {  
8.     public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";  
9.     public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";  
10.     public static final String EMPTY = "";  
11.     public static final String POINT = ".";  
12.     public static SimpleDateFormat sdf =   new SimpleDateFormat("yyyy/MM/dd");  
13.     /** 
14.      * 获得path的后缀名 
15.      * @param path 
16.      * @return 
17.      */  
18.     public static String getPostfix(String path){  
19.         if(path==null || EMPTY.equals(path.trim())){  
20.             return EMPTY;  
21.         }  
22.         if(path.contains(POINT)){  
23.             return path.substring(path.lastIndexOf(POINT)+1,path.length());  
24.         }  
25.         return EMPTY;  
26.     }  
27.     /** 
28.      * 单元格格式 
29.      * @param hssfCell 
30.      * @return 
31.      */  
32.     @SuppressWarnings({ "static-access", "deprecation" })  
33.     public static String getHValue(HSSFCell hssfCell){  
34.          if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {  
35.              return String.valueOf(hssfCell.getBooleanCellValue());  
36.          } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {  
37.              String cellValue = "";  
38.              if(HSSFDateUtil.isCellDateFormatted(hssfCell)){                  
39.                  Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue());  
40.                  cellValue = sdf.format(date);  
41.              }else{  
42.                  DecimalFormat df = new DecimalFormat("#.##");  
43.                  cellValue = df.format(hssfCell.getNumericCellValue());  
44.                  String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());  
45.                  if(strArr.equals("00")){  
46.                      cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));  
47.                  }    
48.              }  
49.              return cellValue;  
50.          } else {  
51.             return String.valueOf(hssfCell.getStringCellValue());  
52.          }  
53.     }  
54.     /** 
55.      * 单元格格式 
56.      * @param xssfCell 
57.      * @return 
58.      */  
59.     public static String getXValue(XSSFCell xssfCell){  
60.          if (xssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {  
61.              return String.valueOf(xssfCell.getBooleanCellValue());  
62.          } else if (xssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {  
63.              String cellValue = "";  
64.              if(XSSFDateUtil.isCellDateFormatted(xssfCell)){  
65.                  Date date = XSSFDateUtil.getJavaDate(xssfCell.getNumericCellValue());  
66.                  cellValue = sdf.format(date);  
67.              }else{  
68.                  DecimalFormat df = new DecimalFormat("#.##");  
69.                  cellValue = df.format(xssfCell.getNumericCellValue());  
70.                  String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());  
71.                  if(strArr.equals("00")){  
72.                      cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));  
73.                  }    
74.              }  
75.              return cellValue;  
76.          } else {  
77.             return String.valueOf(xssfCell.getStringCellValue());  
78.          }  
79.     }     
80. /** 
81.  * 自定义xssf日期工具类 
82.  * @author lp 
83.  * 
84.  */  
85. class XSSFDateUtil extends DateUtil{  
86.     protected static int absoluteDay(Calendar cal, boolean use1904windowing) {    
87.         return DateUtil.absoluteDay(cal, use1904windowing);    
88.     }   
89. }</span></span>  

2、ExcelRead：读取Excel类

[java] view plain copy print?

1. package com.ssm.util;  
2.   
3. import java.io.IOException;  
4. import java.io.InputStream;  
5. import java.util.ArrayList;  
6. import java.util.List;  
7.  
8. import org.springframework.web.multipart.MultipartFile;  
9. /** 
10.  * 读取Excel 
11.  * @author lp 
12.  * 
13.  */  
14. public class ExcelRead {      
15.     public int totalRows; //sheet中总行数  
16.     public static int totalCells; //每一行总单元格数  
17.     /** 
18.      * read the Excel .xlsx,.xls 
19.      * @param file jsp中的上传文件 
20.      * @return 
21.      * @throws IOException  
22.      */  
23.     public List<ArrayList<String>> readExcel(MultipartFile file) throws IOException {  
24.         if(file==null||ExcelUtil.EMPTY.equals(file.getOriginalFilename().trim())){  
25.             return null;  
26.         }else{  
27.             String postfix = ExcelUtil.getPostfix(file.getOriginalFilename());  
28.             if(!ExcelUtil.EMPTY.equals(postfix)){  
29.                 if(ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){  
30.                     return readXls(file);  
31.                 }else if(ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)){  
32.                     return readXlsx(file);  
33.                 }else{                    
34.                     return null;  
35.                 }  
36.             }  
37.         }  
38.         return null;  
39.     }  
40.     /** 
41.      * read the Excel 2010 .xlsx 
42.      * @param file 
43.      * @param beanclazz 
44.      * @param titleExist 
45.      * @return 
46.      * @throws IOException  
47.      */  
48.     @SuppressWarnings("deprecation")  
49.     public List<ArrayList<String>> readXlsx(MultipartFile file){  
50.         List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();  
51.         // IO流读取文件  
52.         InputStream input = null;  
53.         XSSFWorkbook wb = null;  
54.         ArrayList<String> rowList = null;  
55.         try {  
56.             input = file.getInputStream();  
57.             // 创建文档  
58.             wb = new XSSFWorkbook(input);                         
59.             //读取sheet(页)  
60.             for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){  
61.                 XSSFSheet xssfSheet = wb.getSheetAt(numSheet);  
62.                 if(xssfSheet == null){  
63.                     continue;  
64.                 }  
65.                 totalRows = xssfSheet.getLastRowNum();                
66.                 //读取Row,从第二行开始  
67.                 for(int rowNum = 1;rowNum <= totalRows;rowNum++){  
68.                     XSSFRow xssfRow = xssfSheet.getRow(rowNum);  
69.                     if(xssfRow!=null){  
70.                         rowList = new ArrayList<String>();  
71.                         totalCells = xssfRow.getLastCellNum();  
72.                         //读取列，从第一列开始  
73.                         for(int c=0;c<=totalCells+1;c++){  
74.                             XSSFCell cell = xssfRow.getCell(c);  
75.                             if(cell==null){  
76.                                 rowList.add(ExcelUtil.EMPTY);  
77.                                 continue;  
78.                             }                             
79.                             rowList.add(ExcelUtil.getXValue(cell).trim());  
80.                         }                                                 
81.                     }  
82.                 }  
83.             }  
84.             return list;  
85.         } catch (IOException e) {             
86.             e.printStackTrace();  
87.         } finally{  
88.             try {  
89.                 input.close();  
90.             } catch (IOException e) {  
91.                 e.printStackTrace();  
92.             }  
93.         }  
94.         return null;  
95.           
96.     }  
97.     /** 
98.      * read the Excel 2003-2007 .xls 
99.      * @param file 
100.      * @param beanclazz 
101.      * @param titleExist 
102.      * @return 
103.      * @throws IOException  
104.      */  
105.     public List<ArrayList<String>> readXls(MultipartFile file){   
106.         List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();  
107.         // IO流读取文件  
108.         InputStream input = null;  
109.         HSSFWorkbook wb = null;  
110.         ArrayList<String> rowList = null;  
111.         try {  
112.             input = file.getInputStream();  
113.             // 创建文档  
114.             wb = new HSSFWorkbook(input);                         
115.             //读取sheet(页)  
116.             for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){  
117.                 HSSFSheet hssfSheet = wb.getSheetAt(numSheet);  
118.                 if(hssfSheet == null){  
119.                     continue;  
120.                 }  
121.                 totalRows = hssfSheet.getLastRowNum();                
122.                 //读取Row,从第二行开始  
123.                 for(int rowNum = 1;rowNum <= totalRows;rowNum++){  
124.                     HSSFRow hssfRow = hssfSheet.getRow(rowNum);  
125.                     if(hssfRow!=null){  
126.                         rowList = new ArrayList<String>();  
127.                         totalCells = hssfRow.getLastCellNum();  
128.                         //读取列，从第一列开始  
129.                         for(short c=0;c<=totalCells+1;c++){  
130.                             HSSFCell cell = hssfRow.getCell(c);  
131.                             if(cell==null){  
132.                                 rowList.add(ExcelUtil.EMPTY);  
133.                                 continue;  
134.                             }                             
135.                             rowList.add(ExcelUtil.getHValue(cell).trim());  
136.                         }          
137.                         list.add(rowList);  
138.                     }                     
139.                 }  
140.             }  
141.             return list;  
142.         } catch (IOException e) {             
143.             e.printStackTrace();  
144.         } finally{  
145.             try {  
146.                 input.close();  
147.             } catch (IOException e) {  
148.                 e.printStackTrace();  
149.             }  
150.         }  
151.         return null;  
152.     }  
153. }  

3、Controller

    @ResponseBody
    @RequestMapping(value = "/import",method = RequestMethod.POST)
    public responseRusult importIndex(@RequestParam(name = "file",required = false)MultipartFile indexLabelFile) {
       ResponseRusult responseRusult = newResponseRusult ();
        try {
            if(indexLabelFile == null) {
               responseRusult.error("请选择要上传的文件");
                returnresponseRusult;
            }
            //读取Excel数据到List中
            List<ArrayList<String>> list = new ExcelRead().readExcel(file);
            indexService.indexImport(list);
           responseRusult.success("导入成功");
        } catch (Exception e) {
           responseRusult.error("导入失败");
        }
        returnresponseRusult;
    }

转载地址：点击打开链接