poi excel 导入 mysql_poi实现excel数据导入到mysql数据库（加上excel的验证）

最新推荐文章于 2022-03-13 17:48:27 发布

首码

最新推荐文章于 2022-03-13 17:48:27 发布

阅读量177

点赞数

文章标签： poi excel 导入 mysql

本文链接：https://blog.csdn.net/weixin_31509859/article/details/113190265

版权

首先先简单介绍一下本文的代码，主要是通过前后台的交互，读取excel中的数据存入到数据库中。(另有关于sheet为空，sheet中某行某列数据为空跟数据库中对应字段不能为空而导致的不能存入的判断等方法的思路)。

1. 本文针对的是1个或多个sheet的情况

2. 默认每个sheet第一行存放的是对应数据库的字段名称。比如：name,age等，这样写的原因是比较灵活，不同的sheet可以存不同的字段值，具体参考下面的图。

excel格式如图：

代码部分：

首先要在pom.xml中添加poi的依赖

3 org.apache.poi

4 poi

5 3.15

8 org.apache.poi

9 poi-ooxml-schemas

10 3.15

13 org.apache.poi

14 poi-ooxml

15 3.15

前台form：

要注意的是：

1. input的type类型要是file。

2. input的name属性要跟后台接收的requestparam参数一致。

3. form的id后面js代码会用到。

4. 其他的属性可以根据情况自行调整。

前台button：

要注意的是：

1. 按钮的id和onclick事件后面会用到。

2. 其他的属性根据情况自行调整。

前台js：

functionupload() {//document.getElementById('importFile').files[0].name;//可以获取文件名称

var formData = new FormData($("#controlform")[0]);

$.ajax({

type:'POST',

url:'/customsupplement/reportList/uploadExcel?formid=12131232131',//参数可以根据情况传递

data:formData,

async:false,

cache:false,

contentType:false,

processData:false, //必须要

success: function(data) {

$("#importFile").val("");if (data.valid == "true") {

alert("导入成功");

}else{

alert(data.valid);//弹出的就是报错的信息

}

}，

error:function(data){

alert("系统错误");

}

});

}functionimportFile() {

$("#importFile").click();

}

后台代码(无检查部分)：

1 @RequestMapping(method = RequestMethod.POST, value = "/uploadExcel")2 @ResponseBody3 public Map uploadExcel(@RequestParam(value = "file", required = false) MultipartFile inputFile, String formid) throws IOException {//其中value=file的file要与form中input标签里的name属性的值一致，formid为传过来的参数，可有可无，4 //用于存放所有不正确的信息如果是 "" 说明没有报错可以执行sql

5 String msg = "";6 //用于返回前台的标志

7 String valid = "";8 //用于存放将要执行的所有sql

9 List sqlList = newArrayList();10 //用于返回前台标志的map

11 Map mp = newHashMap();12 if(inputFile.isEmpty()) {13 msg += "此excel文件为空";14 }15 if("".equals(msg)){16 //通过formid查询操作的表名称,这里可以根据业务自行做

17 String tablename =reportListService.getTableName(formid);18 //获得Workbook工作薄对象

19 Workbook workbook = null;20 //获得文件名

21 String fileName =inputFile.getOriginalFilename();22 //获取excel文件的io流

23 InputStream is =inputFile.getInputStream();24 //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象

25 if(fileName.endsWith("xls")){26 workbook = new HSSFWorkbook(is);//2003

27 }else if(fileName.endsWith("xlsx")){28 workbook = new XSSFWorkbook(is);//2007 及2007以上

29 }30 //获取sheet的数量

31 int sheetnum =workbook.getNumberOfSheets();32 //存在sheet并且没有报错信息

33 if (sheetnum > 0 && "".equals(msg)) {34 //将要执行的sql拼接好并放入list中

35 sqlList =dealsheet(workbook,sheetnum,tablename);36 }37 //list中有要执行的sql数据

38 if (sqlList.size() > 0) {39 //批量插入

40 valid =reportListService.insertSqlList(sqlList);41 }42 }43

44 if (valid == "true") {45 mp.put("valid", "true");46 } else{47 mp.put("valid", msg);48 }49 returnmp;50 }

dealsheet方法：

1 public static List dealsheet(Workbook workbook,intsheetnum,String tablename){2 //用于存放sql集合

3 List sqlList = newArrayList();4 for(int s=0;s

6 String idSign = "false";7 //获取要解析的表格(第一个sheet)

8 HSSFSheet sheet =(HSSFSheet) workbook.getSheetAt(s);9 //获取总行数(包括列头)

10 int arrays =sheet.getPhysicalNumberOfRows();11 //行数为0，说明此sheet没有数据，直接忽略掉。

12 if(arrays == 0){13 continue;14 }15 //用于存储insertsql的前半部分(取sheet的第一行)

16 String sql = "";17 //用于存储insertsql的后半部分

18 String sqltemp = "";19 //获取总列数

20 int rows = sheet.getRow(0).getPhysicalNumberOfCells();21 if(arrays>0){//拼接insert sql的前半部分(列头)

22 HSSFRow row = sheet.getRow(0);//获得要解析的行(第一行列头部分)

23 for(int i=0;i

26 sql += cell.getStringCellValue()+",";27 if("ID".equals(cell.getStringCellValue().toUpperCase())){28 idSign = "true";//sheet中有id列

29 }30 }31 //sheet中有id列

32 if("true".equals(idSign)){33 sql = "insert into "+tablename+" ("+sql.substring(0,sql.length()-1)+") values ";34 }else{35 sql = "insert into "+tablename+" (id,"+sql.substring(0,sql.length()-1)+") values ";36 }37 }38 //拼接insert sql的后半部分(值)

39 for (int k = 1; k

41 HSSFRow row =sheet.getRow(k);42 for(int f=0;f

46 String datevalue = DateFormatUtils.format(cell.getDateCellValue(), "yyyy-MM-dd HH:mm:ss");47 sqltemp += datevalue+",";48 }else{49 cell.setCellType(CellType.STRING);//读取数据前设置单元格类型如果不设置有可能会报错

50 sqltemp += cell.getStringCellValue()+",";51 }52 }else{53 sqltemp += ",";54 }55 }56 //sheet中有id列

57 if("true".equals(idSign)){58 sqlList.add(sql+"('"+sqltemp.substring(0,sqltemp.length()-1).replaceAll(",","','")+"')");59 }else{60 sqlList.add(sql+"(UUID(),'"+sqltemp.substring(0,sqltemp.length()-1).replaceAll(",","','")+"')");61 }62 sqltemp = "";63 }64 }65 returnsqlList;66 }

检查的内容：

1. 查出为空的sheet，自动忽略。

2. 某行某列对应的字段如果为空是否会影响到insert sql的执行。

3. excel中每个sheet第一行存放的字段名称是否真实存在于数据库对应表中。

4. excel中是否有id列，如果没有就在后台自动添加一个(这个可以根据情况自行决定)。

验证部分：

首先mysql中有一个语句可以直接查出某个表的对应字段名称，长度，是否允许为空的信息，那么我们就可以直接拿过来用了，因为上面的代码中我们已经写了如何去遍历sheet中的每个单元格的内容，那么同理，我们可以在遍历的同时加上下面的这些验证，也可以单独写一个方法去在拼接sql之前就检验一下excel中的数据是否符合要求。同时将不符合的信息放入msg中，一起返回给前台即可。

1 Select COLUMN_NAME,DATA_TYPE,COLUMN_TYPE,IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS Where table_name = '表名' AND table_schema = '数据库名'

注意：

1. column_name是字段名称。(用来验证sheet中第一行的字段值是否真实存在于数据库中)

2. is_nullable意思是是否可以为空。(可以验证sheet中某行某列的值如果为空是否可以存入数据库)

3. colunm_type意思是字段类型和长度。(可以取出varchar类型的长度，并跟sheet中的做对比)

下面补充一个判断某个字符串长度的方法(汉字2个字符，其他一个字符的标准)

1 public static intString_length(String value) {2 int valueLength = 0;3 String chinese = "[\u4e00-\u9fa5]";4 for (int i = 0; i < value.length(); i++) {5 String temp = value.substring(i, i + 1);6 if(temp.matches(chinese)) {7 valueLength += 2;8 } else{9 valueLength += 1;10 }11 }12 returnvalueLength;13 }

最后，还要提醒一下，在excel中如果存储时间类型的数据，格式可能会不对，这个时候需要我们设置一下excel这一列的格式。举个例子：比如我们想要将2019/10/22 11:45:47存入某个单元格中，如果直接存就会如下图一样，excel会自动去掉秒的部分。

解决办法如下：在单元格/列/行上右键--设置单元格格式--数字--自定义，之后如图所示填写保存 yyyy-m-d h:mm:ss，那么保存之后的就是年月日时分秒格式的了。

持续更新！！

首码

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
poi excel 导入 mysql_poi实现excel数据导入到mysql数据库（加上excel的验证）

首先先简单介绍一下本文的代码，主要是通过前后台的交互，读取excel中的数据存入到数据库中。(另有关于sheet为空，sheet中某行某列数据为空跟数据库中对应字段不能为空而导致的不能存入的判断等方法的思路)。1. 本文针对的是1个或多个sheet的情况2. 默认每个sheet第一行存放的是对应数据库的字段名称。比如：name,age等，这样写的原因是比较灵活，不同的sheet可以存不同的字段值，...
复制链接

扫一扫