Direct Excel Export

Often in the web world developers are asked to add Excel exporting capabilities to their web application or web services. The following demonstration shows you how you can implement a decent solution that is both quick and easy to implement in you own code. I built a class named DirectExcel that inherits the ASP.NET GridView control. The reason it inherits the GridView is because this exporting technique requires that our data be databound to a grid. First lets take a look at a snippet of code on how we can use this class.

 

Usage Snippet:

SqlCommand oCmd = new SqlCommand(“usp_get_data “);

oCmd.CommandType = CommandType.StoredProcedure;

 // The report name will also be used as the filename when saving.

DirectExcel directExcel = new  DirectExcel(“Report Name”); 

// oCmd is a SqlCommand that contains the Query you want executed against the database

 directExcel .Export(oCmd);

 

You will need to add a connectionString key to your web.config named “DBConnectionString“ and make sure the query/SP in the SqlCommand above is valid. Take a quick look at the Export method in code below. You will notice that we are first pulling the data from the database using a SqlDataAdapter and filling a DataTable with that data. Since our class inherits the GridView Class we bind the data to this.DataSource. Next we set some properties to the current context by clearing the response, change the content type, and setting the MsoFormats. Notice how the MsoFormat are embedded in the Style tag; This is because we are going to render the GridView to HTML and we can use these classes to format the columns by hooking into the RowCreated event (but I’ll get into details on this later). Next, we create a StringWriter and pass it into a HtlmTextWriter object. When we call the this.RenderControl() method, we pass in the HtmlTextWriter object which is used to write the grid HTML generated by the RenderControl() method. At this point, the StringWriter object should hold the HTML generated based on the GridView. Now it’s just a matter of writing the StringWriter to the response and the user will be prompted with a Open or Save dialog.

 

 public class DirectExcel : System.Web.UI.WebControls.GridView

{

//MsoFormats should be all on one line, it’s used to format columns

public string MsoFormats = “<style>.TextFormat { mso-number-format:\\@; } .DateFormat { mso-number-format:’mm\\/dd\\/yy’ } .CurrencyFormat { mso-number-format:\”\\0022$\\0022\\#\\,\\#\\#0\\.00\” } .NumberFormat {mso-number-format:0;} .FixedNumberFormat{mso-number-format:Fixed;} .PercentFormat{mso-number-format:0%;} .PercentWithDecimalsFormat{mso-number-format:Percent;}</style>”;

 

                // default filename used when saving

                public string ReportName;

                // columns to remove from exported view

                public List<string> ColumnsToRemove = new List<string>();

 

                public DirectExcel(string strReportName)

                {

                                ReportName = strReportName;

                                // default empty data text to display when grid has zero records

                                this.EmptyDataText = “No data record(s) found for criteria.”;

                }

 

                public DirectExcel(string strReportName, string strEmptyDataText)

                {

                                ReportName = strReportName;

                                this.EmptyDataText = strEmptyDataText;

                }

 

                public void Export(System.Data.SqlClient.SqlCommand oCmd)

                {

                                SqlConnection objConn = new SqlConnection(ConfigurationManager.AppSettings[“DBConnectionString”].ToString());

                                SqlDataAdapter objAdapter = new System.Data.SqlClient.SqlDataAdapter();

                                DataTable objDt = new DataTable();

 

                                oCmd.Connection = objConn;

                                oCmd.CommandTimeout = 300;

 

                                objAdapter.SelectCommand = oCmd;

                                objAdapter.Fill(objDt);

 

                                //remove any columns specified.

                                foreach (string colName in ColumnsToRemove)

                                {

                                                objDt.Columns.Remove(colName);

                                }

 

                                this.DataSource = objDt;

                                this.DataBind();

 

                                //clear the reponse and change content type

                                HttpContext.Current.Response.Clear();

                                HttpContext.Current.Response.AddHeader(“Content-Disposition”, “attachment; filename=\”” + ReportName + “.xls\””);

                                HttpContext.Current.Response.Charset = “”;

                                HttpContext.Current.Response.ContentType = “application/vnd.ms-excel”;

                                //add the mso classes that can be used to format columns

                                HttpContext.Current.Response.Write(this. MsoFormats);

 

                                System.IO.StringWriter tw = new System.IO.StringWriter();

                                System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);

                                this.RenderControl(hw);

                                HttpContext.Current.Response.Write(tw.ToString());

                                HttpContext.Current.Response.End();

                }

 

                  // Used to Format Direct Excel Export

                protected override void OnRowCreated(GridViewRowEventArgs e)

                {

                                base.OnRowCreated(e);

                                if (e.Row.RowType == DataControlRowType.DataRow)

                                {

                                                e.Row.VerticalAlign = VerticalAlign.Top;

                                }

                }

}

 

Leave a Reply