asked on

Exporting data grid to excel, also add the filter criteria on the first line on the excel workbook

I have an asp.net application where I would like to export the contents of the datagrid to excel.
I have seen a few examples where the datagrid is exported, but in my case I want a header row on the first line of the excel workseet with the filters that were used

Example
StartDate 10/01/2011 - EndDate 10/31/2011
Grid data follows

Any code sample would be great.

I saw this example
private void LinkButton1_Click(object sender, System.EventArgs e)
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=MyPicks.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

System.Web.UI.WebControls.DataGrid myGrid = (System.Web.UI.WebControls.DataGrid)PlaceHolder1.FindControl("DataGrid1");
myGrid.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}

gopaltayde

Not the actual solution, but the work around is -
use crystal reports for .net (which is free with .net). Build a report with your stored procedure, the one which you use to bind the gridview control. Now export the ReportDocument object to any format without any pain.

countrymeister

ASKER

I do not want crystal reports, also crystal reports is somthing my company will not purchase.

Nasir Razzaq

What version of .NET are you using? The mention of "DataGrid1" has got me worried. If its a GridView, set its Caption property to the filter text and then it would render on top in Excel (hopefully)

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.caption.aspx

countrymeister

ASKER

Hi ! CodeCruiser

I am fine to use a Gridview instead of using a DataGrid, but what I am facing is that Response.Write does not bode well with AJAXed pages.
Also I want to stay away from Excel Interop dll's any other suggestion would be geat

Nasir Razzaq

You can open a new window which does the response.write so it does not mess up with ajax.

countrymeister

ASKER

CodeCruiser

I did venture into that direction. It gives me an error stating Acces denied.
Please note that I do not have excel installed on the web server, Is this needed to be installed?
Code is attached inline.

I do see the excel file being created, and Everyone is granted Full permissions to the Excel folder.

Protected Sub ExportToExcel()
        Try


            Dim strFilePath As String = String.Empty
            strFilePath = Server.MapPath("~/Excel/") + "ExcelFileName" + ".xls"

            If (File.Exists(strFilePath)) Then
                File.Delete(strFilePath)
            End If

            Dim oStringWriter As System.IO.StringWriter = New StringWriter()
            Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(oStringWriter)
            Dim objStreamWriter As StreamWriter
            Dim strHeader As String = "XYZ Report"
            Dim strHeader1 As String = "StartDate: " & txtStartDate.Text & "  EndDate: " & txtEndDate.Text
            objStreamWriter = File.AppendText(strFilePath)
            dgCriticalResults.RenderControl(oHtmlTextWriter)
            objStreamWriter.WriteLine(strHeader)
            objStreamWriter.WriteLine(strHeader1)
            objStreamWriter.WriteLine(oStringWriter.ToString())
            objStreamWriter.Close()
           
            Dim sb As StringBuilder = New StringBuilder("")
            sb.Append("window.open('" & strFilePath & "','mywindow','width=400,height=400,resizable=1,top=50,left=50,scrollbars=1');")

            ScriptManager.RegisterClientScriptBlock(Me.Page, Me.Page.GetType(), Me.ClientID, sb.ToString(), True)
        Catch ex As Exception

            'Handle Exception
            Dim strError As String = ex.Message()
           

        End Try
    End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

countrymeister

ASKER

Code cruiser

I did try that too.

I am using Master page which has AJAX and then my child page uses the master page.
I pulled out the button from the updatepanel and the same error still exits.