Forums Ultimate UI for ASP.NET Web Forms
Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
Close
Randy
140
Excel Export IndexOutOfRangeException
posted

In order to combat the various speed issues when exporting large amounts of data to excel I implemented a routine that converts a dataset to an excel spreadsheet.  It worked correctly in version 11.1.20111.1006 of infragistics but since I upgraded to 11.1.20111.2036 I have had one particular dataset that causes the conversion routine to fail randomly with an IndexOutOfRangeException.  Using the debugger I can check the value of the excel cell and it correctly returns "Nothing".  I can check the datatable row and column that are being read and correctly retrive the string value of "198004"  If I trap for that cell and ignore it, then the procedure continues to run and crashes later on with the same error on several other cells.  The data table being used has 9259 rows and 23 columns so I should not be exceeding any type of excel limit.  Did something change between the two versions?

The line of code the program crashes on is:

sheetToAddTo.Rows(iRow + 1).Cells(iCol).Value = dataTable.Rows(iRow)(iCol).ToString

 

Here is the function including the loops

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Private

 

Shared Sub AddData(ByVal dataTable As System.Data.DataTable, ByRef sheetToAddTo As Infragistics.Documents.Excel.Worksheet)

 

 

Dim i As Integer = 0

 

 

'add the column names from the datatable

 

 

For i = 0 To dataTable.Columns.Count - 1

sheetToAddTo.Rows(0).Cells(i).Value = dataTable.Columns(i).ColumnName

 

 

Next

 

 

'insert the data into the object[,]

 

 

Dim iRow As Integer = 0

 

 

Dim iCol As Integer = 0

 

 

For iRow = 0 To dataTable.Rows.Count - 1

 

 

For iCol = 0 To dataTable.Columns.Count - 1

 

 

 

 

Try

sheetToAddTo.Rows(iRow + 1).Cells(iCol).Value = dataTable.Rows(iRow)(iCol).ToString

 

 

Catch ex As ArgumentException

 

 

If ex.Message = "String values assigned to a cell cannot have a length greater than 32767."

Then

 

 

'trapping for exceptionally long strings (> 32767) this is an excel limit

 

 

'customer has requested that we get the last 32767 characters

 

 

Dim TrappedLongString As String = dataTable.Rows(iRow)(iCol).ToString

 

 

'change vbcrlf to chr(13), that saves us one character per crlf so we can actually hit the 32767 mark.

TrappedLongString = TrappedLongString.Replace(vbCrLf, Chr(13))

sheetToAddTo.Rows(iRow + 1).Cells(iCol).Value = Mid(TrappedLongString, TrappedLongString.Length - 32766, 32767)

 

 

End If

 

 

End Try

 

 

 

 

Next

 

 

 

Next

 

 

 

 

End Sub