none
Problem in calculating formulas in word using open xml RRS feed

  • Question

  • Hi Team,

    I am generating a word document using open XML. In my word doc i have formulas like

    {=IF(<<MERGEFIELD>> > 100,10,0)}
    am able to replace the MERGEFIELD with a value lets suppose 50 and when trying to generate the word document the formula is not updating, it just displays as 
    {=IF(50 > 100,10,0)}

    rather than 0. The value is not updating

    please help me out


    Harish

    Thursday, November 7, 2013 7:19 AM

Answers

  • Hi Harish

    Word fields, with a very few exceptions, such as a PAGE field in the header or footer, will not update automatically when a document is opened. This is a security consideration.

    You have three basic options:

    1. Instruct the user to update fields manually by pressing Ctrl+A, F9

    2. Set the "Dirty" property of the FieldChar object to True. This will inform Word when it opens the document that the field code should be updated. Word will display a message to the user, asking if it's allowed to update the field code.

    3. Before passing the document to the user, process it using Word Automation Services to update the "dirty" field codes. (More about the Automation Service at http://blogs.office.com/b/microsoft-word/archive/2012/09/26/what-s-new-in-word-automation-services.aspx).

    4. Perform the calculation yourself, as part of the Open XML code, and insert the result into the document. (In this case, it might even make sense to remove the If field code completely and insert only the result.)


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, November 7, 2013 4:41 PM
    Moderator
  • Hi Harish

    Right...

    It's a fairly complicated thing to do, especially since I don't know exactly how you're making the substitution. However, below is some code that picks up all the merge fields in a document, checks whether they're nested in an IF field and, if so, gets the If statement (including the merge field name).

    What it does not do is parse the If statement, do any substitution, or write a result. This is just to give you an idea how you can determine whether the Mergefield is nested in an IF and how to get the full If statement.

    Note that a result must be written to a FieldCharsValue.Separate element, in its own Run. In order to see this, open a document with field codes, press Ctrl+A then F9 to force the fields to update. Then close the document and look at the Word Open XML.

            private void btnMergeFieldInIfField_Click(object sender, EventArgs e)
            {
                string fileName = @"C:\test\CalcIfFields - Copy.docx";
                string fldContent = String.Empty;
                using (WordprocessingDocument pkgDoc = WordprocessingDocument.Open(fileName, true))
                {
                    string fieldList = string.Empty;
                    bool inIfCode;
                    Run rFldCodeIf = null;
                    Document doc = pkgDoc.MainDocumentPart.Document;
                    //Get all merge fields in the document
                    IEnumerable<FieldCode> fldCodesMergeField = doc.Descendants<FieldCode>().Where(fc => fc.InnerText.Contains("Mergefield"));
                    foreach (FieldCode fldCodeMergeField in fldCodesMergeField)
                    {
                        inIfCode = false;
                        System.Diagnostics.Debug.Print(fldCodeMergeField.InnerText);
                        //Get all Field codes in the same paragraph as the merge field.
                        IEnumerable<FieldCode> fldCodesPrevious = fldCodeMergeField.Parent.Parent.Descendants<FieldCode>();
                        //Loop all these field codes, stopping when the field code is the same as the current merge field.
                        //Check each whether it's an IF field...
                        foreach (FieldCode fldCodePrevious in fldCodesPrevious)
                        {
                            System.Diagnostics.Debug.Print(fldCodePrevious.InnerText);
                            if (fldCodeMergeField.Equals(fldCodePrevious)) break; //we're only interested in previous codes
                            if (fldCodePrevious.InnerText.StartsWith(" If "))
                            {   //For an If field, we want the parent element (Run)
                                Run r = (Run)fldCodePrevious.Parent;
                                //Work forward, until the Run with the merge field,
                                //Checking whether we hit an end-of-field character.
                                //If we do, the merge field is not nested in the If
                                //and stands alone. Otherwise, the mergefield is in the If field
                                //and must be processed further.
                                while (r != fldCodeMergeField.Parent)
                                {
                                    FieldChar fldChar = r.Elements<FieldChar>().FirstOrDefault();
                                    if (fldChar != null)
                                    {
                                        if (fldChar.FieldCharType == FieldCharValues.End)
                                        {
                                            inIfCode = false;
                                            rFldCodeIf = null;
                                        }
                                        else
                                        {
                                            inIfCode = true;
                                            rFldCodeIf = (Run)fldCodePrevious.Parent;
                                        }
                                    }
                                    r = (Run)r.NextSibling();
                                }
                            }
                        }
                        //The purpose of this section is to put together the If statement in the field
                        //After this finishes, the statement still needs to be parsed.
                        if (inIfCode)
                        {
                            System.Diagnostics.Debug.Print(fldCodeMergeField.InnerText + " is in an If field.");
                            if (rFldCodeIf != null)
                            {
                                string fldCode = "If " + fldCodeMergeField.InnerText;
                                Run r = rFldCodeIf;
                                bool inNestedField = false;
                                bool endOfIfField = false;
                                //Go to each NextSibling until end-of-field
                                //Need to skip any nested fields (Mergefield)
                                //So test instrText if contains "begin";
                                //if yes, keep walking until reach "end",
                                //then pick up the instrText again until reach "end".
                                do
                                {
                                    r = (Run)r.NextSibling();
                                    FieldChar fldCharRun = r.Elements<FieldChar>().FirstOrDefault();
                                    if (fldCharRun != null &&
                                        fldCharRun.FieldCharType == FieldCharValues.Begin)
                                    { //We're in a nested field. Go forward until we get to an end-point
                                        inNestedField = true;
                                    }
                                    if (fldCharRun != null &&
                                        fldCharRun.FieldCharType == FieldCharValues.End &&
                                        !inNestedField)
                                    { //end of the If field
                                        endOfIfField = true;
                                    }
                                    if (fldCharRun != null &&
                                        fldCharRun.FieldCharType == FieldCharValues.End &&
                                        inNestedField)
                                    { //We're at the end of a nested field
                                        inNestedField = false;
                                    }
                                    //There will be either a field character or a field code, but not both
                                    FieldCode fldIfFieldCode = r.Elements<FieldCode>().FirstOrDefault();
                                    if (fldCharRun == null && !inNestedField)
                                    {
                                        fldCode += fldIfFieldCode.InnerText;
                                    }
                                } while (!endOfIfField);
                                System.Diagnostics.Debug.Print(fldCode);
                            }
                        }
                    }
                }
    }


    Cindy Meister, VSTO/Word MVP, my blog

    Sunday, November 10, 2013 8:06 PM
    Moderator

All replies

  • Hi Harish

    Word fields, with a very few exceptions, such as a PAGE field in the header or footer, will not update automatically when a document is opened. This is a security consideration.

    You have three basic options:

    1. Instruct the user to update fields manually by pressing Ctrl+A, F9

    2. Set the "Dirty" property of the FieldChar object to True. This will inform Word when it opens the document that the field code should be updated. Word will display a message to the user, asking if it's allowed to update the field code.

    3. Before passing the document to the user, process it using Word Automation Services to update the "dirty" field codes. (More about the Automation Service at http://blogs.office.com/b/microsoft-word/archive/2012/09/26/what-s-new-in-word-automation-services.aspx).

    4. Perform the calculation yourself, as part of the Open XML code, and insert the result into the document. (In this case, it might even make sense to remove the If field code completely and insert only the result.)


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, November 7, 2013 4:41 PM
    Moderator
  • Thank you for replying sir. In my case i want to choose the 4th option. Can you please suggest me on how to calculate the if part using open xml. I can be able to replace the IF part with the result if you help me out in getting the result.

    Thanks and regards


    Harish


    Friday, November 8, 2013 5:19 AM
  • Hi Harish

    Since I don't know your document, or how your code is expected to work with the document, it's difficult to provide an "answer" to this.

    Are you basically just looking for Mergefields and doing a substitution? Or something else?


    Cindy Meister, VSTO/Word MVP, my blog

    Friday, November 8, 2013 8:17 PM
    Moderator
  • Hi sir,

    am just looking for Mergefields and doing a substitution thats it.


    Harish

    Sunday, November 10, 2013 1:45 PM
  • Hi Harish

    Right...

    It's a fairly complicated thing to do, especially since I don't know exactly how you're making the substitution. However, below is some code that picks up all the merge fields in a document, checks whether they're nested in an IF field and, if so, gets the If statement (including the merge field name).

    What it does not do is parse the If statement, do any substitution, or write a result. This is just to give you an idea how you can determine whether the Mergefield is nested in an IF and how to get the full If statement.

    Note that a result must be written to a FieldCharsValue.Separate element, in its own Run. In order to see this, open a document with field codes, press Ctrl+A then F9 to force the fields to update. Then close the document and look at the Word Open XML.

            private void btnMergeFieldInIfField_Click(object sender, EventArgs e)
            {
                string fileName = @"C:\test\CalcIfFields - Copy.docx";
                string fldContent = String.Empty;
                using (WordprocessingDocument pkgDoc = WordprocessingDocument.Open(fileName, true))
                {
                    string fieldList = string.Empty;
                    bool inIfCode;
                    Run rFldCodeIf = null;
                    Document doc = pkgDoc.MainDocumentPart.Document;
                    //Get all merge fields in the document
                    IEnumerable<FieldCode> fldCodesMergeField = doc.Descendants<FieldCode>().Where(fc => fc.InnerText.Contains("Mergefield"));
                    foreach (FieldCode fldCodeMergeField in fldCodesMergeField)
                    {
                        inIfCode = false;
                        System.Diagnostics.Debug.Print(fldCodeMergeField.InnerText);
                        //Get all Field codes in the same paragraph as the merge field.
                        IEnumerable<FieldCode> fldCodesPrevious = fldCodeMergeField.Parent.Parent.Descendants<FieldCode>();
                        //Loop all these field codes, stopping when the field code is the same as the current merge field.
                        //Check each whether it's an IF field...
                        foreach (FieldCode fldCodePrevious in fldCodesPrevious)
                        {
                            System.Diagnostics.Debug.Print(fldCodePrevious.InnerText);
                            if (fldCodeMergeField.Equals(fldCodePrevious)) break; //we're only interested in previous codes
                            if (fldCodePrevious.InnerText.StartsWith(" If "))
                            {   //For an If field, we want the parent element (Run)
                                Run r = (Run)fldCodePrevious.Parent;
                                //Work forward, until the Run with the merge field,
                                //Checking whether we hit an end-of-field character.
                                //If we do, the merge field is not nested in the If
                                //and stands alone. Otherwise, the mergefield is in the If field
                                //and must be processed further.
                                while (r != fldCodeMergeField.Parent)
                                {
                                    FieldChar fldChar = r.Elements<FieldChar>().FirstOrDefault();
                                    if (fldChar != null)
                                    {
                                        if (fldChar.FieldCharType == FieldCharValues.End)
                                        {
                                            inIfCode = false;
                                            rFldCodeIf = null;
                                        }
                                        else
                                        {
                                            inIfCode = true;
                                            rFldCodeIf = (Run)fldCodePrevious.Parent;
                                        }
                                    }
                                    r = (Run)r.NextSibling();
                                }
                            }
                        }
                        //The purpose of this section is to put together the If statement in the field
                        //After this finishes, the statement still needs to be parsed.
                        if (inIfCode)
                        {
                            System.Diagnostics.Debug.Print(fldCodeMergeField.InnerText + " is in an If field.");
                            if (rFldCodeIf != null)
                            {
                                string fldCode = "If " + fldCodeMergeField.InnerText;
                                Run r = rFldCodeIf;
                                bool inNestedField = false;
                                bool endOfIfField = false;
                                //Go to each NextSibling until end-of-field
                                //Need to skip any nested fields (Mergefield)
                                //So test instrText if contains "begin";
                                //if yes, keep walking until reach "end",
                                //then pick up the instrText again until reach "end".
                                do
                                {
                                    r = (Run)r.NextSibling();
                                    FieldChar fldCharRun = r.Elements<FieldChar>().FirstOrDefault();
                                    if (fldCharRun != null &&
                                        fldCharRun.FieldCharType == FieldCharValues.Begin)
                                    { //We're in a nested field. Go forward until we get to an end-point
                                        inNestedField = true;
                                    }
                                    if (fldCharRun != null &&
                                        fldCharRun.FieldCharType == FieldCharValues.End &&
                                        !inNestedField)
                                    { //end of the If field
                                        endOfIfField = true;
                                    }
                                    if (fldCharRun != null &&
                                        fldCharRun.FieldCharType == FieldCharValues.End &&
                                        inNestedField)
                                    { //We're at the end of a nested field
                                        inNestedField = false;
                                    }
                                    //There will be either a field character or a field code, but not both
                                    FieldCode fldIfFieldCode = r.Elements<FieldCode>().FirstOrDefault();
                                    if (fldCharRun == null && !inNestedField)
                                    {
                                        fldCode += fldIfFieldCode.InnerText;
                                    }
                                } while (!endOfIfField);
                                System.Diagnostics.Debug.Print(fldCode);
                            }
                        }
                    }
                }
    }


    Cindy Meister, VSTO/Word MVP, my blog

    Sunday, November 10, 2013 8:06 PM
    Moderator
  • Addendum: Of course, if you don't want to retain the IF and Mergefield codes it would be simplest to simply delete the entire field code structure and substitute the calculated result...

    Cindy Meister, VSTO/Word MVP, my blog

    Sunday, November 10, 2013 8:18 PM
    Moderator