-1

I am writing this particular code in C# to write to an excel file.

public partial class WriteExcelForm : Form
{
    public WriteExcelForm()
    {
        InitializeComponent();
    }

    private void writeExcelButton_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
        if (xlApp == null)
        {
            MessageBox.Show("Excel is not installed!!!");
            return;
        }

        Excel.Workbooks xlWorkBooks = xlApp.Workbooks;
        Excel.Workbook xlWorkBook = xlWorkBooks.Add(Type.Missing);
        Excel.Worksheet xlWorkSheet = xlWorkBook.ActiveSheet;
        xlWorkSheet.Name = "sample";

        Excel.Range range1 = (Excel.Range)xlWorkSheet.Cells[1, 1];
        range1.Value = "dhiraj";

        Excel.Range range2 = xlWorkSheet.Range["A2"];
        range2.Value = "dhiraj";

        xlWorkBook.SaveAs("C:\\output.xlsx");

        //Properly closing the excel app
        GC.Collect();
        GC.WaitForPendingFinalizers();

        xlWorkBook.Close(false, Type.Missing, Type.Missing);
        xlApp.Quit();

        Marshal.FinalReleaseComObject(range1);
        Marshal.FinalReleaseComObject(range2);
        Marshal.FinalReleaseComObject(xlWorkSheet);
        Marshal.FinalReleaseComObject(xlWorkBook);
        Marshal.FinalReleaseComObject(xlWorkBooks);
        Marshal.FinalReleaseComObject(xlApp);
    }
}

If I run this code, the excel.exe does not quit, but keeps on hanging around in the background.

However, if I comment out this particular line

        Excel.Range range1 = (Excel.Range)xlWorkSheet.Cells[1, 1];
        range1.Value = "dhiraj";

the excel.exe quits elegantly.

What am I missing here?

EDIT: I have solved my issue. Posting my findings as answer.

P.S: Do not know why I was down voted, I did research a lot before posting this question.

Community
  • 1
  • 1
dhiraj suvarna
  • 505
  • 7
  • 20
  • @YowE3K: You mean without the explicit Type Casting? I did tried that, but i end up with the same result. – dhiraj suvarna Dec 03 '17 at 08:27
  • Check this link: http://www.siddharthrout.com/2012/08/06/vb-net-two-dot-rule-when-working-with-office-applications-2/ - and eliminate the double dots. Also suggest you avoid using the brackets notation to do type conversion. – Robin Mackenzie Dec 03 '17 at 08:44
  • And see this: https://stackoverflow.com/questions/29067714/vsto-manipulating-com-objects-one-dot-good-two-dots-bad - for a counter point. – Robin Mackenzie Dec 03 '17 at 08:46
  • 1
    @RobinMackenzie: I have gone through those links, I hope you don't see any "double dots" in the code that I have shared here. – dhiraj suvarna Dec 03 '17 at 09:11
  • Maybe it's the cast using brackets? – Robin Mackenzie Dec 03 '17 at 09:29
  • 1
    Every `[]` is a hidden period. https://ausdotnet.wordpress.com/2008/06/04/com-interop-principle-3-fear-the-hidden-period/ – mjwills Dec 03 '17 at 09:29
  • Are you sure that **all** workbooks have been closed (including workbooks that have been opened from outside your code)? (You can enforce that by looping through the workbooks object) Some of your variables (for instance **range1**) are still pointing to the closed workbook. It might help if you clear them before trying to quit the excel application. – Peter Pesch Dec 03 '17 at 12:34
  • Not sure if this will help or not, but check this out: https://stackoverflow.com/questions/47418452/is-there-a-way-to-fill-out-an-excel-workbook-without-involving-excel-itself-or. I think the reason it works when you omit that line of code is because you haven't altered the workbook, which prevents the "Save document?" dialog. – Hambone Dec 04 '17 at 02:41

2 Answers2

0

So, I continued my research after asking the question, and I found this particular link How do I properly clean up Excel interop objects?

Now in this particular link, three answers which when put together helped me out.

First answer is this particular one https://stackoverflow.com/a/158752/2241802 It says to avoid using "Never use two dots with COM objects".

Second answer is this one https://stackoverflow.com/a/159419/2241802 This answer refers to how to use excel object practically and it is very important to follow this, since I will not be the only developer working on my piece of code.

The third answer https://stackoverflow.com/a/1893653/2241802 talks about the how Garbage Collector behavior differs in Release mode and Debug mode.

So that was my problem, when i ran the code pasted in the question in Release mode, it worked fine and the excel.exe quit gracefully.

However, to make it work in the Debug mode I took the suggestion of the Thrid Answer link above and created a function which implemented the excel writing stuff.

dhiraj suvarna
  • 505
  • 7
  • 20
-1

Find below code 

protected void Button3_Click(object sender, EventArgs e)
    {
        int randomvalue = new Random().Next(10);
        try
        {
            filename = Server.MapPath("~/Reports/Tro_Reports" + randomvalue + ".xlsx");
            using (var getReportCollection = new DataSet1())
            {
                using (var tableCollection = getReportCollection.Tables["SheetNames"])
                {
                    var excelApplication = new Microsoft.Office.Interop.Excel.Application();

                    try
                    {
                        var wb = excelApplication.Workbooks.Add();

                        var collection = new Microsoft.Office.Interop.Excel.Worksheet[20];

                        for (var i = 0; i < tableCollection.Columns.Count; i++)
                        {
                            collection[i] = wb.Worksheets.Add();
                            collection[i].Name = tableCollection.Columns[i].ToString();
                        }
                        var thisWorksheet = collection[2];
                        var thisRange = thisWorksheet.Range["A1"];
                        thisRange.Value = "Event Summary Report";

                        wb.SaveAs(filename);
                        wb.Close();
                    }
                    finally
                    {
                        Marshal.ReleaseComObject(excelApplication);
                    }
                }
            }
        }
        catch (ExternalException ex)
        {

        }
    }

The above code snippet will work fine to close your excel sheet.

maruthi
  • 41
  • 3