Using SheetJS with SharePoint 2013 and InfoPath 2010 #839

New Issue

2017-10-12T07:51:52Z

akkbright commented

2017-10-12 07:51:52 +00:00

(Migrated from github.com)

How can this powerful tool, SheetJS, be used with SharePoint 2013 and InfoPath 2010 forms?

danxfisher commented

2017-10-12 23:38:49 +00:00

(Migrated from github.com)

What are you looking to do with it? SharePoint lists and libraries (including form libraries) are already exportable to Excel as far as I know.

SheetJSDev commented

2017-10-16 16:52:58 +00:00

(Migrated from github.com)

Admittedly not too familiar with InfoPath, what is the end goal?

SheetJSDev commented

2018-01-13 23:16:26 +00:00

(Migrated from github.com)

This library lets you convert between worksheets and structured tables, like arrays of arrays or arrays of JS objects. The utility functions section of the docs explain those operations in more detail.

The raw mechanics of interacting with the SharePoint and InfoPath infrastructure is outside of the scope, but from what I've read you should be able to interact using the SP library or using the REST API directly.

With the SP library, SP.ListItem.get_fieldValues() will return a JS object that should play nice with sheet_to_json and sheet_add_json, so (untested) exporting a SP list would look like:

var context = SP.ClientContext.get_current();
var item_list; /* set this to the list you want to export */ 
context.executeQueryAsync(function() {
  /* gather the results */
  var array_of_objects = [];
  var enumerator = item_list.getEnumerator();
  while(enumerator.moveNext()) {
    var curr = enumerator.get_current();
    var obj = item.get_fieldValues();
    array_of_objects.push(obj);
  }
  /* generate worksheet */
  var ws = XLSX.utils.json_to_sheet(obj);
  /* generate workbook */
  var wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, "Export");
  /* generate XLSX file */
  var wbout = XLSX.write(wb, {bookType:'xlsx', type: 'binary'});
  /* generate download */
  saveAs(new Blob([s2ab(wbout)],{type:"application/octet-stream"}), "sheetjs.xlsx");
});

A quick google search also revealed:

This library lets you convert between worksheets and structured tables, like arrays of arrays or arrays of JS objects. The [utility functions section of the docs](https://docs.sheetjs.com/#utility-functions) explain those operations in more detail. The raw mechanics of interacting with the SharePoint and InfoPath infrastructure is outside of the scope, but from what I've read you should be able to interact using the SP library or using the REST API directly. With the SP library, [`SP.ListItem.get_fieldValues()`](https://msdn.microsoft.com/en-us/library/office/ee548409.aspx) will return a JS object that should play nice with `sheet_to_json` and `sheet_add_json`, so (untested) exporting a SP list would look like: ```js var context = SP.ClientContext.get_current(); var item_list; /* set this to the list you want to export */ context.executeQueryAsync(function() { /* gather the results */ var array_of_objects = []; var enumerator = item_list.getEnumerator(); while(enumerator.moveNext()) { var curr = enumerator.get_current(); var obj = item.get_fieldValues(); array_of_objects.push(obj); } /* generate worksheet */ var ws = XLSX.utils.json_to_sheet(obj); /* generate workbook */ var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Export"); /* generate XLSX file */ var wbout = XLSX.write(wb, {bookType:'xlsx', type: 'binary'}); /* generate download */ saveAs(new Blob([s2ab(wbout)],{type:"application/octet-stream"}), "sheetjs.xlsx"); }); ``` A quick google search also revealed: - https://sp77.blogspot.com/2017/12/reading-excel-file-using-html-5-and.html - http://julieturner.net/2017/01/extending-sharepoint-with-adal-and-the-microsoft-graph-api-part-3-the-execution/ (h/t @juliemturner)

Sign in to join this conversation.