conditional cell formatting row + column based

I am using AG/IG v 12.3.

My basic use case is a comparison of 2 time-based scenarios with a dynamic horizon (production projections for 2 years starting different time periods). This produces 3 rows (projections_q1’22, projections_q2’22, difference) for each mapping (product at a production facility) within either scenario (they are matched, even when data isn’t present in one of the scenarios). The current columns-making process is as follows:

  buildColumns(param:string, shorter:boolean, compareTimePeriods:string[], source:string) {
    let formatColumnData = (value:any) => {
      if (value !== null && value !== undefined) {
        if (param.indexOf('PB') > -1 || param.indexOf('EB') > -1) { return value.toFixed(5); }
        else if (param.indexOf('(%)') > -1) { return (value.toFixed(9) * 100).toLocaleString() + '%'; }
        else { return value; }
      } else { return null; }
    };
    let columns:column[] = [{ field: 'Design', type: GridColumnDataType.String, pinned: true, width: '110' }, { field: 'Family', type: GridColumnDataType.String, pinned: true, width: '80' }, { field: 'Facility', type: GridColumnDataType.String, pinned: true, width: '80' }, { field: 'Process', type: GridColumnDataType.String, pinned: true, width: '75' }];
    if (source !== 'UOM') { columns.unshift({ field: 'Scenario', type: GridColumnDataType.String, pinned: true, width: '250' }); }
    if (param.indexOf('%') < 0 && param.indexOf('(days)') < 0 && param.indexOf('AVG') < 0) { columns.push({ field: 'Total', type: GridColumnDataType.Number, pinned: true, width: shorter ? '90' : '125', formatter: formatColumnData }); } compareTimePeriods.forEach(w => columns.push({ field: w, type: GridColumnDataType.Number, pinned: false, width: shorter ? '80' : '115', formatter: formatColumnData }));
    return columns;
  };

Users will now have the option to stitch in data from a third scenario, the actual production values The actual data can be stitched into either or both projection scenarios, and the projection data can start at any workweek within the total horizon (chosen start week within Actual data horizon through last workweek chosen, up to the last workweek of the later projection scenario).

Users would like to see the actual data highlighted in a subdued yellow/orange (background) color. I imagine the logic to decide whether it should be highlighted should look something like this:

scenIndex = rowIndex % 3;
if (columnName < starts[scenIndex] && scenIndex < 2) { cell.highlight(); }

The implementation at the row level seems a bit beyond the available example.

Hello Chris, 

Thank you for posting in our community!

This is an initial update to let you know that I am currently in the process of creating a sample for you, illustrating how such behavior could be achieved. I will keep you posted on my progress.

Sincerely,
Riva Ivanova
Entry Level Software Developer

Hello Chris,

Thank you for your patience while I was looking into this matter for you.

I have been looking into your question and what I could say is that applying conditional cell styling based both on information of the row and column is possible using either of the two IgxGrid’s properties – cellClasses or cellStyles.

Both input properties accept an object literal, containing key-value pairs, where the key is the name of the CSS class/style properties, while the value is either a callback function that returns a boolean, or boolean value. The callback signature for both properties looks like the following:

(rowData: any, columnKey: string, cellValue: any, rowIndex: number) => boolean

This provides different information regarding the cell (i.e., the data for the respective row, the column field, the value of the cell, and the row index) which allows defining custom rules based on one or many parameters, depending on the scenario.

Having this in mind, I have prepared a small sample, demonstrating how such behavior could be achieved. In the attached sample I have included five IgxGrids, each of which demonstrates how the different parameters could be used for custom styling, where the last example includes styling based on the row index and the column filed.

Additionally, you have mentioned that the “actual data can be stitched into either or both projection scenarios” and that the “users would like to see the actual data highlighted”. What I could say is that if you are referring to the data being added dynamically and this change should be reflected (i.e., the added data should be highlighted), then using either cellClasses or cellStyles would be quite helpful for achieving this as the styling is applied immediately if the cell meets the condition of the styling.

Furthermore, since I was not sure how and when these changes are applied on your side, I have used a button in the first IgxGrid (i.e., “ADD UNITS IN STOCK”) and when clicking it, new values are added in the “Units In Stock” column for the first and fourth records of the grid, which initially are not present, and respectively the custom styling is applied only for the first cell, as it meets the condition.

However, if you are referring that the data (i.e., “data from a third scenario, the actual production values”) could be present anywhere in the grid and should be highlighted, then I believe that you would find the third example “Grid #3 – cellValue based style” quite helpful, as in this example I am applying styling to all cells whose values are present in a predefined data set (i.e., _cellValues in the .ts file).

Here could be found my sample for your reference. Please test it on your side and let me know if you need any further assistance.

If this is not an accurate demonstration of what you are trying to achieve, please feel free to modify it and send it back to me along with steps to reproduce it.

Looking forward to hearing from you. 

Sincerely,
Riva Ivanova
Entry Level Software Developer

I apologize for the requirements being a bit obfuscated, in terms of what the data will look like. I do greatly appreciate the thoroughness with which you have answered, though, hence the +1 for the post. I am currently investigating the implementation for Grid5 since it seems closest to my use case, which I will attempt to describe a little better below.

In all cases, the only columns that should be colored are the first however many in the horizon, not the scenario/mapping/row total information.

My basic use case is an investigation of a single projection scenario with the actual scenario data prepended into the beginning of a time horizon. The user is able to pick the time period that the data from the projection will start.

For example, consider a projection that is for 2022. The basic use case would generally show Actual data up to the current time frame (week 13 of the year, for example), and for the rest of 2022, it would show the projections. The Actual data (up to week 13) is what would be colored differently to show it is actual production/sales/supply/etc. numbers for each mapping (product linked to the facility it was produced at, is stored, or was shipped from). Each line of the chart would have coloring for the actual data and be the default coloring for projection data, including the totals/avg summary rows at the bottom.

There are more complex situations involving more projections, either some or all will have the Actual data prepended, which will also require the coloring, but the one I want to focus on is a comparison between 2 projections with 1 or both projections prepended by Actual data where projection data will start in different time periods. These 2 scenario comparisons will have a differential row following each product-facility mapping and the totals matrix at the bottom will compare against different facilities and against production vs engineering products.

For the first example, consider the fact that projections are released weekly with changes based on the production from the previous week. A user is likely to compare the current week's projection (13) against a mass projection (all products for next 3-5 years) made at the beginning of the year. The weekly scenario will be prepended by the Actual data (because it will start the week after the last released Actual data period). We will want the Actual data colored for comparison against the mass projection, but not any of the differential or totals matrix data.

The differential rows should have highlighting of red, if negative, or green, if positive, to quickly show users whether we are beating or dragging on an expectation.

As stated, it seems like your examples have covered these needs. I will work on it and let you know if I find a deficiency.

Again, thank you so much for the quick and thorough response.

I have updated based on what you showed in the sample, but for some reason I'm not getting any coloring at all in the grid. Maybe you can see what I'm doing wrong?

Grid declaration:

CSS (not SCSS):

.actualData { background-color: #ffe07a; }

.negativeDiff { color: darkred; }

.positiveDiff { color: darkgreen; }

And the logic for deciding on the colors:

  public stitchClasses = {
    actualData: (
      rowData: any,
      columnKey: string,
      cellValue: any,
      rowIndex: number
    ) => {
      let scenIndex: number = this.scenarioNames.indexOf(rowData.Scenario),
        stitched: boolean =
          scenIndex === 0 ? this.scenario1Stitch : this.scenario2Stitch,
        end: string =
          scenIndex === 0 ? this.scen1StartWw.name : this.scen2StartWw.name;
      console.log(
        `in horizon? ${
          columnKey >= this.actualStartWw.name && columnKey <= end
        }`
      );
      return (
        stitched && columnKey >= this.actualStartWw.name && columnKey <= end
      );
    },
    negativeDiff: (
      rowData: any,
      columnKey: string,
      cellValue: any,
      rowIndex: number
    ) => {
      return rowData.Scenario === 'Diff' && cellValue < 0;
    },
    positiveDiff: (
      rowData: any,
      columnKey: string,
      cellValue: any,
      rowIndex: number
    ) => {
      return rowData.Scenario === 'Diff' && cellValue > 0;
    },
  };

Let me know what you think I'm missing or doing wrong here.

Hello Chris,

Thank you for following up!

I am glad that you find my suggestion helpful.

After reviewing your second question what I could say is that the two possible scenarios in which custom styling is not being applied is either none of the cells meets the specific condition or the component is using an Emulated ViewEncapsulation. Additionally, in order to force the custom styles down through the current component and its children you should either penetrate this encapsulation using ::ng-deep or set it to None.

Through ViewEncapsulation in .ts file:

@Component({
    selector: 'app-my-component',
    ...
    encapsulation: ViewEncapsulation.None,
})

 

Through ::ng-deep in .scss/.css file:

::ng-deep {
    .custom-background {
        background-color: #68e684;
    }
}

 

Please test these approaches on your side and let me know if you need any further assistance.

Looking forward to your reply.

Sincerely,
Riva Ivanova
Entry Level Software Developer

The ViewEncapsulation.none solution worked for me. With CSS, you have to designate an element to ::ng-deep on, otherwise it breaks with "bad" code.

Thanks for your help!

Hello Chris,

I am glad that you find my suggestion helpful and managed to achieve your requirement.

Thank you for using Infragistics components. 

Regards,
Riva Ivanova
Entry Level Software Developer

Unfortunately, I feel I must bother you again. The download (export to Excel) feature seems to be broken with the coloring applied. Here is the complete output:

ERROR TypeError: Cannot read properties of undefined (reading 'columnList')
    at IgxExcelExporterService.export (main.js:126115:24)
    at StitchComponent.download (main.js:1293921:37)
    at StitchComponent_div_18_button_2_Template_button_click_0_listener (main.js:1293589:23)
    at executeListenerWithErrorHandling (main.js:35182:12)
    at wrapListenerIn_markDirtyAndPreventDefault (main.js:35233:16)
    at HTMLButtonElement. (main.js:67185:34)
    at ZoneDelegate.invokeTask (polyfills.js:11323:173)
    at Object.onInvokeTask (main.js:50995:25)
    at ZoneDelegate.invokeTask (polyfills.js:11323:56)
    at Zone.runTask (polyfills.js:11073:39)
defaultErrorLogger @ main.js:24740
handleError @ main.js:24793
handleError @ main.js:30164
executeListenerWithErrorHandling @ main.js:35184
wrapListenerIn_markDirtyAndPreventDefault @ main.js:35233
(anonymous) @ main.js:67185
ZoneDelegate.invokeTask @ polyfills.js:11323
onInvokeTask @ main.js:50995
ZoneDelegate.invokeTask @ polyfills.js:11323
Zone.runTask @ polyfills.js:11073
ZoneTask.invokeTask @ polyfills.js:11418
invokeTask @ polyfills.js:12863
globalZoneAwareCallback @ polyfills.js:12894

It basically breaks at this point in my code (specifically on the export line), which is no different from other pages which do not produce this error:

  download() {
    let param:parameter = parameters.filter(p => p.name === this.uomParam)[0], name:string = `${param ? param.short : this.uomParam}`;
    name += this.scenario1Name === this.scenario2Name ? '' : `-${this.scenario2Name.slice(0, this.scenario2Name.length < 20 ? this.scenario2Name.length : 20)}`;
    name += `-${this.scenario1Name.slice(0, this.scenario1Name.length < 20 ? this.scenario1Name.length : 20)}`
    name += `-${this.rollup}-${new Date().getTime().toString().slice(8,13)}`;
    console.log(`Exporting stitch ${name} to Excel.`);
    this.excelExportService.export(this.stitchGrid, new IgxExcelExporterOptions(name));
  };

Users would like to see the coloring applied to the Excel sheet which is produced, if possible. Otherwise, it seems there should be a way to reverse the coloring to make it exportable.

Hello Chris,

I have been looking into your question and what I could say is that at this point we do not provide exporting the grid with custom cell/row styling. What I can suggest in order to have this feature implemented in any of our future releases is logging this as a feature request in our GitHub repository here. Remember when submitting your idea to explain the context in which a feature would be used and why it is needed as well as anything that would prevent you from accomplishing this today. You can even add screenshots to build a stronger case.

Additionally, I have prepared a small sample trying to reproduce the described behavior. However, on my side, I was not able to reproduce the described behavior and the Excel file is downloaded successfully. After reviewing the provided snippet from the console log, what I could say is that the most common reason for receiving such an error is if the selector provided in the ViewChild is not equal to the one assigned to the IgxGrid in the html file.

For example:

<igx-grid #grid [data]="data" [autoGenerate]="true"></igx-grid>

@ViewChild('grid1', { read: IgxGridComponent, static: true }) public grid!: IgxGridComponent;

Here could be found my sample for your reference. Please test it on your side and let me know if you need any further assistance.

Looking forward to hearing from you. 

Sincerely,
Riva Ivanova
Entry Level Software Developer

Ugh, incompleteness on my part and simple mistakes. I forgot to change the name in the HTML template, as the grid was good enough to simply copy over from a previous component.

However, I do not think adding to the GitHub repo will do anything, as the last suggestion I put there (dotted lines in charts) was closed without attempt at implementation, despite being highest user-voted new feature.

Hi Riva Ivanova,

Currently I am using grid with igx-column options, I want to hide or remove cells based on the conditions. Is there any possible way to achieve this requirements?

Thanks in Advance.

Regards,

R.Madhavan