Show Hidden Row and Col

Normally, rows and columns set to be hidden are not visible in the spreadsheet interface. However, when designing complex report templates, it can be challenging to edit content within hidden rows or columns if they are completely invisible.

The "Show Hidden Rows and Columns" feature in ReportSheet allows you to toggle the visibility of rows and columns marked as hidden specifically within the design view of the ReportSheet template. This feature simplifies template creation and modification by providing easy access to all parts of the template, regardless of their final hidden state.

Key aspects of the feature:

• Design Time Visibility: When enabled, rows and columns previously set as hidden will be displayed in the design view.

• Preview Behavior: This feature does not affect the final report preview or output. Hidden rows and columns will always be truly hidden during preview and when the report is generated.

• Visual Indication: Displayed hidden rows and columns are rendered with a distinct style to differentiate them from regularly visible cells.

• Editable: While displayed, the content and properties of these rows and columns can be edited like any other visible cell.

• Customizable Style: The distinct styling (e.g., background color, borders, font effects) applied to displayed hidden rows and columns is fully customizable.

Using Code

You can control and customize the display of hidden rows and columns in the ReportSheet design template using the options property of the reportSheet object.

Enabling and Disabling the Feature

Follow the sample code to configure a report template hiding specific rows/columns.

const reportSheet = spread.getActiveSheetTab();
const template = reportSheet.getTemplate();

// Set value for the template.
template.setTemplateCell(2, 0, { 
    binding: "Orders[orderId]",
    type: "Group",
    filter: {
        condition: {
            and: [
                {
                    "parameter": "startDate",
                    "column": "orderDate",
                    "operator": "GreaterThan"
                },
                {
                    "parameter": "endDate",                
                    "column": "orderDate",
                    "operator": "LessThan"
                },
                {
                    "parameter": "customerId",             
                    "column": "customerId",
                    "operator": "Equal"
                }
            ]
        }
    }

});

// Set binding for the template.
const columns = ['customerId', 'orderDate', 'freight', 'shipName', 'shipCity', 'shipCountry'];
columns.forEach((item, index) => {
    template.setTemplateCell(2, index + 1, {
        type: "Group",
        binding: `Orders[${item}]`
    })
});

// Set the report parameters.
reportSheet.parameter({
    startDate: new Date("1997-01-01"),
    endDate: new Date("1998-01-01"),
    customerId: "VINET"
});

// Hide row4 and column4
template.setRowVisible(4, false);
template.setColumnVisible(4, false);

By default, the showHiddenRowCol parameter is set to false, which means rows or columns that have been explicitly hidden will not appear in the current view.

To examine these previously hidden elements, now switch the parameter to true.

reportSheet.options.showHiddenRowCol = true;
reportSheet.refresh(); //Refresh the sheet.

type=note

Note:

Setting the showHiddenRowCol property will automatically refresh the ReportSheet display.

This action will temporarily make the hidden rows and columns visible, enabling you to inspect or work with them as needed, while still keeping their "hidden" status intact in the underlying configuration.

For example, in the above example, using the API to get the hidden information for the fifth row is as follows:

template.getRowVisible(4);
// true

Customizing the Appearance of Displayed Hidden Items

In the header cell, the ReportSheet is rendered using the default hiddenRowColStyle, or you can set a customized style.

// The default value of hiddenRowColStyle.
hiddenRowColStyle = {
  backColor: {
    type: GC.Spread.Sheets.PatternType.lightDown
  }
}

Now let's adjust this style.

// Create new style.
var style = new GC.Spread.Sheets.Style();
style.backColor = "#B5B5B5";
style.foreColor = "#FFFFFF";
style.fontSize = "24px";
// Apply the style.
reportSheet.options.hiddenRowColStyle = style;
//Refresh the sheet.
reportSheet.refresh();

Using Designer

Enabling and Disabling the Feature

When working in design mode, you can reveal hidden rows or columns by checking the box labeled 'Show Hidden Row/Column' in the 'Report Sheet Design' ribbon.

This feature does not affect the final report preview or output. Hidden rows and columns will always be truly hidden during preview/ paginate and when the report is generated.

Customizing the Appearance of Displayed Hidden Items

You can adjust the style by checking the box labeled 'Hidden Row/Column Style' in the 'Report Sheet Design' ribbon.

Once clicked, you can customize settings directly within the pop-up dialog.