Create xlsx from JSON

import * as wmill from "windmill-client"

import { S3Object } from "windmill-client"

import ExcelJS from "exceljs"

/**

 * Create xlsx from JSON

 * Turn rows (an array of objects) into an .xlsx file written to Windmill object storage, and return its S3 reference. The header row is the union of the rows' keys in first-seen order; nested objects and arrays are written as JSON text. Needs no Microsoft connection and works with any workspace object storage.

 */

export async function main(

  rows: { [key: string]: any }[],

  destination: S3Object,

  sheet_name: string | undefined

) {

  if (!rows?.length) throw new Error("rows needs at least one object")

  const workbook = new ExcelJS.Workbook()

  const sheet = workbook.addWorksheet(sheet_name || "Sheet1")

  const keys = [...new Set(rows.flatMap((row) => Object.keys(row)))]

  sheet.columns = keys.map((key) => ({ header: key, key }))

  for (const row of rows) {

    sheet.addRow(

      Object.fromEntries(

        Object.entries(row).map(([key, value]) => [

          key,

          value != null && typeof value === "object" && !(value instanceof Date)

            ? JSON.stringify(value)

            : value,

        ])

      )

    )

  }

  await wmill.writeS3File(

    destination,

    new Blob([await workbook.xlsx.writeBuffer()])

  )

  return destination

}