With Office 2007, developers are able to access and modify Office 2007 documents by using the Open XML Formats as an alternative to Office Interop. But even a few years after the release of Office 2007, quite a few organizations still use older versions of Microsoft Office, so we still have to use Office Interop to manipulate those documents. If you need more help on getting started, check this official Microsoft article on using PIA for Excel.
Side note: you can only develop your solution against one Office version, which has to be installed on your system together with the correct version of the Primary Interop Assemblies. Later versions can’t be installed on the same development machine, because the latest version will be used and thus break your build. For most of us developers this means we’ll have virtual machines with previous versions of Microsoft Office for these solutions. Office plug-ins can be created to work on both Office 2003 and Office 2007 with some tweaking, but this is not supported by Microsoft. Multiple versions of Microsoft Office can be used on the client machines if you take care of the backward compatibility .
It’s sad to notice that after so many years of using Microsoft Interop, your search for a correct implementation of a simple ‘open-edit-close’ operation brings up plenty of dirty tricks and workarounds to ‘make it work’ instead of the clean solution you expect. It’s mainly closing the process of the Office application that seems to be the largest issue. And be honest, the last thing you want to do to close your process is something like:
Process[] pProcess;
pProcess = System.Diagnostics.Process.GetProcessesByName("Excel");
pProcess[0].Kill();
You might kill the incorrect process, which is clearly not the expected behaviour.
The Primary Interop Assemblies not only offer you a way to open and edit your document, but also to close the application process when you’re finished. The caveat (and the major problem so many stumble upon and force them to use dirty tricks) is that you have to keep track of all references and release them all one by one correctly. If you forget to release one reference or do it in an incorrect order, you’ll end up with an open process.
To be sure I always release the COM references (done with Marshal.ReleaseComObject method) and close my process correctly, I implement the IDisposable interface. This brings me to this possible implementation:
// Implement IDisposable interface to release the COM references on the disposal of the object
public sealed class Excel2003Connector : IDisposable
{
//keep track of all your references
private Application m_excelApplication;
private Workbooks m_workbooks;
private _Workbook m_workbook;
private _Worksheet m_worksheet;
private Boolean m_disposed;
/// <summary>
/// Dispose of com-objects
/// </summary>
~Excel2003Connector()
{
Dispose(false);
}
/// <summary>
/// Opens the file and loads the workbook + sheet
/// </summary>
/// <param name="filename"></param>
public void LoadFile(String filename)
{
// start up your Excel process
m_excelApplication = new Application();
if (m_excelApplication == null)
{
throw new Exception("Can't start Excel");
}
// run hidden, disable user interaction
m_excelApplication.Visible = false;
m_excelApplication.UserControl = false;
m_excelApplication.DisplayAlerts = false;
// load the workbook, be sure to keep reference to the Workbooks collection
// to release it later on, this is quite often forgotten
m_workbooks = m_excelApplication.Workbooks;
m_workbook = m_workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, XlPlatform.xlWindows,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
// get the active worksheet
m_worksheet = (_Worksheet)m_workbook.ActiveSheet;
}
/// <summary>
/// Closes the workbook and saves the changes
/// Other overloads can also be implemented
/// </summary>
public void CloseWorkbookAndSaveChanges()
{
if (m_workbook != null)
{
m_workbook.Close(true, Type.Missing, Type.Missing);
}
}
/// <summary>
/// Dispose object
/// </summary>
public void Dispose()
{
Dispose(true);
// This object will be cleaned up by the Dispose method.
// Therefore, you should call GC.SupressFinalize to
// take this object off the finalization queue
// and prevent finalization code for this object
// from executing a second time.
GC.SuppressFinalize(this);
}
// Correct implementation of IDisposable
// http://msdn.microsoft.com/en-us/library/system.idisposable.aspx
// Dispose(bool disposing) executes in two distinct scenarios.
// If disposing equals true, the method has been called directly
// or indirectly by a user's code. Managed and unmanaged resources
// can be disposed.
// If disposing equals false, the method has been called by the
// runtime from inside the finalizer and you should not reference
// other objects. Only unmanaged resources can be disposed.
private void Dispose(Boolean disposing)
{
// Check to see if Dispose has already been called.
if (!m_disposed)
{
// If disposing equals true, dispose all managed
// and unmanaged resources.
if (disposing)
{
// Dispose managed resources if needed
}
// Call the appropriate methods to clean up
// unmanaged resources here.
// If disposing is false,
// only the following code is executed.
try
{
if (m_worksheet != null)
{
Marshal.ReleaseComObject(m_worksheet);
m_worksheet = null;
}
if (m_workbook != null)
{
Marshal.ReleaseComObject(m_workbook);
m_workbook = null;
}
if (m_workbooks != null)
{
Marshal.ReleaseComObject(m_workbooks);
m_workbooks = null;
}
if (m_excelApplication != null)
{
// Quit the process
m_excelApplication.Quit();
// finally release the reference
Marshal.ReleaseComObject(m_excelApplication);
m_excelApplication = null;
}
GC.Collect();
}
catch (Exception ex)
{
throw;
}
// Note disposing has been done.
m_disposed = true;
}
// add other methods to manipulate your document, save under another location or discard the changes
}
}
Works fine for me. I never have a process left open and I don’t need to kill the process in code or cheat with several calls to the garbage collector and waiting for pending finalizers.
Other links: Office 2003 Redistributable PIAs.