`cormatrix_to_excel()`: p-value stars display incorrectly in Excel

[rempsyc]

It was reported to me that cormatrix_to_excel() has recently gained a display issue in Excel for the number of stars, which does not match the p values anymore, probably due to changes in openxlsx2. We ought to investigate whether this is something we should change on our side (e.g., change code to respect new API; check the changelog for reference) or a bug in openxlsx2, which should then be reported. Reprex:

packageVersion("correlation")
#> [1] '0.8.8'
packageVersion("openxlsx2")
#> [1] '1.21'

correlation::cormatrix_to_excel(mtcars, "test")
#> # Correlation Matrix (pearson-method)
#> 
#> Parameter |      mpg |      cyl |     disp |       hp |     drat |       wt
#> ---------------------------------------------------------------------------
#> mpg       |          | -0.85*** | -0.85*** | -0.78*** |  0.68*** | -0.87***
#> cyl       | -0.85*** |          |  0.90*** |  0.83*** | -0.70*** |  0.78***
#> disp      | -0.85*** |  0.90*** |          |  0.79*** | -0.71*** |  0.89***
#> hp        | -0.78*** |  0.83*** |  0.79*** |          |    -0.45 |   0.66**
#> drat      |  0.68*** | -0.70*** | -0.71*** |    -0.45 |          | -0.71***
#> wt        | -0.87*** |  0.78*** |  0.89*** |   0.66** | -0.71*** |         
#> qsec      |     0.42 |   -0.59* |    -0.43 | -0.71*** |     0.09 |    -0.17
#> vs        |   0.66** | -0.81*** | -0.71*** | -0.72*** |     0.44 |   -0.55*
#> am        |   0.60** |   -0.52* |   -0.59* |    -0.24 |  0.71*** | -0.69***
#> gear      |     0.48 |    -0.49 |   -0.56* |    -0.13 |  0.70*** |   -0.58*
#> carb      |   -0.55* |    0.53* |     0.39 |  0.75*** |    -0.09 |     0.43
#> 
#> Parameter |     qsec |       vs |       am |    gear |    carb
#> --------------------------------------------------------------
#> mpg       |     0.42 |   0.66** |   0.60** |    0.48 |  -0.55*
#> cyl       |   -0.59* | -0.81*** |   -0.52* |   -0.49 |   0.53*
#> disp      |    -0.43 | -0.71*** |   -0.59* |  -0.56* |    0.39
#> hp        | -0.71*** | -0.72*** |    -0.24 |   -0.13 | 0.75***
#> drat      |     0.09 |     0.44 |  0.71*** | 0.70*** |   -0.09
#> wt        |    -0.17 |   -0.55* | -0.69*** |  -0.58* |    0.43
#> qsec      |          |  0.74*** |    -0.23 |   -0.21 | -0.66**
#> vs        |  0.74*** |          |     0.17 |    0.21 |  -0.57*
#> am        |    -0.23 |     0.17 |          | 0.79*** |    0.06
#> gear      |    -0.21 |     0.21 |  0.79*** |         |    0.27
#> carb      |  -0.66** |   -0.57* |     0.06 |    0.27 |        
#> 
#> p-value adjustment method: Holm (1979)
#> 
#> 
#>  [Correlation matrix 'test.xlsx' has been saved to working directory (or where specified).]
#> Warning: will not open file when not interactive
#> NULL

^{Created on 2025-11-17 with reprex v2.1.1}

[JanMarvin]

Hi @rempsyc , just came across your issue. Tbh I do not really know what has changed, probably something in openxlsx2 most likely JanMarvin/openxlsx2#1397 and JanMarvin/openxlsx2#1420.

Apparently the conditional formatting is executed in a waterfall. Currently the first one wins and the latter is never executed. Shifting the ordering seems to solve the issue.

[JanMarvin]

Updated this code here and it seems to fix the issue.

[rempsyc]

Oh thanks so much @JanMarvin ! :D

[rempsyc]

Mmm tried on openxlsx2 devel version on two different computers and Excel versions but still getting this:

packageVersion("correlation")
#> [1] '0.8.8'
packageVersion("openxlsx2")
#> [1] '1.21.0.9000'

correlation::cormatrix_to_excel(mtcars, "test")

^{Created on 2025-11-18 with reprex v2.1.1}

[JanMarvin]

Yes, you have to fix it in correlation. But you should be able to adapt from the code I linked yesterday. The issue here is caused by the waterfall in which the conditional formats are applied. Previously openxlsx2 added conditional formatting in reverse. This caused issues in other places, thus now conditional formatting is added in the order in which it is applied. When you add the lowest conditional formatting first, this wins. Add the three star rule first, and this will win.

It is unfortunate that I did not think about this when the change was added. But usually there are not that many conditional formats on a single cell and there was apparently no test for this.

You can see the impact, when changing the order of the conditional formatting in spreadsheet software.

[rempsyc]

openxlsx2 v1.15 works:

But fails in openxlsx2 v1.16

Sorry I haven't testing this function in a while hehe

[rempsyc]

Fixed in #362! Waiting for review approval to merge