I don't think you can force Excel to round trip unknown attributes but you can add extension elements using ExtensionLists and Extensions. Excel will roundtrip these elements and are designed (as far as I can make out) for storing application specific data just as you are after.
There doesn't seem to be too much documentation around that I can find but Part 3 of the ECMA-376 spec mentions extensions.
The following code will create a sheet with a value in cell A1 and an ExtensionList with one Extension in it as a child of that cell:
public static void CreateSpreadsheetWorkbook(string filepath)
{
if (File.Exists(filepath))
File.Delete(filepath);
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
SheetData sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Sheet1"
};
sheets.Append(sheet);
Row row = new Row()
{
RowIndex = 1U
};
Cell cell = new Cell()
{
CellReference = "A1",
CellValue = new CellValue("A Test"),
DataType = CellValues.String
};
ExtensionList extensions = new ExtensionList();
Extension extension = new Extension()
{
Uri = "Testing1234"
};
extensions.AppendChild(extension);
extension.AddNamespaceDeclaration("ns", "http://tempuri/someUrl");
cell.AppendChild(extensions);
row.Append(cell);
sheetData.Append(row);
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
}
The following will read the value back again, even if the file has been round tripped through Excel.
public static void ReadSheet(string filename, string sheetName)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
//get the correct sheet
Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).First();
if (sheet != null)
{
WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id) as WorksheetPart;
foreach (Cell cell in worksheetPart.Worksheet.Descendants<Cell>())
{
ExtensionList extensions = cell.GetFirstChild<ExtensionList>();
if (extensions != null)
{
Extension extension = extensions.GetFirstChild<Extension>();
if (extension != null)
{
Console.WriteLine("Cell {0} has value {1}", cell.CellReference, extension.Uri);
}
}
}
}
}
}
The output from which is
Cell A1 has value Testing1234
As for your side question:
What are the attributes for if Excel does not preserve them?
I'm not too sure - the only time I've used the OpenXmlAttribute class is when I've used a SAX approach to write a document. In that case you need to write the attributes explicitly along with the elements. For example:
List<OpenXmlAttribute> oxa = new List<OpenXmlAttribute>();
//cell reference attribute
oxa.Add(new OpenXmlAttribute("r", "", "A1"));
//cell type attribute
oxa.Add(new OpenXmlAttribute("t", "", "str"));
//write the start element of a cell with the above attributes
oxw.WriteStartElement(new Cell(), oxa);
//write a value to the cell
oxw.WriteElement(new CellValue("Test"));
//write the end element
oxw.WriteEndElement();
My answer here has a full example of using a SAX approach.
