To dynamically create customizable, styled, multi-sheet Excel files directly from databases

With this function, you can create an XLSX file with customizable content and multiple sheets, reflecting the data and style defined in the input parameters.

The resulting file is saved directly in Ninox, offering dynamic data management and formatting options.

Syntax

createXLSX(nid, any, string)

Return

file

Examples

sample_script (2KB)

sample_result.xlsx (7KB)

Using createXLSX

To create an Excel file with the createXLSX function:

1. Create a Button field: When clicked, the button triggers a function to create an Excel file from the data provided in the formula editor.
2. Create an Image field: Serves a link, allowing you to download the file directly.

Now enable edit mode and click the On click field. In the formula editor:

1. Define the columns and rows.
2. Define the worksheet structure.
3. Use the createXLSX function.
4. Define styles and formatting (optional).
5. Save the script. Create the Excel file by clicking the button.

Define columns and rows

First, create an object to define the columns:

let columns = [
    {
        header: "Name",
        key: "name",
        width: 10,
    },
    {
        header: "Age",
        key: "age",
        width: 10
    },
    {
        header: "URL",
        key: "url",
        width: 30
    },
    {
        header: "Description",
        key: "description",
        width: 20
    }
];

Next, define the rows. You can use Supported special fields (read below), if you need to:

let rows = [
{
name: "Luis Gómez",
age: 30,
url: {
text: "www.google.com",
hyperlink: "http://www.google.com",
tooltip: "www.google.com"
}
},
{
name: "Maria Silva",
age: 25,
url: {
text: "www.google.com",
hyperlink: "http://www.google.com",
tooltip: "www.google.com"
}
},
{
name: "Ayesha Khan",
age: 35,
url: {
text: "www.google.com",
hyperlink: "http://www.google.com",
tooltip: "www.google.com"
}
},
{
name: "Li Wei",
age: 40,
url: {
text: "www.google.com",
hyperlink: "http://www.google.com",
tooltip: "www.google.com"
}
},
{
name: "Rajesh Kumar",
age: 21,
url: {
text: "www.google.com",
hyperlink: "http://www.google.com",
tooltip: "www.google.com"
}
},
{
name: "Sofia Müller",
age: 24,
url: {
text: "www.google.com",
hyperlink: "http://www.google.com",
tooltip: "www.google.com"
}
}
];

Define worksheet structure

Define a worksheet with columns and rows:

let worksheets = {
Sheet1: {
columns: columns,
rows: rows
}
};

Use createXLSX

Call the createXLSX function with the defined worksheets:

Image := createXLSX(this, worksheets, "example.xlsx")

Define styles and formatting (optional)

Apply style to a header cell:

let columns = [
{
header: "Name",
key: "name",
width: 10,
headerStyle: {
font: {
bold: true
}
}
}
];

Apply style to an entire column except the header:

let columns = [
{
header: "Name",
key: "name",
width: 10,
style: {
font: {
name: "Comic Sans MS"
}
}
}
];

Apply style to an entire row:

let rows = [
{
name: "Luis Gómez",
age: 30,
// hyperlink field
url: {
text: "www.google.com",
hyperlink: "http://www.google.com",
tooltip: "www.google.com"
},
styles: [
{
fill: {
type: "pattern",
pattern: "solid",
fgColor: {
argb: "F08080"
}
}
}
]
}
];

Apply style to specific cells in a row:

let rows = [
{
name: "Luis Gómez",
age: 30,
// hyperlink field
url: {
text: "www.google.com",
hyperlink: "http://www.google.com",
tooltip: "www.google.com"
},
styles: [
{
targets: ["name", "age"],
fill: {
type: "pattern",
pattern: "solid",
fgColor: {
argb: "F08080"
}
}
}
]
}
];

Create Excel file

Finally, when you've saved your script, click the button to create an Excel file.

Supported styles and formatting options

Font

{
  font: {
    name: "Arial Black",
    color: { argb: "FF00FF00" },
    family: 2,
    size: 14,
    italic: true,
    underline: true,
    bold: true
  }
}

Font formatting options

Alignment

{ alignment: { vertical: "top", horizontal: "left" }

Excel-Datei erstellen

Wenn Sie Ihr Skript gespeichert haben, klicken Sie abschließend auf den Button, um eine Excel-Datei zu erstellen.

Unterstützte Stil- und Formatierungsoptionen

Schriftart

{
  font: {
    name: "Arial Black",
    color: { argb: "FF00FF00" },
    family: 2,
    size: 14,
    italic: true,
    underline: true,
    bold: true
  }
}

Schriftformatierungsoptionen

Ausrichtung

{ alignment: { vertical: "top", horizontal: "left" }

Alignment options

Fill

{
  fill: {
    type: "pattern",
    pattern: "solid",
    fgColor: { argb: "FF0000FF" }
  }
}

Fill options

Border

{
  border: {
    top: { style: "thin", color: { argb: "FF000000" } },
    left: { style: "thin", color: { argb: "FF000000" } },
    bottom: { style: "thin", color: { argb: "FF000000" } },
    right: { style: "thin", color: { argb: "FF000000" } }
  }
}

Border options

Border style options