java上传Excel并解析存储数据库

最新推荐文章于 2024-04-26 13:53:13 发布

Mr_SnakeZ

最新推荐文章于 2024-04-26 13:53:13 发布

阅读量445

点赞数

分类专栏： java 文件上传文章标签： java excel

本文链接：https://blog.csdn.net/Mr_SnakeZ/article/details/116482904

版权

本文档介绍了如何使用Java处理Excel文件，首先通过Maven引入相关依赖，然后在Controller层接收文件，最后在ServiceImpl层解析Excel内容并存储到数据库。

摘要由CSDN通过智能技术生成

一、引入maven依赖

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

二、controller层

@RequestMapping(value = "/importWorkExcel", method = RequestMethod.POST, produces = "application/json;charset=utf-8")
    public void importWorkExcel(@RequestParam("excel") MultipartFile multipartFile) throws BusinessException,IOException, ParseException {
   
        workSpotService.importWorkExcel(multipartFile);
    }

三、serviceImpl层

@Override
    public void importWorkExcel(MultipartFile file) throws IOException, ParseException {
   

        //检查文件
        checkFile(file);
        //获得WorkBook对象
        Workbook workbook = getWorkBook(file);
        if(workbook != null){
   
            for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
   
                //获得当前sheet表
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if(sheet == null){
   
                    continue;
                }
                //获得当前sheet的开始行
                int firstRowNum = sheet.getFirstRowNum();
                //获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();
                //循环前两行之外的所有行
                for(int rowNum = firstRowNum+2;rowNum <= lastRowNum;rowNum++){
   
                    //获得当前行
                    Row row = sheet.getRow(rowNum);
                    if(row == null){
   
                        continue;
                    }
                    //获得当前行的开始行
                    int firstCellNum = row.getFirstCellNum();
                    //获得当前行的列数
                    int lastCellNum = row.getPhysicalNumberOfCells();
                    String workName = "";
                    String leaderName = "";
                    Integer leaderId = null;
                    Date startTime = null;
                    Date endTime = null;
                    String levelStr = "";
                    Integer level = null;
                    String majorStr = "";
                    Integer major = null;
                    for(int cellNum = 0; cellNum<row.