Zach Hunter's Busy Life

I been working on a project site that is using WordPress as the CMS engine. This is pretty easy to do, since you can basically disable everything in WordPress (including management screen options). Once everything is removed/disabled, you start by creating pages (e.g. Home, About, News, etc…) to support all the content you need on the website. The final step required is changing the default home page from a dynamic page of blog posts to a static page. You change the default home page under Settings –> Reading –> Front Page Display –> A Static Page –> Choose an option from the Front Page drop down list. That’s it, WordPress has now been converted into a basic CMS solution.

I wish I could say that’s it, but most likely you’ll need to download and install some plug-ins to add additional functionality. I found many plug-ins still do not support WordPress 3.0, if you do get a bad plug-in installed that crashes your site remember there is an easy fix. Delete the plug-in folder via FTP or Control Panel and the plug-in will be disabled!

Here are the plug-in’s I added for my project:

1. Contact Form 7
   This is a great plug-in, it provides a very easy way to create a form that will be emailed to you. It’s designed using basic HTML online, and it provides access to those dynamic files in the processor that sends you an email and sends results back to the user.
2. Google Maps
   This is a very basic Google Maps plug-in, the only things you can set are size. There is lots of tweaking needed to get your maps looking right (e.g. Had to use both Google Maps and Google Earth to get my icons to display on the map).
3. WordPress.com Stats
   I’ve been using this on my blog and love the UI. I wish this was all being done client-side, but for some reason it was created as service based and it injects a single line text ad into the top right of the admin interface. Regardless of that lil annoyance, this is an awesome stat solution for WordPress.

The only other thing I added was a customized SSL plug-in that was based on 3 other plug-ins that I found not-working. I wanted a single page (“Membership”) to use HTTPS but everything else to use “HTTP”. Here is the code I wrote to get everything working.

<?php
/*
This plug-in is based on WPSSL, forcessl and various other posts/comments I found while searching for a soltuion.  The plug-in is used by adding the meta tag "force_ssl" with any value to any pages where you want SSL ("HTTPS") enabled.  If the page does NOT have this set and your not looking at an admin page (you can enable SSL for admin/login page in wpconfig.php), then display as HTTP.  This prevent links on the SSL page from displaying as SSL for non-secure pages.  This plug-in was tested on WordPress 3.0
*/

function wpssl_forcessl()
{
	global $post;

    $post_id = $post;

    if (is_object($post_id))
    {
		$post_id = $post_id->ID;
	}

    $force_ssl  = get_post_meta($post_id, 'force_ssl');

    if(!empty($force_ssl))
    {
    	if(!stristr($_SERVER['REQUEST_URI'], 'wp-admin')) {
			if($_SERVER["HTTPS"] != "on") {
				$newurl = "https://" . $_SERVER["SERVER_NAME"] . $_SERVER["REQUEST_URI"];
            	header("Location: $newurl");
            	exit();
        	}
     	}
    } else {
		if(!stristr($_SERVER['REQUEST_URI'], 'wp-admin')) {
			if($_SERVER["HTTPS"] == "on") {
				$newurl = "http://" . $_SERVER["SERVER_NAME"] . $_SERVER["REQUEST_URI"];
				header("Location: $newurl");
				exit();
			}
	}	}
}

add_action('wp', 'wpssl_forcessl');
?>

I was looking over my “generic” export a 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 columns for subtotals. To fix the problem they could use “Text to Columns”, but this was really a work-around to my lack of creativeness. I came up with an improved “ExportDataTableToExcel” function that will now default the data types Int/Double/Decimal into the NPOI Double type.

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

        // Build Header
        foreach (DataColumn column in sourceTable.Columns)
        {
            // Create New Cell
            HSSFCell headerCell = headerRow.CreateCell(column.Ordinal);

            // Set Cell Value
            headerCell.SetCellValue(column.ColumnName);

            // Create Style
            HSSFCellStyle headerCellStyle = workbook.CreateCellStyle();
            headerCellStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
            headerCellStyle.FillPattern = CellFillPattern.SOLID_FOREGROUND;

            // Add Style to Cell
            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);
                HSSFRow additionalHeaderRow = sheet.CreateRow(0);

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

                rowIndex = 1;
            }

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

            foreach (DataColumn column in sourceTable.Columns)
            {
                switch (column.DataType.FullName)
                {
                    case "System.String":
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                        break;
                    case "System.Int":
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Double":
                    case "System.Decimal":
                        dataRow.CreateCell(column.Ordinal).SetCellValue(Double.Parse(row[column].ToString()));
                        break;
                    default:
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                        break;
                }
            }

            rowIndex++;
        }

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

        workbook.Write(memoryStream);
        memoryStream.Flush();

        HttpResponse response = HttpContext.Current.Response;
        response.ContentType = "application/vnd.ms-excel";
        response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
        response.Clear();

        response.BinaryWrite(memoryStream.GetBuffer());
        response.End();
    }

The key part to look at above is the “switch (column.DataType.FullName)” block. This grabs the data type of the source column to use in the SetCellValue() call. The result is an Excel file with numeric data types formatted as numbers!

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 being cleaning up all the basic stuff (excess view state, duplicate references, compressed JS/CSS, sprites, etc…), I had to start looking for additional items…

After using Fiddlerand FireBug to analyze the page contents, I found that the ASP.NET validation controls were generating ~10K in increase download size. Since my portal consists of injecting multiple user controls into a page, this number would vary by the number of modules using the validation controls. To stay consist with my client side library, I decided to implement jQuery Validation Plugin as a replacement to the ASP.NET validation controls.

The idea was simple enough, but since I’m living in the ASP.NET Web Form this were a bit more difficult to implement.

Issue #1 – jQuery Validation by FIELDSET

I’ve been using jQuery Validation for about a year, so I was already aware that it was built around using a single FORM versus multiple FIELDSETs. I put together a solution based on multiple articles I read about to come up with a “SaveAndSubmit” function. This function allows me to re-scope (ex. FIELDSET vs FORM) the group of controls you want to validate.

Here is my custom validation function that validates a group of controls within a FIELDSET with the class “validationGroup”.

    function ValidateAndSubmit(evt) {
       // Find the parent control that contains the class validationGroup
        var $group = $(evt.currentTarget).parents('.validationGroup');

        var isValid = true;

       // Look at all the input controls and run them against the rules
        $group.find(':input').each(function(i, item) {
            if (!$(item).valid())
                isValid = false;
        });

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

To use a custom validation function, you need disable the automatic binding of all buttons to the default validation function. This allows you to bind to your new custom event and to control which controls should trigger the validation.

        // Disable default/auto binding of all buttons
        $("#aspnetForm").validate({
                onsubmit: false
        });

        // 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.

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>

I saw a discussion posting on the NPOI discussion forum on CodePlex today, asking if there was a function like SetCell(X,Y,Value) in NPOI. Unfortunately there isn’t… At least I was never able to find one, so I created my own. Since I only needed to set values I added three basic ones into my helper class to make available to everybody using NPOI in my project. I was really tempted to add these to the HSSFWorksheet, but to keep my code save I figured a helper class for all my extras would be enough. Here is my version of a SetCellValue() helper set of functions.

        private static void SetCellValue(HSSFSheet worksheet, int columnPosition, int rowPosition, DateTime value)
        {
            // Get row
            using (HSSFRow row = worksheet.GetRow(rowPosition))
            {
                // Get or Create Cell
                using (HSSFCell cell = row.GetCell(columnPosition) ?? row.CreateCell(columnPosition))
                {
                    cell.SetCellValue(value);
                    cell.CellStyle.DataFormat = 14;
                }
            }
        }

        private static void SetCellValue(HSSFSheet worksheet, int columnPosition, int rowPosition, double value)
        {
            // Get row
            using (HSSFRow row = worksheet.GetRow(rowPosition))
            {
                // Get or Create Cell
                using (HSSFCell cell = row.GetCell(columnPosition) ?? row.CreateCell(columnPosition))
                {
                    cell.SetCellValue(value);
                }
            }
        }

        private static void SetCellValue(HSSFSheet worksheet, int columnPosition, int rowPosition, string value)
        {
            // Get row
            using (HSSFRow row = worksheet.GetRow(rowPosition))
            {
                // Get or Create Cell
                using (HSSFCell cell = row.GetCell(columnPosition) ?? row.CreateCell(columnPosition))
                {
                    cell.SetCellValue(value);
                }
            }
        }

        // Set Date
        SetCellValue(sheet, 9, 3, DateTime.Now);
        // Set Number
        SetCellValue(sheet, 9, 4, 100.01);
        // Set Text
        SetCellValue(sheet, 9, 5, "Zach Roxs!");

As you can see it’s pretty easy to create a SetCellValue() helper. I plan to create another version of these that uses Excel coordinates (e.g. A5, Z10, etc…), so my die hard Excel teammates can use their native Excel mapping syntax!

Another day and another little tidbit on using NPOI. I was doing tool mock-up at work today when I ran across a need for a copy row function. After searching high and low, I realized NPOI does not currently offer this capability. After looking around (Google, NPOI and POI threads) I decided to create my own helper function. I’m sure there might be a few things I missed in my routine since the library is a bit new to me, but after testing this against a bunch of different scenarios I’m pretty confident this will work for 99% of my needs and maybe a high percent of yours as well.

Here is the function in all it’s glory, I thought about modify the NPOI source but since I’m not sure where it’s going I figured I’d just add this in my own little NPOI.CustomHelpers class that I can use with my NPOI project.

        /// <summary>
        /// HSSFRow Copy Command
        ///
        /// Description:  Inserts a existing row into a new row, will automatically push down
        ///               any existing rows.  Copy is done cell by cell and supports, and the
        ///               command tries to copy all properties available (style, merged cells, values, etc...)
        /// </summary>
        /// <param name="workbook">Workbook containing the worksheet that will be changed</param>
        /// <param name="worksheet">WorkSheet containing rows to be copied</param>
        /// <param name="sourceRowNum">Source Row Number</param>
        /// <param name="destinationRowNum">Destination Row Number</param>
        private void CopyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum)
        {
            // Get the source / new row
            HSSFRow newRow = worksheet.GetRow(destinationRowNum);
            HSSFRow sourceRow = worksheet.GetRow(sourceRowNum);

            // If the row exist in destination, push down all rows by 1 else create a new row
            if (newRow != null)
            {
                worksheet.ShiftRows(destinationRowNum, worksheet.LastRowNum, 1);
            }
            else
            {
                newRow = worksheet.CreateRow(destinationRowNum);
            }

            // Loop through source columns to add to new row
            for (int i = 0; i < sourceRow.LastCellNum; i++)
            {
                // Grab a copy of the old/new cell
                HSSFCell oldCell = sourceRow.GetCell(i);
                HSSFCell newCell = newRow.CreateCell(i);

                // If the old cell is null jump to next cell
                if (oldCell == null)
                {
                    newCell = null;
                    continue;
                }

                // Copy style from old cell and apply to new cell
                HSSFCellStyle newCellStyle = workbook.CreateCellStyle();
                newCellStyle.CloneStyleFrom(oldCell.CellStyle); ;
                newCell.CellStyle = newCellStyle;

                // If there is a cell comment, copy
                if (newCell.CellComment != null) newCell.CellComment = oldCell.CellComment;

                // If there is a cell hyperlink, copy
                if (oldCell.Hyperlink != null) newCell.Hyperlink = oldCell.Hyperlink;

                // Set the cell data type
                newCell.SetCellType(oldCell.CellType);

                // Set the cell data value
                switch (oldCell.CellType)
                {
                    case HSSFCellType.BLANK:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                    case HSSFCellType.BOOLEAN:
                        newCell.SetCellValue(oldCell.BooleanCellValue);
                        break;
                    case HSSFCellType.ERROR:
                        newCell.SetCellErrorValue(oldCell.ErrorCellValue);
                        break;
                    case HSSFCellType.FORMULA:
                        newCell.SetCellFormula(oldCell.CellFormula);
                        break;
                    case HSSFCellType.NUMERIC:
                        newCell.SetCellValue(oldCell.NumericCellValue);
                        break;
                    case HSSFCellType.STRING:
                        newCell.SetCellValue(oldCell.RichStringCellValue);
                        break;
                    case HSSFCellType.Unknown:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                }
            }

            // If there are are any merged regions in the source row, copy to new row
            for (int i = 0; i < worksheet.NumMergedRegions; i++)
            {
                CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i);
                if (cellRangeAddress.FirstRow == sourceRow.RowNum)
                {
                    CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
                                                                                (newRow.RowNum +
                                                                                 (cellRangeAddress.FirstRow -
                                                                                  cellRangeAddress.LastRow)),
                                                                                cellRangeAddress.FirstColumn,
                                                                                cellRangeAddress.LastColumn);
                    worksheet.AddMergedRegion(newCellRangeAddress);
                }
            }

        }

The code comments above should give you a good idea of what I’m doing, if something doesn’t make sense just ask. The key things I wanted to make sure got copied were; Cell Style, Cell Value, Cell Type, Merged Cell Settings. In the end I noticed a few other things that I thought I might use in the future, so I included them as well. Here is an example of how to call to CopyRow along with a snapshot of the end result.

            // Grab my NPOI workbook memorystream
            HSSFWorkbook workbook = new HSSFWorkbook(memoryStream);

            // Grab my test worksheet
            HSSFSheet sheet = workbook.GetSheet("Sheet1");

            // Copy Excel Row 1 to Excel Row 3
            CopyRow(workbook, sheet, 0, 2);

            // Copy Excel Row 2 to Excel Row 4
            CopyRow(workbook, sheet, 1, 3);

NPOI - Copy Row Helper Function Test Results