Springboot 导入文件夹并把文件里面的全部Excel表格的数据导入数据库，及其遇到Excel值取值问题处理（一个文件夹里面有多个excel表格，一张excel表格有多张sheet表）

综合服务官网 (tmxkj.top)https://tmxkj.top/#/

1.pom.xml 相关依赖

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

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

        <!--zip使用-->
        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-compress -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-compress</artifactId>
            <version>1.21</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
        </dependency>
        <dependency>
            <groupId>org.dom4j</groupId>
            <artifactId>dom4j</artifactId>
            <version>2.1.3</version>
            <scope>compile</scope>
        </dependency>

2.Util工具类方法

这个方法是对文件里面excel表格处理

public class ZipEncodeUtil {
    public static List<String> getExcelFilePaths(String folderPath) {
        File folder = new File(folderPath);
        File[] files = folder.listFiles();
        List<String> excelFilePaths = new ArrayList<>();

        if (files != null) {
            for (File file : files) {
                if (isExcelFile(file)) {
                    excelFilePaths.add(file.getAbsolutePath());
                }
            }
        }

        return excelFilePaths;
    }

    private static boolean isExcelFile(File file) {
        String fileName = file.getName().toLowerCase();
        return fileName.endsWith(".xlsx") || fileName.endsWith(".xls");
    }

}

 取值

import java.text.ParseException;
import java.text.SimpleDateFormat;
import com.nengtou.cloudpivot.api.util.Result;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


@Slf4j
public class ExeclType {
    public static HashMap<Object,String> getExcelType(CellType cellType, Row row, Cell cell, int i, int j) {
        String send = null;
        String title = null;
        HashMap<Object,String> map =new HashMap<>();
        // 根据类型进行相应的处理
        switch (cellType) {
            case STRING:
                //接收字符串类型值
                send =row.getCell(i).getStringCellValue();
                title = row.getCell(j).getStringCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    //接收时间类型值
                    send= String.valueOf(row.getCell(i).getDateCellValue());
                    title = row.getCell(j).getStringCellValue();
                } else {
                    //接收数字类型值
                    send = String.valueOf(row.getCell(i).getNumericCellValue());
                    title = row.getCell(j).getStringCellValue();
                }
                break;
            case BOOLEAN:
                //接收布尔值类型值
                send = String.valueOf(row.getCell(i).getBooleanCellValue());
                title = row.getCell(j).getStringCellValue();

                break;
            case FORMULA:
                //接收函数值类型
                send = row.getCell(i).getCellFormula();
                title = row.getCell(j).getStringCellValue();
                break;
            default:

//                System.out.println("获取不到类型");
                break;
        }
        map.put("value",send);
        map.put("title",title);
        return map;
    }


    //获取值   需要参数分别是Row每行的数据、Cell第几列的索引值,j是第几行值索引,i第几列的值的索引
    public static HashMap<Object,String> getExcelValue( Row row,int cellIndex,FormulaEvaluator formulaEvaluator, int i) {
        HashMap<Object,String> map =new HashMap<>();
       try {
           //获取到到第几单元格cellIndex
           Cell cell = row.getCell(cellIndex);

           // 获取单元格的值的类型
           CellType cellType = cell.getCellType();
           String send = null;
           Boolean bool =false;
           String type =  String.valueOf(cellType);;
           // 根据类型进行相应的处理
           switch (cellType) {//接收字符串类型值
               case _NONE:
               case BLANK://空单元格
                   send = "-";
                   bool = false;
                   break;
               case STRING:

                   String newStr =String.valueOf(row.getCell(i).getStringCellValue());
                   String regex = "^\\d{4}－\\d{2}－\\d{2}$";
                   Pattern pattern = Pattern.compile(regex);
                   Matcher matcher = pattern.matcher(newStr);

                   if (matcher.matches()) {
                       send = newStr.replace("－","-");
                       bool = send.length()>=1;
                   } else {
                       send = newStr;
                       bool = send.length()>=1;
                   }
                   break;
               case NUMERIC: ;
                   if (DateUtil.isCellDateFormatted(cell)) {
                       SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                       String data = sdf.format(row.getCell(i).getDateCellValue());
                       //接收时间类型值
                       send= data;
                       bool = send.length()>=1;
                   }else if(cellType.name().equals("NUMERIC")){
                       CellValue evaluate = formulaEvaluator.evaluate(cell);
                       String dateString = String.valueOf(row.getCell(i).getStringCellValue());
                       if(isValidDate(dateString)){
                           SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                           String data = sdf.format(row.getCell(i).getDateCellValue());
                           send = data;
                           bool = send.length()>=1;
                       }else {
                           if(evaluate.getCellType().name().equals("NUMERIC")) {
                               BigDecimal value =BigDecimal.valueOf(cell.getNumericCellValue());
                               send = String.valueOf(value);
                               bool = send.length()>=1;
                           }else {
                               send = String.valueOf(row.getCell(i).getStringCellValue());
                               bool = send.length()>=1;
                           }
                       }

                   } else {
                       send = String.valueOf(row.getCell(i).getStringCellValue());
                       bool = send.length()>=1;
                   }

                   break;
               case BOOLEAN:
                   //接收布尔值类型值
                   send = String.valueOf(row.getCell(i).getBooleanCellValue());
                   bool = send.length()>=1;

                   break;
               case FORMULA:
                      if(cellType.name().equals("FORMULA")){
                          CellValue evaluate = formulaEvaluator.evaluate(cell);
                          if(evaluate.getCellType().name().equals("NUMERIC")) {
                              BigDecimal value =BigDecimal.valueOf(evaluate.getNumberValue());
                              send = String.valueOf(value);
                              bool = send.length()>=1;
                          }else {
                                  send = row.getCell(i).getCellFormula();
                                  bool = send.length()>=1;
                          }
                      }else if(cellType.name().equals("NUMERIC")) {

                          BigDecimal value =BigDecimal.valueOf(cell.getNumericCellValue());
                          send = String.valueOf(value);
                          bool = send.length()>=1;
                      }else {
                          send = row.getCell(i).getCellFormula();
                          bool = send.length()>=1;
                      }

                   break;
               case ERROR:
                   send = String.valueOf(row.getCell(i).getErrorCellValue());
                   bool = send.length()>=1;
                   break;
               default:
                   send = row.getCell(i).getStringCellValue();
                   bool = send.length()>=1 || send.equals(null);
                   break;
           }
           map.put("value",send);
           map.put("type",type);
           map.put("bool", String.valueOf(bool));
           return map;
       }catch (Exception err){
           //System.out.println("类型值抛出异常不影响");
           //获取到到第几单元格cellIndex
           Cell cell = row.getCell(cellIndex);
           CellType cellType = cell.getCellType();


           map.put("value",null);
           map.put("type",String.valueOf(cellType));
           map.put("bool", String.valueOf(false));
           return map;
       }

    }

    public static boolean isValidDate(String dateString) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy年MM月dd日");
        dateFormat.setLenient(false);
        try {
            dateFormat.parse(dateString);
            return true;
        } catch (ParseException e) {
            return false;
        }
    }

 合并单元格取值

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

@Slf4j
public class ExcelMergedRegion {

    public static String GetMergedRegionValue(Sheet sheet,int rowIndex,int columnIndex)
    {
        try {
            List<CellRangeAddress> list=sheet.getMergedRegions();
            for(int i=0;i<list.size();i++)
            {
                CellRangeAddress cellAddresses=list.get(i);
                int firstRow=cellAddresses.getFirstRow();
                int lastRow=cellAddresses.getLastRow();
                int firstColumm=cellAddresses.getFirstColumn();
                int lastColumm=cellAddresses.getLastColumn();

                if(firstRow<=rowIndex&&lastRow>=rowIndex&&firstColumm<=columnIndex&&lastColumm>=columnIndex)
                {

                    Cell cell=sheet.getRow(firstRow).getCell(firstColumm);
                    CellType cellType=cell.getCellType();
                    if(cellType.name().equals("STRING"))
                    {
                        return cell.getStringCellValue();
                    }
                    else
                    {
                        log.error("合并的单元格的值不是字符串");
                        return "0";
                    }
                }

            }

            return "0";
        }catch (Exception e){
            return "0";
        }

    }

}

 3.Controller 

/**
 * @Description Excel 表格数据导入
 * @Author yxc
 * @Data 2023/12/18 16:03
 */

@Transactional //事务
@RestController
@RequestMapping("/excels")
public class EexcelFileController {

    @Autowired
    private ExcelFileServices excelFileServices;

    public Result importExcel(String filePath) {

        Result result = new Result();

        File file = new File(filePath);

        String fileName = file.getName();
        //打印文件名字
        //校验格式是否正确
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            result.setCode(400);
            result.setMsg("文件格式错误");
            return result;
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream inputStream = null;//输入流
        Workbook workbook = null;//工作流
        //业务操作
        try {
            inputStream = new FileInputStream(file);

            if (isExcel2003) {
                workbook = new HSSFWorkbook(inputStream);
            } else {
                workbook = new XSSFWorkbook(inputStream);
            }

            result =excelFileServices.ReadExcel(workbook);
            workbook.close();
            return result ;
        } catch (IOException e) {
            e.printStackTrace();
            result.setCode(500);
            result.setMsg(String.valueOf(e));
            return result;
        }

    }

    @GetMapping("get")
    public Result ZipExtractor() {
      try {
          Result result = new Result();
          ArrayList<String> maps =new ArrayList<>();
          ArrayList<String> maps2 =new ArrayList<>();
          String zipFilePath = "C:\\Users\\Administrator\\Desktop\\青鱼社区农户经济档案调查表\\农户经济档案调查（小海口二)(1)\\农户经济档案调查（小海口二)";
          List<String> excelFilePaths = getExcelFilePaths(zipFilePath);
          int num = 0;
          for (String filePath : excelFilePaths) {
              Result res =  importExcel(filePath);
              if (res.getCode().equals(200)){
                  maps.add(filePath);
                  HashMap<Object, ArrayList> map = new HashMap<>();
                  map.put("okFile",maps);
                  map.put("errFile",maps2);
                  result.setRows(map);
                  num++;
              }else{
                  HashMap<Object, ArrayList> map = new HashMap<>();
                  maps2.add(filePath);
                  map.put("okFile",maps);
                  map.put("errFile",maps2);
                  result.setCode(500);
                  result.setMsg("第"+(num+1)+"条执行失败,并终止程序");
                  result.setRows(map);
                  result.setTotal(1);
                  result.setData(res);
                  return result;
              }
          }
          result.setRows("成功执行:"+num+"条");
          result.setTotal(num);
          return result;
      }catch (Exception err){
          System.out.println(err);
          return Result.success(err);
      }

    }

}

4.业务类

@Service
@Slf4j
public class ExcelFileServiceImpI implements ExcelFileServices {

    @Resource
    private FamilyInfoDao familyInfoDao;//"1家庭基本信息数据表";
    @Autowired
    private FamilyMembersDao familyMembersDao; //"2家庭成员信息数据表";
    @Autowired
    private RealEstateDao realEstateDao;//"3房地产资产数据表";


    String  igmfl_base_family_info1         = "家庭基本信息数据表";
    String  igmfl_base_family_member_info2  = "家庭成员信息数据表";
    String  igmfl_real_estate_info3         = "房地产资产数据表";

    
  @Override
    @Transactional
    public Result ReadExcel(Workbook workbook) {
        Result result =new Result();
       try {
           FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
           //返回modeo


           ArrayList<Object> arrList =new ArrayList<>();

           //1.家庭基本信息数据表
           FamilyInfo familyInfo =new FamilyInfo();

           //2.家庭成员信息数据表
           List<FamilyMembers> familyMembers=new ArrayList<>();

           //3房地产资产数据表
           List<RealEstate> realEstates=new ArrayList<>();


           //获取sheet表数量
           int sheetCount = workbook.getNumberOfSheets();
           HashMap<Object,Object> map =new HashMap<>();
           for (int i = 0; i < sheetCount; i++) {
               //读取每页的sheet表
               Sheet sheet = workbook.getSheetAt(i);
               String shetTitle = sheet.getSheetName();
               //1.家庭基本信息数据表
               if (shetTitle.contains(igmfl_base_family_info1)&&false){
                   int lastRowNum = sheet.getLastRowNum(); //获取最后一行的索引
                   for (int num = 3; num <= lastRowNum; num++)
                   {
                       Row row = sheet.getRow(num);
                       CellType cellType=row.getCell(1).getCellType();
                       if(cellType.name().equals("STRING"))
                       {
                           String text=row.getCell(1).getStringCellValue();
                           CellType cellTypeValue=row.getCell(2).getCellType();

                           if(text.contains("户主姓名"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setHouseholders_name(value);
                           }
                           else if(text.contains("所在行政村"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setTown_name(value);
                           }
                           else if(text.contains("家庭住址"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setFm_address(value);
                           }
                           else if(text.contains("本地居住年限"))
                           {
                               BigDecimal value=BigDecimal.valueOf(row.getCell(2).getNumericCellValue());
                               familyInfo.setLive_limit(String.valueOf(value));
                           }
                           else if(text.contains("户籍地址"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setDomicile_address(value);
                           }
                           else if(text.contains("主要经营地址"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               if(value.contains("下拉选项菜单"))
                               {

                               }
                               else {
                                   familyInfo.setBusiness_address(value);
                               }
                           }
                           else if(text.contains("主要经营年限"))
                           {
                               if(cellTypeValue.name().equals("BLANK"))
                               {

                               }
                               else if(cellTypeValue.name().equals("NUMERIC"))
                               {
                                   BigDecimal value=BigDecimal.valueOf(row.getCell(2).getNumericCellValue());
                                   familyInfo.setBusiness_limit(String.valueOf(value));
                               }

                           }
                           else if(text.contains("家庭人口"))
                           {
                               if(cellTypeValue.name().equals("NUMERIC"))
                               {
                                   BigDecimal value=BigDecimal.valueOf(row.getCell(2).getNumericCellValue());
                                   familyInfo.setFamily_population(String.valueOf(value));
                               }
                           }
                           else if(text.contains("劳动人口"))
                           {
                               if(cellTypeValue.name().equals("NUMERIC"))
                               {
                                   BigDecimal value=BigDecimal.valueOf(row.getCell(2).getNumericCellValue());
                                   familyInfo.setLabour_population(String.valueOf(value));
                               }
                           }
                           else if(text.contains("供养人口"))
                           {
                               if(cellTypeValue.name().equals("NUMERIC"))
                               {
                                   BigDecimal value=BigDecimal.valueOf(row.getCell(2).getNumericCellValue());
                                   familyInfo.setSupporting_population(String.valueOf(value));
                               }
                           }
                           else if(text.contains("农户类别"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setFarmers_category(String.valueOf(value));
                           }
                           else if(text.contains("是否低保户"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setIs_lowincome(value);
                           }
                           else if(text.contains("是否从事涉农生产经营"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setIs_nybx(value);
                           }
                           else if(text.contains("经营主体类别"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setJyzt_category(value);
                           }
                           else if(text.contains("是否参加农业保险"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setIs_nybx(value);
                           }
                           else if(text.contains("种养产品市场特色"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setTese_lx(value);
                           }
                           else if(text.contains("营业执照号码"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setUuic_id(String.valueOf(value));
                           }
                           else if(text.contains("烤烟种植面积"))
                           {
                               if(cellTypeValue.name().equals("NUMERIC"))
                               {
                                   BigDecimal value=BigDecimal.valueOf(row.getCell(2).getNumericCellValue());
                                   familyInfo.setKaoyan_plant_area(String.valueOf(value));
                               }
                           }
                           else if(text.contains("烤烟种植土地流转面积"))
                           {
                               if(cellTypeValue.name().equals("NUMERIC"))
                               {
                                   BigDecimal value=BigDecimal.valueOf(row.getCell(2).getNumericCellValue());
                                   familyInfo.setKaoyan_lz_area(String.valueOf(value));
                               }
                           }
                           else if(text.contains("是否乡村医生家庭"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setIs_xcys(String.valueOf(value));
                           }
                           else if(text.contains("是否村委干部家庭"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setIs_nybx(value);
                           }
                           else if(text.contains("是否退役军人家庭"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setIs_tyjr(value);
                           }
                           else if(text.contains("是否政府公职人员家庭"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setIs_gzjt(value);
                           }
                           else if(text.contains("是否国企职工家庭"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setIs_gzjt(value);
                           }
                           else if(text.contains("是否党员家庭"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setIs_dy(value);
                           }
                           else if(text.contains("是否致富带头人家庭"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setIs_zfdtr(value);
                           }
                           else if(text.contains("农户信用分档"))
                           {
                               String value=row.getCell(2).getStringCellValue();
                               familyInfo.setXyh_level(value);
                           }


                       }
                       else
                       {
                           log.info("数据存在异常");
                       }

                   }

                   //插入数据操作
                   try {
                       arrList.add(returnlValue(familyInfoDao.insert(familyInfo),i,shetTitle,false,""));
                   }catch (Exception e){
                       arrList.add(returnlValue(0,i,shetTitle,true,String.valueOf(e)));
                       return errReturnlValue(arrList,i);
                   }

               }
               //2家庭成员信息数据表
               else if (shetTitle.contains(igmfl_base_family_member_info2)&&false){

                   Sheet sheet2 = workbook.getSheetAt(i);
                   try {
                       Row row3 = sheet2.getRow(3);
                       for (int k3=2;k3<row3.getLastCellNum();k3++) {
                           HashMap<Object, String> maps = getExcelValue(row3, k3, formulaEvaluator, k3);
                           String cellValue = maps.get("value");
                           if (!cellValue.contains("据实录入（导入）")) {
                               FamilyMembers familyMembers1 = new FamilyMembers();
                               familyMembers1.setMemberName(cellValue);
                               for (int j = 3; j < sheet2.getLastRowNum(); j++) {
                                   Row row = sheet2.getRow(j);
                                   HashMap<Object, String> mapValue = getExcelValue(row, k3, formulaEvaluator, k3);
                                   String valueStr = mapValue.get("value");
                                   if (Boolean.valueOf(mapValue.get("bool"))){
                                       switch (j) {
                                           case 4://性别
                                               familyMembers1.setMemberSex(valueStr);
                                               break;
                                           case 5://民族
                                               familyMembers1.setMemberMz(valueStr);
                                               break;
                                           case 6://出生日期
                                               familyMembers1.setMemberBirth(valueStr);
                                               break;
                                           case 7://居民身份证号码
                                               familyMembers1.setMemberIdno(valueStr);
                                               break;
                                           case 8://发证机关
                                               familyMembers1.setFzjg(valueStr);
                                               break;
                                           case 9://居民身份证生效日期
                                               familyMembers1.setSfzSxrq(valueStr);
                                               break;
                                           case 10://居民身份证有效截止日期
                                               familyMembers1.setSfzYxjzrq(valueStr);
                                               break;
                                           case 11://本地居住年限(年)
                                               familyMembers1.setLiveLimit(valueStr);
                                               break;
                                           case 12://常住地址
                                               familyMembers1.setChzhDaaress(valueStr);
                                               break;
                                           case 13://手机号码
                                               familyMembers1.setMemberMobile(valueStr);
                                               break;
                                           case 14://专业技术职称
                                               familyMembers1.setProfessional(valueStr);
                                               break;
                                           case 15://婚姻状况
                                               familyMembers1.setHyStatus(valueStr);
                                               break;
                                           case 16://健康状况
                                               familyMembers1.setJkStatus(valueStr);
                                               break;
                                           case 17://个人品质
                                               familyMembers1.setGrpzStatus(valueStr);
                                               break;
                                           case 18://政治面貌
                                               familyMembers1.setZzmm(valueStr);
                                               break;
                                           case 19://文化程度
                                               familyMembers1.setWhcd(valueStr);
                                               break;
                                           case 20://与户主关系
                                               familyMembers1.setHzGx(valueStr);
                                               break;
                                           case 21://获得荣誉情况
                                               familyMembers1.setRyStatus(valueStr);
                                               break;
                                           case 22://医疗保险类别
                                               familyMembers1.setYilbxLb(valueStr);
                                               break;
                                           case 23://养老保险类别
                                               familyMembers1.setYlbxLb(valueStr);
                                               break;
                                           case 24://是否退役军人
                                               familyMembers1.setIsTyjr(valueStr);
                                               break;
                                           case 25://合作社成员类别
                                               familyMembers1.setHzscyLb(valueStr);
                                               break;
                                           case 26://是否乡村医生
                                               familyMembers1.setIsXcys(valueStr);
                                               break;
                                           case 27://是否村(居)委干部
                                               familyMembers1.setIsCwgb(valueStr);
                                               break;
                                           case 28://是否农村工匠
                                               familyMembers1.setIsNcgj(valueStr);
                                               break;
                                           case 29://是否非物质文化传承人
                                               familyMembers1.setIsFwzwh(valueStr);
                                               break;
                                           case 30://是否致富带头人家庭
                                               familyMembers1.setIsZfdtr(valueStr);
                                               break;
                                           case 31://务工状况
                                               familyMembers1.setWgStatus(valueStr);
                                               break;
                                           case 32://年务工收入（元）
                                               familyMembers1.setYearWgsr(valueStr);
                                               break;
                                           default:
                                               break;


                                       }
                                   }
                               }
                               if(!familyMembers1.getMemberName().isEmpty()){
                                   familyMembers.add(familyMembers1);
                               }

                           }
                       }


                   }catch (Exception e){
                       log.info("sheet表整理数据失败："+e);
                   }
                   //数据插入
                   for (int p = 0;p<familyMembers.size();p++){
                       try {
                           System.out.println(familyMembers.get(p));
                           arrList.add(returnlValue(familyMembersDao.insert(familyMembers.get(p)),i,shetTitle,false,""));
                       }catch (Exception e){
                           log.info("家庭成员表异常："+e);
                           arrList.add(returnlValue(0,i,shetTitle,true,String.valueOf(e)));
                           return errReturnlValue(arrList,i);
                       }
                   }


               }
               //3房地产资产数据表
               else if (shetTitle.contains(igmfl_real_estate_info3)&&false){

                   Sheet sheet3 = workbook.getSheetAt(i);

                   for (int j = 3; j < sheet3.getLastRowNum()-1; j++) {
                       try {
                           // 获取每行的单元格
                           Row row = sheet3.getRow(j);
                           //获取项目标题
                           String title = GetMergedRegionValue(sheet3,j,0);
                           //判断项目类别
                           if (title.contains("居住用房")||title.contains("商业用房")||title.contains("办公用房")||title.contains("工业用房")
                                   ||title.contains("国有土地使用权")){
                               //获取列的索引
                               int cellIndex=row.getLastCellNum();
                               //实体
                               RealEstate realEstate =new RealEstate();
                               //循环获取列值
                               for (int k=1;k<cellIndex-1;k++){
                                   HashMap<Object,String> maps = getExcelValue(row,k,formulaEvaluator,k);
                                   String  cellValue =maps.get("value");
                                   //校验获得的值名称是为空
                                   if (Boolean.valueOf(maps.get("bool"))){
                                       //插入资产名称
                                       realEstate.setZc_lb(title);
                                       switch (k){
                                           case 1://资产名称
                                               realEstate.setZc_name(cellValue);
                                               break;
                                           case 2://所有权人
                                               realEstate.setOwner_name(cellValue);
                                               break;
                                           case 3://居民身份证号码
                                               realEstate.setOwner_idno(cellValue);
                                               break;
                                           case 4://取得时间
                                               realEstate.setAcquisition_time(cellValue);
                                               break;
                                           case 5://取得方式
                                               realEstate.setAcquisition_fs(cellValue);
                                               break;
                                           case 6://不动产权证号
                                               realEstate.setProperty_right_code(cellValue);
                                               break;
                                           case 7://房屋所有权证号
                                               realEstate.setFwsyq_code(cellValue);
                                               break;
                                           case 8://国有土地使用权证号
                                               realEstate.setGytdsyq_code(cellValue);
                                               break;
                                           case 9://建筑结构
                                               realEstate.setJzjg(cellValue);
                                               break;
                                           case 10://楼层数
                                               realEstate.setLc_num(cellValue);
                                               break;
                                           case 11://建筑面积（㎡）
                                               realEstate.setJz_mj(cellValue);
                                               break;
                                           case 12://土地面积（㎡）
                                               realEstate.setTd_mj(cellValue);
                                               break;
                                           case 13://自建成本（含土地）（元）
                                               realEstate.setZjcb(cellValue);
                                               break;
                                           case 14://购买成本（元）
                                               realEstate.setGmcb(cellValue);
                                               break;
                                           case 15://预计价值（元）
                                               realEstate.setYgjg(cellValue);
                                               break;
                                           case 16://房龄（年）
                                               realEstate.setFl(cellValue);
                                               break;
                                           case 17://剩余使用年限（年）
                                               realEstate.setShyu_limit(cellValue);
                                               break;
                                           case 18://使用状态
                                               realEstate.setSy_status(cellValue);
                                               break;
                                           case 19://年租金（元）
                                               realEstate.setYear_zj(cellValue);
                                               break;
                                           default:
                                               break;

                                       }
                                   }

                               }
                               if (!realEstate.getZc_lb().isEmpty()){
                                   realEstates.add(realEstate);
                               }
                           }

                       }catch (Exception err){
                          log.info("sheet表数据错误"+err);
                       }

                   }

                   for (int p = 0;p<realEstates.size();p++){
                       try {
                           arrList.add(returnlValue(realEstateDao.insert(realEstates.get(p)),i,shetTitle,false,""));
                       }catch (Exception e){
                           System.out.println(e);
                           arrList.add(returnlValue(0,i,shetTitle,true,String.valueOf(e)));
                           return errReturnlValue(arrList,i);
                       }
                   }



               }
              }


           //以上流程全部走完以后就插入数据

           result.setData("数据插入成功");

           return result;
       }catch (Exception err){
           System.out.println(err);
           return result;
       }


    }
}

5.总结 该代码并非完整代码，实体类和返回类型根据自己的业务逻辑去调整，核心代码是util封装的方法，按照需要的参数发送就可以取出Excel表格相应的值，包括合并单元格，函数，时间格式等等