Saving Excel Spreadsheet using Interop C#

Question

static void Main()
{
  Application excelapp = new Application();
  Workbook book = excelapp.Workbooks.Open(@"C:\HWYFAB.xlsx",
    0, false, 5, "", "", false, XlPlatform.xlWindows , "",
    true, false, 0, true, false, false);

  Worksheet sheet = (Worksheet)book.Sheets[1];

  Range cell = (Range)sheet.Cells[3, 2];
  Console.WriteLine(cell.Text);
  cell.ClearContents();
  book.Close(true, "HWYFAB.xlsx", false);
  excelapp.Quit();
}

This program runs and exits as expected. It does print the correct value that's in cell B3 to the console. When closing it asks if I want to replace the existing file. I click yes. When I open the spreadsheet in Excel, the value is still in cell B3 despite the cell.ClearContents().

Any thoughts?

Answer 1

Your call to cell.ClearContents() will clear formulas and value constants from the cell. This should absolutely be working. You can confirm this after your call to cell.ClearContents() by testing if cell.Value2 == null, which should be true.

I believe that the problem with your code is in the call to book.Close(true, "HWYFAB.xlsx", false). The problem is that you are explicitly passing in the name of your workbook, but you are leaving off the path. (Note that when you open the workbook you are including the full path, including the "C:\" directory.) By leaving off the full path to the workbook when you save, you are saving to the current directory, which could be "My Documents" or anywhere else.

If you wish to save the workbook in-place, at its original location, then you should pass in Type.Missing for the Filename parameter. For example:

book.Close(true, Type.Missing, false);

This idea is untested, but I'm fairly confident that this should work for you. Give it a try...

-- Mike