Export excel file from ASP.NET

[viruskiller10]

im creating an asp.net page with visual basic laguage where i have a gridview, what i need to do is to export the data in that gridview as a web query that i can open with excel and be able to regresh the data form excel.

[kadghar]

Since you cannot guarantee you'll have Excel installed on your web server, the easiest way is to create an HTML table in plain text, then throw it as an .XLS file.

Excel can open HTML tables quite nice; with the little inconvenient it'll show a dialog before opening. (no big deal)

[viruskiller10]

But how do i do that?

[Frinavale]

Well, you need to create an HTML table within something like a String or a StringBuilder.

Once you have generated the HTML table, you will have to write the HTML table to the Response stream. But first, you need to change the Header to indicate that the Content-Type is "Application/x-msexel" and that the Content-Disposition is an attachment with the file name that you want to save the file as.

For example, say you had an "Export.aspx" page whose purpose is to export the data as described.

In the Page Load event you would have something like:
(VB.NET)

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  Dim fileName As String = "exportedData.xls"
 
  Response.Clear()
  Response.AddHeader("Content-Type","Application/x-msexcel")
  Response.AddHeader("Content-Disposition","attachment;filename=" & fileName)
 
  Response.Write(GenerateHTMLTable)
End Sub

(C#)

protected void Page_Load(object sender, System.EventArgs e)
{
    string fileName = "exportedData.xls";
 
    Response.Clear();
    Response.AddHeader("Content-Type", "Application/x-msexcel");
    Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
 
    Response.Write(GenerateHTMLTable());
}

You would have to implement the GenerateHTMLTable as a method that returns the HTML in a string.

Something like:

Private Function GenerateHTMLTable() As String
 
'Retrieve the data you want to export from where ever it is stored
 
'Declare a StringBuilder to contain the exported HTML table content
 
'Append a line containing an open <table> tag to the StringBuilder
 
'Append a line containing a row for the column headers
'For each column that should exist in the HTML table, Append a column header to the table with the Column Name within it.
'Append a line closing the row for the column headers
 
'For each row in your table, add an <tr>
'  For each column in the row, add a <td>
'    add the data within the column
'    add a close </td>
'  finish the loop through the columns
' add a close for the table row </tr>
'finish the for each for adding the html row 
 
  Dim theTableData As DataTable = GetDataToExport()
  Dim theTable As New System.Text.StringBuilder
 
  theTable.AppendLine("<table>")
 
  'Inserting table Headers:
  theTable.AppendLine("  <tr>") 
  For Each columnHeader As DataColumn in theTableData.Columns
    Dim columnName As String = columnHeader.ColumnName
    theTable.AppendLine("    <th>")
    theTable.AppendLine("       " & columnName)
    theTable.AppendLine("    </th>")
  Next
  theTable.AppendLine("  </tr>")
 
  'Inserting table Data:
  For Each row As DataRow In theTableData.Rows
    theTable.AppendLine("  <tr>")
 
    Dim column As DataColumn
    For Each column In theTableData.Columns
      theTable.AppendLine("    <td>" & row(column) & "</td>")
    Next
 
    theTable.AppendLine("  </tr>")
  Next  
 
  theTable.AppendLine("</table>")
 
  Return theTable.ToString()
End Function

(C#)

private string GenerateHTMLTable()
{
 
    //Retrieve the data you want to export from where ever it is stored
 
    //Declare a StringBuilder to contain the exported HTML table content
 
    //Append a line containing an open <table> tag to the StringBuilder
 
    //Append a line containing a row for the column headers
    //For each column that should exist in the HTML table, Append a column header to the table with the Column Name within it.
    //Append a line closing the row for the column headers
 
    //For each row in your table, add an <tr>
    //  For each column in the row, add a <td>
    //    add the data within the column
    //    add a close </td>
    //  finish the loop through the columns
    // add a close for the table row </tr>
    //finish the for each for adding the html row 
 
    DataTable theTableData = GetDataToExport();
    System.Text.StringBuilder theTable = new System.Text.StringBuilder();
 
    theTable.AppendLine("<table>");
 
    //Inserting table Headers:
    theTable.AppendLine("  <tr>");
    foreach (DataColumn columnHeader in theTableData.Columns) {
        string columnName = columnHeader.ColumnName;
        theTable.AppendLine("    <th>");
        theTable.AppendLine("       " + columnName);
        theTable.AppendLine("    </th>");
    }
    theTable.AppendLine("  </tr>");
 
    //Inserting table Data:
    foreach (DataRow row in theTableData.Rows) {
        theTable.AppendLine("  <tr>");
 
        DataColumn column = null;
        foreach (DataColumn column_loopVariable in theTableData.Columns) {
            column = column_loopVariable;
            theTable.AppendLine("    <td>" + row[column] + "</td>");
        }
 
        theTable.AppendLine("  </tr>");
    }
 
    theTable.AppendLine("</table>");
 
    return theTable.ToString();
}

Please note that I did not test the above code that generates the HTML Table, so if you copy & paste it into your code, be prepared to debug and test it.

-Frinny

[viruskiller10]

that code export the entire page, but i only want to export a table or gridview

[kadghar]

1) Create a FORM than only contains the gridView, or
2) Create a string by your own. It's quite simple to develop it by yourself.

[viruskiller10]

thanks a lot, i already fix my issue, i did the option with the grdiview into a single form. Thanks