This is the 3rd post of a series of posts about NPOI 2.0.

This time we’re going to see how easy it’s to copy cells, rows and even sheets. As a bonus you also get to see the process for workbook merging/combining.

There’s nothing much to say so we’re going directly distill some “codez” in front of you…

Copying Cells and Rows
Here’s what it gets to copy Cells and Rows:

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;

namespace CopyRowsAndCellsInXls
{
    class Program
    {
        static HSSFWorkbook hssfWorkbook;

        static void Main(string[] args)
        {
            InitializeWorkbook();

            ISheet sheet = hssfWorkbook.GetSheetAt(0);

            ICell cell = sheet.GetRow(4).GetCell(1);
            cell.CopyCellTo(3); // Copy B5 to D5
            
            IRow row = sheet.GetRow(3);
            row.CopyCell(0, 1); // Copy A4 to B4

            sheet.CopyRow(0,1); // Copy row A to row B; row B will be moved to row C automatically

            WriteToFile();
        }

        static void WriteToFile()
        {
            //Write the workbook’s data stream to the root directory
            FileStream file = new FileStream(@"test.xls", FileMode.Create);

            hssfWorkbook.Write(file);

            file.Close();
        }

        static void InitializeWorkbook()
        {
            using (var fs = File.OpenRead(@"Data\test.xls"))
            { 
                hssfWorkbook = new HSSFWorkbook(fs);
            }
        }
    }
}

The code above was taken from CopyRowsAndCellsInXls sample project.

Copying Sheets
Here’s what it gets to copy Sheets:

using NPOI.HSSF.UserModel;
using System;
using System.IO;
using System.Windows.Forms;

namespace CopySheet
{
    class Program
    {
        [STAThread]
        static void Main(string[] args)
        {
            // Excel worksheet merge/combine sample
            // You will be prompted to select two Excel files/workbooks. test.xls will be created that combines/merges the sheets from those two workbooks.
            // Note: This example does not check for duplicate sheet names. Your test files must have different sheet names.
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel document (*.xls)|*.xlsx";
            ofd.Title = "Select first Excel document";

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                HSSFWorkbook book1 = new HSSFWorkbook(new FileStream(ofd.FileName, FileMode.Open));

                ofd.Title = "Select second Excel document";

                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    HSSFWorkbook book2 = new HSSFWorkbook(new FileStream(ofd.FileName, FileMode.Open));

                    HSSFWorkbook merged = new HSSFWorkbook();

                    for (int i = 0; i < book1.NumberOfSheets; i++)
                    {
                        HSSFSheet sheet1 = book1.GetSheetAt(i) as HSSFSheet;

                        sheet1.CopyTo(merged, sheet1.SheetName, true, true);
                    }
                    for (int j = 0; j < book2.NumberOfSheets; j++)
                    {
                        HSSFSheet sheet2 = book2.GetSheetAt(j) as HSSFSheet;

                        sheet2.CopyTo(merged, sheet2.SheetName, true, true);
                    }

                    merged.Write(new FileStream("merged.xls", FileMode.Create, FileAccess.ReadWrite));
                }                
            }
        }        
    }
}

The code above was taken from CopySheet sample project.

To go deeper and explore all the features available for Excel 2007 you can check a handful of sample projects here (as of now 24 sample projects showcasing many available features):
https://github.com/tonyqus/npoi/tree/master/examples/xssf

Open the file NPOI.XSSF.Examples.2010.sln to have all them show up in Visual Studio’s Solution Explorer.

This is a handy piece of code that I used last year in one of my projects. I had scheduled to post it but it was just after I saw this question at StackOverflow that I decided to write about it. So here it is…

Let’s say you want a nice UI experience and to achieve that you wanna colorize/highlight a table row according to a given value present in a column of this row.
This is a simple task when we use jQuery.

I use the WebGrid that comes with ASP.NET MVC to display data on a web page using an HTML <table> element. It’s available in the namespace System.Web.Helpers. The approach described in this post is useful not only with the WebGrid but with any framework/language you use to output HTML code since the manipulation is done on the client side with jQuery.

Take this screen as an example:

Figure 1 - HTML table with last column Active to be hidden displaying values Yes and No

We want hide the last column “Active” and color the row which has a value Yes in this same column.

The above statement can be accomplished with the following code:

(function ($)
{
    hideColumnColorRow = function (column)
    {
        $('td:nth-child(' + column + '),th:nth-child( ' + column + ')').hide();

        $('tr').find('td:nth-child(' + column + '):contains(Yes)').parent().css('backgroundColor', 'LightGreen'); // Could be an hexadecimal value as #EE3B3B 
    };

})(jQuery);

The hideColumnColorRow function* takes the column number as a parameter. It hides the column <td> and its header <th> using jQuery’s supper useful nth-child selector. Then for each table row <tr> it traverses the row’s columns and looks at the value of each column using :contains selector. If it finds a value = ‘Yes’ it’ll assign a background color to the column’s parent, that in this case is the <tr> (the row) using its CSS backgroundColor property.

So, taking Figure 1 as an example, the above code can be used in an ASP.NET MVC view this way:

<script type="text/javascript">

    $(document).ready(function ()
    {
        hideColumnColorRow(5); // Hiding the 5th column and colorizing the row for which this column has a value = Yes
    });

</script>

* I’ve placed the jQuery/JavaScript function inside a file named custom.js. It resides inside the Scripts folder of the sample app available here. There’s no need to reference this script file in the view page because with the introduction of ASP.NET 4.5 we now have an all new Bundling and Minification Support for CSS and JavaScript files.

When the the app is run, this is the result:

Figure 2 - HTML table with column Active hidden and highlighted rows based on its value

This is a really interesting requirement that one can implement in no time thanks to the power of jQuery. jQuery is one of the most fascinating things when we talk about software development. Its creator “John Resig” should be awarded a Computer Science Nobel Prize if that existed. Well it could be the Turing Award.

Anyone should take a look at jQuery and start using it as early as possible. It’s a must have today. I simply love it!

Source code
I’ve put together a sample ASP.NET MVC 4 (uses NET Framework 4.5) so that you can try this out. You can run the app using the recently launched Visual Studio 11 Beta. You can download the free Visual Studio 11 Express Beta for Web here and the app code here.

Hope it helps.