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!!!
.
