Stack Overflow
Stack Overflow

Questions tagged [openxlsx]

Use this tag for questions related to the openxlsx package. The openxlsx package provides functions to make it easy to read, write and edit XLSX Files in the R programming language.

The package simplifies the creation of Excel files in the programming language by providing a high level interface to writing, styling and editing worksheets. Through the use of , read/write times are comparable to the xlsx () and packages with the added benefit of removing the dependency on Java.

For more information on the package see:

Original author: Alex Walker
Current maintainer: Philipp Schauberger

410 questions
31
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
  • 553
  • 1
  • 5
  • 9
19
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
  • 191
  • 1
  • 4
18
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
  • 3,004
  • 3
  • 29
  • 37
16
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
  • 753
  • 1
  • 9
  • 23
11
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
  • 8,212
  • 3
  • 32
  • 59
11
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
  • 423
  • 5
  • 16
10
votes
2 answers

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
  • 8,347
  • 1
  • 23
  • 28
10
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
  • 551
  • 2
  • 5
  • 19
10
votes
2 answers

openxlsx format cells as number with comma and decimal places

How can one create an excel file using openxlsx where the number formatting includes both comma thousand separators and 2 decimal places? I have tried the code below, but no luck on the formatting. # Create Customer Dataset cust <-…
Jason
  • 305
  • 1
  • 2
  • 11
10
votes
1 answer

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
  • 101
  • 1
  • 3
9
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
  • 10,956
  • 6
  • 50
  • 69
9
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
  • 391
  • 2
  • 8
8
votes
5 answers

How to detect TIME when reading from an excel sheet using R

The issue is that when I read from an excel sheet into R using read.xlsx from openxlsx package, the TIME column is converted into a fraction. Here is an example, dfin <- DATE TIME 15/02/2015 8:00 AM 22/01/2014 10:00…
Amer
  • 2,131
  • 3
  • 23
  • 38
8
votes
1 answer

Read and write a xlsm file using exceljs package npm

I have a xlsm file with few data already in it and need to write some data and create a new xlsm file during automation. With below code the file gets created , but it becomes corrupt and unable to open. File size reduces, for ex from 8kb to 7kb.…
Nalu
  • 195
  • 2
  • 13
7
votes
2 answers

Can I write identical xlsx files from the same data frame in R?

Can I make sure that two XLSX files (written with openxlsx::write.xlsx) are identical, when given the same data to write? I think there's a timestamp written to the spreadsheet which means the same data written more than one second apart creates a…
Spacedman
  • 92,590
  • 12
  • 140
  • 224
1
2 3
…
27 28