NPOI 2.0 - Cell, Row, Sheet copying and Workbook merging

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.