ExcelExportExtender: from IQueryable to Excel with Asp.Net

I needed to export some query results to an Excel workbook from an Asp.Net 4.0 webpage.
The simplest way I found is to exploit Excel for all the stinky work.
Excel is able to understand an html form and to render it to a worksheet, so this is the starting point.
Thus, I also needed to have this export available on several pages and with different ObjectResult.
Then I decided to write an Extender for an IQueryable object that can get the work done.

This is how the class look like, with some comments

// A static class
public static class ExcelExportExtender
{
  static String C_CONTENT = "attachment;filename={0}.xls";

  // static method that extend an IQueryable
  public static void ExportToExcel(this IQueryable Data, 
    Page ParentPage, String FileName)
  {
    if (String.IsNullOrEmpty(FileName))
      throw new ArgumentNullException("FileName is null");

    HttpResponse ResponseObject = ParentPage.Response;
    GridView gdvList = new GridView();
    // Let's hide all unwanted stuffing    
    gdvList.AllowPaging = false;
    gdvList.AllowSorting = false;
    gdvList.EditIndex = -1;
    // Let's bind data to GridView    
    gdvList.DataSource = Data;
    // We exploit the data binding for columns name!
    gdvList.DataBind();
    // Let's output HTML of GridView    
    ResponseObject.Clear();
    ResponseObject.ContentType = "application/vnd.xls";
    String head = String.Format(C_CONTENT, FileName.Trim());
    ResponseObject.AddHeader("content-disposition", head);
    StringWriter swriter = new StringWriter();
    HtmlTextWriter hwriter = new HtmlTextWriter(swriter);
    HtmlForm frm = new HtmlForm();
    ParentPage.Controls.Add(frm);
    frm.Attributes["runat"] = "server";
    frm.Controls.Add(gdvList);
    frm.RenderControl(hwriter);
    ResponseObject.Write(swriter.ToString());
    ResponseObject.End();
    }
}

and this is an example of how to utilize the ExcelExportExtender

	
protected void LinkButton1_Click(object sender, EventArgs e)
{
  FakeDataAccess fda = new FakeDataAccess();
  var DataToExport = from r in fda.RandomData
                     where (r.MyFirstProperty % 2 ==0)
                     select r;
  // only for learning purposes =)
  IQueryable result = DataToExport.AsQueryable();
  // "this" refers to the Page where this method lives
  // Let's rock!
  result.ExportToExcel(this, "EvenRecordsFileName");
}

Job Done!
Download the source code and the example project here!

How to get a simple but good form validation

I used to get the form validation by hand, with some ugly checks, message boxes, several boolean variables to combine with a lot of operators && and ||. A TOTAL mess.

My target was to provide a simple validation tool that I can spread (by inheritance) to all the forms in a project.
So if your project needs only a simple check for mandatory fields, the solution I found maybe can interest you. If you need something more sofisticated, you can imagine an extension of this solution to suite your needs.

How to get it

In a base form class, the parent of all my forms in the project, get an instance of an ErrorProvider, by putting it into the form. Be sure to have “Modifier” property set to “Protected”.
By default, the form property AutoValidate is set to EnablePreventFocusChange. This way, all the controls fire up Validating Event when it’s time to, and the focus will remain on the control when the validation fails.
In this base form we have to add two methods: ValidateControl and SetValidationHandlerOnControl.
The first one is the method that will handle the validation on the control we need to check.
The second one will add the validation handler to the control.

namespace ValidationTest{
  public partial class MyBaseForm 
  {
    ... 
    protected void ValidateControlHandler(object sender, CancelEventArgs e)
    {
      // this string will be the tooltip shown when an error appears
      String error = null;
      String textToCheck = String.Empty;
  
      // we need to be sure that the sender control is 
      // of the type System.TextBox
      Type senderType = sender.GetType();
      if (senderType == typeof(TextBox))
        textToCheck = ((TextBox)sender).Text;
      else 
        throw new ArgumentException(
  	    "The sender is not a TextBox, but a " + senderType.Name);
  
      // Mandatory field: if the textToCheck is null or empty, fire up the error 
      if (String.IsNullOrEmpty(textToCheck))
      {
        error = "Mandatory field"; 
        // this CancelEventArgs property locks the pointer over the control 
        // until the error disappears
        e.Cancel = true;
      }
  
      // if there isn't any error, the SetError get a 
      // null string and it will not shown.
      // otherwise the SetError will show an icon and a tooltip 
      // next to the control that fails the validation
      errorProvider.SetError((Control)sender, error);
    }
  
    protected void SetValidationHandlerOnControl(Control ctrl) 
    { 
      // we need to be sure that the sender control 
      // is of the type System.TextBox
      Type ctrlType = ctrl.GetType();
      if (ctrlType == typeof(TextBox))
        ctrl.Validating += new CancelEventHandler(this.ValidateControlHandler);
    }
  }
}

Ok, that’s almost done.
Now, if we had a form that inherits from MyBaseForm, we can add the controls we need to validate with a simple call to SetValidationHandlerOnControl.
Let’s consider that we have three TextBoxes: by default they all have property CausesValidation set to true. And let’s have a button that is the default AcceptButton for the form, with the TabIndex property value set to be greater than the TabIndex value of the textboxes.
So we need to call

namespace ValidationTest{
  public partial class MyChildForm : MyBaseForm
  {
    public MyChildForm(){
      InitializeComponent();
      SetValidationHandlerOnControl(this.textBox1);
      SetValidationHandlerOnControl(this.textBox2);
      SetValidationHandlerOnControl(this.textBox3);
    ...
    }
  }
}

that’s it!
Now if we don’t provide any string in one of the textBox, the ErrorProvider will show a little (!) icon near to the textBox, the focus will remain on until the string is null or empty. (See image below)
validation
Get the solution with this example here (18Kb). Hope you’ll find it interesting…
Comment out the article if you like it!

EDIT: corrected a typo. Sorry!