问题描述
大家好！
我有以下问题:
我必须从数据集中填写Excel工作表.
DataSet由MS SQL Server 2008填充.(那不是问题.)
首先，这是我的源代码:
Hi everyone!
I have the following problem:
I have to fill an Excel Sheet from a DataSet.
The DataSet is filled by a MS SQL Server 2008. (That''s not the problem..)
At first, here is my sourcecode:
Dim sqlConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(readSettings.getSqlCon(False))
Dim sqlcmd As SqlCommand
Dim dsleistungen As DataSet
Try
sqlConnection.Open()
Dim cmd As String = "select * from dbo.Leistungen order by beginn"
sqlcmd = New SqlClient.SqlCommand(cmd, sqlConnection)
Dim da As New SqlDataAdapter(sqlcmd)
dsleistungen = New DataSet
da.Fill(dsleistungen)
Catch ex As Exception
MessageBox.Show(Convert.ToString(ex))
Finally
sqlConnection.Close()
End Try
''Here is the section with the "Excel-Fill-Code"
Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet As Excel._Worksheet
Dim range As Excel.Range
'' Create a new instance of Excel and start a new workbook.
objApp = New Excel.Application()
objBooks = objApp.Workbooks
objBook = objBooks.Add
objSheets = objBook.Worksheets
objSheet = objSheets(1)
''Get the range where the starting cell has the address
''m_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
range = objSheet.Range("A1", Reflection.Missing.Value)
range = range.Resize(dsleistungen.Tables(0).Rows.Count - 1, dsleistungen.Tables(0).Columns.Count - 1)
''Create an array.
Dim saRet(dsleistungen.Tables(0).Rows.Count - 1, dsleistungen.Tables(0).Columns.Count - 1) As String
''Fill the array.
Dim iRow As Integer
Dim iCol As Integer
For iRow = 0 To dsleistungen.Tables(0).Rows.Count - 1
For iCol = 0 To dsleistungen.Tables(0).Columns.Count - 1
saRet(iRow, iCol) = dsleistungen.Tables(0).Rows(iRow).Item(iCol)
Next iCol
Next iRow
''Set the range value to the array.
range.Value = saRet
''Return control of Excel to the user.
objApp.Visible = True
objApp.UserControl = True
''Clean up a little.
range = Nothing
objSheet = Nothing
objSheets = Nothing
objBooks = Nothing
我有Microsoft的代码，这里是链接: KB302094
我编辑了代码. Excel应该用我的数据集(数据库的表)填充.
取而代之的是，我得到了一条带有无用条目的半成品线. "Select * from dbo.Leistungen"仅重排13个具有5行的列.
我的代码有什么问题?^^
>>谢谢！
I have the code from Microsoft, here is a Link: KB302094
I edited the Code. Excel should be filled up with my DataSet (a Table of my Database).
Instead I get one half-finished line with useless entrys. The "Select * from dbo.Leistungen" retourns only 13 colums with 5 rows.
What''s wrong with my code?^^
>> Thanks!
推荐答案
是什么意思?我认为，如果针对sql数据库的select查询返回13列5行，那么问题就出在excel/vb.net中.您的数据库可能仅包含13列，5行.
what''s the meaning? I think if the select query against sql database returns 13 columns with 5 rows then the problem is not in excel/vb.net things. Your database may contains only 13 columns with 5 rows.
我的数据库仅包含13个列和5行(结构..).选择工作正常.当我查看我的数据集时，我看到了所有数据库条目(从选择..."中).
但是，当我用此代码编写Excel工作表时，我只能看到1个半填充行.
当我查看String-Arry saRet时，它包含"我的数据集:
saRet(X，Y)-> X是行，Y是列.从我的角度来看，看起来还不错！...
-------------------------------------------------- --------------------
现在Microsoft无法使用这种形式的表格.
->现在代码也只写一行...
也许存在兼容性问题? ->我正在使用Microsoft Office 2010和Windows 7 x64 ...?
-------------------------------------------------- --------------------
解决方案
嗨
好的，我已经解决了问题:
我定义了错误的范围...
对于每个想做同样示例的人，这是我正确的源代码！
(您需要向您的项目添加一些引用...您可以在此处获取信息: http://support.microsoft. com/kb/302094/en [ ^ ]
My Database only contains 13 colums and 5 rows (the structur..). The select is working fine. When I look into my DataSet i see all database entrys (from my Select...).
But when i write the Excel Sheet with this Code, I can only see 1 half-filled row.
When I look at the String-Arry saRet, it "contains" my DataSet:
saRet(X,Y) -> X is the row and Y is the Column. From my side, it looks pretty good!...
----------------------------------------------------------------------
Now the smaple form Microsoft doesn''t work any more.
-> Now the code also writes only one line...
Maybe there is a compatiblity problem? -> I am using Microsoft Office 2010 and Windows 7 x64...?
----------------------------------------------------------------------
Solution
Hi.
Okay, i have solved the problem:
I defined the wrong range...
For everyone who want to do the same example, here is my correct source-code!
(You need to add some references to your project... you can get the infos here: http://support.microsoft.com/kb/302094/en[^]
Imports System.Data.SQLClient
Imports Microsoft.Office.Interop
Dim sqlConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection("Connection-String")
Dim sqlcmd As SqlCommand
Dim ds As DataSet
Try
sqlConnection.Open()
Dim cmd As String = "" ''Put your Select-cmd here!
sqlcmd = New SqlClient.SqlCommand(cmd, sqlConnection)
Dim da As New SqlDataAdapter(sqlcmd)
ds = New DataSet
da.Fill(ds)
Catch ex As Exception
MessageBox.Show(Convert.ToString(ex))
Finally
sqlConnection.Close()
End Try
Dim objApp As Excel.Application
Dim objBook As Excel._Workbook
Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet As Excel._Worksheet
Dim range As Excel.Range
objApp = New Excel.Application()
objBooks = objApp.Workbooks
objBook = objBooks.Add
objSheets = objBook.Worksheets
objSheet = objSheets(1)
range = objSheet.Range("A1", Reflection.Missing.Value) ''Starting-Cell
Dim RowCount, ColumCount As Integer
RowCount = ds.Tables(0).Rows.Count
ColumCount = ds.Tables(0).Columns.Count
range = range.Resize(RowCount, ColumCount)
RowCount = RowCount - 1
ColumCount = ColumCount - 1
''Create an array.
Dim saRet(RowCount, ColumCount) As String
''''Fill the array.
Dim iRow As Integer
Dim iCol As Integer
For iRow = 0 To RowCount
For iCol = 0 To ColumCount
saRet(iRow, iCol) = ds.Tables(0).Rows(iRow).Item(iCol)
Next iCol
Next iRow
range.Value = saRet
objApp.Visible = True
objApp.UserControl = True
''Clean up a little.
range = Nothing
objSheet = Nothing
objSheets = Nothing
objBooks = Nothing
感谢Thatraja的帮助！
Z
Thanks thatraja for your help!
Z
从dbo.Leistungen中选择*"仅重述了13个列有5行.
The "Select * from dbo.Leistungen" retourns only 13 colums with 5 rows.
什么意思?我认为，如果针对sql数据库的select查询返回13列5行，那么问题就出在excel/vb.net中.您的数据库可能仅包含13列，5行.
所以
检查数据库值(列和行)，
数据集(列和行).
让我们再知道一遍.
what''s the meaning? I think if the select query against sql database returns 13 columns with 5 rows then the problem is not in excel/vb.net things. Your database may contains only 13 columns with 5 rows.
So
check the database values (Columns & Rows),
dataset (Columns & Rows).
Let us know the things again.
这篇关于编写Excel工作表，数据源=数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！