Я пытаюсь использовать NPOI для преобразования файлов XLS и XLSX в файлы CSV. Сейчас мой код отлично работает с файлами XLS, но у меня возникают проблемы с файлами XLSX.
Вот код для преобразования файлов в файлы CSV с использованием NPOI (на самом деле этот код преобразует его в набор данных, преобразование в 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
Теперь мы конвертируем его в 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
Ошибка, которую я получаю:
System.IndexOutOfRangeException: не удается найти таблицу 0. в System.Data.DataTableCollection.get_Item (индекс Int32) в CSVConverter.Converter.WriteToCSV (разделитель строк, логическое значение continueHeaders) в E:\LodgingLogistics\Lodgx\CSVConverter\Converter.vb: строка 56
Какая это линия
If ds.Tables(0).Rows.Count() > 0 Then
Может ли кто-нибудь найти время, чтобы показать мне, где я ошибаюсь. Я использую NPOI, потому что пока застрял на .NET 2.0.