如何转储数据库表到Excel表? [英] How dump database table to excel sheet?
本文介绍了如何转储数据库表到Excel表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我从我的数据库中获取的数据,我想有一个数据为Excel文件中的表格。 所以,我写了下面的:
昏暗sheetToPopulate作为Excel.Worksheet = getSheet()
昏暗的读者作为OleDbDataReader
昏暗的查询作为字符串=SELECT * FROM的dataTable
DIM在cmd作为新的OleDbCommand(查询,oleConn)
昏暗的读者作为OleDbDataReader
oleConn.Open()
读卡器= cmd.ExecuteReader()
做,而reader.Read()
//如何使用阅读器来填充表一次。
//我有表对象sheetToPopulate。
// cell.Vaule = reader.GetString(0)'这将是非常在效率和复杂的。
//我怎么能转储表到我的Excel工作表?
循环
reader.Close()
oleConn.Close()
应该有这样做的直接明显的方式?
转储数据库表中的Excel工作表?
[应我? ] 的
我应该使用的数据集的东西..?如果有,如何处理是什么?
请帮助..我是新来这个!
解决方案
下面是我如何解决这样的:
专用功能的getData(BYVAL查询作为字符串,BYVAL构造作为字符串)作为对象
昏暗的适配器作为新Data.OleDb.OleDbDataAdapter(查询,构造)
昏暗数据集作为新Data.DataSet
adapter.Fill(数据集)
昏暗的dataTable作为Data.DataTable = dataSet.Tables(0)
昏暗的数据(dataTable.Rows.Count,dataTable.Columns.Count - 1)作为对象
对于COL = 0〜dataTable.Columns.Count - 1
对于行= 0〜dataTable.Rows.Count - 1
数据(行,列)= dataTable.Rows(行).ItemArray(COL)
下一个
下一个
返回的数据
端功能
然后最后,请执行以下操作,你想拥有这个数据的范围
range.Value = GETDATE(查询,构造)
这是解决整个问题!
I am getting data from my database and I want to have that data as a table in excel file. So, I have written the following :
Dim sheetToPopulate As Excel.Worksheet = getSheet()
Dim reader As OleDbDataReader
Dim query As String = "SELECT * FROM dataTable"
Dim cmd As New OleDbCommand(query, oleConn)
Dim reader As OleDbDataReader
oleConn.Open()
reader = cmd.ExecuteReader()
Do While reader.Read()
// How use the reader to populate the sheet at once.
// I have the sheet object as sheetToPopulate.
// cell.Vaule = reader.GetString(0) ' It would be very in-efficient and complex.
// How can I dump the table to my excel sheet ?
Loop
reader.Close()
oleConn.Close()
There should be a straight obvious way of doing this ?
Dumping a database table to excel sheet ?
[ Should I ? ]
Should I use dataset of something.. ? If yes, how to proceed for that ?
Please help.. I am new to this !!
解决方案
Here is how I solved this :
Private Function getData(ByVal query As String, ByVal conStr As String) As Object
Dim adapter As New Data.OleDb.OleDbDataAdapter(query, conStr)
Dim dataSet As New Data.DataSet
adapter.Fill(dataSet)
Dim dataTable As Data.DataTable = dataSet.Tables(0)
Dim data(dataTable.Rows.Count, dataTable.Columns.Count - 1) As Object
For col = 0 To dataTable.Columns.Count - 1
For row = 0 To dataTable.Rows.Count - 1
data(row, col) = dataTable.Rows(row).ItemArray(col)
Next
Next
Return data
End Function
Then Finally, do the following to the range where you want to have this data
range.Value = getDate(query,conStr)
That solved the whole problem !
这篇关于如何转储数据库表到Excel表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文