[ERROR FIX:] The term ‘Add-SPSolution ’ is not recognized as the name of a cmdlet, function, script file, or operable program.

I. PROBLEM

For my today’s task, my onshore counterpart has given me a backup file (.bak) to restore on a new site collection. So I have decided to do a restore script in Powershell to accomplish this job.  I was about to run my script in Powershell, when I came across this error. .

“The term ‘Add-SPSolution ’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.”

I thought that Powershell can automatically interpret Microsoft. Sharepoint.Powershell commands, but I was wrong.

II. SOLUTION

On top of your powershell script, add a code to reference Microsoft.Sharepoint.Powershell so it will be made available to Powershell ISE.
Add the line below at the start of your powershell script.

Add-PSSnapin Microsoft.Sharepoint.Powershell –EA 0

But for a long-term solution, so you do not have to constantly adding the above line every time you write a script, you may refer to the site below.

http://blogs.msdn.com/b/kaevans/archive/2011/11/14/add-microsoft-sharepoint-powershell-snap-in-to-all-powershell-windows.aspx

HOW TO PRINT A DATATABLE TO PDF FORM USING MIGRADOC AND PDFSHARP

HOW TO PRINT A DATATABLE TO PDF FORM USING MIGRADOC AND PDFSHARP

I. INTRODUCTION
This post will detail out how to print a datatable in a PDF form using Migradoc and PDFSharp (http://www.pdfsharp.com/PDFsharp/).  This functionality can be accomplished using a regular web application but since I was asked to do it in Sharepoint, the step by step implementation will be via a Sharepoint site collection. Special thanks to Mr. John Arrieta who is the original creator of this POC.

II. SCOPE
This post assumes that there is already an existing site collection created in Sharepoint 2010. The content of the datatable doesn’t come from an external data source and was just hard-coded for the purpose of presentation.

III. REQUIREMENTS
- Sharepoint 2010
- Visual Studio 2010
- Migradoc and PDF Assemblies (required DLLs)

IV. IMPLEMENTATION

1.  Open Visual Studio and create an Empty Sharepoint Application. Input your Site Collection URL and click Finish. On my VM, the site collection URL is (http://valedevserver:3706)

2. Since we will be using third-party assemblies for the PDF printing, you have to download the required DLLs in (http://www.pdfsharp.com/PDFsharp/). In the Solution Explorer Toolbar, right-click on references and click on ‘Add Reference’.  On the dialog box, add the assemblies needed.

3. We will create a class that will contain the methods to print our datatable. On the Solution Explorer Toolbar, right-click on ‘Add’  and then ‘New Item’. Create a new class. In my solution, I have named my class to be PDFform.cs.

4. Add the reference in the using section on the newly created class file. This class file handles the creation and formatting of the PDF.

using System.Data;
using MigraDoc.DocumentObjectModel.Tables;
using MigraDoc.DocumentObjectModel.Shapes;

5. Replace the contents of your class with the codes below.

class PDFform
    {
        // The MigraDoc document that represents the invoice.
        Document document;
        DataTable dt;
        string path;
                
        // The text frame of the MigraDoc document that contains the address.
        TextFrame addressFrame;
/// The table of the MigraDoc document that contains the data
  Table table;

// Initializes a new instance of the class and opens the specified datasource
        public PDFform(DataTable dtIn, string pathIn)
        {
            dt = dtIn;
            path = pathIn;
        }
// Creates the document.
        public Document CreateDocument()
        {
            // Create a new MigraDoc document
            this.document = new Document();
            this.document.Info.Title = "";
            this.document.Info.Subject = "";
            this.document.Info.Author = "Aftab";
DefineStyles();
CreatePage();
FillContent();

return this.document;
        }
// Defines the styles used to format the MigraDoc document.
        void DefineStyles()
        {
            // Get the predefined style Normal.
            Style style = this.document.Styles["Normal"];
            // Because all styles are derived from Normal, the next line changes the 
            // font of the whole document. Or, more exactly, it changes the font of
            // all styles and paragraphs that do not redefine the font.
            style.Font.Name = "Verdana";
style = this.document.Styles[StyleNames.Header];
            style.ParagraphFormat.AddTabStop("16cm", TabAlignment.Right);
style = this.document.Styles[StyleNames.Footer];
            style.ParagraphFormat.AddTabStop("8cm", TabAlignment.Center);
// Create a new style called Table based on style Normal
            style = this.document.Styles.AddStyle("Table", "Normal");
            style.Font.Name = "Verdana";
            style.Font.Name = "Times New Roman";
            style.Font.Size = 9;
// Create a new style called Reference based on style Normal
            style = this.document.Styles.AddStyle("Reference", "Normal");
            style.ParagraphFormat.SpaceBefore = "5mm";
            style.ParagraphFormat.SpaceAfter = "5mm";
            style.ParagraphFormat.TabStops.AddTabStop("16cm", TabAlignment.Right);
        }
// Creates the static parts of the invoice.
        void CreatePage()
        {
            // Sets the PDF Page Orientation
            this.document.DefaultPageSetup.Orientation = Orientation.Landscape;
// Each MigraDoc document needs at least one section.
            Section section = this.document.AddSection();

// Put a logo in the header
            Image image = section.AddImage(path);
image.Top = ShapePosition.Top;
            image.Left = ShapePosition.Left;
            image.WrapFormat.Style = WrapStyle.Through;
// Create footer
            Paragraph paragraph = section.Footers.Primary.AddParagraph();
            paragraph.AddText("This is the footer section");
            paragraph.Format.Font.Size = 9;
            paragraph.Format.Alignment = ParagraphAlignment.Center;
// Create the text frame for the address
            this.addressFrame = section.AddTextFrame();
            this.addressFrame.Height = "3.0cm";
            this.addressFrame.Width = "7.0cm";
            this.addressFrame.Left = ShapePosition.Left;
            this.addressFrame.RelativeHorizontal = RelativeHorizontal.Margin;
            this.addressFrame.Top = "5.0cm";
            this.addressFrame.RelativeVertical = RelativeVertical.Page;
// Put sender in address frame
            paragraph = this.addressFrame.AddParagraph("Karachi,Pakistan");
            paragraph.Format.Font.Name = "Times New Roman";
            paragraph.Format.Font.Size = 7;
            paragraph.Format.SpaceAfter = 3;
// Add the print date field
            paragraph = section.AddParagraph();
            paragraph.Format.SpaceBefore = "6cm";
            paragraph.Style = "Reference";
            paragraph.AddFormattedText("Patients Detail", TextFormat.Bold);
            paragraph.AddTab();
            paragraph.AddText("Date, ");
            paragraph.AddDateField("dd.MM.yyyy");
// Create the item table
            this.table = section.AddTable();
            this.table.Style = "Table";
            this.table.Borders.Color = TableBorder;
            this.table.Borders.Width = 0.25;
            this.table.Borders.Left.Width = 0.5;
            this.table.Borders.Right.Width = 0.5;
            this.table.Rows.LeftIndent = 0;
// Before you can add a row, you must define the columns
            Column column;
            foreach (DataColumn col in dt.Columns)
            {
                column = this.table.AddColumn(Unit.FromCentimeter(3));
                column.Format.Alignment = ParagraphAlignment.Center;
            }
// Create the header of the table
            Row row = table.AddRow();
            row.HeadingFormat = true;
            row.Format.Alignment = ParagraphAlignment.Center;
            row.Format.Font.Bold = true;
            row.Shading.Color = TableBlue;

for (int i = 0; i < dt.Columns.Count; i++)
            {
                row.Cells[i].AddParagraph(dt.Columns[i].ColumnName);
                row.Cells[i].Format.Font.Bold = false;
                row.Cells[i].Format.Alignment = ParagraphAlignment.Left;
                row.Cells[i].VerticalAlignment = VerticalAlignment.Bottom;
            }
this.table.SetEdge(0, 0, dt.Columns.Count, 1, Edge.Box, BorderStyle.Single, 0.75, Color.Empty);
        }
// Creates the dynamic parts of the PDF
        void FillContent()
        {
            // Fill address in address text frame
            Paragraph paragraph = this.addressFrame.AddParagraph();
            paragraph.AddText("Dr. Anwar Ali");
            paragraph.AddLineBreak();
            paragraph.AddText("Health And Social Services ");
            paragraph.AddLineBreak();
            paragraph.AddText("Karachi");
            Row newRow;
for (int i = 0; i < dt.Rows.Count; i++)
            {
                // Format Cells and Display Records
                newRow = this.table.AddRow();
                newRow.TopPadding = 1.5;
for (int j = 0; j < dt.Columns.Count; j++)
                {
                    newRow.Cells[j].Shading.Color = TableGray;
                    newRow.Cells[j].VerticalAlignment = VerticalAlignment.Center;
newRow.Cells[j].Format.Alignment = ParagraphAlignment.Left;
                    newRow.Cells[j].Format.FirstLineIndent = 1;
                    newRow.Cells[j].AddParagraph(dt.Rows[i][j].ToString());
         this.table.SetEdge(0, this.table.Rows.Count - 2, dt.Columns.Count, 1, Edge.Box, BorderStyle.Single, 0.75);
                }
            }
        }
// Some pre-defined colors
#if true
        // RGB colors
        readonly static Color TableBorder = new Color(81, 125, 192);
        readonly static Color TableBlue = new Color(235, 240, 249);
        readonly static Color TableGray = new Color(242, 242, 242);
#else
    // CMYK colors
    readonly static Color tableBorder = Color.FromCmyk(100, 50, 0, 30);
    readonly static Color tableBlue = Color.FromCmyk(0, 80, 50, 30);
    readonly static Color tableGray = Color.FromCmyk(30, 0, 0, 0, 100);
#endif
    }

5. For the next step, we will create a webpart that will serve as our User Interface for this function. Right-click on our Sharepoint solution and click on ‘New Item’ and then select ‘Visual Webpart’.

6. Create a Gridview on the User Control. This will contain our hard-coded datatable and a button that will trigger our PDF printing. Our user interface will look something like the one below.

6. On the source code of the created visual webpart, add javascript function that creates a new browser window to display the generated PDF. Set the OnClientClick event of the button to the javascript function.

<script type="text/javascript">
        function PostToNewWindow() {
            originalTarget = document.forms[0].target;
            document.forms[0].target = '_blank';
            window.setTimeout("document.forms[0].target=originalTarget;", 300);
            return true;
        } 
</script>

<asp:Button ID="btnSaveViewPDF" runat="server" onclick="btnSaveViewPDF_Click"
    Text="Save  and View PDF" OnClientClick="return PostToNewWindow();" />

7. On the code-behind of the created visual webpart, add the following code on the PageLoad event and ButtonClick events. A script fix has been added on the PageLoad to handle viewing of PDF in a new browser window.  ButtonClick event handles the creation of the PDF by calling the PDFform class created earlier and saves it in the 14th hive of Sharepoint, then renders the saved PDF document on a new browser window.

DataTable dt = new DataTable();

        protected void Page_Load(object sender, EventArgs e)
        {
            // Script Fix for Viewing PDF in a new browser window
            ScriptManager.RegisterStartupScript(this.Page, this.GetType(), 
                "UpdatePanelFixup", "_spOriginalFormAction = document.forms[0].action; _spSuppressFormOnSubmitWrapper=true;", true);

// Sample Data
            dt.Columns.Add("Column 1", typeof(string));
            dt.Columns.Add("Column 2", typeof(string));
            dt.Columns.Add("Column 3", typeof(string));
            dt.Rows.Add("Data 1", "Data 2", "Data 3");
            dt.Rows.Add("Data 1", "Data 2", "Data 3");
            dt.Rows.Add("Data 1", "Data 2", "Data 3");

            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
      protected void btnSaveViewPDF_Click(object sender, EventArgs e)
        {
                // Create instance of PDFform class
                PDFform pdfForm = new PDFform(dt, "ImageLogo");
                // Create a MigraDoc document
                MigraDoc.DocumentObjectModel.Document document = pdfForm.CreateDocument();
                document.UseCmykColor = true;
                // Create a renderer for PDF that uses Unicode font encoding
                PdfDocumentRenderer pdfRenderer = new PdfDocumentRenderer(true);
                // Set the MigraDoc document
                pdfRenderer.Document = document;
                // Create the PDF document
                pdfRenderer.RenderDocument();
                // Save the PDF document...
                string filename = "PDFReport1.pdf";
                //14 Hive Path
                string spSetupPath = SPUtility.GetGenericSetupPath(string.Empty);
                pdfRenderer.Save(spSetupPath + filename); ;
                // ...and start a viewer.
                Response.Clear();
                Response.ClearContent();
                Response.ClearHeaders();
                //Set the appropriate ContentType.         
                Response.ContentType = "Application/pdf";
                //Get the physical path to the file.         
                Response.WriteFile(spSetupPath + filename);
                Response.Flush();
                Response.End();
        }


8. On this step, the solution is now ready to be deployed in Sharepoint. Right-click on our Sharepoint solution and click on Deploy.

9. To consume our Visual webpart, Edit a page and insert a webpart.  On the Categories section, Click on the Customs group. Add the custom webpart (VisualWebPart1) on the Sharepoint page.

10. Clicking the button will open a new browser window containing the generated PDF. Created PDF is located at the 14 hive.

HOW TO READ AN EXCEL DATA USING SHAREPOINT WEB SERVICES

INTRODUCTION
This post aims to describe how to access Excel File data using the built-in Excel webservice exposed by Sharepoint. It will also detail out how to export extracted data from an Excel file to a datatable.

SCOPE
This post assumes that the Excel File has already been uploaded into one of the document libraries (Shared Documents) in a site collection. It will not detail out how to upload Excel files in a document library.

REQUIREMENTS
-Sharepoint 2010
-Visual Studio 2010

IMPLEMENTATION


1.  Open Visual Studio and create an Empty Sharepoint Application. Input your Site Collection URL and click Finish.

2. On the Solution Explorer Toolbar, right-click on references and click on ‘Add Service Reference’. Click on the ‘Advanced’ button. On the dialog box, Click on Add Web Reference button.

3. On the Add Web Reference dialog box, access the excelservice.asmx. On my Virtual Machine, the URL is: ‘http://win-a80foe01p0e:4039/_vti_bin/excelservice.asmx’. Type in the name of the Web reference and click on the ‘Add Reference’ button.

4. In this tutorial, I have named my Web Reference to be ‘XLService’. This should now be available in the web references section in the Solution Explorer.

5. On your code-behind file, add a reference to the Excel Service in the Using section.

using TestExcelServices.XLService;

6. Inside your method, create an instance of the ExcelService class and set its URL and Credentials Properties. When the application calls the web service, the default network credentials need to be passed to the web service to get authenticated to use the data repository.

ExcelService objXL = new ExcelService();

objXL.Url = @"http://win-a80foe01p0e:4039/_vti_bin/excelservice.asmx";
objXL.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;

7. Next is to create the datatable that will be the container of the queried data from the Excel file. On this example, I will declare a datatable with 4 columns.

//Create Datatable Structure
DataTable dt = new DataTable();
dt.Columns.Add("Product", typeof(string));
dt.Columns.Add("ProductType", typeof(string));
dt.Columns.Add("Date", typeof(string));
dt.Columns.Add("TotalProductType", typeof(int));

8. Declare the Cell ranges of each of your fields on the datatable.

My Excel file looks something like the one below:

On this example, I will assign my cell ranges to be like this:
string ProductRange = "B2:B1000";
string ProductTypeRange = "C2:C1000";
string DateRange = "E2:E1000";
string TotalProductRange = "D2:D1000";

9. Get the SessionID by calling the OpenWorkbook method. Add on your code like the one below.

string sessionID = objXL.OpenWorkbook(@"http://win-a80foe01p0e:4039/Shared%20Documents/Test.xlsx", "en-US", "en-US", out wsStatus);

10. Call the GetRangeA1 method to query the excel data per range. So for my example, the following code should be implemented.

object[] ProductData = objXL.GetRangeA1(sessionID, sheetName, ProductRange, false, out wsStatus);
object[] ProductTypeData = objXL.GetRangeA1(sessionID, sheetName, ProductTypeRange, false, out wsStatus);
object[] DateData = objXL.GetRangeA1(sessionID, sheetName, DateRange, false, out wsStatus);
object[] TotalProductData = objXL.GetRangeA1(sessionID, sheetName, TotalProductRange, false, out wsStatus);

11. Formulate a For Loop that will browse through each of the generated objects and save it on the datatable that we have created earlier.

for (int i = 0; i < ProductData.Length; i++)
            {
                if (string.IsNullOrEmpty(Convert.ToString(((object[])(ProductData[i]))[0])))
                {
                    break;
                }
                newRow = dt.NewRow();
                newRow["Product"] = ((object[])(ProductData[i]))[0].ToString();
                newRow["ProductType"] = ((object[])(ProductTypeData[i]))[0].ToString();
                newRow["Date"] = ((object[])(DateData[i]))[0].ToString();
                newRow["TotalProductType"] = Convert.ToInt32(((object[])(TotalProductData[i]))[0]);

                dt.Rows.Add(newRow);
            }

12. Here is the complete code for this method.

13. After this call, we can now bind the resulting datatable in our gridview.

HOW TO DEPLOY INFOPATH FORMS USING POWERSHELL

INTRODUCTION

This post aims to describe how to deploy infopath forms as form templates in a sharepoint site using Powershell.

SCOPE

This post will not detail out how to create a custom infopath form. It will assume that there is already an existing Infopath source files that are for deployment.

  

REQUIREMENTS

Sharepoint 2010

Microsoft Infopath Designer 2010

IMPLEMENTATION

1.Locate the manifest.xml, right-click and click on Design. The Infopath Designer should load.

2.Whenever modifications have been made to the form, it is a good practice to change the version of the infopath form. This is because when you create a version 1.0 and so on for a form, all of the previous versions will be saved in Sharepoint. To do this, Click on File on the Infopath Form Designer. On the Info Ribbon tab, Click on Advanced Form Options.

The Form Option dialog box should appear.

3.Change the version on the Version field.

4.[Not Required] If you would like to create another set of source files for the modified Infopath form, you can do so by re-exporting the source files into another location.

Click File and select the Publish Ribbon. Click on the Export Source Files.

5.Select the folder where you want to save the Source Files. On the Folder field, type in the folder name where you want to save the files.

6.To publish your form into an Infopath .xsn format, Click on File and Click on the Info Ribbon Tab. On the Info Ribbon Tab, Click on the Quick Publish button.

7.Please take note on the location of the .xsn to be published. The directory is displayed on the right hand part of the Quick Publish section.

8.This operation will result in the creation of a .xsn file. A .xsn file defines the data structure, appearance, and behavior of Finished forms. This particular file is what is to be deployed in sharepoint.

9.Deploy Infopath form using Powershell. Click on Start>All Programs>Microsoft Sharepoint 2010 Products. Click on Sharepoint 2010 Management Shell.

10.To Uninstall the existing xsn Form Template. Type in the Management shell

Uninstall-SPInfoPathFormTemplate -Identity Exampleform.xsn

11.Install the new solution based on the .xsn file path. For example, the .xsn is located in drive c:/

Install-SPInfoPathFormTemplate -Path C:\Form.xsn

12.To disable the feature on the site collection level, type in the management shell. For example, the site collection URL is http://SPSite.

Disable-SPInfoPathFormTemplate -Identity "Form.xsn" -Site http://SPSite 

13.To enable the feature on the site collection level, type in the management shell. For example, the site collection URL is http://SPSite.

Enable-SPInfoPathFormTemplate -Identity "Form.xsn" -Site "http://SPSite"

14.Verify that the form was indeed deployed on the Sharepoint site. Browse to your site, make sure that you have the necessary rights to view the Form Templates. Click on Site Actions > View All Site Contents and Click on Form Templates.

15.The Infopath form should be on the List of the Form Templates.