1

I need to save long (,document specific) string to an Excel document. Since the length limit for Office.Core.CustomDocumentProperty.value is only 255 char, please advise on how to overcome this limit, or suggesting other ways to store data in an Excel document.

(To my recollection, a cell formula can only store 255 char, so this is not a workable solution.)

CSharpie
  • 9,195
  • 4
  • 44
  • 71
Carson
  • 35
  • 8
  • What version of Interop are you using. Old version are for Excel 2003 which has the limit of 255 char. – jdweng Feb 08 '17 at 18:32
  • VSTO for Excel 2010. It seems only first 255 char are stored... – Carson Feb 08 '17 at 18:33
  • You can definitely store **more than 255 chars** text in a cell in Excel 2010. The [Excel specifications and limits document](https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3#ID0EBABAAA=Excel_2010) states: *"Total number of characters that a cell can contain: 32,767 characters"* – Marcus Mangelsdorf Nov 12 '17 at 14:32

1 Answers1

3

Just split your value into multiple properties. Something like this would work.

private static void WriteCustomDocumentProperty(Workbook workbook, string name, string value)
{
    dynamic customDocumentProperties = workbook.CustomDocumentProperties;
    var numParts = value.Length/255 + (value.Length%255 != 0 ? 1 : 0);
    for (var i = 0; i < numParts; ++i)
    {
        var part = value.Substring(i*255, Math.Min(255, value.Length - i*255));
        customDocumentProperties.Add(name + "." + i, false, MsoDocProperties.msoPropertyTypeString, part);
    }
    customDocumentProperties.Add(name + ".Count", false, MsoDocProperties.msoPropertyTypeNumber, numParts);
}

private static string ReadCustomDocumentProperty(Workbook workbook, string name)
{
    dynamic customDocumentProperties = workbook.CustomDocumentProperties;
    var numParts = Convert.ToInt32(customDocumentProperties[name + ".Count"].Value);
    var value = new StringBuilder();
    for (var i = 0; i < numParts; ++i)
        value.Append(customDocumentProperties[name + "." + i].Value);
    return value.ToString();
}

Depending on the size of your strings, this may be very slow. A better option might be to use Custom XML Parts. (I highly recommend changing the namespace "urn:custom-storage:XXX" to something unique and proprietary, lest you run afoul of another software written using this same technique.)

private static void WriteCustomDocumentProperty(Workbook workbook, string name, string value)
{
    var ns = "urn:custom-storage:" + name;
    var document = new XDocument(new XElement(XName.Get("custom-storage", ns), value));
    var xmlValue = document.ToString();
    workbook.CustomXMLParts.Add(xmlValue);
}

private static string ReadCustomDocumentProperty(Workbook workbook, string name)
{
    var ns = "urn:custom-storage:" + name;
    var parts = workbook.CustomXMLParts.SelectByNamespace(ns);
    switch (parts.Count)
    {
        case 0:
            return null;
        case 1:
            return XDocument.Parse(parts[1].XML).Root.Value;
        default:
            throw new ApplicationException("Duplicate part in workbook.");
    }
}
Michael Gunter
  • 12,528
  • 1
  • 24
  • 58
  • Please advise if there are size limit for the number of custom document property. – Carson Feb 08 '17 at 19:03
  • I don't know the answer to that, but testing (which took me 1 minute to write and which you could have done yourself) indicates that I can safely write 10,000 strings of length 255. If you need to store something longer than that, I'll leave it to you for further testing. – Michael Gunter Feb 08 '17 at 19:06
  • FYI: You could also use a CustomXMLPart for this storage. These things don't have a size limit (or it's huge). – Michael Gunter Feb 08 '17 at 19:06
  • Thanks! It works! Sorry, I thought you may have the answer off the top of your head and I am still figuring your code... I searched for the answer for the size limit too, but it seems there are none in the documentation. Besides, it seems the customDocumentProperty is using xml to store data too, so I believe there are no limits. I was considering CustomXMLPart too (as it seems to be more suitable for my case), but I found it very hard to manipulate xml, so I will switch to that technique if it is too slow~ – Carson Feb 08 '17 at 19:48