Archive for June, 2010

Improved NPOI ExportToExcel Function

I was looking over my generic export DataTable to Excel function the other day and noticed an issue. My default method was throwing everything into a string format, which was preventing users from highlighting number columns for subtotals. To fix the problem they could use “Text to Columns”, but this was becoming a common complaint I wanted to resolve. I came up with an improved “ExportToExcel” function that will insert the correct data type.

/// <summary>
///   Render DataTable to Excel File
/// </summary>
/// <param name = "sourceTable">Source DataTable</param>
/// <param name = "fileName">Destination File Name</param>
public static void ExportToExcel(DataTable sourceTable, string fileName)
{
	HSSFWorkbook workbook = new HSSFWorkbook();
	Sheet sheet = workbook.CreateSheet("Sheet1");
	Row headerRow = sheet.CreateRow(0);

	// Create Header Style
	CellStyle headerCellStyle = workbook.CreateCellStyle();
	headerCellStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
	headerCellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;

	// Create Date Style
	CellStyle dateCellStyle = workbook.CreateCellStyle();
	dateCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");

	// Build Header
	foreach (DataColumn column in sourceTable.Columns)
	{
		Cell headerCell = headerRow.CreateCell(column.Ordinal);
		headerCell.SetCellValue(column.ColumnName);
		headerCell.CellStyle = headerCellStyle;
	}

	// Build Details (rows)
	int rowIndex = 1;
	int sheetIndex = 1;
	const int maxRows = 65536;

	foreach (DataRow row in sourceTable.Rows)
	{
		// Start new sheet max rows reached
		if (rowIndex % maxRows == 0)
		{
			// Auto size columns on current sheet
			for (int h = 0; h < headerRow.LastCellNum; h++)
			{
				sheet.AutoSizeColumn(h);
			}

			sheetIndex++;
			sheet = workbook.CreateSheet("Sheet" + sheetIndex);
			Row additionalHeaderRow = sheet.CreateRow(0);

			for (int h = 0; h < headerRow.LastCellNum; h++)
			{
				Cell additionalHeaderColumn = additionalHeaderRow.CreateCell(h);
				additionalHeaderColumn.CellStyle = headerRow.GetCell(h).CellStyle;
				additionalHeaderColumn.SetCellValue(headerRow.GetCell(h).RichStringCellValue);
			}

			rowIndex = 1;
		}

		// Create new row in sheet
		Row dataRow = sheet.CreateRow(rowIndex);

		foreach (DataColumn column in sourceTable.Columns)
		{
			Cell dataCell = dataRow.CreateCell(column.Ordinal);

			switch (column.DataType.FullName)
			{
				case "System.String":
					dataCell.SetCellValue(row[column].ToString());
					break;
				case "System.Int":
				case "System.Int32":
				case "System.Int64":
				case "System.Double":
				case "System.Decimal":
					double val;
					dataCell.SetCellValue(Double.TryParse(row[column].ToString(), out val) ? val : 0);
					break;
				case "System.DateTime":
					DateTime dt = new DateTime(1900, 01, 01);
					DateTime.TryParse(row[column].ToString(), out dt);

					dataCell.SetCellValue(dt);
					dataCell.CellStyle = dateCellStyle;
					break;
				default:
					dataCell.SetCellValue(row[column].ToString());
					break;
			}
		}

		rowIndex++;
	}

	for (int h = 0; h < headerRow.LastCellNum; h++)
	{
		sheet.AutoSizeColumn(h);
	}

	ExportToExcel(workbook, fileName);
}

The key part of the function above to review is the “switch (column.DataType.FullName)” code block. This grabs the DataTable’s column data type to use in the SetCellValue() call.

Once the workbook is built, we call a overload of the same function that expects a NPOI workbook. This overload will send a XLS file back to the user via their web browser using the HttpResponse stream.

/// <summary>
/// Render Excel File to HttpResponse (Browser)
/// </summary>
/// <param name="workbook">NPOI Workbook</param>
/// <param name="fileName">Destination File Name</param>
public static void ExportToExcel(HSSFWorkbook workbook, string fileName)
{
	using (MemoryStream memoryStream = new MemoryStream())
	{
		workbook.Write(memoryStream);
		memoryStream.Flush();

		try
		{
			HttpResponse response = HttpContext.Current.Response;
			response.ClearContent();
			response.ClearHeaders();
			response.Buffer = true;
			response.ContentType = "application/vnd.ms-excel";
			response.AddHeader("Content-Length", memoryStream.Length.ToString());
			response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
			response.BinaryWrite(memoryStream.GetBuffer());
			response.Flush();
			response.End();
		}
		catch
		{
			// Do nothing, error expected due to Flush();
		}
	}
}

I’ve used the above function to send files with 3-4 sheets full of data back to the browser… but the file size is usually “VERY LARGE”. To work around this, I have a helper function which will add the excel file to a zip (in memory) before sending back to the client.

In my last test, a 17.5MB document with 2 sheets of data was compressed to 3.5MB with my zip utility. There was still a ~13 second delay to generate the XLS file, but once prompted to download form the browser the file download completed in a few seconds.

jQuery Validation with ASP.NET

Over the past few weeks I’ve been working to migrate away from the Ajax Control Toolkit & various other ASP.NET built-in controls. My goal has been to get every page of our intranet portal down around 100K, with a maximum load time of < 1 second. I’ve made a lot of progress, but after cleaning up all the basic stuff (excess view state, duplicate references, compressed JS/CSS, sprites, etc…), I had to start looking for additional items…

Using Fiddler and FireBug, I started looking for resources that were increasing my download sizes and found that the ASP.NET validation controls were generating ~10K each time they were used in a module. Since my portal consists of injecting multiple user controls into a page, this number would vary page-by-page based on the number of modules that used the library on one page. Since I was using jQuery for all my scripting, I decided to implement jQuery Validation Plugin as a replacement for the built-in ASP.NET validation controls.

The idea was simple enough, but since I’m living in ASP.NET Web Form world (aka: One form per/page) this was a bit more difficult to implement.

Issue #1 – jQuery Validation by FIELDSET

As noted, the default setup of the validation plug in is to validate a FORM which isn’t possible with ASP.NET Web Forms. To get around this, I decided I was going to wrap all my forms in a FIELDSET with the class “validationGroup”. Using this class as my selection, I’m able to have multiple groups of controls wrapped in a FIELDSET validated separately.

To get started, you need to create a custom validation function that will recursively move up the elements/groups to find a FIELDSET with the class “validationGroup”, and then check all the input elements against the rules assigned.

Here is my custom validation function:

function ValidateAndSubmit(evt) {
    var isValid = true;

    // Get Validator & Settings
    var validator = $("#aspnetForm").validate();
    var settings = validator.settings;

    // Find the parent control that contains the elements to be validated
    var $group = $(evt.currentTarget).parents('.validationGroup');

    // Grab all the input elements (minus items listed below)
    $group
        .find(":input")
        .not(":submit, :reset, :image, [disabled]")
        .not(settings.ignore)
        .each(function (i, item) {
            // Don't validate items without rules
            if (!validator.objectLength($(item).rules()))
                return true;

            if (!$(item).valid())
                isValid = false;
        });

    // If any control is the group fails, prevent default actions (aka: Submit)
    if (!isValid)
        evt.preventDefault();
}

Next, I need to trigger the validation for the current FIELDSET I’m working in based on on some action… like the click of a submit button. Since the default setting is to validation on submit, you need to disable this and register your ValidateAndSubmit function call when your event is triggered.

// Disable default/auto binding of all buttons
$("#aspnetForm").validate({
	onsubmit: false,
	ignore: ':hidden',
	focusInvalid: true
});

// Bind the ASP.NET button with the ID "Search" to the "ValidateAndSubmit" custom validation function.
$('#<%= Search.ClientID %>').click(ValidateAndSubmit);

That’s it, with those three little little steps (Custom Validate Method, Disable Default/Auto Bind, Manual bind button to custom event), you now have a working validate by FIELDSET (or any other grouping control) solution.

Issue #2 – How to validate a group of ASP.NET Check Boxes

By default, ASP.NET will render a CheckBox or a CheckBoxList with an auto generated unique name attribute. This becomes a problem when you want to validate the group, since you can’t set a common name attribute for all the elements.

Here is an example of the HTML that would be rendered by a CheckBoxList with a layout of “Flow”.

<span id="CheckBoxGroup">
  <input id="cbk1" name="cbk1" type="checkbox">Check Box 1</input>
  <input id="cbk2" name="cbk2" type="checkbox">Check Box 2</input>
  <input id="cbk3" name="cbk3" type="checkbox">Check Box 3</input>
</span>
<span class="jQueryValError"></span>

My solution to the problem required a custom validator and a custom errorPlacement function, for the jQuery validaiton plug-in.

First, I added a custom validator that looked at a group of check boxes that are inside a wrapping “Parent()” element. I then count the number of checked check boxes to make sure the limit required (e.g. Must check at least 3 check boxes) is greater than the minimum we set.

jQuery.validator.addMethod("minChecked",
 function(value, element, param) {
	var $p = $(element).parent();
	var selected = $p.children('input[type=checkbox]:checked').length;

	if (selected >= param) {
		$p.children().removeClass('error');
		$p.siblings('.error').remove();
		return true;
		}

	$p.children('input[type=checkbox]').addClass('error');

In order to hookup this custom event to all the controls inside of a wrapping “Parent()” control, we use jQuery’s $().each function to call the validator’s rules() function.

$(".checkBoxGroup").children('input[type=checkbox]').each(function(){ $(this).rules("add", { minChecked: 3}); });

Second, our plug-in will by default put an error message next to the control(s) that triggered the error. I over ride the default behavior, we setup a “errorPlacement” function to put one error beside the wrapping “Parent()” control.

$("#<%= form1.ClientID %>").validate({
	errorPlacement: function(error, element){
		if(element.rules().minChecked > 0) {
			var $p = $(element).parent();
			if($p.siblings().hasClass("error") == false) {
				error.insertAfter($p);
			}
		}
		else {
			error.insertAfter(element);
		}
	}
});

Here is what the form looks like when you have a error with your check boxes.

jQuery Validation of ASP.NET CheckBoxes

Here is source code used to create the example above.

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Contact Form Demo</title>

    <script src="styles/jQuery.js" type="text/javascript"></script>

    <script src="styles/jQuery.Validate.js" type="text/javascript"></script>

    <script src="styles/jQuery.Validate.AddOns.js" type="text/javascript"></script>

    <script type="text/javascript">

    jQuery.validator.addMethod("minChecked",
     function(value, element, param) {
        var $p = $(element).parent();
        var selected = $p.children('input[type=checkbox]:checked').length;

        if (selected >= param) {
            $p.children().removeClass('error');
            $p.siblings('.error').remove();
            return true;
            }

        $p.children('input[type=checkbox]').addClass('error');

        return false;}, jQuery.validator.format("Please check at least {0} items.")); 

        $(document).ready(function(){
            $("#<%= form1.ClientID %>").validate({
                rules: {
                    <%= FirstName.UniqueID %>: { required: true },
                    <%= LastName.UniqueID %>: { required: true },
                    <%= Email.UniqueID %>: { required: true, email: true },
                    <%= Phone.UniqueID %>: { required: true, phoneUS: true }
                },
                errorPlacement: function(error, element){
                    if(element.rules().minChecked > 0) {
                        var $p = $(element).parent();
                        if($p.siblings().hasClass("error") == false) {
                            error.insertAfter($p);
                        }
                    }
                    else {
                        error.insertAfter(element);
                    }
                }
            });

            $(".checkBoxGroup").children('input[type=checkbox]').each(function(){ $(this).rules("add", { minChecked: 3}); });

            $(".myGroupRandom").children('input[type=checkbox]').each(function(){ $(this).rules("add", { minChecked: 1}); });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <fieldset>
            <ol>
                <li>
                    <label class="left">
                        First Name
                    </label>
                    <input type="text" id="FirstName" runat="server" /></li>
                <li>
                    <label class="left">
                        Last Name
                    </label>
                    <input type="text" id="LastName" runat="server" /></li>
                <li>
                    <label class="left">
                        Email
                    </label>
                    <input type="text" id="Email" runat="server" /></li>
                <li>
                    <label class="left">
                        Phone
                    </label>
                    <input type="text" id="Phone" runat="server" /></li>
                <li>
                    <label class="left">
                        Contact Method
                    </label>
                    <span class="checkBoxGroup">
                        <input type="checkbox" id="ReqEmail" runat="server" /><label>Email</label>
                        <input type="checkbox" id="ReqMail" runat="server" /><label>Mail</label>
                        <input type="checkbox" id="ReqPhone" runat="server" /><label>Phone</label>
                        <input type="checkbox" id="ReqNoContact" runat="server" /><label>No Contact</label>
                    </span></li>
                <li>
                    <label class="left">
                        New Letter Type
                    </label>
                    <span class="myGroupRandom" >
                        <input type="checkbox" id="Checkbox1" runat="server" /><label>Company News</label>
                        <input type="checkbox" id="Checkbox2" runat="server" /><label>Press Releases</label>
                        <input type="checkbox" id="Checkbox3" runat="server" /><label>Deals</label>
                        <input type="checkbox" id="Checkbox4" runat="server" /><label>Employement</label>
                    </span></li>
                <li>
                    <input type="submit" id="Submit" value="Submit" /></li>
            </ol>
        </fieldset>
    </div>
    </form>
</body>
</html>