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

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 :: Reading SharePoint Document Library Excel File Using Spfx And Pnp.js

BLOGROLL

Featured On

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

BLOGROLL

Featured On

Tags