European Sharepoint 2013 Hosting Blog

SharePoint 2013 Hosting – HostForLIFE.eu :: ReactHooks with Spfx

Nov 14th

Posted by Peter in European SharePoint 2013 Hosting

Hooks Overview

React Hooks are a way to add React.Component features to functional components. Features include:

State
Lifecycle

Hooks let you use React’s features without classes.

What is this useState() syntax?

You may be unfamiliar with the useState() syntax. This uses destructuring assignment for arrays. It is similar to how we would pull props out an object with object destructuring.

What does useState() give us?

useState gives us two variables. We can name our two variables whatever we want, just know that:

The first variable is the value. Similar to this.state
The second variable is a function to update that value. Similar to this.setState
The final part of useState is the argument that we pass to it. The useState argument is the initial state value. In the case of our counter, we started at 0.

What’s wrong with classes?

The React Hooks intro gives a good section on this: Classes confuse both people and machines. Classes can sometimes be confusing and be written in any number of ways. Dive into somebody else’s project and you could be in for a world of different syntax and style choices. Though there are no plans to remove class support. we just have another way to code.

By allowing classes to be converted into smaller functional components, we can further break out parts of our application into more focused components.

React’s Effect Hook

The State Hook allows us to use state in React functional components. This gets us a step closer to using functional components over class components. The next part of moving to functional components is lifecycle methods.

Effects are similar to componentDidMount, componentDidUpdate, and componentWillUnmount

This is what the Effect Hook is for. Side-effects are things you want your application to do such as:

Fetch data
Manually change the DOM (document title)
Set up a subscription
Run effects after every render

Running an Effect Hook only when something changes

Since useEffect() runs every time a component renders, how do we get it to only run once on mount? The Effect Hook can take a second argument, an array. It will look through the array and only run the effect if one of the values has changed.

componentDidMount: Runs once  
// only run on mount. pass an empty array  
useEffect(() => {  
  // only runs once  
}, []);  
componentDidUpdate: Runs on changes  
// only run if count changes  
useEffect(  
  () => {  
    // run here if count changes  
  },  
  [count]  
);

componentDidMount: Runs once

// only run on mount. pass an empty array

useEffect(() => {

// only runs once

}, []);

componentDidUpdate: Runs on changes

// only run if count changes

useEffect(

() => {

// run here if count changes

[count]

);

What about componentWillUnmount()

To run something before a component unmounts, we just have to return a function from useEffect()

Using State and Effects Together

There’s no problem using them together! Together they can create functional components that work the same as your class components!

Open a command prompt. Create a directory for SPFx solution.

md spfx-React-Hooks

Navigate to the newly created directory above.

cd spfx-React-Hooks

Run the Yeoman SharePoint Generator to create the solution.

yo @microsoft/sharepoint

Solution Name

Hit Enter to have default name (spfx-pnp-DropDown in this case) or type in any other name for your solution.

Selected choice – Hit Enter

Target for the component

Here, we can select the target environment where we are planning to deploy the client web part, i.e., SharePoint Online or SharePoint OnPremise (SharePoint 2016 onwards).

Selected choice – SharePoint Online only (latest)

Place of files

We may choose to use the same folder or create a subfolder for our solution.

Selected choice – Same folder

Deployment option

Selecting Y will allow the app to deploy instantly to all sites

Selected choice – N (install on each site explicitly)

Permissions to access web APIs

Choose if the components in the solution require permission to access web APIs that are unique and unshared with other components in the tenant.

Selected choice – N (solution contains unique permissions)

Type of client-side component to create

We can choose to create a client-side web part or an extension. Choose the web part option.

Selected choice – WebPart

Web part name

Hit Enter to select the default name or type in any other name.

Selected choice – SpfxReactHooksCrud

Web part description

Hit Enter to select the default description or type in any other value.

Framework to use

Select any JavaScript framework to develop the component. Available choices are – No JavaScript Framework, React, and Knockout.

Selected choice – React

The yeoman generator will perform a scaffolding process to generate the solution. The scaffolding process will take a significant amount of time.

Once the scaffolding process is completed, lock down the version of the project dependencies by running below command.

npm shrinkwrap

In the command prompt, type the below command to open the solution in the code editor of your choice.

code .

NPM Packages Used,

On the command prompt, run below command.

npm i @pnp/logging @pnp/common @pnp/odata @pnp/sp –save

for Polyfills

npm install –save @pnp/polyfill-ie11

in SpfxReactHooksCrudWebPart.ts,

import * as React from 'react';  
import * as ReactDom from 'react-dom';  
import { Version } from '@microsoft/sp-core-library';  
import {  
  BaseClientSideWebPart,  
  IPropertyPaneConfiguration,  
  PropertyPaneTextField  
} from '@microsoft/sp-webpart-base';  
  
import * as strings from 'SpfxReactHooksCrudWebPartStrings';  
import SpfxReactHooksCrud from './components/SpfxReactHooksCrud';  
import { ISpfxReactHooksCrudProps } from './components/ISpfxReactHooksCrudProps';  
import "@pnp/polyfill-ie11";  
import { sp, Web } from '@pnp/sp';   
  
export interface ISpfxReactHooksCrudWebPartProps {  
  description: string;  
}  
  
export default class SpfxReactHooksCrudWebPart extends BaseClientSideWebPart<ISpfxReactHooksCrudWebPartProps> {  
  protected onInit(): Promise<void> {  
    return new Promise<void>((resolve: () => void, reject: (error?: any) => void): void => {  
      sp.setup({  
        sp: {  
          headers: {  
            "Accept": "application/json; odata=nometadata"  
          }  
        }  
      });  
      resolve();  
    });  
  }  
  public render(): void {  
    const element: React.ReactElement<ISpfxReactHooksCrudProps > = React.createElement(  
      SpfxReactHooksCrud,  
      {  
        description: this.properties.description,  
        context: this.context    
      }  
    );  
  
    ReactDom.render(element, this.domElement);  
  }  
  
  protected onDispose(): void {  
    ReactDom.unmountComponentAtNode(this.domElement);  
  }  
  
  protected get dataVersion(): Version {  
    return Version.parse('1.0');  
  }  
  
  protected getPropertyPaneConfiguration(): IPropertyPaneConfiguration {  
    return {  
      pages: [  
        {  
          header: {  
            description: strings.PropertyPaneDescription  
          },  
          groups: [  
            {  
              groupName: strings.BasicGroupName,  
              groupFields: [  
                PropertyPaneTextField('description', {  
                  label: strings.DescriptionFieldLabel  
                })  
              ]  
            }  
          ]  
        }  
      ]  
    };  
  }  
}

import * as React from 'react';

import * as ReactDom from 'react-dom';

import { Version } from '@microsoft/sp-core-library';

import {

BaseClientSideWebPart,

IPropertyPaneConfiguration,

PropertyPaneTextField

} from '@microsoft/sp-webpart-base';

import * as strings from 'SpfxReactHooksCrudWebPartStrings';

import SpfxReactHooksCrud from './components/SpfxReactHooksCrud';

import { ISpfxReactHooksCrudProps } from './components/ISpfxReactHooksCrudProps';

import "@pnp/polyfill-ie11";

import { sp, Web } from '@pnp/sp';

export interface ISpfxReactHooksCrudWebPartProps {

description: string;

}

export default class SpfxReactHooksCrudWebPart extends BaseClientSideWebPart<ISpfxReactHooksCrudWebPartProps> {

protected onInit(): Promise<void> {

return new Promise<void>((resolve: () => void, reject: (error?: any) => void): void => {

sp.setup({

sp: {

headers: {

"Accept": "application/json; odata=nometadata"

}

});

resolve();

});

}

public render(): void {

const element: React.ReactElement<ISpfxReactHooksCrudProps > = React.createElement(

SpfxReactHooksCrud,

{

description: this.properties.description,

context: this.context

}

);

ReactDom.render(element, this.domElement);

}

protected onDispose(): void {

ReactDom.unmountComponentAtNode(this.domElement);

}

protected get dataVersion(): Version {

return Version.parse('1.0');

}

protected getPropertyPaneConfiguration(): IPropertyPaneConfiguration {

return {

pages: [

{

header: {

description: strings.PropertyPaneDescription

groups: [

{

groupName: strings.BasicGroupName,

groupFields: [

PropertyPaneTextField('description', {

label: strings.DescriptionFieldLabel

})

]

}

]

}

]

};

}

in HookUsestate.ts

export interface IHooksuseState {    
  MileStone?:any;  
    Id?:any;  
    Title?:any;  
}   
/*interface ServiceInit { 
    mysttt: 'init'; 
  } 
  interface ServiceLoading { 
    status: 'loading'; 
  } 
  interface ServiceLoaded<T> { 
    status: 'loaded'; 
    payload: T; 
  } 
  interface ServiceError { 
    status: 'error'; 
    error: Error; 
  } 
  export type Service<T> = 
    | ServiceInit 
    | ServiceLoading 
    | ServiceLoaded<T> 
    |IHooksuseState 
    | ServiceError;*/

export interface IHooksuseState {

MileStone?:any;

Id?:any;

Title?:any;

}

/*interface ServiceInit {

mysttt: 'init';

}

interface ServiceLoading {

status: 'loading';

}

interface ServiceLoaded<T> {

status: 'loaded';

payload: T;

}

interface ServiceError {

status: 'error';

error: Error;

}

export type Service<T> =

| ServiceInit

| ServiceLoading

| ServiceLoaded<T>

|IHooksuseState

| ServiceError;*/

in ISpfxReactHooksCrudProps.ts

import {WebPartContext} from '@microsoft/sp-webpart-base';  
export interface ISpfxReactHooksCrudProps {  
  description: string;  
  context: WebPartContext;  
}

import {WebPartContext} from '@microsoft/sp-webpart-base';

export interface ISpfxReactHooksCrudProps {

description: string;

context: WebPartContext;

}

in SpfxReactHooksCrud.tsx

import * as React from 'react';  
/*import styles from './SpfxReactHooksCrud.module.scss';*/  
import { ISpfxReactHooksCrudProps } from './ISpfxReactHooksCrudProps';  
import { escape } from '@microsoft/sp-lodash-subset';  
import { MyListComponent } from './MyList';  
const UserContext = React.createContext([]);  
export default class SpfxReactHooksCrud extends React.Component<ISpfxReactHooksCrudProps, {}> {  
  public render(): React.ReactElement<ISpfxReactHooksCrudProps> {  
    return (  
      <div >  
        <MyListComponent />  
      </div>  
    );  
  }  
}

import * as React from 'react';

/*import styles from './SpfxReactHooksCrud.module.scss';*/

import { ISpfxReactHooksCrudProps } from './ISpfxReactHooksCrudProps';

import { escape } from '@microsoft/sp-lodash-subset';

import { MyListComponent } from './MyList';

const UserContext = React.createContext([]);

export default class SpfxReactHooksCrud extends React.Component<ISpfxReactHooksCrudProps, {}> {

public render(): React.ReactElement<ISpfxReactHooksCrudProps> {

return (

<div >

</div>

);

}

in MyList.tsx

import * as React from 'react';  
import { SpListCollection } from './mySpListCollection';  
  
export const MyListComponent = () => {  
  const { datacol, loaddata } = SpListCollection();  
  React.useEffect(() => {  
    loaddata();  
  }, []);  
  return (  
    <div>  
      <DataBinding DataNewCol={datacol} />  
  
    </div>  
  );  
};  
const  DataBinding = (props) => {  
  //function DataBinding(props) {  
  const DataNewCol = props.DataNewCol;  
  const listItems = DataNewCol.map((data, index) =>  
    <div key={index}>  
      <h3>{data.Title}</h3>  
      <p>{data.MileStone}</p>  
      <p>{data.Id}</p>  
    </div>  
  );  
  return (  
    <div>{listItems}</div>  
  );  
};

import * as React from 'react';

import { SpListCollection } from './mySpListCollection';

export const MyListComponent = () => {

const { datacol, loaddata } = SpListCollection();

React.useEffect(() => {

loaddata();

}, []);

return (

<div>

</div>

);

};

const DataBinding = (props) => {

//function DataBinding(props) {

const DataNewCol = props.DataNewCol;

const listItems = DataNewCol.map((data, index) =>

<h3>{data.Title}</h3>

<p>{data.MileStone}</p>

</div>

);

return (

<div>{listItems}</div>

);

};

in mySpListCOllection.tsx

import * as React from 'react';  
import { sp } from "@pnp/sp";  
import { IHooksuseState } from './HooksuseState';  
export const SpListCollection = () => {  
  const [datacol, setdatacol] = React.useState([]);  
  const loaddata = () => {  
    sp.web.lists.getByTitle("ProjectStatus").items.select('Title', 'Id', 'MileStone').get().then((items) => {  
      let result: IHooksuseState[] = [];  
      items.forEach(element => {  
        result.push({  
          Id: element.Id, Title: element.Title, MileStone: element.MileStone  
        });  
      });  
      return result;  
    }) .then(resultdata => setdatacol(resultdata));  
  };  
  return { datacol, loaddata, setdatacol };  
};

import * as React from 'react';

import { sp } from "@pnp/sp";

import { IHooksuseState } from './HooksuseState';

export const SpListCollection = () => {

const [datacol, setdatacol] = React.useState([]);

const loaddata = () => {

sp.web.lists.getByTitle("ProjectStatus").items.select('Title', 'Id', 'MileStone').get().then((items) => {

let result: IHooksuseState[] = [];

items.forEach(element => {

result.push({

Id: element.Id, Title: element.Title, MileStone: element.MileStone

});

return result;

}) .then(resultdata => setdatacol(resultdata));

};

return { datacol, loaddata, setdatacol };

};

Here, I am using “ProjectStatus” as a list name with “MileStone,Id,Title” as Field Names.

SharePoint 2013 Hosting – HostForLIFE.eu :: Creating a BalkanGraph OrgChart with Spfx

Nov 7th

Posted by Peter in European SharePoint 2013 Hosting

Open a command prompt. Create a directory for SPFx solution.

md spfx-React-BalkanGraphChart

Navigate to the above-created directory.

cd spfx-React-BalkanGraphChart

Run the Yeoman SharePoint Generator to create the solution.

yo @microsoft/sharepoint

Solution Name

Hit Enter for the default name (spfx-pnp-DropDown in this case,) or type in any other name for your solution.

Selected Choice – Hit Enter

Target for the Component

Here, we can select the target environment where we are planning to deploy the client web part, i.e., SharePoint Online or SharePoint OnPremise (SharePoint 2016 onwards).

Selected Choice – SharePoint Online only (latest)

File Location

We may choose to use the same folder or create a subfolder for our solution.

Selected Choice – Same folder

Deployment Option

Selecting Y will allow the app to be deployed instantly to all sites and accessible everywhere.

Selected Choice: N (install on each site explicitly)

Permissions to access Web APIs

Choose if the components in the solution require permissions to access web APIs which are unique and not shared with other components in the tenant.

Selected choice: N (solution contains unique permissions)

Type of client-side component to create

We can choose to create a client-side web part or an extension. Choose the web part option.

Selected Choice – web part

Web part name

Hit Enter to select the default name or type in any other name.

Selected Choice – BalkanOrgChart

Web part description

Hit Enter to select the default description or type in any other value.

Framework to use

Select any JavaScript framework to develop the component. Available choices are – No JavaScript Framework, React, and Knockout.

Selected Choice – React

Yeoman generator will perform a scaffolding process to generate the solution. The scaffolding process will take a significant amount of time.

Once the scaffolding process is completed, lock down the version of project dependencies by running the below command:

npm shrinkwrap

In the command prompt, type below command to open the solution in the code editor of your choice.

code .

In BalkanOrgChart.tsx

import * as React from 'react';
import { IBalkanOrgChartProps } from './IBalkanOrgChartProps';
declare var OrgChart:any;
var chart:any;
export default class BalkanOrgChart extends React.Component<IBalkanOrgChartProps> {
public componentDidMount () {
var my_script = this.new_script('https://balkangraph.com/js/latest/OrgChart.js');
my_script.then(()=> {
chart = new OrgChart(document.getElementById("OrgChart"), {
template: "olivia",
layout: OrgChart.mixed,
mouseScrool: OrgChart.none,
menu: {
pdfPreview: {
text: "PDF Preview",
icon: OrgChart.icon.pdf(24,24, '#7A7A7A'),
onClick: preview
},
pdf: { text: "Export PDF" },
png: { text: "Export PNG" },
svg: { text: "Export SVG" },
csv: { text: "Export CSV" }
},
nodeMenu: {
pdf: { text: "Export PDF" },
png: { text: "Export PNG" },
svg: { text: "Export SVG" }
},
nodeBinding: {
field_0: "name",
field_1: "title",
img_0: "img"
},
nodes: [
{ id: "1", name: "Jack Hill", title: "Chairman and CEO", email: "amber@domain.com", img: "https://balkangraph.com/js/img/1.jpg" },
{ id: "2", pid: "1", name: "Lexie Cole", title: "QA Lead", email: "ava@domain.com", img: "https://balkangraph.com/js/img/2.jpg" },
{ id: "3", pid: "1", name: "Janae Barrett", title: "Technical Director", img: "https://balkangraph.com/js/img/3.jpg" },
{ id: "4", pid: "1", name: "Aaliyah Webb", title: "Manager", email: "jay@domain.com", img: "https://balkangraph.com/js/img/4.jpg" },
{ id: "5", pid: "2", name: "Elliot Ross", title: "QA", img: "https://balkangraph.com/js/img/5.jpg" },
{ id: "6", pid: "2", name: "Anahi Gordon", title: "QA", img: "https://balkangraph.com/js/img/6.jpg" },
{ id: "7", pid: "2", name: "Knox Macias", title: "QA", img: "https://balkangraph.com/js/img/7.jpg" },
{ id: "8", pid: "3", name: "Nash Ingram", title: ".NET Team Lead", email: "kohen@domain.com", img: "https://balkangraph.com/js/img/8.jpg" },
{ id: "9", pid: "3", name: "Sage Barnett", title: "JS Team Lead", img: "https://balkangraph.com/js/img/9.jpg" },
{ id: "10", pid: "8", name: "Alice Gray", title: "Programmer", img: "https://balkangraph.com/js/img/10.jpg" },
{ id: "11", pid: "8", name: "Anne Ewing", title: "Programmer", img: "https://balkangraph.com/js/img/11.jpg" },
{ id: "12", pid: "9", name: "Reuben Mcleod", title: "Programmer", img: "https://balkangraph.com/js/img/12.jpg" },
{ id: "13", pid: "9", name: "Ariel Wiley", title: "Programmer", img: "https://balkangraph.com/js/img/13.jpg" },
{ id: "14", pid: "4", name: "Lucas West", title: "Marketer", img: "https://balkangraph.com/js/img/14.jpg" },
{ id: "15", pid: "4", name: "Adan Travis", title: "Designer", img: "https://balkangraph.com/js/img/15.jpg" },
{ id: "16", pid: "4", name: "Alex Snider", title: "Sales Manager", img: "https://balkangraph.com/js/img/16.jpg" }
]
});
}).catch(()=> {
});
}
private new_script(src:any) {
return new Promise((resolve, reject)=>{
var script = document.createElement('script');
script.src = src;
script.addEventListener('load', ()=> {
resolve();
});
script.addEventListener('error', (e)=> {
reject(e);
});
document.body.appendChild(script);
});
}
public render(): React.ReactElement<IBalkanOrgChartProps> {
return (
<div id='OrgChart'>
</div>
);
}
}
const preview = () => {
return OrgChart.pdfPrevUI.show(chart, {
format: 'A4'
});
};
/*var preview= function(){
OrgChart.pdfPrevUI.show(chart, {
format: 'A4'
});
}*/

import * as React from 'react';

import { IBalkanOrgChartProps } from './IBalkanOrgChartProps';

declare var OrgChart:any;

var chart:any;

export default class BalkanOrgChart extends React.Component<IBalkanOrgChartProps> {

public componentDidMount () {

var my_script = this.new_script('https://balkangraph.com/js/latest/OrgChart.js');

my_script.then(()=> {

chart = new OrgChart(document.getElementById("OrgChart"), {

template: "olivia",

layout: OrgChart.mixed,

mouseScrool: OrgChart.none,

menu: {

pdfPreview: {

text: "PDF Preview",

icon: OrgChart.icon.pdf(24,24, '#7A7A7A'),

onClick: preview

pdf: { text: "Export PDF" },

png: { text: "Export PNG" },

svg: { text: "Export SVG" },

csv: { text: "Export CSV" }

nodeMenu: {

pdf: { text: "Export PDF" },

png: { text: "Export PNG" },

svg: { text: "Export SVG" }

nodeBinding: {

field_0: "name",

field_1: "title",

img_0: "img"

nodes: [

{ id: "1", name: "Jack Hill", title: "Chairman and CEO", email: "amber@domain.com", img: "https://balkangraph.com/js/img/1.jpg" },

{ id: "2", pid: "1", name: "Lexie Cole", title: "QA Lead", email: "ava@domain.com", img: "https://balkangraph.com/js/img/2.jpg" },

{ id: "3", pid: "1", name: "Janae Barrett", title: "Technical Director", img: "https://balkangraph.com/js/img/3.jpg" },

{ id: "4", pid: "1", name: "Aaliyah Webb", title: "Manager", email: "jay@domain.com", img: "https://balkangraph.com/js/img/4.jpg" },

{ id: "5", pid: "2", name: "Elliot Ross", title: "QA", img: "https://balkangraph.com/js/img/5.jpg" },

{ id: "6", pid: "2", name: "Anahi Gordon", title: "QA", img: "https://balkangraph.com/js/img/6.jpg" },

{ id: "7", pid: "2", name: "Knox Macias", title: "QA", img: "https://balkangraph.com/js/img/7.jpg" },

{ id: "8", pid: "3", name: "Nash Ingram", title: ".NET Team Lead", email: "kohen@domain.com", img: "https://balkangraph.com/js/img/8.jpg" },

{ id: "9", pid: "3", name: "Sage Barnett", title: "JS Team Lead", img: "https://balkangraph.com/js/img/9.jpg" },

{ id: "10", pid: "8", name: "Alice Gray", title: "Programmer", img: "https://balkangraph.com/js/img/10.jpg" },

{ id: "11", pid: "8", name: "Anne Ewing", title: "Programmer", img: "https://balkangraph.com/js/img/11.jpg" },

{ id: "12", pid: "9", name: "Reuben Mcleod", title: "Programmer", img: "https://balkangraph.com/js/img/12.jpg" },

{ id: "13", pid: "9", name: "Ariel Wiley", title: "Programmer", img: "https://balkangraph.com/js/img/13.jpg" },

{ id: "14", pid: "4", name: "Lucas West", title: "Marketer", img: "https://balkangraph.com/js/img/14.jpg" },

{ id: "15", pid: "4", name: "Adan Travis", title: "Designer", img: "https://balkangraph.com/js/img/15.jpg" },

{ id: "16", pid: "4", name: "Alex Snider", title: "Sales Manager", img: "https://balkangraph.com/js/img/16.jpg" }

]

});

}).catch(()=> {

});

}

private new_script(src:any) {

return new Promise((resolve, reject)=>{

var script = document.createElement('script');

script.src = src;

script.addEventListener('load', ()=> {

resolve();

});

script.addEventListener('error', (e)=> {

reject(e);

});

document.body.appendChild(script);

});

}

public render(): React.ReactElement<IBalkanOrgChartProps> {

return (

</div>

);

}

const preview = () => {

return OrgChart.pdfPrevUI.show(chart, {

format: 'A4'

});

};

/*var preview= function(){

OrgChart.pdfPrevUI.show(chart, {

format: 'A4'

});

}*/

System requirements

OrgChart JS will run on any server that supports HTML. You can even run OrgChart.js locally from a filesystem.

OrgChart JS does not depend on any third-party JavaScript library.

OrgChart JS works in all modern browsers and uses SVG for the graphics rendering.

Where…

nodes is the data source. The ‘id’ property is mandatory.

pid is the parent id, this represents a connection between two nodes.

nodeBinding is the ‘name’ property form, the data source will be bound to ‘field_0’ UI element from the template.

SharePoint 2013 Hosting – HostForLIFE.eu :: Spfx Crud Operations with Dynamic Controls (PNPJS)

Oct 31st

Posted by Peter in European SharePoint 2013 Hosting

Open a command prompt. Create a directory for the SPFx solution.

md spfx-pnp-Crud

Navigate to the above-created directory.

cd spfx-pnp-Crud

Run the Yeoman SharePoint Generator to create the solution.

yo @microsoft/sharepoint

Solution Name

Hit Enter to have default name (spfx-pnp-DropDown in this case) or type in any other name for your solution.

Selected choice – Hit Enter

Target for the component

Here, we can select the target environment where we are planning to deploy the client web part, i.e., SharePoint Online or SharePoint OnPremise (SharePoint 2016 onwards).

Selected Choice: SharePoint Online only (latest)

Place of files

We may choose to use the same folder or create a subfolder for our solution.

Selected Choice: Same folder

Deployment option

Selecting Y will allow the app to deployed instantly to all sites and will be accessible everywhere.

Selected Choice: N (install on each site explicitly)

Permissions to access web APIs

Choose if the components in the solution require permission to access web APIs that are unique and not shared with other components in the tenant. Selected Choice: N (solution contains unique permissions)

Type of client-side component to create

We can choose to create a client-side web part or an extension. Choose the web part option.

Selected Choice: WebPart

Web part name

Hit Enter to select the default name or type in any other name.

Selected Choice: SpfxDynamicCOntrols

Web part description

Hit Enter to select the default description or type in any other value.

Framework to use

Select any JavaScript framework to develop the component. Available choices are – No JavaScript Framework, React, and Knockout.

Selected Choice: React

Yeoman generator will perform a scaffolding process to generate the solution. The scaffolding process will take a significant amount of time.

Once the scaffolding process is completed, lock down the version of the project dependencies by running the below command.

npm shrinkwrap

In the command prompt, type the below command to open the solution in the code editor of your choice.

NPM Packages Used,

On the command prompt, run the below command.

npm i @pnp/logging @pnp/common @pnp/odata @pnp/sp --save
for Pollyfills
npm install --save @pnp/polyfill-ie11

npm i @pnp/logging @pnp/common @pnp/odata @pnp/sp --save

for Pollyfills

npm install --save @pnp/polyfill-ie11

in SpfxDynamicCOntrolsWebPart.ts

import "@pnp/polyfill-ie11";    
import { sp, Web } from '@pnp/sp';     
export interface ISpfxDynamicCOntrolsWebPartProps {    
  description: string;    
}     
export default class SpfxDynamicCOntrolsWebPart extends BaseClientSideWebPart<ISpfxDynamicCOntrolsWebPartProps> {    
  protected onInit(): Promise<void> {    
    return new Promise<void>((resolve: () => void, reject: (error?: any) => void): void => {    
      sp.setup({    
        sp: {    
          headers: {    
            "Accept": "application/json; odata=nometadata"    
          }    
        }    
      });    
      resolve();    
    });    
  }    
  public render(): void {    
    const element: React.ReactElement<ISpfxDynamicCOntrolsProps > = React.createElement(    
      SpfxDynamicCOntrols,    
      {    
        description: this.properties.description,    
        context: this.context      
      }    
    );    
    ReactDom.render(element, this.domElement);    
  }    
  protected onDispose(): void {    
    ReactDom.unmountComponentAtNode(this.domElement);    
  }    
  protected get dataVersion(): Version {    
    return Version.parse('1.0');    
  }    
  protected getPropertyPaneConfiguration(): IPropertyPaneConfiguration {    
    return {    
      pages: [    
        {    
          header: {    
            description: strings.PropertyPaneDescription    
          },    
          groups: [    
            {    
              groupName: strings.BasicGroupName,    
              groupFields: [    
                PropertyPaneTextField('description', {    
                  label: strings.DescriptionFieldLabel    
                })    
              ]    
            }    
          ]    
        }    
      ]    
    };    
  }    
}

import "@pnp/polyfill-ie11";

import { sp, Web } from '@pnp/sp';

export interface ISpfxDynamicCOntrolsWebPartProps {

description: string;

}

export default class SpfxDynamicCOntrolsWebPart extends BaseClientSideWebPart<ISpfxDynamicCOntrolsWebPartProps> {

protected onInit(): Promise<void> {

return new Promise<void>((resolve: () => void, reject: (error?: any) => void): void => {

sp.setup({

sp: {

headers: {

"Accept": "application/json; odata=nometadata"

}

});

resolve();

});

}

public render(): void {

const element: React.ReactElement<ISpfxDynamicCOntrolsProps > = React.createElement(

SpfxDynamicCOntrols,

{

description: this.properties.description,

context: this.context

}

);

ReactDom.render(element, this.domElement);

}

protected onDispose(): void {

ReactDom.unmountComponentAtNode(this.domElement);

}

protected get dataVersion(): Version {

return Version.parse('1.0');

}

protected getPropertyPaneConfiguration(): IPropertyPaneConfiguration {

return {

pages: [

{

header: {

description: strings.PropertyPaneDescription

groups: [

{

groupName: strings.BasicGroupName,

groupFields: [

PropertyPaneTextField('description', {

label: strings.DescriptionFieldLabel

})

]

}

]

}

]

};

}

in SpfxDynamicCOntrols.ts

import { ISpfxDynamicCOntrolsState } from './ISpfxDynamicCOntrolsState';    
import * as $ from 'jquery';    
require('./Styles/capstatus.css');    
let ProjectMapId:any;    
var search = window.location.search;    
var params = new URLSearchParams(search);    
var MYProjectname = params.get('ProjectName');    
var managerlistid: number;    
import { sp, Web } from '@pnp/sp';     
export default class SpfxDynamicCOntrols extends React.Component<ISpfxDynamicCOntrolsProps, ISpfxDynamicCOntrolsState> {    
  constructor(props: ISpfxDynamicCOntrolsProps, state: ISpfxDynamicCOntrolsState) {    
    super(props);    
    this.state = {    
      Projectstatus: [{ mymilestone: "", myscore: "", id: "",Forcastedate:"",Actualdate:"",TgtResDate:"" }]       
    };    
    this.fetchdatas = this.fetchdatas.bind(this);    
    this.AddProject = this.AddProject.bind(this);    
    this.updateproject = this.updateproject.bind(this);    
    this.AddProgram = this.AddProgram.bind(this);    
    this.UpdateProgram = this.UpdateProgram.bind(this);    
    if (MYProjectname)    
        this.fetchdatas();    
  }    
  private mybutton() {    
    if (!MYProjectname) {    
      return <button    
        onClick={this.AddProgram}>     
        Add Project    
      </button>;    
    } else {    
      return <button    
      onClick={() => this.UpdateProgram()}>    
        Update Project    
    </button>;    
    }    
  }    
  private createUI() {    
    let widthstyle = {    
      width:"100%"    
    };    
    let heightstyle = {    
      width:"100%"    
    };    
    return this.state.Projectstatus.map((el, i) => (    
<tr  key={i}>    
              <td><input type="text" style={widthstyle} mycustomattribute={el.id || ''} placeholder="MileStone" name="mymilestone" value={el.mymilestone || ''} onChange={this.handleChange.bind(this, i)}/></td>    
              <td><input type="text" style={widthstyle} mycustomattribute={el.id || ''} placeholder="Percentage" name="myscore" value={el.myscore || ''} onChange={this.handleChange.bind(this, i)}/></td>    
              <td><input type="text" style={widthstyle} mycustomattribute={el.id || ''} placeholder="Forcastedate" name="Forcastedate" value={el.Forcastedate || ''} onChange={this.handleChange.bind(this, i)}/></td>    
              <td><input type="text" style={widthstyle} mycustomattribute={el.id || ''} placeholder="Actualdate" name="Actualdate" value={el.Actualdate || ''} onChange={this.handleChange.bind(this, i)}/></td>    
              <td><input type="text" style={widthstyle} mycustomattribute={el.id || ''} placeholder="TgtResDate" name="TgtResDate" value={el.TgtResDate || ''} onChange={this.handleChange.bind(this, i)}/></td>    
              <td><span><a href="" mycustomattribute={el.id || ''} onClick={(e) => {this.removeClick(e,this, i, el.id)}}>-</a></span></td>    
              </tr>    
    ));    
  }    
  private handleChange(i, e) {    
    const { name, value } = e.target;    
    let Projectstatus = [...this.state.Projectstatus];    
    Projectstatus[i] = { ...Projectstatus[i], [name]: value };    
    this.setState({ Projectstatus });    
  }    
  private addClick() {    
    this.setState(prevState => ({    
      Projectstatus: [...prevState.Projectstatus, { mymilestone: "", myscore: "", id: "",Forcastedate:"",Actualdate:"",TgtResDate:"" }]    
    }));    
  }    
  private removeClick(event,mythis,i, delid) {    
    event.preventDefault();    
    // var delid=Number($(this).attr("ProjectStatus"));    
    if (MYProjectname) {    
      let list = sp.web.lists.getByTitle("ProjectStatus");    
      list.items.getById(delid).delete().then(_ => {    
        console.log("Deleted");    
      });    
    }    
    let Projectstatus = [...this.state.Projectstatus];    
    Projectstatus.splice(i, 1);    
    this.setState({ Projectstatus });    
  }    
  private async AddProject(myid:number) {    
    const web = new Web(this.props.context.pageContext.web.absoluteUrl);    
    const batch = web.createBatch();    
    const list = web.lists.getByTitle("ProjectStatus");    
    const entityTypeFullName = await list.getListItemEntityTypeFullName();    
    for (let k = 0; k < this.state.Projectstatus.length; k++) {    
      list.items.inBatch(batch).add({    
        ProjectNameId: myid,    
        MileStone:this.state.Projectstatus[k].mymilestone,    
        PercentageComplete: this.state.Projectstatus[k].myscore,    
        ForcastDate: this.state.Projectstatus[k].Forcastedate,    
        ActualDate: this.state.Projectstatus[k].Actualdate,    
        TargetResolutionDate: this.state.Projectstatus[k].TgtResDate    
      }, entityTypeFullName).then(b => {       
      });    
    }    
    batch.execute().then(() => {    
    });    
  }    
  private async updateproject() {    
    const web = new Web(this.props.context.pageContext.web.absoluteUrl);    
    const batch = web.createBatch();    
    const list = web.lists.getByTitle("ProjectStatus");    
    const entityTypeFullName = await list.getListItemEntityTypeFullName();    
    for (let k = 0; k < this.state.Projectstatus.length; k++) {    
      if (!this.state.Projectstatus[k].id) {    
        list.items.inBatch(batch).add({    
          ProjectNameId: managerlistid,    
          MileStone: this.state.Projectstatus[k].mymilestone,    
          PercentageComplete:this.state.Projectstatus[k].myscore,    
          ForcastDate: this.state.Projectstatus[k].Forcastedate,    
          ActualDate: this.state.Projectstatus[k].Actualdate,    
          TargetResolutionDate: this.state.Projectstatus[k].TgtResDate    
        }, entityTypeFullName).then(b => {    
          // console.log(b);    
        });    
      }    
      else {    
        list.items.inBatch(batch).getById(this.state.Projectstatus[k].id).update({    
          ProjectNameId:managerlistid,    
          MileStone: this.state.Projectstatus[k].mymilestone,    
          PercentageComplete: this.state.Projectstatus[k].myscore,    
          ForcastDate: this.state.Projectstatus[k].Forcastedate,    
          ActualDate: this.state.Projectstatus[k].Actualdate,    
          TargetResolutionDate: this.state.Projectstatus[k].TgtResDate    
        }).then(b => {    
          // console.log(b);    
        });    
      }    
    }    
    batch.execute().then(() => console.log("All done!"));    
    // event.preventDefault();    
  }    
  private fetchdatas() {    
    var reg1 = new RegExp('<div class=\"ExternalClass[0-9A-F]+\">', "");    
    var reg2 = new RegExp('</div>$', "");    
    const web = new Web(this.props.context.pageContext.web.absoluteUrl);    
    //const batch1 = web.createBatch();    
    const list1 = web.lists.getByTitle("Program");    
    list1.items.select('Id,ProgramName').filter("ProgramName eq '" + MYProjectname + "'").get().then(r => {       
      managerlistid = r[0].Id;    
      $("#ProjectName").val(r[0].ProgramName);    
    });    
    const list2 = web.lists.getByTitle("ProjectStatus");    
 let FetchProjectDetails = [];    
     list2.items.select('Id,MileStone,PercentageComplete,ForcastDate,ActualDate,TargetResolutionDate').filter("ProjectName/ProgramName eq '" + MYProjectname + "'").top(5000).get().then(r => {    
      for (let i = 0; i < r.length; i++) {    
        FetchProjectDetails.push({    
          mymilestone: r[i].MileStone,    
          myscore: r[i].PercentageComplete,    
          id: r[i].Id,    
          Forcastedate:r[i].ForcastDate,    
          Actualdate:r[i].ActualDate,    
          TgtResDate:r[i].TargetResolutionDate    
        });    
      }    
      this.setState({ Projectstatus: FetchProjectDetails });    
    });    
  }      
  public render(): React.ReactElement<ISpfxDynamicCOntrolsProps> {         
    return (    
      <div className="wrapper">    
      <div className="container">    
        <div className="program_list">    
        <div>    
            <div><label>Program Name:</label></div>    
            <div><input className="input" type="text" id="ProjectName" /></div>    
          </div>    
        </div>    
        <div className="milestone">    
          <table>    
          <tbody>    
              <tr>    
              <th>Milestone Title</th>    
              <th>%Complete</th>    
              <th>Forecast Date</th>    
              <th>Actual Date</th>    
              <th>Target Resolution Date</th>    
              </tr>    
              <a href="#" id="Addbutton" onClick={this.addClick.bind(this)}>+</a><br></br>    
              {this.createUI()}    
              </tbody>    
          </table>    
        </div>    
        {this.mybutton()}      
      </div>  </div>      
    );    
  }    
  private AddProgram(): void{    
        
    let projectname = $("#ProjectName").val();    
   
    var AddData ={    
      "ProgramName": projectname,    
    };    
    sp.web.lists.getByTitle("Program").items.add(AddData).then(i => {    
      this.AddProject(i.data.Id);    
    });    
  }    
  private UpdateProgram(): void {    
    let projectname = $("#ProjectName").val();    
    var UpdateData ={    
      "ProgramName": projectname    
    };    sp.web.lists.getByTitle("Program").items.getById(managerlistid).update(UpdateData).then(i => {    
      this.updateproject();    
    });    
  }    
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

import { ISpfxDynamicCOntrolsState } from './ISpfxDynamicCOntrolsState';

import * as $ from 'jquery';

require('./Styles/capstatus.css');

let ProjectMapId:any;

var search = window.location.search;

var params = new URLSearchParams(search);

var MYProjectname = params.get('ProjectName');

var managerlistid: number;

import { sp, Web } from '@pnp/sp';

export default class SpfxDynamicCOntrols extends React.Component<ISpfxDynamicCOntrolsProps, ISpfxDynamicCOntrolsState> {

constructor(props: ISpfxDynamicCOntrolsProps, state: ISpfxDynamicCOntrolsState) {

super(props);

this.state = {

Projectstatus: [{ mymilestone: "", myscore: "", id: "",Forcastedate:"",Actualdate:"",TgtResDate:"" }]

};

this.fetchdatas = this.fetchdatas.bind(this);

this.AddProject = this.AddProject.bind(this);

this.updateproject = this.updateproject.bind(this);

this.AddProgram = this.AddProgram.bind(this);

this.UpdateProgram = this.UpdateProgram.bind(this);

if (MYProjectname)

this.fetchdatas();

}

private mybutton() {

if (!MYProjectname) {

return <button

onClick={this.AddProgram}>

Add Project

</button>;

} else {

return <button

onClick={() => this.UpdateProgram()}>

Update Project

</button>;

}

private createUI() {

let widthstyle = {

width:"100%"

};

let heightstyle = {

width:"100%"

};

return this.state.Projectstatus.map((el, i) => (

<td><span><a href="" mycustomattribute={el.id || ''} onClick={(e) => {this.removeClick(e,this, i, el.id)}}>-</a></span></td>

</tr>

));

}

private handleChange(i, e) {

const { name, value } = e.target;

let Projectstatus = [...this.state.Projectstatus];

Projectstatus[i] = { ...Projectstatus[i], [name]: value };

this.setState({ Projectstatus });

}

private addClick() {

this.setState(prevState => ({

Projectstatus: [...prevState.Projectstatus, { mymilestone: "", myscore: "", id: "",Forcastedate:"",Actualdate:"",TgtResDate:"" }]

}));

}

private removeClick(event,mythis,i, delid) {

event.preventDefault();

// var delid=Number($(this).attr("ProjectStatus"));

if (MYProjectname) {

let list = sp.web.lists.getByTitle("ProjectStatus");

list.items.getById(delid).delete().then(_ => {

console.log("Deleted");

});

}

let Projectstatus = [...this.state.Projectstatus];

Projectstatus.splice(i, 1);

this.setState({ Projectstatus });

}

private async AddProject(myid:number) {

const web = new Web(this.props.context.pageContext.web.absoluteUrl);

const batch = web.createBatch();

const list = web.lists.getByTitle("ProjectStatus");

const entityTypeFullName = await list.getListItemEntityTypeFullName();

for (let k = 0; k < this.state.Projectstatus.length; k++) {

list.items.inBatch(batch).add({

ProjectNameId: myid,

MileStone:this.state.Projectstatus[k].mymilestone,

PercentageComplete: this.state.Projectstatus[k].myscore,

ForcastDate: this.state.Projectstatus[k].Forcastedate,

ActualDate: this.state.Projectstatus[k].Actualdate,

TargetResolutionDate: this.state.Projectstatus[k].TgtResDate

}, entityTypeFullName).then(b => {

});

}

batch.execute().then(() => {

});

}

private async updateproject() {

const web = new Web(this.props.context.pageContext.web.absoluteUrl);

const batch = web.createBatch();

const list = web.lists.getByTitle("ProjectStatus");

const entityTypeFullName = await list.getListItemEntityTypeFullName();

for (let k = 0; k < this.state.Projectstatus.length; k++) {

if (!this.state.Projectstatus[k].id) {

list.items.inBatch(batch).add({

ProjectNameId: managerlistid,

MileStone: this.state.Projectstatus[k].mymilestone,

PercentageComplete:this.state.Projectstatus[k].myscore,

ForcastDate: this.state.Projectstatus[k].Forcastedate,

ActualDate: this.state.Projectstatus[k].Actualdate,

TargetResolutionDate: this.state.Projectstatus[k].TgtResDate

}, entityTypeFullName).then(b => {

// console.log(b);

});

}

else {

list.items.inBatch(batch).getById(this.state.Projectstatus[k].id).update({

ProjectNameId:managerlistid,

MileStone: this.state.Projectstatus[k].mymilestone,

PercentageComplete: this.state.Projectstatus[k].myscore,

ForcastDate: this.state.Projectstatus[k].Forcastedate,

ActualDate: this.state.Projectstatus[k].Actualdate,

TargetResolutionDate: this.state.Projectstatus[k].TgtResDate

}).then(b => {

// console.log(b);

});

}

batch.execute().then(() => console.log("All done!"));

// event.preventDefault();

}

private fetchdatas() {

var reg1 = new RegExp('<div class=\"ExternalClass[0-9A-F]+\">', "");

var reg2 = new RegExp('</div>$', "");

const web = new Web(this.props.context.pageContext.web.absoluteUrl);

//const batch1 = web.createBatch();

const list1 = web.lists.getByTitle("Program");

list1.items.select('Id,ProgramName').filter("ProgramName eq '" + MYProjectname + "'").get().then(r => {

managerlistid = r[0].Id;

$("#ProjectName").val(r[0].ProgramName);

});

const list2 = web.lists.getByTitle("ProjectStatus");

let FetchProjectDetails = [];

list2.items.select('Id,MileStone,PercentageComplete,ForcastDate,ActualDate,TargetResolutionDate').filter("ProjectName/ProgramName eq '" + MYProjectname + "'").top(5000).get().then(r => {

for (let i = 0; i < r.length; i++) {

FetchProjectDetails.push({

mymilestone: r[i].MileStone,

myscore: r[i].PercentageComplete,

id: r[i].Id,

Forcastedate:r[i].ForcastDate,

Actualdate:r[i].ActualDate,

TgtResDate:r[i].TargetResolutionDate

});

}

this.setState({ Projectstatus: FetchProjectDetails });

});

}

public render(): React.ReactElement<ISpfxDynamicCOntrolsProps> {

return (

<div>

<div><label>Program Name:</label></div>

</div>

<table>

<tbody>

<tr>

<th>Milestone Title</th>

<th>%Complete</th>

<th>Forecast Date</th>

<th>Actual Date</th>

<th>Target Resolution Date</th>

</tr>

{this.createUI()}

</tbody>

</table>

</div>

{this.mybutton()}

</div> </div>

);

}

private AddProgram(): void{

let projectname = $("#ProjectName").val();

var AddData ={

"ProgramName": projectname,

};

sp.web.lists.getByTitle("Program").items.add(AddData).then(i => {

this.AddProject(i.data.Id);

});

}

private UpdateProgram(): void {

let projectname = $("#ProjectName").val();

var UpdateData ={

"ProgramName": projectname

}; sp.web.lists.getByTitle("Program").items.getById(managerlistid).update(UpdateData).then(i => {

this.updateproject();

});

}

in ISpfxDynamicCOntrolsProps.ts

import { WebPartContext } from '@microsoft/sp-webpart-base';      
export interface ISpfxDynamicCOntrolsProps {    
  description: string;    
  context: WebPartContext;     
}

import { WebPartContext } from '@microsoft/sp-webpart-base';

export interface ISpfxDynamicCOntrolsProps {

description: string;

context: WebPartContext;

}

in ISpfxDynamicCOntrolsState.ts

export interface ISpfxDynamicCOntrolsState {      
    Projectstatus?:any[];    
}

export interface ISpfxDynamicCOntrolsState {

Projectstatus?:any[];

}

Here I am using 2 lists, namely Program and ProjectStatus.

Program list contains ProgramName field.

ProjectStatus list contains MileStone,PercentageComplete,ForcastDate,ActualDate,TargetResolutionDate fields.

ynamic Fields are used in the second list.

SharePoint 2013 Hosting – HostForLIFE.eu :: Reading SharePoint Document Library Excel File Using Spfx And Pnp.js

Oct 24th

Posted by Peter in European SharePoint 2013 Hosting

Develop SharePoint Framework Web Part.

Open a command prompt. Create a directory for SPFx solution.

md spfx-pnp-Excel

Navigate to the above created directory.

cd spfx-pnp-Excel

Run the Yeoman SharePoint Generator to create the solution.

yo @microsoft/sharepoint

Solution Name

Hit Enter to have default name (spfx-pnpExcel in this case) or type in any other name for your solution.

Selected choice – Hit Enter

Target for the component

Here, we can select the target environment where we are planning to deploy the client web part, i.e., SharePoint Online or SharePoint OnPremise (SharePoint 2016 onwards).

Selected choice: SharePoint Online only (latest)

Place of files

We may choose to use the same folder or create a subfolder for our solution.

Selected choice: Same folder

Deployment option

Selecting Y will allow the app to deployed instantly to all sites and will be accessible everywhere.

Selected choice: N (install on each site explicitly)

Permissions to access web APIs

Choose if the components in the solution require permissions to access web APIs that are unique and not shared with other components in the tenant.

Selected choice: N (solution contains unique permissions)

Type of client-side component to create

We can choose to create a client-side web part or an extension. Choose web part option.

Selected choice: WebPart

Web part name

Hit Enter to select the default name or type in any other name.

Selected choice: PnPExcelFile

Web part description

Hit Enter to select the default description or type in any other value.

Framework to use

Select any JavaScript framework to develop the component. Available choices are – No JavaScript Framework, React, and Knockout.

Selected choice: React

Yeoman generator will perform a scaffolding process to generate the solution. The scaffolding process will take a significant amount of time.

Once the scaffolding process is completed, lock down the version of project dependencies by running below command.

npm shrinkwrap

In the command prompt, type below command to open the solution in the code editor of your choice.

code .

NPM Packages Used,

@pnp/sp(https://www.npmjs.com/package/@pnp/sp)

On the command prompt, run below command.

npm i @pnp/logging @pnp/common @pnp/odata @pnp/sp –save

xlsx

On the command prompt, run below command.

npm i xlsx

in ts file

public render(): void {  
    sp.setup({  
        spfxContext: this.context  
    });  
    const element: React.ReactElement <IPnpcheckProps> = React.createElement(Pnpcheck, {  
        description: this.properties.description  
    });  
    ReactDom.render(element, this.domElement);  
}

public render(): void {

sp.setup({

spfxContext: this.context

});

const element: React.ReactElement <IPnpcheckProps> = React.createElement(Pnpcheck, {

description: this.properties.description

});

ReactDom.render(element, this.domElement);

}

in tsx file

import * asReactfrom 'react';  
import styles from './Pnpcheck.module.scss';  
import {  
    IPnpcheckProps  
} from './IPnpcheckProps';  
import {  
    escape  
} from '@microsoft/sp-lodash-subset';  
import {  
    sp  
} from "@pnp/sp";  
import * as $ from 'jquery';  
import * as XLSX from 'xlsx';  
export default class Pnpcheck extends React.Component <IPnpcheckProps, {} > {  
    constructor(props: IPnpcheckProps) {  
        super(props);  
        this.mypnpcheck();  
    }  
    public render(): React.ReactElement <IPnpcheckProps> {  
        return ( < div className = { styles.pnpcheck } > < /div>);  
    }  
    public mypnpcheck() {  
        sp.web.getFileByServerRelativeUrl("/sites/SPCapabilityTeam/Documents/ss.xlsx").getBuffer().then((buffer: ArrayBuffer) => {  
            var workbook = XLSX.read(buffer, {  
                type: "buffer"  
            });  
            var first_sheet_name = workbook.SheetNames[0];  
            var worksheet = workbook.Sheets[first_sheet_name];  
            var headers = {};  
            var data = [];  
            let z: any;  
            for (z in worksheet) {  
                if (z[0] === '!') continue;  
                var tt = 0;  
                for (var i = 0; i < z.length; i++) {  
                    if (!isNaN(z[i])) {  
                        tt = i;  
                        break;  
                    }  
                }  
                var col = z.substring(0, tt);  
                var row = parseInt(z.substring(tt));  
                var value = worksheet[z].v;  
                //store header names  
                if (row == 1 && value) {  
                    headers[col] = value;  
                    continue;  
                }  
                if (!data[row]) data[row] = {};  
                data[row][headers[col]] = value;  
            }  
            console.log(data)  
        });  
    }  
}

import * asReactfrom 'react';

import styles from './Pnpcheck.module.scss';

import {

IPnpcheckProps

} from './IPnpcheckProps';

import {

escape

} from '@microsoft/sp-lodash-subset';

import {

} from "@pnp/sp";

import * as $ from 'jquery';

import * as XLSX from 'xlsx';

export default class Pnpcheck extends React.Component <IPnpcheckProps, {} > {

constructor(props: IPnpcheckProps) {

super(props);

this.mypnpcheck();

}

public render(): React.ReactElement <IPnpcheckProps> {

return ( < div className = { styles.pnpcheck } > < /div>);

}

public mypnpcheck() {

sp.web.getFileByServerRelativeUrl("/sites/SPCapabilityTeam/Documents/ss.xlsx").getBuffer().then((buffer: ArrayBuffer) => {

var workbook = XLSX.read(buffer, {

type: "buffer"

});

var first_sheet_name = workbook.SheetNames[0];

var worksheet = workbook.Sheets[first_sheet_name];

var headers = {};

var data = [];

let z: any;

for (z in worksheet) {

if (z[0] === '!') continue;

var tt = 0;

for (var i = 0; i < z.length; i++) {

if (!isNaN(z[i])) {

tt = i;

break;

}

var col = z.substring(0, tt);

var row = parseInt(z.substring(tt));

var value = worksheet[z].v;

//store header names

if (row == 1 && value) {

headers[col] = value;

continue;

}

if (!data[row]) data[row] = {};

data[row][headers[col]] = value;

}

console.log(data)

});

}

In the data Array object all the cell values are stored

SharePoint 2013 Hosting – HostForLIFE.eu :: [SharePoint] Get Social Comments From SharePoint 2013 OnPrem Using PowerShell CSOM

Oct 10th

Posted by Peter in European SharePoint 2013 Hosting

I had a requirement to get all the social comments from a news board web part for a specific page. Though this can be achieved using “Server Side Object Model,” it is restricted for some of the companies to run their “SSOM” code on production environment. Thus, we came up with an idea of creating PowerShell script using CSOM to retrieve social comments using “Social Data Web Service”.

Here you will be seeing how to get all the social comments in SharePoint 2013 using the PowerShell + CSOM for a specific page using “Social Data WebService” (SocialDataService.asmx)

The below code retrieves all the comments for the specified page URL and exports the resulting data to Excel.

Code Usage

##Create an empty System.Array object  
$PageNoteBoardComments = @()  


## Web Service Reference - http://Site/_vti_bin/SocialDataService.asmx  
$uri="http://<sitepath>/_vti_bin/SocialDataService.asmx?wsdl"  


##PageURL to be specified as input parameter of which contains social comments.  
$url="http://<sitepath>/Pages/TopNews.aspx"  


## if you are executing this code in a Virtual Machine it's default credentials will be used to connect to proxy.  
$socialDataServiceWebServiceReference = New-WebServiceProxy -Uri $uri -UseDefaultCredential  


##Get total number of comments for a specified URL  
[int]$CountCommentsOnUrl=$socialDataServiceWebServiceReference.CountCommentsOnUrl($url)  
  
Write-Host -ForegroundColor Green "Number of comments for the specified URL : " $CountCommentsOnUrl  


##Get Comments for the URL Specified  
$CommentsOnUrl = $socialDataServiceWebServiceReference.GetCommentsOnUrl($url,$null,$null,$null)  


##Iterate on the comments collection and store the value in PageNoteBoardComments array object.   
foreach($item in $CommentsOnUrl)  
{  
  
 $PageNoteBoardComments += New-Object   
 PsObject -property @{  
  
   'Comments' = $item.Comment  
  
   'Last Modified Time' = $item.LastModifiedTime  
  
   'URL' = [string]$item.Url  
  
   'Owner' = [string]$item.Owner  
  
   'Page Title' = [string]$item.Title  
   }  
}  


##Finally, use Export-Csv to export the data to a csv file  
$PageNoteBoardComments | Export-Csv -NoTypeInformation -Path "D:\Madhu\Powershell\NoteBoardComments.csv"

##Create an empty System.Array object

$PageNoteBoardComments = @()

## Web Service Reference - http://Site/_vti_bin/SocialDataService.asmx

$uri="http://<sitepath>/_vti_bin/SocialDataService.asmx?wsdl"

##PageURL to be specified as input parameter of which contains social comments.

$url="http://<sitepath>/Pages/TopNews.aspx"

## if you are executing this code in a Virtual Machine it's default credentials will be used to connect to proxy.

$socialDataServiceWebServiceReference = New-WebServiceProxy -Uri $uri -UseDefaultCredential

##Get total number of comments for a specified URL

[int]$CountCommentsOnUrl=$socialDataServiceWebServiceReference.CountCommentsOnUrl($url)

Write-Host -ForegroundColor Green "Number of comments for the specified URL : " $CountCommentsOnUrl

##Get Comments for the URL Specified

$CommentsOnUrl = $socialDataServiceWebServiceReference.GetCommentsOnUrl($url,$null,$null,$null)

##Iterate on the comments collection and store the value in PageNoteBoardComments array object.

foreach($item in $CommentsOnUrl)

{

$PageNoteBoardComments += New-Object

PsObject -property @{

'Comments' = $item.Comment

'Last Modified Time' = $item.LastModifiedTime

'URL' = [string]$item.Url

'Owner' = [string]$item.Owner

'Page Title' = [string]$item.Title

}

##Finally, use Export-Csv to export the data to a csv file

$PageNoteBoardComments | Export-Csv -NoTypeInformation -Path "D:\Madhu\Powershell\NoteBoardComments.csv"

Scenario 2

To iterate all the page URL’s from excel and to retrieve all the social comments from social data web service based on page URL.

Note
All the Page URL’s are stored under “PageUrl” column in excel sheet.

#Path to the CSV output path file.  
   $CSVOutputfilepath = "D:\Madhu\GetSocialComments.csv"  
  
   #Path to the log file.  
   $outputfilepath = "D:\Madhu\GetMySocialCommentsLogs.txt"  
  
   #Excel File path.  
   $FilePath = "D:\Madhu\pagelist.xls"  
  
   #Create an Object Excel.Application using Com interface  
   $objExcel = New-Object -ComObject Excel.Application  
  
   # Disable the 'visible' property so the document won't open in excel  
   $objExcel.Visible = $False  
     
   # Open the Excel file and save it in $WorkBook  
   $WorkBook = $objExcel.Workbooks.Open($FilePath)  
     
  
   # Load First sheet  
   $sheet = $WorkBook.Worksheets.Item(1)  
     
  
   #Find PageUrl and get the column position  
   $pageurlcolumns = $sheet.UsedRange.Columns.Find('PageUrl').Column  
  
   #Get all the Rows within Excel  
   $rows = $sheet.UsedRange.Rows.Count  
  
   ## Get number of user in excel  
   $rowCount  = $rows - 1  
  
   "-"*80 | Write-Host  
   Write-Host -ForegroundColor Green "Total number of the pages initiated : " $rowCount  
   Write-Host -ForegroundColor Green "Started at : $((Get-Date).ToString())"  
   "-"*80 | Write-Host  
  
   "-"*80 | Out-File -Encoding Ascii -append $outputfilepath  
   "Total number of the pages initiated : " + $rowCount | Out-File -Encoding Ascii -append $outputfilepath  
   "Started at :" + $((Get-Date).ToString()) | Out-File -Encoding Ascii -append $outputfilepath  
   "-"*80 | Out-File -Encoding Ascii -append $outputfilepath  
  
   ## Get Page Number Count          
   $count = 1;  
  
   # loop for each row of the excel file  
   for ($i=2; $i -le $rows; $i++)  
   {  
        try{       
          
               #Get PageUrl column values  
               $filePath = $sheet.Cells.Item($i, $pageurlcolumns).text  
  
               #Log the result to log file  
               Write-Host -ForegroundColor Green "$count : " $filePath   
               "$count : " + $filePath | Out-File -Encoding Ascii -append $outputfilepath  
                 
               ## $url is a string that contains the URL from where we need to count the social comments  
               ## list contains the pages from pages library and hence we got a substring by spliting the url.  
               $url=$filePath.Substring(0,$filePath.IndexOf('/Pages'))  
                                 
               ## Get path of socail comments webservice file  
               $uri = $url + "/_vti_bin/SocialDataService.asmx"  
                 
               ## Web Service Reference - http://Site/_vti_bin/SocialDataService.asmx  
               $socialDataServiceWebServiceReference = New-WebServiceProxy -Uri $uri -UseDefaultCredential   
  
               ##Retrieve number of comments for a specific page.  
               [int]$CountCommentsOnUrl=$socialDataServiceWebServiceReference.CountCommentsOnUrl($filePath)  
               Write-Host -ForegroundColor yellow "Number of comments for the specified URL : " $CountCommentsOnUrl  
               "Number of comments for the specified URL : " +  $CountCommentsOnUrl | Out-File -Encoding Ascii -append $outputfilepath   
  
               ##Retrieve comments for a specific page.  
               $CommentsOnUrl = $socialDataServiceWebServiceReference.GetCommentsOnUrl($filePath,$null,$null,$null)   
  
               foreach($item in $CommentsOnUrl)  
               {  
                       $PageNoteBoardComments += New-Object PsObject -property @{  
                           'Comments' = $item.Comment  
                           'URL' = [string]$item.Url  
                           'Last Modified Time' = $item.LastModifiedTime  
                           'Owner' = [string]$item.Owner  
                     
                        }     
              
                         ##Add comments for a specific page.  
                         #$WriteCommentsOnUrl = $socialDataServiceWebServiceReference.AddComment($url,$item.Comment,0,"TestMultiline")  
               
               }  
  
               $count++  
            }  
       catch{  
           Write-Host -ForegroundColor Green "$accountID : " $_.Exception.Message   
           "$accountID : " + $_.Exception.Message | Out-File -Encoding Ascii -append $outputfilepath  
       }  
  
   }  
  
   #Finally, use Export-Csv to export the data to a csv file  
   $PageNoteBoardComments | Export-Csv -NoTypeInformation -Path $$CSVOutputfilepath  
        
   "-"*80 | Write-Host  
   "-"*80 | Out-File -Encoding Ascii -append $outputfilepath  
   Write-Host -ForegroundColor Green "Completed at : $((Get-Date).ToString())"  
   "Completed at : " +  $((Get-Date).ToString()) | Out-File -Encoding Ascii -append $outputfilepath  
   Write-Host -ForegroundColor Green "Total Comments Found : " $PageNoteBoardComments.Count  
   "Total Comments Found : " + $PageNoteBoardComments.Count | Out-File -Encoding Ascii -append $outputfilepath  
   "-"*80 | Write-Host  
   "-"*80 | Out-File -Encoding Ascii -append $outputfilepath  
     
   #Close the workbook object    
   $WorkBook.close()  
  
   #close the excel object  
   $objexcel.quit()

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

#Path to the CSV output path file.

$CSVOutputfilepath = "D:\Madhu\GetSocialComments.csv"

#Path to the log file.

$outputfilepath = "D:\Madhu\GetMySocialCommentsLogs.txt"

#Excel File path.

$FilePath = "D:\Madhu\pagelist.xls"

#Create an Object Excel.Application using Com interface

$objExcel = New-Object -ComObject Excel.Application

# Disable the 'visible' property so the document won't open in excel

$objExcel.Visible = $False

# Open the Excel file and save it in $WorkBook

$WorkBook = $objExcel.Workbooks.Open($FilePath)

# Load First sheet

$sheet = $WorkBook.Worksheets.Item(1)

#Find PageUrl and get the column position

$pageurlcolumns = $sheet.UsedRange.Columns.Find('PageUrl').Column

#Get all the Rows within Excel

$rows = $sheet.UsedRange.Rows.Count

## Get number of user in excel

$rowCount = $rows - 1

"-"*80 | Write-Host

Write-Host -ForegroundColor Green "Total number of the pages initiated : " $rowCount

Write-Host -ForegroundColor Green "Started at : $((Get-Date).ToString())"

"-"*80 | Write-Host

"-"*80 | Out-File -Encoding Ascii -append $outputfilepath

"Total number of the pages initiated : " + $rowCount | Out-File -Encoding Ascii -append $outputfilepath

"Started at :" + $((Get-Date).ToString()) | Out-File -Encoding Ascii -append $outputfilepath

"-"*80 | Out-File -Encoding Ascii -append $outputfilepath

## Get Page Number Count

$count = 1;

# loop for each row of the excel file

for ($i=2; $i -le $rows; $i++)

{

try{

#Get PageUrl column values

$filePath = $sheet.Cells.Item($i, $pageurlcolumns).text

#Log the result to log file

Write-Host -ForegroundColor Green "$count : " $filePath

"$count : " + $filePath | Out-File -Encoding Ascii -append $outputfilepath

## $url is a string that contains the URL from where we need to count the social comments

## list contains the pages from pages library and hence we got a substring by spliting the url.

$url=$filePath.Substring(0,$filePath.IndexOf('/Pages'))

## Get path of socail comments webservice file

$uri = $url + "/_vti_bin/SocialDataService.asmx"

## Web Service Reference - http://Site/_vti_bin/SocialDataService.asmx

$socialDataServiceWebServiceReference = New-WebServiceProxy -Uri $uri -UseDefaultCredential

##Retrieve number of comments for a specific page.

[int]$CountCommentsOnUrl=$socialDataServiceWebServiceReference.CountCommentsOnUrl($filePath)

Write-Host -ForegroundColor yellow "Number of comments for the specified URL : " $CountCommentsOnUrl

"Number of comments for the specified URL : " + $CountCommentsOnUrl | Out-File -Encoding Ascii -append $outputfilepath

##Retrieve comments for a specific page.

$CommentsOnUrl = $socialDataServiceWebServiceReference.GetCommentsOnUrl($filePath,$null,$null,$null)

foreach($item in $CommentsOnUrl)

{

$PageNoteBoardComments += New-Object PsObject -property @{

'Comments' = $item.Comment

'URL' = [string]$item.Url

'Last Modified Time' = $item.LastModifiedTime

'Owner' = [string]$item.Owner

}

##Add comments for a specific page.

#$WriteCommentsOnUrl = $socialDataServiceWebServiceReference.AddComment($url,$item.Comment,0,"TestMultiline")

}

$count++

}

catch{

Write-Host -ForegroundColor Green "$accountID : " $_.Exception.Message

"$accountID : " + $_.Exception.Message | Out-File -Encoding Ascii -append $outputfilepath

}

#Finally, use Export-Csv to export the data to a csv file

$PageNoteBoardComments | Export-Csv -NoTypeInformation -Path $$CSVOutputfilepath

"-"*80 | Write-Host

"-"*80 | Out-File -Encoding Ascii -append $outputfilepath

Write-Host -ForegroundColor Green "Completed at : $((Get-Date).ToString())"

"Completed at : " + $((Get-Date).ToString()) | Out-File -Encoding Ascii -append $outputfilepath

Write-Host -ForegroundColor Green "Total Comments Found : " $PageNoteBoardComments.Count

"Total Comments Found : " + $PageNoteBoardComments.Count | Out-File -Encoding Ascii -append $outputfilepath

"-"*80 | Write-Host

"-"*80 | Out-File -Encoding Ascii -append $outputfilepath

#Close the workbook object

$WorkBook.close()

#close the excel object

$objexcel.quit()

SharePoint 2013 Hosting Recommendation

HostForLIFE.eu’s SharePoint 2013 Hosting solution offers a comprehensive feature set that is easy-to-use for new users, yet powerful enough for the most demanding web developer expert. Hosted SharePoint Foundation 2013 is the premiere web-based collaboration and productivity enhancement tool on the market today. With SharePoint 2013 Foundation, you can quickly access and manage documents and information anytime, anywhere though a Web browser in a secure and user friendly way. SharePoint hosting services start at only at €9.99/mo, allowing you to take advantage of the robust feature set for a small business price. HostForLIFE.eu offers a variety of hosted SharePoint Foundation 2013 plans as well as dedicated SharePoint 2013 Foundation options.

SharePoint 2013 Hosting – HostForLIFE.eu :: [SharePoint] Retrieve, Add, Remove Fields From A SharePoint List Using PnP PowerShell

Oct 3rd

Posted by Peter in European SharePoint 2013 Hosting

In this blog, we are going to retrieve all the fields from a list and will add/remove a field from a SharePoint list using PnP PowerShell. For that, first, we need to connect to the site.

To establish the connection, add this command.

$SiteUrl = Read-Host “Provide Site URL”

Connect-PnPOnline -Url $SiteUrl

#Executing this line will ask for credentials. Provide a username and password to connect.

To retrieve all fields, add the below command.

$Fields = Get-PnPField -List “Test”

To add a field to a list, add this command.

Add-PnPField -List “Test” -DisplayName “Designation” -InternalName “Designation” -Type MultiChoice -AddToDefaultView -Choices “HR”,”CEO”, “DEVELOPER”,”TESTER”

To remove a field from list, execute this command.

Remove-PnPField -List “Test” -Identity “Designation”

Executing this command opens a popup window for confirmation of the field to be deleted.

Clicking on YES will remove the field from the list.

SharePoint 2013 Hosting – HostForLIFE.eu :: ReactHooks with Spfx

Hooks Overview

What is this useState() syntax?

What does useState() give us?

What’s wrong with classes?

React’s Effect Hook

Running an Effect Hook only when something changes

Using State and Effects Together

SharePoint 2013 Hosting – HostForLIFE.eu :: Creating a BalkanGraph OrgChart with Spfx

SharePoint 2013 Hosting – HostForLIFE.eu :: Spfx Crud Operations with Dynamic Controls (PNPJS)

Solution Name

Target for the component

Place of files

Permissions to access web APIs

Type of client-side component to create

Framework to use

SharePoint 2013 Hosting – HostForLIFE.eu :: Reading SharePoint Document Library Excel File Using Spfx And Pnp.js

SharePoint 2013 Hosting – HostForLIFE.eu :: [SharePoint] Get Social Comments From SharePoint 2013 OnPrem Using PowerShell CSOM

SharePoint 2013 Hosting Recommendation

SharePoint 2013 Hosting – HostForLIFE.eu :: [SharePoint] Retrieve, Add, Remove Fields From A SharePoint List Using PnP PowerShell

SharePoint 2013 Hosting Recommendation

BLOGROLL

Featured On

European Sharepoint 2013 Hosting Blog

SharePoint 2013 Hosting – HostForLIFE.eu :: ReactHooks with Spfx

Hooks Overview

What is this useState() syntax?

What does useState() give us?

What’s wrong with classes?

React’s Effect Hook

Running an Effect Hook only when something changes

Using State and Effects Together

SharePoint 2013 Hosting – HostForLIFE.eu :: Creating a BalkanGraph OrgChart with Spfx

SharePoint 2013 Hosting – HostForLIFE.eu :: Spfx Crud Operations with Dynamic Controls (PNPJS)

Solution Name

Target for the component

Place of files

Permissions to access web APIs

Type of client-side component to create

Framework to use

SharePoint 2013 Hosting – HostForLIFE.eu :: Reading SharePoint Document Library Excel File Using Spfx And Pnp.js

SharePoint 2013 Hosting – HostForLIFE.eu :: [SharePoint] Get Social Comments From SharePoint 2013 OnPrem Using PowerShell CSOM

SharePoint 2013 Hosting Recommendation

SharePoint 2013 Hosting – HostForLIFE.eu :: [SharePoint] Retrieve, Add, Remove Fields From A SharePoint List Using PnP PowerShell

SharePoint 2013 Hosting Recommendation

BLOGROLL

Featured On

Tags