Export the output of a function as Excel?

Ask the Community
,
4

A few options:

  • Use Function Backed Export in Workshop - you can write a function that returns a string, that will be downloaded as a CSV by end users in Workshop
  • Use a Function On Object to write an attachment that is an Excel file, and let the user download this attachment in Workshop or elsewhere
  • Use a Function On Object to return an Excel file and use Slate downloadBlob event to download it as a file - Embed the Slate in the Workshop application

Function Backed Export in Workshop

This solution is particularly useful, if a CSV would fulfill the need.

You can call a Function that returns a string containing the content of a CSV.
Here the content of the CSV is hardcoded, but you could use queries to the Ontology, to Model, calls to web-hooks, … to actually fetch the data required to populate the CSV string.

    @Function()
    public exampleFunctionThatOutputsCSV(): string {
        // See /docs/foundry/workshop/widgets-button-group#function-backed-export
         return "col1,col2,col3\nval1,val2,val3";
    }

You can then call it from Workshop, on click of a button:
image

Which will get you a CSV, that you can open in Excel:
image

Function to write an Attachment

This solution is particularly useful if you want to persist the Excel generated as an attachment on an Object. For example, if you want one user to generate an Excel - report like - and others can access the Excel instead of re-generating it

In a Typescript Function Code repository, we can write a Function that will create an attachment which contains an Excel file blob .

Here the content of the Excel is hardcoded, but you could use queries to the Ontology, to Model, calls to web-hooks, … to actually fetch the data required to populate the Excel file.

You will need to import those libraries:

  • exceljs
  • text-encoding
import { Function, Integer, Attachment, Attachments } from "@foundry/functions-api"; // 
import { Buffer } from 'buffer';
// Import ""es2017", "dom"" in the tsconfig.json to have the Blob made available

import { Objects, exampleReportFileObject } from "@foundry/ontology-api";
import * as ExcelJS from 'exceljs';

declare var require:any
const te = require("text-encoding");

interface Blob {
    readonly size: number;
    readonly type: string;
    slice(start?: number, end?: number, contentType?: string): Blob;
}

export class GenerateAttachment {

    private _generate_xls(): 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 create_and_upload_attachment(obj: exampleReportFileObject): Promise<void> {
        var workbook = this._generate_xls()
        // We get a Buffer
        var xls64 = await workbook.xlsx.writeBuffer()
        var uarray = new Uint8Array(xls64);
        var curr_excel = new Blob([uarray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })

        // create attachment
        const createdAttachment = await Attachment.uploadFile("my-excel.xls", curr_excel);

        // attach it to one object (can do more)
        obj.attachmentRid = createdAttachment;
    }

My tsconfig content looks like:

{
// <...>
    "lib": [
      "esnext",
      "es2017", "dom"
    ],
//  <...>
}

Function to return an Excel Blob to Slate

This solution is particularly useful if you want to not persist the file and require an actual Excel file (not CSV). Overall this solution gives the most flexibility.

In a Typescript Function Code repository, we can write a Function that will return an Excel blob serialized.

You will need to import those libraries:

  • exceljs
  • textencoding

Here the content of the Excel is hardcoded, but you could use queries to the Ontology, to Model, calls to webhooks, … to actually fetch the data required to populate the Excel file.

import { Function, Integer } from "@foundry/functions-api";
import { Buffer } from 'buffer';
import * as ExcelJS from 'exceljs';

declare var require:any
const te = require("text-encoding");

export class MyFunctions {

    private _generate_xls(): 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 generate_excel_text_data_custom(): Promise<string> {
        var workbook = this._generate_xls()

        // We get a Buffer
        var xls64 = await workbook.xlsx.writeBuffer()

        // Get the data out of the buffer
        const json = JSON.stringify(xls64)

        return json;
    }

In Slate, we will call the Foundry Function (potentially with some input, the below example doesn’t have input, as that’s just an example)

image
image767×605 34.3 KB

const newValue = {{ff_generate_excel_text_data_custom}}
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

In Slate, an event will define that on click of a button, the “downloadBlob” action will be executed:

image
image897×160 24.8 KB

The file will be downloaded.

1 Like