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

1、导入poi相关依赖

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

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

2、添加ImportExcel工具类

使用工具类读取文件

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、创建excel参数对象

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class ImportData {

    private  String name1;
    private  String name2;
    private  String name3;
}

4、后台controller层处理接收的excel文件

@RestController
@RequestMapping("/test")
public class importExcel{

    @PostMapping("/test")
    public String importExcel(@RequestParam MultipartFile mFile){
        try {
            String fileName = mFile.getOriginalFilename();
            // 获取上传文件的输入流
            InputStream inputStream = mFile.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<ImportData> list = objMapper.readValue(infos, new TypeReference<List<ImportData>>() {});
            list.stream().forEach(li->{
                System.out.println(li);
            });
            // 批量添加

            return "成功！";
        } catch (Exception e) {
            e.printStackTrace();
            return "失败！";
        }
    }
}

5、前端上传文件

<div class="layui-form-item">
            <label class="layui-form-label">选择文件</label>
            <div class="layui-input-block">
                <input type="file" name="mFile" id="no1" class="layui-input">
            </div>
        </div>
        <div class="layui-form-item">
            <input class="layui-btn" style="margin-left: 10%"  id="btn1" type="submit" value="确认导入">
        </div>

6、文件上传相关设置

spring:    
    servlet:
        multipart:
            enabled: true #是否启用http上传处理
            max-request-size: 100MB #最大请求文件的大小
            max-file-size: 20MB #设置单个文件最大长度
            file-size-threshold: 20MB #当文件达到多少时进行磁盘写入

然后ok啦！