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