java excel导入代码

1.导入poi.jar包（全部导入）

2.//产品价格Excel表导入

   @RequestMapping("addExcel.do")

   public StringinstallExcel(@RequestParam(value="file",required=false)MultipartFile file){

      try {

         String fname =file.getOriginalFilename();

         InputStream is = file.getInputStream();

         Workbook wb = null;

         if(fname.endsWith("xls")){

            wb = new HSSFWorkbook(is);

         }else if(fname.endsWith("xlsx")){

            wb = new XSSFWorkbook(is);

         }

         List<PriceModel> list = new ArrayList<PriceModel>();

         if(wb != null){

            for(int sheetNum = 0;sheetNum<wb.getNumberOfSheets();sheetNum++){

                Sheet sheet =wb.getSheetAt(sheetNum);

                if(sheet == null){

                   continue;

                }

                //获得当前sheet的开始行 

                int firstRowNum = sheet.getFirstRowNum();

                int lastRowNum = sheet.getLastRowNum();

               //行

                for(int rowNum=firstRowNum+1;rowNum<=lastRowNum;rowNum++){

                   Row row =sheet.getRow(rowNum);

                   if(row == null){

                      continue;

                   }

                   //获得当前行的开始列  

                   int firstCellNum = row.getFirstCellNum();

                   int lastCellNum = row.getLastCellNum();

                   PriceModel pm = new PriceModel();

                   String[] str = new String[row.getPhysicalNumberOfCells()];

                   //列

                   for(int cellNum=firstCellNum;cellNum<lastCellNum;cellNum++){

                      Cell cell = row.getCell(cellNum);

                      String cellValue = "";

                      if(cell.getCellType()==cell.CELL_TYPE_NUMERIC){

                         cell.setCellType(cell.CELL_TYPE_STRING);

                      }

                     

                      switch (cell.getCellType()) {

                         case Cell.CELL_TYPE_NUMERIC:

                            cellValue = String.valueOf(cell.getNumericCellValue());

                            break;

                         case Cell.CELL_TYPE_STRING:

                            cellValue = String.valueOf(cell.getStringCellValue());

                            break;

                         case Cell.CELL_TYPE_FORMULA:

                            cellValue = String.valueOf(cell.getCellFormula());

                            break;

                         case Cell.CELL_TYPE_BLANK:

                            cellValue = "";

                            break;

                         case Cell.CELL_TYPE_ERROR:

                            cellValue = "非法字符";

                            break;

                         default:

                            cellValue = "未知字符";

                            break;

                      }

                      str[cellNum] = cellValue;

                   }

                   pm.setSKU(Integer.parseInt(str[0]));

                   pm.setPrice1(Double.parseDouble(str[1]));

                   pm.setPrice2(Double.parseDouble(str[2]));

                   pm.setPrice3(Double.parseDouble(str[3]));

                   pm.setPrice4(Double.parseDouble(str[4]));

                   pm.setPrice5(Double.parseDouble(str[5]));

                   pm.setPrice6(Double.parseDouble(str[6]));

                   list.add(pm);

                }

                for(int i=0;i<list.size();i++){

                   priceModelBiz.addExcel(list.get(i));

                }

            }

         }

         return "redirect:mplist.do?pg=1";

      } catch (Exception e) {

         e.printStackTrace();

      }

      return null;

   }

 

1.导入poi.jar包（全部导入）

2.//产品价格Excel表导入

   @RequestMapping("addExcel.do")

   public StringinstallExcel(@RequestParam(value="file",required=false)MultipartFile file){

      try {

         String fname =file.getOriginalFilename();

         InputStream is = file.getInputStream();

         Workbook wb = null;

         if(fname.endsWith("xls")){

            wb = new HSSFWorkbook(is);

         }else if(fname.endsWith("xlsx")){

            wb = new XSSFWorkbook(is);

         }

         List<PriceModel> list = new ArrayList<PriceModel>();

         if(wb != null){

            for(int sheetNum = 0;sheetNum<wb.getNumberOfSheets();sheetNum++){

                Sheet sheet =wb.getSheetAt(sheetNum);

                if(sheet == null){

                   continue;

                }

                //获得当前sheet的开始行 

                int firstRowNum = sheet.getFirstRowNum();

                int lastRowNum = sheet.getLastRowNum();

               //行

                for(int rowNum=firstRowNum+1;rowNum<=lastRowNum;rowNum++){

                   Row row =sheet.getRow(rowNum);

                   if(row == null){

                      continue;

                   }

                   //获得当前行的开始列  

                   int firstCellNum = row.getFirstCellNum();

                   int lastCellNum = row.getLastCellNum();

                   PriceModel pm = new PriceModel();

                   String[] str = new String[row.getPhysicalNumberOfCells()];

                   //列

                   for(int cellNum=firstCellNum;cellNum<lastCellNum;cellNum++){

                      Cell cell = row.getCell(cellNum);

                      String cellValue = "";

                      if(cell.getCellType()==cell.CELL_TYPE_NUMERIC){

                         cell.setCellType(cell.CELL_TYPE_STRING);

                      }

                     

                      switch (cell.getCellType()) {

                         case Cell.CELL_TYPE_NUMERIC:

                            cellValue = String.valueOf(cell.getNumericCellValue());

                            break;

                         case Cell.CELL_TYPE_STRING:

                            cellValue = String.valueOf(cell.getStringCellValue());

                            break;

                         case Cell.CELL_TYPE_FORMULA:

                            cellValue = String.valueOf(cell.getCellFormula());

                            break;

                         case Cell.CELL_TYPE_BLANK:

                            cellValue = "";

                            break;

                         case Cell.CELL_TYPE_ERROR:

                            cellValue = "非法字符";

                            break;

                         default:

                            cellValue = "未知字符";

                            break;

                      }

                      str[cellNum] = cellValue;

                   }

                   pm.setSKU(Integer.parseInt(str[0]));

                   pm.setPrice1(Double.parseDouble(str[1]));

                   pm.setPrice2(Double.parseDouble(str[2]));

                   pm.setPrice3(Double.parseDouble(str[3]));

                   pm.setPrice4(Double.parseDouble(str[4]));

                   pm.setPrice5(Double.parseDouble(str[5]));

                   pm.setPrice6(Double.parseDouble(str[6]));

                   list.add(pm);

                }

                for(int i=0;i<list.size();i++){

                   priceModelBiz.addExcel(list.get(i));

                }

            }

         }

         return "redirect:mplist.do?pg=1";

      } catch (Exception e) {

         e.printStackTrace();

      }

      return null;

   }