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… Smile

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.