mybatis-Plus解析excel表格并添加到数据库

素颜马尾好姑娘i 2023-09-25 17:22 53阅读 0赞

**1.首先，创建一个StringBoot项目**

**2.导入相关依赖：**

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.5.2</version>
        </dependency>
        <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.47</version>
         </dependency>
         <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
         </dependency>

3. application.yaml的配置

spring:
      datasource:
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/student-programmer?useSSL=false&characterEncoding=utf8
        username: root
        password: root
      servlet:
        multipart:
          max-file-size: 100MB
          max-request-size: 500MB
     
    mybatis-plus:
      configuration:
        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
        map-underscore-to-camel-case: true
      mapper-locations: classpath*:/mapper/**/*.xml
      type-aliases-package: cn.java999studentlogin.bean
    server:
      port: 9090

4.创建java实体类

package cn.java999.uploadandparseexcel.entity;
    
    
    import com.baomidou.mybatisplus.annotation.TableId;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import org.springframework.stereotype.Component;
    
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    @Component
    public class Community {
    
    
        @TableId
        private Integer ID;
        private String number;
        private String relationship;
        private String name;
        private String gender;
        private String age;
        private String Date;
        private String card;
        private String Mobile;
        private String nationality;
        private String station;
        private String committee;
    
    
    }

5.创建ExcelUtils工具类

package cn.java999.uploadandparseexcel.Utils;
    
    import cn.java999.uploadandparseexcel.entity.Community;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.stereotype.Component;
    import org.springframework.web.multipart.MultipartFile;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    public class ExcelUtils {
        //总行数
        private static int totalRows = 12;
        //总条数
        private static int totalCells = 12;
        //错误信息接收器
        private static String errorMsg;
        /**
         * 验证EXCEL文件
         *
         * @param filePath
         * @return
         */
        public static boolean validateExcel(String filePath) {
            if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
                errorMsg = "文件名不是excel格式";
                return false;
            }
            return true;
        }
        /*
            excel2003版本的excel文件是.xls格式，excel2007及以上版本的excel的是.xlsx格式。
        */
        // @描述：是否是2003的excel，返回true是2003
        public static boolean isExcel2003(String filePath)  {
            return filePath.matches("^.+\\.(?i)(xls)$");
        }
        //@描述：是否是2007的excel，返回true是2007
        public static boolean isExcel2007(String filePath)  {
            return filePath.matches("^.+\\.(?i)(xlsx)$");
    
        }
        /**
         * 读取Excel里面客户的信息
         * @param wb
         * @return
         */
        private static List<Community> readExcelValue(Workbook wb) {
            //默认会跳过第一行标题
            // 得到第一个shell
            Sheet sheet = wb.getSheetAt(0);
            // 得到Excel的行数
            totalRows = sheet.getPhysicalNumberOfRows();
            // 得到Excel的列数(前提是有行数)
            if (totalRows > 1 && sheet.getRow(0) != null) {
                totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
            }
            List<Community> manychoiceList = new ArrayList<Community>();
            // 循环Excel行数
            for (int r = 1; r < totalRows; r++) {
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                Community community = new Community();
                // 循环Excel的列
                for (int c = 0; c < totalCells ; c++) {
                    Cell cell = row.getCell(c);
                    if (null != cell) {
                        if (c == 0) {            //第一列
                            //如果是纯数字,将单元格类型转为String
                            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                                double numericCellValue = cell.getNumericCellValue();
                                community.setID((int)numericCellValue);
                            }
    
                        } else if (c == 1) {
                            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                                cell.setCellType(CellType.STRING);
                            }
                            community.setNumber(cell.getStringCellValue());
                        } else if (c == 2) {
                            if (cell.getCellTypeEnum() == CellType.STRING) {
                                String stringCellValue = cell.getStringCellValue();
                                community.setRelationship(stringCellValue);
                            }
    
                        } else if (c == 3) {
                            if (cell.getCellTypeEnum() == CellType.STRING) {
                                String stringCellValue = cell.getStringCellValue();
                                community.setName(stringCellValue);
                            }
    
                        } else if (c == 4) {
                            if (cell.getCellTypeEnum() == CellType.STRING) {
                                String stringCellValue = cell.getStringCellValue();
                                community.setGender(stringCellValue);
                            }
    
                        } else if (c == 5) {
                            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                                double numericCellValue = cell.getNumericCellValue();
                                community.setAge(String.valueOf(Integer.valueOf((int) numericCellValue)));
    
                            }
    
    
                        } else if (c == 6) {
                            if (cell.getCellTypeEnum() == CellType.STRING) {
                                String  numericCellValue = cell.getStringCellValue();
                                community.setCard(numericCellValue);
                            }
    
                        } else if (c == 7) {
                            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                                cell.setCellType(CellType.STRING);
                            } String numericCellValue = cell.getStringCellValue();
                            community.setDate(numericCellValue);
    
                        } else if (c == 8) {
                            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                                cell.setCellType(CellType.STRING);
                            }
                            String numericCellValue = cell.getStringCellValue();
                            community.setMobile(numericCellValue);
    
                        } else if (c == 9) {
                            if (cell.getCellTypeEnum() == CellType.STRING) {
                                String stringCellValue = cell.getStringCellValue();
                                community.setNationality(stringCellValue);
                            }
    
                        } else if (c == 10) {
                            if (cell.getCellTypeEnum() == CellType.STRING) {
                                String stringCellValue = cell.getStringCellValue();
                                community.setStation(stringCellValue);
                            }
    
                        } else if (c == 11) {
                            if (cell.getCellTypeEnum() == CellType.STRING) {
                                String stringCellValue = cell.getStringCellValue();
                                community.setCommittee(stringCellValue);
                            }
    
                            //score属性是int数据类型，转换成int
    //                        manychoice.setScore(Integer.valueOf(cell.getStringCellValue()));
                        }
                    }
                }
                //将excel解析出来的数据赋值给对象添加到list中
                manychoiceList.add(community);
            }
            return manychoiceList;
        }
    
        /**
         * 根据excel里面的内容读取客户信息
         * @param is 输入流
         * @param isExcel2003 excel是2003还是2007版本
         * @return
         * @throws IOException
         */
        public static List<Community> createExcel(InputStream is, boolean isExcel2003) {
            try{
                Workbook wb = null;
                if (isExcel2003) {
                    // 当excel是2003时,创建excel2003
                    wb = new HSSFWorkbook(is);
                } else {
                    // 当excel是2007时,创建excel2007
                    wb = new XSSFWorkbook(is);
                }
                // 读取Excel里面客户的信息
                List<Community> communityList = readExcelValue(wb);
                return communityList;
            } catch (IOException e) {
                e.printStackTrace();
            }
            return null;
        }
    
    
        /**
         * 读EXCEL文件，获取信息集合
         * @return
         */
        public static List<Community> getExcelInfo(MultipartFile file) {
            String files = file.getOriginalFilename();//获取文件名
            try {
                if (!validateExcel(files)) {// 验证文件名是否合格
                    return null;
                }
                boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
                if (isExcel2007(files)) {
                    isExcel2003 = false;
                }
                List<Community> communityList = createExcel(file.getInputStream(), isExcel2003);
                return communityList;
            } catch (Exception e) {
                e.printStackTrace();
            }
            return null;
        }
    
    }

6.创建mapper

package cn.java999.uploadandparseexcel.mapper;
    
    import cn.java999.uploadandparseexcel.entity.Community;
    import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    import org.apache.ibatis.annotations.Mapper;
    
    @Mapper
    public interface Communitymapper extends BaseMapper<Community> {
    
    }

7.创建CommunityController

package cn.java999.uploadandparseexcel.controller;
    
    import cn.java999.uploadandparseexcel.Utils.ExcelUtils;
    import cn.java999.uploadandparseexcel.entity.Community;
    import cn.java999.uploadandparseexcel.mapper.Communitymapper;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.PostMapping;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.multipart.MultipartFile;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.util.List;
    
    @Controller
    
    public class CommunityController {
    
        @Autowired
        private Communitymapper communitymapper;
        @GetMapping("/aaa")
        public String aaa(){
            return "kkk.html";
        }
    
        @PostMapping("/excelExport")
        public void test(HttpServletRequest request, HttpServletResponse response, @RequestParam(value="file",required = false) MultipartFile file) {
            List<Community> excelInfo = ExcelUtils.getExcelInfo(file);
            for (Community patientInfo : excelInfo) {
    
                System.err.println(patientInfo);
                System.err.println("==========");
    
    
                Integer id = patientInfo.getID();
                String relationship = patientInfo.getRelationship();
                String number = patientInfo.getNumber();
                String name = patientInfo.getName();
                String gender = patientInfo.getGender();
                String age = patientInfo.getAge();
                String date = patientInfo.getDate();
                String card = patientInfo.getCard();
                String mobile = patientInfo.getMobile();
                String nationality = patientInfo.getNationality();
                String station = patientInfo.getStation();
                String committee = patientInfo.getCommittee();
    
    
                Community community = new Community();
    
                community.setID(id);
                community.setNumber(number);
                community.setRelationship(relationship);
                community.setName(name);
                community.setGender(gender);
                community.setAge(age);
                community.setDate(date);
                community.setCard(card);
                community.setMobile(mobile);
                community.setNationality(nationality);
                community.setStation(station);
                community.setCommittee(committee);
                System.err.println(age);
    
                int insert = communitymapper.insert(community);
                System.err.println(insert);
            }
    
        }
    }

8.在resource/static目录下创建updateFile.html文件

<!DOCTYPE html>
    <html>
    <head>
        <meta charset="utf-8">
        <title></title>
    </head>
    <body>
    <form method="post" enctype="multipart/form-data" action="/excelExport">
        <input type="file" name="file" value="选择文件"/>
        <input type="submit" value="上传"/>
    </form>
    </body>
    </html>

9.excel表格

![6d19721ad0fd4e129b951a27423ea2e4.png][]

10.前端页面展示，选择文件，点击上传

![31d0189f479d42aaaed18a8fea1d35a8.png][]

11.数据库展示

![c8bd7a7b3e6e495796dd8f5de6ebc124.png][]

12.到这里我们这解析excel并添加到数据库就完成了，请各位铁子们多多支持点赞，如有不足之处请评论下方小宁即使更改

[6d19721ad0fd4e129b951a27423ea2e4.png]: https://img-blog.csdnimg.cn/6d19721ad0fd4e129b951a27423ea2e4.png
[31d0189f479d42aaaed18a8fea1d35a8.png]: https://img-blog.csdnimg.cn/31d0189f479d42aaaed18a8fea1d35a8.png
[c8bd7a7b3e6e495796dd8f5de6ebc124.png]: https://img-blog.csdnimg.cn/c8bd7a7b3e6e495796dd8f5de6ebc124.png