Merging documents into a single document using Open XML SDK 2.0 and Word Automation Services

By peter.stilgoe









The code below will create a webpart that can be added to any document library, when the button ‘Merge Reports’ is clicked it will merge all the documents together in the library in to a single document using Open XML SDK & Word Automation Services.

You will need to add the following references:

DocumentFormat.OpenXml
Microsoft.Office.Word.Server
Microsoft.Sharepoint
Microsoft.Sharepoint.Client
Microsoft.Sharepoint.Client.Runtime
Windows.Base

using System;
using System.ComponentModel;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;
using System.Linq;
using System.IO;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;
using System.Text;
using Microsoft.SharePoint.Client;
using ClientOM = Microsoft.SharePoint.Client;
using Word = Microsoft.Office.Word.Server;
using Microsoft.Office.Word.Server.Conversions;

namespace DocumentMerge.VisualWebPart1
{
    [ToolboxItemAttribute(false)]
    public class VisualWebPart1 : WebPart
    {
        // Visual Studio might automatically update this path when you change the Visual Web Part project item.
        private const string _ascxPath = @"~/_CONTROLTEMPLATES/DocumentMerge/VisualWebPart1/VisualWebPart1UserControl.ascx";

        protected override void CreateChildControls()
        {
            System.Web.UI.Control control = Page.LoadControl(_ascxPath);
            Controls.Add(control);
            base.CreateChildControls();
            Button btnSubmit = new Button();
            btnSubmit.Text = "Merge Reports";
            btnSubmit.Click += new EventHandler(OnSubmitClick);
            Controls.Add(btnSubmit);
        }

        void OnSubmitClick(object sender, EventArgs e)
        {
            // String to store the output path
            string outputPath = string.Format(@"{0}/masterreport-{1}.docx", SPContext.Current.RootFolderUrl, DateTime.Now.ToString("ddMMyyyy"));

            // String containing the blank document part for our new DOCX
            string strEmptyMainPart = "<?xml version='1.0' encoding='UTF-8' standalone='yes'?>" +
            "<w:document xmlns:w='http://schemas.openxmlformats.org/wordprocessingml/2006/main'>" +
            "<w:body><w:p><w:r><w:t></w:t></w:r></w:p></w:body></w:document>";

            // In-memory stream for our consolidated WSR DOCX.
            MemoryStream memOut = new MemoryStream();

            // Out output document's OpenXML object
            WordprocessingDocument outputDoc = WordprocessingDocument.Create(memOut, DocumentFormat.OpenXml.WordprocessingDocumentType.Document);

            MainDocumentPart mainPart = outputDoc.AddMainDocumentPart();

            Stream partStream = mainPart.GetStream();
            UTF8Encoding encoder = new UTF8Encoding();

            // Add our blank main part string to the newly created document
            Byte[] buffer = encoder.GetBytes(strEmptyMainPart);
            partStream.Write(buffer, 0, buffer.Length);

            // Save the document in memory
            mainPart.Document.Save();

            SPListItemCollection files = SPContext.Current.List.Items;

            int id = 1;
            foreach (SPListItem item in files)
            {
                SPFile inputFile = item.File;

                string altChunkId = "AltChunkId" + id;
                id++;
                byte[] byteArray = inputFile.OpenBinary();

                AlternativeFormatImportPart chunk = outputDoc.MainDocumentPart.AddAlternativeFormatImportPart(AlternativeFormatImportPartType.WordprocessingML,
                    altChunkId);

                using (MemoryStream mem = new MemoryStream())
                {
                    mem.Write(byteArray, 0, (int)byteArray.Length);
                    mem.Seek(0, SeekOrigin.Begin);
                    chunk.FeedData(mem);
                }

                AltChunk altChunk = new AltChunk();
                altChunk.Id = altChunkId;

                outputDoc.MainDocumentPart.Document.Body.InsertAfter(altChunk,
                    outputDoc.MainDocumentPart.Document.Body.Elements<Paragraph>().Last());
                outputDoc.MainDocumentPart.Document.Save();
            }

            outputDoc.Close();

            memOut.Seek(0, SeekOrigin.Begin);

            ClientContext clientContext = new ClientContext(SPContext.Current.Site.Url);
            ClientOM.File.SaveBinaryDirect(clientContext, outputPath, memOut, true);

            // Conversion

            string docPath = string.Format(@"{0}{1}", SPContext.Current.Site.Url.Replace(@"\\", ""), outputPath);
            //string pdfPath = docPath.Replace(".docx", ".pdf");

            ConversionJobSettings JobSettings = new ConversionJobSettings();
            JobSettings.OutputFormat = SaveFormat.Document;
            JobSettings.OutputSaveBehavior = SaveBehavior.AlwaysOverwrite;

            ConversionJob ConvJob = new ConversionJob("Word Automation Services", JobSettings);
            ConvJob.UserToken = SPContext.Current.Site.UserToken;
            ConvJob.AddFile(docPath, docPath);
            ConvJob.Start();

        }
    }
}

Share

Leggi tutto

How to create an Excel Workbook from an SQL database using Open XML SDK 2.0

By peter.stilgoe









Below is the C# code to create a new Excel workbook based on a template file & populated by an SQL database.

You need to add references to:

DocumentFormat.OpenXml
System.Data.Linq
Windows.Base

using System;
using System.IO;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;

namespace ElitetoExcel
{
    class Program
    {
        static string[] headerColumns =
            new string[] { "A", "B", "C", "D", "E"};

        static void Main(string[] args)
        {
            //Make a copy of the template file
            File.Copy(@"c:\templatedoc.xlsx", @"c:\newdoc.xlsx", true);

            //Open up the copied workbook
            using (SpreadsheetDocument myWorkbook =
                SpreadsheetDocument.Open(@"c:\newdoc.xlsx", true))
            {
                //Access the main workbook part which contains all the references
                WorkbookPart workbookPart = myWorkbook.WorkbookPart;

                //Grab the first worksheet
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

                //Sheet data will contain all the data
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                //Connect to Database LINQ
                DataClasses1DataContext
                DataClasses1DataContext = new
                DataClasses1DataContext();
                int index = 2;

                //select * from SPOpenMatters table
                var MatterQuery = from t in DataClasses1DataContext.SPOpenMatters
                                     select t;

                //For each record in database add row in spreadsheet

                Console.WriteLine("Creating Excel report...........");

                foreach (var item in MatterQuery)
                {
                    string MatterNum = item.matterno;
                    string Description = item.description;
                    string OpenedDate = item.openeddate.ToString();
                    string MatterStatus = item.matterstatus;
                    string BillingTKPR = item.billingTKPR;

                    //Add a new row
                    Row contentRow =
                        CreateContentRow(index, MatterNum, Description, OpenedDate, MatterStatus, BillingTKPR);
                    index++;

                    //Append new row to sheet data
                    sheetData.AppendChild(contentRow);
                }
                worksheetPart.Worksheet.Save();
            }
        }

        private static Row CreateContentRow(int index, string MatterNum, string Description, string OpenedDate, string MatterStatus, string BillingTKPR)
        {
            //Create new row
            Row r = new Row();
            r.RowIndex = (UInt32)index;

            //First cell is a text cell, so create it and append it
            Cell firstCell =
                CreateTextCell(headerColumns[0], MatterNum, index);
            r.AppendChild(firstCell);//

            //create cells that contain data
            for (int i = 1; i < headerColumns.Length; i++)
            {
                Cell c = new Cell();
                c.CellReference = headerColumns[i] + index;
                CellValue v = new CellValue();
                if (i == 1)

                    v.Text = Description.ToString();

                if (i == 2)
                    v.Text = Convert.ToDateTime(OpenedDate).ToString("D");

                if (i == 3)
                    v.Text = MatterStatus.ToString();

                if (i == 4)
                    v.Text = BillingTKPR.ToString();

                c.AppendChild(v);
                r.AppendChild(c);

            }
            return r;
        }

        private static Cell CreateTextCell(string header,
            string MatterNum, int index)
        {
            //Create a new inline string cell
            Cell c = new Cell();
            c.DataType = CellValues.InlineString;
            c.CellReference = header + index;

            //Add text to text cell
            InlineString inlineString = new InlineString();
            Text t = new Text();
            t.Text = MatterNum;
            inlineString.AppendChild(t);
            c.AppendChild(inlineString);
            return c;
        }
    }
}




Share

Leggi tutto

Word Automation Services – Convert a Word 2010 Doc to PDF

By peter.stilgoe









The following example provides the complete C# listing for the simplest Word Automation Services application

You need to add references to WindowsBase, Microsoft.Office.Word.Server & Microsoft.Sharepoint

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.Office.Word.Server.Conversions;

class Program
{
    static void Main(string[] args)
    {
        string siteUrl = "http://localhost";
        // If you manually installed Word automation services, then replace the name
        // in the following line with the name that you assigned to the service when
        // you installed it.
        string wordAutomationServiceName = "Word Automation Services";
        using (SPSite spSite = new SPSite(siteUrl))
        {
            ConversionJob job = new ConversionJob(wordAutomationServiceName);
            job.UserToken = spSite.UserToken;
            job.Settings.UpdateFields = true;
            job.Settings.OutputFormat = SaveFormat.PDF;
            job.AddFile(siteUrl + "/Shared%20Documents/Test.docx",
                siteUrl + "/Shared%20Documents/Test.pdf");
            job.Start();
        }
    }
}

Share

Leggi tutto

Modifying an Open XML Document in a SharePoint Document Library

By peter.stilgoe









C# Console Application code to query a Sharepoint Library, Open a document, Make a change & then save the document.

You need references to WindowsBase.dll, Microsoft.Sharepoint & DocumentFormat.OpenXml

You also need to ensure your project is .Net 3.5 & set to target ‘Any CPU’ in the project properties.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using Microsoft.SharePoint;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;

class Program
{
 static void Main(string[] args)
 {
 string siteUrl = "http://localhost";
 using (SPSite spSite = new SPSite(siteUrl))
 {
 Console.WriteLine("Querying for Test.docx");
 SPList list = spSite.RootWeb.Lists["Shared Documents"];
 SPQuery query = new SPQuery();
 query.ViewFields = @"<FieldRef Name='FileLeafRef' />";
 query.Query =
 @"<Where>
 <Eq>
 <FieldRef Name='FileLeafRef' />
 <Value Type='Text'>Test.docx</Value>
 </Eq>
 </Where>";
 SPListItemCollection collection = list.GetItems(query);
 if (collection.Count != 1)
 {
 Console.WriteLine("Test.docx not found");
 Environment.Exit(0);
 }
 Console.WriteLine("Opening");
 SPFile file = collection[0].File;
 byte[] byteArray = file.OpenBinary();
 using (MemoryStream memStr = new MemoryStream())
 {
 memStr.Write(byteArray, 0, byteArray.Length);
 using (WordprocessingDocument wordDoc =
 WordprocessingDocument.Open(memStr, true))
 {
 Document document = wordDoc.MainDocumentPart.Document;
 Paragraph firstParagraph = document.Body.Elements<Paragraph>()
 .FirstOrDefault();
 if (firstParagraph != null)
 {
 Paragraph testParagraph = new Paragraph(
 new Run(
 new Text("Test")));
 firstParagraph.Parent.InsertBefore(testParagraph,
 firstParagraph);
 }
 }
 Console.WriteLine("Saving");
 string linkFileName = file.Item["LinkFilename"] as string;
 file.ParentFolder.Files.Add(linkFileName, memStr, true);
 }
 }
 }
}

Code written by Eric White

Share

Leggi tutto

How to auto populate Word template using Open XML SDK 2.0

By peter.stilgoe









Here is some simple code that updates a word document template that contains 2 content controls in the template document called:

MatterNumber
DocType

The template document is – C:\Users\stilgoep\WordTest\test-contentcontrol.docx

The output document is – C:\Users\stilgoep\WordTest\test-contentcontrol-updated.docx

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using System.Xml;
using System.IO;

namespace BasicOpenXMLUpdater
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Starting up Word template updater ...");

            //get path to template and instance output
            string docTemplatePath = @"C:\Users\stilgoep\WordTest\test-contentcontrol.docx";
            string docOutputPath = @"C:\Users\stilgoep\WordTest\test-contentcontrol-updated.docx";

            //create copy of template so that we don't overwrite it
            File.Copy(docTemplatePath, docOutputPath);

            Console.WriteLine("Created copy of template ...");

            //stand up object that reads the Word doc package
            using (WordprocessingDocument doc = WordprocessingDocument.Open(docOutputPath, true))
            {
                //create XML string matching custom XML part
                string newXml = "<root>" +
                    "<MatterNumber>00011111</MatterNumber>" +
                    "<DocType>Contract</DocType>" +
                    "</root>";

                MainDocumentPart main = doc.MainDocumentPart;
                main.DeleteParts<CustomXmlPart>(main.CustomXmlParts);

                //add and write new XML part
                CustomXmlPart customXml = main.AddCustomXmlPart(CustomXmlPartType.CustomXml);
                using (StreamWriter ts = new StreamWriter(customXml.GetStream()))
                {

                    ts.Write(newXml);
                }

                //closing WordprocessingDocument automatically saves the document
            }

            Console.WriteLine("Done");
            Console.ReadLine();

        }
    }
}

You need to add the following 2 references:

DocumentFormat.OpenXml
WindowsBase.dll

You will also need to download the Word Content Control Toolkit from CodePlex to map your content controls to your XML data.

Share

Leggi tutto