批量excel导入mysql数据库_上传excel表格批量导入数据到数据库

控制层

@RequestMapping(value="upload")

@ResponseBody

public String upload(MultipartFile file){

try {

String fileName = file.getOriginalFilename();

// 获取上传文件的输入流

InputStream inputStream = file.getInputStream();

// 调用工具类中方法，读取excel文件中数据

List> sourceList = ImportExcel.readExcel(fileName, inputStream);

// 将对象先转为json格式字符串，然后再转为List 对象

ObjectMapper objMapper = new ObjectMapper();

String infos = objMapper.writeValueAsString(sourceList);

// json字符串转对象

List list = objMapper.readValue(infos, new TypeReference>() {});

// 批量添加

userService.addUsers(list);

return "success";

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

return e.getMessage();

}

}

ImportExcel工具类

package com.example.demo.User.service;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

public class ImportExcel {

public static boolean isXls(String fileName){

// (?i)忽略大小写

if(fileName.matches("^.+\\.(?i)(xls)$")){

return true;

}else if(fileName.matches("^.+\\.(?i)(xlsx)$")){

return false;

}else{

throw new RuntimeException("格式不对");

}

}

public static List> readExcel(String fileName, InputStream inputStream) throws Exception{

boolean ret = isXls(fileName);

Workbook workbook = null;

// 根据后缀创建不同的对象

if(ret){

workbook = new HSSFWorkbook(inputStream);

}else{

workbook = new XSSFWorkbook(inputStream);

}

Sheet sheet = workbook.getSheetAt(0);

// 得到标题行

Row titleRow = sheet.getRow(0);

int lastRowNum = sheet.getLastRowNum();

int lastCellNum = titleRow.getLastCellNum();

List> list = new ArrayList<>();

for(int i = 1; i <= lastRowNum; i++ ){

Map map = new HashMap<>();

Row row = sheet.getRow(i);

for(int j = 0; j < lastCellNum; j++){

// 得到列名

String key = titleRow.getCell(j).getStringCellValue();

Cell cell = row.getCell(j);

cell.setCellType(CellType.STRING);

map.put(key, cell.getStringCellValue());

}

list.add(map);

}

workbook.close();

return list;

}

}

【注意】excel每个列名要和数据库字段名一致！！

对于有date类型的数据，excel输入2019-12-12会变为日期格式数据，日期类型传到后台时会转成字符串，其格式会出错，就无法转换Date类型，

所以Excel表格一定要将时间相关数据用文本格式存储！！！

数据库对应实体类与时间相关属性要添加@DateTimeFormat(pattern="yyyy-MM-dd")注解