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(B yRef 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.T ableName & vbNewLine & vbNewLine)
' Add column headers.
For col = 0 To tbl.Columns.Cou nt - 1
If Not IsDBNull(tbl.Co lumns.Item(col) .ColumnName) Then
sb.Append(tbl.C olumns.Item(col ).ColumnName)
End If
sb.Append(vbTab )
Next
sb.Append(vbNew Line)
' Add rows.
For row = 0 To tbl.Rows.Count - 1
For col = 0 To tbl.Columns.Cou nt - 1
If Not IsDBNull(tbl.Ro ws(row)(col)) Then
If TypeOf tbl.Rows(row)(c ol) Is DateTime Then
Dim d As DateTime = tbl.Rows(row)(c ol)
sb.Append(d.ToS hortDateString)
Else
sb.Append(tbl.R ows(row)(col))
End If
End If
sb.Append(vbTab )
Next
sb.Append(vbNew Line)
Next
' Copy tab-delmitted data to Clipboard.
Clipboard.SetDa taObject(sb.ToS tring)
'MessageBox.Sho w("Table copied to Clipboard.")
' Create Excel Objects
Dim ExcelApp As Excel.Applicati on
Dim Book As Excel.Workbook
Dim Sheet As Excel.Worksheet
Dim Range As Excel.Range
' Start Excel and get Application object:
ExcelApp = CreateObject("E xcel.Applicatio n")
ExcelApp.Visibl e = True
' Add a new workbook
Book = ExcelApp.Workbo oks.Add
Sheet = Book.ActiveShee t
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.EntireCol umn.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.Cou nt)
Range.Value = "Report Created: " & Now.ToString
Range.Font.Size = 8
Range.Horizonta lAlignment = Excel.XlHAlign. xlHAlignRight
' Center title across selection.
Dim cellStart As Excel.Range = ExcelApp.Range( "A1")
Dim cellEnd As Excel.Range = _
DirectCast(Shee t.Cells(1, _
tbl.Columns.Cou nt), Excel.Range)
Dim rng As Excel.Range = _
ExcelApp.Range( cellStart, cellEnd)
rng.Merge()
rng.HorizontalA lignment = _
Excel.XlHAlign. xlHAlignCenterA crossSelection
End Sub
'////
<an*******@disc ussions.microso ft.com> wrote in message
news:e0******** *****@TK2MSFTNG P10.phx.gbl...
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
