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.
416
questions
31
votes
2
answers
38k
views
How do I modify an existing sheet in an Excel Workbook using Openxlsx package?
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 ...
- 553
19
votes
2
answers
9k
views
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 ...
- 191
18
votes
1
answer
5k
views
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()
...
- 3,004
16
votes
4
answers
19k
views
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 ...
- 753
11
votes
2
answers
5k
views
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 ...
- 423
11
votes
3
answers
7k
views
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 ...
- 8,232
10
votes
2
answers
16k
views
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.
...
- 305
10
votes
2
answers
11k
views
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, ...
- 551
10
votes
2
answers
4k
views
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 ...
- 8,371
10
votes
1
answer
4k
views
Getting "<checkErrorInfo> 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, ...
- 101
9
votes
2
answers
5k
views
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 ...
- 391
9
votes
3
answers
7k
views
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 ...
- 11k
8
votes
5
answers
8k
views
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/...
- 2,131
8
votes
1
answer
5k
views
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 ...
- 195
7
votes
4
answers
20k
views
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 ...
- 115
7
votes
1
answer
3k
views
How to download workbook via downloadHandler on Shiny?
How can we save a workbook in a certain folder and make it available for user to download it?
ref:
Shiny + downloadHandler + Openxlsx does not generate a xlsx file
Procedure:
Create a data
Save as ...
- 417
7
votes
1
answer
4k
views
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 ...
- 3,238
7
votes
2
answers
148
views
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 ...
- 92.7k
7
votes
0
answers
941
views
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", ...
- 1,976
7
votes
1
answer
1k
views
Add a filter to an openxlsx object with a query activated
I'm dumping into a file a report, and I'm using openxlsx to do so, as it can be seen below:
library(openxlsx)
set.seed(100)
dataset <- data.frame(A=runif(100),B=runif(100),C=sample(c("A","B","C")...
- 2,259
6
votes
4
answers
25k
views
openxlsx::write.xlsx overwriting existing worksheet instead append
The openxlsx::write.xlsx function is overwriting spreadsheet instead of adding another tab.
I tried do follow some orientations of Stackoverflow, but without sucess.
dt.escrita <- format(Sys.time(...
- 420
6
votes
6
answers
10k
views
"Error in addWorksheet(wb, "sheet1") : First argument must be a Workbook"
I am using ‘openxlsx’ package in R. ٰI want to add some data in xlsx file. I have used following code to create the workbook and add worksheet in it.
wb=createWorkbook()
addWorksheet(wb,"sheet 1")
...
- 123
6
votes
3
answers
2k
views
Filter data highlighted in Excel by cell fill color using openxlsx
I have a big Excel table (18k rows and 400 cols) which has some rows highlighted using different colors. Is there a way to filter the rows by colors using openxlsx?
I first loaded the workbook
wb <-...
- 1,723
6
votes
2
answers
4k
views
openxlsx currency style - Is there a rounding option?
The createStyle function in the openxlsx package has an argument numFmt, which allows you to create an excel format to be applied to specific cells in a .xlsx file. You can round values by specifying ...
- 28.2k
6
votes
2
answers
2k
views
Openxlsx hyperlink output display in Excel
I am trying to take in a data frame an excel sheet that has two columns,
Column A contains names of stores
Column B contains the URL of those stores.
I would like to take Column A and make it a ...
- 103
6
votes
1
answer
4k
views
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 ...
- 365
6
votes
1
answer
4k
views
How to write 1-million-row Excel Files with openxlsx Package in R
I have users who cannot or do not want to connect with relational databases, but instead prefer to work with data exported to excel files.
The recordsets exported from these database data can become ...
- 9,138
6
votes
1
answer
4k
views
Convert Number Stored as Text in Excel File Using R
I would like to output data from R to an excel file, and have been using the openxlsx package.
writeDataTable is the function I have been using. The problem is that I want the entries of one of the ...
6
votes
1
answer
3k
views
Reading a password-protected xlsx-file into R without installing Java (password is known)
currently I am preparing an R-lesson for a customer. We want to read in data from a password protected xlsx file. Thus, I am trying to read this password protected xlsx file into R (unprotecting the ...
- 412
6
votes
0
answers
657
views
How can I bold part of a text string using openxlsx?
I want to bold part of a text string. For example, is there a way to only bold 'text' in 'this is my text string' using openxlsx R package:
wb <- openxlsx::createWorkbook()
openxlsx::addWorksheet(...
- 95
5
votes
2
answers
2k
views
openxlsx - fill row row based on text in another column
I am trying to fill a row in an excel workbook with openxlsx based on text in a column from that row. For example, if cell C3 contains A then highlight row 3.
Ideally, this would not be ...
- 718
5
votes
3
answers
3k
views
Newline in openxlsx
A question on openxlsx library in R:
I'm looking for a way in openxlsx to add a newline to a cell value so that the value is in two (or more lines) in Excel. I mean similar functionality that you get ...
- 51
5
votes
2
answers
1k
views
Write file xlsx in R without using Java
wight now I'having an issue with R. I could not use library openxlsx in my PC. When I try to import, these errors show me that:
Error: package or namespace load failed for ‘openxlsx’:
object ‘zipr’ ...
- 463
5
votes
2
answers
625
views
Is there a way to accelerate formatted table writing from R to excel?
I have a 174603 rows and 178 column dataframe, which I'm importing to Excel using openxlsx::saveWorkbook, (Using this package to obtain the aforementioned format of cells, with colors, header styles ...
- 522
5
votes
1
answer
1k
views
openxlsx::writeFormula rowwise - is there an efficient way to do this?
openxlsx functions to write formula seem to add formula columnwise only.
Is there an efficient way to write formula rowwise?
Working through a reproducible example to illustrate the issue:
df <- ...
- 11.7k
5
votes
0
answers
1k
views
R "openxlsx" package not maintaining formatting
R Version: 4.0.1 (2020-06-06), openxlsx Version: 4.2.4
I'm having formatting issues with the openxlsx package. When I load a workbook (loadWorkbook) and then save it again (saveWorkbook), formatting ...
- 149
5
votes
0
answers
1k
views
Formatting numbers with openxlsx package in R
I am trying to format certain columns using openxlsx to write an r data frame to an excel file.
Here is a snippet of the R data frame:
The square bracket part in the "seed" column is used ...
- 147
5
votes
1
answer
1k
views
Tidyverse/faster solution to conditional formatting with openxlsx in R?
I'm working with genetic data that look like this table, but larger:
ID allele.a allele.b
A 115 90
A 115 90
A 116 90
B 120 82
B 120 82
B ...
- 5,463
4
votes
3
answers
5k
views
Import Excel data into R using openxlsx: Error in file(con, "r") : invalid 'description' argument
I am trying to import the data in my Excel file into R using Openxlsx library:
library(openxlsx)
data <- read.xlsx("datafile.xlsx", sheet = "Sheet1")
However, I get the ...
- 231
4
votes
3
answers
2k
views
Evaluate Excel File Without Opening/Saving
Is there a way to calculate excel formulas without opening the file manually? If openxlsx is not the best option please feel free to suggest other packages. Thanks!
My goal is to paste data into an ...
4
votes
1
answer
2k
views
Change cell value in openxlsx workbook
I want to use openxlsx to change an individual cell in a workbook sheet and write it back out as the same .xlsx without losing the styling, validation, etc of the original .xlsx file. I stipulate ...
- 108k
4
votes
1
answer
355
views
Possibility to combine openxlsx-Workbook and xl_add_vg from rvg - Export editable graphics
I want to export some tables and ggplot-graphics from a shiny app in an excel-file. Until now I worked with openxlsx, adding many sheets and content. Now I want to export a ggplot as editable vector ...
4
votes
1
answer
1k
views
Is there a way to get the content created time of an excel file in r?
I have been trying to obtain the time at which the content of an .xlsx file was created without any success so far. I can track the much-desired information on Windows either through File Properties -&...
- 43
4
votes
1
answer
511
views
Keep protection(or re-protect) certain columns of imported Excel workbook through R
I have an Excel workbook with two sheets. On the first sheet columns B and C have formulas (referring to the second, still empty, sheet) that are protected against editing. Now I imported this ...
- 193
4
votes
1
answer
1k
views
Format an Excel cell from R if a cell is not blank
I am trying to format a cell in a particular column in Excel from R (producing a workbook using a dataframe). I am currently using openxlsx.
This is the line of code that I am currently trying to get ...
- 373
4
votes
1
answer
1k
views
Apply a style to an entire Excel sheet with the R package openxlsx
I'm utilizing an R package named openxlsx. I've created the AlignStyle seen below. I want to apply this style to the entire sheet titled 'test-sheet'. When I try to apply this style to all rows and ...
- 4,173
4
votes
0
answers
1k
views
R error argument 'findVar' is not an environment
For working with excel files in R I use the openxlsx package. Works fine, as well for reading an xlsx-file into R as for writing to such a file from within R. Until today.
In one of my scripts I have ...
- 186
4
votes
0
answers
631
views
error message with openxlsx
I have 42 excel files I am bringing into R using lapply and openxlsx. The original excel files have 2 worksheets. When I try to bring them in I get the following error message:
Error in read.xlsx....
- 978
3
votes
1
answer
4k
views
R: Getting note about using zip::zipr() instead of zip::zip() - is it cause for concern?
When I use write.xlsx from library(xlsx), the following appears in the console:
Note: zip::zip() is deprecated, please use zip::zipr() instead
What does this mean? How important is it that I use zip:...
- 77
3
votes
1
answer
3k
views
Using mergeCells() in openxlsx package R creates error in output
I'm trying to output a data frame in R into excel but I keep getting an error when I use mergeCells() when trying to open the resulting xlsx file. While the cells do merge, my data is "lost." I can ...
- 71