Generating the Word Document with SharePoint list data

Question

I have an issue with exporting the list data from sharepoint to microsoft word (docx).

Basically, the export succeeds, I can move the data from the list to the word document using xml (XElement etc) but the issue is with multiline!

So when i try to export the multiline value, it doesn't work properly, the value is imported but only as one single long line, so there are no /r/n basically, no new line, no carrage return.

Any idea how to do it?

Here is the code:

using (SPSite oCurrentSite = SPContext.Current.Site) { using (SPWeb oCurrentWeb = SPContext.Current.Web) { MemoryStream docStream = new MemoryStream(); //SPFile templateFile = SPContext.Current.Web.GetFile(base.Request["Template"]); SPFile templateFile = (SPFile)oCurrentWeb.GetFileOrFolderObject(Request["Template"]); Stream templateStream = templateFile.OpenBinaryStream(); BinaryReader reader = new BinaryReader(templateStream); BinaryWriter writer = new BinaryWriter(docStream); writer.Write(reader.ReadBytes(Convert.ToInt32(templateStream.Length))); writer.Flush(); reader.Close(); templateStream.Dispose(); string xml = templateFile.Item.Fields["Query"].GetFieldValueAsText(templateFile.Item["Query"]); xml = xml.Replace("{ItemId}", Request["ID"]); System.Xml.XmlDocument caml = new System.Xml.XmlDocument(); caml.LoadXml(xml); using (WordprocessingDocument wordDoc = WordprocessingDocument.Open(docStream, true)) { // Get the content control structure from the template document. XElement contentControlStructure = ContentControlManager.GetContentControls(wordDoc); // Retrive data from SharePoint, constructing the XML tree to pass to // the ContentControlManager.SetContentControls method. foreach (XElement table in contentControlStructure.Elements("Table")) { try { string webUrl = String.Empty; string list = String.Empty; SPQuery query = new SPQuery(); query.DatesInUtc = true; query.MeetingInstanceId = -1; XmlNode node = caml.SelectSingleNode("//Query[@Name='" + table.Attribute("Name").Value + "']"); if (node != null) { query.Query = node.InnerXml; if (node.Attributes["SiteUrl"] != null) { webUrl = node.Attributes["SiteUrl"].Value; } else { webUrl = oCurrentWeb.ServerRelativeUrl; } if (node.Attributes["List"] != null) { list = node.Attributes["List"].Value; } else { list = node.Attributes["Name"].Value; } } using (SPWeb oWeb = oCurrentSite.OpenWeb(webUrl)) { table.Add(new XAttribute("SiteUrl", oWeb.Url)); SPList itemList = oWeb.Lists[list]; table.Add(new XAttribute("ListId", itemList.ID.ToString())); table.Add(new XAttribute("ListUrl", itemList.DefaultViewUrl)); if (itemList != null) { SPListItemCollection items = itemList.GetItems(query); int itemListCount = items.Count; for (int u = 0; u < itemListCount; ++u) { XElement row = new XElement("Row"); foreach (XElement field in table.Elements("Field")) { string value; bool isHtml = false; try { switch(field.Attribute("Name").Value) { case "Today": value = DateTime.Today.ToString("d MMMM yyyy"); row.Add(new XElement("Field", field.Attribute("Name"), new XAttribute("Value", value))); break; default: SPField column = itemList.Fields.GetFieldByInternalName(field.Attribute("Name").Value); if (column is SPFieldCalculated) { value = column.GetFieldValueAsHtml(items[u][field.Attribute("Name").Value]); row.Add(new XElement("Field", field.Attribute("Name"), new XAttribute("Value", value))); } else { value = column.GetFieldValueAsText(items[u][field.Attribute("Name").Value]); row.Add(new XElement("Field", field.Attribute("Name"), new XAttribute("Value", value.Replace("\r\n","<w:br />"))); //HERE IS THE ISSUE } break; } } catch (Exception ex) { row.Add(new XElement("Field", field.Attribute("Name"), new XAttribute("Value", String.Empty))); //row.Add(new XElement("Field", field.Attribute("Name"), new XElement(W.r,new XElement(W.rPr),new XElement(W.t, String.Empty)))); } } table.Add(row); } } } } catch (System.ArgumentException) { } } // Replace content controls with the data retrieved from SharePoint. ContentControlManager.SetContentControls(wordDoc, contentControlStructure); } HttpContext.Current.Response.ClearContent(); HttpContext.Current.Response.ClearHeaders(); HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + templateFile.Name); HttpContext.Current.Response.ContentType = "application/vnd.ms-word.document.12"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; docStream.Position = 0; const Int32 size = 4096; byte[] bytes = new Byte[4096]; Int32 numBytes; numBytes = docStream.Read(bytes, 0, size); while (numBytes > 0) { HttpContext.Current.Response.OutputStream.Write(bytes, 0, numBytes); numBytes = docStream.Read(bytes, 0, size); } HttpContext.Current.Response.Flush(); docStream.Close(); docStream.Dispose(); Response.Close(); } } } 

I wrote "HERE IS THE ISSUE" in comments where the exact problem i got, so i tried to use some xml to automatically create the line breaks and stuff, but no success, it only adds to the text some stuff and that is it.

We know that docx is xml based, it has some xml structure, but maybe anyone has an idea how to properly extract the line break?

PS: i have a word template which is of course used, with 2 empty some sort of text boxes, i set there allow carrige return. It is strange but in string value i get for example "hello /r/n hello /r/n, but later when i add it to xml as attribute i get it replaced by "
"

Answer 1

The issue is you are trying to work with xml as with text, but you should work with it as with structure. When you insert xml-like text into an XElement the .net library uses escape sequences to encode special characters so it guarantees that this text should be interpreted as text but not xml.

In your case you should prepare your XElement with internal structure and then add it to your word document structure. For doing this you nedd to split your text into text rows and then combine them with <w:br> between. Something like this

XElement contacts = new XElement("Contacts", new XElement("Contact", new XElement("Name", "Patrick Hines"), new XElement("Phone", "206-555-0144"), new XElement("Address", new XElement("Street1", "123 Main St"), new XElement("City", "Mercer Island"), new XElement("State", "WA"), new XElement("Postal", "68042") ) ) ); 

------------------added later--------------

As I understood later the problem is how to save newlines in the xml attribute value. The sequences 
 and 
 are standard escape sequences (for example, see w3c). They are just the representation of CR-NL, so that is the expected behavior.

In order to get the source as a multiline string, you can use this piece of code

SecurityElement securityElement = System.Security.SecurityElement.FromString("<test>H&amp;M</test>"); string unescapedText = securityElement.Text; Console.WriteLine(unescapedText); // Result: H&M 

Answer 2

Ok, thank you for the help, but I found out the problem, it seems the problem was deeper in the code. As the code generated a full xml further deeper i found where to put in the element and it was solved.