300字范文 > 导入excel表格到数据库导入excel表格到数据库代码根据excel表格路径将数据导入到

导入excel表格到数据库导入excel表格到数据库代码根据excel表格路径将数据导入到

时间：2022-04-09 17:46:24

导入excel表格到数据库、根据路径导入excel表格到数据库代码、根据excel表格路径将数据导入到数据库、验证要导入的excel表格数据、根据路径获取MultipartFile、FileItem文件

导入数据前台导入数据后台导入xmlController层UserController Controller层上传的为文件路径根据路径获取MultipartFile、FileItem文件UserController service层serviceImpl层dao层UserDaoGenderDao xml层UserMapper.xmlGenderMapper.xml 实体类UserGender实体类工具类ExcelReadUtils类CheckVoluationUtil类ExeclClass工具类演示遇到的错误详细的导出数据下载源码

导入数据前台

<!DOCTYPE html><html><head><meta charset="UTF-8"><title>导入excel表格到数据库</title></head><body><form id="uploadForm" enctype="multipart/form-data">文件:<input id="file" type="file" name="file" />批次：<input type="number" name="batch" id="batch" /></form><button id="upload">导入数据</button><h2 class="errorlist">错误列表</h2><button class="errorlist" onclick="daochu()">导出错误列表</button><table class="errorlist" border="" cellspacing="" cellpadding="" id="tab1"><tr id="hide"><th>姓名</th><th>身份证号</th><th>性别</th><th>出生日期</th><th>年龄</th><th>错误原因</th><th>操作</th></tr></table><h2 class="upd">修改信息</h2><div class="upd"><form action="#" method="post"><input type="text" name="batchTwo" id="batchTwo" style="display: none;" /><br /> name <input type="text" name="name" id="name" /><br /> idCard <input type="text" name="idCard" id="idCard" /><br /> genderId <input type="text" name="genderId" id="genderId" /><br /> birthday <input type="date" name="birthday" id="birthday" /><br /> age <input type="text" name="age" id="age" /><br /><input type="button" onclick="addOne()" value="保存" /><input type="button" onclick="haha()" value="取消" /></form></div><script type="text/javascript" src="js/jquery-2.1.0.js"></script><script type="text/javascript" src="js/moment.min.js"></script><script type="text/javascript" src="js/Export2Excel.js"></script><script type="text/javascript" src="js/FileSaver.min.js"></script><script type="text/javascript" src="js/Blob.js"></script><script type="text/javascript" src="js/xlsx.core.min.js"></script><script type="text/javascript">//返回的错误列表var error = [];//选择的批次var batchs = "";//要修改的用户的下标var updId = 0;//就绪函数$(function() {//隐藏修改信息的东西$(".upd").hide()//隐藏错误信息的东西$(".errorlist").hide()var file = null;//选择的文件（没有用到）$("#file").change(function(e) {//选择文件时触发的事件console.log($("#file")[0].files[0])//打印文件file = $("#file")[0].files[0];//获取文件});$("#upload").click(function() {//点击导入数据时触发if(error.length > 0) {//判断错误列表是否为空alert("请处理错误数据！");return;}if($("#batch").val().length < 1) {//判断选择的批次是否为空alert("请输入批次！");return;}$("#hide").nextAll().remove();//清空错误列表var formData = new FormData($('#uploadForm')[0]);//获取表单的信息（参数）$.ajax({//提交type: "post",url: "http://192.168.0.121:8090/importInfo",data: formData,//参数processData: false, // jQuery不要去处理发送的数据contentType: false, // jQuery不要去设置Content-Type请求头dataType: "json",success: function(data) {if(data.success == "true") {//判断是否有导入成功的数据alert(data.row + "条导入成功！");}if(data.errorList != null) {//判断错误列表是否为空error = data.errorList;//获取错误列表$("#tab1").append(getTableString(error));//根据错误列表获取表格内容 getTableString(error)方法见下$(".errorlist").show()//显示错误信息}}})})})function getTableString(errorList) {//根据列表返回表格字符串var $table = ""//要返回的信息for(var i = 0; i < errorList.length; i++) {//循环列表拼接表格信息errorList[i].birthday = moment(errorList[i].birthday).format("YYYY-MM-DD")//将日期格式化var user = errorList[i];//获取当前循环的对象$table += "<tr class=" + i + ">" +//拼接class为当前下标"<td>" + user.name + "</td>" +//姓名"<td>" + user.idCard + "</td>" +//身份证"<td>" + user.genderId + "</td>" +//性别"<td>" + user.birthday + "</td>" +//出生日期"<td>" + user.age + "</td>" +//年龄"<td>" + user.errorInfo + "</td>" +//错误信息"<td>" +"<a οnclick='del(" + i + ")' style='margin-right: 10px;'>删除</a>" +//点击删除时将下标传过去"<a οnclick='upd(" + i + ")' >修改</a>" +//修改时传下标"</td>" +"</tr>";}return $table//返回拼接好的表格数据}function del(i) {//点击删除时var ok = confirm("确认要删除吗？");//是否删除if(ok) {//是error = rem(i);//根据下标删除元素rem(i)方法见下$("#hide").nextAll().remove();//清空表格错误列表信息$("#tab1").append(getTableString(error));//重新获取表格内容进行追加}}//根据下标删除元素function rem(i) {//前提要知道循环哪个列表这里为error错误列表要是不确定可以通过参数传过来var newList = []///要返回的列表for(var j = 0; j < error.length; j++) {//遍历要删除的列表 if(j != i) {newList.push(error[j])//将不是这个下标的加到数组}}return newList//返回新数组}function upd(i) {//点击修改时var user = error[i];//获取对象updId = i;//获取要修改的下标$(".upd").show()//显示要修改的信息$("#name").val(user.name);//赋值$("#idCard").val(user.idCard);$("#genderId").val(user.genderId);$("#birthday").val(user.birthday);$("#age").val(user.age);$("#batchTwo").val(batchs);}function haha() {//点击取消时$(".upd").hide()//隐藏要修改的信息}function addOne() {//点击保存时$.ajax({//提交type: "post",url: "http://192.168.0.121:8090/addOne",data: {//参数"batch": $("#batchTwo").val(),"name": $("#name").val(),"idCard": $("#idCard").val(),"genderId": $("#genderId").val(),"birthday": $("#birthday").val(),"age": $("#age").val()},dataType: "json",success: function(data) {if(data.success == "true") {//判断是否添加成功alert("添加成功！");//提示error = rem(updId);//根据修改的人的下标删除改用户$("#hide").nextAll().remove();//清空表格内容$("#tab1").append(getTableString(error));//重新追加表格信息$(".upd").hide()//隐藏修改的信息} else {alert(data.errorList[0].errorInfo)//提示错误原因}}})}function daochu() {//导出错误列表var title = [{title: '姓名',dataIndex: 'name'},{title: '身份证',dataIndex: 'idCard'},{title: '性别',dataIndex: 'genderId'},{title: '出生日期',dataIndex: 'birthday'},{title: '年龄',dataIndex: 'age'},{title: '错误原因',dataIndex: 'errorInfo'}];export2Excel(title, error, '错误列表')}function export2Excel(columns, list, fileName) {let tHeader = []let filterVal = []columns.forEach(item => {tHeader.push(item.title)filterVal.push(item.dataIndex)})const data = list.map(v => filterVal.map(j => v[j]))export_json_to_excel(tHeader, data, fileName);}</script></body></html>

导入数据后台

导入xml

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.0.0</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.0.0</version></dependency><dependency><groupId>commons-fileupload</groupId><artifactId>commons-fileupload</artifactId><version>1.3.3</version></dependency>

Controller层

UserController

Controller层上传的为文件路径

根据路径获取MultipartFile、FileItem文件

UserController

import mon.web.BaseController;import com.ceshi.entity.User;import com.ceshi.service.UserService;import mons.fileupload.FileItem;import mons.fileupload.FileItemFactory;import mons.fileupload.disk.DiskFileItemFactory;import org.springframework.web.bind.annotation.*;import org.springframework.web.multipart.MultipartFile;import org.springframework.monsMultipartFile;import javax.annotation.Resource;import javax.servlet.http.HttpServletRequest;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.OutputStream;import java.util.Map;@RestController@CrossOriginpublic class UserController extends BaseController {@Resourceprivate UserService userService;/*** 导入数据* @param batch 批次* @param picPath 路径*/@PostMapping("/importInfoTow")public Map<String, Object> importInfoTow(String batch, String picPath){FileItem fileItem = createFileItem(picPath);//根据路径获取文件 createFileItem(picPath)方法见下MultipartFile mfile = new CommonsMultipartFile(fileItem);//根据fileItem获取MultipartFile文件return userService.importInfo(batch,mfile);//调用service层导入}private static FileItem createFileItem(String filePath) {FileItemFactory factory = new DiskFileItemFactory(16, null);String textFieldName = "textField";int num = filePath.lastIndexOf(".");String extFile = filePath.substring(num);FileItem item = factory.createItem(textFieldName, "text/plain", true,"MyFileName" + extFile);File newfile = new File(filePath);int bytesRead = 0;byte[] buffer = new byte[8192];try{FileInputStream fis = new FileInputStream(newfile);OutputStream os = item.getOutputStream();while ((bytesRead = fis.read(buffer, 0, 8192))!= -1){os.write(buffer, 0, bytesRead);}os.close();fis.close();}catch (IOException e){e.printStackTrace();}return item;}}

service层

import com.ceshi.entity.User;import org.springframework.web.multipart.MultipartFile;import java.util.Map;public interface UserService {//导入数据Map<String,Object> importInfo(String batch, MultipartFile file);//修改导入数据Map<String,Object> addOne(User user);}

serviceImpl层

import mon.utils.CheckVoluationUtil;import mon.utils.ExcelReadUtils;import mon.utils.ExeclClass;import com.ceshi.dao.UserDao;import com.ceshi.entity.User;import com.ceshi.service.UserService;import org.springframework.stereotype.Service;import org.springframework.transaction.annotation.Transactional;import org.springframework.web.multipart.MultipartFile;import javax.annotation.Resource;import java.util.*;@Service@Transactional(rollbackFor = Exception.class)public class UserServiceImpl implements UserService {@Resourceprivate UserDao userDao;//dao层@Overridepublic Map<String, Object> importInfo(String batch, MultipartFile file) {Map<String,Object> map = new HashMap<>();try {//验证数据类CheckVoluationUtil工具类见下CheckVoluationUtil checkVoluationUtil = new CheckVoluationUtil(); Map<String,Object> mapEntry = new HashMap<>();//正确与错误列表ExeclClass<User> execlClass = ExcelReadUtils.batchImportJxPlanForWorker(file);//获取表格数据 ExeclClass工具类见下ExcelReadUtils工具类见下mapEntry = checkVoluationUtil.cheskAndVoluation(execlClass.getDataLsit());//进行数据验证List<User> errorList = (List<User>)mapEntry.get("error");//得到错误列表List<User> succeedList = (List<User>)mapEntry.get("succeed"); //得到正确列表if(errorList.size() > 0){//判断错误列表是否为空map.put("errorList",errorList);//返回错误数据}if(succeedList.size()>0){//判断正确列表int rows = 0;for(int i = 0; i < succeedList.size(); i++){//循环String id = UUID.randomUUID().toString().replaceAll("-", "");//获取uuidUser user = succeedList.get(i);user.setId(id);user.setBatch(batch);int row = userDao.addOne(user);//插入数据库if (row == 1){//是否添加成功rows++;//记录添加了多少条}}map.put("success","true");//方法添加成功map.put("row",rows);//返回成功条数map.put("batchId",batch);//返回选的批次数}} catch (Exception e) {e.printStackTrace();map.put("success","false");//出错}return map;//返回}@Overridepublic Map<String, Object> addOne(User userParameter) {Map<String,Object> map = new HashMap<>();try {CheckVoluationUtil checkVoluationUtil = new CheckVoluationUtil(); //验证数据类Map<String,Object> mapEntry = new HashMap<>();//正确与错误列表List<User> userList = new ArrayList<>();userList.add(userParameter);mapEntry = checkVoluationUtil.cheskAndVoluation(userList); //进行数据验证List<User> errorList = (List<User>)mapEntry.get("error");//得到错误列表List<User> succeedList = (List<User>)mapEntry.get("succeed"); //得到正确列表if(errorList.size() > 0){map.put("errorList",errorList);//返回错误数据}if(succeedList.size()>0){int rows = 0;for(int i = 0; i < succeedList.size(); i++){String id = UUID.randomUUID().toString().replaceAll("-", ""); //生成uuidUser user = succeedList.get(i);user.setId(id);int row = userDao.addOne(user);//插入数据库if (row == 1){rows++;}}map.put("success","true");map.put("row",rows);}} catch (Exception e) {e.printStackTrace();map.put("success","false");}return map;}}

dao层

UserDao

import com.ceshi.entity.User;import org.apache.ibatis.annotations.Param;import org.ponent;import java.util.List;@Componentpublic interface UserDao {/*** 添加* @param user* @return*/int addOne(User user);/*** 根据身份证查询* @param idCard* @return*/List<User> getOneByIdCard(@Param("idCard") String idCard);}

GenderDao

import com.ceshi.entity.Gender;import org.apache.ibatis.annotations.Param;import org.ponent;import java.util.List;@Componentpublic interface GenderDao {//查询性别List<Gender> getOneByContent(@Param("content") String content);}

xml层

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.ceshi.dao.UserDao"><insert id="addOne" parameterType="com.ceshi.entity.User">INSERT INTO `user` (`id`,`name`,`age`,`genderId`,`birthday`,`batch`,`idCard`)VALUES(#{id},#{name},#{age},#{genderId},#{birthday},#{batch},#{idCard})</insert><select id="getOneByIdCard" resultType="com.ceshi.entity.User">select * from `user` where idCard = #{idCard}</select></mapper>

GenderMapper.xml

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.ceshi.dao.GenderDao"><select id="getOneByContent" resultType="com.ceshi.entity.Gender">select * from `gender` where content = #{content}</select></mapper>

实体类

User

import java.io.Serializable;import java.util.Date;public class User implements Serializable {private String id;//主键idprivate String name; //姓名private String age; //年龄private String genderId; //性别private Date birthday;//出生日期private String idCard; //身份证号private String errorInfo;//错误信息private String batch;//批次public String getBatch() {return batch;}public void setBatch(String batch) {this.batch = batch;}public String getErrorInfo() {return errorInfo;}public void setErrorInfo(String errorInfo) {this.errorInfo = errorInfo;}public String getIdCard() {return idCard;}public void setIdCard(String idCard) {this.idCard = idCard;}public String getId() {return id;}public void setId(String id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getAge() {return age;}public void setAge(String age) {this.age = age;}public String getGenderId() {return genderId;}public void setGenderId(String genderId) {this.genderId = genderId;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}}

Gender实体类

工具类

ExcelReadUtils类

import com.ceshi.entity.User;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.usermodel.WorkbookFactory;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.ponent;import org.springframework.web.multipart.MultipartFile;import java.io.InputStream;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.List;import java.util.Map;@Componentpublic class ExcelReadUtils {/*** 导入Excel* @param file* @throws Exception*///@Transactional(readOnly = false,rollbackFor = Exception.class)public static ExeclClass batchImportJxPlanForWorker(MultipartFile file) throws Exception {List<User> ss = new ArrayList<>();//获取的列表InputStream is = file.getInputStream();Workbook workbook = WorkbookFactory.create(is);//确定版本boolean isExcel = file.getOriginalFilename().endsWith("xls") ? true : false;List<Map<String, Object>> returnMap;ExeclClass execlClass = new ExeclClass();//返回execlClass类见下if (isExcel) {//判断版本//有多少个sheetint sheets = workbook.getNumberOfSheets();for (int i = 0; i < 1; i++) {HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(i);//获取多少行int rows = sheet.getPhysicalNumberOfRows();if (rows > 0) {int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();String[] columnNames = new String[coloumNum + 1];HSSFRow row0 = sheet.getRow(0);for (int ij = 0; ij < coloumNum; ij++) {columnNames[ij] = row0.getCell(ij).getStringCellValue();}columnNames[columnNames.length - 1] = "错误说明";execlClass.setColumnNames(columnNames);User s = null;//遍历每一行，注意：第 0 行为标题for (int j = 1; j < rows; j++) {s = new User();//获得第 j 行HSSFRow row = sheet.getRow(j);int k = 0;SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");boolean ok = false; //此行数据是否为空//姓名if (row.getCell(k) != null && row.getCell(k).getStringCellValue().replace(" ", "").length() > 0) {//非空验证row.getCell(k).setCellType(CellType.STRING); //将此单元设置为字符ok = true;//此行不为空s.setName(row.getCell(k++).getStringCellValue().replace(" ", ""));//获取内容并去空格} else {s.setName("");//此单元格为空k++;//获取下个单元格下面相同}//证件号码（身份证号）if (row.getCell(k) != null && row.getCell(k).getStringCellValue().replace(" ", "").length() > 0) {row.getCell(k).setCellType(CellType.STRING);ok = true;s.setIdCard(row.getCell(k++).getStringCellValue().replace(" ", "").toUpperCase());} else {s.setIdCard("");k++;}//性别if (row.getCell(k) != null && row.getCell(k).getStringCellValue().replace(" ", "").length() > 0) {row.getCell(k).setCellType(CellType.STRING);ok = true;s.setGenderId(row.getCell(k++).getStringCellValue().replace(" ", ""));} else {s.setGenderId("");k++;}//出生日期if (row.getCell(k) != null) {s.setBirthday(row.getCell(k++).getDateCellValue());} else {s.setBirthday(null);k++;}//年龄if (row.getCell(k) != null && row.getCell(k).getStringCellValue().replace(" ", "").length() > 0) {row.getCell(k).setCellType(CellType.STRING);ok = true;s.setAge(row.getCell(k++).getStringCellValue().replace(" ", ""));} else {s.setAge("");k++;}if (ok) {//是否为空行ss.add(s);}}}}execlClass.setDataLsit(ss);} else {//有多少个sheetint sheets = workbook.getNumberOfSheets();for (int i = 0; i < 1; i++) {XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(i);//获取多少行int rows = sheet.getPhysicalNumberOfRows();if (rows > 0) {int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();String[] columnNames = new String[coloumNum + 1];XSSFRow row0 = sheet.getRow(0);for (int ij = 0; ij < coloumNum; ij++) {columnNames[ij] = row0.getCell(ij).getStringCellValue();}columnNames[columnNames.length - 1] = "错误说明";execlClass.setColumnNames(columnNames);User s = null;//遍历每一行，注意：第 0 行为标题for (int j = 1; j < rows; j++) {s = new User();//获得第 j 行XSSFRow row = sheet.getRow(j);//设置参数与Excel列对应int k = 0;SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");boolean ok = false;//姓名if (row.getCell(k) != null && row.getCell(k).getStringCellValue().replace(" ", "").length() > 0) {row.getCell(k).setCellType(CellType.STRING);ok = true;s.setName(row.getCell(k++).getStringCellValue().replace(" ", ""));} else {s.setName("");k++;}//证件号码（身份证号）if (row.getCell(k) != null && row.getCell(k).getStringCellValue().replace(" ", "").length() > 0) {row.getCell(k).setCellType(CellType.STRING);ok = true;s.setIdCard(row.getCell(k++).getStringCellValue().replace(" ", "").toUpperCase());} else {s.setIdCard("");k++;}//性别if (row.getCell(k) != null && row.getCell(k).getStringCellValue().replace(" ", "").length() > 0) {row.getCell(k).setCellType(CellType.STRING);ok = true;s.setGenderId(row.getCell(k++).getStringCellValue().replace(" ", ""));} else {s.setGenderId("");k++;}//出生日期if (row.getCell(k) != null) {s.setBirthday(row.getCell(k++).getDateCellValue());} else {s.setBirthday(null);k++;}//年龄if (row.getCell(k) != null && row.getCell(k).getStringCellValue().replace(" ", "").length() > 0) {row.getCell(k).setCellType(CellType.STRING);ok = true;s.setAge(row.getCell(k++).getStringCellValue().replace(" ", ""));} else {s.setAge("");k++;}if (ok) {ss.add(s);}}}}execlClass.setDataLsit(ss);}return execlClass;}}

CheckVoluationUtil类

import com.ceshi.dao.GenderDao;import com.ceshi.dao.UserDao;import com.ceshi.entity.Gender;import com.ceshi.entity.User;import org.springframework.beans.factory.annotation.Autowired;import org.ponent;import javax.annotation.PostConstruct;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.regex.Matcher;import java.util.regex.Pattern;import java.util.*;@Componentpublic class CheckVoluationUtil {@Autowiredprivate UserDao userDao;//dao层@Autowiredprivate GenderDao genderDao;//dao层public static CheckVoluationUtil checkVoluationUtil;@PostConstructpublic void init(){checkVoluationUtil = this;checkVoluationUtil.userDao = this.userDao;checkVoluationUtil.genderDao = this.genderDao;}public Map<String,Object> cheskAndVoluation(List<User> list){Map<String,Object> resultMap = new HashMap<>();//正确的列表List<User> insertList = new ArrayList<User>();//错误列表List<User> errorList = new ArrayList<User>();for(int i=0;i<list.size();i++){//错误信息String errorStr = "";User userInsert = list.get(i);User userError = new User();//姓名userError.setName(userInsert.getName());//证件号码（身份证号）userError.setIdCard(userInsert.getIdCard());//性别userError.setGenderId(userInsert.getGenderId());//出生日期userError.setBirthday(userInsert.getBirthday());//年龄userError.setAge(userInsert.getAge());//根据身份证查询数据是否已导入boolean ok = true;//根据身份证查询导入的列表中有没有重复的数据int ge = 0;//身份证是否规范boolean isOk = true;//身份证是否为空if(userInsert.getIdCard() != null && !userInsert.getIdCard().equals("")){try{//验证身份证是否符合规范isOk = CheckVoluationUtil.IDCardValidate(userInsert.getIdCard());if (isOk){//符合规范for (int j=0;j<list.size();j++){//循环表格User haha = list.get(j);if (userInsert.getIdCard().equals(haha.getIdCard())){//查询有没有重复数据ge++;}}if (checkVoluationUtil.userDao.getOneByIdCard(userInsert.getIdCard()).size()>0){//查询数据库是否已存在该数据ok=false;}}}catch (ParseException e) {e.printStackTrace();}if(!isOk){if(errorStr.equals("")){errorStr+="身份证号不规范";}else{errorStr+="、身份证号不规范";}}}else {if(errorStr.equals("")){errorStr+="身份证号为空";}else{errorStr+="、身份证号为空";}}//姓名验证if(userInsert.getName() == null || userInsert.getName().equals("")){if(errorStr.equals("")){errorStr+="姓名为空";}else{errorStr+="、姓名为空";}}//性别验证if(userInsert.getGenderId() != null && !userInsert.getGenderId().equals("")){//非空验证List<Gender> genderlist = checkVoluationUtil.genderDao.getOneByContent(userInsert.getGenderId()); //根据内容查询idif(genderlist.size() == 0){if(errorStr.equals("")){errorStr+="性别不存在";}else{errorStr+="、性别不存在";}}else if (genderlist.size() > 1){if(errorStr.equals("")){errorStr+="请补全性别";}else{errorStr+="、请补全性别";}}else{userInsert.setGenderId(genderlist.get(0).getId());}}else {if(errorStr.equals("")){errorStr+="性别为空";}else{errorStr+="、性别为空";}}//年龄验证if(userInsert.getAge() != null && !userInsert.getAge().equals("")){//非空验证if(Integer.parseInt(userInsert.getAge())<=0){if(errorStr.equals("")){errorStr+="年龄不符合规范";}else{errorStr+="、年龄不符合规范";}}}else {if(errorStr.equals("")){errorStr+="年龄为空";}else{errorStr+="、年龄为空";}}if(errorStr.equals("") && ge == 1 && ok){//判断有没有出错insertList.add(userInsert);//放入正确的列表}else{if (!ok){errorStr="此表格数据已导入";}if (ge>1){errorStr="数据重复";}errorStr+="-无法导入";userError.setErrorInfo(errorStr);//放入错误信息errorList.add(userError);//放入错误的列表}}resultMap.put("succeed",insertList);//正确列表resultMap.put("error",errorList);//错误列表return resultMap;}/**功能：身份证的有效验证* @param IDStr* 身份证号* @return true 有效：false 无效* * @throws ParseException* */public static boolean IDCardValidate(String IDStr) throws ParseException {String[] ValCodeArr = {"1", "0", "X", "9", "8", "7", "6", "5", "4", "3", "2" };String[] Wi = {"7", "9", "10", "5", "8", "4", "2", "1", "6", "3", "7", "9", "10", "5", "8", "4", "2" };String Ai = "";// ================ 号码的长度18位 ================if (IDStr.length() != 18) {return false;}// ================ 数字除最后以为都为数字 ================if (IDStr.length() == 18) {Ai = IDStr.substring(0, 17);}if (isNumeric(Ai) == false) {//errorInfo = "身份证15位号码都应为数字 ; 18位号码除最后一位外，都应为数字。";return false;}// ================ 出生年月是否有效 ================String strYear = Ai.substring(6, 10);// 年份String strMonth = Ai.substring(10, 12);// 月份String strDay = Ai.substring(12, 14);// 日if (isDate(strYear + "-" + strMonth + "-" + strDay) == false) {//errorInfo = "身份证生日无效。";return false;}GregorianCalendar gc = new GregorianCalendar();SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd");try {if ((gc.get(Calendar.YEAR) - Integer.parseInt(strYear)) > 150 || (gc.getTime().getTime() - s.parse(strYear + "-" + strMonth + "-" + strDay).getTime()) < 0) {//errorInfo = "身份证生日不在有效范围。";return false;}} catch (NumberFormatException e) {e.printStackTrace();} catch (ParseException e) {e.printStackTrace();}if (Integer.parseInt(strMonth) > 12 || Integer.parseInt(strMonth) == 0) {//errorInfo = "身份证月份无效";return false;}if (Integer.parseInt(strDay) > 31 || Integer.parseInt(strDay) == 0) {//errorInfo = "身份证日期无效";return false;}// ================ 地区码时候有效 ================Hashtable h = GetAreaCode();if (h.get(Ai.substring(0, 2)) == null) {//errorInfo = "身份证地区编码错误。";return false;}// ================ 判断最后一位的值 ================int TotalmulAiWi = 0;for (int i = 0; i < 17; i++) {TotalmulAiWi = TotalmulAiWi + Integer.parseInt(String.valueOf(Ai.charAt(i))) * Integer.parseInt(Wi[i]);}int modValue = TotalmulAiWi % 11;String strVerifyCode = ValCodeArr[modValue];Ai = Ai + strVerifyCode;if (IDStr.length() == 18) {if (Ai.equals(IDStr) == false) {//errorInfo = "身份证无效，不是合法的身份证号码";return false;}} else {return true;}return true;}/*** 功能：设置地区编码** * @return Hashtable 对象* */@SuppressWarnings("unchecked")private static Hashtable GetAreaCode() {Hashtable hashtable = new Hashtable();hashtable.put("11", "北京");hashtable.put("12", "天津");hashtable.put("13", "河北");hashtable.put("14", "山西");hashtable.put("15", "内蒙古");hashtable.put("21", "辽宁");hashtable.put("22", "吉林");hashtable.put("23", "黑龙江");hashtable.put("31", "上海");hashtable.put("32", "江苏");hashtable.put("33", "浙江");hashtable.put("34", "安徽");hashtable.put("35", "福建");hashtable.put("36", "江西");hashtable.put("37", "山东");hashtable.put("41", "河南");hashtable.put("42", "湖北");hashtable.put("43", "湖南");hashtable.put("44", "广东");hashtable.put("45", "广西");hashtable.put("46", "海南");hashtable.put("50", "重庆");hashtable.put("51", "四川");hashtable.put("52", "贵州");hashtable.put("53", "云南");hashtable.put("54", "西藏");hashtable.put("61", "陕西");hashtable.put("62", "甘肃");hashtable.put("63", "青海");hashtable.put("64", "宁夏");hashtable.put("65", "新疆");hashtable.put("71", "台湾");hashtable.put("81", "香港");hashtable.put("82", "澳门");hashtable.put("91", "国外");return hashtable;}/** 功能：判断字符串是否为数字** @param str** @return* */private static boolean isNumeric(String str) {Pattern pattern = pile("[0-9]*");Matcher isNum = pattern.matcher(str);if (isNum.matches()) {return true;} else {return false;}}/*** 功能：判断字符串是否为日期格式** @return*/public static boolean isDate(String strDate) {Pattern pattern = pile("^((\\d{2}(([02468][048])|([13579][26]))[\\-\\/\\s]?((((0?[13578])|(1[02]))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])))))|(\\d{2}(([02468][1235679])|([13579][01345789]))[\\-\\/\\s]?((((0?[13578])|(1[02]))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|(1[0-9])|(2[0-8]))))))(\\s(((0?[0-9])|([1-2][0-3]))\\:([0-5]?[0-9])((\\s)|(\\:([0-5]?[0-9])))))?$");Matcher m = pattern.matcher(strDate);if (m.matches()) {return true;} else {return false;}}}

ExeclClass工具类

import java.util.List;public class ExeclClass<T> {private static final long serialVersionUID = 1L;/** 表头 */private String [] columnNames;/** 数据 */private List<T> dataLsit;public String[] getColumnNames() {return columnNames;}public void setColumnNames(String[] columnNames) {this.columnNames = columnNames;}public List<T> getDataLsit() {return dataLsit;}public void setDataLsit(List<T> dataLsit) {this.dataLsit = dataLsit;}}

演示

1.之前数据库为0条

2.这是要导入的数据

没问题的数据有：测试一、测试三、测试六、测试八

3 导入

4.查看导入的数据

5.查看错误列表

6.操作错误列表

我们将：测试二、测试五修改导入

7.如果不修改直接点击保存

8.修改后点击保存测试五也一样

9.查看数据库

10.其他的择可以删除或者继续保存，这里就进行删除了

11.其他的继续删除即可

12.我们在导入一次数据

所以本次一条数据也进不去注意：上方代码以判断身份证是否已导入优先，所以我导入时将3个重复的身份证号更换了

13.重新导入结果

14，点击导出错误列表会下载表格数据

遇到的错误

1.上传文件提交数据时

查看原因

2.java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell错

获取Excel数据时number无法转String错

解决——在获取之间先转下类型如下获取姓名时

row.getCell(k).setCellType(CellType.STRING); //将此单元设置为String
s.setName(row.getCell(k++).getStringCellValue().replace(" ", “”)); //在获取去空格

详细的导出数据

点击查看

下载源码

点击下载

导入excel表格到数据库导入excel表格到数据库代码根据excel表格路径将数据导入到数据库验证要导入的excel表格数据根据路径获取MultipartFile FileItem文件

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。

导入excel表格到数据库 导入excel表格到数据库代码 根据excel表格路径将数据导入到

导入excel表格到数据库、根据路径导入excel表格到数据库代码、根据excel表格路径将数据导入到数据库、验证要导入的excel表格数据、根据路径获取MultipartFile、FileItem文件

导入数据前台

导入数据后台

导入xml

Controller层

UserController

Controller层上传的为文件路径

根据路径获取MultipartFile、FileItem文件

UserController

service层

serviceImpl层

dao层

UserDao

GenderDao

xml层

UserMapper.xml

GenderMapper.xml

实体类

User

Gender实体类

工具类

ExcelReadUtils类

CheckVoluationUtil类

ExeclClass工具类

演示

遇到的错误

详细的导出数据

下载源码

导入excel表格到数据库导入excel表格到数据库代码根据excel表格路径将数据导入到