excel导入

来源：互联网发布：eplan软件安装编辑：程序博客网时间：2024/05/22 10:53

    /**
   * 供应商导入
   *
   * @param excel
   * @param request
   * @return
   * @throws Exception
   */
   @SecurityMapping(title = "supplier_import.htm供应商导入", value = "/erp/supplier_import.htm*", rtype = "seller", rname = "supplier供应商导导入", rcode = "supplier_user", display = false, rgroup = "supplier管理")
   @RequestMapping("/erp/supplier_import.htm")
   @ResponseBody
   public BaseDTOBean supplier_import(@RequestParam("excel") CommonsMultipartFile excel, HttpServletRequest request)
           throws Exception {
       int execute = 0;
       Long userId = SecurityUserHolder.getConditionId();
       System.out.println("上传文件大小：" + excel.getSize());
       if (excel.getSize() <= 10 * 1024 * 1024) {
           List<String[]> list = this.goodsService.readExcel(excel);
           int i = 0;
           for (String[] str : list) {
               if (i > 0) {
                   Supplier su = new Supplier();
                   su.setUserId(userId);
                   su.setAddTime(new Date());
                   su.setCode(str[0]);
                   su.setSequence(CommUtil.null2Int(str[1]));
                   su.setName(str[2]);
                   su.setContacts(str[3]);
                   su.setPhoneNum(str[4]);
                   su.setAddress(str[5]);
                   this.supplierService.save(su);
                   i++;
               } else {
                   i++;
               }
           }
       }
       BaseDTOBean bb = new BaseDTOBean();
       bb.setStatus(EnumStateParameter.OK);
       return bb;

}

    @Override
   public List<String[]> readExcel(CommonsMultipartFile excel) {
       Map<Integer, Object> map = new LinkedHashMap<Integer, Object>();// 导入行
       Workbook workbook = null;
       List<String[]> list = new ArrayList<String[]>();
       try {
           workbook = this.getWorkbook(excel);
           if (workbook != null) {
               // 读取excel1（sheet从0开始）
               Sheet sheet = workbook.getSheetAt(0);
               int firstRowIndex = sheet.getFirstRowNum();
               int lastRowIndex = sheet.getLastRowNum();
               // 读取首行即,表头
               Row firstRow = sheet.getRow(firstRowIndex);
               /**
               * 读取导入表格第一行文本的下标和文本放到map集合里 3.30
               */
               for (int i = firstRow.getFirstCellNum(); i <= firstRow.getLastCellNum() - 1; i++) {
                   Cell cell = firstRow.getCell(i);
                   Integer rowValue = i;
                   String cellValue = this.getCellValue(cell, true);
                   System.out.print(rowValue + " " + cellValue + "\t");
                   map.put(rowValue, cellValue);
                   System.out.println(map);
               }
               // 读取数据行
               for (int rowIndex = firstRowIndex; rowIndex <= lastRowIndex; rowIndex++) {
                   Row currentRow = sheet.getRow(rowIndex);// 当前行
                   int firstColumnIndex = firstRow.getFirstCellNum(); // 首列
                   int lastColumnIndex = firstRow.getLastCellNum();// 最后一列
                   String[] str = new String[lastColumnIndex - firstColumnIndex];
                   for (int columnIndex = firstColumnIndex; columnIndex < lastColumnIndex; columnIndex++) {
                       Cell currentCell = currentRow.getCell(columnIndex);// 当前单元格
                       String currentCellValue = this.getCellValue(currentCell, true);// 当前单元格的值
                       str[columnIndex] = currentCellValue;
                   }

                   if (str != null) {
                       list.add(str);
                   }
               }
           }
       } catch (Exception e) {
           e.printStackTrace();
       }
       return list;
   }

0 0