Updating existing excel file while it is open

Question

I read a lot about how to communicate from C# to Excel and saw some good references.

The thing is I'm looking for an easy way to update existing excel file while it is still open, using the most advanced way (linq for example) and not OLEDB.

This should be few lines of code describing how can I read current cell, update his value and take into consideration the file might not be exist, but if it does exist and open, it will just update the file without giving the notification the file is already exist. If the file doesn't exist it will create a new one.

SO: 1. connect to an excel file, check if it exist, if not create one
2. read from cell
3. update cell
4. do this while the excel sheet can be still open wild.

I already visited the following places:

http://social.msdn.microsoft.com/Forums/vstudio/en-US/ef11a193-54f3-407b-9374-9f5770fd9fd7/writing-to-excel-using-c

Updating an excel document programmatically

Update specific cell of excel file using oledb

I used the following code:

   if (File.Exists(@"C:\\temp\\test.xls"))
        {
            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbooks workBooks = excelApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workBook = workBooks.Open(@"C:\\temp\\test.xls");
            Microsoft.Office.Interop.Excel.Worksheet workSheet = workBook.Worksheets.get_Item(1);
            int nColumns = workSheet.UsedRange.Columns.Count;
            int nRows = workSheet.UsedRange.Rows.Count;

            for (int i = 2; i < nRows; i++)
            {
                workSheet.Columns["1","A"] = "test";
            }

            workBook.Save();
            workBook.Close();
        }

Answer 1

OK thank you all for trying to solve the issue The solution was using Excel 2011/2013 Add-In which can communicate excel as a plugin

create an application-level add-in for Microsoft Office Excel. The features that you create in this kind of solution are available to the application itself, regardless of which workbooks are open.

You can visit MSDN

Answer 2

So I use VSTO Contrib to help out with COM Interop and memory management and that's why you see .WithComCleanup().

To open up a spreadsheet:

try
{
    using (var xlApp = new Microsoft.Office.Interop.Excel.Application().WithComCleanup())
    using (var wrkbooks = xlApp.Resource.Workbooks.WithComCleanup())
    using (var wrkbook = wrkbooks.Resource.Open(filePath, false, true).WithComCleanup())
    {

If the excel file is already open, then to get around the Read-Only follow this tip:

wrkbooks.Resource.Open(filePath, false, FALSE).WithComCleanup())

Here's how I iterate though the sheets (note that some Excel sheets are ChartSheets):

foreach (object possibleSheet in xlApp.Resource.Sheets)
{
    Microsoft.Office.Interop.Excel.Worksheet aSheet = possibleSheet as Microsoft.Office.Interop.Excel.Worksheet;
    if (aSheet == null)
        continue;

Here is a quick way to get a reference to the sheet you're interested in:

activeSheet = wrkbook.Resource.Sheets[sheetToImport];

You read and write to cells just as you've identified:

for (int i = 2; i < nRows; i++)
{
  activeSheet.Columns["1","A"] = "test";
}

Here is how I close Excel:

MathematicaAPI.XlHelper.CloseExcel((Worksheet)activeSheet, (Workbook)wrkbook.Resource , (Workbooks)wrkbooks.Resource);

public static void CloseExcel(Worksheet activeSheet, Workbook wrkbook, Workbooks wrkbooks)
{
    //http://support.microsoft.com/kb/317109 -> excel just wont close for some reason
    if (activeSheet != null)
    {
        Marshal.FinalReleaseComObject(activeSheet);
        activeSheet = null;
    }
    if (wrkbook != null)
    {
        wrkbook.Saved = true;
        wrkbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlDoNotSaveChanges);
    }
    if (wrkbooks != null)
    {
        wrkbooks.Close();
    }
    GC.Collect();
    GC.WaitForPendingFinalizers();
    GC.Collect();
    GC.WaitForPendingFinalizers();
}

Sometimes Excel just wont close and you have to kill it (after trying to close it properly of course) - I dont recommend this, but if you cant track down the un-disposed memory and all else fails then...

if (xlApp != null)
{
  ExcelDataSourceHelper.GetWindowThreadProcessId(new IntPtr(xlApp.Resource.Hwnd), ref excelProcessId);
}

if (excelProcessId > 0)
{
    XlHelper.KillProcess(excelProcessId);
}

public static void KillProcess(int excelProcessId)
{
    if (excelProcessId > 0)
    {
        System.Diagnostics.Process ExcelProc = null;
        try
        {
            ExcelProc = System.Diagnostics.Process.GetProcessById(excelProcessId);
            if (ExcelProc != null)
            {
                ExcelProc.Kill();
            }
        }
        catch
        { }
    }
}

Note: I reduce the chances of needing to kill Excel by using VSTO Contrib with Using's.