0

My requirement is to read the Excel sheet data which is located in the SharePoint Online library using JavaScript and get specific values from columns based on the comparison.

E.g., if cell(a1-a20) has some data I need to compare some value from one of my form which matches the cell data and extract other related information in the other cells to the html form

How we can achieve this using jQuery /JavaScript, preferably using with out any plugins.

0

I doubt you want to implement SPO authentication, file transfer, unzip, OOXML parsing yourself, so here's how you'd do it with existing libraries.

  1. Read file using PNP JS: https://pnp.github.io/pnpjs/sp/docs/files/
sp.web.getFileByServerRelativeUrl("/sites/dev/documents/file.xlsx").getBuffer().then((buffer: ArrayBuffer) => { // parse Excel here });
  1. Parse Excel using js-xlsx: https://github.com/SheetJS/js-xlsx
var workbook = XLSX.read(buffer, {type:"buffer"});
  1. Search for cell, this example extracts the value stored in cell A1 from the first worksheet:
var first_sheet_name = workbook.SheetNames[0];
var address_of_cell = 'A1';

/* Get worksheet */
var worksheet = workbook.Sheets[first_sheet_name];

/* Find desired cell */
var desired_cell = worksheet[address_of_cell];

/* Get the value */
var desired_value = (desired_cell ? desired_cell.v : undefined);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.