Exporting to Excel

[Guest]

Hello,

I have a routine in my Windows application that exports the contents of a
datagrid to Excel. It is modeled closely after the HowTo example on MSDN:
http://tinyurl.com/5g2jm.

Depending on the number of rows/columns in the datagrid, it can be extremely
slow (several minutes to populate a 1000x50 spreadsheet). That is
understandable, looking at the number of steps to copy over just one row.

I did notice that when you physically highlight the entire datagrid and
press Ctrl+C to copy it to the Clipboard and then manually paste that into
Excel, it is nearly instantaneous. How can I duplicate that Copy/Paste
operation? In what data format should I copy to the Clipboard for Excel to
recognize it?

Thank you

 

[Lucas Tam]

I did notice that when you physically highlight the entire datagrid
and press Ctrl+C to copy it to the Clipboard and then manually paste
that into Excel, it is nearly instantaneous. How can I duplicate that
Copy/Paste operation? In what data format should I copy to the
Clipboard for Excel to recognize it?

If you tab delimit the data, Copy and Paste will recognize it in Excel.

 

[Guest]

You could also try using ADO and the jet driver to write to an excel file. The driver is quite buggy, but should be easy just writing into a file.

Or just write into a .csv file and excel should be able to open it.

 

[Guest]

Thank you for your replies, Lucus and Anand.

I finally got a chance this morning to try your suggestions. I had problems
with Excel recognizing the comma-delimitted data...most likely something I'm
messing up. However, I tried tab-delimitted and it worked perfectly the
first time! This is very exciting (geesh, I'm a nerd), as it reduces the
worst-case export from 30 minutes to about 3 seconds!

Below is the code to do this. (It assumes a reference to the Microsoft
Excel 10.0 Object Library, but you can use other versions with very little
modification.)

Thanks again; your tips helped tremendously!

Eric

'\\\\
Private Sub ExportToExcel(ByRef tbl As DataTable)
' This routine copies the contents of a data table, named "dt"
(declared
' Private within this Public class), to the Windows Clipboard in a
tab-
' delimitted format. It then creates an Excel spreadsheet and
pastes the
' contents of the Clipboard to the spreadsheet.

Dim sb As New StringBuilder
Dim row, col As Integer

' Add the title.
sb.Append(tbl.TableName & vbNewLine & vbNewLine)

' Add column headers.
For col = 0 To tbl.Columns.Count - 1
If Not IsDBNull(tbl.Columns.Item(col).ColumnName) Then
sb.Append(tbl.Columns.Item(col).ColumnName)
End If
sb.Append(vbTab)
Next
sb.Append(vbNewLine)

' Add rows.
For row = 0 To tbl.Rows.Count - 1
For col = 0 To tbl.Columns.Count - 1
If Not IsDBNull(tbl.Rows(row)(col)) Then
If TypeOf tbl.Rows(row)(col) Is DateTime Then
Dim d As DateTime = tbl.Rows(row)(col)
sb.Append(d.ToShortDateString)
Else
sb.Append(tbl.Rows(row)(col))
End If
End If
sb.Append(vbTab)
Next
sb.Append(vbNewLine)
Next

' Copy tab-delmitted data to Clipboard.
Clipboard.SetDataObject(sb.ToString)

'MessageBox.Show("Table copied to Clipboard.")

' Create Excel Objects
Dim ExcelApp As Excel.Application
Dim Book As Excel.Workbook
Dim Sheet As Excel.Worksheet
Dim Range As Excel.Range

' Start Excel and get Application object:
ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True

' Add a new workbook
Book = ExcelApp.Workbooks.Add
Sheet = Book.ActiveSheet
Sheet.Name = "Orders"

' Paste the Clipboard contents.
Sheet.Paste()

' Format column headers.
Range = Sheet.Rows(3)
Range.Font.Bold = True

' AutoFit Columns
Range = Sheet.Range("A1", "IA1")
Range.EntireColumn.AutoFit()

' Format title.
Range = Sheet.Cells(1, 1)
Range.Font.Bold = True
Range.Font.Size = 14

' Add date/time stamp.
Range = Sheet.Cells(2, tbl.Columns.Count)
Range.Value = "Report Created: " & Now.ToString
Range.Font.Size = 8
Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight

' Center title across selection.
Dim cellStart As Excel.Range = ExcelApp.Range("A1")
Dim cellEnd As Excel.Range = _
DirectCast(Sheet.Cells(1, _
tbl.Columns.Count), Excel.Range)
Dim rng As Excel.Range = _
ExcelApp.Range(cellStart, cellEnd)
rng.Merge()
rng.HorizontalAlignment = _
Excel.XlHAlign.xlHAlignCenterAcrossSelection
End Sub
'////