export to excel from vb.net from sql datareader

Question

I have an sql query with sql datareader. i put a for loop for the data reader. now when the data starts coming in from the query i want it to export to excel in the for loop. here's my code

Try Dim SqlStr As String = "", dr As SqlDataReader = Nothing

        ConnectDB(Cnn)
        Str = "query"
        SqlCmd = New SqlCommand(Str, Cnn)
        dr = SqlCmd.ExecuteReader

while dr.read ..EXPORT TO EXCEL do end while does anyone know how to do this?

Answer 1

This sample will render the entire Grid content to an Excel file.

private void _Click(object sender, System.EventArgs e)
{
//export to excel
Response.Clear();
    Response.Buffer= true;
    Response.ContentType = "application/vnd.ms-excel";
    Response.Charset = "";
    this.EnableViewState = false;
    System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter oHtmlTextWriter = 
        new System.Web.UI.HtmlTextWriter(oStringWriter);
    this.ClearControls(dg);
    dg.RenderControl(oHtmlTextWriter);
    Response.Write(oStringWriter.ToString()); 
    Response.End();
}

Answer 2

I read about saving dataset to excel on CodeProject. You can refer to that article: http://www.codeproject.com/KB/dotnet/ExportToExcel.aspx

or http://www.codeproject.com/KB/aspnet/Export_large_data_to_xl.aspx

Answer 3

There are a number of approaches you can use

1. Decide if you are going to create a file on the server that can be referenced in a hyperlink

2. Stream the response to the client in real time, setting the mime type so that the response is loaded in excel.

For either approach, you need to create an output that Excel will open. There are some options here:

1. You can use spreadsheet xml. This is an xml format older versions of Excel can open.
2. You can write html into a file and rename it with a .xls extension/ write html to the response stream and set the mime type to excel.
3. Use OOXML to create an xlsx file. This file can be opened by all versions of Excel from Office 2000 onwards (client may need to download a free update to enable this). OOXML is quite complex so I suggest using a free library such as Simple OOXML. You can also download the OOXML SDK from that link.

See my blog post here for information on writing an xlsl document to the response stream.