๐Ÿ†•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:

  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

  1. 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, 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)

  1. Apply style to a header cell

let columns = [
	{
		header: "Name",
		key: "name",
		width: 10,
		headerStyle: {
			font: {
				bold: true
			}
		}
	}
];
  1. Apply style to an entire column except the header

let columns = [
	{
		header: "Name",
		key: "name",
		width: 10,
		style: {
			font: {
				name: "Comic Sans MS"
			}
		}
	}
];
  1. 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"
					}
				}
			}
		]
	}
];
  1. 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 propertyDescriptionExample value(s)

name

Specifies the font name.

'Arial', 'Calibri', etc.

family

Specifies the font family for fallback as an integer value.

1 - Serif, 2 - Sans Serif, 3 - Mono, Others - unknown

scheme

Specifies the font scheme.

'minor', 'major', 'none'

charset

Specifies the font character set as an integer value.

1, 2, etc.

size

Specifies the font size as an integer value.

9, 10, 12, 16, etc.

color

Specifies the font color as an ARGB object.

{argb: 'FFFF0000'}

bold

Specifies whether the font is bold, indicating weight.

true, false

italic

Specifies whether the font is italic, indicating slope.

true, false

underline

Specifies the font underline style.

true, false, 'none', 'single', 'double', 'singleAccounting', 'doubleAccounting'

strike

Specifies whether the font has strikethrough.

true, false

outline

Specifies whether the font has an outline.

true, false

vertAlign

Specifies the font's vertical alignment.

'superscript', 'subscript'

Alignment

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

Alignment formatting options

horizontalverticalwrapTextshrinkToFitindentreadingOrdertextRotation

left

top

true

true

integer

rtl

0 to 90

center

middle

false

false

ltr

-1 to -90

right

bottom

vertical

fill

distributed

justify

justify

centerContinuous

distributed

Borders

// 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

Fills

// fill with red darkVertical 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 fills

PropertyRequiredDescription

type

Yes

Specifies that this fill uses a pattern.

pattern

Yes

fgColor

No

Specifies the pattern's foreground color.

The default color is black.

bgColor

No

Specifies the pattern's background color.

The default color is white.

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 fills

PropertyRequiredDescription

type

Yes

Specifies that this fill uses a gradient.

gradient

Yes

Defines the type of gradient, which can be either "angle" or "path."

degree

angle

  • Indicates the gradient's direction.

  • A value of 0 places it from left to right.

  • Values from 1 to 359 rotate the direction clockwise.

center

path

  • Specifies the relative coordinates for the start of the gradient path.

  • "Left" and "Top" values range from 0 to 1.

stops

Yes

  • Specifies the gradient's color sequence.

  • An array of objects defines the position and color, starting at position 0 and ending at position 1.

  • Additional positions can specify other colors on the path.


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.

// 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)'};

Dates

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

Last updated