帮助 - 如何将数据集转储到Excel ??? [英] HELP - How to Dump a Dataset into Excel???
问题描述
大家好，
我正在寻找一种有效的方法将数据集中的表转储到Excel表单中的
。我正在逐个单元格复制数据，这对于大型数据集来说相当低效。
我目前的代码如下：
objExcel =新Excel.Application（）
objWorkBooks = objExcel.Workbooks
objWorkBook = objWorkBooks.Add
objWorkSheet = objExcel.ActiveWorkbook.ActiveSheet
objWorkSheet.Name = sTitle
objExcel.Visible = True
''列标题
每个dcCol在dsExport.Tables（0）.Columns
iColIndex + = 1
objWorkSheet.Cells（1，iColIndex）= dcCol。 ColumnName
objWorkSheet.Cells（1，iColIndex）.Font.Bold = True
下一个dcCol
''现在放入实际数据
''************************************* ******
''这是需要花费很长时间的部分
''*************** ****************************
iRowIndex = 1
每个drRow在dsExport.Tables（0）中.Rows
iRowIndex + = 1
iColIndex = 0
每个dcCol在dsExport.Tables（0）.Columns
iColIndex + = 1
objWorkSheet.Cells（iRowIndex，iColIndex）=
drRow（dcCol.ColumnName）.ToString（）
下一个dcCol
下一个drRow
''自动调整单元格
objExcel.Cells（1,1）.CurrentRegion.EntireColumn。 AutoFit（）
有没有人知道如何改进这个？而且我不想要
来使用XML方法。
非常感谢!!!
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!!!
推荐答案
我相信你需要的是
http://www.dotnetjohn.com/articles/articleid36.aspx
这个!! :)
- Sahil Malik
独立顾问
你可以通过我的博客联系我 - http://dotnetjunkies.com/WebLog/sahilmalik/
" ED" <乐**** @ hotmail.com>在消息中写道
news：cf ************************** @ posting.google.c om ...
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...
大家好，
我正在寻找一种将数据集中的表转储到Excel工作表中的有效方法。我目前正在逐个单元地复制数据，这对于大型数据集而言效率相当低。
我目前的代码如下：
objExcel =新Excel .Application（）
objWorkBooks = objExcel.Workbooks
objWorkBook = objWorkBooks.Add
objWorkSheet = objExcel.ActiveWorkbook.ActiveSheet
objWorkSheet.Name = sTitle
objExcel.Visible =真的
'列标题
每个dcCol在dsExport.Tables（0）.Columns
iColIndex + = 1
objWorkSheet.Cells（1，iColIndex）= dcCol.ColumnName
objWorkSheet.Cells（1，iColIndex）.Font.Bold = True
下一个dcCol
''现在输入实际数据
''* **********************************************这是需要花费很长时间的部分
''************************************* ******
iRowIndex = 1
每个drRow在dsExport.Tables（0）。行
iRowIndex + = 1
iColIndex = 0
每个dcCol在dsExport.Tables中（0）.Columns
iColIndex + = 1
objWorkSheet.Cells（iRowIndex，iColIndex）=
drRow（dcCol.ColumnName）.ToString（）
下一个dcCol
下一个drRow
''自动调整细胞
objExcel.Cells（1,1）.CurrentRegion.EntireColumn.AutoFit（）
有谁在那里知道我怎么样可以改善吗？我不希望
使用XML方法。
非常感谢!!!
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!!!
Ok另一个想法是，Excel 2003，可以直接读取或保存到XML ..你
可以使用该设施。
- Sahil Malik
独立顾问
你可以通过我的博客联系我 - http://dotnetjunkies.com/WebLog/sahilmalik/
" Ed" <乐**** @ hotmail.com>在消息中写道
news：cf ************************** @ posting.google.c om ...
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...
大家好，
我正在寻找一种将数据集中的表转储到Excel工作表中的有效方法。我目前正在逐个单元地复制数据，这对于大型数据集而言效率相当低。
我目前的代码如下：
objExcel =新Excel .Application（）
objWorkBooks = objExcel.Workbooks
objWorkBook = objWorkBooks.Add
objWorkSheet = objExcel.ActiveWorkbook.ActiveSheet
objWorkSheet.Name = sTitle
objExcel.Visible =真的
'列标题
每个dcCol在dsExport.Tables（0）.Columns
iColIndex + = 1
objWorkSheet.Cells（1，iColIndex）= dcCol.ColumnName
objWorkSheet.Cells（1，iColIndex）.Font.Bold = True
下一个dcCol
''现在输入实际数据
''* **********************************************这是需要花费很长时间的部分
''************************************* ******
iRowIndex = 1
每个drRow在dsExport.Tables（0）。行
iRowIndex + = 1
iColIndex = 0
每个dcCol在dsExport.Tables中（0）.Columns
iColIndex + = 1
objWorkSheet.Cells（iRowIndex，iColIndex）=
drRow（dcCol.ColumnName）.ToString（）
下一个dcCol
下一个drRow
''自动调整细胞
objExcel.Cells（1,1）.CurrentRegion.EntireColumn.AutoFit（）
有谁在那里知道我怎么样可以改善吗？我不希望
使用XML方法。
非常感谢!!!
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!!!
如果如果源数据来自于说Sql Server
或Access，那么你使用Excel2000或更高版本就可以在Excel中使用com ADO
。
Dim RS作为新的ADODB.Recordset，conn As New ADODB.Connection
设置conn = ...
RS.Open ...
Sheet1.Range（" A1"）。CopyFromRecordset RS
如果使用Excel97，您仍然可以使用com ADO，除了97
不不支持ADO，因此无法使用
Sheet1.Range（" A1"）。CopyFromRecordset RS
必须使用Range对象将
记录集变量中的数据写入工作表。
如果源数据不是来自Sql Server（或者某些东西） />
你可以连接到）然后你可以在你的
vb.net应用程序中使用Com ADO并将数据写入Excel：
RS.Cursor Location = adUseClient
conn.Mode = adModeReadWrite
conn.Open" Provider = Microsoft.Jet.OLEDB.4.0;" &安培; _
"数据源= C：\ test1.xls" &安培; _
"扩展属性="" Excel 9.0; HDR = NO;"""
对于j = 0到dt.Rows.Count - 1
dr = dt.Rows（j）
strSql =" SELECT * FROM [Sheet1
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
这篇关于帮助 - 如何将数据集转储到Excel ???的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
