CODEWITHSUNDEEP
c#.netvb.netexcelexcel-interop
Will Marcouiller
Will Marcouiller

Reputation: 24132

Excel Interop: Quitting the Excel application instance makes my tests fail?

I want to encapsulate the use of Excel Interop to make it easier to use somehow in a reuseable library.

So far, I have written some tests that work good altogether, except that between each test, I force Excel to quite so that it does have handle on the test file no more in order to be able to delete.

Once I quit the application instance between after each of my tests, Excel seems to be no longer responding, causing Windows to display

"Excel has stopped working and is looking for a solution"

message. This message lasts a few seconds, meanwhile Excel is closing, causing the hang on the file to occur and my tests to throw an exception like

"Cannot access file because it is being used by another process..."

message. Otherwise, each of my tests run individually just fine!

Any clue on how to solve this issue?

Do I overuse the ApplicationClass.Quit() method?

Quitting Excel only once the testing is through with my tests causes the files created for the tests purpose not to be deleteable.

Thanks! =)

Upvotes: 3

Views: 2208

Answers (2)

The Muffin Man
The Muffin Man

Reputation: 20004

I was having the same "Cannot access file because it is being used by another process..." issue.

I was saving the worksheet, what you need to do is save the work book. Here's a full example.

private void TestCreateExcel(IEnumerable<Sales1> data)
    {
        var excelApp = new Excel.Application();
        var workBook = excelApp.Workbooks.Add();
        Excel._Worksheet workSheet = excelApp.ActiveSheet;

        workSheet.Cells[1, "A"] = "Title";
        workSheet.Cells[1, "B"] = "Author(s)";
        workSheet.Cells[1, "C"] = "Release Date";
        workSheet.Cells[1, "D"] = "Total Books Sold";
        workSheet.Cells[1, "E"] = "Last 30 Days Sales";
        workSheet.Cells[1, "F"] = "Average Sales Per Month";
        workSheet.Cells[1, "G"] = "Starting Advance";
        workSheet.Cells[1, "H"] = "Current Advance";

        int index = 1; //Keep track of the which row we're writing to.
        foreach (var n in data)
        {
            index++;
            workSheet.Cells[index, "A"] = n.Title;
            workSheet.Cells[index, "B"] = n.Author;
            workSheet.Cells[index, "C"] = n.ReleaseDate.ToShortDateString();
            workSheet.Cells[index, "D"] = n.TotalBooksSold;
            workSheet.Cells[index, "E"] = n.Last30DaysSales;
            workSheet.Cells[index, "F"] = n.AverageSalesPerMonth;
            workSheet.Cells[index, "G"] = n.StartingAdvance;
            workSheet.Cells[index, "H"] = n.CurrentAdvance;
        }

        workBook.SaveAs(Server.MapPath(filePath + fileName));
        excelApp.Quit();
    }

Upvotes: 1

barrowc
barrowc

Reputation: 10679

If you've made changes to the Workbook then could it be the "Do you wish to save changes?" dialog which is holding things up. You could try setting the _Workbook.Saved property to True prior to quitting the application. See here for details

Upvotes: 2

Related Questions