Search code examples
rdocxgt

How can I export a gt table to Word while retaining formatting in RStudio?

I've generated a formatted table in RStudio using the gt package, and I'm ready to export it. My issue is that when I run the gtsave() function and specify that my output file is a .docx file, I successfully get an output, but the output retains none of the formatting I coded for:

"Successful" output:

I tried adding the webshot2 and rmarkdown packages to get the export to preserve formatting, and it started throwing an error

Code:

library(tidyverse)
library(ggplot2)
library(gt)
library(webshot2)
library(rmarkdown)

sampletabledata<-read_csv('sampledata.csv')

gt_sampletabledata<-gt(sampletabledata)

gt_sampletabledata|>tab_header(title="Count of Households with Pets")%>%
  opt_align_table_header(align='left')%>%
  tab_spanner(label="Fiscal Year", columns=c(`15/16`,`16/17`,`17/18`,`18/19`,`19/20`,`20/21`,`21/22`,`22/23`,`23/24`))%>%
  tab_style(style=list(cell_text(color='#F76900',weight = 'bold')),locations=cells_column_labels())%>%
  tab_style(style=list(cell_text(color='#000E54',weight = 'bold')),locations=cells_body(col=1))%>%
  tab_style(style=list(cell_text(color='#F76900',weight = 'bold')),locations=cells_column_spanners())%>%
  opt_table_font(google_font(name='Aptos'))

gtsave(gt_sampletabledata, 'gt_sampledata.docx')

Error:

Unknown output format doc
Pandoc can convert to DOCX, but not to DOC.
Error: pandoc document conversion failed with error 22

This is the desired output (screenshot from RStudio Viewer):

This is the dataset used (for troubleshooting purposes):

structure(list(`Number of Pets in Household` = c("0", "1", "2", 
"3", "4", "5", "6", "7", "(Missing)", "Total"), `15/16` = c(1, 
446, 294, 42, 6, 5, 2, 0, 146, 942), `16/17` = c(0, 641, 574, 
82, 12, 3, 1, 0, 610, 1923), `17/18` = c(1, 799, 747, 112, 30, 
5, 1, 0, 1662, 3357), `18/19` = c(0, 805, 807, 67, 15, 6, 1, 
1, 2108, 3810), `19/20` = c(0, 1260, 787, 74, 19, 4, 3, 0, 1748, 
3895), `20/21` = c(1, 1510, 854, 102, 22, 8, 3, 0, 1387, 3887
), `21/22` = c(1, 1746, 971, 121, 19, 5, 1, 0, 1699, 4563), `22/23` = c(26, 
1531, 1064, 162, 42, 8, 4, 1, 4469, 7307), `23/24` = c(6, 445, 
300, 48, 7, 2, 0, 1, 810, 1619)), row.names = c(NA, -10L), spec = structure(list(
    cols = list(`Number of Pets in Household` = structure(list(), class = c("collector_character", 
    "collector")), `15/16` = structure(list(), class = c("collector_double", 
    "collector")), `16/17` = structure(list(), class = c("collector_double", 
    "collector")), `17/18` = structure(list(), class = c("collector_double", 
    "collector")), `18/19` = structure(list(), class = c("collector_double", 
    "collector")), `19/20` = structure(list(), class = c("collector_double", 
    "collector")), `20/21` = structure(list(), class = c("collector_double", 
    "collector")), `21/22` = structure(list(), class = c("collector_double", 
    "collector")), `22/23` = structure(list(), class = c("collector_double", 
    "collector")), `23/24` = structure(list(), class = c("collector_double", 
    "collector"))), default = structure(list(), class = c("collector_guess", 
    "collector")), delim = ","), class = "col_spec"), problems = <pointer: 0x000001317ba37ad0>, class = c("spec_tbl_df", 
"tbl_df", "tbl", "data.frame"))
Solution

  • With thanks to DavoOZ for the solve, what I was missing was saving the formats back to the original variable at the end of the dplyr piped code. Adding a right assignment pipe <- is the key. Here's the updated code:

    library(tidyverse)
library(gt)

sampletabledata<-read_csv('sampledata.csv')

gt_sampletabledata<-gt(sampletabledata) 

gt_sampletabledata <- gt_sampletabledata%>%
  tab_header(title="Count of Households with Pets")%>%
  opt_align_table_header(align='left')%>%
  tab_spanner(label="Fiscal Year", columns=c(`15/16`,`16/17`,`17/18`,`18/19`,`19/20`,`20/21`,`21/22`,`22/23`,`23/24`))%>%
  tab_style(style=list(cell_text(color='#F76900',weight = 'bold')),locations=cells_column_labels())%>%
  tab_style(style=list(cell_text(color='#000E54',weight = 'bold')),locations=cells_body(col=1))%>%
  tab_style(style=list(cell_text(color='#F76900',weight = 'bold')),locations=cells_column_spanners())%>%
  tab_options(table_body.vlines.color=NULL, table.border.left.color=NULL,table.border.right.color = NULL)%>%
  opt_table_font(google_font(name='Aptos'))-> gt_sampletabledata


gtsave(gt_sampletabledata,'gt_sampledata.docx')

    This is the output (not perfect, but closer to what I wanted):Updated Output