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

I don't get it. Where are you stuck? You've got your TO-DO list and it sounds like you can do it, so what is your question? or did you want someone here to do your work.. Also just use the Object Model, dont bother using linq using the Open XML would be a much better choice. — Jeremy Thompson, Sep 10 '13 at 06:24
No i don't know how to do it i would like to get a simple example of few lines of code describing my TO-DO list — Liad Livnat, Sep 10 '13 at 06:30
Liad, what have you tried? The question sounds like you're asking us **to do your job** not `give us a hand with this problem`. There is a plethora of resources on Excel VSTO, I suggest you give it a go before asking questions here. Questions asking for code must demonstrate a minimal understanding of the problem being solved. Include attempted solutions, why they didn't work, and the expected results. — Jeremy Thompson, Sep 10 '13 at 06:37
@JeremyThompson i come across few examples but none of them helped me, i can put the code i've used but it didn't work, ok let me edit the question — Liad Livnat, Sep 10 '13 at 06:38
Oh you've got half the solution already - apologies - this is what we need to help you. — Jeremy Thompson, Sep 10 '13 at 06:39
Sure, but this lines are not working and causing excel to popup a window asking to recreate the excel file — Liad Livnat, Sep 10 '13 at 06:44
To overcome that simply pass in a 3rd parameter of FALSE to the `workBooks.Open(@"C:\\temp\\test.xls", false, false);` method. — Jeremy Thompson, Sep 10 '13 at 06:54
@JeremyThompson the think is is still want to replace the existing file and doesn't update it when the excel is still open i need to open and close it to see the changes — Liad Livnat, Sep 10 '13 at 07:13

Jeremy Thompson · Answer 1 · 2013-09-10T08:10:22.887

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.

Jeremy thank you i'll try it soon, but can't i leave the excel open and just update it without open and close it? or not to say kill to process? — Liad Livnat, Sep 10 '13 at 07:08
in addition the link you gave for VSTO Contrib doesn't exist — Liad Livnat, Sep 10 '13 at 07:11

score 0 · Answer 2 · answered Sep 10 '13 at 08:29

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

Updating existing excel file while it is open

2 Answers2