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

控制层

@RequestMapping(value="upload")
    @ResponseBody
    public String  upload(MultipartFile file){
        try {
             String fileName = file.getOriginalFilename();
             // 获取上传文件的输入流
             InputStream inputStream = file.getInputStream();
             // 调用工具类中方法，读取excel文件中数据
             List<Map<String, Object>> sourceList = ImportExcel.readExcel(fileName, inputStream);
             // 将对象先转为json格式字符串，然后再转为List<SysUser> 对象
             ObjectMapper objMapper = new ObjectMapper();
             String infos = objMapper.writeValueAsString(sourceList);
             // json字符串转对象
             List<User> list = objMapper.readValue(infos, new TypeReference<List<User>>() {});
             // 批量添加
             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<Map<String, Object>> 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<Map<String, Object>> list = new ArrayList<>();
         for(int i = 1; i <= lastRowNum; i++ ){
             Map<String, Object> 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")注解