简单导入Excel

水深无声 2021-09-13 16:54 536阅读 0赞

## 简单导入Excel ##

**1.页面上传文件  
这里用的vue的插件**

<el-upload
            class="upload-demo"
            action="http://localhost:8089/file/exportExcel.do"
            multiple
            :limit="3">
            <el-button size="small" type="primary">导入Excel</el-button>
          </el-upload>

**controller**

@RequestMapping("exportExcel")
        public void exportExcel(MultipartFile file){
    
            productService.exportExcel(file);
    
        }

**service层【所有的业务逻辑都在这里】**

public void exportExcel(MultipartFile file) {
    
            //通过WorkbookFactory加载文件
            Workbook workbook = null;
            try {
                workbook = WorkbookFactory.create(file.getInputStream());
                //获取sheet
                Sheet sheet = workbook.getSheetAt(0);
    
                //获取当前sheet一共有多少行
                int lastRowNum = sheet.getLastRowNum();
    
                Row row;
    
                //查询出所有的brand集合，为了判断brandName对应的id
                List<Brand> brandList = brandService.queryListAll();
    
                //为了避免多次与数据库交互，定义一个list集合进行批量增加
                List productList = new ArrayList();
    
                for (int i = 1; i <= lastRowNum; i++) {
    
                    Product product = new Product();
    
                    //从第一行开始读取
                    row = sheet.getRow(i);
    
                    //读取第0列开始,productName
                    Cell cell = row.getCell(0);
    
                    //获取当前列的信息
                    String productName = cell.getStringCellValue();
                    //得出的值放到product中，新增
                    product.setProductName(productName);
    
                    //读取第1列
                    cell= row.getCell(1);
                    //获取当前列的信息，是数字类型的，不能用String
                    String brandName = cell.getStringCellValue();
    
                    Integer brandId = null;
                    //进行遍历查询brandName与那个相等，拿到id
                    for (Brand brand : brandList) {
                        if(brandName.equals(brand.getBrandName())){
                            brandId = brand.getId();
                        }
                    }
                    Brand brand = new Brand();
                    brand.setBrandName(brandName);
                  //  brand.setCreateDate(new Date());
                    //如果品牌名称没有查出来，则进行增加(id需要在增加以后返回)
                    if(brandId == null){
                        brandService.insertBrand(brand);
                        brandId= brand.getId();
                    }
                    product.setBrandId(brandId);
    
    
                    //读取第2列
                    cell= row.getCell(2);
                    double productPrice = cell.getNumericCellValue();
                    product.getProductPrice(productPrice);
    
                    product.setCreateDate(new Date());
    
                    productList.add(product);
    
                }
                productMapper.insertBath(productList);
    
    
            } catch (IOException e) {
                e.printStackTrace();
            } catch (InvalidFormatException e) {
                e.printStackTrace();
            }

就可以了