简单的上传excel文件 并且添加数据到数据库

1.引入pom文件（预防报错 保持各个jar包版本一致）

    <dependency>  
        <groupId>org.apache.poi</groupId>  
        <artifactId>poi-ooxml</artifactId>  
        <version>3.17</version>  
    </dependency>  
    <dependency>  
        <groupId>org.apache.poi</groupId>  
        <artifactId>poi-ooxml-schemas</artifactId>  
        <version>3.17</version>  
    </dependency>  
    <dependency>  
        <groupId>org.apache.poi</groupId>  
        <artifactId>poi</artifactId>  
        <version>3.17</version>  
    </dependency> 

2.导入文件的工具类

package com.yami.shop.admin.config;

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 {
    // abc.xls
    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;
    }
}

3.controller控制层

package com.yami.shop.admin.controller;

import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.yami.shop.admin.config.ImportExcel;
import com.yami.shop.bean.model.TzJobSet;
import com.yami.shop.common.bean.Result;
import com.yami.shop.service.ITzJobSetService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/admin/staff")
public class TestExcel {

    @Autowired
    private ITzJobSetService tzJobSetService;

    @PostMapping("/import")
    @Transactional
    public Boolean importExcel(@RequestParam MultipartFile mFile){
        Result result =new Result();
        try {
            String fileName = mFile.getOriginalFilename();
            // 获取上传文件的输入流
            InputStream inputStream = mFile.getInputStream();
            // 调用工具类中方法，读取excel文件中数据
            List<Map<String, Object>> sourceList = ImportExcel.readExcel(fileName, inputStream);

            // 将对象先转为json格式字符串，然后再转为List<TzJobSet> 对象  TzJobSet:表对应的实体类
            ObjectMapper objMapper = new ObjectMapper();
            String infos = objMapper.writeValueAsString(sourceList);

            // json字符串转对象
            List<TzJobSet> list = objMapper.readValue(infos, new TypeReference<List<TzJobSet>>() {});

            // 批量添加  项目用的mybatis-pius 框架 saveBatch批量添加的方法是mybatis-pius自带的
            boolean b = tzJobSetService.saveBatch(list);
            return b;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return false;
    }
}

 4.上传文件内容格式

 

 --------------------------------请多多指教