none
Issue in programmatic Mail-Merge with word document using open-xml, asp.net, C#. RRS feed

  • Question

  • Issue in programmatic Mail-Merge with word document using open-xml, asp.net, C#.

    In one of our project we want to programmatically do mail-merge. That mean, we have an existing word document with mail-merge fields in it and when we run our code, values from table will be replaced in mail-merge fields of word document and the document will be saved with a new name.

    The project is developed using asp.net, C# and open-xml.

    Our code works, but not giving guaranteed results. That is, in one word document some fields get merged but few other do not. Please refer to attached code extract, we are using. The issue is for some of the nodes in nodelist (xnodeList), child nodes, data nodes getting generated as null. So getting Null reference exception.

    Please suggest how to resolve this issue by suggesting a correction in code or any other method altogether. Please note we cannot use interops etc. (Microsoft.Office.Interop.Word) because this is a web application and we cannot install MS-Office on application server.


    ////////////////////////// CODE ////////////////////////////////////////

        using System.IO;
        using System.Xml;
        using DocumentFormat.OpenXml.Packaging;
        using System.IO.Packaging;
        using System.Xml.Linq;
        using WordDocument = DocumentFormat.OpenXml.Wordprocessing;
        protected void mail_merge_file1()
            {
        
          FileInfo fi;
         Object oTemplatePath = AppConstants.TemplatesUpload + hdnTemplatePath.Value.ToString().Trim();
        
        fi = new FileInfo(oTemplatePath.ToString());
        oFileName = lstActualFilePath[0].FilePath + fi.Name.ToString().Replace(fi.Extension, "MM") + fi.Extension;
        // if file exists
        if (System.IO.File.Exists(oTemplatePath.ToString()))
                        {
        
                            XmlNamespaceManager nsManager = new XmlNamespaceManager(nt);
        
                            nsManager.AddNamespace("w", "http://schemas.openxmlformats.org/wordprocessingml/2006/main");
                            PackagePart packagePart;
                            XmlNodeList nodes;
                            XmlNodeList nodes1;
                            XmlNode parentnodes;
                            XmlNode childnode;
                            XmlNode Datanode;
                            string columnname;
                            int N = 1;
                            int br = 1;
                            string strDateTimeFormat = string.Empty;
                            string strDateTimeString;
                            int intIndexofDatestart;
                            string strrootnode;
                            XmlNode root;
        
                            File.Copy(oTemplatePath.ToString(), oFileName.ToString(), true);
                            
                            // open file , read the contents of file into an byte array
                            byte[] sourceBytestemp = File.ReadAllBytes(oFileName.ToString());
                            using (MemoryStream _workingMemoryStream = new MemoryStream())
                            {
                                // Load block of bytes into memory
                                _workingMemoryStream.Write(sourceBytestemp, 0, sourceBytestemp.Length);
                                using (WordprocessingDocument wordDocument = WordprocessingDocument.Open(_workingMemoryStream, true))
                                {
                                    XElement newBody = XElement.Parse(wordDocument.MainDocumentPart.Document.Body.OuterXml);
                                    // Delete MailMerge Data Source Part
                                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                                    {
                                        //populate all nodes of mergefields from the file
                                        IList<XElement> xnodeList = (from el in newBody.Descendants()
                                            //select el).ToList();
                                            where el.Name.LocalName == "t" && el.Value.StartsWith("«")
                                            select el).ToList();
                                        // fetch each node 1 by 1,remove child nodes and replace data nodes's value with the value of the record.    
                                        foreach (XElement xnode in xnodeList)
                                        {
                                            columnname = xnode.Value.ToString().Substring(1, xnode.Value.ToString().Length - 2);
                                            if (ds.Tables[0].Columns.Contains(columnname))
                                            {
                                                
                                                XElement xchildnode = (XElement)xnode.Parent.NextNode;
                                                //below line is getting exception as xchildnode is getting generated "null" value in case of some nodes/columns (xnode).
                                                xchildnode.Remove();
                                                
                                                XElement xDatanode = (XElement)((XElement)xnode.Parent.PreviousNode.PreviousNode).LastNode;
                                                if (((XElement)xDatanode).Value.ToString().Contains("\\"))
                                                {
                                                    if (ds.Tables[0].Columns[columnname] != null)
                                                    {
                                                        intIndexofDatestart = ((XElement)((XElement)xnode.Parent.PreviousNode.PreviousNode).LastNode).Value.ToString().IndexOf("\\");
        
                                                        strDateTimeString = ((XElement)((XElement)xnode.Parent.PreviousNode.PreviousNode).LastNode).Value.ToString().Trim();
                                                        switch (xDatanode.Value.ToString().Substring(intIndexofDatestart, 2))
                                                        {
                                                            case "\\@":
                                                                strDateTimeFormat = strDateTimeString.Substring(intIndexofDatestart + 3, strDateTimeString.Length - (intIndexofDatestart + 4));
                                                                break;
                                                            case "\\#":
                                                                strDateTimeFormat = strDateTimeString.Substring(intIndexofDatestart + 2, strDateTimeString.Length - (intIndexofDatestart + 2));
                                                                break;
                                                        }
                                                        if (ds.Tables[0].Columns[columnname].DataType == Type.GetType("System.DateTime"))
                                                        {
                                                            if (Convert.IsDBNull(ds.Tables[0].Rows[i][columnname]))
                                                            {
                                                                ((XElement)xnode).Value = "";
                                                            }
                                                            else
                                                            {
                                                                ((XElement)xnode).Value  = Convert.ToDateTime(ds.Tables[0].Rows[i][columnname].ToString()).
                    ToString(strDateTimeFormat);
                                                            }
                                                        }
                                                        else
                                                        {
                                                            if (Convert.IsDBNull(ds.Tables[0].Rows[i][columnname]))
                                                            {
                                                                ((XElement)xnode).Value = "";
                                                            }
                                                            else
                                                            {
                                                                /*new code*/
                                                                ((XElement)xnode).Value = ds.Tables[0].Rows[i][columnname].ToString();
                                                                /*new code ends*/
                                                            }
        
                                                        }
                                                    }
                                                }
                                                else
                                                {
                                                    if (Convert.IsDBNull(ds.Tables[0].Rows[i][columnname]))
                                                    {
                                                        ((XElement)xnode).Value = "";
                                                    }
                                                    else
                                                    {
                                                        ((XElement)xnode).Value = ds.Tables[0].Rows[i][columnname].ToString();
                                                    }
                                                }
        
                                                for (int k = 0; k < 3; k++)
                                                {
                                                    xchildnode = (XElement)xnode.Parent.PreviousNode;
                                                    xchildnode.Remove();
                                                }
        
                                            }
        
                                            ///adding page break .
                                            ///
                                            if (i + 1 < ds.Tables[0].Rows.Count)
                                            {
        
                                                IList<XElement> xnodes = (from el in newBody.Descendants()
                                                                          //select el).ToList();
                                                                          where el.Name.LocalName == "sectPr"
                                                                          select el).ToList();
                                                /// end Adding document for next row
                                            }
                                            else
                                            {
                                                wordDocument.MainDocumentPart.Document.Body = new WordDocument.Body(newBody.ToString());
                                                wordDocument.MainDocumentPart.Document.Save();
                                            }
        
                                        }
                                    }
                                    DocumentSettingsPart settingsPart = wordDocument.MainDocumentPart.GetPartsOfType<DocumentSettingsPart>().First();
                                    // Delete reference to Mail Merge Data sources
                                    XElement settings = XElement.Parse(settingsPart.RootElement.OuterXml);
                                    IList<XElement> mailMergeElements =
                                        (from el in settings.Descendants()
                                         where el.Name.LocalName == ("mailMerge")
                                         select el).ToList();
        
                                    foreach (XElement field in mailMergeElements)
                                    {
                                        field.Remove();
                                    }
                                    settingsPart.RootElement.InnerXml = settings.ToString();
                                    settingsPart.RootElement.Save();
        
                                    // Save in output directory
                                    // Create a new document based on updated template
                                    using (FileStream fileStream = new FileStream(oFileName.ToString(), FileMode.Create))
                                    {
                                        _workingMemoryStream.WriteTo(fileStream);
                                    }
                                }
                            }
                        }
        
        
            }   

               
    Thursday, September 11, 2014 6:16 AM

All replies

  • Issue in programmatic Mail-Merge with word document using open-xml, asp.net, C#.

    In one of our project we want to programmatically do mail-merge. That mean, we have an existing word document with mail-merge fields in it and when we run our code, values from table will be replaced in mail-merge fields of word document and the document will be saved with a new name.

    The project is developed using asp.net, C# and open-xml.

    Our code works, but not giving guaranteed results. That is, in one word document some fields get merged but few other do not. Please refer to attached code extract, we are using. The issue is for some of the nodes in nodelist (xnodeList), child nodes, data nodes getting generated as null. So getting Null reference exception.

    Please suggest how to resolve this issue by suggesting a correction in code or any other method altogether. Please note we cannot use interops etc. (Microsoft.Office.Interop.Word) because this is a web application and we cannot install MS-Office on application server.


    ////////////////////////// CODE ////////////////////////////////////////

        using System.IO;
        using System.Xml;
        using DocumentFormat.OpenXml.Packaging;
        using System.IO.Packaging;
        using System.Xml.Linq;
        using WordDocument = DocumentFormat.OpenXml.Wordprocessing;
        protected void mail_merge_file1()
            {
        
          FileInfo fi;
         Object oTemplatePath = AppConstants.TemplatesUpload + hdnTemplatePath.Value.ToString().Trim();
        
        fi = new FileInfo(oTemplatePath.ToString());
        oFileName = lstActualFilePath[0].FilePath + fi.Name.ToString().Replace(fi.Extension, "MM") + fi.Extension;
        // if file exists
        if (System.IO.File.Exists(oTemplatePath.ToString()))
                        {
        
                            XmlNamespaceManager nsManager = new XmlNamespaceManager(nt);
        
                            nsManager.AddNamespace("w", "http://schemas.openxmlformats.org/wordprocessingml/2006/main");
                            PackagePart packagePart;
                            XmlNodeList nodes;
                            XmlNodeList nodes1;
                            XmlNode parentnodes;
                            XmlNode childnode;
                            XmlNode Datanode;
                            string columnname;
                            int N = 1;
                            int br = 1;
                            string strDateTimeFormat = string.Empty;
                            string strDateTimeString;
                            int intIndexofDatestart;
                            string strrootnode;
                            XmlNode root;
        
                            File.Copy(oTemplatePath.ToString(), oFileName.ToString(), true);
                            
                            // open file , read the contents of file into an byte array
                            byte[] sourceBytestemp = File.ReadAllBytes(oFileName.ToString());
                            using (MemoryStream _workingMemoryStream = new MemoryStream())
                            {
                                // Load block of bytes into memory
                                _workingMemoryStream.Write(sourceBytestemp, 0, sourceBytestemp.Length);
                                using (WordprocessingDocument wordDocument = WordprocessingDocument.Open(_workingMemoryStream, true))
                                {
                                    XElement newBody = XElement.Parse(wordDocument.MainDocumentPart.Document.Body.OuterXml);
                                    // Delete MailMerge Data Source Part
                                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                                    {
                                        //populate all nodes of mergefields from the file
                                        IList<XElement> xnodeList = (from el in newBody.Descendants()
                                            //select el).ToList();
                                            where el.Name.LocalName == "t" && el.Value.StartsWith("«")
                                            select el).ToList();
                                        // fetch each node 1 by 1,remove child nodes and replace data nodes's value with the value of the record.    
                                        foreach (XElement xnode in xnodeList)
                                        {
                                            columnname = xnode.Value.ToString().Substring(1, xnode.Value.ToString().Length - 2);
                                            if (ds.Tables[0].Columns.Contains(columnname))
                                            {
                                                
                                                XElement xchildnode = (XElement)xnode.Parent.NextNode;
                                                //below line is getting exception as xchildnode is getting generated "null" value in case of some nodes/columns (xnode).
                                                xchildnode.Remove();
                                                
                                                XElement xDatanode = (XElement)((XElement)xnode.Parent.PreviousNode.PreviousNode).LastNode;
                                                if (((XElement)xDatanode).Value.ToString().Contains("\\"))
                                                {
                                                    if (ds.Tables[0].Columns[columnname] != null)
                                                    {
                                                        intIndexofDatestart = ((XElement)((XElement)xnode.Parent.PreviousNode.PreviousNode).LastNode).Value.ToString().IndexOf("\\");
        
                                                        strDateTimeString = ((XElement)((XElement)xnode.Parent.PreviousNode.PreviousNode).LastNode).Value.ToString().Trim();
                                                        switch (xDatanode.Value.ToString().Substring(intIndexofDatestart, 2))
                                                        {
                                                            case "\\@":
                                                                strDateTimeFormat = strDateTimeString.Substring(intIndexofDatestart + 3, strDateTimeString.Length - (intIndexofDatestart + 4));
                                                                break;
                                                            case "\\#":
                                                                strDateTimeFormat = strDateTimeString.Substring(intIndexofDatestart + 2, strDateTimeString.Length - (intIndexofDatestart + 2));
                                                                break;
                                                        }
                                                        if (ds.Tables[0].Columns[columnname].DataType == Type.GetType("System.DateTime"))
                                                        {
                                                            if (Convert.IsDBNull(ds.Tables[0].Rows[i][columnname]))
                                                            {
                                                                ((XElement)xnode).Value = "";
                                                            }
                                                            else
                                                            {
                                                                ((XElement)xnode).Value  = Convert.ToDateTime(ds.Tables[0].Rows[i][columnname].ToString()).
                    ToString(strDateTimeFormat);
                                                            }
                                                        }
                                                        else
                                                        {
                                                            if (Convert.IsDBNull(ds.Tables[0].Rows[i][columnname]))
                                                            {
                                                                ((XElement)xnode).Value = "";
                                                            }
                                                            else
                                                            {
                                                                /*new code*/
                                                                ((XElement)xnode).Value = ds.Tables[0].Rows[i][columnname].ToString();
                                                                /*new code ends*/
                                                            }
        
                                                        }
                                                    }
                                                }
                                                else
                                                {
                                                    if (Convert.IsDBNull(ds.Tables[0].Rows[i][columnname]))
                                                    {
                                                        ((XElement)xnode).Value = "";
                                                    }
                                                    else
                                                    {
                                                        ((XElement)xnode).Value = ds.Tables[0].Rows[i][columnname].ToString();
                                                    }
                                                }
        
                                                for (int k = 0; k < 3; k++)
                                                {
                                                    xchildnode = (XElement)xnode.Parent.PreviousNode;
                                                    xchildnode.Remove();
                                                }
        
                                            }
        
                                            ///adding page break .
                                            ///
                                            if (i + 1 < ds.Tables[0].Rows.Count)
                                            {
        
                                                IList<XElement> xnodes = (from el in newBody.Descendants()
                                                                          //select el).ToList();
                                                                          where el.Name.LocalName == "sectPr"
                                                                          select el).ToList();
                                                /// end Adding document for next row
                                            }
                                            else
                                            {
                                                wordDocument.MainDocumentPart.Document.Body = new WordDocument.Body(newBody.ToString());
                                                wordDocument.MainDocumentPart.Document.Save();
                                            }
        
                                        }
                                    }
                                    DocumentSettingsPart settingsPart = wordDocument.MainDocumentPart.GetPartsOfType<DocumentSettingsPart>().First();
                                    // Delete reference to Mail Merge Data sources
                                    XElement settings = XElement.Parse(settingsPart.RootElement.OuterXml);
                                    IList<XElement> mailMergeElements =
                                        (from el in settings.Descendants()
                                         where el.Name.LocalName == ("mailMerge")
                                         select el).ToList();
        
                                    foreach (XElement field in mailMergeElements)
                                    {
                                        field.Remove();
                                    }
                                    settingsPart.RootElement.InnerXml = settings.ToString();
                                    settingsPart.RootElement.Save();
        
                                    // Save in output directory
                                    // Create a new document based on updated template
                                    using (FileStream fileStream = new FileStream(oFileName.ToString(), FileMode.Create))
                                    {
                                        _workingMemoryStream.WriteTo(fileStream);
                                    }
                                }
                            }
                        }
        
        
            }   

               
    Wednesday, September 10, 2014 10:51 AM
  • Have you debug to find which child nodes are null?

    Friday, September 12, 2014 7:21 AM
  • Hi,

    Thanks for sharing your sample code.

    I could not find the obvious issue from your code currently. If this issue only occurred on a specific document. I hope you could also share the sample document through OneDrive to help us reproduce this issue so that we could also troubleshoot it on our side.

    Regards,

    Jeffrey


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 12, 2014 10:02 AM
    Moderator