Empty Worksheet - Microsoft.Office.Interop.Excel

Question

I've been struggling with this for a while now. I am using Microsoft.Office.Interop.Excel and I try to do the following:

I have a template that I made in Excel with 4 Worksheets. When I try to fill the worksheets there is always one worksheet that is empty, even though when debugging I can see that the right Worksheet has been selected and each Range object is given a value without any errors.

In this method there are 2 iterations. If I comment out one of the iterations the other iteration works perfectly. So basically I always end up with one blank worksheet.

Any Idea where I go wrong??

Thank you in advance!

public void generateReport()
    {
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        Excel.Range range;
        int row = 3;
        xlApp = new Excel.Application();
        if (xlApp == null)
        {
            MessageBox.Show("Kon Excel niet starten, kijk uw softwareinstelling na !");
            return;
        }

        string workbookPath = Path.Combine(Environment.CurrentDirectory, @"..\report.xlsx");
        xlWorkBook = xlApp.Workbooks.Open(workbookPath,
            0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
            true, false, 0, true, false, false);


        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        ArrayList dossiers = DossierDao.Instance.getDossiers("SELECT * FROM dossier ORDER BY referentie;");
        foreach (Object o in dossiers)
        {
            File f = (File)o;
            range = xlWorkSheet.get_Range("A" + row);
            range.Value = d.Reference;
            range = xlWorkSheet.get_Range("B" + row);
            range.Value = d.Info;
            row++;
        }

        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(3);
        ArrayList facturen = FactuurDao.Instance.getAllFacturen();
        foreach (Object o in facturen)
        {
            Invoice i = (Invoice)o;
            range = xlWorkSheet.get_Range("A" + row);
            range.Value = f.InvoiceNumber;
            range = xlWorkSheet.get_Range("B" + row);
            range.Value = f.Amount;
            row++;
        }
        try
        {
            xlApp.Visible = true;
        }
        catch

           //...
        }

        releaseObject(xlApp);
        releaseObject(xlWorkBook);
        releaseObject(xlWorkSheet);
        releaseObject(xlWorkSheet);
    }

    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show("Unable to release the Object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }
}

Answer 1

You're not reinitializing row between processing the dossiers and the facturen. So, if you have 50 dossiers, on rows 3 through 52, then the code will start putting the facturen on row 53 of the next worksheet.