Performance Export Excel

[Sébastien]

Hi,

I have Performance Issues with the Excel spreadprocessing export. I just migrated an Excel export that used until now the libraries "Component One". The export takes now 30 seconds for 7221 rows and 88 columns, against 5 seconds before. Here is the code:

001.        ... Fill the table...
002.        Using excBook As New Workbook
003.
004.            excBook.SuspendLayoutUpdate()
005.            Using (New UpdateScope(
006.                Function() (excBook.History.IsEnabled = False),
007.                Function() (excBook.History.IsEnabled = True)))
008.
009.                Dim excSheet As Worksheet
010.                excSheet = excBook.Worksheets.Add()
011.                excSheet.Name = tbl.TableName
012.                fill_ExcelSheet(tbl, excSheet)
013.
014.            End Using
015.            excBook.ResumeLayoutUpdate()
016.
017.            'Save the Excel-File
018.            Dim formatProvider As Telerik.Windows.Documents.Spreadsheet.FormatProviders.IWorkbookFormatProvider _
019.                = New Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider()
020.
021.            Using fsOutput As New FileStream(strFilename, FileMode.Create)
022.                formatProvider.Export(excBook, fsOutput)
023.            End Using
024.
025.        End Using
026.
027.Private Shared Function fill_ExcelSheet(ByVal tblDaten As DataTable, ByVal excSheet As Worksheet) As Boolean
028.    Dim bolOK As Boolean = False
029.    Dim intCntCols As Integer, intCntRows As Integer
030.    Dim strColCaption As String
031.
032.    Try
033.
034.        'Set Columns-Description
035.        For intCntCols = 0 To tblDaten.Columns.Count - 1
036.            strColCaption = get_AttributCaption(tblDaten.Prefix, tblDaten.Columns(intCntCols).ColumnName)
037.            excSheet.Cells.Item(0, intCntCols).SetValue(strColCaption)
038.        Next
039.
040.        'Fill the Sheet with data
041.        excSheet.Cells.Item(1, 0, tblDaten.Rows.Count, tblDaten.Columns.Count).SetFormat(New CellValueFormat("@"))
042.        For intCntRows = 0 To tblDaten.Rows.Count - 1
043.            For intCntCols = 0 To tblDaten.Columns.Count - 1
044.                Dim colName As String = tblDaten.Columns(intCntCols).ColumnName
045.                If (tblDaten.Columns(colName).DataType Is GetType(Date)) Then
046.                    If Not tblDaten.Rows(intCntRows).IsNull(colName) Then
047.                        If CType(tblDaten.Rows(intCntRows).Item(colName), Date).TimeOfDay.Ticks > 0 Then
048.                            excSheet.Cells.Item(intCntRows + 1, intCntCols).SetValue(CType(tblDaten.Rows(intCntRows).Item(colName), Date).ToString)
049.                        Else
050.                            excSheet.Cells.Item(intCntRows + 1, intCntCols).SetValue(CType(tblDaten.Rows(intCntRows).Item(colName), Date).ToShortDateString)
051.                        End If
052.                    End If
053.                ElseIf (tblDaten.Columns(colName).DataType Is GetType(System.Guid)) Then
054.                    excSheet.Cells.Item(intCntRows + 1, intCntCols).SetValue(tblDaten.Rows(intCntRows).Item(colName).ToString)
055.                ElseIf (tblDaten.Columns(colName).DataType Is GetType(String)) Then
056.
057.                    excSheet.Cells.Item(intCntRows + 1, intCntCols).SetValue(tblDaten.Rows(intCntRows).Item(colName).ToString)
058.                ElseIf (tblDaten.Columns(colName).DataType Is GetType(Integer)) _
059.                    Or (tblDaten.Columns(colName).DataType Is GetType(Int16)) _
060.                    Or (tblDaten.Columns(colName).DataType Is GetType(Single)) _
061.                    Or (tblDaten.Columns(colName).DataType Is GetType(Decimal)) _
062.                    Or (tblDaten.Columns(colName).DataType Is GetType(Double)) Then
063.                    If Not tblDaten.Rows(intCntRows).IsNull(colName) Then
064.                        excSheet.Cells.Item(intCntRows + 1, intCntCols).SetValue(CDbl(tblDaten.Rows(intCntRows).Item(colName)))
065.                    End If
066.                ElseIf (tblDaten.Columns(colName).DataType Is GetType(Boolean)) Then
067.                    If Not tblDaten.Rows(intCntRows).IsNull(colName) Then
068.                        excSheet.Cells.Item(intCntRows + 1, intCntCols).SetValue(CBool(tblDaten.Rows(intCntRows).Item(colName)))
069.                    End If
070.                Else
071.                    If Not tblDaten.Rows(intCntRows).IsNull(colName) Then
072.                        excSheet.Cells.Item(intCntRows + 1, intCntCols).SetValue(tblDaten.Rows(intCntRows).Item(colName))
073.                    End If
074.                End If
075.            Next
076.            Application.DoEvents()
077.        Next
078.
079.        'Print properties
080.        Dim pageSetup As Printing.WorksheetPageSetup = excSheet.WorksheetPageSetup
081.        pageSetup.PaperType = Telerik.Windows.Documents.Model.PaperTypes.A4
082.        pageSetup.PageOrientation = Telerik.Windows.Documents.Model.PageOrientation.Landscape
083.        Dim HeadFootSettings As Printing.HeaderFooterSettings = pageSetup.HeaderFooterSettings
084.        HeadFootSettings.Header.CenterSection.Text = excSheet.Name
085.        HeadFootSettings.Footer.CenterSection.Text = DateTime.Now.ToString
086.
087.        If excSheet.Workbook.Styles("NormalStyle") Is Nothing Then
088.            Dim normalStyleCells As Telerik.Windows.Documents.Spreadsheet.PropertySystem.CellStyle = excSheet.Workbook.Styles.Add("NormalStyle")
089.            Dim borderDotCell As New CellBorder(CellBorderStyle.Hair, New ThemableColor(ThemeColorType.Text1))
090.            Dim borderThinCell As New CellBorder(CellBorderStyle.Thin, New ThemableColor(ThemeColorType.Text1))
091.            Dim fill As IFill = PatternFill.CreateSolidFill(New ThemableColor(Windows.Media.Colors.White))
092.            normalStyleCells.BeginUpdate()
093.            normalStyleCells.TopBorder = borderDotCell
094.            normalStyleCells.BottomBorder = borderDotCell
095.            normalStyleCells.LeftBorder = borderThinCell
096.            normalStyleCells.RightBorder = borderThinCell
097.            normalStyleCells.FontFamily = New ThemableFontFamily("Arial")
098.            normalStyleCells.FontSize = UnitHelper.PointToDip(8)
099.            normalStyleCells.Fill = fill
100.            normalStyleCells.EndUpdate()
101.
102.            Dim headerStyleCells As Telerik.Windows.Documents.Spreadsheet.PropertySystem.CellStyle = excSheet.Workbook.Styles.Add("HeaderStyle")
103.            fill = PatternFill.CreateSolidFill(New ThemableColor(Windows.Media.Color.FromRgb(192, 192, 192)))
104.            headerStyleCells.CopyPropertiesFrom(normalStyleCells)
105.            headerStyleCells.BeginUpdate()
106.            headerStyleCells.IsWrapped = False
107.            headerStyleCells.TopBorder = borderThinCell
108.            headerStyleCells.BottomBorder = borderThinCell
109.            headerStyleCells.Fill = fill
110.            headerStyleCells.EndUpdate()
111.
112.            Dim footStyleCells As Telerik.Windows.Documents.Spreadsheet.PropertySystem.CellStyle = excSheet.Workbook.Styles.Add("FooterStyle")
113.            footStyleCells.CopyPropertiesFrom(normalStyleCells)
114.            footStyleCells.BottomBorder = borderThinCell
115.
116.            Dim leftAlignStyleCells As Telerik.Windows.Documents.Spreadsheet.PropertySystem.CellStyle = excSheet.Workbook.Styles.Add("LeftAlignStyle")
117.            leftAlignStyleCells.HorizontalAlignment = RadHorizontalAlignment.Left
118.            Dim rightAlignStyleCells As Telerik.Windows.Documents.Spreadsheet.PropertySystem.CellStyle = excSheet.Workbook.Styles.Add("RightAlignStyle")
119.            rightAlignStyleCells.HorizontalAlignment = RadHorizontalAlignment.Right
120.        End If
121.
122.        excSheet.Columns(0, tblDaten.Columns.Count - 1).AutoFitWidth()
123.        Dim rowHeight As New RowHeight(UnitHelper.PointToDip(11.5), True)
124.        excSheet.Rows(0, tblDaten.Rows.Count).SetHeight(rowHeight)
125.
126.        'Format cells
127.        For col As Integer = 0 To tblDaten.Columns.Count - 1
128.            'Links- oder Rechtsbündig setzen
129.            Dim strAHorz As String = "LeftAlignStyle"
130.            Dim typCol As Type = tblDaten.Columns(col).DataType
131.            If (typCol Is GetType(Integer)) Or (typCol Is GetType(Single)) Or (typCol Is GetType(Decimal)) Or (typCol Is GetType(Double)) Then
132.                strAHorz = "RightAlignStyle"
133.            End If
134.
135.            excSheet.Cells(0, col).SetStyleName("HeaderStyle")
136.            excSheet.Cells(1, col, tblDaten.Rows.Count - 1, col).SetStyleName("NormalStyle")
137.            excSheet.Cells(tblDaten.Rows.Count, col).SetStyleName("FooterStyle")
138.
139.            excSheet.Cells(0, col, tblDaten.Rows.Count, col).SetStyleName(strAHorz)
140.        Next
141.        bolOK = True
142.    Catch ex As Exception
143.        show_AppErrorMsgBox(ex)
144.    End Try
145.    Return bolOK
146.End Function

 

The code that takes the most time is:

 - Lines 42 - 77: Fill the sheet with data: 12 seconds
 - Line 122: AutoFitWidth(): 6 seconds --> (It's important, cannot be removed)
 - Line 22: formatProvider.Export(excBook, fsOutput): 11 seconds --> That's very long !!!

Can you check my code and tell me if there are any improvements, please? So i can unfortunately not use the libraries.

Thanks in advance

[Boby]

Hello Sébastien,

We investigated the code and it seems that there is nothing wrong with it - just the large data set creates large document model which needs more time and memory to be processed. 

For cases like this, we provide RadSpreadStreamProcessing, which provide many times better performance as it doesn't keep the model in the memory (the content is written directly to the zipped XML stream), but the AutoFitWidth feature is not supported there. 

Regards,
Boby
Telerik by Progress