I've got a weird problem.
When I call my class from a unit test, it works great.
When I call it from a button click on my form I can't save the workbook anymore.
My class opens the workbook in its constructor. A method is opening other Excel files, reads them and puts some data in the first workbook.
When calling
_workbookWerkbestand.Save();
or
_workbookWerkbestand.Close(true);
I get the error: Can't save workbook because used by another process
I'm not using any multithreading or paralyzation mechanisms.
My unit test:
try
{
using (var service = new InventarisatieFormulierService(werkbestand))
{
foreach (var formulier in formulieren)
{
service.MergeData(formulier);
}
}
}
catch (Exception e)
{
Assert.Fail(e.Message);
}
My button click:
try
{
using (var service = new InventarisatieFormulierService(_werkbestandLocation))
{
foreach (var formulierLocation in _formulierLocations)
{
service.MergeData(formulierLocation);
}
}
ShowEnd(true);
}
catch (Exception exception)
{
ShowEnd(false);
}
My InventarisatieFormulierService (relevant parts):
public class InventarisatieFormulierService : IDisposable
{
private readonly XlsService _xlsService = new XlsService();
private readonly IWorkbook _workbookWerkbestand;
public InventarisatieFormulierService(string werkbestandLocation)
{
_workbookWerkbestand = _xlsService.OpenWorkbook(werkbestandLocation);
}
public void Dispose()
{
_workbookWerkbestand.Close(true); <---- Throws error
_xlsService?.Dispose();
}
public void MergeData(string formulier)
{
var workbookFormulier = _xlsService.OpenWorkbook(formulier);
var sheet3Werkbestand = _workbookWerkbestand.Worksheets[3];
...
// Loop through formulierWorksheet:
// Write some data to first worksheet:
sheet3Werkbestand.Range[goodRow, 2].Number = 9;
sheet3Werkbestand.Range[goodRow, 5].Text = "DGR, "
// Close workbookFormulier:
workbookFormulier.Close();
}
}
My XlsService class (relevant parts):
public class XlsService: IDisposable
{
public XlsService()
{
_excelEngine = new ExcelEngine();
_application = _excelEngine.Excel;
_application.DefaultVersion = ExcelVersion.Xlsx;
_application.EnableIncrementalFormula = true;
}
public IWorkbook OpenWorkbook(string fileName)
{
return OpenWorkbook(fileName, false);
}
private IWorkbook OpenWorkbook(string fileName, bool readOnly)
{
if (!File.Exists(fileName)) return null;
if (!_application.IsSupported(fileName))
throw new NotSupportedException("This Excel version is not supported and the file cannot be opened.",
new Exception("Working with " + fileName));
return readOnly ? _application.Workbooks.OpenReadOnly(fileName) : _application.Workbooks.Open(fileName);
}
public void Dispose()
{
_excelEngine?.Dispose();
}
}
What am I missing?