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

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

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

2.导入相关依赖：

  1. <dependency>
  2.         <groupId>org.apache.poi</groupId>
  3.         <artifactId>poi-ooxml</artifactId>
  4.         <version>3.17</version>
  5.     </dependency>
  6.     <dependency>
  7.         <groupId>org.apache.poi</groupId>
  8.         <artifactId>poi</artifactId>
  9.         <version>4.1.0</version>
  10.     </dependency>
  11.     <dependency>
  12.             <groupId>com.baomidou</groupId>
  13.             <artifactId>mybatis-plus-boot-starter</artifactId>
  14.             <version>3.5.2</version>
  15.     </dependency>
  16.     <dependency>
  17.             <groupId>mysql</groupId>
  18.             <artifactId>mysql-connector-java</artifactId>
  19.             <version>5.1.47</version>
  20.      </dependency>
  21.      <dependency>
  22.             <groupId>org.projectlombok</groupId>
  23.             <artifactId>lombok</artifactId>
  24.      </dependency>

  1. application.yaml的配置

    spring:
    datasource:

    1.  driver-class-name: com.mysql.jdbc.Driver
    2.  url: jdbc:mysql://localhost:3306/student-programmer?useSSL=false&characterEncoding=utf8
    3.  username: root
    4.  password: root

    servlet:

    1.  multipart:
    2.    max-file-size: 100MB
    3.    max-request-size: 500MB

    mybatis-plus:
    configuration:

    1.  log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    2.  map-underscore-to-camel-case: true

    mapper-locations: classpath:/mapper/**/.xml
    type-aliases-package: cn.java999studentlogin.bean
    server:
    port: 9090

4.创建java实体类

  1. package cn.java999.uploadandparseexcel.entity;
  4. import com.baomidou.mybatisplus.annotation.TableId;
  5. import lombok.AllArgsConstructor;
  6. import lombok.Data;
  7. import lombok.NoArgsConstructor;
  8. import org.springframework.stereotype.Component;
  10. @Data
  11. @AllArgsConstructor
  12. @NoArgsConstructor
  13. @Component
  14. public class Community {
  17.     @TableId
  18.     private Integer ID;
  19.     private String number;
  20.     private String relationship;
  21.     private String name;
  22.     private String gender;
  23.     private String age;
  24.     private String Date;
  25.     private String card;
  26.     private String Mobile;
  27.     private String nationality;
  28.     private String station;
  29.     private String committee;
  32. }

5.创建ExcelUtils工具类

  1. package cn.java999.uploadandparseexcel.Utils;
  3. import cn.java999.uploadandparseexcel.entity.Community;
  4. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  5. import org.apache.poi.ss.usermodel.*;
  6. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  7. import org.springframework.stereotype.Component;
  8. import org.springframework.web.multipart.MultipartFile;
  9. import java.io.IOException;
  10. import java.io.InputStream;
  11. import java.util.ArrayList;
  12. import java.util.List;
  14. public class ExcelUtils {
  15.     //总行数
  16.     private static int totalRows = 12;
  17.     //总条数
  18.     private static int totalCells = 12;
  19.     //错误信息接收器
  20.     private static String errorMsg;
  21.     /**
  22.      * 验证EXCEL文件
  23.      *
  24.      * @param filePath
  25.      * @return
  26.      */
  27.     public static boolean validateExcel(String filePath) {
  28.         if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
  29.             errorMsg = "文件名不是excel格式";
  30.             return false;
  31.         }
  32.         return true;
  33.     }
  34.     /*
  35.         excel2003版本的excel文件是.xls格式，excel2007及以上版本的excel的是.xlsx格式。
  36.     */
  37.     // @描述：是否是2003的excel，返回true是2003
  38.     public static boolean isExcel2003(String filePath)  {
  39.         return filePath.matches("^.+\\.(?i)(xls)$");
  40.     }
  41.     //@描述：是否是2007的excel，返回true是2007
  42.     public static boolean isExcel2007(String filePath)  {
  43.         return filePath.matches("^.+\\.(?i)(xlsx)$");
  45.     }
  46.     /**
  47.      * 读取Excel里面客户的信息
  48.      * @param wb
  49.      * @return
  50.      */
  51.     private static List<Community> readExcelValue(Workbook wb) {
  52.         //默认会跳过第一行标题
  53.         // 得到第一个shell
  54.         Sheet sheet = wb.getSheetAt(0);
  55.         // 得到Excel的行数
  56.         totalRows = sheet.getPhysicalNumberOfRows();
  57.         // 得到Excel的列数(前提是有行数)
  58.         if (totalRows > 1 && sheet.getRow(0) != null) {
  59.             totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
  60.         }
  61.         List<Community> manychoiceList = new ArrayList<Community>();
  62.         // 循环Excel行数
  63.         for (int r = 1; r < totalRows; r++) {
  64.             Row row = sheet.getRow(r);
  65.             if (row == null) {
  66.                 continue;
  67.             }
  68.             Community community = new Community();
  69.             // 循环Excel的列
  70.             for (int c = 0; c < totalCells ; c++) {
  71.                 Cell cell = row.getCell(c);
  72.                 if (null != cell) {
  73.                     if (c == 0) {            //第一列
  74.                         //如果是纯数字,将单元格类型转为String
  75.                         if (cell.getCellTypeEnum() == CellType.NUMERIC) {
  76.                             double numericCellValue = cell.getNumericCellValue();
  77.                             community.setID((int)numericCellValue);
  78.                         }
  80.                     } else if (c == 1) {
  81.                         if (cell.getCellTypeEnum() == CellType.NUMERIC) {
  82.                             cell.setCellType(CellType.STRING);
  83.                         }
  84.                         community.setNumber(cell.getStringCellValue());
  85.                     } else if (c == 2) {
  86.                         if (cell.getCellTypeEnum() == CellType.STRING) {
  87.                             String stringCellValue = cell.getStringCellValue();
  88.                             community.setRelationship(stringCellValue);
  89.                         }
  91.                     } else if (c == 3) {
  92.                         if (cell.getCellTypeEnum() == CellType.STRING) {
  93.                             String stringCellValue = cell.getStringCellValue();
  94.                             community.setName(stringCellValue);
  95.                         }
  97.                     } else if (c == 4) {
  98.                         if (cell.getCellTypeEnum() == CellType.STRING) {
  99.                             String stringCellValue = cell.getStringCellValue();
  100.                             community.setGender(stringCellValue);
  101.                         }
  103.                     } else if (c == 5) {
  104.                         if (cell.getCellTypeEnum() == CellType.NUMERIC) {
  105.                             double numericCellValue = cell.getNumericCellValue();
  106.                             community.setAge(String.valueOf(Integer.valueOf((int) numericCellValue)));
  108.                         }
  111.                     } else if (c == 6) {
  112.                         if (cell.getCellTypeEnum() == CellType.STRING) {
  113.                             String  numericCellValue = cell.getStringCellValue();
  114.                             community.setCard(numericCellValue);
  115.                         }
  117.                     } else if (c == 7) {
  118.                         if (cell.getCellTypeEnum() == CellType.NUMERIC) {
  119.                             cell.setCellType(CellType.STRING);
  120.                         } String numericCellValue = cell.getStringCellValue();
  121.                         community.setDate(numericCellValue);
  123.                     } else if (c == 8) {
  124.                         if (cell.getCellTypeEnum() == CellType.NUMERIC) {
  125.                             cell.setCellType(CellType.STRING);
  126.                         }
  127.                         String numericCellValue = cell.getStringCellValue();
  128.                         community.setMobile(numericCellValue);
  130.                     } else if (c == 9) {
  131.                         if (cell.getCellTypeEnum() == CellType.STRING) {
  132.                             String stringCellValue = cell.getStringCellValue();
  133.                             community.setNationality(stringCellValue);
  134.                         }
  136.                     } else if (c == 10) {
  137.                         if (cell.getCellTypeEnum() == CellType.STRING) {
  138.                             String stringCellValue = cell.getStringCellValue();
  139.                             community.setStation(stringCellValue);
  140.                         }
  142.                     } else if (c == 11) {
  143.                         if (cell.getCellTypeEnum() == CellType.STRING) {
  144.                             String stringCellValue = cell.getStringCellValue();
  145.                             community.setCommittee(stringCellValue);
  146.                         }
  148.                         //score属性是int数据类型，转换成int
  149. //                        manychoice.setScore(Integer.valueOf(cell.getStringCellValue()));
  150.                     }
  151.                 }
  152.             }
  153.             //将excel解析出来的数据赋值给对象添加到list中
  154.             manychoiceList.add(community);
  155.         }
  156.         return manychoiceList;
  157.     }
  159.     /**
  160.      * 根据excel里面的内容读取客户信息
  161.      * @param is 输入流
  162.      * @param isExcel2003 excel是2003还是2007版本
  163.      * @return
  164.      * @throws IOException
  165.      */
  166.     public static List<Community> createExcel(InputStream is, boolean isExcel2003) {
  167.         try{
  168.             Workbook wb = null;
  169.             if (isExcel2003) {
  170.                 // 当excel是2003时,创建excel2003
  171.                 wb = new HSSFWorkbook(is);
  172.             } else {
  173.                 // 当excel是2007时,创建excel2007
  174.                 wb = new XSSFWorkbook(is);
  175.             }
  176.             // 读取Excel里面客户的信息
  177.             List<Community> communityList = readExcelValue(wb);
  178.             return communityList;
  179.         } catch (IOException e) {
  180.             e.printStackTrace();
  181.         }
  182.         return null;
  183.     }
  186.     /**
  187.      * 读EXCEL文件，获取信息集合
  188.      * @return
  189.      */
  190.     public static List<Community> getExcelInfo(MultipartFile file) {
  191.         String files = file.getOriginalFilename();//获取文件名
  192.         try {
  193.             if (!validateExcel(files)) {// 验证文件名是否合格
  194.                 return null;
  195.             }
  196.             boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
  197.             if (isExcel2007(files)) {
  198.                 isExcel2003 = false;
  199.             }
  200.             List<Community> communityList = createExcel(file.getInputStream(), isExcel2003);
  201.             return communityList;
  202.         } catch (Exception e) {
  203.             e.printStackTrace();
  204.         }
  205.         return null;
  206.     }
  208. }

6.创建mapper

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

7.创建CommunityController

  1. package cn.java999.uploadandparseexcel.controller;
  3. import cn.java999.uploadandparseexcel.Utils.ExcelUtils;
  4. import cn.java999.uploadandparseexcel.entity.Community;
  5. import cn.java999.uploadandparseexcel.mapper.Communitymapper;
  6. import org.springframework.beans.factory.annotation.Autowired;
  7. import org.springframework.stereotype.Controller;
  8. import org.springframework.web.bind.annotation.GetMapping;
  9. import org.springframework.web.bind.annotation.PostMapping;
  10. import org.springframework.web.bind.annotation.RequestParam;
  11. import org.springframework.web.multipart.MultipartFile;
  13. import javax.servlet.http.HttpServletRequest;
  14. import javax.servlet.http.HttpServletResponse;
  15. import java.util.List;
  17. @Controller
  19. public class CommunityController {
  21.     @Autowired
  22.     private Communitymapper communitymapper;
  23.     @GetMapping("/aaa")
  24.     public String aaa(){
  25.         return "kkk.html";
  26.     }
  28.     @PostMapping("/excelExport")
  29.     public void test(HttpServletRequest request, HttpServletResponse response, @RequestParam(value="file",required = false) MultipartFile file) {
  30.         List<Community> excelInfo = ExcelUtils.getExcelInfo(file);
  31.         for (Community patientInfo : excelInfo) {
  33.             System.err.println(patientInfo);
  34.             System.err.println("==========");
  37.             Integer id = patientInfo.getID();
  38.             String relationship = patientInfo.getRelationship();
  39.             String number = patientInfo.getNumber();
  40.             String name = patientInfo.getName();
  41.             String gender = patientInfo.getGender();
  42.             String age = patientInfo.getAge();
  43.             String date = patientInfo.getDate();
  44.             String card = patientInfo.getCard();
  45.             String mobile = patientInfo.getMobile();
  46.             String nationality = patientInfo.getNationality();
  47.             String station = patientInfo.getStation();
  48.             String committee = patientInfo.getCommittee();
  51.             Community community = new Community();
  53.             community.setID(id);
  54.             community.setNumber(number);
  55.             community.setRelationship(relationship);
  56.             community.setName(name);
  57.             community.setGender(gender);
  58.             community.setAge(age);
  59.             community.setDate(date);
  60.             community.setCard(card);
  61.             community.setMobile(mobile);
  62.             community.setNationality(nationality);
  63.             community.setStation(station);
  64.             community.setCommittee(committee);
  65.             System.err.println(age);
  67.             int insert = communitymapper.insert(community);
  68.             System.err.println(insert);
  69.         }
  71.     }
  72. }

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

  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4.     <meta charset="utf-8">
  5.     <title></title>
  6. </head>
  7. <body>
  8. <form method="post" enctype="multipart/form-data" action="/excelExport">
  9.     <input type="file" name="file" value="选择文件"/>
  10.     <input type="submit" value="上传"/>
  11. </form>
  12. </body>
  13. </html>

9.excel表格

6d19721ad0fd4e129b951a27423ea2e4.png

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

31d0189f479d42aaaed18a8fea1d35a8.png

11.数据库展示

c8bd7a7b3e6e495796dd8f5de6ebc124.png

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

发表评论

表情：
评论列表 （有 0 条评论，128人围观）

还没有评论，来说两句吧...

相关阅读