编写Excel工作表，数据源=数据集 [英] Write an Excel Sheet, Datasource = Dataset

问题描述

大家好！

我有以下问题:
我必须从数据集中填写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-Command正在工作.
但是当我查看Excel工作表时，它并没有填写.

[Moved from Answers]

Hi.
The meaning is that my Sql-Command is working.
But when I look at my Excel sheet, it isn''t filled up.

thatraja写道:

thatraja wrote:

是什么意思?我认为，如果针对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

The.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工作表，数据源=数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！