Copying Data from one Sheet to Another using NPOI
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:UsersMyDoc.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
}
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:TempMyDoc.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:TempMyDoc.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);