C #: Microsoft.Office.Interop.Excel.Range ID field not saved using Excel worksheet

Question

Before I start with a problem, I want to motivate it. My task is to analyze the changes in the Excel sheet, but it differs from the record changes using the built-in mechanism, the changes must be detected programmatically and even if the user has disabled the record of changes or my Excel-AddIn is not installed. So I used Microsoft.Interop.Excel-Library to access the worksheet and cells inside.

Now, before the problem: to search for changes, even if the user sorted or moved the data, I wanted to have an uniqe identifier for each cell that sticks to it, even if it is moved or copied. Of course, if you copy, the identifier is twice in the sheet, and the newly added cells do not have an identifier, but this is normal. In addition, this identifier should not be visible to the user, and the user should not modify or delete it.

So, I searched the field and found a Range-Object , which can be a single cell and has different elements that can be accessed. One special field caught my attention, an ID field that looked the way I was looking.

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

as well as read how

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

This was perfect because I was able to save the line (in this case, Guid as a line, for example 123463-fc34-c43a-a391-399fc2) and read it. It also attached to the cell and was moved when the cell was moved, etc.

But, unfortunately, this ID field is not saved when the file is saved, and I do not know why. I mean, after closing and reopening the book, all identifiers disappeared.

So my question is: if there is any other element of the Range object that may contain a string (= Guid) and which is not displayed to the user. I tried the member-name and the commenting member, but both of them are visible to the user and can be easily changed.

Or is there a way to tell Excel that I want to keep the ID field also when saving the sheet?

For testing, you can create a project, add a link to Microsoft.Office.Interop.Excel-Dll and add the following code (Excel must be installed on your system). This is a single test that works with JUnit, but just removes the Assert-Command to test it without JUnit:

 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 the identifier in an Excel-Worksheet cell that is saved when the worksheet is saved or anything in this matter.

Thanks a lot in advance, Alex

Update 14.5.2011:

The Name field does not seem to be the solution to my problem for the following reasons:

Firstly, and the most serious is the fact that it seems that the name should be unique, but I wanted to give all the cells in the row the same identifier that does not work.

Secondly, access to the Name field in C # is not entirely 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 he has serious problems. If the name has already been set, it throws an exception if the name is not set, and you are trying to access it with

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

you get an exception. And you need the name Name.Name, because the first Name field is not a string, but an integer object name, inside which there is another Name-Field that contains the 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 

since it already throws an exception when accessing a nonexistent name.

Answer 1

There really is no way to save the identifier directly on a worksheet like me. Neither in the ID field (which is not saved), nor as Names (only unique names are allowed) or Comments (visible to the user).

But the book has the concept of CustomProperties, which can contain strings, and since all serializable classes can be sorted by strings, this allows the programmer to save identifiers separately and restore them when the book is loaded.

In any case, a different approach was used for my purpose, which calculates the hash values ​​of each string and instead compares the values ​​of the hash string.

Answer 2

You can try using a named range for each cell. The name will be saved. I have not tried this with interop, but it works with the good old vba. In the code below, note that names may 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 the book.

Answer 3

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