Skip to main content

XLS - Excel

Process custom Excel files.

Introduction​

Netuno generates EXCEL files compatible with LibreOffice, OpenOffice, Microsoft Office, Google Drive, etc., through the XLS resource, which provides a low-code abstraction of Apache POI for the various programming languages supported by Netuno.

We can generate XLSX, XLS, or ODS files.

The application that comes with Netuno called demo (the demonstration application) contains many examples of possible code implementations in various languages, including a demonstration of spreadsheet export through the export-excel service located at:

  • netuno/apps/demo/server/services/samples/javascript/export-excel.js

Initialization​

To initialize the XLS resource:

const excel = _xls.create()

Creates the object that contains the context of the new file to be built, with an empty worksheet.

Formatting​

The definition of visual formatting styles—that is, date, time, currency formats, and others—must be done before defining cell values, because when defining cell values it is possible to associate their formatting style along with them.

Thus, as demonstrated in the example included in the demo application, the formats are defined at the beginning of the code.

To format dates:

const styleDate = excel.cellStyleFormat("dd/mm/yy")

To format times:

const styleTime = excel.cellStyleFormat("hh:mm:ss")

To format dates with times:

const styleDateTime = excel.cellStyleFormat("dd/mm/yyyy hh:mm:ss")

To format currency values in euros:

const styleMoneyEuro = excel.cellStyleFormat("€#,##0.00;\-€#,##0.00")

To format currency values in Brazilian reais:

const styleMoneyBRL = excel.cellStyleFormat("R$#,##0.00;\-R$#,##0.00")

To format values as percentages:

const stylePercentage = excel.cellStyleFormat("0.00%")

Appearance​

The definition of visual appearance styles—that is, the appearance of cells such as colors, font, borders, alignment, and others—must be done before defining cell values, because when defining cell values it is possible to associate their appearance style along with them.

Thus, as demonstrated in the example included in the demo application, the appearances are defined at the beginning of the code.

Example of defining colors and alignment:

const styleHeader = excel.cellStyle()
styleHeader.setFillPattern(_xls.fillPattern("solid-foreground"))
styleHeader.setFillBackgroundColor(excel.color("black"))
styleHeader.setAlignment(_xls.horizontalAlignment("center"))

Documentation with all methods of the Styles object in Apache POI.

Defining the border style:

const styleData = excel.cellStyle()
styleData.setBorderBottom(_xls.borderStyle("thin"))
styleData.setBorderTop(_xls.borderStyle("thin"))
styleData.setBorderLeft(_xls.borderStyle("thin"))
styleData.setBorderRight(_xls.borderStyle("thin"))

Documentation with all methods of the Styles object in Apache POI.

Creates a new font style for the text and associates the font definitions with a specific existing cell style:

const fontTitle = excel.font()
fontTitle.setBold(true)
fontTitle.setFontHeightInPoints(14)
fontTitle.setColor(excel.color("yellow"))
// Set the font of styleHeader
styleHeader.setFont(fontTitle)

Documentation with all methods of the Font object in Apache POI.

Image​

To insert an image into the worksheet that is located at storage/samples/export-excel/logo.png inside the application:

excel.insertPicture(
    _storage.filesystem("server", "samples/export-excel", "logo.png"),
    1, // Column Number
    1  // Row Number
).resize(2.3)

To insert an image that is in the application at public/images/logo.png:

excel.insertPicture(
    _app.file("public/images/logo.png"),
    10, // Column Number
    1  // Row Number
).resize(1.5)

Documentation with all methods of the Image object in Apache POI.

Merging Cells​

To merge cells in a region, we use the mergedRegion method.

The parameter order is:

  1. Starting Row
  2. Starting Column
  3. Ending Row
  4. Ending Column

Numeric positioning references always start at zero.

In the example below, all cells from column 10 through column 15 that are on row 3 will be merged:

excel.mergedRegion(2, 9, 2, 14)

Cells​

To get the object that represents a cell:

// Gets the cell located in column B and row 2:
const cell = excel.cell(1, 1)

Numeric positioning references always start at zero.

Example of how to log the cell formula:

// Logs the formula of row 2 and column B.
_log.info(
    "Cell Formula B2: "+ excel.cell(1, 1).getCellFormula()
)

Example of how to get the text value of a cell:

// Logs the text value of the cell in row 2 and column B.
_log.info(
    "Cell Value B2: "+ excel.cell(1, 1).getStringCellValue()
)

Documentation with all methods of the Cell object in Apache POI.

Rows​

To get the object that represents a row:

// Gets the object that represents row 2.
const row = excel.row(1)

Numeric positioning references always start at zero.

In the example above, if the row does not exist, a new row will be created automatically.

Documentation with all methods of the Row object in Apache POI.

Inserting Tabular Data​

We can insert a data table using the addDataTable method.

Since the first two parameters define the position where the data table should be inserted, they are the row and column numbers.

Note that positioning in the code for rows and columns always starts from zero.

That is, Row 1 is position 0, and Column A is also position 0.

Next, we pass a list of rows, where each row contains another list for the columns, and only then do we add a map with the cell value.

Complete example:

excel.addDataTable(
    6, 1,
    _val.list() // Rows
        .add(
            _val.list() // Column
                .add(
                    _val.map() // Cell
                        .set("value", "Name")
                )
                .add(
                    _val.map()
                        .set("value", "Age")
                )
        )
        .add( // Another Row
            _val.list()
                .add( // First Column
                    _val.map()
                        .set("value", "Maria")
                )
                .add( // Second Column
                    _val.map()
                        .set("value", 26)
                )
        )
)

Appearance​

In the cell map, we can associate the styles already created, for example using the styles defined in the appearance and formatting section we saw earlier.

The following is an example of how to associate styles with cells:

excel.addDataTable(
    6, 1,
    _val.list()
        .add(
            _val.list()
                .add(
                    _val.map()
                        .set("value", "Name")
                        .set("style", styleHeader)
                )
                .add(
                    _val.map()
                        .set("value", "Age")
                        .set("style", styleHeader)
                )
        )
)

Formulas​

To insert formulas, we use the formula key in the cell definition map.

In this example, the sum of all values from the other cells will be performed in the last cell:

excel.addDataTable(
    6, 1,
    _val.list()
        .add(
            _val.list()
                .add(_val.map().set("value", 45))
                .add(_val.map().set("value", 26))
        )
        .add(
            _val.list()
                .add(_val.map().set("value", 51))
                .add(_val.map().set("value", 30))
        )
        .add(
            _val.list()
                .add(
                    _val.map().set("value", "Total")
                )
                .add(
                    _val.map().set("formula", "SUM(B7:C8)")
                )
        )
)

To get the column letter from the column number, you can use the columnReference method, which can help when defining formulas. The same method can also be used to get the column number from the reference letter, for example:

excel.addDataTable(
    6, _xls.columnReference('B'),
    _val.list()
        .add(
            _val.list()
                .add(_val.map().set("value", 45))
                .add(_val.map().set("value", 26))
        )
        .add(
            _val.list()
                .add(_val.map().set("value", 51))
                .add(_val.map().set("value", 30))
        )
        .add(
            _val.list()
                .add(
                    _val.map().set("value", "Total")
                )
                .add(
                    _val.map().set(
                        "formula",
                        "SUM("+ _xls.columnReference(1) +"7:"+ _xls.columnReference(2) +"8)"
                )
            )
        )
)

Sheets​

To create a new worksheet (sheet) within the document itself, see the following example:

const newSheet = excel.createSheet("New Sheet")

In the methods for manipulating cells, images, and data, the sheet object reference can be added as the first parameter.

Depending on which sheet is active, it is possible to change the active sheet with the activeSheet method.

Example of how to insert an image into the new sheet:

excel.insertPicture(
    newSheet,
    _app.file("public/images/logo.png"),
    10, // Column Number
    1  // Row Number
).resize(1.5)

Example of how to insert a data table into the new sheet:

excel.addDataTable(
    newSheet,
    6, 1,
    _val.list()
        .add(
            _val.list()
                .add(_val.map().set("value", 45))
                .add(_val.map().set("value", 26))
        )
        .add(
            _val.list()
                .add(_val.map().set("value", 51))
                .add(_val.map().set("value", 30))
        )
        .add(
            _val.list()
                .add(
                    _val.map().set("value", "Total")
                )
                .add(
                    _val.map().set("formula", "SUM(B7:C8)")
                )
        )
)

Activate​

So, to avoid having to constantly indicate in the methods which sheet should be processed, as in the examples above, as an alternative you can indicate which sheet is active.

All methods for manipulating cells, images, and data use the sheet that is activated when a specific sheet is not indicated.

See two ways to activate a specific sheet, first with the object reference and then with the sheet index:

excel.activeSheet(mySheet) // Sheet reference object.
excel.activeSheet(1) // Index of the sheet that will be activated.

Useful Resources​

Examples of other useful methods for manipulating sheets.

Get a sheet by name:

// Gets the sheet from its name.
const mySheet = excel.getSheet("Sheet Name")

Get a sheet by its index:

// Gets the sheet from its number.
const mySheet = excel.getSheet(1)

To get the total number of sheets:

// Total number of sheets.
const totalSheets = excel.getNumberOfSheets()

To list all existing sheets in the document.

// Lists all sheets.
const allSheets = excel.getAllSheets()
for (const sheet of allSheets) {
    ...
}

Documentation with all methods of the Sheet object in Apache POI.

Generate File​

In the save and output methods, the final file binary is generated.

Sends the generated file as the service's data output (output):

excel.output("file.xlsx")

To save the generated file in the application's storage/filesystem folder:

excel.save(_storage.filesystem("server", "file.xlsx"))

To save the generated file to the /tmp folder using the hard drive file system:

excel.save(_os.file("/tmp/file.xlsx"))

Edit File​

We can edit files by opening them, making the necessary changes, and then saving the new edited file.

Below are some examples of how to open files.

Open a file at the root of the application:

const excel = _xls.open(_app.file("file.xlsx"))

To open the generated file in the application's storage/filesystem folder:

const excel = _xls.open(_storage.filesystem("server", "file.xlsx"))

And also to open the file in the /tmp folder using the hard drive file system:

const excel = _xls.open(_os.file("/tmp/file.xlsx"))

In the examples above, the excel constant will contain the editing object with the context of the respective file, so it is possible to perform any type of manipulation.

When you finish the changes, generate the new file.

It is not possible to directly change the same opened file; it is necessary to generate a new file with a different path.

To directly change the same file, you can do so using inputStream.

The following is an example of how to change a cell value:

const fileInput = _app.file("file.xlsx").inputStream()
const excel = _xls.open(fileInput)

excel.setCellData(
    10, 1,
    _val.map()
        .set(
            "value",
            excel.getCellData(10, 1).getInt("value") + 10
        )
)

fileInput.close() // Closes the opened file.
excel.save(_app.file("file.xlsx")) // Saves the new version.

Read File​

Extracting data from Excel files is done with the read method.

const data = _xls.read(_app.file("file.xlsx"))

The data is obtained in a structure of Values objects that contains Lists for sheets, rows, and columns, and Maps (key and value) for cells.

To view all the data, we can set it as the service's data output:

_out.json(data)

To perform the iteration, we must keep in mind that the data structure is organized in the following order:

  1. sheets - Sheets
  2. rows - Rows
  3. columns - Columns

Example of a complete iteration:

for (const sheet of data.getValues("sheets")) {
    _out.println(`<h4>Sheet: ${sheet.getInt("index")} - ${sheet.getString("name")}</h4>`)
    for (const row of sheet.getValues("rows")) {
        _out.println(`<h4>Row: ${row.getInt("row")}</h4>`)
        _out.println("<p>")
        for (const column of row.getValues("columns")) {
            _out.print(`${column.getInt("row")}:${column.getInt("column")}`)
            _out.print(` # ${column.getString("address")}`)
            _out.print(` # ${column.getString("type")}`)
            _out.print(` # ${column.getString("value")}`)
            _out.println("<br>")
        }
        _out.println("</p>")
    }
}

The cell type is obtained through the code:

  • column.getString("type")

And the supported types are:

  • string - Text content.
  • numeric - Numeric content or date and/or time content.
  • boolean - True or false.
  • blank - Blank cell.
  • formula - Cell that contains a formula.
  • error - Error in cell processing.

Additional Parameters​

For each cell type, we have some additional parameters.

string - Cell type with textual content.

  • value - Cell text.
  • richValue - Cell formatting.

numeric - Cell type with numeric content, or dates and also times.

  • value - Value of the cell's numeric content.

In the case of dates and times, we have these additional parameters:

  • localDateTime - Java Time LocalDateTime object that represents the cell's date and time value.
  • localDate - Java Time LocalDate object that represents the cell's date value.
  • localTime - Java Time LocalTime object that represents the cell's time value.
  • instant - Java Time Instant object that represents the cell's date and/or time value.
  • date - Java Date object that represents the cell's date and/or time value.

boolean - Cell type with boolean content, that is, true or false.

  • value - Value of type boolean, true or false.

blank - Cell type without content, therefore it does not contain additional parameters.

formula - Cell type that contains calculation formulas.

  • value - Final value resulting from the calculation.
  • formula - Contains the formula expression used for the calculation.

error - Cell type that contains an error.

  • value - Byte value of the generated error.
  • code - If there are errors in the formula calculation, provides the code of the error that occurred here.

Videos​

Below are some example videos on how to generate Excel/XLSX files programmatically: