Update tags in Word document with Data using OpenXML

The objective is to replace the specified tags in a word document(.docx) using OpenXml.
Below is a sample of the word document structure

1) Document content:

2) Footer:

The code sample is as follows:

            using (WordprocessingDocument doc = WordprocessingDocument.Open(@"C:\Users\dev5setup\Desktop\DEPT_TEST.docx", true))
            {
                Dictionary<string, string> tagMappings = GetTagMappingsForTemplate();

                IEnumerable<Paragraph> paras = doc.MainDocumentPart.Document.Descendants<Paragraph>();
                foreach (Paragraph para in paras)
                {
                    // get all runs under the paragraph and convert to be array
                    Run[] runArr = para.Descendants<Run>().ToArray();
                    // foreach each run
                    foreach (Run run in runArr)
                    {
                        string modifiedString = "";
                        int count = 0;
                        string innerText = run.InnerText;

                        #region replace tags
                        foreach (KeyValuePair<string, string> mapping in tagMappings)
                        {
                            if (count == 0)
                            {
                                modifiedString = run.InnerText.Replace(mapping.Key, mapping.Value);
                            }
                            else
                            {
                                modifiedString = modifiedString.Replace(mapping.Key, mapping.Value);
                            }                          
                            count++;
                        }
                        #endregion

                        // if the InnerText doesn't modify
                        if (modifiedString != run.InnerText)
                        {
                            Text t = new Text(modifiedString);
                            run.RemoveAllChildren<Text>();
                            run.AppendChild<Text>(t);
                        }
                    }
                }
                doc.MainDocumentPart.Document.Save();
             
                //Update the word document footer section
                FooterPart pp = doc.MainDocumentPart.FooterParts.ElementAt(0);
                IEnumerable<Paragraph> footerParas = pp.Footer.Descendants<Paragraph>();

                foreach (Paragraph footerPara in footerParas)
                {
                    // get all runs under the paragraph and convert to an array
                    Run[] runArr = footerPara.Descendants<Run>().ToArray();
                    // foreach each run
                    foreach (Run run in runArr)
                    {
                        string modifiedString = "";
                        int count = 0;
                        string innerText = run.InnerText;

                        #region replace tags
                        foreach (KeyValuePair<string, string> mapping in tagMappings)
                        {
                            if (count == 0)
                            {
                                modifiedString = run.InnerText.Replace(mapping.Key, mapping.Value);
                            }
                            else
                            {
                                modifiedString = modifiedString.Replace(mapping.Key, mapping.Value);
                            }
                            count++;
                        }
                        #endregion

                        // if the InnerText doesn't modify
                        if (modifiedString != run.InnerText)
                        {
                            Text t = new Text(modifiedString);
                            run.RemoveAllChildren<Text>();
                            run.AppendChild<Text>(t);
                        }
                    }
                }              
            }

Read a Excel Cell value using OpenXML

//DLL references in the solution
DocumentFormat.OpenXml
Microsoft.Office.Interop.Excel

//using directives
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

//Method      
public string ReadCellValue(string fileName, string sheetName, string addressName)
        {
            string value = null;
         
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
            {
                WorkbookPart myWorkBookPart = document.WorkbookPart;
                Sheet theSheet = myWorkBookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();

                if (theSheet == null)
                {
                    throw new ArgumentException("sheetName");
                }

                WorksheetPart myWorkSheetPart = (WorksheetPart)myWorkBookPart.GetPartById((theSheet.Id));
                Cell targetCell = myWorkSheetPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == addressName).FirstOrDefault();

                if (targetCell != null)
                {
                    value = targetCell.InnerText;

                    if (targetCell.DataType != null)
                    {
                        switch (targetCell.DataType.Value)
                        {
                            case CellValues.SharedString:

                                var stringTable = myWorkBookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                                if (stringTable != null)
                                {
                                    value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
                                }
                                break;

                            case CellValues.Boolean:
                                switch (value)
                                {
                                    case "0":
                                        value = "FALSE";
                                        break;
                                    default:
                                        value = "TRUE";
                                        break;
                                }
                                break;
                        }
                    }
                }
            }
            return value;
        }

//Call method parameters sample
ReadCellValue(@"C:\sample\My test workbook.xlsm", "Sheet Name", "E102");

Update Managed Property & Manually Re-Index a SharePoint Library

The SharePoint crawler picks up content that has changed since the last crawl and updates the search index.

When you change a managed property or when you change the mapping of crawled and managed properties then the site must be re-crawled before he changes are reflected in the search index. Since changes are made in the search schema and not to the actual site, the crawler will not automatically re-index. To make sure that the changes are crawled and fully re-indexed, you must request a re-indexing. The site content will be re-crawled and re-indexed so that you can start using the managed properties in queries, query rules and display templates.

In my case I updated several managed property. After the update a re-indexing of the SharePoint library was required for the items to show up in the search.

Mapping the Managed property with the Crawled property

• In the Central Administration go to the Search Service Application.
• Go to Search Schema
• Search for you Managed Property

• Click on property in the search results.
• Make the property Queryable so that this appear in search items
• Click on the "Add a mapping" button and map with the crawled property

[Note: you can do the mapping other way around too.. By searching the crawled property and map the managed property to the crawled property, depending on your scenario]

• Save changes

Re-Indexing the SharePoint library

• Go to the  library settings page of the target SP library.
• Select Advanced settings
• Scroll down and click on the "Reindex Document Library" button
• Click on "Reindex document library" button as shown below

• The re-indexing will happen in the next scheduled crawl.

Hide Elements Associated to Master pages in Dialogs

Once I had a minor UI issue with a Modal dialog. Yet I thought to blog about it as it may be useful. An image attached to the master page and a button in the page overlapped in a Modal popup. The issue was due to the fact that the image's z-index was higher than the button according to the build up of the DOM tree. Once of the options is to send the image back using the z-index. As a quick fix can adjust the styling of the div containing the image as:

.myImageDiv {

    z-index: -1;

}

Anyway decided to remove the image only for popups. How can this be done? Well, in SharePoint 2013 you can use the UI class "ms-dialogHidden" in your div's. So when the page is displayed in a Modal popup, the divs containing this CSS class are not displayed.

<div class="myImageDiv ms-dialogHidden"></div>

That's it ! 

Best practices when querying SharePoint lists/libraries

1) Golden rule - Never iterate through the whole list to filter/check-on couple of items in the list. List operations are heavy. Filter the required items needed maybe through a CAML query.

2) Use the SPQuery class if your objective is to get items from a single list

3) In your CAML query set ViewFields to filter the fields which you need. In the same way you can use SPList.GetItemByIdSelectedFields() method to get selected fields. Setting ViewFields makes the query more efficient

4) Use the SPSiteDataQuery class to get results from multiple lists. Here you can specify the web and lists which you are targeting at.

Example:

SPSiteDataQuery myQuery = new SPSiteDataQuery();
myQuery.query = "Your CAML query goes here";
myQuery.Webs = @"<Webs Scope=""SiteCollection"" />"; //Returns data from all webs in the current site collection
//myQuery.Webs = @"<Webs Scope=""Recursive"" />"; // will return from current web and its child webs

myQuery.Lists = @"<Lists ServerTemplate=""101"" />"; //101 refers to document libraries
myQuery.ViewFields =  @"
                                            <FieldRef Name=""Field One"" />
                                            <FieldRef Name=""Field Two"" />
                                           ";

Saving lookup meta data values to library using Powershell - SharePoint

When you take an item in a list, the Lookup fields store its value in the following format
id;#string

when programmatically adding meta data to a list's column the lookup value has to be built before assigning to the item field.

In the below example I refer one lookup, get its value/string. Using the string, do a search for the lookup list item id in the lookup list. Then create the new lookup field. Finally update item


  $myLookupItem = [Microsoft.SharePoint.SPFieldLookupValue]($_["FieldOne_InternalName"])
  $newLookupId = GetLookupId $myLookupItem.LookupValue

  $newLookupItem = New-Object Microsoft.SharePoint.SPFieldLookupValue($newLookupId  ,$myLookupItem.LookupValue)

$_["FieldTwo_InternalName"] = $newLookupItem

$_.Update()
$_.File.CheckIn(" FieldTwo value updated", 1)

$_ is explained in an earlier post

Query SharePoint Libraries using CAML query and Powershell

$web = Get-SPWeb $siteUrl
$list = $web.Lists[$listName]

1) Query items in list

if($list -ne $null)
{

$caml = '<OrderBy><FieldRef Name="ID" Ascending="True" /></OrderBy><Where><And> <Geq> <FieldRef Name="ID" /><Value Type="Number">{0}</Value> </Geq><Leq><FieldRef Name="ID" /><Value Type="Number">{1}</Value></Leq> </And> </Where> ' -f $startValue,$endValue

$myQuery = new-object Microsoft.SharePoint.SPQuery
$myQuery.Query = $caml

$filteredItems = $list.GetItems($myQuery)

$filteredItems | ForEach-Object {

      #Assign meta data to variables
      $variableOne = $_["MetaDataOne_InternalName"]
}

}

2) Remove specific items from list

if($list -ne $null)
{

$caml='<Where> <Or> <Or> <Or> <Eq> <FieldRef Name="Title" /><Value Type="Text">{0}</Value> </Eq> <Eq> <FieldRef Name="Title" /><Value Type="Text">{1}</Value> </Eq> </Or> <Eq> <FieldRef Name="Title" /><Value Type="Text">{2}</Value> </Eq> </Or> <Eq> <FieldRef Name="Title" /><Value Type="Text">{3}</Value> </Eq> </Or> </Where> ' -f $itemOne, $itemTwo, $itemThree, $itemFour

$query=new-object Microsoft.SharePoint.SPQuery
$query.Query=$caml
$col=$list.GetItems($query)

Write-Host 'Number of items removed: ' $col.Count

$col | % {$list.GetItemById($_.Id).Delete()}

}

$web.Dispose()

for more info about CAML query syntax visit here