Springboot 处理excel表格实现批量导入MySQL,附实验结果

第一步添加依赖

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

逻辑处理

  /**
 * Author： Be_insighted
 * Description：
 *
 * @date 2019/12/23 23:01
 **/
@Slf4j
public class TestMain {

    public static void excelImportDishs(File file, String shopId) throws Exception{
        String fileOriginalFilename = file.getName();
        String fileType = fileOriginalFilename.substring(fileOriginalFilename.lastIndexOf("."));
        String tableName = file.getName();
        // 仅支持.xlsx格式
        if (!fileOriginalFilename.matches("^.+\\.(?i)(xlsx)$")) {
            log.info("文件全名称，{}", fileOriginalFilename);
            System.out.println("上传文件格式不正确");
        }
        FileInputStream in = new FileInputStream(file);
        List<String> ids = parseExcel(in,fileOriginalFilename);
        log.info("parseExcel解析后：{}",ids.size());
        if (CollectionUtils.isBlank(ids)) {
            System.out.println("请输入正确的文件");
        }
        List<Product> products = new ArrayList<>();
        ids.forEach(e -> {
            products.add(new Product(e, shopId));
        });
        System.out.println("Product"+products);
       }
    public static List<String> parseExcel(InputStream in, String fileName) throws Exception {
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            return new ArrayList<>();
        }
        List<String> ids = new ArrayList<>();
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }

            int totalRows = sheet.getPhysicalNumberOfRows();
            int totalCells = 0;
            for (int n = 0; n < totalRows; n++) {

                //遍历单元格
                row = sheet.getRow(n);
                totalCells = row.getPhysicalNumberOfCells();        //获取每一行的单元格数
                //循环设置每个单元格的数据类型为String类型
                for (int j = 0; j < totalCells; j++) {
                    if (row.getCell(j) != null) {
                        row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
                    }
                }
            }
            // 取第一行标题
            row = sheet.getRow(0);
//            if (!row.getCell(0).getStringCellValue().equals("物品Id")) {
//                return new ArrayList<>();
//            }
            String title[] = null;
             // 遍历当前sheet中的所有行
            for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
                row = sheet.getRow(j);
                if (!row.getCell(0).getStringCellValue().equals("")) {
                    // TODO 遍历所有的列 目前仅处理第一列
                    for (int y = row.getFirstCellNum(); y < 1; y++) {
                        cell = row.getCell(y);
                        String key = row.getCell(0).getStringCellValue();
                        if (key.equals("物品Id") && !getCellValue(cell).equals("")) {
                            continue;
                        }
                    }
                } else {
                    continue;
                }
                ids.add(getCellValue(cell));
            }
        }
        log.info("处理excel获取的数据，{}",ids);
        return ids;
    }
    public static String getCellValue(Cell cell) {
        String value = null;
        DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        DecimalFormat df2 = new DecimalFormat("0"); // 格式化数字
switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                value = cell.getStringCellValue();
//                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
//                    value = df.format(cell.getNumericCellValue());
//                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
//                    value = sdf.format(cell.getDateCellValue());
//                } else {
//                    //Sun Dec 31 15:03:00 CST 1899格式化时间15:03
//                    value = sdf2.format(cell.getDateCellValue()).substring(11, 16);
//                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                //value = cell.getBooleanCellValue();
                value = "";
                break;
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            default:
                break;
        }
        return value;
    }
    public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            wb = new HSSFWorkbook(inStr); // 2003-
        } else if (".xlsx".equals(fileType)) {
            wb = new XSSFWorkbook(inStr); // 2007+
        } else {
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }
    public static void main(String[] args) throws Exception {
        String filePath = "C:\\Users\\Be_insighted\\Desktop\\新建 Microsoft Excel 工作表.xlsx";
        String filePath01 = "C:\\Users\\Be_insighted\\Desktop\\product_template.xlsx";
        File file = new File(filePath01);
        FileInputStream fis = new FileInputStream(file);
        excelImportDishs(file,"555");




    }
}

@Data
@Entity
@EntityListeners(AuditingEntityListener.class)
@Table(uniqueConstraints = {@UniqueConstraint(columnNames = {"productId"})})
@NoArgsConstructor
public class Product  {

    @Id
    @GenericGenerator(name="idGenerator", strategy="uuid")
    @GeneratedValue(generator="idGenerator")
    @Column(length = 64)
    String id;

    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @CreatedDate
    Date createTime;

    /**
     * 店铺id
     */
    @Column(length = 64)
    String  shopId;
    /**
     * 物品id
     */
    @Column(length = 64)
    String productId;

    public Product(String productId, String shopId){
        this.productId = productId;
        this.shopId = shopId;
    }
}

Controller

@PostMapping("product/import")
@ApiOperation(value = "店鋪管理-商品批量导入")
public BaseRet ShopProductImport(@RequestParam("file") MultipartFile file, @RequestParam("shopId") String shopId) throws Exception{
    return k***Service.excelImportDishs(file, shopId);
}

可以微微调整TestMain的内容，直接使用