CubeGrid Excel Export Color Formatting

[pci-abarbaro]

Good afternoon,

I was wondering what methods could be overloaded to influence the text color of cells exported from a cube grid to an excel file using exportClientData. I was able to find the method getExportBGColor in order to override what each cells' background color is. Is there a similar method for the text color? Thank you for your time.

v12.1p_2022-06-11/Enterprise Deployment
Chrome(latest)

[Isomorphic]

Hi Abarbaro,

exportClientData is intended for WYSIWYG reporting, however, we try to be careful about what styling information is conveyed to the server, since it would be easy to create an unreadable export if the skin of your application doesn't match the skin people use in their spreadsheet app (eg white text in default Excel theme!).

If you are concerned specifically about the row/column facets, there is cubeGrid.exportFacetTextColor and related APIs to control text colors for export on row and column facets.

Are you trying to control the text color for normal body cell (data values as in record.valueProperty)? If so, do you want a single text color or a variety of colors?

Hilites is one way to control text color that will be used for export. We'll give a more complete response later on, but wanted to get you this information quickly in case it solves your use case already.

[pci-abarbaro]

Indeed I am attempting to color the body cell data text color; using a variety of colors. I have been trying to use hilites to no avail. Is there anything that would prevent hilite values from being used during the export?

[Isomorphic]

We can make some Framework changes to provide more control over color during export. Even without that, though, you can use a custom DataSource operationBinding to modify the standard export object, which is an Apache POI Workbook .

You'll need to use a databound CubeGrid (with a DataSource). Then, you can add a custom operationBinding to modify the colors in your sheet. The example code below actually sets a checkerboard pattern into the data:

<operationBinding operationType="clientExport" operationId="customExport">
    <script language="groovy"><![CDATA[
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.*;

    DSResponse response = dsRequest.execute();
    response.setSendExportResponse(true);

    Object exportObj = response.getExportObject(dsRequest);
    if (!(exportObj instanceof Workbook)) {
        throw new Exception("Invalid export format - not a Workbook");
    }

    Workbook wb = (Workbook) exportObj;
    Sheet sheet = wb.getSheetAt(0);

    int nColFacets = Integer.parseInt(servletRequest.getParameter("nColFacets"));
    int nRowFacets = Integer.parseInt(servletRequest.getParameter("nRowFacets"));

    def createCellStyle(workbook, textColor, fillColor) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillForegroundColor(fillColor.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        Font cellFont = workbook.createFont();
        cellFont.setColor(textColor.getIndex());
        cellStyle.setFont(cellFont);

        return cellStyle;
    }

    CellStyle evenCellStyle = createCellStyle(wb, HSSFColor.HSSFColorPredefined.RED,
                                              HSSFColor.HSSFColorPredefined.LIGHT_GREEN);
    CellStyle oddCellStyle = createCellStyle(wb, HSSFColor.HSSFColorPredefined.YELLOW,
                                             HSSFColor.HSSFColorPredefined.BLUE);

    for (int rowNum = sheet.getFirstRowNum();
         rowNum <= sheet.getLastRowNum() - nColFacets; rowNum++)
    {
        Row row = sheet.getRow(rowNum + nColFacets);
        for (int colNum = row.getFirstCellNum();
             colNum < row.getLastCellNum() - nRowFacets; colNum++)
        {
            Cell cell = row.getCell(colNum + nRowFacets);
            cell.setCellStyle((rowNum + colNum) % 2 == 0 ? evenCellStyle : oddCellStyle);
        }
    }

    return response;

    ]]></script>
</operationBinding>

Note that this custom code depends the parameters "nColFacets" and "nRowFacets", which are sent from the client as part of the DSRequest. If you start with the Showcase sample Advanced CubeGrid, then you'll just have to add a few new lines to the handler for the export button:

exportButton = new Button("Export");
exportButton.addClickHandler(new ClickHandler() {
    public void onClick(ClickEvent event) {
        DSRequest dsRequestProperties = new DSRequest();
        dsRequestProperties.setExportAs(ExportFormat.XLS);
        dsRequestProperties.setExportDisplay(ExportDisplay.DOWNLOAD);
        dsRequestProperties.setParams(new HashMap() {{ // new code here!!!
            put("nRowFacets", advancedCube.getRowFacets().length);
            put("nColFacets", advancedCube.getColumnFacets().length);
        }});
        advancedCube.exportClientData(dsRequestProperties);
    }
});

We've presented an inline script in the OperationBinding above for simplicity, but you can alternatively define a ServerObject in the binding and use DMI to call a method on a Java class if you like.

[pci-abarbaro]

Phenomenal, I will try this out in our product. This looks promising.
Thank you for your time, I will respond here if there are any other questions.

[Isomorphic]

Let us know if we should add the getExportTextColor() API to SmartGWT to parallel getExportBGColor(), as that solution may end up being easier for you to maintain.