el-upload + springboot + apache poi 实现解析固定excel模板数据导入数据库

前端代码

<template>
  <div>
    <el-upload
      class="upload"
      ref="doUpload"
      :action="uploadUrl"
      :on-preview="beforeUploadHandle"
      :on-success="successHandle"
      :limit="1"
      accept=".xls, .xlsx"
      :file-list="fileList">
      <el-button slot="trigger" size="small" type="primary" @click="submitUpload">导入</el-button>
      <div slot="tip" class="el-upload__tip">只能上传excel文件</div>
    </el-upload>
  </div>
</template>

<script>
  export default {
    data () {
      return {
        uploadUrl: '',
        fileList: []
      }
    },
    methods: {
      // 上传之前
      beforeUploadHandle (file) {
        console.log('fileType', file.type)
      },
      // 上传成功
      successHandle (response, file, fileList) {
        this.fileList = fileList
      },
      // 上传文件
      submitUpload () {
      	// storeType [1, 4, 5, 6, 7] [标准库, 实验库, 动态库, 竞品库, 仿真库]
        let storeType = '1'
        this.uploadUrl = this.$http.BASE_URL + `/doImport/importAll?token=${this.$cookie.get('token')}&storeType=${storeType}`
        this.$nextTick(() => {
          this.$refs.doUpload.submit()
        })
      }
    }
  }
</script>

后端代码

依赖

  <!-- Apache POI -->
  <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>

DoImportController

@RestController
@RequestMapping("doImport")
public class DoImportController extends AbstractController {
	@Autowired
    private WcmsBaseInfoService wcmsBaseInfoService;
    @Autowired
    private WcmsChemicalCompositionService wcmsChemicalCompositionService;

    @RequestMapping("/importAll")
    @Transactional
    public RestResponse importAll(@RequestParam(value = "file", required = false) MultipartFile file, @RequestParam(value = "storeType", required = false) String storeType) {
        if (file != null) {
            // 读取Excel文件中主表内容
            List<WcmsBaseInfo> wcmsMaterialInfoList = ExcelReader.readExcelToGetWcmsMaterialInfo(file);
            // 读取Excel文件中所有化学元素内容
            List<ChemicalComposition> chemicalCompositionList = ExcelReader.readExcelToGetChemicalComposition(file);
            // 获取到数据后执行相应插入数据库操作
            ......
            }
            return RestResponse.success().put("message", "上传成功");
        } else {
            return RestResponse.error("文件上传失败");
        }
    }
 }

ExcelReader

/**
 * @ClassName ExcelReader
 * @Description 读取Excel文件中的各个实体信息
 * @Author F
 * @Version 1.0
 */
public class ExcelReader {

    private final static Logger logger = LoggerFactory.getLogger(ExcelReader.class);

    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    /**
     * @param file 文件
     * @return 读取结果列表，读取失败时返回null
     * @description 读取Excel文件中主表信息
     */
    public static List<WcmsMaterialInfo> readExcelToGetWcmsMaterialInfo(MultipartFile file){
        List<WcmsMaterialInfo> wcmsMaterialInfoList;

        Workbook workbook = null;
        FileInputStream inputStream = null;
        String fileName = file.getOriginalFilename();

        try {
            // 获取Excel后缀名
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);

            // 获取Excel文件
            File excelFile = transferToFile(file);
            if (!excelFile.exists()) {
                logger.info("指定的Excel文件不存在！");
                return null;
            }

            // 获取Excel工作簿
            inputStream = new FileInputStream(excelFile);
            workbook = getWorkbook(inputStream, fileType);

            // 读取Excel第五行之后的合并行数
            List<Integer> mergeRowNumList = ExcelReader.getMergeRowNumList(file);
            // 读取excel中主表的数据
            wcmsMaterialInfoList = parseExcelToGetMaterialInfo(workbook, 1, mergeRowNumList);

            return wcmsMaterialInfoList;

        } catch (Exception e) {
            logger.info("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());
            return null;
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                logger.info("关闭数据流出错！错误信息：" + e.getMessage());
                return null;
            }
        }
    }

    /**
     * @param file 文件
     * @return 读取结果列表，读取失败时返回null
     * @description 读取Excel文件中化学成分信息
     */
    public static List<ChemicalComposition> readExcelToGetChemicalComposition(MultipartFile file) {
        List<ChemicalComposition> chemicalCompositionList;

        Workbook workbook = null;
        FileInputStream inputStream = null;
        String fileName = file.getOriginalFilename();

        try {
            // 获取Excel后缀名
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);

            // 获取Excel文件
            File excelFile = transferToFile(file);
            if (!excelFile.exists()) {
                logger.info("指定的Excel文件不存在！");
                return null;
            }

            // 获取Excel工作簿
            inputStream = new FileInputStream(excelFile);
            workbook = getWorkbook(inputStream, fileType);

            // 读取excel中化学成分数据
            chemicalCompositionList = parseExcelToGetChemicalComposition(workbook, 50);

            return chemicalCompositionList;

        } catch (Exception e) {
            logger.info("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());
            return null;
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                logger.info("关闭数据流出错！错误信息：" + e.getMessage());
                return null;
            }
        }
    }

    /**
     * @param row 行数据
     * @return 解析后的行数据对象，行数据错误时返回null
     * @description 提取每一行中主表的数据，构造成为一个结果数据对象
     * @note 当该行中有单元格的数据为空或不合法时，忽略该行的数据
     * @note 主表数据1-3列
     **/
    private static WcmsMaterialInfo convertRowDataToMaterialInfo(Row row, int startCellNum) {
        WcmsMaterialInfo wcmsMaterialInfo = new WcmsMaterialInfo();

        Cell cell;
        // 从1开始 去掉excel开始的序号
        int cellNum = startCellNum;

        // 材料名称(中文)
        cell = row.getCell(cellNum++);
        String materialCategoryC = convertCellValueToString(cell);
        wcmsMaterialInfo.setMaterialCategoryC(materialCategoryC);

        // 材料名称(英文)
        cell = row.getCell(cellNum++);
        String mMaterialCategoryE = convertCellValueToString(cell);
        wcmsMaterialInfo.setMaterialCategoryE(mMaterialCategoryE);

        // 材料牌号
        cell = row.getCell(cellNum++);
        String mMaterialNum = convertCellValueToString(cell);
        wcmsMaterialInfo.setMaterialNum(mMaterialNum);

        return wcmsMaterialInfo;
    }

    /**
     * @param row 行数据
     * @return 解析后的行数据对象，行数据错误时返回null
     * @Description 提取每一行中化学成分数据，构造成为一个结果数据对象
     * @note 化学成分数据偶从4-7列
     */
    private static ChemicalComposition convertRowDataToChemicalComposition(Row row, int startCellNum) {
        ChemicalComposition chemicalComposition = new ChemicalComposition();

        Cell cell;
        // 从 4开始 模板设置4以后是化学成分数据
        int cellNum = startCellNum;

        // C 碳
        cell = row.getCell(cellNum++);
        String c = convertCellValueToString(cell);
        chemicalComposition.setC(c);

        // N 氮
        cell = row.getCell(cellNum++);
        String n = convertCellValueToString(cell);
        chemicalComposition.setN(n);

        // O 氧
        cell = row.getCell(cellNum++);
        String o = convertCellValueToString(cell);
        chemicalComposition.setO(o);
        
        return chemicalComposition;
    }
    /**
     * @param cell 单元格
     * @return 内容
     * @description 将单元格内容转换为字符串
     */
    private static String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }

        String returnValue = null;
        switch (cell.getCellType()) {
            // 数字
            case NUMERIC:
                Double doubleValue = cell.getNumericCellValue();
                returnValue = doubleValue.toString();
                break;
            // 字符串
            case STRING:
                returnValue = cell.getStringCellValue();
                break;
            // 布尔
            case BOOLEAN:
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            // 空值
            case BLANK:
                break;
            // 公式
            case FORMULA:
                returnValue = cell.getCellFormula();
                break;
            // 故障
            case ERROR:
                break;
            default:
                break;
        }
        return returnValue;
    }

    /**
     * @param multipartFile 原本文件类型
     * @return File
     * @description 转换文件类型
     */
    private static File transferToFile(MultipartFile multipartFile) {
        // 选择用缓冲区来实现这个转换即使用java 创建的临时文件 使用 MultipartFile.transferto()方法 。
        File resultFile = null;
        try {
            String originalFileName = multipartFile.getOriginalFilename();
            
            String[] fileName = originalFileName.split("\\.");
            if (fileName.length > 1) {
                resultFile = File.createTempFile(fileName[0], fileName[1]);
                multipartFile.transferTo(resultFile);
                resultFile.deleteOnExit();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return resultFile;
    }

    /**
     * @param inputStream 读取文件的输入流
     * @param fileType    文件后缀名类型（xls或xlsx）
     * @return 包含文件数据的工作簿对象
     * @throws IOException
     * @description 根据文件后缀名类型获取对应的工作簿对象
     */
    private static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
        Workbook workbook = null;
        if (fileType.equalsIgnoreCase(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equalsIgnoreCase(XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }
    /**
     * @param cell  当前单元格
     * @param sheet 工作sheet
     * @return 合并的行数
     * @decription 获取合并行数
     */
    public static int getMergeRowNum(Cell cell, Sheet sheet) {
        int mergeSize = 1;
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
        for (CellRangeAddress cellRangeAddress : mergedRegions) {
            if (cellRangeAddress.isInRange(cell)) {
                //获取合并的行数
                mergeSize = cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow() + 1;
                break;
            }
        }
        return mergeSize;
    }

    /**
     * @param file 文件
     * @return 返回合并行数列表
     * @decription 获取合并行数列表
     */
    public static List<Integer> getMergeRowNumList(MultipartFile file) {
        // 解析sheet workbook.getNumberOfSheets()获取sheet页数 只有一个sheet页
        List<Integer> result = new ArrayList<>();

        Workbook workbook = null;
        FileInputStream inputStream = null;
        String fileName = file.getOriginalFilename();

        try {
            // 获取Excel后缀名
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);

            // 获取Excel文件
            File excelFile = transferToFile(file);
            if (!excelFile.exists()) {
                logger.info("指定的Excel文件不存在！");
                return null;
            }

            // 获取Excel工作簿
            inputStream = new FileInputStream(excelFile);
            workbook = getWorkbook(inputStream, fileType);
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                return result;
            }
            // 模板设置从第五行数据
            int rowStart = sheet.getFirstRowNum() + 5;
            int rowEnd = sheet.getPhysicalNumberOfRows();
            for (int rowNum = rowStart; rowNum < rowEnd; ) {
                Row row = sheet.getRow(rowNum);
                int physicalNumberOfRows = getMergeRowNum(row.getCell(0), sheet);
                // 合并行数
                result.add(physicalNumberOfRows);
                rowNum += physicalNumberOfRows;
            }
            return result;
        } catch (Exception e) {
            logger.info("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());
            return null;
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                logger.info("关闭数据流出错！错误信息：" + e.getMessage());
                return null;
            }
        }
    }
}