java导入excel数据

基于excel模板

前端

   选择文件:
        <ta-upload name="file"
                   ref="file"
                   :before-upload="beforeUpload"
                   :customRequest="fnUpload"
                   :remove="handleRemove"
                   :fileList="fileList"
                   accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
        >
          <ta-button>
            <ta-icon type="upload"/>
            上传文件
          </ta-button>
        </ta-upload>

    //文件上传开始
    beforeUpload(file) {
      const reg = new RegExp('.(xls|xlsx)$', 'i')
      if (!reg.test(file.name)) {
        this.$message.error('请传入xls或xlsx类型文件')
        return false
      }
      if (Math.ceil(file.size / (1024 * 1024)) > 50) {
        this.$message.warn('文件最多支持50MB')
        return false
      }
      this.fileList = []
      this.currentFile = {}
      this.fileList = [...this.fileList, file]
      this.currentFile = file

    },
//检查模板是否合法
    fnUpload: function () {
      let submitParameter = {
        url: 'mmsDoctorAuthority/uploadDetailDataCheck',
        data: {
          file: this.currentFile, //文件参数，可以是文件数组
        },
        method: 'POST',
        //文件提交时，采用FormData方式提交参数
        isFormData: true,
      }
      this.Base.submit(null, submitParameter).then((res) => {
        console.log(" res.data", res.data)
        if (res.serviceSuccess) {

          //验证模板是否有效
          if (res.code == 200 && res.data.errorMsg == null) {
            this.data = res.data.ret
          } else {
            this.$message.error('不合法的模板')
          }
        } else {
          this.$message.error('不合法的模板')
        }

      })

    },

后端，检查模板是否合法，并读取excel数据，返回一个list集合

    /**
     * 检查导入数据是否合法
     *
     * @param file 文件
     */
    @PostMapping("uploadDetailDataCheck")
    public void uploadDetailDataCheck(@RequestPart("file") MultipartFile file) {
       
        try {
            List<Map<String, Object>> list = readService.uploadDetailDataCheck(file);
            setData("ret", list);
        } catch (Exception e) {
            setData("errorMsg", "不合法的模板");
        }
    }

由于模板是第三行开始是数据，所以这里从第三行开始读取

    public List<Map<String, Object>> uploadDetailDataCheck(MultipartFile file) {

        //将流转换java对象
        String originalFilename = file.getOriginalFilename();
        List<Map<String, Object>> list = new ArrayList<>();
        try (InputStream inputStream = file.getInputStream()) {
            assert originalFilename != null;
            ReadExcelUtils excelReader = new ReadExcelUtils(inputStream, originalFilename.substring(originalFilename.lastIndexOf(".")));
            Map<Integer, Map<Integer, Object>> map = excelReader.readExcelContent();
            if (ValidateUtil.isEmpty(map)) {
                return Collections.emptyList();
            }
            //取出标题列
            Map<Integer, Object> titleMap = map.get(1);
            for (int i = 3; i < map.size(); i++) {
                Map<Integer, Object> objMap = map.get(i);
                if (!ValidateUtil.isEmpty(objMap) && ValidateUtil.isEmpty(objMap.get(0))) {
                    continue;
                }
                LinkedHashMap<String, Object> m = new LinkedHashMap<>();
                for (Map.Entry<Integer, Object> entry : titleMap.entrySet()) {
                    Integer key = entry.getKey();
                    Object value = entry.getValue();
                    m.put(getObjectString(value).split("\\(")[0], getObjectString(objMap.get(key)).replace("，", ","));
                }
                list.add(m);
            }
        } catch (Exception e) {
            throw new AppException("读取模板失败");
        }
        return list;
    }

工具类，读取excel数据

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/**
 * @describe
 */
@Slf4j
public class ReadExcelUtils {

    private Logger logger = LoggerFactory.getLogger(ReadExcelUtils.class);
    private Workbook wb;
    private Sheet sheet;
    private Row row;

    public static String[] patterns = {"yyyy/MM/dd HH:mm:ss", "yyyy-MM-dd HH:mm:ss", "yyyy/MM/dd HH:mm", "yyyy-MM-dd HH:mm", "yyyy-MM-dd", "yyyy-MM", "HH:mm", "yyyy/MM/dd", "yyyy/MM", "yyyy"};

    public ReadExcelUtils(InputStream is, String ext) {
        try {
            if (IConstants.FILE_FORMAT_XLS.equals(ext)) {
                wb = new HSSFWorkbook(is);
            } else if (IConstants.FILE_FORMAT_XLSX.equals(ext)) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = null;
            }
        } catch (FileNotFoundException e) {
            logger.error("FileNotFoundException", e);
        } catch (IOException e) {
            logger.error("IOException", e);
        }
    }

    /**
     * 获取表头
     *
     * @return String 表头内容的数组
     */
    public String[] readExcelTitle() throws Exception {
        if (wb == null) {
            throw new Exception("Workbook对象为空！");
        }
        sheet = wb.getSheetAt(0);
        row = sheet.getRow(0);
        // 标题总列数
        int colNum = row.getPhysicalNumberOfCells();
        System.out.println("colNum:" + colNum);
        String[] title = new String[colNum];
        for (int i = 0; i < colNum; i++) {
            title[i] = row.getCell(i).getCellFormula();
        }
        return title;
    }

    /**
     * 读取Excel数据内容
     *
     * @return Map 包含单元格数据内容的Map对象
     */
    public Map<Integer, Map<Integer, Object>> readExcelContent() throws Exception {
        if (wb == null) {
            throw new Exception("Workbook对象为空！");
        }
        Map<Integer, Map<Integer, Object>> content = new HashMap<>(8);

        sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(1);
        int colNum = row.getPhysicalNumberOfCells();
        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = 0; i <= rowNum; i++) {
            row = sheet.getRow(i);
            int j = 0;
            Map<Integer, Object> cellValue = new HashMap<Integer, Object>();
            while (j < colNum) {
                Object obj = getCellFormatValue(row.getCell(j));
                cellValue.put(j, obj);
                j++;
            }
            content.put(i, cellValue);
        }
        return content;
    }

    /**
     * 根据Cell类型设置数据
     *
     * @param cell
     * @return Object
     */
    private Object getCellFormatValue(Cell cell) {
        Object cellValue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                if (DateUtil.isCellDateFormatted(cell)) {
                    String dateFormat = getDateFormat(cell.getDateCellValue());
                    cellValue = DateFormatUtils.format(cell.getDateCellValue(), dateFormat);
                } else {
                    NumberFormat nf = NumberFormat.getInstance();
                    cellValue = String.valueOf(nf.format(cell.getNumericCellValue())).replace(",", "");
                }
            } else if (cell.getCellTypeEnum() == CellType.STRING) {
                cellValue = String.valueOf(cell.getStringCellValue());
            } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                cellValue = String.valueOf(cell.getBooleanCellValue());
            } else if (cell.getCellTypeEnum() == CellType.ERROR) {
                cellValue = "错误类型";
            }
        }
        return cellValue;
    }

    private String getDateFormat(Date date) {
        String format = "yyyy-MM-dd";
        if (ValidateUtil.isEmpty(date)) {
            return format;
        }
        // 遍历模式数组，判断日期对象的格式
        for (String pattern : patterns) {
            SimpleDateFormat sdf = new SimpleDateFormat(pattern);
            try {
                // 尝试将日期对象格式化为当前模式
                sdf.parse(sdf.format(date));
                format = pattern;
                break;
            } catch (Exception e) {
                log.info("Error parsing date：{}", e.getMessage());
            }
        }
        return format;
    }
}

前端拿到返回的list数据后可自定义入库