StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POC#: ID-field of Microsoft.Office.Interop.Excel.Range is not persisted with an Excel-sheet
text
Body
copied!<p>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.</p> <p>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.</p> <p>So I looked for a field and found the <a href="http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range_members.aspx">Range-Object</a> 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.</p> <pre><code>Guid guid = Guid.NewGuid(); ((Range) worksheet.Cells[rowNr, columnNr]).ID = guid.ToString(); </code></pre> <p>and also be read like</p> <pre><code>Guid guid = Guid.Parse(((Range) worksheet.Cells[rowNr, columnNr]).ID); </code></pre> <p>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.</p> <p>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. </p> <p>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.</p> <p>Or is there a way of telling Excel, that I want to save the ID-field too, when saving the sheet?</p> <p>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:</p> <pre><code>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); } </code></pre> <p>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.</p> <p>Many thanks in advance, Alex</p> <p><strong>Update 14.5.2011:</strong></p> <p>The Name-field seems not to be a solution to my problem for the following reasons:</p> <p>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. </p> <p>Second, to access the Name-Field in C# is not really clear to me. You can set the value with</p> <pre><code>((Range)worksheet.Cells[rowNr, columnNr]).Name = guid.ToString(); //Remark: Special dealing with guids required, //if they start with a number or contain special characters. </code></pre> <p>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 </p> <pre><code>string name = ((Range)worksheet.Cells[rowNr, columnNr]).Name.Name; </code></pre> <p>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.</p> <p>And finally, if you want to check if it has a name or not, you cannot do something like:</p> <pre><code>if(((Range)worksheet.Cells[rowNr, columnNr]).Name == null) //Do something </code></pre> <p>because it already throws an exception when accessing a not existing Name-field.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload