Open a Microsoft Excel file (xls/xlsx) or an openxlsx Workbook

This function tries to open a Microsoft Excel (xls/xlsx) file or an openxlsx Workbook with the proper application, in a portable manner.

In Windows (c) and Mac (c), it uses system default handlers, given the file type.

In Linux it searches (via which) for available xls/xlsx reader applications (unless options('openxlsx.excelApp') is set to the app bin path), and if it finds anything, sets options('openxlsx.excelApp') to the program choosen by the user via a menu (if many are present, otherwise it will set the only available). Currently searched for apps are Libreoffice/Openoffice (soffice bin), Gnumeric (gnumeric) and Calligra Sheets (calligrasheets).

openXL(file=NULL)

Arguments

file

path to the Excel (xls/xlsx) file or Workbook object.

Author

Luca Braglia

Examples

# file example
example(writeData)
#> 
#> writDt> ## See formatting vignette for further examples.
#> writDt> 
#> writDt> ## Options for default styling (These are the defaults)
#> writDt> options("openxlsx.borderColour" = "black")
#> 
#> writDt> options("openxlsx.borderStyle" = "thin")
#> 
#> writDt> options("openxlsx.dateFormat" = "mm/dd/yyyy")
#> 
#> writDt> options("openxlsx.datetimeFormat" = "yyyy-mm-dd hh:mm:ss")
#> 
#> writDt> options("openxlsx.numFmt" = NULL)
#> 
#> writDt> ## Change the default border colour to #4F81BD
#> writDt> options("openxlsx.borderColour" = "#4F81BD")
#> 
#> writDt> #####################################################################################
#> writDt> ## Create Workbook object and add worksheets
#> writDt> wb <- createWorkbook()
#> 
#> writDt> ## Add worksheets
#> writDt> addWorksheet(wb, "Cars")
#> 
#> writDt> addWorksheet(wb, "Formula")
#> 
#> writDt> x <- mtcars[1:6, ]
#> 
#> writDt> writeData(wb, "Cars", x, startCol = 2, startRow = 3, rowNames = TRUE)
#> 
#> writDt> #####################################################################################
#> writDt> ## Bordering
#> writDt> 
#> writDt> writeData(wb, "Cars", x,
#> writDt+   rowNames = TRUE, startCol = "O", startRow = 3,
#> writDt+   borders = "surrounding", borderColour = "black"
#> writDt+ ) ## black border
#> 
#> writDt> writeData(wb, "Cars", x,
#> writDt+   rowNames = TRUE,
#> writDt+   startCol = 2, startRow = 12, borders = "columns"
#> writDt+ )
#> 
#> writDt> writeData(wb, "Cars", x,
#> writDt+   rowNames = TRUE,
#> writDt+   startCol = "O", startRow = 12, borders = "rows"
#> writDt+ )
#> 
#> writDt> #####################################################################################
#> writDt> ## Header Styles
#> writDt> 
#> writDt> hs1 <- createStyle(
#> writDt+   fgFill = "#DCE6F1", halign = "CENTER", textDecoration = "italic",
#> writDt+   border = "Bottom"
#> writDt+ )
#> 
#> writDt> writeData(wb, "Cars", x,
#> writDt+   colNames = TRUE, rowNames = TRUE, startCol = "B",
#> writDt+   startRow = 23, borders = "rows", headerStyle = hs1, borderStyle = "dashed"
#> writDt+ )
#> 
#> writDt> hs2 <- createStyle(
#> writDt+   fontColour = "#ffffff", fgFill = "#4F80BD",
#> writDt+   halign = "center", valign = "center", textDecoration = "bold",
#> writDt+   border = "TopBottomLeftRight"
#> writDt+ )
#> 
#> writDt> writeData(wb, "Cars", x,
#> writDt+   colNames = TRUE, rowNames = TRUE,
#> writDt+   startCol = "O", startRow = 23, borders = "columns", headerStyle = hs2
#> writDt+ )
#> 
#> writDt> #####################################################################################
#> writDt> ## Hyperlinks
#> writDt> ## - vectors/columns with class 'hyperlink' are written as hyperlinks'
#> writDt> 
#> writDt> v <- rep("https://CRAN.R-project.org/", 4)
#> 
#> writDt> names(v) <- paste0("Hyperlink", 1:4) # Optional: names will be used as display text
#> 
#> writDt> class(v) <- "hyperlink"
#> 
#> writDt> writeData(wb, "Cars", x = v, xy = c("B", 32))
#> 
#> writDt> #####################################################################################
#> writDt> ## Formulas
#> writDt> ## - vectors/columns with class 'formula' are written as formulas'
#> writDt> 
#> writDt> df <- data.frame(
#> writDt+   x = 1:3, y = 1:3,
#> writDt+   z = paste0(paste0("A", 1:3 + 1L), paste0("B", 1:3 + 1L), sep = " + "),
#> writDt+   stringsAsFactors = FALSE
#> writDt+ )
#> 
#> writDt> class(df$z) <- c(class(df$z), "formula")
#> 
#> writDt> writeData(wb, sheet = "Formula", x = df)
#> 
#> writDt> #####################################################################################
#> writDt> ## Save workbook
#> writDt> ## Open in excel without saving file: openXL(wb)
#> writDt> ## Not run: 
#> writDt> ##D saveWorkbook(wb, "writeDataExample.xlsx", overwrite = TRUE)
#> writDt> ## End(Not run)
#> writDt> 
#> writDt> 
#> writDt> 
# openXL("writeDataExample.xlsx")

# (not yet saved) Workbook example
wb <- createWorkbook()
x <- mtcars[1:6, ]
addWorksheet(wb, "Cars")
writeData(wb, "Cars", x, startCol = 2, startRow = 3, rowNames = TRUE)
# openXL(wb)