Wednesday, July 30, 2014

Perform calculation/formula in word document using OPEN XML

Find word "Formula()" and consider inside text as formula and perform operation in #.net


XDocument xDoc = myDoc.MainDocumentPart.GetXDocument();
GetAllFormulasInDocument(xDoc);
foreach (KeyValuePair<string, string> item in Lists)
{
                    string result2 = CalculateFormula(item.Value.Replace("Formula", "").Replace("(", "").Replace(")", ""));

                    TextReplacer.SearchAndReplace(myDoc, item.Key, result2, false);

}

public static string CalculateFormula(string evaluationString)
{
            try
            {
                VsaEngine en = VsaEngine.CreateEngine();
                Object result = Eval.JScriptEvaluate(evaluationString, en);

                return result.ToString();
            }
            catch
            {
                return "";
            }

}

public static Dictionary<string, string> Lists = new Dictionary<string, string>();
public static void GetAllFormulasInDocument(XDocument xDoc)
        {
            string s = xDoc.Root.Value;

            Regex r = new Regex(@"Formula((.+?)\))");
            mc = r.Matches(s, 0);
            string tempString = string.Empty;
            string result = string.Empty;
            foreach (Match m in mc)
            {
                if (Lists.ContainsKey(m.Value) == false)
                {
                    //result = CalculateFormula(m.Value.Replace("Formula", "").Replace("(", "").Replace(")", ""));
                    Lists.Add(m.Value, m.Value);
                }
            }
        }



No comments:

Post a Comment