How I merge more excel document in one single with NPOI?

Question

I am trying to Merge more excel document into a single document using NPOI. Here is the code write:``

static void Main(string[] args)
    {
        DataTable dt = new DataTable();
        string[] files = new string[] { @"C:\Users\Ionut\source\repos\ExcelMergeDocument\ExcelMergeDocument\bin\Debug\TAMUExport\Project1\Report3Item.xls",
        @"C:\Users\Ionut\source\repos\ExcelMergeDocument\ExcelMergeDocument\bin\Debug\TAMUExport\Project2\Report3Item.xls"};
        for (int i = 0; i < files.Length; i++)
        {

            MergeData(files[i], dt);
        }
        ExportEasy(dt, finalImagePathReport3full);
    }

    public static string imagePathReport3full = @"\ResultReport3Item.xls";

    public static string finalImagePathReport3full = AssemblyDirectory + imagePathReport3full;


    public static string AssemblyDirectory
    {
        get
        {
            string codeBase = Assembly.GetExecutingAssembly().CodeBase;
            UriBuilder uri = new UriBuilder(codeBase);
            string path = Uri.UnescapeDataString(uri.Path);
            return System.IO.Path.GetDirectoryName(path);
        }
    }

    private static void MergeData(string path, DataTable dt)
    {
        XSSFWorkbook workbook = new XSSFWorkbook(path);
        XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);
        XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
        int cellCount = headerRow.LastCellNum;
        if (dt.Rows.Count == 0)
        {
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                dt.Columns.Add(column);
            }
        }
        else
        {
        }

        int rowCount = sheet.LastRowNum + 1;
        for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++)
        {
            XSSFRow row = (XSSFRow)sheet.GetRow(i);
            DataRow dataRow = dt.NewRow();
            for (int j = row.FirstCellNum; j < cellCount; j++)
            {
                if (row.GetCell(j) != null)
                    dataRow[j] = row.GetCell(j).ToString();
            }
            dt.Rows.Add(dataRow);
        }
        workbook = null;
        sheet = null;
    }
    public static void ExportEasy(DataTable dtSource, string strFileName)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
        HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
        foreach (DataColumn column in dtSource.Columns)
        {
            dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
        }
        for (int i = 0; i < dtSource.Rows.Count; i++)
        {
            dataRow = (HSSFRow)sheet.CreateRow(i + 1);
            for (int j = 0; j < dtSource.Columns.Count; j++)
            {
                dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
            }
        }
        using (MemoryStream ms = new MemoryStream())
        {
            using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
        }
    }
}

When I am run I have the following error:

ICSharpCode.SharpZipLib.Zip.ZipException: 'Cannot find central directory'

what did I suppose to do?

NPUI work only with .xlsx extension? I have the only .xls extension for the excel document. But where I run the program with .xlsx extension I have other error :

System.IO.InvalidDataException: 'Zip File is closed'

The both error appear on this line of code : XSSFWorkbook workbook = new XSSFWorkbook(path);

Answer 1

This is the modified that I do on the code:

private static void MergeData(string path, DataTable dt)
    {
        // HSSFWorkbook workbook = new HSSFWorkbook(path);
        HSSFWorkbook workbook;
        using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
        {
            workbook = new HSSFWorkbook();
        }
        HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);
        HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
        int cellCount = headerRow.LastCellNum;
        if (dt.Rows.Count == 0)
        {
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                dt.Columns.Add(column);
            }
        }
        else
        {
        }

        int rowCount = sheet.LastRowNum + 1;
        for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++)
        {
            HSSFRow row = (HSSFRow)sheet.GetRow(i);
            DataRow dataRow = dt.NewRow();
            for (int j = row.FirstCellNum; j < cellCount; j++)
            {
                if (row.GetCell(j) != null)
                    dataRow[j] = row.GetCell(j).ToString();
            }
            dt.Rows.Add(dataRow);
        }
        workbook = null;
        sheet = null;
    }

Now at this line of code : `

 HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);

he tell me System.ArgumentOutOfRangeException: 'Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index'. I read about this error and I know to initializate the index or create one, I tried that too, but no effect. What I do wrong or maybe where suppose to initializate the index? Rest of the code is the same as the previos panel, only this void I modified to Merge .xls document.