.NET Questions (CLOSED)

Questions and Answers on any aspect of .NET. Now closed.

This discussion group is now closed.

Have a question about .NET development? Try stackoverflow.com, a worldwide community of great developers asking and answering questions 24 hours a day.

The archives of .NET Questions contain years of Q&A. Even older .NET Questions are still online, too.

Quick and easy transfer of SQL Server table to Excel in VB.Net

We have a nice ASP web page with a grid-view into the data.

Now we want the ability to fetch that data in an Excel file.

A config setting does not allow OPENROWSET in a stored procedure, and that setting must be left for "security reasons."

I can make a datasource to Excel and copy fieds in a lopp, but surely there's a simple way in VB.Net to do the equivalent of
INSERT INTO ExcelDS.something
SELECT * FROM SQLS_DS.something

??

thanks
Wes Groleau Send private email
Thursday, January 24, 2008
 
 
If you can't read my typos, that's "copy fields in a loop"
(actually two loops nested)
Wes Groleau Send private email
Thursday, January 24, 2008
 
 
well how much data are you talking about??
jonathan
Thursday, January 24, 2008
 
 
"how much data"

It varies.  one to twelve pages of up to twenty lines each.

Speed is likely not a problem.  But if it can be done in a single call instead of two nested loops, ....
Wes Groleau Send private email
Friday, January 25, 2008
 
 
This kind of thing?

    Sub DatagridToExcel(ByVal dg As DataGrid, ByVal filename As String)
        ' Set the content type to Excel.
        Response.ContentType = "application/vnd.ms-excel"
        ' Remove the charset from the Content-Type header.
        Response.Charset = ""
        Response.AddHeader("Content-Disposition", "inline;filename=" & filename & ".xls")
        ' Turn off the view state.
        Me.EnableViewState = False
        Dim tw As New System.IO.StringWriter()
        Dim hw As New System.Web.UI.HtmlTextWriter(tw)
        ' Get the HTML for the control.
        dg.RenderControl(hw)
        ' Write the HTML back to the browser.
        Response.Write(tw.ToString())
        ' End the response.
        Response.End()
    End Sub
bob the builder
Friday, January 25, 2008
 
 
I've used the same method that Bob the Builder mentions.  Works pretty well.  The browsers handles most of the work for you... the user will get prompted to open the file in Excel.  They can then save it or do whatever they want.
Eric D. Burdo Send private email
Friday, January 25, 2008
 
 
That method requires the datagrid to not have paging, sort, etc. controls.

I was hoping for something (which might even work; I'll try it after the meeting I have to run to) like

SQL_DA.Fill(SQL_DS.Tables("tablename"))

Excel_DS.Tables("tablename") = SQL_DS.Tables("tablename")
Wes Groleau Send private email
Friday, January 25, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz