Java之导入Excel 后端篇

1public void updateExcel(@RequestBody MultipartFile file) {
2try{
3    Workbook workbook = getWorkBook(file);//获取工作簿workbook 
4    Sheet sheetAt = workbook.getSheetAt(0);//根据工作簿获取整张excel表的信息　　for (int i=1; i<= sheetAt.getLastRowNum(); i++){//第一行是表头，所以不要，i从1开始
5
6　　　　for(int j=0; j < sheetAt.getRow(i).getLastCellNum(); j++){//循环每一行
7
8            Cell cell = sheetAt.getRow(i).getCell(j);//获取每一个单元格的值
9           String value = getValue(cell);//把单元格的值转成字符串
10            System.out.print(value+" ");
11    　　}
12    System.out.println();
13　　}
14
15}catch(Exception e){
16    e.printStackTrace();
17}
18}
19
20public static Workbook getWorkBook(MultipartFile file) {
21    //获得文件名
22    String fileName = file.getOriginalFilename();
23    //创建Workbook工作薄对象，表示整个excel
24    Workbook workbook = null;
25    Sheet sheet = null;
26    try {
27        //获取excel文件的io流
28        InputStream is = file.getInputStream();
29        //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
30        if(fileName.endsWith("xls")){
31            //2003
32            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(is);
33            workbook = new HSSFWorkbook(poifsFileSystem);
34            sheet = workbook.getSheetAt(0);
35        }else if(fileName.endsWith("xlsx")){
36            //2007 及2007以上
37            workbook = new XSSFWorkbook(is);
38            sheet = workbook.getSheetAt(0);
39        }
40    } catch (IOException e) {
41        e.printStackTrace();
42    }
43    return workbook;
44}
45
46
47public static String getValue(Cell cell){
48    if(cell.getCellTypeEnum() == org.apache.poi.ss.usermodel.CellType.BOOLEAN){
49        return String.valueOf(cell.getBooleanCellValue());
50    }else if(cell.getCellTypeEnum() == org.apache.poi.ss.usermodel.CellType.NUMERIC){
51        String value = "";
52        if (HSSFDateUtil.isCellDateFormatted(cell)) {
53            Date d = cell.getDateCellValue();
54            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
55            value = sdf.format(d);
56        }else{
57            double temp = cell.getNumericCellValue();
58            //value = new BigDecimal(temp).toString();
59            value = String.valueOf(temp);
60        }
61        return value;
62    }else if (cell.getCellTypeEnum() == org.apache.poi.ss.usermodel.CellType.STRING){
63        return String.valueOf(cell.getStringCellValue());
64    }else{
65        return String.valueOf(cell.getStringCellValue());
66    }
67}