Copying Excel worksheets with NPOI CopySheet; copy is always blank

Question

I'm trying to create an application that will merge the excel spreadsheets in a given directory into a single, tabbed Excel workbook. I only have access to VS Community 2015, so VSTO is out of the question; I'm using Interop and NPOI 2.1.3.1 instead.

Here's my code:

files = Directory.GetFiles(sourcePath);
XSSFWorkbook test = new XSSFWorkbook();

try
{
    string fPath = files[0];
    FileStream fs2 = new FileStream(fPath, FileMode.Open, FileAccess.ReadWrite);
    XSSFWorkbook myBook = new XSSFWorkbook(fs2);
    test.CreateSheet(myBook.GetSheetName(0));
    var sheet1 = myBook.GetSheetAt(0).CopySheet(test.GetSheetName(0));

    test.Write(new FileStream("testFile.xlsx", FileMode.Create, FileAccess.ReadWrite));
}
catch {  }

I'm not trying to do anything fancy, here, just get the first file returned by the GetFiles function, copy everything on the first worksheet, and create a new worksheet in new workbook "test" with what was copied. I know the source file is being found, because sheet1 of the output file "testFile.xlsx" is getting the unique sheet name of the source worksheet -- it isn't "Sheet1". However, the rest of the sheet comes up blank, and I don't know why.

I'm using this against Excel 2016, if that makes a difference.

Why are you using both Interop and NPOI? – mason Jun 13 '17 at 14:13 — mason, Jun 13 '17 at 14:13

krlzlx · Answer 1 · 2017-06-20T14:45:18.897

Please try the two different solutions: one for Interop (xlsx file format) and the other for NPOI (xls file format). These solutions get the excel files in a folder, read the excel files and copy the sheets to a new excel file.

Using Interop.Excel:

// using Microsoft.Office.Interop.Excel;

Application app = new Application();
app.Visible = false;
app.DisplayAlerts = false;

string[] files = Directory.GetFiles(@"c:\temp\excel");

foreach (string file in files)
{
    app.Workbooks.Add(file);
}

for (int i = 2; i <= app.Workbooks.Count; i++)
{
    for (int j = 1; j <= app.Workbooks[i].Worksheets.Count; j++)
    {
        Worksheet ws = app.Workbooks[i].Worksheets[j];
        ws.Copy(app.Workbooks[1].Worksheets[1]);
    }
}

app.Workbooks[1].SaveCopyAs(@"c:\temp\excel\output\testFile.xlsx");
app.Quit();

Using NPOI:

// using NPOI.HSSF.UserModel;

string[] files = Directory.GetFiles(@"c:\temp\excel");
HSSFWorkbook workbookMerged = new HSSFWorkbook();

foreach (string file in files)
{
    HSSFWorkbook workbook;

    using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
    {
        workbook = new HSSFWorkbook(fs);

        for (int i = 0; i < workbook.NumberOfSheets; i++)
        {
            ((HSSFSheet)workbook.GetSheetAt(i)).CopyTo(workbookMerged, workbook.GetSheetName(i), true, true);
        }
    }
}

using (FileStream fs = new FileStream(@"c:\temp\excel\output\testFile.xls", FileMode.Append, FileAccess.Write))
{
    workbookMerged.Write(fs);
}

Note that for the NPOI solution, I converted my xlsx files to xls to use HSSF instead of XSSF, as there's not CopyTo method available for XSSFSheet. This method is only available for HSSFSheet, as of the latest NPOI version 2.3.0.

@krizix. Can I use your solution to convert xls to xlsx? Or I am overlooking the obvious? — Mark, Dec 22 '21 at 17:12
That was not the purpose of my answer. It was to copy a sheet. I converted the Excel file using Excel, not by coding. Maybe you can find an answer with this [question](https://stackoverflow.com/q/29522173/1351076). @Mark — krlzlx, Dec 23 '21 at 12:39

Copying Excel worksheets with NPOI CopySheet; copy is always blank

1 Answers1