Uploading ExcelJS library into slate functions library panel

[39f4c63e3e39d0fd3301]

Hi,
I need exceljs library to be uploaded on slate for using it to format the excel file before it is downloaded.

can anyone help me on downloading and uploading exceljs on slate and further steps of downloading object set in excel file format with required formatting like , cell formatting, text formatting etc.

[drusak]

Function libraries (and advanced widgets like Code Sandbox) are a powerful functionality, but they do come with various security-related limitations. Among them is the requirement that libraries should be bundled in a specific way. In particular, exceljs.js might need a specific distribution to be used as a Function library - personally I didn’t manage to use the version from CDN or their github repo with success. This library can be used out of the box in Code Sandbox as described in the docs, and you can then refer to the global ExcelJS in your sandboxed javascript. Given though that you’re interested in using the library to format the data coming from an object set, I am going to recommend another solution - Functions on Objects, for which there is first-class Slate support.

One of the advantages of this approach is that you can manage external dependencies like exceljs in a friendly way, instead of needing to upload libraries manually. Please see the steps below for an end-to-end instruction!

1. First, create a typescript functions repository.

2. In package.json in the repository, add exceljs as a dependency (general docs on how to do it can be found here)
   

   

3. Install the library by hitting the Add and install library in the Libraries tab on the left-hand side

5. You can now use the library in your code! Example of usage below:

import { Function } from "@foundry/functions-api";
import * as ExcelJS from "exceljs";

export class MyFunctions {
  private generateXls(): ExcelJS.Workbook {
    const workbook = new ExcelJS.Workbook()
    const worksheet = workbook.addWorksheet('People')
    worksheet.columns = [
    { header: 'Id', key: 'id', width: 10 },
    { header: 'Name', key: 'name', width: 10 },
    { header: 'D.O.B.', key: 'DOB', width: 30 }
    ]
    worksheet.addRow([10086, 'Ken1', 'YYYY-MM-DD'])
    worksheet.addRow([10087, 'Ken2', 'YYYY-MM-DD'])
    worksheet.addRow([10088, 'Ken3', 'YYYY-MM-DD'])
    
    return workbook;
  }

  @Function()
  public async generateExcelTextDataCustom(): Promise<string> {
    var workbook = this.generateXls()
    
    // We get a Buffer
    var xls64 = await workbook.xlsx.writeBuffer()
    
    // Get the data out of the buffer
    const json = JSON.stringify(xls64)
    
    return json;
  }
}

6. Now you’re ready to publish your new function!
7. Once function is published, you can reference it in Platform tab in Slate - you can see on the screenshot below that the function produces precisely what we asked it to in our code repository.

8. You have asked about combining the data from an Object Set with the excel formatting - Functions on Objects are great for such workflows. You can have your Function perform both - load the objects from the object set, extract any properties from these objects and format their values using ExcelJS. Please refer to documentation.
9. As for the last step of downloading the data, we come to the idea of using Code Sandbox again! Below are the contents of the respective widget tabs set up to work with our Function
10. In Javascript tab:
    
    

async function saveFile (fileName, xls64) {
  console.log("[FoO] Step 1. Before Blob", xls64)
  var curr_excel = new Blob([xls64], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })
  console.log("[FoO] Step 2. Blob", curr_excel)
  var url = window.URL.createObjectURL(curr_excel, "data:attachment/text", "test.xlsx");
  var content = document.getElementById("content");
  content.setAttribute("href", url)
  content.setAttribute("download", "test.xlsx")
}

SlateFunctions.watch("ff_foundry_function_1", (newValue, oldValue) => {
  console.log("[FoO] Preprocess 1. From FoO : ", newValue)
  var parsed_json = JSON.parse(newValue)
  console.log("[FoO] Preprocess 2. Parsed JSON : ", parsed_json)
  var uarray = new Uint8Array(parsed_json["data"]);
  // var ubuffer = Buffer.from(value);
  console.log("[FoO] Preprocess 3. Uint8Array : ", uarray)

  saveFile('fileNameXXX', uarray);
})

11. In HTML tab:
    
    

<h1 id="title">
  <a id="content">
  Right Click on me... (from Functions)
  </a>
</h1>

12. In Interaction tab, under State

{
"ff_foundry_function_1": "{{ff_foundry_function_1}}"
}

13. Now when right-clicking on the link and opening it in a new tab, we get our Excel downloaded! Of course there’s a myriad of other ways to set up the usage of our Function, but hope this example is useful!

[39f4c63e3e39d0fd3301]

Hi drusak,
Thanks for your response.
For the foundry function code part, could you please provide an example code keeping in mind an ontology which has a backing dummy dataset having 5 records and some 30 columns is imported to function repository, and that has to be filtered for 2 values and then formatting has to be applied using excelJS!

[VincentF]

Here is an example of how can you iterate over a specific object set and build an intermediary datastructure to hold the relevant information for your Excel generation.

import { Function, Integer } from "@foundry/functions-api";
import { Objects, ExampleObjects, ObjectSet} from "@foundry/ontology-api";

type TableRow = {
  [key: string]: string | number;
};

export class MyFunctions {

    @Function()
    public generateSomethingFromObjects(objs: ObjectSet<ExampleObjects>, otherParameter: boolean): string {
        const all_objs = objs.all();

        var rows: TableRow[] = [];
        all_objs.map(curr_obj => 
        
        rows.push( 
            { 
                Name: curr_obj.title ?? "No title", 
                Age: curr_obj.value ?? -1, 
                Occupation:  curr_obj.otherProp ?? "",
            }
        ))
       
        /*         const rowsStatic: TableRow[] = [
            { Name: "John", Age: 34, Occupation: "Engineer" },
            { Name: "Sarah", Age: 28, Occupation: "Designer" },
            { Name: "Mike", Age: 45, Occupation: "Doctor" },
        ];
        */ 
        return this.doSomethingWithThoseObjects(rows);
    }

}

[drusak]

Another note - realized that my initial example of how to download the formatted data via Code Sandbox might be overcomplicated (although if you need very custom styling of the UI element which triggers the download, then it can give you unparalleled flexibility).

There is an Event in Slate, called downloadBlob (see docs here) - if you hook it up to a “trigger” on the page (like a button, see example below) you won’t need to define HTML or most of the javascript one needed for Code Sandbox.

Below I have kept the same naming for the Slate function used in my first reply. Event definition would then be:

const newValue = {{ff_foundry_function_1}}
var parsed_json = JSON.parse(newValue)
var uarray = new Uint8Array(parsed_json["data"]);
var curr_excel = new Blob([uarray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })
console.log(curr_excel)
return curr_excel