fleeting fancies

I was trying to write a simple application the other day that exported data from sql server to excel for the user to edit it and then import the updated excel back into sql server. There’s lot of help online about how to export gridview to excel. Here’s what I came up after looking at various examples:

Protected Sub exportExcel()
            'Clear the response, and set the content type and mark as attachment
            HttpContext.Current.Response.Clear()
            HttpContext.Current.Response.Buffer = True
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=""SupportItems_" & Date.Now.Month() & "_" & Date.Now.Day & "_" & Date.Now.Year & ".xls""")

            'Clear the character set
            HttpContext.Current.Response.Charset = ""

            'Create a string and Html writer needed for output
            Dim oStringWriter As New System.IO.StringWriter()
            Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)

            'Clear the controls from the pased grid
            ClearControls(grdViewAll)

            'Show grid lines
            grdViewAll.GridLines = GridLines.Both
            'Color header
            grdViewAll.HeaderStyle.BackColor = System.Drawing.Color.LightGray

            'Render the grid to the writer
            grdViewAll.RenderControl(oHtmlTextWriter)

            'Write out the response (file), then end the response
            HttpContext.Current.Response.Write(oStringWriter.ToString())
            HttpContext.Current.Response.[End]()
        End Sub

        Private Shared Sub ClearControls(ByVal control As Control)
            'Recursively loop through the controls, calling this method 
            For i As Integer = control.Controls.Count - 1 To 0 Step -1
                ClearControls(control.Controls(i))
            Next

            'If we have a control that is anything other than a table cell 
            If Not (TypeOf control Is TableCell) Then
                If control.[GetType]().GetProperty("SelectedItem") IsNot Nothing Then
                    Dim literal As New LiteralControl()
                    control.Parent.Controls.Add(literal)
                    Try
                        literal.Text = DirectCast(control.[GetType]().GetProperty("SelectedItem").GetValue(control, Nothing), String)
                    Catch
                    End Try
                    control.Parent.Controls.Remove(control)
                ElseIf control.[GetType]().GetProperty("Text") IsNot Nothing Then
                    Dim literal As New LiteralControl()
                    control.Parent.Controls.Add(literal)
                    literal.Text = DirectCast(control.[GetType]().GetProperty("Text").GetValue(control, Nothing), String)
                    control.Parent.Controls.Remove(control)
                End If
            End If
            Exit Sub
        End Sub

    ''' <summary>
    ''' Reads excel file into a dataset reference passed as the first argument
    ''' </summary>
    ''' <param name="ds"></param>
    ''' <param name="filename"></param>
    ''' <param name="query"></param>
    ''' <param name="headers"></param>
    ''' <remarks></remarks>
    Private Sub ReadExcel(ByRef ds As DataSet, ByVal filename As String, ByVal query As String, Optional ByVal headers As Boolean = True, Optional ByVal tablename As String = "table1")
        Dim strConnectionString As String
        Dim conExcel As OleDbConnection
        Dim dAdaptExcel As OleDbDataAdapter
        Dim cmdSelect As OleDbCommand
        'Define Excel connection
        If headers Then
            strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename & _
                ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
        Else
            strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename & _
                ";Extended Properties=""Excel 8.0;IMEX=1"""
        End If
        conExcel = New OleDbConnection(strConnectionString)

        Try
            'Open Connection to Excel
            conExcel.Open()
            cmdSelect = New OleDbCommand(query, conExcel)
            dAdaptExcel = New OleDbDataAdapter
            dAdaptExcel.SelectCommand = cmdSelect
            'Fill the dataset
            dAdaptExcel.Fill(ds, tablename)
        Finally
            conExcel.Close()
        End Try
    End Sub

This works okay and in IE, you get the usual open or save dialog. If you do a save, the file gets saved as some kind of html file which unfortunately, you can't import back. I'm using the Select * from [Sheet1$] method to import the data into a DataTable.

So instead, when you get the file open or save dialog, you have to open the file in excel, and then do a file -> save as and save as an xls file. That’s the only the file can be imported back into a DataTable. 2 hourse of googling and I haven’t found any other solution sadly. Hope the users can remember to do this :).

Credit goes to all the people from whom I copied most of the code for exporting the gridview.