xlsx_table: Generate nice formated tables in excel.

In neale-eldash/pd: Day-2-day functions in R

Description

This function exports a dataframe to a formated table in excel. Format parameters are still hard-coded in function. Cells of the table title will be merged if needed.

Usage

xlsx_table(
  df,
  wb,
  sheet,
  tit.sep = NA,
  lin.inic = 1,
  col.inic = 1,
  width.first = 30,
  width.cols = 20,
  title_font = "blue1",
  title_bg = "lightcyan",
  col1_font = "black",
  col1_bg = "lightgrey"
)

Arguments

df	The dataframe to be exported.
wb	A workbook object (created by createWorkbook), identifying where the table will be created.
sheet	A intenger identifying in which sheet index to create the table.
tit.sep	[Optional] A regular expression identifying how variable names and values are separated in the dataframe title. For example, in "variable_category" the tit.sep parameter would be "_".
lin.inic	[Optional] A integer identifying in which line to insert the table.
col.inic	[Optional] A integer identifying in which column to insert the table.
width.first	[Optional] A integer identifying the width of the first column in the table.
width.cols	[Optional] A integer identifying the width of the other columns in the table.
title_font	[Optional] A character identifying the color of the title font.
title_bg	[Optional] A character identifying the color of the title background.
col1_font	[Optional] A character identifying the color of the first column's font.
col1_bg	[Optional] A character identifying the color of the first column's background.

Value

NULL: the table is written to the provided workbook, no object is returned.

Examples

### carregando dados
data(svy)

### formatando dados
df = svy
df$pop <- runif(nrow(df))
df <- df %>% gather(var,categ,ends_with('cota'))
df <- df %>% group_by(regiao,var,categ) %>% summarise(pop=sum(pop))
df <- df %>% unite(var,var,categ,sep = "#")
df1 <- df
df <- df %>% spread(var,pop)

### gerando tabelas
file <- "C:\\tabela_xlsx.xlsx"
wb <- createWorkbook()
addWorksheet(wb, "Exemplos")
# Tabela com 1 linha no título
xlsx_table(df1,wb=wb,sheet=1,lin.inic = 1,col.inic = 1)
# Tabela com 2 linhas no título
xlsx_table(df,wb=wb,sheet=1,tit.sep = "#",lin.inic = 1,col.inic = 5)
saveWorkbook(wb, file, overwrite = TRUE)

neale-eldash/pd documentation built on June 26, 2021, 10:47 a.m.