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

2.导入相关依赖：

    org.apache.poipoi-ooxml3.17org.apache.poipoi4.1.0com.baomidoumybatis-plus-boot-starter3.5.2mysqlmysql-connector-java5.1.47org.projectlomboklombok

3. application.yaml的配置

spring:datasource:driver-class-name: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3306/student-programmer?useSSL=false&characterEncoding=utf8username: rootpassword: rootservlet:multipart:max-file-size: 100MBmax-request-size: 500MBmybatis-plus:configuration:log-impl: org.apache.ibatis.logging.stdout.StdOutImplmap-underscore-to-camel-case: truemapper-locations: classpath*:/mapper/**/*.xmltype-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 {@TableIdprivate 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是2003public static boolean isExcel2003(String filePath)  {return filePath.matches("^.+\\.(?i)(xls)$");}//@描述：是否是2007的excel，返回true是2007public static boolean isExcel2007(String filePath)  {return filePath.matches("^.+\\.(?i)(xlsx)$");}/*** 读取Excel里面客户的信息* @param wb* @return*/private static List readExcelValue(Workbook wb) {//默认会跳过第一行标题// 得到第一个shellSheet sheet = wb.getSheetAt(0);// 得到Excel的行数totalRows = sheet.getPhysicalNumberOfRows();// 得到Excel的列数(前提是有行数)if (totalRows > 1 && sheet.getRow(0) != null) {totalCells = sheet.getRow(0).getPhysicalNumberOfCells();}List manychoiceList = new ArrayList();// 循环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) {            //第一列//如果是纯数字,将单元格类型转为Stringif (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 createExcel(InputStream is, boolean isExcel2003) {try{Workbook wb = null;if (isExcel2003) {// 当excel是2003时,创建excel2003wb = new HSSFWorkbook(is);} else {// 当excel是2007时,创建excel2007wb = new XSSFWorkbook(is);}// 读取Excel里面客户的信息List communityList = readExcelValue(wb);return communityList;} catch (IOException e) {e.printStackTrace();}return null;}/*** 读EXCEL文件，获取信息集合* @return*/public static List getExcelInfo(MultipartFile file) {String files = file.getOriginalFilename();//获取文件名try {if (!validateExcel(files)) {// 验证文件名是否合格return null;}boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本if (isExcel2007(files)) {isExcel2003 = false;}List 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 {}

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;@Controllerpublic class CommunityController {@Autowiredprivate 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 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文件

9.excel表格 

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

11.数据库展示

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