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)
| file | path to the Excel (xls/xlsx) file or Workbook object. |
|---|
Luca Braglia
#> #> 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)