NPOI、XLSX 和 VB.NET

NPOI, XLSX and VB.NET

提问by PsychoCoder

I'm attempting to use NPOI to convert both XLS and XLSX files to CSV files. Right now my code works perfectly for XLS files but am running into issues with XLSX files.

我正在尝试使用 NPOI 将 XLS 和 XLSX 文件转换为 CSV 文件。现在我的代码非常适合 XLS 文件，但我遇到了 XLSX 文件的问题。

Here's the code for converting the file(s) to CSV files using NPOI (actually this code converts it to a DataSet, conversion to CSV happens in the next piece of code)

这是使用 NPOI 将文件转换为 CSV 文件的代码（实际上此代码将其转换为数据集，转换为 CSV 发生在下一段代码中）

''' <summary>
''' Import file with delimited rows, columns to datatable 
''' </summary>
''' <param name="FilePath">Path to our file</param>
''' <param name="delimiter">string for delimited imported row items</param>
''' <param name="retainHeaders">Including columns header with importing , (if true, the first row will be added as DataColumns) , (if false, DataColumns will be numbers)</param>
''' <param name="worksheetIndex">The index of the worksheet we're wanting to read from</param>
Private Function GenerateDataTableFromXls(ByVal FilePath As String, ByVal delimiter As String, ByVal retainHeaders As Boolean, ByVal worksheetIndex As Integer) As DataSet

    Dim ds As New DataSet
    Dim ext As String = Path.GetExtension(FilePath)
    Try
        'Get the excel from filepath
        Dim workbook As IWorkbook
        Dim worksheet As ISheet
        Dim sheetCount As Integer
        Dim file = New FileStream(FilePath, FileMode.Open)
        If ext.ToLower() = ".xls" Then
            workbook = New HSSFWorkbook(file)
        Else
            workbook = New XSSFWorkbook(file)
        End If


        sheetCount = workbook.NumberOfSheets

        For i As Integer = 1 To sheetCount
            Dim table As New DataTable
            worksheet = workbook.GetSheetAt(i)

            'get Excel rows
            Dim rows As Integer = worksheet.PhysicalNumberOfRows

            'get the column count
            Dim columns As Integer = worksheet.GetRow(0).PhysicalNumberOfCells

            'now for adding the column headers, if retainHeaders is True then we add the
            'actual headers in the XLS file, if not then add the column number as the header
            If retainHeaders Then
                For j As Integer = 0 To columns - 1
                    table.Columns.Add(worksheet.GetRow(0).GetCell(j).ToString())
                Next
            Else
                For k As Integer = 0 To columns - 1
                    table.Columns.Add(k.ToString())
                Next
            End If

            'now we add each row to our new DataTable
            For x As Integer = 0 To rows - 1
                Dim row As DataRow = table.NewRow()
                For y As Integer = 0 To columns - 1
                    row(y) = worksheet.GetRow(x).GetCell(y).ToString()
                Next
                table.Rows.Add(row)
            Next

            ds.Tables.Add(table)
        Next

        worksheet = Nothing
        workbook = Nothing
    Catch ex As Exception
        Me.ReturnMessage = ex.ToString()
    End Try

    Return ds
End Function

Now we convert it to CSV

现在我们将其转换为 CSV

''' <summary>
''' Method for converting an XLS or XLSX file to CSV format without requiring any 3rd party
''' installs like Excel or the ACE/JET drivers
''' </summary>
''' <param name="delimiter">What's the file being delimited by</param>
''' <param name="retainHeaders">Are we exporting the headers as well</param>
''' <returns></returns>
''' <remarks></remarks>
Public Function WriteToCSV(ByVal delimiter As String, Optional ByVal retainHeaders As Boolean = True) As Boolean
    Try
        'Dim table As DataTable = GenerateDataTableFromXls(ExcelFile.SourceFile, ",", True, ExcelFile.WorksheetNum)
        Dim ds As DataSet = GenerateDataTableFromXls(ExcelFile.SourceFile, ",", True, ExcelFile.WorksheetNum)
        Dim count As Integer = ds.Tables.Count()

        If count > 1 Then
            For i As Integer = 0 To ds.Tables.Count() - 1
                If ds.Tables(i).Rows.Count() > 0 Then
                    Using writer = New StreamWriter(String.Format(ExcelFile.TargetFile & "{0}.txt", (i + 1).ToString()))
                        For Each row As DataRow In ds.Tables(i).Rows
                            Dim first As Boolean = True
                            For Each column As DataColumn In ds.Tables(i).Columns
                                If Not first Then
                                    writer.Write(",")
                                Else
                                    first = False
                                End If
                                Dim data = row(column.ColumnName).ToString().Replace("""", """""")
                                writer.Write(String.Format("""{0}""", data))
                            Next
                            writer.WriteLine()
                        Next

                    End Using
                Else
                    Throw New Exception(Me.ReturnMessage)
                End If
            Next
        Else
            If ds.Tables(0).Rows.Count() > 0 Then
                Using writer = New StreamWriter(ExcelFile.TargetFile & ".txt")
                    For Each row As DataRow In ds.Tables(0).Rows
                        Dim first As Boolean = True
                        For Each column As DataColumn In ds.Tables(0).Columns
                            If Not first Then
                                writer.Write(",")
                            Else
                                first = False
                            End If
                            Dim data = row(column.ColumnName).ToString().Replace("""", """""")
                            writer.Write(String.Format("""{0}""", data))
                        Next
                        writer.WriteLine()
                    Next

                End Using
            Else
                Throw New Exception(Me.ReturnMessage)
            End If
        End If
        Return True
    Catch ex As Exception
        Me.ReturnMessage = ex.ToString()
        Return False
    Finally
        If File.Exists(ExcelFile.SourceFile) Then
            File.Delete(ExcelFile.SourceFile)
        End If
    End Try
End Function

The error I'm getting is:

我得到的错误是：

System.IndexOutOfRangeException: Cannot find table 0. at System.Data.DataTableCollection.get_Item(Int32 index) at CSVConverter.Converter.WriteToCSV(String delimiter, Boolean retainHeaders) in E:\LodgingLogistics\Lodgx\CSVConverter\Converter.vb:line 56

System.IndexOutOfRangeException：在 E:\LodgingLogistics\Lodgx\CSVConverter\Converter.vb:line 56 中的 CSVConverter.Converter.WriteToCSV(String delimiter, Boolean retainHeaders) 处的 System.Data.DataTableCollection.get_Item(Int32 index) 处找不到表 0。

Which is this line

这是哪条线

If ds.Tables(0).Rows.Count() > 0 Then

Can someone take the time to show me where I'm going wrong here. I use NPOI because I am stuck using .NET 2.0 for the time being.

有人可以花时间告诉我我哪里出错了。我使用 NPOI 是因为我暂时无法使用 .NET 2.0。

回答by Phil Sattele

You're getting that error because it is entering that function with no tables found. You're COUNT variable is 0 causing it to execute the ELSE statement, where it assumes you have 1 table.

您收到该错误是因为它正在进入该函数而未找到任何表。你的 COUNT 变量是 0 导致它执行 ELSE 语句，它假设你有 1 个表。

Make sure you're using the latest version of NPOI.

确保您使用的是最新版本的 NPOI。