Copying Data from one Sheet to Another using NPOI

Question

I'm new to C# and Excel and I'm trying to copy data from one sheet (that is full of data) to a new one that is empty. I'm using NPOI and I've figured out a few things, but I have problems when adding the data.

The second sheet is empty, so all the cells are null (please correct me if I'm wrong). When I try to copy data from the first sheet to the second, nothing happens: the cells are not populated.

using (FileStream fs = new FileStream(@"C:\Users\MyDoc.xlsx", FileMode.Open, FileAccess.ReadWrite))
{
    XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs);

    //Load the sheets needed
    var sheettoget = templateWorkbook.GetSheet("Sheet1");
    var sheettoadd = templateWorkbook.GetSheet("Sheet2");

    XSSFRow row =  (XSSFRow)sheettoadd.CreateRow(0);
    var cell = row.CreateCell(0);             
    var data = sheettoget.GetRow(0).GetCell(4).StringCellValue.ToString();
    cell.SetCellValue(data); //the cell is not populated
}

score 2 · Accepted Answer · answered Oct 07 '16 at 04:18

In your code it doesn't look like you ever write the changes back to the file. If you're opening the file in Excel and you don't see the changes, that would explain why. I think you want to be doing something like this:

// Read the file into a workbook
XSSFWorkbook templateWorkbook;
using (FileStream fs = new FileStream(@"C:\Temp\MyDoc.xlsx", FileMode.Open, FileAccess.Read))
{
    templateWorkbook = new XSSFWorkbook(fs);
}

// Load the sheets needed (if the new sheet doesn't already exist, create it)
var sheettoget = templateWorkbook.GetSheet("Sheet1");
var sheettoadd = templateWorkbook.GetSheet("Sheet2");
if (sheettoadd == null)
{
    sheettoadd = templateWorkbook.CreateSheet("Sheet2");
}

// Make changes
XSSFRow row = (XSSFRow)sheettoadd.CreateRow(0);
var cell = row.CreateCell(0);
var data = sheettoget.GetRow(0).GetCell(4).StringCellValue.ToString();
cell.SetCellValue(data);

// Write the changes back to the file
using (FileStream fs = new FileStream(@"C:\Temp\MyDoc.xlsx", FileMode.Create, FileAccess.Write))
{
    templateWorkbook.Write(fs);
}

If you're trying to copy the entire sheet, you don't need to do it cell by cell. NPOI has a CopySheet method you can use to do it:

sheettoget.CopySheet("Sheet2", true);

Hey, i've made the changes you suggested, but the first sheet is completely deleted. Is there a way to prevent this from happening? — Work_God_Damnit, Oct 07 '16 at 07:10
Hmm, that doesn't happen when I try it using the above code. Are you sure you're not accidentally overwriting Sheet1? The target of CreateSheet (or CopySheet) should be "Sheet2". — Brian Rogers, Oct 07 '16 at 07:18
ops, I checked and instead of writing in sheet2 I was writing in sheet1. Sorry. Thank you very much for the help :D — Work_God_Damnit, Oct 07 '16 at 07:42

Copying Data from one Sheet to Another using NPOI

1 Answers1