excel 批量导入数据库-spring boot

首先添加maven 依赖

<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>
<dependency>
   <groupId>commons-io</groupId>
   <artifactId>commons-io</artifactId>
   <version>2.6</version>
</dependency>

编写工具类

package com.baking.baking.util;


import com.baking.baking.entity.Goods;
import com.baking.baking.entity.vo.GoodsExecl;
import com.baking.baking.zander.exceptions.BusinessException;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class GoodsExcelUtil {

    private static final Logger LOGGER = LogManager.getLogger(GoodsExcelUtil.class);

    /**
     * 导入excel
     *
     * @param file
     * @param projectId
     * @return
     */
    public static List<GoodsExecl> excelImport(File file, int projectId) {
        //获取文件名称
        String fileOriginalFilename = file.getName();
        String fileType = fileOriginalFilename.substring(fileOriginalFilename.lastIndexOf("."));
        String tableName = file.getName();
        List<GoodsExecl> resultList = new ArrayList<>();
        // 仅支持.xlsx格式
        if (!fileOriginalFilename.matches("^.+\\.(?i)(xlsx)$")) {
            LOGGER.info("文件全名称，{}", fileOriginalFilename);
            throw new BusinessException("文件格式不正確,只支持XLSX格式");
        }
        try {
            FileInputStream in = new FileInputStream(file);
            resultList = parseExcel(in, fileOriginalFilename, projectId);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return resultList;
    }


    public static List<GoodsExecl> parseExcel(InputStream in, String fileName, Integer projectId) throws Exception {
        List<GoodsExecl> resultList = new ArrayList<>();
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new BusinessException("excel為空");
        }
        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("商品編號")) {
                    throw new BusinessException("文件內容與模板不一致");
                }
                for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
                    row = sheet.getRow(j);
                    String key = row.getCell(0).getStringCellValue();
                    if (key.equals("商品編號") && key.equals("")) {
                        continue;
                    }
                    String goodsCode = row.getCell(0).getStringCellValue();
                    String goodsName = row.getCell(1).getStringCellValue();
                    String enGoodsName = row.getCell(2).getStringCellValue();
                    String classCode = row.getCell(3).getStringCellValue();
                    String goodsImg1 = "xxxxxxx/baking_service/goods_img/" + row.getCell(0).getStringCellValue() + ".xxxx";
                    String goodsNote = row.getCell(4).getStringCellValue();
                    String enGoodsNote = row.getCell(5).getStringCellValue();
                    String goodsAddress = row.getCell(6).getStringCellValue();
                    String enGoodsAddress = row.getCell(7).getStringCellValue();
                    GoodsExecl goods = new GoodsExecl(
                            goodsCode,goodsName,enGoodsName,
                            classCode,goodsImg1,
                            goodsNote,enGoodsNote,goodsAddress,enGoodsAddress,
                            projectId);
                    resultList.add(goods);
                }
                return resultList;
            }
        }
        LOGGER.info("处理excel获取的数据，{}", "");
        return null;
    }
    
    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;
    }


}

Controller 调用

   String originalFilename = file.getOriginalFilename();
        String newFileName = UUID.randomUUID().toString().replaceAll("-", "")
                + originalFilename.substring(originalFilename.lastIndexOf("."));

        File newFile = new File(projectConfig.getBakingFile() + File.separator + "/excel/" + File.separator + newFileName);
        //判断目标文件所在的目录是否存在

        if (!newFile.getParentFile().exists()) {
            //如果目标文件所在的目录不存在，则创建父目录
            newFile.getParentFile().mkdirs();
        }
        file.transferTo(newFile);
        List<GoodsExecl> resultList = GoodsExcelUtil.excelImport(newFile, projectId);
        List<Goods> goodsList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        int successFlag = 0;
        int failureFlag = 0;
        for (GoodsExecl goodsExecl : resultList) {
            Goods goods = new Goods();
            if (goodsService.check(goodsExecl.getGoodsCode(), projectId)) {
                goods.setGoodsCode(goodsExecl.getGoodsCode());
                goods.setGoodsName(goodsExecl.getGoodsName());
                goods.setEnGoodsName(goodsExecl.getEnGoodsName());
                GoodsClass goodsClass = goodsClassService.selectByCode(goodsExecl.getClassCode(), projectId);
                System.out.println(goodsClass.getId());
                goods.setGoodsClassId(goodsClass.getId());
                goods.setGoodsImg1(goodsExecl.getGoodsImg1());
                goods.setGoodsNote(goodsExecl.getGoodsNote());
                goods.setEnGoodsNote(goodsExecl.getEnGoodsNote());
                goods.setGoodsAddress(goodsExecl.getGoodsAddress());
                goods.setEnGoodsAddress(goodsExecl.getEnGoodsAddress());
                goods.setProjectId(projectId);
                successFlag++;
                goodsList.add(goods);
            } else {
                failureFlag++;
                sb.append(goodsExecl.getGoodsCode() + "丶");
            }
        }
        if (goodsList.size() > 0) {
            goodsService.insertBatch(goodsList);
        } else {
            return renderError("數據庫已經存在該Excel上所有商品信息，無法再次導入");
        }
        if (failureFlag == 0) {
            return renderSuccess("執行成功，成功導入" + successFlag + "條，失敗" + failureFlag + "條");
        } else {
            return renderSuccess("執行成功，成功導入" + successFlag + "條，" +
                    "失敗" + failureFlag + "條,導入失敗商品編號如下:" + sb.toString());
        }