Stack Overflow
Stack Overflow

Questions tagged [openxlsx]

Read, Write and Edit XLSX Files

Simplifies the creation of Excel .xlsx files by providing a high level interface to writing, styling and editing worksheets. Through the use of 'Rcpp', read/write times are comparable to the 'xlsx' and 'XLConnect' packages with the added benefit of removing the dependency on Java.

Author: Alexander Walker

241 questions
20
votes
2 answers

How do I modify an existing a sheet in an Excel Workbook using Openxlsx package in R?

I am using "openxlsx" package to read and write excel files. I have a fixed file with a sheet called "Data" which is used by formulas in other sheets. I want to update this Data sheet without touching the other. I am trying the following…
Nipun Arora
  • 383
  • 1
  • 3
  • 8
15
votes
1 answer

openxlsx Error: length of rows and cols must be equal

I just ran into the same problem that this Nabble user ran into when trying to apply a style to an Excel workbook using a non-rectangular set of rows and columns. wb <- createWorkbook() addWorksheet(wb, "Iris") writeData(wb, sheet = 1, x =…
D. Woods
  • 2,604
  • 3
  • 23
  • 36
13
votes
4 answers

R - Autofit Excel column width

How do I autofit the column width using openxlsx? One of my columns has a date variable (eg. 21-08-2017) and if copied using ctrl+c from Excel, and pasted normally elsewhere, it shows like #######(if column width is increased to show the content in…
Arani
  • 621
  • 6
  • 23
12
votes
2 answers

Formatting percentages in R-package openxlsx

R3.2.3/openxlsx 3.0.0 I have several dataframes that I'm writing to an Excel-workbook with openxlsx. One of the dataframes contains values such as 0.07. With createStyle(numFmt='PERCENTAGE') I get 7.00% as output in the workbook. So far so good.…
Jack Random
  • 121
  • 1
  • 4
10
votes
3 answers

Use R and Openxlsx to output a list of dataframes as worksheets in a single Excel file

I have a set of CSV files. I want to package them up and export the data to a single Excel file that contains multiple worksheets. I read in the CSV files as a set of data frames. My problem is how to construct the command in openxlsx, I can do it…
Technophobe01
  • 7,300
  • 2
  • 27
  • 54
9
votes
0 answers

Getting " 80020009 Error in .COM(x, name, ...) :" in R while accessing excel file

I have automated the process of accessing VBA Macros, from R, using Openxlsx and RDCOMClient packages. Below is the code: Function: OpenExcel_5param <- function(directory,filename, MacroName, param1, param2, param3,param4, param5) { # Open a…
Rajat Bhadauria
  • 91
  • 1
  • 2
9
votes
2 answers

Possible to write Excel formulas or data validation using R?

I am trying to write a R data frame to Excel and want to add additional cells/columns that have Excel formulas and/or data validation values (e.g. using the Data/Validation menu in Excel to provide drop-down lists of allowable values for a…
Mac471
  • 393
  • 3
  • 14
7
votes
1 answer

How to apply thick border around a cell range using the `openxlsx` package in R

I loaded an Excel workbook in R and want to do some formatting of the borders in a rectangular range of cells. I would like to put a thin border between all cells put a thick border around the outside of the range of the cells. At the moment, I…
Rainer
  • 7,319
  • 1
  • 17
  • 23
7
votes
2 answers

Encoding issue with write.xlsx (openxlsx)

I use the write.xlsx() function (from the openxlsx package) to turn a list object into an excel spreadsheet, where each element of the list is converted into a "sheet" of the excel file. In the past, this function has been incredibly useful, and I…
im2wddrf
  • 369
  • 4
  • 13
7
votes
3 answers

Automatically - "Convert numbers stored as text to numbers"

Lets consider this small example: df1<- data.frame(A=c(1,NA,"pvalue",0.0003),B=c(0.5,7,"I destroy","numbers all day"),stringsAsFactors = T) Write file: openxlsx::write.xlsx(df1,"Test.xlsx") In my resulting excel file, 1 and 7 are text cells. Excel…
Andre Elrico
  • 8,959
  • 1
  • 37
  • 61
7
votes
4 answers

Read multiple xlsx files with multiple sheets into one R data frame

I have been reading up on how to read and combine multiple xlsx files into one R data frame and have come across some very good suggestions like, How to read multiple xlsx file in R using loop with specific rows and columns, but non fits my data set…
Elisah
  • 115
  • 1
  • 1
  • 5
7
votes
2 answers

Cannot insert plot into XLSX via openxlsx package when using command line

I'm trying to insert a plot into an XLSX file using the openxlsx package in R. When I use the R GUI, I am able to accomplish this. However, when using a batch file, the plot is created but it is not inserted into the XLSX file. Instead, it is…
Jimmy G
  • 351
  • 2
  • 6
7
votes
1 answer

Multiple styles() applied to the same row with package openxlsx

I started using the R package openxlsx recently because it does not require the rJava package or Java at all. I'm trying to write dates to a column but row formatting isn't being applied to the final date column in my xlsx file. Example data: tmp…
Steven
  • 3,014
  • 16
  • 41
6
votes
0 answers

hide all rows after row X

Is it possible to hide all rows in a worksheet after a specified row? library("openxlsx") # Create a workbook. wb <- createWorkbook() # Add a worksheet. addWorksheet(wb, sheetName = "test", gridLines = FALSE) # "Hide" all rows after row…
Khaynes
  • 1,781
  • 2
  • 12
  • 26
6
votes
1 answer

openxlsx writing if formula from R to excel

I am trying to export from R to excel an dataframe with two columns which I want to fill with the excel if formula so that the user could later on change the thresholds. My question is how can I export a formula like the following IF(C2>4;…
S.K.
  • 315
  • 2
  • 16
1
2 3
…
16 17