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>
These icons link to social bookmarking sites where readers can share and discover new web pages.
  • DotNetKicks
  • Facebook
  • Digg

NPOI – Set Cell Helper

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!

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • DotNetKicks
  • Facebook
  • Digg

NPOI – Copy Row Helper

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

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • DotNetKicks
  • Facebook
  • Digg

Using jQuery to prevent “Delete” mistakes

A few days ago I was reading a web design article that talked about the “Poka-Yoke” principle. It was such a funny phrase that I had to go to Wikipedia to figure out the definition. In a nutshell, the principle refers to the process of “idiot-proofing” human interaction with a process. This term is based on the Japanese words “yokeru” avoid and “poka” mistake, this is rather funny for me since I work for a Japanese company that has clearly never heard of this principle!

This term came to mind today while helping another programmer develop a system to prevent accidental in their UI. After discussing what needed to be done, it was clear that we needed to implement some client side logic to “prevent… guide…” the user into making the correct choices.

The UI was pretty straight forward, there will be a few buttons to do various actions on rows in a table. The users will select one or more rows for the table and then click the button of the action they want to perform. Sounds simple, right… We’ll it is and it’s also really easy to select the wrong row and/or the wrong button. Training is a big help in any process, but one you leave the users alone the system will be the only thing enforcing the rules.

Here is an example of a table where rows will be selected for deletion. I used a “asp:Table” to simulate the markup for a table that will be generated with a “asp:GridView”. In my past 5 years of working with ASP.NET, this was the first time I ever used or seen this control used.

    <div class="mediumTable">
        <asp:Button ID="DeleteButton" runat="server" Text="Delete" />
        <asp:Table ID="Table1" runat="server">
            <asp:TableHeaderRow>
                <asp:TableHeaderCell>Chk</asp:TableHeaderCell>
                <asp:TableHeaderCell>Product</asp:TableHeaderCell>
                <asp:TableHeaderCell>Stock</asp:TableHeaderCell>
            </asp:TableHeaderRow>
            <asp:TableRow>
                <asp:TableCell>
                    <asp:CheckBox ID="CheckBox1" runat="server" /></asp:TableCell>
                <asp:TableCell>Apples</asp:TableCell>
                <asp:TableCell>0</asp:TableCell>
            </asp:TableRow>
            <asp:TableRow>
                <asp:TableCell>
                    <asp:CheckBox ID="CheckBox2" runat="server" /></asp:TableCell>
                <asp:TableCell>Oranges</asp:TableCell>
                <asp:TableCell>100</asp:TableCell>
            </asp:TableRow>
            <asp:TableRow>
                <asp:TableCell>
                    <asp:CheckBox ID="CheckBox3" runat="server" /></asp:TableCell>
                <asp:TableCell>Pears</asp:TableCell>
                <asp:TableCell>150</asp:TableCell>
            </asp:TableRow>
            <asp:TableRow>
                <asp:TableCell>
                    <asp:CheckBox ID="CheckBox4" runat="server" /></asp:TableCell>
                <asp:TableCell>Watermelon</asp:TableCell>
                <asp:TableCell>2</asp:TableCell>
            </asp:TableRow>
        </asp:Table>
    </div>
    </form>

Here is the delete “prevention” system we came up. We had a few other ideas like, but after doing a few demos of each solution we decided on the following code that will be discussed below.

        $(document).ready(function() {
            $('#<%= DeleteButton.ClientID %>').click(function() {
                // Find all selected items
                var CheckedCheckBoxes = $('#<%= Table1.ClientID %>').find("input[type='checkbox']:checked");
                // If there are no selected items, exit
                if (CheckedCheckBoxes.length == 0) {
                    alert("You must make a selection before you can delete an item.");
                    return false;
                }
                // If there are selected items, loop
                CheckedCheckBoxes.each(function() {
                    var isProcessed = $(this).parent().parent().find('td:last').text();
                    // If current item is already processed, set flat to true
                    if (jQuery.trim(isProcessed) > 0) {
                        deletedProcessed = true;
                    }
                });
                // Check flag, if true stop ALL deletes and notify user
                if (deletedProcessed) {
                    alert("You can't delete a product with stock!");
                    return false;
                }
                else {
                    // Do a final confirmation the user wants to delete the selection
                    var answer = confirm('Are you sure you want to delete your selection?');
                    return answer
                }
            });
        });

Since selection box was used for a few different functions (process orders, hold orders and edit orders) we needed a way to add a little extra checking to only the delete button. Our goals with the logic above was the following.

  1. Disables “Delete” if nothing has been selected.
  2. Disables “Delete” if the user selected a item should not be deleted (e.g. Has Inventory).
  3. Prompts the user to confirm the really want to delete the item.

In addition to these ideas, we also had the following ideas…

  1. Alert row # of items that don’t meet the business rule
  2. Highlight the row in “red” that don’t meet the business rule
  3. Physically “Disable” the delete button if they select an item that don’t meet the business rules

Mistakes are human nature and by double and triple checking what they are doing we can save the users and ourselves from many emails/phone calls/visits about how the system “deleted the data all by itself”.

Mistake proofing is such an easy idea to grasp and agree with, that it’s surprising that we don’t see more of it in the systems we using everyday. I’m sure I’ve developed a few bad ones, hopefully the audience using it was small and it didn’t mitigate itself into other things. Working for a Japanese company who has developed some of the worst internal tools I’ve ever seen (0 UI design effort), I’m sure to bring up the new Japanese “Poka-Yoke” principle that next time I talk to one of the developers in Japan.

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • DotNetKicks
  • Facebook
  • Digg

How to read in XLSX data for editing with NPOI

NPOI is a great solution for working with XLS documents, but what happens when you need to read in XLSX documents as part of your solution? The easiest solution I could think of was to use OLEDB to extract out all the data (the data, the whole data and nothing but the data) from a worksheet into a new NPOI document.

If you need more support than just reading in data from Excel 2007+ (XLSX), you should look at using the ExcelPackage project that is hosted at CodePlex.

The benefit to my approach, is that your able to use your existing NPOI solutions when data is provided in XLSX format. This is far from full Excel 2007+ support, but if your applicaitons only requirement is “reading in data” then your problem is solved. NPOI v1.6 is supposed to fully support the XLSX format, until then this function will provide basic XLSX support.

Functions used to convert XLSX document to a NPOI document
    /// <summary>
    /// Render a Excel 2007 (xlsx) Worksheet to NPOI Excel 2003 Worksheet, all excel formatting
    /// from XLSX will be lost when converted.  NPOI roadmap shows v1.6 will support Excel 2007 (xlsx).
    /// NPOI Roadmap  : http://npoi.codeplex.com/wikipage?title=NPOI%20Road%20Map&referringTitle=Documentation
    /// NPOI Homepage : http://npoi.codeplex.com/
    /// </summary>
    /// <param name="excelFileStream">XLSX FileStream</param>
    /// <param name="sheetName">Excel worksheet to convert</param>
    /// <returns>MemoryStream containing NPOI Excel workbook</returns>
    public static Stream ConvertXLSXWorksheetToXLSWorksheet(Stream excelFileStream, string sheetName)
    {
        // Temp file name
        string tempFile = HttpContext.Current.Server.MapPath("~/uploads/" + HttpContext.Current.Session.LCID + ".tmp");

        // Temp data container (using DataTable to leverage existing RenderDataTableToExcel function)
        DataTable dt = new DataTable();

        try
        {
            // Create temp XLSX file
            FileStream fileStream = new FileStream(tempFile, FileMode.Create, FileAccess.Write);

            const int length = 256;
            Byte[] buffer = new Byte[length];
            int bytesRead = excelFileStream.Read(buffer, 0, length);

            while (bytesRead > 0)
            {
                fileStream.Write(buffer, 0, bytesRead);
                bytesRead = excelFileStream.Read(buffer, 0, length);
            }

            excelFileStream.Close();
            fileStream.Close();

            // Read temp XLSX file using OLEDB
            // Tested on Vista & Windows 2008 R2
            using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;Data Source=" + tempFile + @";Extended Properties=Excel 12.0;"))
            {
                con.Open();
                string sql = String.Format("SELECT * FROM [{0}$]", sheetName);
                OleDbDataAdapter da = new OleDbDataAdapter(sql, con);

                da.Fill(dt);
            }
        }
        finally
        {
            // Make sure temp file is deleted
            File.Delete(tempFile);
        }

        // Return a new POI Excel 2003 Workbook
        return RenderDataTableToExcel(dt);
    }

    /// <summary>
    /// Render DataTable to NPOI Excel 2003 MemoryStream
    /// NOTE:  Limitation of 65,536 rows suppored by XLS
    /// </summary>
    /// <param name="sourceTable">Source DataTable</param>
    /// <returns>MemoryStream containing NPOI Excel workbook</returns>
    public static Stream RenderDataTableToExcel(DataTable sourceTable)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        MemoryStream memoryStream = new MemoryStream();
        // By default NPOI creates "Sheet0" which is inconsistent with Excel using "Sheet1"
        HSSFSheet sheet = workbook.CreateSheet("Sheet1");
        HSSFRow headerRow = sheet.CreateRow(0);

        // Header Row
        foreach (DataColumn column in sourceTable.Columns)
            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

        // Detail Rows
        int rowIndex = 1;

        foreach (DataRow row in sourceTable.Rows)
        {
            HSSFRow dataRow = sheet.CreateRow(rowIndex);

            foreach (DataColumn column in sourceTable.Columns)
            {
                dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
            }

            rowIndex++;
        }

        workbook.Write(memoryStream);
        memoryStream.Flush();
        memoryStream.Position = 0;

        return memoryStream;
    }

NOTE: You could convert an entire workbook (multiple work sheets) into a new NPOI workbook if you looped over each work sheet in the XLSX document. Since all my tools are based on using a single work sheet, I’ll leave the workbook conversion up to you.

Example using XLSX function
            if (FileUpload.PostedFile.ContentLength > 0)
            {
                Stream uploadFileStream = FileUpload.PostedFile.InputStream;

                // If the file uploaded is "XLSX", convert it's Sheet1 to a NPOI document
                if (FileUpload.PostedFile.FileName.EndsWith("xlsx"))
                {
                    uploadFileStream = ExcelHelper.ConvertXLSXWorksheetToXLSWorksheet(uploadFileStream, "Sheet1");
                }
            }

** All your NPOI logic that adds columns, changes cell/row formatting, etc… will now work with the data extracted from the XLSX document.

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • DotNetKicks
  • Facebook
  • Digg