POI 从Excel中批量导入

自定义样式打印Excel

• 引入依赖

		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.1</version>
        </dependency>

Excel中的信息如下：

• java代码如下

public String importXls(String contractId,MultipartFile file) throws Exception {
        InputStream inputStream = file.getInputStream(); //从file获取输入流
        Workbook workbook = new XSSFWorkbook(inputStream); //创建了有内容的工作薄
        Sheet sheet = workbook.getSheetAt(0);  //获取工作表

        int lastRowIndex = sheet.getLastRowNum(); //获取当前sheet的最后一行的索引值
        ContractProduct contractProduct = null;
        Row row = null;
        List<ContractProduct> productList = new ArrayList<>();// 用来接收每个货物对象，为了能一次性调用service的方法
        for (int i = 1; i <= lastRowIndex; i++) {
            contractProduct = new ContractProduct();
            contractProduct.setId(UUID.randomUUID().toString());
            contractProduct.setCompanyId(companyId);
            contractProduct.setCompanyName(companyName);
            contractProduct.setCreateBy(createBy);
            contractProduct.setCreateTime(new Date());

            contractProduct.setContractId(contractId); //设置货物属于哪个合同

            row = sheet.getRow(i);//获取有效
//            生产厂家	货号	数量	包装单位(PCS/SETS)	装率	箱数	单价	货物描述	要求
            String factoryName = row.getCell(1).getStringCellValue(); //生产厂家
            contractProduct.setFactoryName(factoryName);
            String productNo = row.getCell(2).getStringCellValue();//货号
            contractProduct.setProductNo(productNo);
            Double cnumber = row.getCell(3).getNumericCellValue();//数量
            contractProduct.setCnumber(cnumber.intValue());
            String packingUnit = row.getCell(4).getStringCellValue();//包装单位(PCS/SETS)
            contractProduct.setPackingUnit(packingUnit);

            Double loadingRate = row.getCell(5).getNumericCellValue();//装率
            contractProduct.setLoadingRate(loadingRate.toString());

            Double boxNum = row.getCell(6).getNumericCellValue();//箱数
            contractProduct.setBoxNum(boxNum.intValue());

            Double price = row.getCell(7).getNumericCellValue();//单价
            contractProduct.setPrice(price);

            String productDesc = row.getCell(8).getStringCellValue();//货物描述
            contractProduct.setProductDesc(productDesc);

            String productRequest = row.getCell(9).getStringCellValue();//要求
            contractProduct.setProductRequest(productRequest);

            productList.add(contractProduct);
        }
        contractProductService.saveList(productList);

    }