Excel File
Reading an xls file stored in the specified path
Read up to 10000 rows x 1000 columns from the xls file stored in the specified path and get back the content of a specific folder
Syntax
Details
Values stored in each js object can be accessed as: object.get("attributeName")
Export data from database in xlsx format
Starting from a SQL query and bind variables, generate an xlsx file.
It is possible to customize this file in a variety of different ways:
defining the sheet name
adding new sheets to an already existing xlsx (used as template)
adding a row at the beginning, reporting the column headers
customizing horizontal alignment, column width, background/foreground color for each cell (both for header and data rows), font (name, size, bold/italic/underline), data format (for dates and numbers)
breaking up rows in sections, according to the value of one column (grouping data) and adding an header row at the beginning of each section; optionally, a footer row can be included as well, where specifying Excel functions (es. SUM, COUNT, etc.)
Syntax
Details
Argument
Description
templateDirectoryId
can be null; if specified, the xlsx file to create will be generated starting from a copy of an already existing xlsx file to use as a template; such a template file is located in this directory; if not specified, the xlsx file to create will be generated form scratch
templateFileName
can be null; this argument must be specified together with "templateDirectoryId: it represents the xlsx file name of the template file to use when creating the new xlsx file
sheetName
name for the sheet to create within the xlsx file
outDirectoryId
path where storing the xlsx file
outFileName
xlsx file to create, within the path specified through "outDirectoryId"
formatHeaderColumns
can be null: in such a case, there will not be a first header row. If specified, it must be list of javascript objects, where each object is related to the settings of a column header, so it can be used to specify the column header properties, like title, foreground/background color, font (name, size, style), alignment. See below for the allowed values for each property
formatColumns
can be null: in such a case, each numeric/date column will be formatted automatically; the column width will be automatically defined, starting from the column header name. If specified, it must be list of javascript objects, where each object is related to the settings of a (data) column, so it can be used to specify the (data) column properties, like width, foreground/background color, font (name, size, style), alignment. See below for the allowed values for each property
grouping
can be null; if specified, it allows to break up a rows according to the value of a specific column, used to group rows. Each time a different value for that column is found, an additional row is added to the spreadsheet. Optionally, a footer row can be included as well, helpful to execute Excel formulas, like SUM or COUNT, for example. The "grouping" attribute must be a javascript object containing at least the attribute "fieldName". See below for the whole object content.
additionalSettings
set it to null, since at the moment is not managed
datastoreId
related to the database scheme where executing the SQL query to use to fill in the xlsx file.
sqlQuery
SQL query to execute
params
list of javascript objects representing the values for the bind variables expressed in the "sqlQuery" through ? symbols.
Structure of a javascript object contained in the list of objects composing the "formatHeaderColumns":
Attribute name
Description
title
header name; it is mandatory
backColor
can be null; background color. It must be expressed as a string, among the ones recognized by Excel and reported below in "Supported colors"
foreColor
can be null; foreground color. It must be expressed as a string, among the ones recognized by Excel and reported below in "Supported colors"
fontName
can be null; font name, recognized by Excel, like "Arial", "Courier", ...
fontSize
can be null; Font dimension, expressed in points, like 100, 200, etc.
bold
can be null; boolean value: if set to true, the title will be set in bold
italic
can be null; boolean value: if set to true, the title will be set in italic (corsive)
underline
can be null; boolean value: if set to true, the title will be underlined
align
horizontal alignment; supported values are: "LEFT", "CENTER", "RIGHT", "JUSTIFY"
Structure of a javascript object contained in the list of objects composing the "formatColumns":
Attribute name
Description
width
can be null; column width, expressed in points
backColor
can be null; background color. It must be expressed as a string, among the ones recognized by Excel and reported below in "Supported colors"
foreColor
can be null; foreground color. It must be expressed as a string, among the ones recognized by Excel and reported below in "Supported colors"
fontName
can be null; font name, recognized by Excel, like "Arial", "Courier", ...
fontSize
can be null; Font dimension, expressed in points, like 100, 200, etc.
bold
can be null; boolean value: if set to true, the title will be set in bold
italic
can be null; boolean value: if set to true, the title will be set in italic (corsive)
underline
can be null; boolean value: if set to true, the title will be underlined
align
horizontal alignment; supported values are: "LEFT", "CENTER", "RIGHT", "JUSTIFY"
format
Structure of the javascript object for "grouping":
Attribute name
Description
fieldName
field name: mandatory field; it must be one of the fields reported in the SELECT clause
showColumn
can be null; boolean value; if set to false, the column related to "fieldName" will be hidden, since it contains always the same value for all rows in the same section
showValue
can be null; boolean value; if set to false, the column related to "fieldName" is shown but the value is not reported (empty cell), since it contains always the same value for all rows in the same section
headerRow
optional attribute: it is a list of javascript objects, where each object defines a cell to include in the header row added before each section. This object must contains a "title" attribute; optionally it can include all other attributes reported for "formatHeaderColumns"; moreover, it is possible to specify "colSpan" attribute, indicating the number of cells occupied by the current cell. The "title" attribute can contain fixed text and/or values coming from the current record, expressed as <FIELDNAME>
footerRow
Same as for "headerRow," but used to define the optional footer section. Unlike the "headerRow", the "title" attribute can also contain a formula, always applied to the current section. A formula is expressed as "=FORMULA" or "=FORMULA,COLUMNNAME". For example, if you want to apply a SUM to all cells in the column B, limited to the current section, you can define "=SUM,B"
List of supported colors, in background/foreground colors:
AQUA , AUTOMATIC , BLACK , BLACK1 , BLUE , BLUE_GREY , BLUE1 , BRIGHT_GREEN , BRIGHT_GREEN1 , BROWN , CORAL , CORNFLOWER_BLUE , DARK_BLUE , DARK_GREEN , DARK_RED , DARK_TEAL , DARK_YELLOW , GOLD , GREEN , GREY_25_PERCENT , GREY_40_PERCENT , GREY_50_PERCENT , GREY_80_PERCENT , INDIGO , LAVENDER , LEMON_CHIFFON , LIGHT_BLUE , LIGHT_CORNFLOWER_BLUE , LIGHT_GREEN , LIGHT_ORANGE , LIGHT_TURQUOISE , LIGHT_TURQUOISE1 , LIGHT_YELLOW , LIME , MAROON , OLIVE_GREEN , ORANGE , ORCHID , PALE_BLUE , PINK , PINK1 , PLUM , RED , RED1 , ROSE , ROYAL_BLUE , SEA_GREEN , SKY_BLUE , TAN , TEAL , TURQUOISE , TURQUOISE1 , VIOLET , WHITE , WHITE1 , YELLOW , YELLOW1
For a preview of these colors, see the following link:
https://github.com/ClosedXML/ClosedXML/wiki/Excel-Indexed-Colors
Example
Grouping data with an header row for each section, containing a description spreading the whole row
Example
Grouping data with an header row for each section, containing a description spreading the whole row; at the end of each section there is also a footer row with the last cell containing the SUM of values for column "E"
Reading a xls or xlsx file stored in the specified path
Read up to the specified number of rows, starting from the specified index (1..x) from the xls or xlsx file stored in the specified path and get back the content of a specific folder
dirId path identifier fileName name of the xls or xlsx file attributeNames, list of attributes, assigned to each column, starting from leftmost column to the right
Syntax
startRow row index; if null it is the first row, i.e. 1
blockSize max number of rows to read, if available; if null it is set to 10000
attributeNames, list of attributes, assigned to each column, starting from leftmost column to the right
A list of js objects, where each object is expressed as a set of couples <attributename, related value>; a 0 length list in case of no more rows available. It supports also nested objects and list of objects: it depends on the definition of the attribute lists. A few examples of attributes: [ "attr1", "subobject.attrsub1", "subobject.attrsub2", "sublist[0].subattr3","sublist[0].subattr4","sublist[1].subattr5" ]
Writing an image in a xls file stored in the server file system
Starting from an already existing Excel (xls), save one or more images in a series of cells, one for each image.
Syntax
Details
Argument
Description
dirId
directory id, where the already existing xls file has been stored; IT MUST BE IN THE SERVER FILE SYSTEM
srcFileName
xls file name
destFileName
file name for the new xls file to create as a copy of the source file, where images will be included
sheetName
sheet name in the source xls file where images must be stored
images
list of javascript objects, one for each cell where saving the corresponding image
Each javascript object contained in the "images" array must have the following content:
x is the column index (mandatory)
y is the row index (mandatory)
w is the image width (optional)
h is the image height (optional)
imagePath (mandatory) is the image absolute path (path+name) on the server file system.
Important note: do not save images too big or this would lead to a very large spreadsheet, difficult to save and later difficult to open by the end user.
Just to make some examples:
Therefore.. pay attention to the max number of rows allowed and the image size!
Last updated