gtsummary and/or flextable support

[pteridin]

Problem

It would be lovely to have the option to export gtsummary / flextable objects into excel retaining the style and (all) header and footer lines.

In my workplace lots of gtsummary-objects are created. Usually I convert the gtsummary-object to a tibble and export it using openxlsx/openxlsx2. This could be a minimal viable option if it would retain all the strata headers - though it doesn't retain it. A wrapper to correctly export the table into excel - from gtsummary and/or flextable - would be quite nice.

Example

library(gtsummary)
library(openxlsx2)

mtcars |> 
  tbl_strata(strata = am,
             .tbl_fun = \(x) {
               tbl_summary(x, by=vs) |> 
                 add_difference(everything() ~ "smd")
             }) |>
  bold_labels() |> 
  italicize_levels() |>
  tibble::as_tibble() -> x

head(x)

Characteristic	0, N = 12	1, N = 7	Difference	95% CI	0, N = 6	1, N = 7	Difference	95% CI
mpg	15.2 (14.1, 16.6)	21.4 (18.7, 22.2)	-2.3	-3.5, -1.1	20.4 (16.8, 21.0)	30.4 (25.1, 31.4)	-2.1	-3.5, -0.77
cyl	NA	NA	4.1	2.5, 5.7	NA	NA	2.4	0.97, 3.8
4	0 (0%)	3 (43%)	NA	NA	1 (17%)	7 (100%)	NA	NA
6	0 (0%)	4 (57%)	NA	NA	3 (50%)	0 (0%)	NA	NA
8	12 (100%)	0 (0%)	NA	NA	2 (33%)	0 (0%)	NA	NA
disp	355 (297, 410)	168 (144, 196)	3.1	1.8, 4.5	160 (149, 266)	79 (77, 102)	1.9	0.55, 3.2

[JanMarvin]

Hi @pteridin , I agree, but something like this is more of a task for gtsummary or flextable developers or a different package entirely. I have create a basic wrapper for flextable here: #445 and the David, the flextable developer is aware of it. If you have only basic tables without value and standard deviations in a single cell or a plot included or something else, it already works okayish (I wont say well, because it is slow and there still might be other issues, but it is better than nothing and handles styles, captions and a few merged cells).

The linked example should still work, but it is slow, because we have to parse the entire flextable object and visit every cell of the table to collect all the cell styles. Currently the wrapper is only written as two loops of base R on top of openxlsx2.
Also some cases are not handled, because things get tricky once fancy table features like included standard deviations are included as shown in the table above: val (std). In openxml format this is allowed only in a string and otherwise has to be separated into val and std. Similar things happen in flextable with graphics. For some there are replacements in openxml, others are a bit tricky.

Finally, since I use neither flextable nor gtsummary this is not something I have a lot of expertise in and aside from the obvious "it would be nice to have something like this in openxlsx2 " I also have no real interest to invest my time into this (there are still many other things left on my ToDo-List). If you want to tackle this, please go ahead and of course I would be willing to support you as far as possible.

[pteridin]

Hi @JanMarvin,

Thanks for your feedback and for your code, I'll give it a try. Performance is only a minor issue here.

Yes, I also think you can hang it on flextable and/or gtsummary. Maybe I'll hang on those two packages to mention there again, that would be a huge help for one or the other workflow.

I would have thought one could possibly hijack the XML output for PowerPoint in officer to insert a table. Unfortunately, I don't know enough under the hood of XLSX to implement this myself on this level.

[JanMarvin]

Had a quick look, the example in the linked discussion still works: #445 (comment)
There were some things I haven't figured out as you will see once you run the segment. Some additional parts might now be possible, but ... I spend one or two rainy evenings on this. Looking at the flextable object and working my way along. There obviously must be better ways to do this 😄 Maybe it would be feasible to cast the object into a long data frame but I leave this to somebody else. Feel free to take and modify my snipped as you are pleased.

The flextable issue is here: davidgohel/flextable#430 (But from a developer point of view, "+1" comments are not boosting anyone's motivation to work on anything 😉 . David will get there when he has time and/or need.)

The issue regarding gtsummary will most like be the cells with standard deviations attached. So this fictional table

tab
2.2 (1.0)

How do you want to write this to your output? In our data world single cells can only contain either a string or a single numeric. So either it is "2.2 (1.0)" or 2.2 or 2.2 and 1.0 in two separate cells. If an export to csv or xlsx is planned, in my opinion the table creating function should decide how to proceed. Otherwise the function that wants to create the output table has to create additional columns out of thin air. But most likely the table creating functions are not written with csv or xlsx output in mind and therefore adding this additional step on their side is overhead nobody is happy to develop.

I'm not getting your last comment, you should be able to add flextable output to officer powerpoint slides, but this will create a drawing iirc not a table.

[pteridin]

I also ran the experiment. wb_add_flextable does work quite well, though there are some problems with the current implementation that I will try to fix, when I have the time to do so.

Example

Let's start with the following more interesting example:

library(tidyverse)
library(gtsummary)
library(openxlsx2)

as_tibble(Titanic) |> 
    uncount(n) |> 
    tbl_strata(strata = Class,
               .tbl_fun = \(x) {
                 x |> 
                   tbl_summary(by=Sex) |> 
                   add_difference(everything() ~ "smd")
               }) |> 
    bold_labels() |> 
    italicize_levels() |> 
    as_flex_table() -> ft_base

wb_workbook() |> 
    wb_add_worksheet(xlsx, "Sheet 1") |> 
    wb_add_flextable(sheet = "Sheet 1",
                     ft = ft_base) |> 
    wb_save("wb_solution.xlsx")

What works well:

• Labels, even bold and italic
• Adding N (N%) values "as is", as text - that is perfect, I don't need any splitting of columns and fancy merging
• Borders are preserved well

What needs to be fixed:

• The first header (strata) is okay, though the bold formatting is not preserved. Instead it shows only stars ** that imply that it should be bold
• The second header (by) missed any formats and texts that are in the original table
• The footer does show only the first number. Certainly because of the more interesting formatting with superscript numbers followed by text.

Copying from PPTX

Actually copying from PPTX documents with officer preserves many informations as well. Though it is first of all a pain in the butt to do this for hundreds of tables (yeah, that's part of my job :) ) and it re-formats and re-interprets numbers and various inputs as excel does: plain wrong.

Open Thoughts

The question that is still open from my part: Should I push a merge for a future soonish maybe working wb_add_flextable to this repo or should I further inquire with David to get his thoughts on how he does want such a function to be implemented and try to integrate it into flextable?

[JanMarvin]

What needs to be fixed

Regarding the needs to be fixed part: like I said, I spend one or two days on this and some features of openxlsx2 were not yet fully developed, therefore going further was never really an option.

The table you created is one without simple header and captions. You need something like this for captions: (now that fmt_txt() is available)

df <- ft_base$footer$content$content$data
for (i in seq_len(nrow(df))) {
  
  col <- df[[i]]
  
  string <- fmt_txt("")
  for (j in seq_len(nrow(col))) {
    
    if (is.na(col[j, ]$vertical.align))  valign <- ""
    else valign <- col[j, ]$vertical.align
    
    string <- string + fmt_txt(col[j, ]$txt, vert_align = valign)
  }
  
  print(string)
  
}

Similar for captions. Some captions work, but this is a "non trivial" header. You have to look into this

df <- ft_base$header$content$content$data

The benefits of working with flextable is, that David built it with or at least around officer. Hence all the required openxml fields are already present. Therefore we got many nice things quite cheap :)

One issue that currently is not yet resolved are complex merged areas. openxlsx2 doesn't split merged areas, if you want to overwrite parts of an existing merged area. I haven't had time to look into this, but flextable used this somewhere or at least I had issues with it.

Copying from pptx

Yeah, I see why you are not pleased with the copy job :)

Open thought

I'd say you could ask David what his thoughts are, though earlier this year he mentioned that he is quite busy, not sure if this has changed yet. I don't think that it is something for this package, it might be something for some additional new package.

[JanMarvin]

Here I have improved the footer: https://gist.github.com/JanMarvin/1107238d9a07acba8fb1b3f88af618ad
But, that's it for me right now.

[JanMarvin]

I have moved this to a discussion, because as stated, I do not see this as an issue with openxlsx2.

[JanMarvin]

Ah it's fun, here is the update with headers. And I made minor improvements to the code snippet. There is wb_add_ignore_error() if you want to get rid of the green error flags.

But now I'm leaving this to you :)

# source wb_add_flextable() function
source("https://gist.githubusercontent.com/JanMarvin/1107238d9a07acba8fb1b3f88af618ad/raw/fba7170f9b0d566432995e25e0a282b5d53b5325/flextable_fancy.R")

library(tidyr)
library(gtsummary)
library(openxlsx2)

ft_base <- as_tibble(Titanic) |> 
  uncount(n) |> 
  tbl_strata(strata = Class,
             .tbl_fun = \(x) {
               x |> 
                 tbl_summary(by=Sex) |> 
                 add_difference(everything() ~ "smd")
             }) |> 
  bold_labels() |> 
  italicize_levels() |> 
  as_flex_table()


wb_workbook() |> 
  wb_add_worksheet() |> 
  wb_add_flextable(ft = ft_base) |> 
  wb_open()

[pteridin]

Was already working on a solution myself 👍

Current progress on my part is the following solution:

# add styled content
  add_content <- function(section = c("header",
                                      "body",
                                      "footer"),
                          dims_section) {
    section = match.arg(section)
    
    df <- ft[[section]]$content$content$data
    df_dims <- dim(df)
    
    content <- vapply(seq_len(df_dims[2]),
                      # go through each header column
                      \(x) {
                        
                        header_line <- df[,x]
                        
                        # iter through each row
                        vapply(seq_len(df_dims[1]),
                               \(i) {
                                 
                                 col <- header_line[[i]]
                                 
                                 # apply styling to every element
                                 vapply(seq_len(nrow(col)),
                                        \(y) {
                                          if(is.na(col[y,]$txt))
                                            return(NA_character_)
                                          
                                          valign <- if(is.na(col[y,]$vertical.align)) { NULL } else col[y,]$vertical.align
                                          
                                          #todo: add more styling options 
                                          fmt_txt(col[y,]$txt, 
                                                  vert_align = valign,
                                                  bold = ifelse(is.na(col[y,]$bold), 
                                                                FALSE,
                                                                col[y,]$bold))
                                        },
                                        FUN.VALUE = character(1)) |> 
                                   paste0(collapse="")
                                 },
                               FUN.VALUE = character(1))
                      },
                      FUN.VALUE = character(df_dims[1]))
    
    wb$add_data(sheet = sheet, dims = dims_section, x = content, col_names = FALSE)
    wb_style_ft(wb, ft[[section]], dims = dims_section)
    return(NULL)
  }
  
  # todo: dims as named list
  add_content("header", dims_hdr)
  add_content("body", dims_dat)
  add_content("footer", dims_ftr)

It currently handles only bold and superscript text, though more complex styles can be easily implemented - like your solution provides.

The profiler also hints that the overhead from this solution is negligible. Though, wb_style_ft & especially the sub-function add_border add significantly to the runtime and are targets to optimize for. I think it should be possible to optimize these functions.

Next steps would involve adding more styling options for this function and then optimize the runtime a little more. I guess there is no caching for borders and styling and it is added to the XLSX for each cell as we go? Maybe it could be possible to recycle styles and apply it to a whole range of columns at once?

[JanMarvin]

As I said, I approached the whole problem head on. Took the flextable documentation, then created examples. Took the object and tried to convert it to openxlsx2 output. So I loop internally (if I remember correctly even multiple times) over the entire cell range. I always knew that this is very slow, fortunately you can implement such loops in c++ quickly and easily, besides I was only writing on a proof of concept. Therefore ... very likely you can do everything much better :) If you can hash the tables information I don't know, but of course it's faster to format a whole cell range in openxlsx2 than each single cell.

I opened a pull request in #733 that should fix the problem with overlapping cell ranges in cell merges.

[pteridin]

Hello there!

Progress is made, output for gtsummary-flextables looks nice and is somewhat more performant to the previous implementation. Though, I guess there is still room for improvement and test tables needed to see if the output matches the expected output. I already tested the following table that came out messed up. Probably - again - an issue with the merging and applying the respective styling.

flextable::as_flextable(table(mtcars[,1:2]))

To make testing the changes easier I integrated it into openxlsx2 (Link) and I would plan to merge it into openxls2 if more testing is done. Most of the code is still your implementation @JanMarvin and I think it would fit this package better than flextable. It would also be a push to make openxlsx2 more relevant to all those data analysts out there that need to poop out tables as much as I do.

Edit: Repo not longer available. See next post.

[JanMarvin]

Well we can develop this in openxlsx2, but I still think it would be better to have it in an independent package. For instance you use \() which came in R 4.2 iirc. I don't want to break our current dependency in R 3. something for openxlsx2, but other packages obviously don't have to follow this mindset and can include more dplyr or rlang stuff if needed. But feel free to open a pull request, for development.

[JanMarvin]

You also use |> and -> (I personally find this one bad regarding readability), which are not part of the openxlsx2 code style.

[pteridin]

Long story short: Here it is Package Link - a beautiful composition of new pipes and much more dplyr-dependencies then I would like. But at least with a more correct output. :)

[JanMarvin]

Yay! Gave it a try, but the caption part seems to have broken something. Also the remotes part in the README lists me and there are two Rstudio project files included? Have fun with the package :)

[pteridin]

Fixed.

[JanMarvin]

Thanks :)