NPOI / POI Excel Lib ISheet.ShiftRows索引超出范围异常

问题描述

我正在使用NPOI中的.ShiftRows将新行插入Excel文件。但是，当尝试将行向下移动以插入新行时，我在某些文件上收到一个 System.ArgumentOutOfRangeException 。这些只是来自不同公司的通用文件，因此它们可能不是干净的，即底部空白行等。我希望它仍然运行，无论是因为NPOI知道最后一行的位置。

I am using .ShiftRows in NPOI to insert a new row into an Excel file. However, I am getting a System.ArgumentOutOfRangeException on certain files when trying to shift the rows down to insert a new row. These are just generic files from different companies that I'm populating, therefore they may not be "clean" i.e. blank rows at the bottom, etc. I would like it to still run regardless of that because NPOI knows where the last row is.

在这种情况下，我正在将行16到458移动，得到超出范围的异常。

In this case, I was shifting rows 16 through 458 down and got the out of range exception.

例外：

Server Error in '/' Application.

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

Source Error: 


Line 564:           if (newRow != null)
Line 565:           {
Line 566:               iSheet.ShiftRows(destinationRowNum, iSheet.LastRowNum, 1);
Line 567:           }
Line 568:           else

堆栈跟踪： p>

Stack Trace:

[ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index]
    System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource) +64
    NPOI.XSSF.UserModel.XSSFSheet.RemoveMergedRegions(HashSet`1 indices) +170
    NPOI.XSSF.UserModel.Helpers.XSSFRowShifter.ShiftMerged(Int32 startRow, Int32 endRow, Int32 n) +333
    NPOI.XSSF.UserModel.XSSFSheet.ShiftRows(Int32 startRow, Int32 endRow, Int32 n, Boolean copyRowHeight, Boolean resetOriginalRowHeight) +1746
    NPOI.XSSF.UserModel.XSSFSheet.ShiftRows(Int32 startRow, Int32 endRow, Int32 n) +18
    MyApp.App_Code.MyClass.CopyRow(IWorkbook iWorkbook, ISheet iSheet, Int32 sourceRowNum, Int32 destinationRowNum) in C:\Users\MyUser\Documents\Visual Studio 2015\Projects\MyApp\MyApp\App_Code\MyClass.cs:566
    MyApp.App_Code.MyClass.Build(Nullable`1 sheetId, Nullable`1 fileId) in C:\Users\MyUser\Documents\Visual Studio 2015\Projects\MyApp\MyApp\App_Code\MyClass.cs:495
    MyApp.Controllers.SheetsController.Build(BuildViewModel build) in C:\Users\MyUser\Documents\Visual Studio 2015\Projects\MyApp\MyApp\Controllers\SheetsController.cs:640
    lambda_method(Closure , ControllerBase , Object[] ) +103
    System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +14
    System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +157
    System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +27
    System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState) +22
    System.Web.Mvc.Async.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult) +29
    System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
    System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +32
    System.Web.Mvc.Async.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3d() +50
    System.Web.Mvc.Async.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() +225
    System.Web.Mvc.Async.<>c__DisplayClass33.<BeginInvokeActionMethodWithFilters>b__32(IAsyncResult asyncResult) +10
    System.Web.Mvc.Async.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) +10
    System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
    System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +34
    System.Web.Mvc.Async.<>c__DisplayClass2b.<BeginInvokeAction>b__1c() +26
    System.Web.Mvc.Async.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult) +100
    System.Web.Mvc.Async.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) +10
    System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
    System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +27
    System.Web.Mvc.Controller.<BeginExecuteCore>b__1d(IAsyncResult asyncResult, ExecuteCoreState innerState) +13
    System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +29
    System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
    System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +36
    System.Web.Mvc.Controller.<BeginExecute>b__15(IAsyncResult asyncResult, Controller controller) +12
    System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +22
    System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
    System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +26
    System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10
    System.Web.Mvc.MvcHandler.<BeginProcessRequest>b__5(IAsyncResult asyncResult, ProcessRequestState innerState) +21
    System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +29
    System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
    System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +28
    System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
    System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9765121
    System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

我已经修改了一些POI代码从给NPOI来做这个工作。

I have modified some POI code from here to NPOI to make this work.

CopyRow函数：

CopyRow function:

private static void CopyRow(IWorkbook iWorkbook, ISheet iSheet, int sourceRowNum, int destinationRowNum)
{
    // Get the source / new row
    IRow newRow = iSheet.GetRow(destinationRowNum);
    IRow sourceRow = iSheet.GetRow(sourceRowNum);

    // If the row exist in destination, push down all rows by 1 else create a new row
    if (newRow != null)
    {
        iSheet.ShiftRows(destinationRowNum, iSheet.LastRowNum, 1); // this is causing the issue
    }
    else
    {
        newRow = iSheet.CreateRow(destinationRowNum);
    }

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.LastCellNum; i++)
    {
        // Grab a copy of the old/new cell
        ICell oldCell = sourceRow.GetCell(i);
        ICell newCell = newRow.CreateCell(i);

        // If the old cell is null jump to next cell
        if (oldCell == null)
        {
            newCell = null;
            continue;
        }

        // Copy style from old cell and apply to new cell
        ICellStyle newCellStyle = iWorkbook.CreateCellStyle();
        newCellStyle.CloneStyleFrom(oldCell.CellStyle);
        ;
        newCell.CellStyle = newCellStyle;

        // If there is a cell comment, copy
        if (oldCell.CellComment != null)
        {
            newCell.CellComment = oldCell.CellComment;
        }

        // If there is a cell hyperlink, copy
        if (oldCell.Hyperlink != null)
        {
            newCell.Hyperlink = oldCell.Hyperlink;
        }

        // Set the cell data type
        newCell.SetCellType(oldCell.CellType);

        // Set the cell data value
        switch (oldCell.CellType)
        {
            case CellType.Blank:
                newCell.SetCellValue(oldCell.StringCellValue);
                break;
            case CellType.Boolean:
                newCell.SetCellValue(oldCell.BooleanCellValue);
                break;
            case CellType.Error:
                newCell.SetCellErrorValue(oldCell.ErrorCellValue);
                break;
            case CellType.Formula:
                newCell.SetCellFormula(oldCell.CellFormula);
                break;
            case CellType.Numeric:
                newCell.SetCellValue(oldCell.NumericCellValue);
                break;
            case CellType.String:
                newCell.SetCellValue(oldCell.RichStringCellValue);
                break;
        }
    }

    // If there are are any merged regions in the source row, copy to new row
    for (int i = 0; i < iSheet.NumMergedRegions; i++)
    {
        CellRangeAddress cellRangeAddress = iSheet.GetMergedRegion(i);
        if (cellRangeAddress.FirstRow == sourceRow.RowNum)
        {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
                      (newRow.RowNum +
                                 (cellRangeAddress.LastRow - cellRangeAddress.FirstRow
                                            )),
                      cellRangeAddress.FirstColumn,
                      cellRangeAddress.LastColumn);
            iSheet.AddMergedRegion(newCellRangeAddress);
        }
    }
}

有人遇到这个问题使用NPOI或POI之前？

Has anyone run into this issue with NPOI or POI before?

使用NPOI v2.2.1。

Using NPOI v2.2.1.

编辑：我保存了相同的文件一个.xls（以前的.xlsx），没有问题。可能有什么关系.ShiftRows for XSSFWorkbook的唯一...将继续保持发布。

I saved the same file as an .xls (previously .xlsx) and didn't have the issue. Probably something to do with .ShiftRows for XSSFWorkbook's only... will continue to keep you posted.

推荐答案

此问题已修复NPOI V2.3.0 。

This issue is fixed in NPOI V2.3.0.

我试过了。

这篇关于NPOI / POI Excel Lib ISheet.ShiftRows索引超出范围异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！