6

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.

user542393
  • 103
  • 1
  • 6

3 Answers3

6

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.

Stuart Thompson
  • 1,839
  • 2
  • 15
  • 17
  • Will this preserve the XML data after a `Save As ...` from the user? – reinierpost Dec 14 '10 at 19:18
  • It will not survive either a Save or Save As. Thank you for noticing that. I hadn't read that part of the question thoroughly enough. – Stuart Thompson Dec 14 '10 at 19:23
  • Correct, it will not survive Excel.SaveAs. However, Word allows for custom parts. Is there an equivalent in Excel? maybe a special relationship type? See http://msdn.microsoft.com/en-us/library/bb608618.aspx – user542393 Dec 14 '10 at 19:43
  • In looking further at the contents of the .xlsx file, it looks as though there are some manifest files in there. The _rels and Content_Types.xml files appear to store lists of files that are included in the archive along with their content types. It might be possible to add lines to those files to indicate that the custom .xml file should be kept when the file is saved. – Stuart Thompson Dec 14 '10 at 20:00
  • 1
    Found the answer: the relationship type has to be customXml. Then Excel preserves the xml data. – user542393 Dec 14 '10 at 20:37
6

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;
sfuqua
  • 5,797
  • 1
  • 32
  • 33
Fatima
  • 869
  • 10
  • 35
2

Excel is an application for storing, manipulating, and viewing data, and thusly, it is not designed to store arbitrary unreadable data. However, as you alluded to, you could use hidden fields on a separate sheet to put some information.

Mark Avenius
  • 13,679
  • 6
  • 42
  • 50