Victor’s Programming Aid

In C#, a common function in web pages containing data in a table is allowing the web user to export to excel. Through my experiences, I have uncovered that to export from a GridView (or DataGrid) to a Microsoft Excel (xls) file is messy and not easily customizable. Instead, I found that taking the underlying DataTable and instead exporting to a Microsoft Excel Comma Separated Value (CSV) file as a much better method. Below is the snippet of code for the function.

private void exportDataTableToCsv(DataTable formattedDataTable, string filename)
{
   DataTable toExcel = formattedDataTable.Copy();
   HttpContext context = HttpContext.Current;
   context.Response.Clear();

   foreach (DataColumn column in toExcel.Columns)
   {
      context.Response.Write(column.ColumnName + ",");
   }

   context.Response.Write(Environment.NewLine);
   foreach (DataRow row in toExcel.Rows)
   {
      for (int i = 0; i < toExcel.Columns.Count; i++)
      {
         context.Response.Write(row[i].ToString().Replace(",", string.Empty) + ",");
      }

      context.Response.Write(Environment.NewLine);
   }

   context.Response.ContentType = "text/csv";
   context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + ".csv");
   context.Response.End();
}

To call this function, simple create a C# LinkButton or Button Web Control. In your aspx page, your control should look similar to:

<asp:LinkButton ID="lbToExcel" runat="server">Export To Excel</asp:LinkButton>

From here, the lbToExcel_Click function will call the Export to DataTable function exportDataTableToCsv defined earlier in this post. I would also advise making edits at this point to your DataTable by removing columns that were hidden in the GridView (or DataGrid). We want to pass a formattedDataTable.

If your page uses Ajax’s Update Panel controls, it is required to set the lbToExcel web control as a PostBackTrigger as opposed to an AsyncPostBackTrigger. To add a PostBackTrigger in Visual Studio 2008’s Ajax Update Panel, find the properties of the Update Panel. Under Behavior, find Triggers and choose to edit the Collection. Next, Click Add and from the Dropdown select PostBackTrigger. Next, you need to manually (without the aid of intellisense) type in the control name. In our case, the control name was lbToExcel. The result should be the code snippet example below:

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:LinkButton ID="lbToExcel" runat="server">Export To Excel</asp:LinkButton>
</ContentTemplate>
<Triggers>
<asp:PostBackTrigger ControlID="lbToExcel">
</asp:PostBackTrigger>
</Triggers>
</asp:UpdatePanel>

Overall, this has worked flawlessly for me. If you have any problems, feel free to drop a line describing your challenges and I can attempt to give you a hand!

When possible, a programmer should choose to use a switch statement over an if statement. Note that it is not always possible to replace an if statement with a switch statement. The right time to use a switch statement is when the if statement is constantly comparing the same variable. In our example, we are constantly comparing the text in the Label control lblDayOfWeek. To make it easy, I have made blue the string text that will be used in our comparison.

switch (lblDayOfWeek.Text)
{
   case "1":
      lblDayOfWeek.Text = "Sunday";
      break;
   case "2":
      lblDayOfWeek.Text = "Monday";
      break;
   case "3":
      lblDayOfWeek.Text = "Tuesday";
      break;
   case "4":
      lblDayOfWeek.Text = "Wednesday";
      break;
   case "5":
      lblDayOfWeek.Text = "Thursday";
      break;
   case "6":
      lblDayOfWeek.Text = "Friday";
      break;
   case "7":
      lblDayOfWeek.Text = "Saturday";
      break;
   default:
      lblDayOfWeek.Text = "";
      break;
}

The final default is equivalent to else, which means if none of the cases match, the default route will be taken. The greatest advantage to using the switch statement is efficiency. When a swtich statement is called, only one comparison is made.

Note that the switch statement is not restricted to string or integer. It is also possible to use enum or virtually any other comparable variable.