Monday, January 2, 2017

Copy Ranges of Excel Sheet to Word Document using Office Interop

In the below sample I copy a cell Range in a Excel sheet into Word document using Interop

Refer the Excel and Word Office Interop DLLs

Browse and provide the DLL locations. For example:

C:\Program Files (x86)\Microsoft Visual Studio 12.0\Visual Studio Tools for Office\PIA\Office15\Microsoft.Office.Interop.Excel.dll

C:\Program Files (x86)\Microsoft Visual Studio 12.0\Visual Studio Tools for Office\PIA\Office15\Microsoft.Office.Interop.Word.dll

Add using directives

using Word = Microsoft.Office.Interop.Word;
using Excel = Microsoft.Office.Interop.Excel;

Declare a variable in your class

object oMissing = System.Reflection.Missing.Value;

In your method create a Excel Application to copy content and a Word application to paste into

            Excel._Application xlApp = new Excel.Application();
            xlApp.DisplayAlerts = false;
            xlApp.Visible = false;
         

            Word._Application wdApp = new Word.Application();
            wdApp.DisplayAlerts = Word.WdAlertLevel.wdAlertsNone;
            wdApp.Visible = false;
            Word.Document document = null;

            try
            {
                Excel.Workbook workbook = xlApp.Workbooks.Open(filePath);
                Excel.Worksheet worksheet = workbook.Sheets[8];

                document = wdApp.Documents.Add();

                worksheet.Range["A2", "I120"].Copy();
                document.Range().PasteSpecial();
                document.SaveAs2(@"C:\temp\1\Mydoc.docx", Word.WdSaveFormat.wdFormatDocumentDefault, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing);
                workbook.Close(false, oMissing, oMissing);

            }
            catch (Exception ex)
            {
                //TODO - log exception
            }

            finally
            {
                if (xlApp != null)
                    xlApp.Quit();
                if (document != null)
                    ((Microsoft.Office.Interop.Word._Document)document).Close(oMissing, oMissing, oMissing);
                if (wdApp != null)
                    wdApp.Quit();
            }

In the above code segment if you know the name of the workbook sheet instead of using he code line:
Excel.Worksheet worksheet = workbook.Sheets[8]; you could use a variable to hold the sheetName and update the code line as below:
Excel.Worksheet worksheet = workbook.Sheets[sheetName] as Excel.Worksheet;


Tuesday, December 20, 2016

Using Fiddler to test a POST call to a custom WCF REST service hosted in SharePoint 2013

Under Parse tab change the call type to "POST"
Content type should be set to json
In the "Request Body" specify the parameters to be sent as shown below


Fiddler Image:

To create a custom WCF REST service hosted in SharePoint 2013, follow this link

Saturday, October 29, 2016

Sending POST call to WCF REST web method - SharePoint

When it comes to SharePoint usage there are many examples in the web about REST GET calls, but not so much on POST. So here goes..

My target REST method signature is as follows

public List<Details> GetDetails(string ids)

The method declaration in the Interface is as below:

[OperationContract]
[WebInvoke(Method = "POST", UriTemplate = "GetDetails", BodyStyle = WebMessageBodyStyle.Wrapped,
 RequestFormat = WebMessageFormat.Json,
 ResponseFormat = WebMessageFormat.Json)]
List<Details> GetDetails(string ids);

The trick here is making the web message body style "Wrapped"

Now from the client-side you make a call to the method.
In my example I use the service invocation within a AngularJS function. You send the parameter information in the data

    $scope.TestMethod = function () {
        var endpointAddress = "http://myTestSite.com/sites/TestSite/_vti_bin/CustomService.svc";
        var callurl = endpointAddress + "/GetDetails";
        $.ajax({
                type: "POST",
                url: callurl,
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                data: '{"ids": "18"}',
                success: function () {
                    alert('success');
                },
                error: function () {
    alert('error');
}
            });

    };

Friday, October 28, 2016

Passing parameter containing special characters to web service methods

Web service does not process parameters with special characters as those can be candidates to dangerous inputs.

So as a workaround you can send the parameters as a query string. That said, the query string should not be too long since there are limitations

For illustration purposes, in the below example JavaScript call I send two variables id & date as method parameters. The address information which is included in variable address1 & address2 are send as  query string.

var restUrl = _spPageContextInfo.webAbsoluteUrl +
            "/_vti_bin/MyService.svc/MyMethod/" + id + "/" + date + "?address1=" + encodeURIComponent(address1) + "&address2=" + encodeURIComponent(address2) ;

Inside your web method access the query string as below:

string address = HttpContext.Current.Request.QueryString["address1"];

Sunday, September 4, 2016

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);
                        }
                    }
                }              
            }


Saturday, September 3, 2016

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");

Thursday, August 25, 2016

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.