XEROF

xlsgen 4.5.0.11 : Special cells

Build 4.5.0.11 of xlsgen adds exposure to the SpecialCells property in xlsgen, which is a mechanism for selecting cells based on one of a number of conditions.

The application interface for special cells in xlsgen is the programmatic equivalent of the special cells user interface dialog in Excel, raised from Ctrl+G and then the Special button.

Special cells is a range of cells, single area or a union of areas, that meets a certain criteria. One example, is cells carrying a formula. Another is cells with number literals. The conditions are one of these :

specialcells_type_allformatconditions : cells with conditional formatting(s)
specialcells_type_allvalidation : cells with data validation(s)
specialcells_type_blanks : blank (empty) cells
specialcells_type_comments : cells with comment notes
specialcells_type_constants : cells with non-formula values (*)
specialcells_type_formulas : cells carrying formulas (*)
specialcells_type_lastcell : last cell of the range (bottom right corner)
specialcells_type_sameformatconditions : cells with the same conditional formatting
specialcells_type_samevalidation: cells with the same data validation
specialcells_type_visible : visible cells (neither row nor column are hidden)

(*) : If the chosen condition is either constants or formulas, then the application may filter according to the literal data type, one or more of the following :

specialcells_value_na : not applicable (no filter)
specialcells_value_error : cells with formula errors
specialcells_value_logical : cells with a logical (boolean) value
specialcells_value_number : cells with numbers (integer, float, date)
specialcells_value_text : cells with text

Special cells is exposed as a range property, so an existing range must be specified to limit the bounding area where the special cells filtering applies. If the client application knows no such area, it can pass either A1 as range or use the UsedRange which is the area of the current worksheet that encompasses all content and formulas. Here is an example :

Java code


// find cells with a formula, turn them red
XlsRange r = workbook.getWorksheetByIndex(1).
    getUsedRange().getSpecialCells(xlsgen.specialcells_type_formulas, xlsgen.specialcells_value_na);
r.getStyle().getPattern().putBackgroundColor(0xFF0000);
r.Apply();