473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Exporting to Excel

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

Nov 20 '05 #1
3 3704
<an*******@discussions.microsoft.com> wrote in
news:e0*************@TK2MSFTNGP10.phx.gbl:
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.

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/
Nov 20 '05 #2
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.

--
Rgds,
Anand
VB.NET MVP
http://www.dotnetindia.com
"an*******@discussions.microsoft.com" wrote:
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

Nov 20 '05 #3
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

Nov 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: sridevi | last post by:
Hello How to export data from ms-access database to excel worksheet using ASP. mainly i need to export data to multiple worksheets. it is very urgent to us. i have a sample code which works...
4
by: D | last post by:
I've created a report with many subreports of aggregate data. I want my client to be able to export this data to Excel to make her charts, etc. Only one problem: one of the fields is a "SchoolYear"...
2
by: G | last post by:
When I export data from access to excel by with "export" or "Analyze with" I seem to loose parts of some fields (long text strings). Is there a way to export it all to excel? Thanks G
2
by: Kenneth | last post by:
How do I remove the limitation in Access that deny me from exporting 24000 rows and 17 columns (in a query) into Excel? Kenneth
5
by: Neil | last post by:
Hi I'm currently exporting my datagrid to excel, taking advantage of the fact that excel can render html, the problem is that when i click the button to export it opens in browser, I want the button...
2
by: Mustufa Baig | last post by:
Hi everybody, I have an ASP.NET website where clients can view their monthly billings by selecting different options. One of the option is the way they want to see the report i.e. whether they...
1
by: Mustufa Baig | last post by:
I have an ASP.NET website where I am showing off crystal reports to users by exporting them to pdf format. Following is the code: ---------------- 1 Private Sub ExportReport() 2 Dim oStream...
2
by: bienwell | last post by:
Hi, I have a question about exporting data from datagrid control into Excel file in ASP.NET. On my Web page, I have a linkbutton "Export data". This link will call a Sub Function to perform...
2
by: Snozz | last post by:
The short of it: If you needed to import a CSV file of a certain structure on a regular basis(say 32 csv files, each to one a table in 32 databases), what would be your first instinct on how to...
4
by: Tom | last post by:
I have a gridview on all of my web pages in my web app and they all export to excel. I have one page where the gridview is binding to a datatable that i created and only the first column is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.