使用Syncfusion.XlsIO在C#合并多Excel文件时遇范围越界异常求助

使用Syncfusion.XlsIO在C#中合并多个Excel文件到单个工作表时，当累计行数达到一定规模时触发System.ArgumentOutOfRangeException异常。触发异常的代码行如下：

IRange destinationRange = destinationWorksheet.Range[currentDestFirstRow,currentDestFirstCol, currentDestLastRow, currentDestLastCol];

异常触发时的目标范围参数为：[1037502,1,1047586,6]，提前终止循环可正常生成文件，所有范围参数均为int类型，无法定位越界原因。

用户实现合并的核心代码如下：

public Test()
{
    Syncfusion.Licensing.SyncfusionLicenseProvider.RegisterLicense("my_key");
    destinationFile = outputDataPath + "__merged.xlsx";

    // 获取待合并文件列表
    filesToMerge = Directory.GetFiles(this.inputDatapath, "*.xlsx");

    using (ExcelEngine excelEngine = new ExcelEngine())
    {
        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Excel2013;
        // 初始化目标工作簿和工作表
        IWorkbook destinationWorkbook = application.Workbooks.Create(1);
        IWorksheet destinationWorksheet = destinationWorkbook.Worksheets[0];

        int currentDestFirstRow = 1;
        int currentDestFirstCol = 1;
        int currentDestLastRow = 1;
        int currentDestLastCol;

        string currentFile;

        for (int i = 0; i < filesToMerge.Length - 1; i++)
        {
            currentFile = filesToMerge[i];

            FileStream currentStream = new FileStream(currentFile, FileMode.Open, FileAccess.Read);
            IWorkbook currentWorkbook = application.Workbooks.Open(currentStream, ExcelOpenType.Automatic);
            IWorksheet currentWorksheet = currentWorkbook.Worksheets[0];
            IRange currentSourceRange = currentWorksheet.UsedRange;

            if (i == 0)
            {
                currentDestFirstRow = 1;
                currentDestFirstCol = 1;
                currentDestLastRow = currentSourceRange.LastRow;
                currentDestLastCol = currentSourceRange.LastColumn;
            }
            else
            {
                currentDestFirstRow = currentDestLastRow + 1;
                currentDestFirstCol = 1;
                currentDestLastRow = currentDestFirstRow + currentSourceRange.LastRow;
                currentDestLastCol = currentSourceRange.LastColumn;
            }

            IRange destinationRange = destinationWorksheet.Range[currentDestFirstRow, currentDestFirstCol, currentDestLastRow, currentDestLastCol];
            currentSourceRange.CopyTo(destinationRange);
            string line = (i.ToString()).PadLeft(3, '0')+Environment.NewLine;
            line += $"file={currentFile}" + Environment.NewLine;
            line += $"destination range=[{currentDestFirstRow},{currentDestFirstCol},{currentDestLastRow},{currentDestLastCol}]";
            Console.WriteLine(line);
        }

        FileStream stream = new FileStream(destinationFile, FileMode.OpenOrCreate, FileAccess.ReadWrite);
        destinationWorkbook.SaveAs(stream);
        destinationWorkbook.Close();
        stream.Close();
        excelEngine.Dispose();
    }
}

Excel 2013版本的单工作表最大行数为1048576行，代码的行数计算逻辑存在错误：

currentDestLastRow = currentDestFirstRow + currentSourceRange.LastRow;

currentSourceRange.LastRow是源工作表已用区域的最后一行号（从1开始计数），比如源表有N行，这个值就是N。直接将目标起始行加上N，相当于重复计算了源表的起始行，导致行数多算1行。当累计行数接近1048576时，这个多算的1行就会让最终行数超过Excel上限，触发越界异常。

1. 修正行数计算逻辑 #

将else分支中的行数计算改为：

else
{
    currentDestFirstRow = currentDestLastRow + 1;
    currentDestFirstCol = 1;
    // 减去1，避免重复计算源表的起始行
    currentDestLastRow = currentDestFirstRow + currentSourceRange.LastRow - 1;
    currentDestLastCol = currentSourceRange.LastColumn;
}

2. 添加行数上限校验 #

在创建目标范围前，添加校验逻辑避免超出Excel限制：

// 检查是否超出Excel 2013单工作表最大行数
if (currentDestLastRow > 1048576)
{
    // 可根据需求选择抛出异常或创建新工作表继续合并
    throw new InvalidOperationException("合并后行数超出Excel单工作表最大限制（1048576行）");
}

3. 优化资源释放 #

原代码中currentStream未用using包裹，易导致资源泄漏，修改为：

using (FileStream currentStream = new FileStream(currentFile, FileMode.Open, FileAccess.Read))
{
    IWorkbook currentWorkbook = application.Workbooks.Open(currentStream, ExcelOpenType.Automatic);
    IWorksheet currentWorksheet = currentWorkbook.Worksheets[0];
    IRange currentSourceRange = currentWorksheet.UsedRange;

    // 行数计算等逻辑...

    currentWorkbook.Close();
}

内容的提问来源于stack exchange，提问作者TexWiller