Skip to content

对共享公式范围的第一个单元格的所有更新都会导致共享公式丢失,Excel打开报错 #800

@wvfeng

Description

@wvfeng

版本:2.5.5
经过测试,该问题一直存在,并且我这里可以提供一个可以复现的案例,以及修复方案。
测试文件中删除通过NPOI删除A1单元格(或尝试其他类型的修改)可以复现该问题。
修复方案我这里贴出删除单元格代码进行参考,实际上需要考虑其他情况造成的单元格改变
在NPOI组件没有修复该问题前,遇到该问题的人可以通过下面的示例解决类似的问题

public static class CellExt
{
    public static void Remove(this XSSFCell cell)
    {
        cell.ClearFormula();
        cell.Row.RemoveCell(cell);
    }

    public static void ClearCell(this XSSFCell cell)
    {
        cell.ClearFormula();
        cell.SetBlank();
    }

    public static void ClearFormula(this XSSFCell cell)
    {
        if (cell.CellType == CellType.Formula)
        {
            var CTCell = cell.GetCTCell();
            if (
                CTCell.f.t == ST_CellFormulaType.shared
                && CTCell.f.isSetRef()
            )
            {
                var range = CellRangeAddress.ValueOf(CTCell.f.@ref);
                for (int rowNum = range.FirstRow; rowNum <= range.LastRow; rowNum++)
                {
                    var row = cell.Sheet.GetRow(rowNum);
                    if (row == null)
                    {
                        continue;
                    }
                    for (int cellNum = range.FirstColumn; cellNum <= range.LastColumn; cellNum++)
                    {
                        var nextCell = (XSSFCell)row.GetCell(cellNum);
                        if (nextCell == null || nextCell == cell)
                        {
                            continue;
                        }
                        if (nextCell.CellType != CellType.Formula)
                        {
                            continue;
                        }
                        var nextCTCell = nextCell.GetCTCell();
                        var nextRange = new CellRangeAddress(rowNum, range.LastRow, cellNum, range.LastColumn);
                        var newFormula = CTCell.f.Copy();
                        newFormula.Value = nextCell.CellFormula;
                        nextCTCell.f = newFormula;
                        if (nextRange.NumberOfCells == 1)
                        {
                            nextCTCell.f.@ref = default;
                            nextCTCell.f.t = default;
                            nextCTCell.f.si = default;
                        }
                        else
                        {
                            nextCTCell.f.@ref = nextRange.FormatAsString();
                        }
                        return;
                    }
                }
            }
        }
    }

    public static CT_Cell GetCTCell(this XSSFCell cell)
    {
        var GetCTCellMethod = ((TypeInfo)cell.GetType())?.GetDeclaredMethod("GetCTCell");
        return (CT_Cell)GetCTCellMethod?.CreateDelegate(typeof(Func<CT_Cell>), cell).DynamicInvoke();
    }
}

测试共享公式.xlsx

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions