Guten Tag alle zusammen,
ich bin gerade dabei ein Code zu schreiben der 3 große DataGridViews (ca. 3300 Zeilen x 9 Spalten , Integer/DateTime) nach Excel exportieren soll. Dazu führe ich die GridView in ein DataSet und von dort nach Excel. Allerdings dauert mir das zu lange ca. 5 Minuten. 1. Frage : Gibt es eine schnellere Variante oder seht ihr eine Leistungsschwäche im folgenden Code?
2. Frage: Kann man auch ChartSeries "Abbildungen" nach Excel exportieren und wie würde es funktionieren?
Vielen Dank!
ich bin gerade dabei ein Code zu schreiben der 3 große DataGridViews (ca. 3300 Zeilen x 9 Spalten , Integer/DateTime) nach Excel exportieren soll. Dazu führe ich die GridView in ein DataSet und von dort nach Excel. Allerdings dauert mir das zu lange ca. 5 Minuten. 1. Frage : Gibt es eine schnellere Variante oder seht ihr eine Leistungsschwäche im folgenden Code?
2. Frage: Kann man auch ChartSeries "Abbildungen" nach Excel exportieren und wie würde es funktionieren?
Visual Basic-Quellcode
- [/b] Dim regDate As Date = Date.Now()
- Dim strDate As String = regDate.ToString("MM-dd-yyyy")
- Dim saveFileDialog1 As New SaveFileDialog()
- saveFileDialog1.Filter = "Excel Arbeitsmappe (.xls)|*.xls"
- saveFileDialog1.Title = "Als Excel Datei exportieren"
- saveFileDialog1.FileName = "exportedxx-" & strDate & ".xls"
- If saveFileDialog1.FileName IsNot "" And saveFileDialog1.ShowDialog = DialogResult.OK = True Then
- If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
- Exit Sub
- End If
- System.Windows.Forms.Cursor.Current = Cursors.WaitCursor
- Dim dset As New DataSet
- 'add table to dataset
- dset.Tables.Add()
- 'add column to that table
- For i As Integer = 0 To DataGridView1.ColumnCount - 1
- dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
- Next
- 'add rows to the table
- Dim dr1 As DataRow
- For i As Integer = 0 To DataGridView1.RowCount - 1
- dr1 = dset.Tables(0).NewRow
- For j As Integer = 0 To DataGridView1.Columns.Count - 1
- dr1(j) = DataGridView1.Rows(i).Cells(j).Value
- Next
- dset.Tables(0).Rows.Add(dr1)
- Next
- 'add table to dataset
- dset.Tables.Add()
- 'add column to that table
- For i As Integer = 0 To DataGridView2.ColumnCount - 1
- dset.Tables(1).Columns.Add(DataGridView2.Columns(i).HeaderText)
- Next
- 'add rows to the table
- Dim dr2 As DataRow
- For i As Integer = 0 To DataGridView2.RowCount - 1
- dr2 = dset.Tables(1).NewRow
- For j As Integer = 0 To DataGridView2.Columns.Count - 1
- dr2(j) = DataGridView2.Rows(i).Cells(j).Value
- Next
- dset.Tables(1).Rows.Add(dr2)
- Next
- dset.Tables.Add()
- For i As Integer = 0 To DataGridView3.ColumnCount - 1
- dset.Tables(2).Columns.Add(DataGridView3.Columns(i).HeaderText)
- Next
- 'add rows to the table
- Dim dr3 As DataRow
- For i As Integer = 0 To DataGridView3.RowCount - 1
- dr3 = dset.Tables(2).NewRow
- For j As Integer = 0 To DataGridView3.Columns.Count - 1
- dr3(j) = DataGridView3.Rows(i).Cells(j).Value
- Next
- dset.Tables(2).Rows.Add(dr3)
- Next
- Dim excel As New Microsoft.Office.Interop.Excel.Application
- Dim wBook As Microsoft.Office.Interop.Excel.Workbook
- Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
- Dim wSheet2 As Microsoft.Office.Interop.Excel.Worksheet
- Dim wSheet3 As Microsoft.Office.Interop.Excel.Worksheet
- 'excel.Workbooks.Close()
- wBook = excel.Workbooks.Add()
- wSheet = wBook.ActiveSheet()
- wSheet.Name = "xxx"
- Dim dt As System.Data.DataTable = dset.Tables(0)
- Dim dt2 As System.Data.DataTable = dset.Tables(1)
- Dim dt3 As System.Data.DataTable = dset.Tables(2)
- Dim dc As System.Data.DataColumn
- Dim dr As System.Data.DataRow
- Dim colIndex As Integer = 0
- Dim rowIndex As Integer = 0
- For Each dc In dt.Columns
- colIndex = colIndex + 1
- excel.Cells(1, colIndex) = dc.ColumnName
- excel.Cells(1, colIndex).Interior.ColorIndex = 23
- excel.Cells(1, colIndex).Font.Name = "Arial"
- excel.Cells(1, colIndex).Font.Size = 10
- Next
- For Each dr In dt.Rows
- rowIndex = rowIndex + 1
- colIndex = 0
- For Each dc In dt.Columns
- colIndex = colIndex + 1
- excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
- excel.Cells(rowIndex + 1, colIndex).EntireRow.Borders.ColorIndex = 1
- Next
- Next
- wSheet.Columns.AutoFit()
- wSheet2 = wBook.Sheets.Add()
- wSheet2 = wBook.ActiveSheet()
- wSheet2.Name = "xxx"
- colIndex = 0
- rowIndex = 0
- For Each dc In dt2.Columns
- colIndex = colIndex + 1
- excel.Cells(1, colIndex) = dc.ColumnName
- excel.Cells(1, colIndex).Interior.ColorIndex = 23
- excel.Cells(1, colIndex).Font.Name = "Arial"
- excel.Cells(1, colIndex).Font.Size = 10
- Next
- For Each dr In dt2.Rows
- rowIndex = rowIndex + 1
- colIndex = 0
- For Each dc In dt2.Columns
- colIndex = colIndex + 1
- excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
- excel.Cells(rowIndex + 1, colIndex).Borders.ColorIndex = 1
- Next
- Next
- wSheet2.Columns.AutoFit()
- wSheet3 = wBook.Sheets.Add()
- wSheet3 = wBook.ActiveSheet()
- wSheet3.Name = "xxx"
- For Each dc In dt3.Columns
- colIndex = colIndex + 1
- excel.Cells(1, colIndex) = dc.ColumnName
- excel.Cells(1, colIndex).Interior.ColorIndex = 23
- excel.Cells(1, colIndex).Font.Name = "Arial"
- excel.Cells(1, colIndex).Font.Size = 10
- Next
- For Each dr In dt3.Rows
- rowIndex = rowIndex + 1
- colIndex = 0
- For Each dc In dt3.Columns
- colIndex = colIndex + 1
- excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
- excel.Cells(rowIndex + 1, colIndex).Borders.ColorIndex = 1
- Next
- Next
- Dim strFileName As String = saveFileDialog1.FileName
- Dim blnFileOpen As Boolean = False
- Try
- Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
- fileTemp.Close()
- Catch ex As Exception
- blnFileOpen = False
- End Try
- If System.IO.File.Exists(strFileName) Then
- System.IO.File.Delete(strFileName)
- End If
- wBook.SaveAs(strFileName)
- excel.Workbooks.Open(strFileName)
- excel.Visible = True[b]
Vielen Dank!