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.

Answer 2

I had the same issue and here is my code to deal with it, using Microsoft's Custom XML Parts. (You may find all the necessary explanations in my comments in the code).

//Deletes all the previously added parts and adds a new part 
//containing the string argument which has to be in XML format. 


public void addCustomXMLPart(string test)
{
    IEnumerator e = Xlworkbook.CustomXMLParts.GetEnumerator();
    e.Reset();
    CustomXMLPart p;
    //The !p.BuiltIn is because before our customXMLPart there are some
    // Excel BuiltIns of them and if we try to delete them we will get an exception.
    while (e.MoveNext())
    {
        p = (CustomXMLPart)e.Current;
        if (p != null && !p.BuiltIn) 
            p.Delete();
    }
    Xlworkbook.CustomXMLParts.Add(test, Type.Missing);
}

About the xlworkbook object used above:

using Excel = Microsoft.Office.Interop.Excel;

Excel.Workbook XlWorkbook = (Excel.Workbook)
  (Excel.Application)Marshal.GetActiveObject("Excel.Application")).ActiveWorkbook;