1

During validation process xlsx document is converted to pdf.

Inside of this xlsx documents there are "tokens" which need to be override with proper values. To do this I'm using this:

 byte[] fileContent = document.Contents;
    using (MemoryStream mem = new MemoryStream())
    {  
        mem.Write(fileContent, 0, (int)fileContent.Length);
        OverrideXLSX(mem, document);
    }          


    private static byte[] OverrideXLSX(MemoryStream mem, Document document)
    {
        byte[] toReturn;
        using(SpreadsheetDocument sprDoc = SpreadsheetDocument.Open(mem, true))
        {                      
            foreach(WorksheetPart worksheetPart in sprDoc.WorkbookPart.WorksheetParts)
            {               
                OverridXmlPart(worksheetPart, document);            
            }       
            mem.Position = 0;
            toReturn = mem.ToArray();
        }
        return toReturn;
    }

    private static void OverridXmlPart(OpenXmlPart xmlPart, Document document)
    {
        foreach (IdPartPair part in xmlPart.Parts)
            OverridXmlPart(part.OpenXmlPart, document); 

        string docText = string.Empty;
        using(StreamReader sr = new StreamReader(xmlPart.GetStream()))
            docText = sr.ReadToEnd();

        if (docText.StartsWith("<?xml ") && docText.IndexOf("token_", StringComparison.InvariantCultureIgnoreCase) > 0)
        {
            docText = OverrideTokens(docText, document); //This is chaning the tokens to the right one in thext
            using (StreamWriter sw = new StreamWriter(xmlPart.GetStream(FileMode.Create)))
                sw.Write(docText);
        }
    }



 private string OverrideTokens(string docText, Document document)
    { 
       return docText.Replace("token_id", document.Id); 
    }

But this is changing only some of the tokens. Looks like I'm not searching in all of the places. Can someone point me what I'm doing wrong in here?

Here is example of the xlsx before changing tokens, and generated pdf: https://1drv.ms/f/s!AiO9pakkDfFygaI57tLcxWBC3q68IA

Here is the answer: open xml excel Insert actual value in the placeholder

Community
  • 1
  • 1
Jacek
  • 53
  • 1
  • 6
  • You're not showing the `OverrideTokens` and it's a key part of your code. What's exactly a token here: a placeholder? If so, look [here](http://stackoverflow.com/questions/5934429/open-xml-excel-insert-actual-value-in-the-placeholder) –  Aug 10 '16 at 17:18
  • Image it like this: private string OverrideTokens(string docText, Document document) { return docText.Replace("token_id", document.Id); } It's a bit more complex, but basically this part is working fine. – Jacek Aug 10 '16 at 19:31
  • Can you add a minimum example (I guess that a basic xlsx is needed) so that we are able to verify current vs expected results? –  Aug 10 '16 at 19:42
  • Answer to this post contains missing part: http://stackoverflow.com/questions/5934429/open-xml-excel-insert-actual-value-in-the-placeholder?noredirect=1&lq=1 – Jacek Aug 12 '16 at 07:52

0 Answers0