Открыть DataTable в Excel VB

Question

Хорошо, это интересный вопрос. Мне было поручено изменить существующий проект VB. В настоящее время пользователь выбрал из серии выпадающих меню, чтобы выбрать SQL-запрос, а затем запустить этот запрос. Таким образом, пользователь выбирает и раскрывающийся список среды, результаты этого раскрывающегося списка заполняют раскрывающийся список категорий. Как только категория выбрана, они получают выпадающий список доступных запросов. Как только они выбирают запрос и нажимают кнопку "Выполнить", они получают сетку с результатами запроса. Некоторые из результатов запроса огромны. Запрос, который я запускаю в качестве теста, содержит 40 столбцов и 20000 записей. Запрос выполняется менее чем за 5 секунд, но для рендеринга сетки требуется более минуты. После того как просмотр сетки завершен, пользователь может экспортировать результаты в Excel. Под этим я подразумеваю, что код открывает экземпляр Excel через gridview.RenderControl и отображает результаты в Excel. Пользователь не хочет сохранять файл Excel и затем переходить к файлу, он хочет, чтобы он открывался прямо из веб-формы, которую он использует, что и делает код в настоящее время.

Однако пользователю нет дела до вида сетки. Им все равно, увидят ли они это вообще. Они хотят просто открыть Excel. Поэтому вместо использования gridview.RenderControl я хочу открыть Excel и заполнить его DataTable (или DataSet) в памяти. Есть мысли о том, как сделать это лучше?

Вот как они в настоящее время заполняют сетку: Dim MyConnection As SqlConnection Dim MyCommand As SqlCommand Dim MyDataTable As DataTable Dim MyReader As SqlDataReader

        MyConnection = New SqlConnection()
        MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings(Connection).ConnectionString

        MyCommand = New SqlCommand()
        MyCommand.CommandText = Sqlquery
        MyCommand.CommandType = CommandType.Text
        MyCommand.Connection = MyConnection

        MyCommand.Connection.Open()
        MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection)

        MyDataTable = New DataTable()
        MyDataTable.Load(MyReader)

        If (MyDataTable.Rows.Count > 0) Then
            QueryresultPanel.Visible = True
            gvLineItems.DataSource = MyDataTable
            gvLineItems.DataBind()
        End If

        MyDataTable.Dispose()
        MyCommand.Dispose()
        MyConnection.Dispose()

Вот как они открывают и заполняют экземпляр Excel:

     Protected Sub btnExportToExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click
        Response.Clear()
        Response.Buffer = True
        '
        ' Set the content type to Excel.
        ' 
        Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls")
        Response.Charset = ""
        Response.ContentType = "application/vnd.ms-excel"
        ' 
        ' Turn off the view state.
        ' 
        Me.EnableViewState = False
        Dim oStringWriter As New System.IO.StringWriter()
        Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)
        ' 
        ' Get the HTML for the control.
        '
        gvLineItems.RenderControl(oHtmlTextWriter)
        ' 
        ' Write the HTML back to the browser.
        '
        Response.Write(oStringWriter.ToString())
        Response.[End]()
    End Sub

Очевидно, что нет никакого RenderControl для DataTable или DataSet, и он не может понять, как заставить этот набор записей отображаться в экземпляре Excel без предварительного сохранения его в файл.

Answer 1

Хорошо, вот решение, которое я нашел (на случай, если кому-то интересно). Это довольно просто на самом деле. Я просто перебрал данные и использовал StringWriter.

    Protected Sub WriteToExcelFile(dt As DataTable)
    Dim sw As StringWriter

    For Each datacol As DataColumn In dt.Columns
        sw.Write(datacol.ColumnName + vbTab)
    Next

    Dim row As DataRow
    For Each row In dt.Rows
        sw.Write(vbNewLine)
        Dim column As DataColumn
        For Each column In dt.Columns
            If Not row(column.ColumnName) Is Nothing Then
                sw.Write(row(column).ToString() + vbTab)
            Else
                sw.Write(String.Empty + vbTab)
            End If
        Next column
    Next row

    Response.Clear()
    Response.ContentType = "application/vnd.ms-excel"
    Response.AddHeader("Content-Disposition", "attachment;filename=DataTable.xls")
    Response.Output.Write(sw.ToString())
    Response.Flush()
    System.Web.HttpContext.Current.Response.Flush()
    System.Web.HttpContext.Current.Response.SuppressContent = True
    System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest()
End Sub