openxlsx (version 2.0.1)

writeData: Write an object to a worksheet

Description

Write an object to worksheet with optional styling.

Usage

writeData(wb, sheet, x, startCol = 1, startRow = 1, xy = NULL,
  colNames = TRUE, rowNames = FALSE, headerStyle = NULL,
  borders = c("none", "surrounding", "rows", "columns"),
  borderColour = getOption("openxlsx.borderColour", "black"),
  borderStyle = getOption("openxlsx.borderStyle", "thin"), ...)

Arguments

wb

A Workbook object containing a worksheet.

sheet

The worksheet to write to. Can be the worksheet index or name.

x

Object to be written. For classes supported look at the examples.

startCol

A vector specifiying the starting columns(s) to write to.

startRow

A vector specifiying the starting row(s) to write to.

xy

An alternative to specifying startCol and startRow individually. A vector of the form c(startCol, startRow).

colNames

If TRUE, column names of x are written.

rowNames

If TRUE, data.frame row names of x are written.

headerStyle

Custom style to apply to column names.

borders

Either "none" (default), "surrounding", "columns", "rows" or respective abbreviations. If "surrounding", a border is drawn around the data. If "rows", a surroundin

borderColour

Colour of cell border. A valid colour (belonging to colours() or a hex colour code, eg see http://www.colorpicker.com{here}).

borderStyle

Border line style

• none

{ no border} thin{ thin border} medium{ medium border} dashed{ dashed border} dotted{ dotted border}

item

...

See Also

writeData

Examples

wb <- createWorkbook()
options("openxlsx.borderStyle" = "thin")
options("openxlsx.borderColour" = "#4F81BD")
## Add worksheets
addWorksheet(wb, "Cars")


x <- mtcars[1:6,]
writeData(wb, "Cars", x, startCol = 2, startRow = 3, rowNames = TRUE)
writeData(wb, "Cars", x, rowNames = TRUE, startCol = "O", startRow = 3,
         borders="surrounding", borderColour = "black") ## black border

writeData(wb, "Cars", x, rowNames = TRUE,
         startCol = 2, startRow = 12, borders="columns")

writeData(wb, "Cars", x, rowNames = TRUE,
         startCol="O", startRow = 12, borders="rows")

## header styles
hs1 <- createStyle(fgFill = "#DCE6F1", halign = "CENTER", textDecoration = "Italic",
                   border = "Bottom")

writeData(wb, "Cars", x, colNames = TRUE, rowNames = TRUE, startCol="B",
     startRow = 23, borders="rows", headerStyle = hs1, borderStyle = "dashed")

hs2 <- createStyle(fontColour = "#ffffff", fgFill = "#4F80BD",
                   halign = "center", valign = "center", textDecoration = "Bold",
                   border = "TopBottomLeftRight")

writeData(wb, "Cars", x, colNames = TRUE, rowNames = TRUE,
          startCol="O", startRow = 23, borders="columns", headerStyle = hs2)

## Save workbook
saveWorkbook(wb, "writeDataExample.xlsx", overwrite = TRUE)

## inspired by xtable gallery
##' http://cran.r-project.org/web/packages/xtable/vignettes/xtableGallery.pdf

## Create a new workbook
wb <- createWorkbook()
data(tli, package = "xtable")

## TEST 1 - data.frame
test.n <- "data.frame"
my.df <- tli[1:10, ]
addWorksheet(wb = wb, sheetName = test.n)
writeData(wb = wb, sheet = test.n, x = my.df, borders = "n")

## TEST 2 - matrix
test.n <- "matrix"
design.matrix <- model.matrix(~ sex * grade, data = my.df)
addWorksheet(wb = wb, sheetName = test.n)
writeData(wb = wb, sheet = test.n, x = design.matrix)

## TEST 3 - aov
test.n <- "aov"
fm1 <- aov(tlimth ~ sex + ethnicty + grade + disadvg, data = tli)
addWorksheet(wb = wb, sheetName = test.n)
writeData(wb = wb, sheet = test.n, x = fm1)

## TEST 4 - lm
test.n <- "lm"
fm2 <- lm(tlimth ~ sex*ethnicty, data = tli)
addWorksheet(wb = wb, sheetName = test.n)
writeData(wb = wb, sheet = test.n, x = fm2)

## TEST 5 - anova 1
test.n <- "anova"
my.anova <- anova(fm2)
addWorksheet(wb = wb, sheetName = test.n)
writeData(wb = wb, sheet = test.n, x = my.anova)

## TEST 6 - anova 2
test.n <- "anova2"
fm2b <- lm(tlimth ~ ethnicty, data = tli)
my.anova2 <- anova(fm2b, fm2)
addWorksheet(wb = wb, sheetName = test.n)
writeData(wb = wb, sheet = test.n, x = my.anova2)

## TEST 7 - GLM
test.n <- "glm"
fm3 <- glm(disadvg ~ ethnicty*grade, data = tli, family = binomial())
addWorksheet(wb = wb, sheetName = test.n)
writeData(wb = wb, sheet = test.n, x = fm3)

## TEST 8 - prcomp
test.n <- "prcomp"
pr1 <- prcomp(USArrests)
addWorksheet(wb = wb, sheetName = test.n)
writeData(wb = wb, sheet = test.n, x = pr1)

## TEST 9 - summary.prcomp
test.n <- "summary.prcomp"
addWorksheet(wb = wb, sheetName = test.n)
writeData(wb = wb, sheet = test.n, x = summary(pr1))

## TEST 10 - simple table
test.n <- "table"
data(airquality)
airquality$OzoneG80 <- factor(airquality$Ozone > 80,
                              levels = c(FALSE, TRUE),
                              labels = c("Oz <= 80", "Oz > 80"))
airquality$Month <- factor(airquality$Month,
                           levels = 5:9,
                           labels = month.abb[5:9])
my.table <- with(airquality, table(OzoneG80,Month) )
addWorksheet(wb = wb, sheetName = test.n)
writeData(wb = wb, sheet = test.n, x = my.table)

## Save workbook
saveWorkbook(wb, "classTests.xlsx",  overwrite = TRUE)