C#: ID-field of Microsoft.Office.Interop.Excel.Range is not persisted with an Excel-sheet

before I start with the problem, I want to motivate it. My task is to analyse changes in an Excel-Sheet, but different from recording changes via the in-build mechanism the changes should be detected programmatically and even if the user deactivated the recording of changes or my Excel-AddIn is not installed. Therefor I've used Microsoft.Interop.Excel-Library to access a sheet and the cells within.

Now to the problem: For finding changes, even if the user has sorted or moved the data, I wanted to have a uniqe id per cell, which sticks to it, even if moved or copied. Of course if copied, the id is twice in the sheet and new added cells have no id, but that is ok. Further on, this ID should not be visible to the user and the user should not be able to modify or delete it.

So I looked for a field and found the Range-Object which can represent a single cell and has different members which can be accessed. One special field drew my attention, the ID-field, which looked like what I was searching for.

Guid guid = Guid.NewGuid();
((Range) worksheet.Cells[rowNr, columnNr]).ID = guid.ToString();

and also be read like

Guid guid = Guid.Parse(((Range) worksheet.Cells[rowNr, columnNr]).ID);

This was perfect, because I was able to store a string (in this case a Guid as a string, like 123463-fc34-c43a-a391-399fc2) and read it. It also stuck to the cell, and was moved, when the cell was moved etc.

But unfortunately this ID-field is not persisted, when the file is saved, and I don't know why. I mean that after closing and reopening a workbook, all IDs are gone.

So my question is, if there is any other member of the Range object, that can hold a string (=Guid), and which is not visible to the user. I tried the Name-Member and the Comment-Member, but both of them are visible to the user, and can be modified easily.

Or is there a way of telling Excel, that I want to save the ID-field too, when saving the sheet?

For testing, you can create a project, add a reference to the Microsoft.Office.Interop.Excel-Dll and add the following code (you must have Excel installed on your system). It is a unit-test, that runs with JUnit, but simply remove the Assert-Command to test it without JUnit too:

using System;
using System.IO;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;开发者_开发百科
public void AddGuidAndRead() 
{
    Excel.Application excelApp = new Excel.Application();
    Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
    Worksheet worksheet = excelWorkbook.Sheets[1]; //1-based index

    Guid rowGuid1 = Guid.NewGuid();
    const string filename = "C:\\temp\\anyTemporaryFilename.xlsx";

    //Make sure, this file does not exist previously
    if (File.Exists(filename))
        File.Delete(filename);

    //Write the ID to the worksheet
    ((Range)worksheet.Cells[1, 1]).ID = rowGuid1.ToString();

    //Act (save and close the workbook)
    excelWorkbook.SaveAs(filename);
    excelWorkbook.Close();

    //Now open the workbook again
    Workbook openedWorkbook = excelApp.Workbooks.Open(filename);
    //Fetch the worksheet, where we worked previously
    Worksheet openedWorksheet = openedWorkbook.Sheets[1]; //1-based index

    //Read the ID from the cell
    string guid1 = ((Range)openedWorksheet.Cells[1, 1]).ID;

    //Cleanup
    openedWorkbook.Close(false);
    File.Delete(filename);
    excelWorkbook.Close(false, Type.Missing, Type.Missing);
    excelApp.Quit();

    //Assert - this fails!!
    Assert.AreEqual(rowGuid1.ToString(), guid1);

}

I would appreciate any idea, how to put an ID to an Excel-Worksheet-Cell that is persisted, when saving the worksheet or anything on this subject.

Many thanks in advance, Alex

Update 14.5.2011:

The Name-field seems not to be a solution to my problem for the following reasons:

First, and most serious is the fact, that it seems that the name must be unique, but I wanted to give all cells in a row the same ID, which doesn't work.

Second, to access the Name-Field in C# is not really clear to me. You can set the value with

((Range)worksheet.Cells[rowNr, columnNr]).Name = guid.ToString();
//Remark: Special dealing with guids required, 
//if they start with a number or contain special characters.

but it has serious issues. If the name was already set, it throws an exception, if no name was set, and you try to access it with

string name = ((Range)worksheet.Cells[rowNr, columnNr]).Name.Name;

you get an exception. And you need the Name.Name, because the first Name-field is not the string but a whole Name-Object, which inside has another Name-Field which contains a string.

And finally, if you want to check if it has a name or not, you cannot do something like:

if(((Range)worksheet.Cells[rowNr, columnNr]).Name == null)
    //Do something

because it already throws an exception when accessing a not existing Name-field.

Actually there is no way to persist the ID directly in the sheet as I though of it. Neither in the ID-field (which is not persisted), nor as Names (only unique names allowed) or Comments (are visible to the user).

But there is the concept of CustomProperties in a workbook, which can hold strings, and since all serializable classes can be marshalled to strings, this allows the programmer to persist the IDs separately and restore them upon loading of a workbook.

Anyway, for my purpose another approach was used, that calculates hash-values of each line and compares the line-hash-values instead.

You can try using a named range for each cell. The name will persist. I have not tried this with interop but it works with good old vba. In the code below, note that the names can be hidden from the user.

Function try()
    Dim x As Integer
    Dim y As String

    Worksheets("Sheet1").Range("a1").Name = "_firstCell"
    Range("_firstCell").Value = 9999
    Dim nm As Name
    'hide
     For Each nm In ActiveWorkbook.Names
        If Left(nm.Name, 1) = "_" Then
            nm.Visible = False
        End If
    Next
    'move the named cell
    Range("_firstCell").Cut Range("b1")
    'check the value and address
    x = Range("_firstCell").Value
    y = Range("_firstCell").Address

End Function 

From what I understand there is no logical limit to the number of named ranges in a workbook.

try using : FormatConditions activecell.FormatConditions.Add xlExpression, formula1:="test_1234" to get value for ID IDRange = mid(activecell.FormatConditions(1).formula1,2) "test_1234"