In Workshop, I’m backing a chart XY by a function (which itself is calling a live model). I want to export the output of the model, which is backing the chart, as an Excel.
Is it possible to do so ?
What’s the easiest way to do so ?
Hey Vincent, unfortunately we do not currently support exporting directly from the Chart widget.
We do allow exporting files as Excel or CSV via the Object Table widget, and so if your function can be repurposed to fit that, then you can export it that way.
Unfortunately, the function returns a timeseries, which is not a derived column. Hence we can’t use it in a Table without changing the shape of the Ontology for this purpose.
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:
Which will get you a CSV, that you can open in Excel:
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:
exceljstext-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:
exceljstextencoding
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)
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:
The file will be downloaded.
1 Like