Openxlsx2 does not write tables correctly - it fist converts them to dataframes when writing to Excel

Question

I create a two way table in R using the following code

t <- table(df[["Sex"]], df[["C or Better"]], useNA = "ifany")
colnames(t)[is.na(colnames(t))] <- "Blank"
CountsBySex  <- addmargins(t, c(1,2))

This works just fine, and I can print the table without a problem

> t
   
    C-, D or F Other Pass Blank
  F       1967   737 3169   106
  M       1555   480 2452    71

But when I write the table to Excel as follows, the contents of the Excel file look weird:

  wb <- openxlsx2::wb_add_data(wb, sheet = cs, CountsBySex, 
                               start_col = 1,  start_row = writeRow)

   Var1       Var2  Freq
1     F C-, D or F  1967
2     M C-, D or F  1555
3   Sum C-, D or F  3522
4     F      Other   737
5     M      Other   480
6   Sum      Other  1217
7     F       Pass  3169
8     M       Pass  2452
9   Sum       Pass  5621
10    F      Blank   106
11    M      Blank    71
12  Sum      Blank   177
13    F        Sum  5979
14    M        Sum  4558
15  Sum        Sum 10537

It appears that openxlsx2 first converts my table to a data frame using as.data.frame(CountsBySex) and then writes it to Excel. What change do I need to make to my syntax to write my table to Excel as a table, and not as a data.frame? Interestingly, this problem does not arise when I use openxlsx - it writes the table to Excel correctly.

Many thanks in advance

Thomas Philips

Answer 1

Figured it out: I needed as.data.matrix:

  wb <- openxlsx2::wb_add_data(wb, sheet = cs,  
                               as.data.frame.matrix(CountsBySex), 
                               start_col = 1,    start_row = writeRow,
                               col_names = TRUE, row_names = TRUE)

Answer 2

this is the author of openxlsx2. Thank you for your interest in the project, although the claim that we are not writing a particular data class correctly sounds a bit harsh. There are countless ways to create data structures in R. Vectors, data frames and data tables, matrices, tables, xtables, ftables, time series, flexables, gtables, pivottabler tables, tibbles, vctrs and these are just a few that come to mind, not to mention model classes like lm, glm etc. Most likely new data classes are being created as we speak. It's just not possible to stay ahead of the myriad ways to write data.

We provide options for writing vectors, data frames and matrices. Though you are of course correct, openxlsx provides some functions to convert certain selected class object into data frames. However, I believe that people should have the freedom to decide how they want to write their data. That's why we don't try to interfere much with their input. We mainly try to do one thing well. To provide an interface for writing xlsx files. But R is about being creative, and I encourage anyone interested to write their own coerce function(s) for openxlsx2. Package it up, make it available via r-universe or CRAN, and that way everyone can choose their favorite option.

PS: And here, just as with your previous question, it may well be that we haven't covered something precisely enough in our man pages or in the book. If you stumble across such a case, please let us know. We, at least I, have been dealing with this project for so long, and many quirks in openxlsx2 have been invented by me. So maybe I didn't document it because it felt natural to me. Nice code and nice documentation are two different things, and hopefully our code is always a bit better than our documentation.