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

  • Thread starter Thread starter Ed
  • Start date Start date
E

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!!!
 
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
 
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
 
Hi Kelly,

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

I saw it to late,

Sorry

:-)

Cor
 
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
 
Post the code you are using and an attachment with sample data.

I will run it through for you.
 
Back
Top