ExcelEngine not always disposing - file remaining open | WinForms Forums

Travis Chambers

Asked On April 15, 2019 02:09 PM UTC

Hi, I have several buttons that open an excel file, read or write to it, and then close/save the file and call the dispose() method of the ExcelEngine. However, Often times, I am getting an exception saying that a file cannot be opened because it is being used by another process, which is my application. It is as if the disposing of the excel engine doesn't always work.

Is there a known reason for why this may happen? Is there a way to check if the target file is already open in memory (or anywhere else) and close it programmatically if it is?

Thanks.

Reply | Reply with quote

Abirami Varadharajan [Syncfusion]

Replied On April 16, 2019 05:01 PM UTC

Hi Travis,

Thank you for contacting Syncfusion support.

We suspect that the file stream used for opening the input document is not closed after disposing the ExcelEngine. So, we suggest you to invoke the Stream.Close() method after completing the manipulation with the Excel document. Please refer the code snippet below.

Code snippet:

FileStream inputStream = new FileStream("../../Data/Test.xlsx", FileMode.Open, FileAccess.ReadWrite);

ExcelEngine engine = new ExcelEngine();

IApplication app = engine.Excel;

IWorkbook book = app.Workbooks.Open(inputStream);

book.SaveAs("../../Output/Resave.xlsx");

book.Close();

engine.Dispose();

inputStream.Close();

We have shared a sample for your reference.

Sample link : http://www.syncfusion.com/downloads/support/forum/144030/ze/XlsIOSample-1063709996

If above process does not resolve your issue, kindly modify the sample to reproduce the issue and share us along with issue reproducing input document. It will help us to analyse further on this and provide you the solution at earliest.

Regards,

Abirami

Reply | Reply with quote

Travis Chambers

Replied On April 16, 2019 08:17 PM UTC

Unfortunately, that is not my problem as I do not use a fileStream. When I try that it corrupts my file.

The below code runs in a loop, which may be a part of the problem. As you can see I first query data from the workbook via oledb and then open the same file via excelengine to import the datatable which is from the query. It then does a bunch of stuff after that but after looping through 3 times it then says it cannot open the file because it is being used by another process.

I do not understand why it works the first 3 times through the loop but not after that...

The loop begins above this line andsimply defines some variables.

string clean1 = "SELECT * FROM [SOURCE$] WHERE [Region] = '" + regionBox.Items[r].ToString() + "'";

try

{

odcSOURCE.Open();

}

catch { }

OleDbDataAdapter dAdapter1 = new OleDbDataAdapter();

OleDbCommand cmd1 = new OleDbCommand(clean1, odcSOURCE);

cmd1.Parameters.Add("?", OleDbType.Integer, 5).Value = 1234;

cmd1.Parameters.Add("?", OleDbType.BSTR, 5).Value = "asdf";

dAdapter1.SelectCommand = cmd1;

System.Data.DataTable dt1 = new System.Data.DataTable();

dAdapter1.Fill(dt1);

odc.Close();

odc.Dispose();

ExcelEngine excelEngine = new ExcelEngine();

IApplication application = excelEngine.Excel;

IWorkbook workbook = application.Workbooks.Open(exeDir + "\\Templates\\" + comboBox3.Text + "\\" + comboBox3.Text + " -Data.xlsx");

IWorksheet dataTab = workbook.Worksheets["DATA"];

try

{

dataTab.UsedRange.Clear();

}

catch { }

dataTab.ImportDataTable(dt1, true, 1, 1);

workbook.SaveAs(exeDir + "\\Templates\\" + comboBox3.Text + "\\" + comboBox3.Text + " -Data.xlsx");

workbook.Close(false);

excelEngine.Dispose();

dt1.Dispose();

GC.Collect();

The loop continues on to do various things.

Reply | Reply with quote

Abirami Varadharajan [Syncfusion]

Replied On April 17, 2019 03:41 PM UTC

Hi Travis,

We are still unable to reproduce the issue with the given code snippet. We suspect that the input document is opened by other process in your application. We have shared a sample which can be downloaded from the following link.

Sample link : http://www.syncfusion.com/downloads/support/forum/144030/ze/XlsIOSample-1063709996-1189545112

Kindly modify the sample to reproduce the issue and share us along with issue reproducing input documents. It will be helpful for us to analyse further on this and provide you the solution at earliest.

Regards,

Abirami

Reply | Reply with quote

Thread ID:	Created:	Updated:	Platform:	Replies:
144030	Apr 15,2019 02:09 PM UTC	Apr 17,2019 03:41 PM UTC	WinForms	3

ExcelEngine not always disposing - file remaining open

Thread ID:

Created:

Updated:

Platform:

Replies:

Please sign in to access our forum

CONTACT US