0

I want to format tables in xlsx format and save to xlsx file, and convert third column in percentage value.

    mtcars
df <- mtcars[1:5,1:5]

df <- janitor::adorn_totals(df)

wb <- loadWorkbook("blank_template.xlsx")
addWorksheet(wb, "tables")
writeDataTable(wb, "tables", df, tableStyle = "TablestyleMedium6")

I want to format a table like below and save it to an xlsx file.

enter image description here

Peter
  • 11,500
  • 5
  • 21
  • 31
str_rst
  • 173
  • 4
  • 1
    Have a look at the `openxlsx` package. – stefan Jan 31 '22 at 08:18
  • What have you tried? See DT, formattable, gt, etc packages. – zx8754 Jan 31 '22 at 08:18
  • This can be achieved, but please give us a reproducible example. Are you asking two questions? How to manage your data to get these results and how to download as Excel to get this table formatting? – ViviG Jan 31 '22 at 09:51
  • apologies for wrong question, i have just updated the question. actually i am able to create workbook, worksheet etc but i am unable to for colour, percentage, in table to save as table in excel workbook – str_rst Jan 31 '22 at 10:17
  • Do you have a workbook with prepared formatting? openxlsx help for `loadWorkbook` is that it "returns a workbook object conserving styles and formatting of the original .xlsx file." Or do you want to create the formatting shown for a new workbook? – Peter Jan 31 '22 at 12:42

2 Answers2

2

This is one way: openxlsx does need a fair bit of work but the great thing is you can generally achieve what you want.

library(openxlsx)
library(tibble)
library(dplyr)


df1 <- 
  mtcars[1:5,1:5] %>% 
  # to convert row names into a variable
  rownames_to_column(var = "model") %>% 
  # to enable a percentage format to be applied
  mutate(disp = disp/100)

df1 <- janitor::adorn_totals(df1)


wb <- createWorkbook()
addWorksheet(wb, "tables")

setColWidths(wb, 1, cols = 1, widths = 25)

writeData(wb, 1, df1)

header_style <- createStyle(fgFill = "#4F81BD", halign = "center", textDecoration = "bold", fontColour = "white")
addStyle(wb, 1, style = header_style, rows = 1, cols = 1:ncol(df1))

name_style <- createStyle(fgFill = "gray70", halign = "center", fontColour = "white")
addStyle(wb, 1, style = name_style, rows = 2 : nrow(df1), cols = 1)

percent_style <- createStyle(halign = "center", numFmt = "00%")
addStyle(wb, 1, style = percent_style, rows = 2 : nrow(df1), which(colnames(df1) == "disp"))

center_style <- createStyle(halign = "center")
addStyle(wb, 1, style = center_style, rows = 2 : nrow(df1), cols = which(!colnames(df1) %in% c("model", "disp")), gridExpand = TRUE)

total_style <- createStyle(fgFill = "yellow", halign = "center", fontColour = "black", fontSize = 14)
addStyle(wb, 1, style = total_style, rows = nrow(df1) + 1, cols = 1:ncol(df1))

saveWorkbook(wb, "openxlsx_mtcars_eg.xlsx", overwrite = TRUE)

Created on 2022-01-31 by the reprex package (v2.0.1) enter image description here

Peter
  • 11,500
  • 5
  • 21
  • 31
  • My R studio is hanging on this line every time when i run and taking a lot of time. writeData(wb, 1, df1) – str_rst Feb 01 '22 at 09:41
  • i am just running this above sample data of mtcars and its hanging , did't test my own data – str_rst Feb 01 '22 at 09:49
  • Not sure what to suggest: try restarting your computer, try on another computer, ensure that you have copied the code correctly, make sure you have up to date packages installed, do you have an up to date version of R and RStudio?. The code in the answer runs fine on my computer. – Peter Feb 01 '22 at 09:53
0

Take a look at a similar thread here

1. Create a new row with sum for each column

library(janitor)
df = adorn_totals(df, "row")

2. Export to xlsx with the openxlsx or xlsx package

Bart
  • 128
  • 8