Write an Excel Sheet, Datasource = Dataset

[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 wrote:

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.

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

Thanks thatraja for your help!
Z