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
Structure of a javascript object contained in the list of objects composing the "formatHeaderColumns":
Structure of a javascript object contained in the list of objects composing the "formatColumns":
Structure of the javascript object for "grouping":
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
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