Custom column names in openxlsx

user_10679177 0
Viewed 886

I'm going to use openxlsx to generate a report.

I have a dataframe with column names that aren't friendly to an end user. So I would like to generate an excel file with custom columns. I've figured out how to ignore column names using colNames = FALSE, but what is the best way to add names I need?

report <- createWorkbook('report.xlsx')

addWorksheet(report, 'test')

# how to add column names

writeData(report, sheet = 1, df, colNames = FALSE)

saveWorkbook(report, 'report.xlsx', overwrite = TRUE)
openxlsx r
1 Answers

It's probably better to rename the column names, rather than to ignore them. For example:

library(tidyverse)
library(openxlsx)

wb = createWorkbook()

sht = addWorksheet(wb, "data")

mtcars %>%
  rename(Cylinders = cyl, 
         `Miles per Gallon` = mpg) %>%
  writeData(wb, sht, .)

saveWorkbook(wb, "myworkbook.xlsx")

If you're going to generate regular reports like this, you can create a vector of new-name/old-name pairs for renaming. For example:

# Renaming vector
rename.vec = c("Cylinders"="cyl",
               "Miles per Gallon"="mpg",
               "Displacement"="disp")

# Example of renaming using the vector we just created
mtcars %>% 
  rename(!!!rename.vec) %>% 
  head

                  Miles per Gallon Cylinders Displacement  hp drat    wt  qsec vs am
Mazda RX4                     21.0         6          160 110 3.90 2.620 16.46  0  1
Mazda RX4 Wag                 21.0         6          160 110 3.90 2.875 17.02  0  1
Datsun 710                    22.8         4          108  93 3.85 2.320 18.61  1  1
Hornet 4 Drive                21.4         6          258 110 3.08 3.215 19.44  1  0
Hornet Sportabout             18.7         8          360 175 3.15 3.440 17.02  0  0
Valiant                       18.1         6          225 105 2.76 3.460 20.22  1  0
                  gear carb
Mazda RX4            4    4
Mazda RX4 Wag        4    4
Datsun 710           4    1
Hornet 4 Drive       3    1
Hornet Sportabout    3    2
Valiant              3    1

# Write data to a worksheet, renaming columns on the fly
writeData(wb, sht, mtcars %>% rename(!!!rename.vec))
user_4964880
Related