Exporting data to excel sheet!

[Piya27]

Hi all,
I just need an idea (not code) how to export data from my databse to excel sheet..that too row wise..
I only need a starting idea... m using Visual studio 2008.

[Piya27]

no idea!!! :(

[Teme64]

One way to export data to an (existing) Excel file is to use OleDb (namespace System.Data.OleDb). Check out Connection strings for Excel how to form a proper connection string to access an Excel file.

If you want to write a general Excel export, I suggest importing data from the data source to a DataView object and passing that to a routine which export DataView to XLS-file. A DataView object contains both data and field names.

And one thing to remember with Excel files is that the table names are Excel sheet names, for example "[sheet1$]". That caused me a bit headache before I realized it :)

HTH

[sknake]

What database are you using? You can also use Excel automation to extract the data but I would use the OleDb approach outlined above.

[Piya27]

Dim con As New SqlConnection(connection_string)
            Dim sqlquery As String = ""
            con.Open()


                   Dim dataExport As String = ""

            'File.Create(fpath)

            Dim cmd As New SqlCommand("Select * From company_profile Where serial_no = '" & TextBox1.Text & "'", con)
            Dim str As New StringBuilder
            Dim da As New SqlDataAdapter(cmd)
            Dim ds As New DataSet
            Dim dt As New DataTable
            'Dim firstRecord As Boolean = True
            Dim cb As New SqlCommandBuilder(da)

            da.Fill(ds)
            ds.Tables(0).Rows(0).Item(0) = TextBox1.Text
            ds.Tables(0).Rows(0).Item(1) = TextBox2.Text
            ds.Tables(0).Rows(0).Item(2) = TextBox6.Text
            ds.Tables(0).Rows(0).Item(3) = TextBox7.Text
            ds.Tables(0).Rows(0).Item(4) = DateTime.Now.ToString
            ds.Tables(0).Rows(0).Item(5) = DateTime.Now.ToString

            '''''''''''Exporting data from GridView to CSV File'''''''''''''''''
            GridView1.DataSource = ds.Tables(0)
            GridView1.DataBind()

            GridView1.AutoGenerateColumns = True

            GridView1.Enabled = True
            GridView1.Visible = True

            'Extracting Column heading for Header Field
            For Each dc As DataGridColumn In GridView1.Columns
                dataExport = dataExport & dc.HeaderText
            Next

            dataExport = Environment.NewLine.ToString()

            For Each drow As GridViewRow In GridView1.Rows

                For Each dcell As GridViewSelectEventArgs In GridView1.Rows
                    If dcell Is Nothing Then
                        dataExport = dataExport & drow.DataItem.ToString & ","
                    End If
                Next
            Next

            dataExport = dataExport & Environment.NewLine.ToString()

            Dim tw As New System.IO.StreamWriter("E:\SBS_sample.csv")
            tw.Write(dataExport)
            tw.Close()

this is what i have done,but this is in csv format. i am reading my data from the GridView just for checking of it works, but cudnt get it working.. if any changes??

[Piya27]

Protected Sub Button15_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button15.Click

        Dim con As New SqlConnection(connection_string)
       'Dim cmd As New SqlCommand("Select * From company_profile where serial_no = '" & TextBox1.Text & "'", con)


        Dim ad As New SqlDataAdapter("SELECT * FROM company_profile where serial_no = '1'", con)
        Dim ds As New DataSet()

        ad.Fill(ds)

        GridView1.DataSource = ds
        GridView1.DataBind()
    
        Dim style As String = "<style> .text { mso-number-format:\@; } </script> "

        Response.ClearContent()
        Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls")
        Response.ContentType = "application/excel"

        Dim sw As New StringWriter()
        Dim htw As New HtmlTextWriter(sw)

        GridView1.RenderControl(htw)
        ' Style is added dynamically 
        Response.Write(style)
        Response.Write(sw.ToString())
        Response.[End]()
    End Sub

    Private Sub DisableControls(ByVal gv As Control)
        Dim lb As New LinkButton()
        Dim l As New Literal()

        Dim name As String = [String].Empty

        For i As Integer = 0 To gv.Controls.Count - 1
            If gv.Controls(i).[GetType]() Is GetType(LinkButton) Then
                l.Text = TryCast(gv.Controls(i), LinkButton).Text
                gv.Controls.Remove(gv.Controls(i))
                gv.Controls.AddAt(i, l)

            ElseIf gv.Controls(i).[GetType]() Is GetType(DropDownList) Then
                l.Text = TryCast(gv.Controls(i), DropDownList).SelectedItem.Text
                gv.Controls.Remove(gv.Controls(i))
                gv.Controls.AddAt(i, l)
            End If


            If gv.Controls(i).HasControls() Then
                DisableControls(gv.Controls(i))
            End If

        Next
    End Sub

    Protected Sub Button16_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button16.Click
        DisableControls(GridView1)

        Response.ClearContent()
        Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls")
        Response.ContentType = "application/excel"

        Dim sw As New StringWriter()
        Dim htw As New HtmlTextWriter(sw)

        GridView1.RenderControl(htw)
        Response.Write(sw.ToString())
        Response.[End]()
    End Sub

    Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub

    Protected Sub gvUsers_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)

        If e.Row.RowType = DataControlRowType.DataRow Then
            e.Row.Cells(1).Attributes.Add("class", "text")
        End If

End Sub

This is what i am able to achieve in it. but now the error comes that could not connect to database.???

[Piya27]

any help??

[Karthick N]

Hi all,
I just need an idea (not code) how to export data from my databse to excel sheet..that too row wise..
I only need a starting idea... m using Visual studio 2008.

Hi,
Here is my Idea,

Open the excel using the connection,
Query the EXCEL for the data u need,
store it in a dataset,

loop through the dataset for the rows of its table,
get the item details
and use it as the parameter for the database query or stored procedure

Regards
Karthick N

[Piya27]

I was able to export the data from database to excel using gridview but now the problem is how to append new values in the same existing excel sheet that i created earlier while exporting the data from gridview. I keep losing formats in the xcel sheet.

any help?