June 2011 - Posts

Creating a Currency Masked TextBox with On-the-Fly Currency Formatting
25 June 11 03:21 AM | Scott Mitchell | with no comments

By default, a user can enter any character into a textbox. Masked textboxes help reduce user input error by limiting what characters a user can type into a textbox. Masked textboxes have been a standard user input element in desktop applications for decades, but are less common in web applications for a variety of reasons. However, it’s not terribly difficult to implement masked textboxes. All that’s required is a touch of JavaScript and a sprinkle of jQuery.

In a recent project the client wanted a masked textbox for the textboxes on the page collecting currency information. Moreover, he wanted the user’s input to automatically be displayed as a formatted currency value in the textbox after entering their value. (Check out a live demo of my script…) But first things first, let’s see how to create a currency masked textbox.

Allowing Only Currency-Related Characters In a TextBox

There are a number of existing masked input plugins for jQuery. After trying some out I decided to roll my own JavaScript functions. I intend to come back to these and turn them into jQuery plugins, but for now they’re just JavaScript functions. As you can see in the script below, I created four functions:

  • numbersOnly – allows just number inputs, whether they are from the letters at the top of the keyboard or from the number pad.
  • numbersAndCommasOnly – allows number inputs and commas.
  • decimalsOnly – allows numbers, commas, and periods (either from the main keyboard or the number pad).
  • currenciesOnly – allows numbers, commas, periods, and the dollar sign.

In addition to the allowed characters discussed above, the functions also permit “special character key codes,” namely Delete, Backspace, left arrow, right arrow, Home, End and Tab. What keycodes are valid are listed in the variables at the top of the script; see Javascript Char Codes for a table listing the keys and their corresponding key codes.

Here is the script of interest:

// JavaScript I wrote to limit what types of input are allowed to be keyed into a textbox 
var allowedSpecialCharKeyCodes = [46,8,37,39,35,36,9];
var numberKeyCodes = [44, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105];
var commaKeyCode = [188];
var decimalKeyCode = [190,110];

function numbersOnly(event) {
    var legalKeyCode =
        (!event.shiftKey && !event.ctrlKey && !event.altKey)
            &&
        (jQuery.inArray(event.keyCode, allowedSpecialCharKeyCodes) >= 0
            ||
        jQuery.inArray(event.keyCode, numberKeyCodes) >= 0);

    if (legalKeyCode === false)
        event.preventDefault();
}

function numbersAndCommasOnly(event) {
    var legalKeyCode =
        (!event.shiftKey && !event.ctrlKey && !event.altKey)
            &&
        (jQuery.inArray(event.keyCode, allowedSpecialCharKeyCodes) >= 0
            ||
        jQuery.inArray(event.keyCode, numberKeyCodes) >= 0
            ||
        jQuery.inArray(event.keyCode, commaKeyCode) >= 0);

    if (legalKeyCode === false)
        event.preventDefault();
}

function decimalsOnly(event) {
    var legalKeyCode =
        (!event.shiftKey && !event.ctrlKey && !event.altKey)
            &&
        (jQuery.inArray(event.keyCode, allowedSpecialCharKeyCodes) >= 0
            ||
        jQuery.inArray(event.keyCode, numberKeyCodes) >= 0
            ||
        jQuery.inArray(event.keyCode, commaKeyCode) >= 0
            ||
        jQuery.inArray(event.keyCode, decimalKeyCode) >= 0);

    if (legalKeyCode === false)
        event.preventDefault();
}

function currenciesOnly(event) {
    var legalKeyCode =
        (!event.shiftKey && !event.ctrlKey && !event.altKey)
            &&
        (jQuery.inArray(event.keyCode, allowedSpecialCharKeyCodes) >= 0
            ||
        jQuery.inArray(event.keyCode, numberKeyCodes) >= 0
            ||
        jQuery.inArray(event.keyCode, commaKeyCode) >= 0
            ||
        jQuery.inArray(event.keyCode, decimalKeyCode) >= 0);

    // Allow for $
    if (!legalKeyCode && event.shiftKey && event.keyCode == 52)
        legalKeyCode = true;

    if (legalKeyCode === false)
        event.preventDefault();
}

My script is, admittedly, very US-centric. I have not tested the key codes with a non-English keyboard and for currencies I only allow a dollar sign. For the project I wrote this script for this is (currently) a non-issue since it is used within the corporate firewall and all sales are domestic, but clearly the above script would not work as well for international settings.

Applying the Currency Masking Script to a TextBox on the Page

With the above script in place you can have a textbox on the page mask its input by having the appropriate function called in response to the keydown event. The following jQuery syntax wires up this logic to all single-line textboxes that have the CSS class currenciesOnly.

$(document).ready(function () {
    $("input[type=text].currenciesOnly").live('keydown', currenciesOnly);
});

That’s it!

Formatting the Just-Entered Currency

Another requirement my client had was to format the just-entered number as a currency. That is, to change the user’s input – say, 45000 – to a formatted value like $45,000.00 immediately after their tabbed out of the textbox. To accomplish this I used Ben Dewey’s jQuery Format Currency Plugin, which you can see a demo of at http://www.bendewey.com/code/formatcurrency/demo/. This plugin adds a formatCurrency function that you can call on a set of elements returned by a jQuery selector.

To use this plugin I updated the $(document).ready event handler shown above to also call the formatCurrency function on blur:

$(document).ready(function () {
    $("input[type=text].currenciesOnly").live('keydown', currenciesOnly)
                        .live('blur', 
                                 function () { 
                                     $(this).formatCurrency(); 
                                 }
                              );
});

In short, whenever a textbox with a CSS class of currenciesOnly is blurred, the just-blurred textbox’s inputs are formatted as a currency thanks to the formatCurrency function.

And that’s all there is to it, folks.

To see a live demo of the above code, check out this jsfiddle script: http://jsfiddle.net/CBDea/1/

Happy Programming!

Filed under:
A Synchronized Visual Studio Crash
20 June 11 11:52 PM | Scott Mitchell | with no comments

About month ago at a user group meeting the guy sitting across from me shared the tale of a talk he had attended. The speaker’s laptop had downloaded a slew of Windows Updates in the background, after which the “Restart your computer to finish installing important updates” dialog box appeared. The speaker unwittingly clicked the “Restart now” button, which closed the presentation and displayed those ominous words: “Please do not power off or unplug your machine… Installing update 1 of 43.” But how many speakers have the distinction of not only crashing their own system, but in addition the laptops of dozens of others in the audience? I am now among those hallowed ranks.

On Saturday I presented another full-day ASP.NET MVC 3 training event in Los Angeles. Attendees were encouraged to bring their laptops and to follow along as I presented the material. At one point, I was creating a demo and writing some HTML in the _Layout.cshtml file. After typing in some HTML I switched from the _Layout.cshtml file to a controller at which point Visual Studio froze on me. Hard. And with vengeance. I quickly apologized and launched Windows Task Manager to kill VS and restart it.

As Visual Studio was restarting I heard a rising murmur from the crowd – many other people just had Visual Studio crash on them, too! Several people were following along with me key stroke for key stroke and had experienced the same crash. Lovely. I asked the audience to humor me and I tried to reproduce the crash again, but had no luck. But clearly whatever sequence of events caused Visual Studio to crash was deterministic seeing that several other people had the same experience when following the same steps.

So the next time you are at a conference or user group and are swapping stories of speaker flubs or miscues, feel free to tell them about this one guy who not only crashed his own demo, but was so bad that also he crashed dozens of other laptops in the audience.

Filed under:
Export an ADO.NET DataTable to Excel using NPOI
08 June 11 03:10 AM | Scott Mitchell | with no comments

My latest article on DotNetSlackers looks at how to create Excel spreadsheets using NPOI. NPOI is a free, open-source .NET library for creating and reading Excel spreadsheets and is a port of the Java POI library. In the article I show how to use NPOI to programmatically export data into a spreadsheet with multiple sheets, formatting, and so on. Specifically, my demos look at having a set of objects to export – for example, a set of Linq-to-Sql entity objects – and then crafting an Excel spreadsheet by enumerating those objects and adding applicable rows and columns to the spreadsheet.

Recently, I needed the ability to allow for more generic exports to Excel. In one of the web applications I work on there is an Excel Export page that offers a number of links that, when clicked, populate an ADO.NET DataTable with the results of a particular database view, generate a CSV file, and then stream that file down to the client with a Content-Type of application/vnd.ms-excel, which prompts the browser to display the CSV content in Excel. This has worked well enough over the years, but unfortunately such data cannot be viewed from the iPad; however, the iPad can display a native Excel file (.xls). The solution, then, was to update the code to use NPOI to return an actual Excel spreadsheet rather than a CSV file.

To accomplish this I wrote a bit of code that exports the contents of any ol’ DataTable into an Excel spreadsheet using NPOI. It’s pretty straightforward, looping through the rows of the DataTable and adding each as a row to the Excel spreadsheet. There were, however, a couple of gotcha points:

  1. Excel 2003 limits a sheet inside a workbook to a maximum of 65,535 rows. To export more rows than this you need to use multiple sheets. Zach Hunter’s blog entry, NPOI and the Excel 2003 Row Limit, provided a simple approach to avoiding this problem. In short, I keep track of how many rows I’ve added to the current sheet and once it exceeds a certain threshold I create a new sheet and start from the top.
  2. Excel has limits and restrictions on the length of sheet names and what characters can appear in a sheet name. I have a method named EscapeSheetName that ensures the sheet name is of a valid length and does not contain any offending characters.
  3. When exporting very large Excel spreadsheets you may bump into OutOfMemoryExceptions if you are developing on a 32-bit system and are trying to dump the Excel spreadsheet into a MemoryStream object, which is a common technique for streaming the data to the client. See this Stackoverflow discussion for more information and possible workarounds: OutOfMemoryException When Generating a Large Excel Spreadsheet.

To demonstrate exporting a DataTable to Excel using NPOI, I augmented the code demo available for download from my DotNetSlackers article to include a new class in the App_Code folder named NPoiExport, which you can download from http://scottonwriting.net/demos/ExcelExportToDataTable.zip. This class offers an ExportDataTableToWorkbook method that takes as input a DataTable and the sheet name to use for the Excel workbook. (If there are multiple sheets needed, the second sheet is named “sheetName – 2,” the third, “sheetName – 3,” and so forth.)

The ExportDataTableToWorkbook method follows:

public void ExportDataTableToWorkbook(DataTable exportData, string sheetName)
{
    // Create the header row cell style
    var headerLabelCellStyle = this.Workbook.CreateCellStyle();
    headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
    var headerLabelFont = this.Workbook.CreateFont();
    headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
    headerLabelCellStyle.SetFont(headerLabelFont);

    var sheet = CreateExportDataTableSheetAndHeaderRow(exportData, sheetName, headerLabelCellStyle);
    var currentNPOIRowIndex = 1;
    var sheetCount = 1;

    for (var rowIndex = 0; rowIndex < exportData.Rows.Count; rowIndex++)
    {
        if (currentNPOIRowIndex >= MaximumNumberOfRowsPerSheet)
        {
            sheetCount++;
            currentNPOIRowIndex = 1;

            sheet = CreateExportDataTableSheetAndHeaderRow(exportData, 
                                                            sheetName + " - " + sheetCount, 
                                                            headerLabelCellStyle);
        }

        var row = sheet.CreateRow(currentNPOIRowIndex++);

        for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)
        {
            var cell = row.CreateCell(colIndex);
            cell.SetCellValue(exportData.Rows[rowIndex][colIndex].ToString());
        }
    }
}

Whenever a new sheet needs to be generated – either when starting or when the maximum number of rows per sheet is exceeded – the CreateExportDataTableSheetAndHeaderRow method is called. This method creates a header row, listing the name of each column in the DataTable.

protected Sheet CreateExportDataTableSheetAndHeaderRow(DataTable exportData, string sheetName, CellStyle headerRowStyle)
{
    var sheet = this.Workbook.CreateSheet(EscapeSheetName(sheetName));

    // Create the header row
    var row = sheet.CreateRow(0);

    for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)
    {
        var cell = row.CreateCell(colIndex);
        cell.SetCellValue(exportData.Columns[colIndex].ColumnName);

        if (headerRowStyle != null)
            cell.CellStyle = headerRowStyle;
    }

    return sheet;
}

Here’s how you would go about using the NpoiExport class to export a DataTable and then stream it down to the client:

  1. Create and populate the DataTable with the data to export. Remember, the DataTable’s column names are what will appear in the header row so use aliases in the SQL query to provide more description/formatted column names, if you prefer.
  2. Create an instance of the NpoiExport class.
  3. Call the object’s ExportDataTableToWorkbook method passing in the DataTable from step 1 (along with a sheet name of your choice).
  4. Set the Content-Type and Content-Disposition response headers appropriately and then stream down the contents of the Excel document, which is accessible via the NpoiExport object’s GetBytes method.

The following code snippet illustrates the above four steps.

// Populate the DataTable
var myDataTable = new DataTable();
using (var myConnection = new SqlConnection(connectionString)
{
    using (var myCommand = new SqlCommand())
    {
        myCommand.Connection = myConnection;
        myCommand.CommandText = sqlQuery;

        using (var myAdapter = new SqlDataAdapter(myCommand))
        {
            myAdapter.Fill(myDataTable);
        }
    }
}


// Creat the NpoiExport object
using (var exporter = new NpoiExport())
{
    exporter.ExportDataTableToWorkbook(myDataTable, "Results");

    string saveAsFileName = string.Format("Results-{0:d}.xls", DateTime.Now);

    Response.ContentType = "application/vnd.ms-excel";
    Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
    Response.Clear();
    Response.BinaryWrite(exporter.GetBytes());
    Response.End();
}

That’s all there is to it. The screen shot below shows an example of the exported report. Note that it lacks the nice formatting, auto-sized columns, and other bells and whistles that are possible with NPOI when constructing a report by hand (as I showed in Create Excel Spreadsheets Using NPOI), but it does make exporting data to Excel an exercise of just a few lines of code. And its exported data that can be opened and viewed from an iPad.

ExcelOutput

The above Excel spreadsheet was created using the ad-hoc query:

SELECT CategoryName AS [Category], 
       Description, 
       (SELECT COUNT(*) 
        FROM Products 
        WHERE Products.CategoryID = Categories.CategoryID) 
            AS [Product Count]
FROM Categories
WHERE CategoryID >= 3

Happy Programming!

Download: http://scottonwriting.net/demos/ExcelExportToDataTable.zip

Filed under:
More Posts

Archives

My Books

  • Teach Yourself ASP.NET 4 in 24 Hours
  • Teach Yourself ASP.NET 3.5 in 24 Hours
  • Teach Yourself ASP.NET 2.0 in 24 Hours
  • ASP.NET Data Web Controls Kick Start
  • ASP.NET: Tips, Tutorials, and Code
  • Designing Active Server Pages
  • Teach Yourself Active Server Pages 3.0 in 21 Days

I am a Microsoft MVP for ASP.NET.

I am an ASPInsider.