1 of 1

createXLSX

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

Using `createXLSX`

To create an Excel file with the createXLSX function:

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

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 #special-supported-fields, 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" }

Alignment formatting options

Border

// set single thin border
{
  border: {
    top: { style: "thin" },
    left: { style: "thin" },
    bottom: { style: "thin" },
    right: { style: "thin" }
  }
}

// set double thin green border
{
  border: {
    top: { style: "double", color: { argb: "FF00FF00" } },
    left: { style: "double", color: { argb: "FF00FF00" } },
    bottom: { style: "double", color: { argb: "FF00FF00" } },
    right: { style: "double", color: { argb: "FF00FF00" } }
  }
}

// set thick red cross
{
  border: {
    diagonal: { up: true, down: true, style: "thick", color: { argb: "FFFF0000" } }
  }
}

Valid border styles

thin
dotted
dashDot
hair
dashDotDot
slantDashDot
mediumDashed
mediumDashDotDot
mediumDashDot
medium
double
thick

Patterned fill

// fill with red dark vertical stripes
{
  fill: {
    type: "pattern",
    pattern: "darkVertical",
    fgColor: { argb: "FFFF0000" }
  }
}

// fill with yellow dark trellis and blue behind
{
  fill: {
    type: "pattern",
    pattern: "darkTrellis",
    fgColor: { argb: "FFFFFF00" },
    bgColor: { argb: "FF0000FF" }
  }
}

// fill with solid coral
{
  fill: {
    type: "pattern",
    pattern: "solid",
    fgColor: { argb: "F08080" }
  }
}

// fill with blue-white-blue gradient from left to right
{
  fill: {
    type: "gradient",
    gradient: "angle",
    degree: 0,
    stops: [
      { position: 0, color: { argb: "FF0000FF" } },
      { position: 0.5, color: { argb: "FFFFFFFF" } },
      { position: 1, color: { argb: "FF0000FF" } }
    ]
  }
}

// fill with red-green gradient from center
{
  fill: {
    type: "gradient",
    gradient: "path",
    center: { left: 0.5, top: 0.5 },
    stops: [
      { position: 0, color: { argb: "FFFF0000" } },
      { position: 1, color: { argb: "FF00FF00" } }
    ]
  }
}

Pattern fill options

To fill a cell using the solid pattern, you don't need to specify bgColor.

Valid pattern types

none
solid
darkGray
mediumGray
lightGray
gray125
gray0625
darkHorizontal
darkVertical
darkDown
darkUp
darkGrid
darkTrellis
lightHorizontal
lightVertical
lightDown
lightUp
lightGrid
lightTrellis

Gradient fill

Supported special fields

The function supports special fields like hyperlinks, rich text, and formulas:

Hyperlinks provide links to web content or internal references.
Rich text allows for mixed-format text, including bold, italic, and other font styles.
Formulas enable cells to compute values dynamically.
Dates can be used directly from Ninox.

Hyperlink

// link to web
value = {
  text: "www.mylink.com",
  hyperlink: "http://www.mylink.com",
  tooltip: "www.mylink.com"
};
// internal link
value = {
  text: "Sheet2",
  hyperlink: "#'Sheet2'!A1"
};

Rich text

value = {
  richText: [
    { text: "This is" },
    { font: {italic: true}, text: "italic" },
  ]
};

Formula

value = { formula: "A1+A2" };
value = { formula: "SUM(A1,A2)" };

Date

let columns = [{
    header: "Birthdate",
    key: "birthdate",
    width: 10,
    date: true
}];

createXLSX

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

Using `createXLSX`

To create an Excel file with the createXLSX function:

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

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 #special-supported-fields, 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

Font property

Description

Example value(s)

Alignment

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

Alignment formatting options

horizontal

vertical

wrapText

shrinkToFit

indent

readingOrder

textRotation

Border

// set single thin border
{
  border: {
    top: { style: "thin" },
    left: { style: "thin" },
    bottom: { style: "thin" },
    right: { style: "thin" }
  }
}

// set double thin green border
{
  border: {
    top: { style: "double", color: { argb: "FF00FF00" } },
    left: { style: "double", color: { argb: "FF00FF00" } },
    bottom: { style: "double", color: { argb: "FF00FF00" } },
    right: { style: "double", color: { argb: "FF00FF00" } }
  }
}

// set thick red cross
{
  border: {
    diagonal: { up: true, down: true, style: "thick", color: { argb: "FFFF0000" } }
  }
}

Valid border styles

thin
dotted
dashDot
hair
dashDotDot
slantDashDot
mediumDashed
mediumDashDotDot
mediumDashDot
medium
double
thick

Patterned fill

// fill with red dark vertical stripes
{
  fill: {
    type: "pattern",
    pattern: "darkVertical",
    fgColor: { argb: "FFFF0000" }
  }
}

// fill with yellow dark trellis and blue behind
{
  fill: {
    type: "pattern",
    pattern: "darkTrellis",
    fgColor: { argb: "FFFFFF00" },
    bgColor: { argb: "FF0000FF" }
  }
}

// fill with solid coral
{
  fill: {
    type: "pattern",
    pattern: "solid",
    fgColor: { argb: "F08080" }
  }
}

// fill with blue-white-blue gradient from left to right
{
  fill: {
    type: "gradient",
    gradient: "angle",
    degree: 0,
    stops: [
      { position: 0, color: { argb: "FF0000FF" } },
      { position: 0.5, color: { argb: "FFFFFFFF" } },
      { position: 1, color: { argb: "FF0000FF" } }
    ]
  }
}

// fill with red-green gradient from center
{
  fill: {
    type: "gradient",
    gradient: "path",
    center: { left: 0.5, top: 0.5 },
    stops: [
      { position: 0, color: { argb: "FFFF0000" } },
      { position: 1, color: { argb: "FF00FF00" } }
    ]
  }
}

Pattern fill options

Property

Required

Description

To fill a cell using the solid pattern, you don't need to specify bgColor.

Valid pattern types

none
solid
darkGray
mediumGray
lightGray
gray125
gray0625
darkHorizontal
darkVertical
darkDown
darkUp
darkGrid
darkTrellis
lightHorizontal
lightVertical
lightDown
lightUp
lightGrid
lightTrellis

Gradient fill

Property

Required

Description

Supported special fields

The function supports special fields like hyperlinks, rich text, and formulas:

Hyperlinks provide links to web content or internal references.
Rich text allows for mixed-format text, including bold, italic, and other font styles.
Formulas enable cells to compute values dynamically.
Dates can be used directly from Ninox.

Hyperlink

// link to web
value = {
  text: "www.mylink.com",
  hyperlink: "http://www.mylink.com",
  tooltip: "www.mylink.com"
};
// internal link
value = {
  text: "Sheet2",
  hyperlink: "#'Sheet2'!A1"
};

Rich text

value = {
  richText: [
    { text: "This is" },
    { font: {italic: true}, text: "italic" },
  ]
};

Formula

value = { formula: "A1+A2" };
value = { formula: "SUM(A1,A2)" };

Date

let columns = [{
    header: "Birthdate",
    key: "birthdate",
    width: 10,
    date: true
}];

createXLSX

Syntax

Return

Examples

Using createXLSX

Define columns and rows

Define worksheet structure

Use createXLSX

Define styles and formatting (optional)

Create Excel file

Supported styles and formatting options

Font

Font formatting options

Alignment

Alignment formatting options

Border

Valid border styles

Patterned fill

Pattern fill options

Valid pattern types

Gradient fill

Supported special fields

Hyperlink

Rich text

Formula

Date

createXLSX

Syntax

Return

Examples

Using createXLSX

Define columns and rows

Define worksheet structure

Use createXLSX

Define styles and formatting (optional)

Create Excel file

Supported styles and formatting options

Font

Font formatting options

Alignment

Alignment formatting options

Border

Valid border styles

Patterned fill

Pattern fill options

Valid pattern types

Gradient fill

Supported special fields

Hyperlink

Rich text

Formula

Date

Using `createXLSX`

Use `createXLSX`

Using `createXLSX`

Use `createXLSX`