Excel文件上传并解析入库——通用方法（未处理获取合并单元格的值）

Excel文件上传需要的配置见另一篇博客 https://blog.csdn.net/yywdys/article/details/79490992

Excel文件上传并解析的工具类ExcelUploadUtil.java

package com.example.demo.controller;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description: Excel文件解析工具类
 * @Author: zhaowang
 * @Date: 2018/9/25 11:16
 * @Version: 1.0
 */
@Slf4j
@Component
public class ExcelUploadUtil {


    /**
     *
     * @param file 上传的文件
     * @param path 文件保存路径
     * @return
     */
    // TODO 获取合并单元格的值
    public  Map<Integer,List<List<? super Object>>> upload(MultipartFile file,String path) {
        //创建读取excel的类(区分excel2003和2007文件)
        Workbook workbook = createWorkBook(file,path);
        //excel每一行看做一个List,作为value;sheet页数作为key
        Map<Integer,List<List<? super Object>>> map = new HashMap<>();
        // 得到sheet页
        for(int i = 0;i<workbook.getNumberOfSheets();i++) {
            Sheet sheet = workbook.getSheetAt(i);
            // 得到Excel的行数
            int totalRows = sheet.getPhysicalNumberOfRows();
            // 得到Excel的列数(前提是有行数)
            int totalCells = 0;
            if (totalRows > 1 && sheet.getRow(0) != null) {
                totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
            }
            // 循环Excel行数
            List<List<? super Object>> list = new ArrayList<>();
            for (int r = 1; r < totalRows; r++) {
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                // 循环Excel的列
                if (row != null) {
                    List<? super Object> valueList = new ArrayList<>();
                    for (int c = 0; c < totalCells; c++) {
                        Cell cell = row.getCell(c);
                        DecimalFormat df = new DecimalFormat("0");
                        try {
                            if (null != cell) {
                                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                    String value = df.format(cell.getNumericCellValue());
                                    valueList.add(value);
                                } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                                    if (!StringUtils.isEmpty(cell.getStringCellValue())) {
                                        valueList.add(cell.getStringCellValue());
                                    }
                                } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                                    valueList.add("");
                                } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                                    valueList.add(cell.getBooleanCellValue());
                                } else if(cell.getCellType() == Cell.CELL_TYPE_ERROR) {
                                    valueList.add("非法字符");
                                } else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                                    valueList.add(cell.getCellFormula());
                                };
                            }
                        } catch (Exception e) {
                            log.error("\n==========解析Excel单元格异常==========", e);
                        }
                    }
                    list.add(valueList);
                }
            }
            map.put(i, list);
        }
        return map;
    }

    public  Workbook createWorkBook(MultipartFile file, String path) {
        multipartToFile(file,path);
        File f = createNewFile(file,path);
        Workbook workbook = null;
        try {
            InputStream is = new FileInputStream(f);
            workbook = WorkbookFactory.create(is);
            is.close();
        } catch (InvalidFormatException | IOException e) {
            log.error("\n==========文件流转换为Workbook对象异常============", e);
        }
        return workbook;
    }

    public  void multipartToFile(MultipartFile multfile, String path) {
        File file = createNewFile(multfile,path);
        try {
            multfile.transferTo(file);
        } catch (IOException e) {
            log.error("\n上传的文件保存失败");
        }
    }

    public  File createNewFile(MultipartFile multfile,String path) {
        String fileName = multfile.getOriginalFilename();
        File file = new File(path + fileName);
        File parentFile = file.getParentFile();
        if(!parentFile.exists()) parentFile.mkdirs();
        try {
            file.createNewFile();
        } catch (IOException e) {
            log.error("\n新文件创建失败");
        }
        return file;
    }
}

利用工具类将解析的结果保存入库示例

ExcelUploadController.java

package com.example.demo.controller;

import com.example.demo.entity.Test;
import com.example.demo.service.TestService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @Description:
 * @Author: zhaowang
 * @Date: 2018/9/29 22:33
 * @Version: 1.0
 */
@RestController
@Slf4j
@RequestMapping("demo/excel")
public class ExcelUploadController {

    @Autowired
    private TestService testService;

    @Autowired
    private ExcelUploadUtil excelUploadUtil;

    @Transactional(value = "transactionManager",propagation = Propagation.REQUIRED)
    @RequestMapping(value = "/upload", method = {RequestMethod.POST})
    public void upload(@RequestParam("file") MultipartFile file) {

        long startTime = System.currentTimeMillis();

        String path = "D:/upload";
        Map<Integer,List<List<? super Object>>> map = excelUploadUtil.upload(file,path);
        //将解析出的数据批量插入或更新到数据库中
        List<Test> allList = new ArrayList<>();
        for(Integer i:map.keySet()) {
            List<List<? super Object>> valueList = map.get(i);
            for(List<? super Object> subList:valueList) {
                Test test = new Test();
                for(Object o:subList) {
                    int length = subList.size();
                    if(length>0) {
                        for (int k = 0; k < length; k++) {

                            if (k == 0) {
                                test.setFirst_menu((String) subList.get(0));
                                continue;
                            }else  if (k == 1 ) {
                                if(k < length) {
                                    Integer report_id = subList.get(1) == null ? null : Integer.valueOf((String) subList.get(1));
                                    test.setReport_id(report_id);
                                } else {
                                    test.setReport_id(null);
                                }
                                continue;
                            } else if (k == 2 ) {
                                if(k < length)  test.setReport_name((String) subList.get(2));
                                else test.setReport_name(null);
                                continue;
                            } else if (k == 3 ) {
                                if(k < length) test.setFund_column((String) subList.get(3));
                                else test.setFund_column(null);
                                continue;
                            } else  if (k == 4) {
                                if(k < length)  test.setColum((String) subList.get(4));
                                else test.setColum(null);
                                continue;
                            }
                        }
                    }
                }
                allList.add(test);
            }
        }
        testService.saveAll(allList);

        long endTime = System.currentTimeMillis();
        log.info("\n解析Excel文件并入库的总时间为:{}",(endTime-startTime));
    }
}

解析Excel具体做法

    将Execl每一行的记录看做一个List<? super Object> ,一个sheet页的所有list则为List<List<? super Object>>。将每个sheet页的内容保存到Map中，key为sheet页码，vaue为List<List<? super Object>>。解析完Excel，获得并返回HashMap。然后根据model，遍历map的value。逐一set给实体属性，获得所有对象，最后批量插入model。

注意事项

1.如果项目基于SpringBoot，内置MultipartFile的bean,因此不需要额外配置。

2.建议添加事务回滚注解，防止因执行方法抛出异常导致产生脏数据。