NPOI c# copy worksheet in another workbook xlsx

Question

I try to copy a worksheet in another workbook xlsx with NPOI but it fails. There is an example for xls files here

I don't find the equivalent XSSF functions. I always get a corrupted file.

All examples use HSSF, can you help me?

Here is my code does not work

List<string> fileToMerge = new List<string>();
fileToMerge.Add("file1.xlsx");
fileToMerge.Add("file2.xlsx");

MergeFiles(fileToMerge, "fileMerged.xlsx");


private void MergeFiles(List<string> filenames, string outFilename)
{
    XSSFWorkbook merged = new XSSFWorkbook();

    foreach (string filename in filenames)
    {
        XSSFWorkbook workbook = GetWorkbook(filename);

        for (int i = 0; i < workbook.NumberOfSheets; i++)
        {
            XSSFSheet sheet1 = workbook.GetSheetAt(i) as XSSFSheet;

            merged.Add(sheet1.CopySheet(sheet1.SheetName, true));
        }
    }

    SaveWorkbook(merged, outFilename);
}

private XSSFWorkbook GetWorkbook(string filename)
{
    XSSFWorkbook workbook = new XSSFWorkbook();
    string path = Path.Combine(Environment.CurrentDirectory, filename);

    if (!File.Exists(path))
    {
        Console.WriteLine("This file does not exist: " + path);
        Environment.Exit(7);
    }
    else
    {
        FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
        workbook = new XSSFWorkbook(fs);
        fs.Close();
        fs.Dispose();
    }

    return workbook;
}

private void SaveWorkbook(XSSFWorkbook workbook, string fileName)
{
    // metadata
    workbook.GetProperties().CoreProperties.Creator = "My Company " + DateTime.Now.Year.ToString();
    workbook.GetProperties().ExtendedProperties.props.AddNewProperties().Company = "My Company";

    FileStream sw = File.Create(fileName);
    workbook.Write(sw);
    sw.Close();
    sw.Dispose();
}

Thank you in advance.

possible duplicate of [Copy sheet from one workbook to another using NPOI in C#](https://stackoverflow.com/questions/31916943/copy-sheet-from-one-workbook-to-another-using-npoi-in-c-sharp#comment51882313_31916943) — Stefan Anghel, Aug 13 '15 at 14:00

score 0 · Answer 1 · answered Jul 19 '17 at 21:13

public FileResult DownloadFile()
{
    // code to create workbook 
    using (var exportData = new MemoryStream())
    {
        workbook.Write(exportData);
        string saveAsFileName = string.Format("MembershipExport-{0:d}.xls", DateTime.Now).Replace("/", "-");

        byte[] bytes = exportData.ToArray();
        return File(bytes, "application/vnd.ms-excel", saveAsFileName);
    }
}

Taken from post how to download a xls file generated by NPOI in ASP MVC

NPOI c# copy worksheet in another workbook xlsx

1 Answers1