getPhysicalNumberOfCells获取列数不是合并前实际列数

问题就是:有的导入复杂表头被合并的单元格有默认空字符串，有的直接不存在这个单元格

实际我需要下面这种情况

断点可以看到这个导入第一行合并了，被合并单元格还有默认的空字符串

解决办法就是在合并单元格里面判断，不是第一行第一列都设置空字符串

之前贴过导入复杂表头，这里再贴一边

package com.njry.modules.system.rest;


import com.njry.exception.BadRequestException;
import com.njry.modules.tools.domain.vo.HeaderCell;
import com.njry.modules.tools.domain.vo.HeaderRegion;
import com.njry.modules.system.domain.SysTest;
import com.njry.modules.system.service.SysTestService;
import com.njry.utils.SecurityUtils;
import com.njry.utils.excel.ExcelColorHelper;
import com.njry.utils.excel.HssfHelper;
import com.njry.utils.excel.XssfHelper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.RequiredArgsConstructor;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
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.util.*;

@RestController
@RequiredArgsConstructor
@Api(tags = "工具：本地存储管理")
@RequestMapping("/api/localStorageExcel")
public class testExcelController {

    private final SysTestService sysTestService;

    @PostMapping
    @ApiOperation("上传文件")
    public ResponseEntity<Object> createFile(@RequestParam String name, @RequestParam("file") MultipartFile file){
//        保存导入表头配置
        Map<String, String> mp = new HashMap<String, String>();
        int res = 1;

        try {
            if (file == null || file.isEmpty()) {
                res = -1;
            }
            HeaderCell[][] headerCells = null;
            int typeNumber = checkFile(file);
            if(typeNumber == 1){
                headerCells = readXLSX(file);
            }
            if(typeNumber == 2){
                headerCells = readXLS(file);
            }
            if(typeNumber == 0 || typeNumber == 3){
                throw new BadRequestException("上传文件格式不正确");
            }
            Map cellMap = getHtmlStr(headerCells, 1);//传1 正序
//            获取表头的后处理父子级关系
            List cellList = (List)cellMap.get("cellList");
            int rowNum = (int)cellMap.get("rowNum");

//            只有正序的时候才能这样使用
            for(int i = 0; i < cellList.size(); i++){
                HashMap<String,Object> hashMap = (HashMap)cellList.get(i);
                if(hashMap.get("rowfrom").equals(0)){
                    hashMap.put("parentLevel",null);
//                    hashMap.put("leafFlag",0);
                }else{
//                    判断不是第一行起的父级是谁
                    int rowfrom = convertObjectToInt(hashMap.get("rowfrom")) - 1;
                    int colfrom = convertObjectToInt(hashMap.get("colfrom"));
                    for (int j = 0; j < cellList.size(); j++) {
                        HashMap<String,Object> hashMapInner = (HashMap)cellList.get(j);
                        int rowfrom1 = convertObjectToInt(hashMapInner.get("rowfrom"));
                        int rowto = convertObjectToInt(hashMapInner.get("rowto"));
                        int colfrom1 = convertObjectToInt(hashMapInner.get("colfrom"));
                        int colto = convertObjectToInt(hashMapInner.get("colto"));
                        if(rowfrom >= rowfrom1 && rowfrom <= rowto && colfrom >= colfrom1 && colfrom <= colto){
                            hashMap.put("parentLevel",hashMapInner.get("headid"));
//                            hashMap.put("leafFlag",1);
                        }
                    }
                }
//                处理leafFlag是最后一行表示叶子节点
                int rowto = convertObjectToInt(hashMap.get("rowto"));
                if(rowto == rowNum - 1){
                    hashMap.put("leafFlag",1);
                }else{
                    hashMap.put("leafFlag",0);
                }
            }
            System.out.println(cellList);
            for (int k = 0; k < cellList.size(); k++) {
                HashMap<String,Object> hashMap = (HashMap)cellList.get(k);
                SysTest resources =  new SysTest();
                resources.setColfrom((Integer) hashMap.get("colfrom"));
                resources.setColto((Integer) hashMap.get("colto"));
                resources.setColspan((Integer) hashMap.get("colspan"));
                resources.setRowfrom((Integer) hashMap.get("rowfrom"));
                resources.setRowto((Integer) hashMap.get("rowto"));
                resources.setRowspan((Integer) hashMap.get("rowspan"));
//                数据库不允许title在插入的时候是null(表头传入有的是空)---修改数据表为null
                resources.setTitle((String) hashMap.get("title"));
                resources.setHeadid((String) hashMap.get("headid"));
                resources.setParentlevel((String) hashMap.get("parentLevel"));
                resources.setHeight((Float) hashMap.get("height"));
                resources.setBgColor((String) hashMap.get("bg_color"));
                resources.setFontColor((String) hashMap.get("font_color"));
                resources.setFontHeight((String) hashMap.get("font_height"));
                resources.setLeafFlag((Integer) hashMap.get("leafFlag"));
                sysTestService.create(resources);
            }
        }catch (Exception e)
            {
                throw e;
//                res = 0;
            }
            mp.put("res", res + "");
        return new ResponseEntity<>(HttpStatus.CREATED);
    }


    public int convertObjectToInt(Object obj) {
        if (obj instanceof String) {
            return Integer.parseInt((String) obj);
        } else if (obj instanceof Integer) {
            return (Integer) obj;
        } else {
            throw new IllegalArgumentException("Object cannot be converted to int");
        }
    }

    public HeaderCell[][] readXLS(MultipartFile file){
        Workbook wb = null;
        try
        {
            wb = new HSSFWorkbook(file.getInputStream());
        }
        catch (Exception ex)
        {
        }
        Sheet sheet = wb.getSheetAt(0);
        return excelToHtml(sheet,wb,1);
    }

    public HeaderCell[][] readXLSX(MultipartFile file){
        Workbook wb = null;
        try
        {
            wb = new XSSFWorkbook(file.getInputStream());
        }
        catch (Exception ex)
        {
        }
        Sheet sheet = wb.getSheetAt(0);
        return excelToHtml(sheet,wb,2);
    }

    /**
     * 判断File文件的类型
     * @param file 传入的文件
     * @return 0-文件为空，1-XLSX文件，2-XLS文件，3-其他文件
     */
    public int checkFile(MultipartFile file) {
        if (file == null) {
            return 0;
        }
        String filename = file.getOriginalFilename();
        String fileSuffix= filename.substring(filename.lastIndexOf("."));
        if (fileSuffix.endsWith("xlsx")) {
            return 1;
        }
        if (fileSuffix.endsWith("xls")) {
            return 2;
        }
        return 3;
    }

    /**
     * Convert byte[] to hex string.这里我们可以将byte转换成int，然后利用Integer.toHexString(int)
     *来转换成16进制字符串。
     * @param src 传入的rbg
     * @return @return hex string
     */
    public static String bytesToHexString(byte[] src){
        StringBuilder stringBuilder = new StringBuilder("");
        if (src == null || src.length <= 0) {
            return null;
        }
        for (int i = 0; i < src.length; i++) {
            int v = src[i] & 0xFF;
            String hv = Integer.toHexString(v);
            if (hv.length() < 2) {
                stringBuilder.append(0);
            }
            stringBuilder.append(hv);
        }
        return stringBuilder.toString();
    }


    /**
     * @description 解析表头
     * @author 
     * @param sheet
     * @param wb
     * @param type 1是 HSSFWorkbook 2 是XSSFWorkbook
     * @return
     */
    @SuppressWarnings({"deprecation", "unchecked", "static-access"})
    public HeaderCell[][] excelToHtml(Sheet sheet, Workbook wb,int type)
    {
        //开始解析excel
        HssfHelper hssfHelper = null;
        XssfHelper xssfHelper = null;
        if(type == 1){
            hssfHelper= new HssfHelper();
        }
        if(type == 2){
            xssfHelper = new XssfHelper();
        }
        ExcelColorHelper colorHelper = new ExcelColorHelper();
        Font headFont;
        CellStyle headStyle;


        //获取Sheet中的合并单元格信息(为下文HeaderCell下属性判断使用)
        HeaderRegion[] headerRegions = new HeaderRegion[sheet.getNumMergedRegions()];
        for(int k = 0; k < sheet.getNumMergedRegions(); k++)
        {
            HeaderRegion headerRegion = null;
            CellRangeAddress region = sheet.getMergedRegion(k);
            headerRegion = new HeaderRegion();
            int firstRow = region.getFirstRow();
            int lastRow = region.getLastRow();
            int firstColumn = region.getFirstColumn();
            int lastColumn = region.getLastColumn();
            headerRegion.setTargetRowFrom(firstRow);
            headerRegion.setTargetRowTo(lastRow);
            headerRegion.setTargetColumnFrom(firstColumn);
            headerRegion.setTargetColumnTo(lastColumn);
            if(type == 1){
                HSSFCell cell = (HSSFCell)sheet.getRow(firstRow).getCell(firstColumn);
                headerRegion.setText(hssfHelper.getCellStringValue(cell));
            }
            if(type == 2){
                XSSFCell cell = (XSSFCell)sheet.getRow(firstRow).getCell(firstColumn);
                headerRegion.setText(xssfHelper.getCellStringValue(cell));
            }
            headerRegion.setColLength(1 + (lastColumn - firstColumn));
            headerRegion.setRowLength(1 + (lastRow - firstRow));
//            当前合并单元的合并的列数
//            int numberOfCells = region.getNumberOfCells();
//            System.out.println(numberOfCells);
//            合并单元格，被合并单元格没数据设置成默认空字符----getPhysicalNumberOfCells获取不到存在合并行(合并前的)实际列数
            for (int i = firstRow; i <= lastRow; i++) {
                for (int j = firstColumn; j <= lastColumn; j++) {
//                    除去第一行一列默认有值，其余全部设置为空字符串
                    if(i == firstRow && j == firstColumn){
//                        第一行第一列啥也不处理
                    }else{
//                        被合并但是有数据默认空字符串不处理
                        Cell cell = sheet.getRow(i).getCell(j);
                        if(cell == null){
                            cell = sheet.getRow(i).createCell(j);
                            cell.setCellValue(""); // 设置单元格为空字符串
                        }
                    }
                }
            }
            headerRegions[k] = headerRegion;
        }

        //获取Sheet中的单元格信息
        int rowNum = sheet.getPhysicalNumberOfRows();
//        获取第一行,得到第一行有多少列,就是excel有多少列(有的第一行合并后,获取是合并的列数,不是合并前实际列数)
        Row rowFirst = sheet.getRow(0);
        int cellNum = rowFirst.getPhysicalNumberOfCells();

//        short firstCellNum = rowFirst.getFirstCellNum();
//        System.out.println(firstCellNum);
//        short lastCellNum = rowFirst.getLastCellNum();
//        System.out.println(lastCellNum);
        HeaderCell[][] headerCells = new HeaderCell[rowNum][cellNum];
        Iterator iter = sheet.rowIterator();
        for(int i = 0; iter.hasNext(); i++)
        {
            HeaderCell headerCell = null;
            if(type == 1){
                HSSFRow row = (HSSFRow) iter.next();
//            获取每一行高度
                float heightInPoints = row.getHeightInPoints();
//            获取每一行有多少列 physical物理的
                int cellNums = row.getPhysicalNumberOfCells();

                for(int j = 0; j < cellNums; j++)
                {
                    headerCell = new HeaderCell();
//                获取到每一行下每一列
                    HSSFCell cell = row.getCell((short) j);
                    headStyle = cell.getCellStyle();
                    headFont = wb.getFontAt(cell.getCellStyle().getFontIndex());//得到单元格的字体
                    headerCell.setRowIndex(i);
                    headerCell.setColIndex(j);
                    headerCell.setText(hssfHelper.getCellStringValue(cell));
                    headerCell.setHeight(heightInPoints);
                    headerCell.setWidth(sheet.getColumnWidth((short) j) / 32);


                    HSSFCellStyle cellStyle = cell.getCellStyle();
                    HSSFColor fillForegroundColorColor1 = cellStyle.getFillForegroundColorColor();
                    String foregroundColorHexString = fillForegroundColorColor1.getHexString();
                    HSSFFont font = cellStyle.getFont(wb);
                    HSSFWorkbook tempTransport = (HSSFWorkbook) wb;
                    HSSFColor hssfColor = font.getHSSFColor(tempTransport);
                    if(hssfColor == null){
                        String colorHexString = "#000000";
                        headerCell.setFontColor(colorHexString);
                    }else{
                        String colorHexString = hssfColor.getHexString();
                        headerCell.setFontColor(colorHexString);
                    }
                    headerCell.setBgcolor(foregroundColorHexString);

                    headerCell.setFontHeight(String.valueOf(headFont.getFontHeight()/20));

                    boolean hasRegion = false;
                    for(int k = 0; k < headerRegions.length; k++)
                    {
//                    判断当前cell是否属于合并单元格 i 在合并单元格之内 同时 j 在合并单元格内(上文headerRegions使用)
                        if(i >= headerRegions[k].getTargetRowFrom() && i <= headerRegions[k].getTargetRowTo()
                                && j >= headerRegions[k].getTargetColumnFrom() && j <= headerRegions[k].getTargetColumnTo())
                        {
                            headerCell.setHeaderRegion(headerRegions[k]);
                            hasRegion = true;
                        }
                    }

                    if(!hasRegion)
                    {
                        HeaderRegion headerRegion2 = new HeaderRegion();
                        headerRegion2.setTargetRowFrom(i);
                        headerRegion2.setTargetRowTo(i);
                        headerRegion2.setTargetColumnFrom(j);
                        headerRegion2.setTargetColumnTo(j);
                        HSSFCell cell1 = (HSSFCell)sheet.getRow(i).getCell((short) j);
                        headerRegion2.setText(hssfHelper.getCellStringValue(cell1));
                        headerRegion2.setColLength(1);
                        headerRegion2.setRowLength(1);
                        headerCell.setHeaderRegion(headerRegion2);
                    }
//                通过!hasRegion将没有合并的单元格都也有headerRegion,就是自己本身,本身的话下面设置两个变量都是true
//                合并区域的开始行i 和开始列 j 是否是当前单元格的开始行和列
                    headerCell.setAscDisplay((i == headerCell.getHeaderRegion().getTargetRowFrom() && j == headerCell.getHeaderRegion().getTargetColumnFrom()) ? true : false);
//                合并区域的结束行i 和开始列 j 是否是当前单元格的开始行和列
                    headerCell.setDescDisplay((i == headerCell.getHeaderRegion().getTargetRowTo() && j == headerCell.getHeaderRegion().getTargetColumnFrom()) ? true : false);
                    headerCells[i][j] = headerCell;
                }
            }
            if(type == 2){
                XSSFRow row = (XSSFRow) iter.next();
//            获取每一行高度
                float heightInPoints = row.getHeightInPoints();
//            获取每一行有多少列 physical物理的
                int cellNums = row.getPhysicalNumberOfCells();

                for(int j = 0; j < cellNums; j++)
                {
                    headerCell = new HeaderCell();
//                获取到每一行下每一列
                    XSSFCell cell = row.getCell(j);
                    headFont = wb.getFontAt(cell.getCellStyle().getFontIndex());//得到单元格的字体
                    headerCell.setRowIndex(i);
                    headerCell.setColIndex(j);
                    headerCell.setText(xssfHelper.getCellStringValue(cell));
                    headerCell.setHeight(heightInPoints);
                    headerCell.setWidth(sheet.getColumnWidth((short) j) / 32);


                    XSSFCellStyle cellStyle = cell.getCellStyle();
                    XSSFColor fillForegroundColorColor = cellStyle.getFillForegroundColorColor();
                    if(fillForegroundColorColor == null){
                        String foregroundColorHexString = "#ffffff";
                        headerCell.setBgcolor(foregroundColorHexString);
                    }else{
                        byte[] argb = fillForegroundColorColor.getARGB();
                        String tempString = bytesToHexString(argb);
                        headerCell.setBgcolor(tempString);
                    }
                    XSSFFont font = cellStyle.getFont();
                    XSSFColor xssfColor = font.getXSSFColor();
                    if(xssfColor == null){
                        String colorHexString = "#000000";
                        headerCell.setFontColor(colorHexString);
                    }else{
                        byte[] argb = xssfColor.getARGB();
                        String tempString = bytesToHexString(argb);
                        headerCell.setFontColor(tempString);
                    }

                    headerCell.setFontHeight(String.valueOf(headFont.getFontHeight()/20));

                    boolean hasRegion = false;
                    for(int k = 0; k < headerRegions.length; k++)
                    {
//                    判断当前cell是否属于合并单元格 i 在合并单元格之内 同时 j 在合并单元格内(上文headerRegions使用)
                        if(i >= headerRegions[k].getTargetRowFrom() && i <= headerRegions[k].getTargetRowTo()
                                && j >= headerRegions[k].getTargetColumnFrom() && j <= headerRegions[k].getTargetColumnTo())
                        {
                            headerCell.setHeaderRegion(headerRegions[k]);
                            hasRegion = true;
                        }
                    }

                    if(!hasRegion)
                    {
                        HeaderRegion headerRegion2 = new HeaderRegion();
                        headerRegion2.setTargetRowFrom(i);
                        headerRegion2.setTargetRowTo(i);
                        headerRegion2.setTargetColumnFrom(j);
                        headerRegion2.setTargetColumnTo(j);
                        XSSFCell cell1 = (XSSFCell)sheet.getRow(i).getCell((short) j);
                        headerRegion2.setText(xssfHelper.getCellStringValue(cell1));
                        headerRegion2.setColLength(1);
                        headerRegion2.setRowLength(1);
                        headerCell.setHeaderRegion(headerRegion2);
                    }
//                通过!hasRegion将没有合并的单元格都也有headerRegion,就是自己本身,本身的话下面设置两个变量都是true
//                合并区域的开始行i 和开始列 j 是否是当前单元格的开始行和列
                    headerCell.setAscDisplay((i == headerCell.getHeaderRegion().getTargetRowFrom() && j == headerCell.getHeaderRegion().getTargetColumnFrom()) ? true : false);
//                合并区域的结束行i 和开始列 j 是否是当前单元格的开始行和列
                    headerCell.setDescDisplay((i == headerCell.getHeaderRegion().getTargetRowTo() && j == headerCell.getHeaderRegion().getTargetColumnFrom()) ? true : false);
                    headerCells[i][j] = headerCell;
                }
            }
        }
        return headerCells;
    }

    /**
     * @description 拼接表头样式（报表表头导入）
     * @author 
     * @param headerCells
     * @param flag
     * @return
     */
    @SuppressWarnings("unchecked")
    public Map getHtmlStr(HeaderCell[][] headerCells, int flag) {

        if(headerCells == null || headerCells.length == 0) {
            return null;
        }
        Map map = new HashMap();
        int rowNum = headerCells.length;
        int cellNum = headerCells[0].length;
        String htmlStr = "";
        String cellStr = "";
        List cellList = new ArrayList();
        int width = 0;
        Map cellMap = null;
        int i = 0;
        boolean bool = i < rowNum;
        if(flag == -1)
        {
            i = rowNum - 1;
            bool = i >= 0;
        }
        while(bool)
        {
            for(int j = 0; j < cellNum; j++)
            {
//                通过是否是正序(合并单元格开始行,限制列仅为开始)或者倒序,可以排除合并中不是开始的单元格(不必要的循环)
                boolean bool_tem = flag == -1 ? headerCells[i][j].isDescDisplay(): headerCells[i][j].isAscDisplay();
                if(bool_tem)
                {
                    String uuid = UUID.randomUUID().toString().replaceAll("-", "");
                    cellMap = new HashMap();
                    cellMap.put("title", headerCells[i][j].getText());                                  //标题
                    cellMap.put("headid", uuid);                                                        //唯一id
                    cellMap.put("rowfrom", headerCells[i][j].getHeaderRegion().getTargetRowFrom());     //起始行
                    cellMap.put("colfrom", headerCells[i][j].getHeaderRegion().getTargetColumnFrom());  //起始列
                    cellMap.put("rowto", headerCells[i][j].getHeaderRegion().getTargetRowTo());         //目标行
                    cellMap.put("colto", headerCells[i][j].getHeaderRegion().getTargetColumnTo());      //目标列
                    cellMap.put("colspan", headerCells[i][j].getHeaderRegion().getColLength());         //合并列
                    cellMap.put("rowspan", headerCells[i][j].getHeaderRegion().getRowLength());         //合并行
                    cellMap.put("width", headerCells[i][j].getWidth());         //宽度
                    cellMap.put("height", headerCells[i][j].getHeight());         //高度
                    cellMap.put("bg_color", headerCells[i][j].getBgcolor());         //背景色
                    cellMap.put("font_color", headerCells[i][j].getFontColor());         //字体颜色
                    cellMap.put("font_height", headerCells[i][j].getFontHeight());         //字体大小
                    cellList.add(cellMap);

/*//                    不是倒数第一和第二行循环
//                    添加随机id,方便后面建立父子级关系
                    String uuid = UUID.randomUUID().toString().replaceAll("-", "");
                    if((i != rowNum - 2) && (i != rowNum - 1))
                    {
                        cellMap = new HashMap();
                        cellMap.put("title", headerCells[i][j].getText());                                  //标题
                        cellMap.put("headid", uuid);                                                        //唯一id
                        cellMap.put("rowfrom", headerCells[i][j].getHeaderRegion().getTargetRowFrom());     //起始行
                        cellMap.put("colfrom", headerCells[i][j].getHeaderRegion().getTargetColumnFrom());  //起始列
                        cellMap.put("rowto", headerCells[i][j].getHeaderRegion().getTargetRowTo());         //目标行
                        cellMap.put("colto", headerCells[i][j].getHeaderRegion().getTargetColumnTo());      //目标列
                        cellMap.put("colspan", headerCells[i][j].getHeaderRegion().getColLength());         //合并列
                        cellMap.put("rowspan", headerCells[i][j].getHeaderRegion().getRowLength());         //合并行
                        cellMap.put("width", headerCells[i][j].getWidth());         //宽度
                        cellMap.put("height", headerCells[i][j].getHeight());         //高度
                        cellMap.put("bg_color", headerCells[i][j].getBgcolor());         //背景色
                        cellMap.put("font_color", headerCells[i][j].getFontColor());         //字体颜色
                        cellMap.put("font_height", headerCells[i][j].getFontHeight());         //字体大小
                        cellList.add(cellMap);
                    }
                    if(i == rowNum - 2) //标题对应英文字段(总行数rowNum不变,i变化,总函数减去2就是倒数第二行)
                    {
                        htmlStr +=  "#" + headerCells[i][j].getHeaderRegion().getText() + ">>" + j;
                        width += new Float(headerCells[i][j].getWidth()).intValue() + 20;
                    }
                    else if(i == rowNum - 1)  //英文字段类型
                    {
                        cellStr += "#" + headerCells[i][j].getHeaderRegion().getText() + ">>" + j;
                    }*/
                }
            }
            if(flag == -1)
            {
                i--;
                bool = i >= 0;
            }
            else
            {
                i++;
                bool = i < rowNum;
            }
        }
//        map.put("htmlStr", htmlStr.substring(1));
//        map.put("cellStr", cellStr.substring(1));
        map.put("cellList", cellList);
        map.put("rowNum", rowNum);
//        map.put("width", width);
        return map;
    }
}