c# - What is the best way to store XML data in an Excel file

Question

I am looking for a way to store XML data in an Excel file. The data should be completely hidden to the user, it should not be in a cell or comment, even hidden. Also, the data should be preserved when the user opens and then saves the file with Excel. I am not looking for a way to map cells to external XML data. The XML data should be inside the xlsx file.

The data will be entered using a C# tool, not Excel itself.

Answer 1

The .xlsx file is actually just a compression archive (zip file) so if you truly want to add a hidden file then you could just add a .xml file to the archive. That way Excel wouldn't even know it was there.

Rename a .xlsx file to .zip, extract it, add your file, then select the contents of the .zip file and re-archive them. Rename to .xlsx and you'll have your hidden .xml file inside there. (NOTE: Do not zip the top-level folder, only the contents)

You can do this in C# using a zip library like SharpZipLib: http://www.sharpdevelop.net/OpenSource/SharpZipLib/

UPDATE: This "hidden" file will not be preserved if the user saves the file from within Excel. The best idea I can come up with for that scenario is to invoke the code as part of a VBA macro embedded in the sheet.

This link contains useful information about manipulating the parts of an Office package: http://msdn.microsoft.com/en-us/library/aa982683.aspx

The core.xml and app.xml (in the docProps folder) contain the document properties and might be a good location to store additional xml information.