By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,855 Members | 1,988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,855 IT Pros & Developers. It's quick & easy.

HELP - How to Dump a Dataset into Excel???

P: n/a
Ed
Hi All,

I'm looking for an efficient way of dumping a table in a Dataset into
an Excel sheet. I'm currently copying data cell by cell and this is
rather inefficient for large Datasets.

My current code is as follows:

objExcel = New Excel.Application()
objWorkBooks = objExcel.Workbooks
objWorkBook = objWorkBooks.Add
objWorkSheet = objExcel.ActiveWorkbook.ActiveSheet
objWorkSheet.Name = sTitle
objExcel.Visible = True

'Column headers
For Each dcCol In dsExport.Tables(0).Columns
iColIndex += 1
objWorkSheet.Cells(1, iColIndex) = dcCol.ColumnName
objWorkSheet.Cells(1, iColIndex).Font.Bold = True
Next dcCol

'Now put in the actual data
'*******************************************
'THIS IS THE PART THAT TAKES A LONG TIME
'*******************************************
iRowIndex = 1
For Each drRow In dsExport.Tables(0).Rows
iRowIndex += 1
iColIndex = 0
For Each dcCol In dsExport.Tables(0).Columns
iColIndex += 1
objWorkSheet.Cells(iRowIndex, iColIndex) =
drRow(dcCol.ColumnName).ToString()
Next dcCol
Next drRow

'Autofit the cells
objExcel.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit()
Does anyone out there know how I can improve this? And I don't want
to use the XML method.

Many Thanks!!!
Nov 20 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
I believe what you need is
http://www.dotnetjohn.com/articles/articleid36.aspx

this !! :)

- Sahil Malik
Independent Consultant
You can reach me thru my blog - http://dotnetjunkies.com/WebLog/sahilmalik/

"Ed" <le****@hotmail.com> wrote in message
news:cf**************************@posting.google.c om...
Hi All,

I'm looking for an efficient way of dumping a table in a Dataset into
an Excel sheet. I'm currently copying data cell by cell and this is
rather inefficient for large Datasets.

My current code is as follows:

objExcel = New Excel.Application()
objWorkBooks = objExcel.Workbooks
objWorkBook = objWorkBooks.Add
objWorkSheet = objExcel.ActiveWorkbook.ActiveSheet
objWorkSheet.Name = sTitle
objExcel.Visible = True

'Column headers
For Each dcCol In dsExport.Tables(0).Columns
iColIndex += 1
objWorkSheet.Cells(1, iColIndex) = dcCol.ColumnName
objWorkSheet.Cells(1, iColIndex).Font.Bold = True
Next dcCol

'Now put in the actual data
'*******************************************
'THIS IS THE PART THAT TAKES A LONG TIME
'*******************************************
iRowIndex = 1
For Each drRow In dsExport.Tables(0).Rows
iRowIndex += 1
iColIndex = 0
For Each dcCol In dsExport.Tables(0).Columns
iColIndex += 1
objWorkSheet.Cells(iRowIndex, iColIndex) =
drRow(dcCol.ColumnName).ToString()
Next dcCol
Next drRow

'Autofit the cells
objExcel.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit()
Does anyone out there know how I can improve this? And I don't want
to use the XML method.

Many Thanks!!!

Nov 20 '05 #2

P: n/a
Ok another idea is, Excel 2003, can directly read or save to XML .. you
could use that facility instead.

- Sahil Malik
Independent Consultant
You can reach me thru my blog - http://dotnetjunkies.com/WebLog/sahilmalik/

"Ed" <le****@hotmail.com> wrote in message
news:cf**************************@posting.google.c om...
Hi All,

I'm looking for an efficient way of dumping a table in a Dataset into
an Excel sheet. I'm currently copying data cell by cell and this is
rather inefficient for large Datasets.

My current code is as follows:

objExcel = New Excel.Application()
objWorkBooks = objExcel.Workbooks
objWorkBook = objWorkBooks.Add
objWorkSheet = objExcel.ActiveWorkbook.ActiveSheet
objWorkSheet.Name = sTitle
objExcel.Visible = True

'Column headers
For Each dcCol In dsExport.Tables(0).Columns
iColIndex += 1
objWorkSheet.Cells(1, iColIndex) = dcCol.ColumnName
objWorkSheet.Cells(1, iColIndex).Font.Bold = True
Next dcCol

'Now put in the actual data
'*******************************************
'THIS IS THE PART THAT TAKES A LONG TIME
'*******************************************
iRowIndex = 1
For Each drRow In dsExport.Tables(0).Rows
iRowIndex += 1
iColIndex = 0
For Each dcCol In dsExport.Tables(0).Columns
iColIndex += 1
objWorkSheet.Cells(iRowIndex, iColIndex) =
drRow(dcCol.ColumnName).ToString()
Next dcCol
Next drRow

'Autofit the cells
objExcel.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit()
Does anyone out there know how I can improve this? And I don't want
to use the XML method.

Many Thanks!!!

Nov 20 '05 #3

P: n/a
If you are using Excel2000 or higher you can use a com ADO
inside Excel, if the source data comes from say Sql Server
or Access.

Dim RS As New ADODB.Recordset, conn As New ADODB.Connection
Set conn = ...
RS.Open...
Sheet1.Range("A1").CopyFromRecordset RS

If using Excel97 you can still use com ADO except that 97
doesn't support ADO and thus can't use

Sheet1.Range("A1").CopyFromRecordset RS

Have to use a Range object to write the data from the
recordset variable to the sheet.

If the source data is not from Sql Server (or something
you can connect to) then you can use Com ADO in your
vb.net app and write the data to Excel:

RS.CursorLocation = adUseClient
conn.Mode = adModeReadWrite
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test1.xls" & _
"Extended Properties=""Excel 9.0;HDR=NO;"""
For j = 0 to dt.Rows.Count -1
dr = dt.Rows(j)
strSql = "SELECT * FROM [Sheet1$A" & j + 1 & ":M" & j + 1
& "]"
RS.Open strSql, cn, adOpenDynamic, adLockPessimistic
For i = 0 to RS.Fields.Count - 1 : RS(i) = dr(i) : Next
RS.Update
RS.close
Next

Note: writing to Excel using com ADO (very fast) can only
do one row at a time. j will define each row in your
dataTable and each row in Excel. Here you will write to a
row in Excel from Range("A" & j + 1 & ":M" & j + 1).
Excel Rows start at 1. So you will have 2 loops, one for
each row and one for each column. With this method you
don't even have to make a reference to the Excel Object
library from your project.

Rich
-----Original Message-----
Hi All,

I'm looking for an efficient way of dumping a table in a Dataset intoan Excel sheet. I'm currently copying data cell by cell and this israther inefficient for large Datasets.

My current code is as follows:

objExcel = New Excel.Application()
objWorkBooks = objExcel.Workbooks
objWorkBook = objWorkBooks.Add
objWorkSheet = objExcel.ActiveWorkbook.ActiveSheet
objWorkSheet.Name = sTitle
objExcel.Visible = True

'Column headers
For Each dcCol In dsExport.Tables(0).Columns
iColIndex += 1
objWorkSheet.Cells(1, iColIndex) = dcCol.ColumnName
objWorkSheet.Cells(1, iColIndex).Font.Bold = True
Next dcCol

'Now put in the actual data
'*******************************************
'THIS IS THE PART THAT TAKES A LONG TIME
'*******************************************
iRowIndex = 1
For Each drRow In dsExport.Tables(0).Rows
iRowIndex += 1
iColIndex = 0
For Each dcCol In dsExport.Tables(0).Columns
iColIndex += 1
objWorkSheet.Cells(iRowIndex, iColIndex) =
drRow(dcCol.ColumnName).ToString()
Next dcCol
Next drRow

'Autofit the cells
objExcel.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit()
Does anyone out there know how I can improve this? And I don't wantto use the XML method.

Many Thanks!!!
.

Nov 20 '05 #4

P: n/a
Hi Ed,

It is very simple and the sample is here.

http://msdn.microsoft.com/library/de...ntodataset.asp

I hope this helps?

Cor
Nov 20 '05 #5

P: n/a
Hi Rich,

Is this not the answer for a Recordset?

Cor

Dim RS As New ADODB.Recordset, conn As New ADODB.Connection
Set conn = ...
RS.Open...
Sheet1.Range("A1").CopyFromRecordset RS

If using Excel97 you can still use com ADO except that 97
doesn't support ADO and thus can't use

Sheet1.Range("A1").CopyFromRecordset RS

Have to use a Range object to write the data from the
recordset variable to the sheet.

If the source data is not from Sql Server (or something
you can connect to) then you can use Com ADO in your
vb.net app and write the data to Excel:

RS.CursorLocation = adUseClient
conn.Mode = adModeReadWrite
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test1.xls" & _
"Extended Properties=""Excel 9.0;HDR=NO;"""
For j = 0 to dt.Rows.Count -1
dr = dt.Rows(j)
strSql = "SELECT * FROM [Sheet1$A" & j + 1 & ":M" & j + 1
& "]"
RS.Open strSql, cn, adOpenDynamic, adLockPessimistic
For i = 0 to RS.Fields.Count - 1 : RS(i) = dr(i) : Next
RS.Update
RS.close
Next

Note: writing to Excel using com ADO (very fast) can only
do one row at a time. j will define each row in your
dataTable and each row in Excel. Here you will write to a
row in Excel from Range("A" & j + 1 & ":M" & j + 1).
Excel Rows start at 1. So you will have 2 loops, one for
each row and one for each column. With this method you
don't even have to make a reference to the Excel Object
library from your project.

Nov 20 '05 #6

P: n/a
http://www.kjmsolutions.com/datasetarray.htm

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:em**************@TK2MSFTNGP10.phx.gbl:
Hi Ed,

It is very simple and the sample is here.
http://msdn.microsoft.com/library/de...us/dv_vbcode/h
tml/vbtskcodeexamplereadingexceldataintodataset.asp

I hope this helps?

Cor


Nov 20 '05 #7

P: n/a
Hi Kim,

I see it now, I was as well reading it wrong.
Dumping a table in a dataset .............................. in an Excel
sheet and that I did not read as it was.

Otherwise I had given your link.

:-)

Cor
Nov 20 '05 #8

P: n/a
My name is "Kelly"...

:)

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:uj**************@TK2MSFTNGP12.phx.gbl:
Hi Kim,

I see it now, I was as well reading it wrong.
Dumping a table in a dataset .............................. in an Excel
sheet and that I did not read as it was.

Otherwise I had given your link.

:-)

Cor


Nov 20 '05 #9

P: n/a
Hi Kelly,

I knew that there was a posibillity that you would write this KJM.

I saw it to late,

Sorry

:-)

Cor
My name is "Kelly"...


Nov 20 '05 #10

P: n/a
Ed
Thanks for all your responses... I tried the example and I keep
getting the error message:
"Exception from HRESULT: 0x800A03EC."

Whenever this line executes:
objWorkSheet.Range("A1").Resize(iRowCount, iColumnCount).Value =
DataArray

Any ideas?
ed

scorpion53061 <ad***@nospampleasekjmsolutions.com> wrote in message news:<ui**************@TK2MSFTNGP12.phx.gbl>...
http://www.kjmsolutions.com/datasetarray.htm

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:em**************@TK2MSFTNGP10.phx.gbl:
Hi Ed,

It is very simple and the sample is here.
http://msdn.microsoft.com/library/de...us/dv_vbcode/h
tml/vbtskcodeexamplereadingexceldataintodataset.asp

I hope this helps?

Cor

Nov 20 '05 #11

P: n/a
Post the code you are using and an attachment with sample data.

I will run it through for you.

"Ed" <le****@hotmail.com> wrote in message
news:cf*************************@posting.google.co m:
Thanks for all your responses... I tried the example and I keep
getting the error message:
"Exception from HRESULT: 0x800A03EC."

Whenever this line executes:
objWorkSheet.Range("A1").Resize(iRowCount, iColumnCount).Value =
DataArray

Any ideas?
ed

scorpion53061 <ad***@nospampleasekjmsolutions.com> wrote in message
news:<ui**************@TK2MSFTNGP12.phx.gbl>...
http://www.kjmsolutions.com/datasetarray.htm

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:em**************@TK2MSFTNGP10.phx.gbl:
Hi Ed,

It is very simple and the sample is here.

http://msdn.microsoft.com/library/de...us/dv_vbcode/h

tml/vbtskcodeexamplereadingexceldataintodataset.asp

I hope this helps?

Cor


Nov 20 '05 #12

P: n/a
No problem. It gave me a good laugh this morning.... :)

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:ez*************@TK2MSFTNGP09.phx.gbl:
Hi Kelly,

I knew that there was a posibillity that you would write this KJM.

I saw it to late,

Sorry

:-)

Cor
My name is "Kelly"...


Nov 20 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.