GeneXus Office Module

Official Content
This documentation is valid for:
It implements a module for the creation and management of excel spreadsheets.

It includes the following objects:

ExcelSpreadSheet data type module GeneXusOffice.Office.Excel Manages the excel spreadsheet.
ExcelWorksheet data type module GeneXusOffice.Office.Excel Manages a specific worksheet in the spreadsheet.
ExcelCellRange data type module GeneXusOffice.Office.Excel.Cell Manages a cell or a group of cells in the spreadsheet.
ExcelAlignment data type module GeneXusOffice.Office.Excel.Style    Sets the vertical and horizontal alignment of a cell.
ExcelBorderStyle data type module GeneXusOffice.Office.Excel.Style Sets the color and type of the border of a cell.
ExcelCellBorder data type module GeneXusOffice.Office.Excel.Style Sets the different borders of a cell (up, down, left, right and diagonal).
ExcelCellStyle data type module GeneXusOffice.Office.Excel.Style Sets the style of a cell.
ExcelColor data type module GeneXusOffice.Office.Excel.Style Sets the color of a cell.
ExcelFill data type module GeneXusOffice.Office.Excel.Style Sets the style of the background of a cell.
ExcelFont Data Type module GeneXusOffice.Office.Excel.Style Sets the font of a cell.

 

Scope

Languages: Java

Example

event "Excel"

    //opens an excel sheet
    &excelSpreadsheet.Open("C:\testExcel\test.xlsx")

    //select a cell
    &excelcellrange = &excelSpreadsheet.Cell(1,2)

    //sets a value in the cell
    &excelcellrange.ValueText = "hidden"
    

    //sets the style of a cell
    &excelCellStyle = new()
    &excelCellStyle.Hidden = true
    &excelCellStyle.Indent = 5
    &excelCellStyle.Alignment.Horizontal = CellHorizontalAlignment.Left
    //sets the border in the style (diagonal down and bottom borders)
    &ExcelCellStyle.Border.DiagonalDown.Type = CellBorderType.DOUBLE
    &ExcelCellStyle.Border.DiagonalDown.Color.SetColorRGB(200,20,50)
    &ExcelCellStyle.Border.Bottom.Type = CellBorderType.DOUBLE
    &ExcelCellStyle.Border.bottom.Color.SetColorRGB(200,20,50)
    

    //sets the new style in the cell 
    &excelcellrange.SetCellStyle(&excelCellStyle)

    //saves the changes in the sheet and close
    &boolean =&excelSpreadsheet.Save()
    if &boolean
        &excelSpreadsheet.Close()
    else
        msg("Error code:"+&excelSpreadsheet.ErrCode.ToString())    
        msg("Error description:"+&excelSpreadsheet.ErrDescription.ToString())    
    endif

endevent

This module compared to Excel document data type

This module has more features to customize spreadsheets and workbooks than the ExcelDocument data type. But, on the other side, it does not support (by the design of the implementation on which it lies, called POI XSSF in Java) creating spreadsheets with thousands of rows and hundreds of columns.
You may use one or another depending on your needs.

Availability

This feature is available since GeneXus 16 Upgrade 11.