本文介绍了编写Excel工作表,数据源=数据集的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

大家好!

我有以下问题:
我必须从数据集中填写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!

推荐答案

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

08-13 20:33