如何使用C# / FileHelpers ExcelNPOIStorage从Excel文件中提取数据

Question

如何使用C# / FileHelpers ExcelNPOIStorage从Excel文件中提取数据

3

我正在尝试使用Filehelpers ExcelNPOIStorage从Excel表格中提取数据。因此，我创建了一个类：

public static class UalExcelReader
    {
        public static UalShipmentRecord[] ReadInput(String pathToFile)
        {
            var provider = new ExcelNPOIStorage(typeof (UalShipmentRecord))
            {
                StartRow = 2,
                StartColumn = 1,
                FileName = pathToFile
            };
            var res = (UalShipmentRecord[]) provider.ExtractRecords();
            return res;
        }
    }

当然，还有模型类：

[DelimitedRecord("|")]
public class UalShipmentRecord
{
    public string contentofcol1;
    public string contentofcol2;
    ...

}

但是在调用ExtractRecords()时，我遇到了IndexOutOfRangeException异常：

System.IndexOutOfRangeException was unhandled
  HResult=-2146233080
  Message=Index was outside the bounds of the array.
  Source=FileHelpers
  StackTrace:
       at FileHelpers.RecordOperations.ValuesToRecord(Object[] values)
       at FileHelpers.DataLink.DataStorage.ValuesToRecord(Object[] values)
       at FileHelpers.ExcelNPOIStorage.ExcelNPOIStorage.ExtractRecords()
       at Test.Controller.UalExcelReader.ReadInput(String pathToFile) in c:\TEMP\test\Test\Test\Test\Controller\UalExcelReader.cs:line 17
       at Test.App.OnStartup(StartupEventArgs eventArgs) in c:\TEMP\test\Test\Test\Test\App.xaml.cs:line 23
       at System.Windows.Application.<.ctor>b__1(Object unused)
       at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
       at MS.Internal.Threading.ExceptionFilterHelper.TryCatchWhen(Object source, Delegate method, Object args, Int32 numArgs, Delegate catchHandler)
       at System.Windows.Threading.DispatcherOperation.InvokeImpl()
       at System.Windows.Threading.DispatcherOperation.InvokeInSecurityContext(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Windows.Threading.DispatcherOperation.Invoke()
       at System.Windows.Threading.Dispatcher.ProcessQueue()
       at System.Windows.Threading.Dispatcher.WndProcHook(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
       at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
       at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
       at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
       at MS.Internal.Threading.ExceptionFilterHelper.TryCatchWhen(Object source, Delegate method, Object args, Int32 numArgs, Delegate catchHandler)
       at System.Windows.Threading.Dispatcher.LegacyInvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
       at MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)
       at MS.Win32.UnsafeNativeMethods.DispatchMessage(MSG& msg)
       at System.Windows.Threading.Dispatcher.PushFrameImpl(DispatcherFrame frame)
       at System.Windows.Threading.Dispatcher.PushFrame(DispatcherFrame frame)
       at System.Windows.Threading.Dispatcher.Run()
       at System.Windows.Application.RunDispatcher(Object ignore)
       at System.Windows.Application.RunInternal(Window window)
       at System.Windows.Application.Run(Window window)
       at System.Windows.Application.Run()
       at Test.App.Main() in c:\TEMP\test\Test\Test\Test\obj\Debug\App.g.cs:line 0
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

我是否正确使用它？有没有示例可以查看？

- hot33331

请发布完整的异常信息，包括其调用堆栈。您只需调用Exception.ToString()即可获取此信息。 - Panagiotis Kanavos

我添加了异常。 - hot33331

你能提供一个测试项目和演示文件来证明这个吗？ - netniV

你是否解决了这个问题？我遇到了相同的情况，很难找到关于ExcelNPOIStorage使用的任何文档。 - Jonathan Shay

1

我使用了ExcelStorage而不是ExcelNPOIStorage，我的代码如下：

public static MyDatastructure[] ReadInput(String pathToFile)
{
   var provider = new ExcelStorage(typeof(MyDatastructure), pathToFile, 2, 1);
   var res = provider.ExtractRecords();
   return (MyDatastructure[])res;
}

- hot33331

3个回答

0

NPOI库中的MissingCellPolicy默认设置为MissingCellPolicy.RETURN_NULL_AND_BLANK，适用于HSSFWorkbook和XSSFWorkbook类。

将值更改为MissingCellPolicy.CREATE_NULL_AS_BLANK可解决相关工作簿实例的问题。

FileHelpers.ExcelNPOIStorage
ExcelNPOIStorage.cs (OpenWorkbook method)
Fix on Line 101

  private void OpenWorkbook(string filename)
    {
        FileInfo info = new FileInfo(filename);
        if (info.Exists == false)
            throw new FileNotFoundException(string.Concat("Excel File '", filename, "' not found."), filename);

        using (FileStream file = new FileStream(filename, FileMode.Open, FileAccess.Read)) {
            var extension = Path.GetExtension(filename);
            if (extension.ToLowerInvariant() == ".xlsx" || extension.ToLowerInvariant() == ".xlsm")
            {
                mWorkbook = new XSSFWorkbook(file);
            }
            else
                mWorkbook = new HSSFWorkbook(file);
            // Next line fix
            Line 101 - mWorkbook.MissingCellPolicy = MissingCellPolicy.CREATE_NULL_AS_BLANK;
            // Previous line fix  
            if (String.IsNullOrEmpty(SheetName))
                mSheet = mWorkbook.GetSheetAt(mWorkbook.ActiveSheetIndex);  
            else {
                try {
                    mSheet = mWorkbook.GetSheet(SheetName);
                    if (mSheet == null) {
                        throw new ExcelBadUsageException(string.Concat("The sheet '",
                            SheetName,
                            "' was not found in the workbook."));
                    }

                    var sheetIndex = mWorkbook.GetSheetIndex(mSheet);
                    mWorkbook.SetActiveSheet(sheetIndex);
                }
                catch {
                    throw new ExcelBadUsageException(string.Concat("The sheet '",
                        SheetName,
                        "' was not found in the workbook."));
                }
            }
        }
    }

Below the url to the fix on the github repo

https://github.com/MarcosMeli/FileHelpers/pull/291

- Jp Van Schalkwyk

1

嗨，仅提供链接并不是一个好的答案。请确保即使没有链接，答案也能够正常工作。 - Azsgy

0

我认为空单元格是一个问题。在我的文件中，我只是在数据周围的边缘上做了一些调整，这对我有所帮助。尝试用鼠标标记数据并打开字段周围的边缘。

- Leszek Dedko

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- wlf · Accepted Answer

1) 当您的Excel表格中存在空白单元格时，可能会出现此错误。这似乎是ExcelNPOIStorage检索给定行的值的基本问题。

NPOI使用NPOI.CellWalk遍历每行中的单元格，但似乎跳过了空白单元格。因此，Values数组比预期的短了许多空白单元格数量。

看起来需要采用不同的方法，如此处所述：http://poi.apache.org/spreadsheet/quick-guide.html#Iterator

2) 导致不存在空白单元格时出现空白单元格的原因是StartRow和StartColumn值不正确。

与StartRow和StartColumn的intellisense注释相反，对于ExcelNPOIStorage，它们是基于0的（而在ExcelStorage中，它们是基于1的）

来源：https://github.com/MarcosMeli/FileHelpers/blob/master/FileHelpers.ExcelNPOIStorage/Test/Program.cs

基于我遇到的问题，我赞同上面的评论，并使用旧的ExcelStorage类，它更可靠，但缺点是依赖于Excel Interop。