ExcelEngine not always disposing - file remaining open

[Travis Chambers]

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.

[Abirami Varadharajan]

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 

[Travis Chambers]

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();

GC.Collect();

The loop continues on to do various things.

[Abirami Varadharajan]

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