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
'////
<an*******@discussions.microsoft.com> wrote in message
news:e0*************@TK2MSFTNGP10.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
