ycphs/openxlsx

openxlsx - a fast way to read and write complex xslx files

openxlsx's Issues

DataValidation without additional sheet

I would be very helpful to add validation to a column of a sheet, without referring to string values in another sheet. The code could look like:

dataValidation(wb, 1, col = 1, rows = 2:3, type = "list", value = c("", val1", "val2"))

NA column name causes overwrites in other sheets

Describe the bug
With a column name that is NA, it causes other sheets to having missing data.

** Steps to reproduce**

wb <- createWorkbook()
sheets <- tibble(sheetname=c('Table of Contents', "Demographics"), index=c(1,2))
demo <- data.frame(Feature=c("Age.mean", "Case.pct","Control.pct"), c(55, 40, 60), other=c(NA, NA, "hello"))
names(demo)[3] <- NA      ## take this line out, and looks fine
for (i in 1:length(sheets$sheetname)){
   addWorksheet(wb, sheets$sheetname[i], gridLines = FALSE)
}
##define header style 
headSty <- createStyle(fgFill="#DCE6F1", halign="center", border = "TopBottomLeftRight")

writeData(wb, sheet = sheets$sheetname[1], x = sheets,
            borders =  'all',
            borderColour = 'black',
            headerStyle = headSty)
  
writeData(wb, sheet = sheets$sheetname[2], demo,
            startCol = 1,   startRow = 2,
            borders = 'all',  borderColour = 'black',
            headerStyle = headSty)
  ## Save workbook
  saveWorkbook(wb, "./nacols.xlsx", overwrite = TRUE)

Additional context
Bug occurs with R 3.6.2 on RHEL7 flavor. Did not get the bug, with same data, in R 3.4.2 on RHEL6 flavor.

insertPlot fails when passing pixels for units

Copied from awalker89/openxlsx#343

Expected Behavior

Properly-sized plot inserted into workbook

Actual Behavior

 Error in dev.copy(png, filename = fileName, width = width, height = height,  : 
  invalid graphics state 

Steps to Reproduce the Problem

Z <- createWorkbook()
addWorksheet(Z, sheetName = "Plot")
plot(x = rnorm(100), y = rnorm(100))
insertPlot(Z, sheet = "Plot", width = 600, height = 500, units = "px", fileType = 'png')

Returns

Error in dev.copy(png, filename = fileName, width = width, height = height,  : 
  invalid graphics state

sessionInfo()

R version 3.4.1 Patched (2017-07-04 r72891)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

Matrix products: default

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252    LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C                           LC_TIME=English_United States.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] nloptr_1.0.4    openxlsx_4.0.17

loaded via a namespace (and not attached):
[1] compiler_3.4.1 tools_3.4.1    yaml_2.1.14    Rcpp_0.12.12  

Corrupted workbook when using template with Data Bars and / or Icon Sets

If we load a workbook with data bars (or icon sets) set up and immediately save it, workbook will be corrupted. We do not really need to input any data whatsoever.

Upon opening freshly saved workbook and attempting to recover it, there will be an error "HRESULT 0x8000ffff". Workbook will be recovered, but many aspects of formatting will be stripped.

I am able to reproduce it in current CRAN version 4.1.4 and development version from this repo. However, it works fine with an older 4.1.3 version from CRAN.

This is not the same as #18 (but may be related), since this bug is introduced with latest version and sparklines are failing in 4.1.3 and with different error message upon recovering.

library(openxlsx)

wb_test <- loadWorkbook("test_wb.xlsx")

saveWorkbook(wb_test, "test_wb_openxlsx.xlsx", overwrite = TRUE)
openXL("test_wb_openxlsx.xlsx")

The template and resulting file:
test_wb.xlsx
test_wb_openxlsx.xlsx

Session info

R version 3.6.2 (2019-12-12)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 18363)
Matrix products: default
locale:

[1] LC_COLLATE=Russian_Russia.1251  LC_CTYPE=Russian_Russia.1251    LC_MONETARY=Russian_Russia.1251 LC_NUMERIC=C                    LC_TIME=Russian_Russia.1251
attached base packages:

[1] stats     graphics  grDevices utils     datasets  methods   base
other attached packages:

[1] openxlsx_4.1.3
loaded via a namespace (and not attached):

[1] Rcpp_1.0.3             rstudioapi_0.10.0-9002 magrittr_1.5           usethis_1.5.1          devtools_2.2.1         pkgload_1.0.2          R6_2.4.1               rlang_0.4.2

[9] fansi_0.4.1            tools_3.6.2            pkgbuild_1.0.6         sessioninfo_1.1.1      cli_2.0.1              withr_2.1.2            ellipsis_0.3.0         remotes_2.1.0

[17] assertthat_0.2.1       digest_0.6.23          rprojroot_1.3-2        crayon_1.3.4           zip_2.0.4              processx_3.4.1         callr_3.4.0            fs_1.3.1

[25] ps_1.3.0               testthat_2.3.1         memoise_1.1.0          glue_1.3.1             compiler_3.6.2         desc_1.2.0             backports_1.1.5        prettyunits_1.1.0

I am also using Russian version of Excel 365 if that matters.

@ is appended to formula at start of cell range

Hey ycphs,

Thanks for continuing to develop openxlsx!

Expected Behavior

use writeForumla to write and excel formula taking the average of the absolute value of a range of cells. E.g., write "AVERAGE(ABS(A1:A10))" to cell B1.

Actal Behavior

an '@' is appended to the beginning of the cell range. E.g., "AVERAGE(ABS(@A1:A10))"

Steps to Reproduce the Problem

library(openxlsx)

openxlsx::addWorksheet(workbook, "Sheet1")
openxlsx::writeData(workbook, sheet = "Sheet1", 1:10)
openxlsx::writeFormula(workbook, sheet = "Sheet1", startCol = 2, "AVERAGE(ABS(A1:A10))")

openxlsx::saveWorkbook(workbook, file = "test.xlsx", overwrite = TRUE)

The error appears in cell B1. The formula should say "AVERAGE(ABS(A1:A10))", but says
"AVERAGE(ABS(@A1:A10))"

Thanks for your time!

sessionInfo()

R version 3.6.1 (2019-07-05)
Platform: x86_64-apple-darwin15.6.0 (64-bit)
Running under: macOS Mojave 10.14.4

Matrix products: default
BLAS:   /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib
LAPACK: /Library/Frameworks/R.framework/Versions/3.6/Resources/lib/libRlapack.dylib

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

loaded via a namespace (and not attached):
[1] compiler_3.6.1 tools_3.6.1    packrat_0.5.0 

• Version of openxlsx: 4.1.4
• Version of R: 3.6.1

Release openxlsx 4.1.4

Prepare for release:

• Check current CRAN check results
• devtools::check(remote = TRUE, manual = TRUE)
• devtools::check_win_devel()
• rhub::check_for_cran()
• rhub::check(platform = 'ubuntu-rchk')
• rhub::check_with_sanitizers()
• Update cran-comments.md
• Polish NEWS
• Review pkgdown reference index for, e.g., missing topics

Submit to CRAN:

• usethis::use_version('patch')
• devtools::submit_cran()
• Approve email

Wait for CRAN...

• Accepted 🎉
• usethis::use_github_release()
• usethis::use_dev_version()

Improve speed using stringi package

I think this pull request is worth attention awalker89/openxlsx#440: extra speed gives more value.

writeFormula does put r-script at pause until excel file is closed

R v3.6.1
R-Studio v1.2.1335
openxlsm v4.1.4
Excel 2016

I am writing data from an r-script to an existing excel file. Loading the workbook, reading sheets, doing some writeData and a saveWorkbook at the end works fine. Then in the script I am opening the so edited file using system(filename).

expected behaviour:

The r-script does run through to the end including editing the excel file and opening it up in excel.

actual behaviour:

If I add a writeFormula function to the r-script, the r-script pauses where the file is opened by the system call. The excel file is opened. Only after closing the excel file, the r-script continues to the end.

CRAN publication fails

Currently the Publication on CRAN fails. The error message is the following:

read_workbook.cpp:570:29: warning: '%d' directive writing between 1 and 10 bytes into a region of size 5 [-Wformat-overflow=]
read_workbook.cpp:506:31: warning: '%d' directive writing between 1 and 10 bytes into a region of size 5 [-Wformat-overflow=]

Looking forward get support fixing the issue.

Feature Request: Add Group and Ungroup columns & rows

Hi @ycphs

I have a problem to output a data frame to xlsx with certain styling like grouping several columns based on some conditions.

I think it will be great if openxlsx can have functionality to do grouping & ungrouping columns & rows, while user need to do some manipulation on data frame.

Currently I am doing it in a VBscript, and call it from R, which hard to trace bugs.

Here is the functionality as discussed, under 'Data' tab in excel

Creating columns from openxlsx

It would be great if there was a funtion to add/create new columns in a loaded workbook.

For example

myfile <- "..."
library(openxlsx)
tlW <- loadWorkbook(myfile)
addCol(tlW, mtcars$cyl, afterCol = 3, colNames = FALSE)
saveWorkbook(tlW, myfile,overwrite = TRUE)

Writing/saving very large xlsx file - Shiny server crashes

Expected Behavior

Code is very straightforward.
Reads excel (100k+ rows, 12 columns, both text and numbers), does calculations, prints out same file + output sheet.

Actual Behavior

Run well on different PC's when running the shiny app locally.
Crashes when run on a Shiny server. Same code runs well with less data.
Nothing in the logs.

Steps to Reproduce the Problem

(please attach an example xlsx file if possible)

sessionInfo()

Version of openxlsx: latest
Version of R: 3.6.1 deployed on Ubuntu/AWS

getTables() -> Error looking for worksheet containing & ( & )

Hello, guys,

I am using the function getTables(), to discover the tables on Sheet "T&O", but I receive the message that this sheet does not exist.

    getTables(wb,"T&O")
    Error: Sheet 'T&O' does not exist.

The function in some way looks for the sheet 'T& amp;O' , changing the '&' by ' & amp;'(I needed to put a space here for the conversion to & note happening).

Any suggestions?

Thanks in advance!

Allan

sessionInfo()
Version of openxlsx: 4.1.4
Version of R: 3.4.3

Some value are reconize like int or like Char on different sheet

Describe the bug
At first thank you and thanks to the team for this very good and useful package.

My issue is this : I have data like "0.25", on a sheet this will recognize like a int by read.xlsx and on a second sheet it will be recognize like a char.

I think the bug come from Excel and the metadata of the cell some cell are left-align (char cell) and the other are right align (int cell).

Is it possible to unconsidered the excel formatting cell ? and base the type of vector on the recognition of data ? like for a CSV ?

Maybe my demand is much a feature development and not a bug ? sorry if is it.

By the way sorry for my english I'm not nativ english speaker.

To Reproduce
This ecm file reproduce the bug :
ecm.xlsx

Expected behavior
Have in the data frame all values in int type and not some char and some numeric

writeFormula only accepts english formula names

R v3.6.1
R-Studio v1.2.1335
openxlsm v4.1.4
Excel 2016

I am using the writeFormula function. When writing "=SUMME(T17:T18)" to the formula Excel does give me a #NAME? error. After entering the cell with F2 and pressing return to confirm the German formula, the formula is accepted and the value is calculated.

When using the English syntax "=SUM(T17:T18)" the formula is accepted by excel straight away.

I think a note in the documentation would be helpful. Idealy using a locale formula would be allowed too.

Edit Author/Creator

Option to edit the Last Modified By Attribute when Updating an Existing Sheet in Excel, Because by Default it Takes ExcelJs not the user's Name

Excel Array Formulas

Is your feature request related to a problem? Please describe.
I'm trying to automate the creation of a survey workbook that incorporates Excel Tables for data input. I currently manually create array formulas to add an automatically-updated visible entry number to the table. My workbook now has dozens of tables, so the array formula creation is becoming burdensome.

Describe the solution you'd like
I would like to be able to use openxlsx to create these array formulas as part of my automation.

Describe alternatives you've considered
I cannot think of alternatives other than continuing manual array formula creation.

Macros removed from buttons when using loadWorkbook() on .xlsm file

Describe the bug
Hi,

First off I just want to say that this package is excellent and my bug must be quite a niche use case. Also, I am totally new to this so I apologise in advance if I write anything stupid.

I am currently working on something where I need to write some data to a .xlsm file, which I am using as a template.

The .xlsm file has a worksheet containing a simple form with some buttons that are linked to macros.

After the workbook is loaded into R via loadWorkbook() and then saved using saveWorkbook(), the macro links are getting removed from the buttons in the form - the buttons simply become pictures instead. The macros also don't appear to be listed when you go to Developer -> Macros. I can see that all the VBA code is there, though.

Looking at the underlying folder structure of the .xlsm file, it looks like the ctrlProps folder is missing from the xl directory:

Steps to reproduce the behavior:

1. Run loadWorkbook() on a .xlsm file with a button linked to a macro
2. Save the workbook to any location using saveWorkbook()
3. Open the .xlsm file output
4. Observe that the buttons have simply become pictures and are no longer linked to macros

Expected behavior
Macro links to the buttons are maintained and can be executed

Desktop (please complete the following information):

• OS: Windows 10 Enterprise

sessionInfo()
R version 3.6.1 (2019-07-05)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 17763)
openxlsx version 4.1.4

Use formula as table header

I would like to use formulas in all cells of the first row of a sheet. This is because I need the column header to be linked to another sheet. As the second sheet changes, so do column headers in the first sheet.

Perhaps this feature is already implemented. However, I have not been able to find it in the package.

Clarification documentation makeHyperlinkString

I was interesting in creating an external link to a docx file in an excel cell. I went through your examples and this was the one that came closest:

## Link to file - Text to display
writeFormula(wb, "Sheet1", startRow = 3
  , x = makeHyperlinkString(sheet = "testing", row = 3, col = 10
    , file = system.file("loadExample.xlsx", package = "openxlsx"), text = "Link to File."))

In the corresponding excel file I got following formula: =HYPERLINK("[]'testing'!J3", "Link to File."). However, it is not really clear what this is accomplishing. An additional comment in the R file that explains this would be helpful.

Next, I played around with it and found that if I change the formula to: =HYPERLINK("[C:/path to file.docx]", "Link to File."), I created a hyperlink to the actual file.

Perhaps an example like this can be added to the documentation?

## Link to external file - Text to display
openxlsx::writeFormula(wb, "Sheet1", startRow = 10, startCol=1,
                      x= "=HYPERLINK(\"[C:/Users]\", \"Link to an external file\")")

Kind regards.

Release openxlsx 4.1.3

Prepare for release:

• Check current CRAN check results
• devtools::check(remote = TRUE, manual = TRUE)
• devtools::check_win_devel()
• rhub::check_for_cran()
• Update cran-comments.md
• Polish NEWS
• Review pkgdown reference index for, e.g., missing topics

Submit to CRAN:

• usethis::use_version('patch')
• devtools::submit_cran()
• Approve email

Wait for CRAN...

• Accepted 🎉

Custom Input Messages and Error Alerts for Validations

Is your feature request related to a problem? Please describe.
I'm creating validations that require custom input messages and error alerts, but the current implementation of validations does not appear to support either type of custom message.

Describe the solution you'd like
I would like to be able to add a custom input message, titles, and error alert to validations.

Describe alternatives you've considered
I've tried using addComment for the Input Message, but that doesn't produce exactly the result I'm looking for. I can see no alternative for a custom Error Alert.

setWidth="auto" makes excel **really slow**

Describe the bug

I've noticed that the excel files I create with openxlsx make excel extremely (unusably) slow when using the "auto" feature. It's possible this is because some of my columns contain rather long text (gene lists).

To Reproduce
Steps to reproduce the behavior:

1. Make a long table with long text columns
2. Split into list of data.frames
3. Write to xlsx
4. Open with Excel on MacOS
5. Wait for the spinning wheel to finish every time you click anywhere.

Expected behavior
Unless the "auto" setting does something funky, this is probably an Excel bug. So either of these:

A. Put a warning in to the docs against using "auto" on big excel sheets with long data columns.
B. Hack something to guess column widths and set them fixed, as I believe one must when using Excel directly.

Desktop (please complete the following information):

• OS: Excel on MacOS Catalina, R on Linux

Function to copy sheets between workbook objects

When processing the construction of several sheets in parallel, I ended up with a list of workbook objects containing one heavily formatted sheet each. Apparently there is no way to combine all those sheets into one workbook object.

I propose a addSheetFromWorkbook() function, that does just that - or any other way of concatenating sheets from workbook objects.

Current alternatives involve either losing all sheet information beyond the raw cell data, combining the sheets manually or using an external VBA script, which involves additional complications when you are on UNIX-based systems.

note: zip::zip() is deprecated, please use zip::zipr() instead

When writing xlsx files, i am getting the following:

note: zip::zip() is deprecated, please use zip::zipr() instead

here is an example:

library(openxlsx)

df <- data.frame(x = c(1, 2, 3), y = c("a", "b", "c"))

write.xlsx(df, file = "C:/Users/abrown_17/Desktop/df_test.xlsx", overwrite = T)

Thanks,
Alex

All tabs becoming visible after loadWorkbook

Expected Behavior

When opening a workbook with loadWorkbooks, all tabs should retain the same visibility state that they have in the saved workbook that is loaded.

Actual Behavior

All tabs become visible.

Steps to Reproduce the Problem

(please attach an example xlsx file if possible)

1. Create a workbook (an .xlsm in my case, if that matters)

2. Hide one of the tabs

3. call wb <- loadWorkbook(filename)

4. sheetVisibility(wb) will show all the tabs are visible; when saving the resulting workbook, all of the tabs are visible.

sessionInfo()

• Version of openxlsx: 4.1.3
• Version of R: 3.6.1

improve description for hyperlink usage

The documentation for openxlsx package does provide information on the function makeHyperlinkString and for writeForumula.

In the description for makeHyperlinkString a reference to writeForumula is given:

create Excel hyperlink string

Wrapper to create internal hyperlink string to pass to writeFormula()

But the documentation for writeForumula does not contain an example or further description on how to use the makeHyperlinkString function in conjunction with writeForumula.

Update NEWS for 4.1.2

The current version of openxlsx on CRAN is 4.1.2. Unfortunately, the last record in NEWS are for version 4.1.1 (https://cran.rstudio.com/web/packages/openxlsx/NEWS). I expect this file to be updated: at least it should be announced that there is a new maintainer.

Inline formatting of text within a cell

Is your feature request related to a problem? Please describe.
I'm using openxlsx to create an Excel workbook that has an introduction and instructions in a large merged cell on the first worksheet in the workbook. I currently have to manually format parts of the text after the workbook has been created.

Describe the solution you'd like
I'd like to be able to write text with inline character formatting (e.g., size, color, font type, weight, etc.). The approach taken for Comments, although not perfect for my needs, would be useful.

Describe alternatives you've considered
I haven't found a way to do this other than manually after the workbook has been saved.

Additional context

saveWorkbook(): zip error

Describe the bug
When trying to save a workbook variable to an .xlsx file I receive the following error message:

Fehler in zip_internal(zipfile, files, recurse, compression_level, append = FALSE, :
zip error: Cannot add file [Content_Types].xmlto archivefile347c2455470.xlsx`` in file zip.c:394

To Reproduce
I set up a reproducible example, but I am pretty sure, this issue is user machine dependent.

# RScript for 'openxls saveWorkbook'-Issue:
library(openxlsx)
# Load dataset 'mtcars'
var1 <- mtcars
# Create workbook variable
wb1 <- createWorkbook("wb_mtcars")
addWorksheet(wb = wb1, sheetName = names(var1)[1])
writeData(wb=wb1, sheet = names(var1)[1], x = var1[1] ,withFilter = FALSE)
saveWorkbook(wb=wb1, file=paste(getwd(), '/wb1.xlsx',sep = ""),overwrite = TRUE)

Desktop (please complete the following information):

• OS: Win10
• R.3.6.1
• R.Studio Version 1.2.5033
• openxlsx 4.1.4

A few weeks ago, the saveWorkbook function worked fine. Unfortunately, I had to add multiple packages and Rtools 3.5 for some other reasons. As part of this, I also updated from R.3.6.1 (I guess) to R.3.6.2. Comming back to my old script with the saveWorkbook, the error occured.

I already found the following issue and tried to set my system variable TMP and TEMP in the script, but the error message still occurs.
[https://github.com/awalker89/openxlsx/issues/478]
The environment variables are (or contain) the following:
System
PATH: C:\Rtools\bin; [...]; C:\Program Files\R\R-3.6.2\bin\x64
TEMP: C:\WINDOWS\TEMP
TMP: C:\WINDOWS\TEMP

User
Path: no R or Rtools path
TEMP: %USERPROFILE%\AppData\Local\Temp
TMP: %USERPROFILE%\AppData\Local\Temp

Since my username has an accent, the sys.getenv() shows
TEMP C:\Users\ANDR~1\AppData\Local\Temp
Changing manually does lead to correct name, but does not solve the issue
Sys.setenv(TMP = "C:\\Users\\André\\AppData\\Local\\Temp")
Sys.setenv(TEMP = "C:\\Users\\André\\AppData\\Local\\Temp")

Sys.setenv(TMP = "C:\\WINDOWS\\TEMP")
Sys.setenv(TEMP = "C:\\WINDOWS\\TEMP")

TEMP C:\WINDOWS\TEMP
TMP C:\WINDOWS\TEMP

sessionInfo()

R version 3.6.2 (2019-12-12)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 18362)

Matrix products: default

locale:
[1] LC_COLLATE=German_Germany.1252 LC_CTYPE=German_Germany.1252 LC_MONETARY=German_Germany.1252 LC_NUMERIC=C
[5] LC_TIME=German_Germany.1252

attached base packages:
[1] stats graphics grDevices utils datasets methods base

other attached packages:
[1] openxlsx_4.1.4

loaded via a namespace (and not attached):
[1] compiler_3.6.2 tools_3.6.2 Rcpp_1.0.3 stringi_1.4.4 zip_2.0.4

Many thanks in advance for any adivce from your end!
André

"Worksheet by that name already exists" error - specify name?

Might addWorksheet specify the name of the worksheet associated with the "Worksheet by that name already exists" error? I use this package in functions that create many worksheets so this would help identify my mistake.

Installation fails with ICU version 65

openxlsx assumes that the version of the system ICU library is 64 and thus fails to install if a different version is installed.

To Reproduce: try to install openxlsx on a system with ICU version 65

Expected behavior: package should install

Actual behavior: I get the following output:

> install.packages("openxlsx")
Installing package into ‘/home/kozet/R/x86_64-pc-linux-gnu-library/3.6’
(as ‘lib’ is unspecified)
trying URL 'https://cran.rstudio.com/src/contrib/openxlsx_4.1.4.tar.gz'
Content type 'application/x-gzip' length 1383224 bytes (1.3 MB)
==================================================
downloaded 1.3 MB

* installing *source* package ‘openxlsx’ ...
** package ‘openxlsx’ successfully unpacked and MD5 sums checked
** using staged installation
** libs
g++ -std=gnu++11 -I"/usr/include/R/" -DNDEBUG  -I"/home/kozet/R/x86_64-pc-linux-gnu-library/3.6/Rcpp/include" -D_FORTIFY_SOURCE=2  -fpic  -march=x86-64 -mtune=generic -O2 -pipe -fno-plt  -c RcppExports.cpp -o RcppExports.o
g++ -std=gnu++11 -I"/usr/include/R/" -DNDEBUG  -I"/home/kozet/R/x86_64-pc-linux-gnu-library/3.6/Rcpp/include" -D_FORTIFY_SOURCE=2  -fpic  -march=x86-64 -mtune=generic -O2 -pipe -fno-plt  -c helper_functions.cpp -o helper_functions.o
g++ -std=gnu++11 -I"/usr/include/R/" -DNDEBUG  -I"/home/kozet/R/x86_64-pc-linux-gnu-library/3.6/Rcpp/include" -D_FORTIFY_SOURCE=2  -fpic  -march=x86-64 -mtune=generic -O2 -pipe -fno-plt  -c load_workbook.cpp -o load_workbook.o
gcc -I"/usr/include/R/" -DNDEBUG  -I"/home/kozet/R/x86_64-pc-linux-gnu-library/3.6/Rcpp/include" -D_FORTIFY_SOURCE=2  -fpic  -march=x86-64 -mtune=generic -O2 -pipe -fno-plt  -c openxlsx_init.c -o openxlsx_init.o
g++ -std=gnu++11 -I"/usr/include/R/" -DNDEBUG  -I"/home/kozet/R/x86_64-pc-linux-gnu-library/3.6/Rcpp/include" -D_FORTIFY_SOURCE=2  -fpic  -march=x86-64 -mtune=generic -O2 -pipe -fno-plt  -c read_workbook.cpp -o read_workbook.o
g++ -std=gnu++11 -I"/usr/include/R/" -DNDEBUG  -I"/home/kozet/R/x86_64-pc-linux-gnu-library/3.6/Rcpp/include" -D_FORTIFY_SOURCE=2  -fpic  -march=x86-64 -mtune=generic -O2 -pipe -fno-plt  -c write_data.cpp -o write_data.o
g++ -std=gnu++11 -I"/usr/include/R/" -DNDEBUG  -I"/home/kozet/R/x86_64-pc-linux-gnu-library/3.6/Rcpp/include" -D_FORTIFY_SOURCE=2  -fpic  -march=x86-64 -mtune=generic -O2 -pipe -fno-plt  -c write_file.cpp -o write_file.o
g++ -std=gnu++11 -I"/usr/include/R/" -DNDEBUG  -I"/home/kozet/R/x86_64-pc-linux-gnu-library/3.6/Rcpp/include" -D_FORTIFY_SOURCE=2  -fpic  -march=x86-64 -mtune=generic -O2 -pipe -fno-plt  -c write_file_2.cpp -o write_file_2.o
g++ -std=gnu++11 -shared -L/usr/lib64/R/lib -Wl,-O1,--sort-common,--as-needed,-z,relro,-z,now -o openxlsx.so RcppExports.o helper_functions.o load_workbook.o openxlsx_init.o read_workbook.o write_data.o write_file.o write_file_2.o -L/usr/lib64/R/lib -lR
installing to /home/kozet/R/x86_64-pc-linux-gnu-library/3.6/00LOCK-openxlsx/00new/openxlsx/libs
** R
** inst
** byte-compile and prepare package for lazy loading
Error in dyn.load(file, DLLpath = DLLpath, ...) : 
  unable to load shared object '/home/kozet/R/x86_64-pc-linux-gnu-library/3.6/stringi/libs/stringi.so':
  libicui18n.so.64: cannot open shared object file: No such file or directory
Calls: <Anonymous> ... namespaceImport -> loadNamespace -> library.dynam -> dyn.load
Execution halted
ERROR: lazy loading failed for package ‘openxlsx’
* removing ‘/home/kozet/R/x86_64-pc-linux-gnu-library/3.6/openxlsx’
Warning in install.packages :
  installation of package ‘openxlsx’ had non-zero exit status

The downloaded source packages are in
	‘/tmp/Rtmp7IjBBN/downloaded_packages’

Workaround: it should be possible to symlink libicui18n.so.64 to libicui18n.so.65 nope, ICU has versioned symbols

Function getCellRefs is documented, but not defined

Expected Behavior

getCellRefs (data.frame(6,8))
would show Excel-style alphanumeric cell reference

Actual Behavior

Cannot find function "getCellRefs"

Steps to Reproduce the Problem

getCellRefs (data.frame(6,8))

sessionInfo()

R version 3.6.1 (2019-07-05)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 18362)

Matrix products: default

Random number generation:
RNG: Mersenne-Twister
Normal: Inversion
Sample: Rounding

locale:
[1] LC_COLLATE=German_Germany.1252 LC_CTYPE=German_Germany.1252 LC_MONETARY=German_Germany.1252
[4] LC_NUMERIC=C LC_TIME=German_Germany.1252

attached base packages:
[1] stats graphics grDevices utils datasets methods base

other attached packages:
[1] dplyr_0.8.3 openxlsx_4.1.2 data.table_1.12.6 dbplyr_1.4.2 RMySQL_0.10.17
[6] DBI_1.0.0

loaded via a namespace (and not attached):
[1] Rcpp_1.0.2 crayon_1.3.4 assertthat_0.2.1 R6_2.4.0 magrittr_1.5 zip_2.0.4
[7] pillar_1.4.2 rlang_0.4.1 fortunes_1.5-4 tools_3.6.1 glue_1.3.1 purrr_0.3.3
[13] compiler_3.6.1 pkgconfig_2.0.3 tidyselect_0.2.5 tibble_2.1.3

• Version of openxlsx: 4.1.2
• Version of R: 3.6.1

readWorkbook fillMergedCells = T is not working

Expected Behavior

  plate rows                   col1                   col2
1     1    A       Std 1 (30 ng/mL)       Std 1 (30 ng/mL)
2     1    B       Std 2 (15 ng/mL)       Std 2 (15 ng/mL)
3     1    C      Std 3 (7.5 ng/mL)      Std 3 (7.5 ng/mL)
4     1    D     Std 4 (3.75 ng/mL)     Std 4 (3.75 ng/mL)
5     1    E    Std 5 (1.875 ng/mL)    Std 5 (1.875 ng/mL)
6     1    F   Std 6 (0.9375 ng/mL)   Std 6 (0.9375 ng/mL)
7     1    G  Std 7 (0.46875 ng/mL)  Std 7 (0.46875 ng/mL)
8     1    H Std 8 (0.234375 ng/mL) Std 8 (0.234375 ng/mL)

Actual Behavior

  plate rows                   col1 col2
1     1    A       Std 1 (30 ng/mL)   NA
2     1    B       Std 2 (15 ng/mL)   NA
3     1    C      Std 3 (7.5 ng/mL)   NA
4     1    D     Std 4 (3.75 ng/mL)   NA
5     1    E    Std 5 (1.875 ng/mL)   NA
6     1    F   Std 6 (0.9375 ng/mL)   NA
7     1    G  Std 7 (0.46875 ng/mL)   NA
8     1    H Std 8 (0.234375 ng/mL)   NA

Steps to Reproduce the Problem

mergedCell.xlsx

library(openxlsx)
file = "mergedCell.xlsx"
wb <- openxlsx::loadWorkbook(file)
openxlsx::readWorkbook(wb, sheet = 1, cols = 1:4, fillMergedCells = T) 

sessionInfo()

R version 3.6.1 (2019-07-05)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 18.04.3 LTS

Matrix products: default
BLAS:   /usr/lib/x86_64-linux-gnu/openblas/libblas.so.3
LAPACK: /usr/lib/x86_64-linux-gnu/libopenblasp-r0.2.20.so

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C               LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8     LC_MONETARY=en_US.UTF-8   
 [6] LC_MESSAGES=en_US.UTF-8    LC_PAPER=en_US.UTF-8       LC_NAME=C                  LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] openxlsx_4.1.2     jsonlite_1.6       plumber_0.4.7.9000

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.2          packrat_0.5.0       crayon_1.3.4        later_1.0.0         crul_0.8.4          R6_2.4.0            magrittr_1.5       
 [8] zip_2.0.4           stringi_1.4.3       curl_4.2            rstudioapi_0.10     promises_1.1.0.9000 tools_3.6.1         sofa_0.3.2.9100    
[15] httpuv_1.5.2.9000   compiler_3.6.1      httpcode_0.2.0     

R version: 3.6.1
openxlsx version: 4.1.2

sort functions alphabetical in r documentation

The R-documentation for openxlsx package does list all functions. But, at least to me, they seem to be at random order.

I believe it would be much more useful to have the functions there in alphabetical order. The package documentation that is provided within R-Studio does list functions in alphabetical order though. 🤔

functions used from package dependencies require updating. zip::zip()

functions used from package dependencies require updating.

When i run:
openxlsx::saveWorkbook()
i receive the following warning:

Error message: "Note: zip::zip() is deprecated, please use zip::zipr() instead"

• OS: Windows 10
• Rstudio: 1.2.1335
• R: 3.6.1
• openxlsx: 4.1.3

setColWidths(..., widths='auto')

Expected Behavior: When setting widths='auto': expect the column width to be the minimum which fits the data in that column.

Actual Behavior: It sets the column width to 250 (nearly the full screen width)

Steps to Reproduce the Problem

(please attach an example xlsx file if possible)

wb<-loadWorkbook('ColWidthBug.xlsx') # see attached file
setColWidths(wb, 1, 1, widths='auto')
saveWorkbook('ColWidthsBug2.xlsx') # this spreadsheet has a column width of 250
2.

sessionInfo()

• Version of openxlsx: 4.1.4
• Version of R: 3.6.2
  ColWidthBug.xlsx

Adding multiple conditional formats of type databar breaks the Excel file

There are two bugs for conditional formatting in WorkbookClass.R and baseXML.R.

The first bug: if the type of the conditional format is "expression", then the guid is derived from the length of worksheets[[sheet]]$extLst, but nothing is appended to extLst. So if another „expression“ or a „databar“ is added later, it will get the same guid.

The second bug: if more than one region with „databar“s is added (e.g. for two columns with independent scaling), then each of the database conditional formatting nodes is surrounded by

<ext uri="{78C0D931-6437-407d-A8EE-F0AAD7539E65}" 
      xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
…
</ext>

This leads to an error in Excel - this environment should occur only once if there is at least one conditional format of type databar.

So this is wrong:

  <extLst>
    <ext uri="{78C0D931-6437-407d-A8EE-F0AAD7539E65}" 
      xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
      <x14:conditionalFormattings>
        <x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
          <x14:cfRule type="dataBar" id="{F7189283-14F7-4DE0-9601-54DE9DB40000}">
            <x14:dataBar minLength="0" maxLength="100" border="0" gradient = "0" negativeBarBorderColorSameAsPositive="0">
              <x14:cfvo type="autoMin"/>
              <x14:cfvo type="autoMax"/>
              <x14:borderColor rgb="FFFFB628"/>
              <x14:negativeFillColor rgb="FFFFB628"/>
              <x14:negativeBorderColor rgb="FFFFB628"/>
              <x14:axisColor rgb="FF000000"/>
            </x14:dataBar>
          </x14:cfRule>
          <xm:sqref>B2:B43</xm:sqref>
        </x14:conditionalFormatting>
      </x14:conditionalFormattings>
    </ext>
    <ext uri="{78C0D931-6437-407d-A8EE-F0AAD7539E65}" 
      xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
      <x14:conditionalFormattings>
        <x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
          <x14:cfRule type="dataBar" id="{F7189283-14F7-4DE0-9601-54DE9DB40001}">
            <x14:dataBar minLength="0" maxLength="100" border="0" gradient = "0" negativeBarBorderColorSameAsPositive="0">
              <x14:cfvo type="autoMin"/>
              <x14:cfvo type="autoMax"/>
              <x14:borderColor rgb="FFFFB628"/>
              <x14:negativeFillColor rgb="FFFFB628"/>
              <x14:negativeBorderColor rgb="FFFFB628"/>
              <x14:axisColor rgb="FF000000"/>
            </x14:dataBar>
          </x14:cfRule>
          <xm:sqref>C2:C43</xm:sqref>
        </x14:conditionalFormatting>
      </x14:conditionalFormattings>
    </ext>
  </extLst>

And this is how it should look like:

  <extLst>
    <ext uri="{78C0D931-6437-407d-A8EE-F0AAD7539E65}" 
      xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
      <x14:conditionalFormattings>
        <x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
          <x14:cfRule type="dataBar" id="{F7189283-14F7-4DE0-9601-54DE9DB40000}">
            <x14:dataBar minLength="0" maxLength="100" border="0" gradient = "0" negativeBarBorderColorSameAsPositive="0">
              <x14:cfvo type="autoMin"/>
              <x14:cfvo type="autoMax"/>
              <x14:borderColor rgb="FFFFB628"/>
              <x14:negativeFillColor rgb="FFFFB628"/>
              <x14:negativeBorderColor rgb="FFFFB628"/>
              <x14:axisColor rgb="FF000000"/>
            </x14:dataBar>
          </x14:cfRule>
          <xm:sqref>B2:B43</xm:sqref>
        </x14:conditionalFormatting>
        <x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
          <x14:cfRule type="dataBar" id="{F7189283-14F7-4DE0-9601-54DE9DB40001}">
            <x14:dataBar minLength="0" maxLength="100" border="0" gradient = "0" negativeBarBorderColorSameAsPositive="0">
              <x14:cfvo type="autoMin"/>
              <x14:cfvo type="autoMax"/>
              <x14:borderColor rgb="FFFFB628"/>
              <x14:negativeFillColor rgb="FFFFB628"/>
              <x14:negativeBorderColor rgb="FFFFB628"/>
              <x14:axisColor rgb="FF000000"/>
            </x14:dataBar>
          </x14:cfRule>
          <xm:sqref>C2:C43</xm:sqref>
        </x14:conditionalFormatting>
      </x14:conditionalFormattings>
    </ext>
  </extLst>

So there are two fixes needed:

1. Code needs to be added that prevents two conditional formats of types expression or databar from getting the same guid.
2. Adding a separate {78C0D931-6437-407d-A8EE-F0AAD7539E65} ext node for each databar format breaks the Excel file, so it is necessary to keep track of a list of databar formats and to sourround them with a single {78C0D931-6437-407d-A8EE-F0AAD7539E65} node. This requires changes to the conditionalFormatting method in WorkbookClass.R and to the gen_databar_extlst function in baseXML.R.

Create a pkgdown website

Create a pkgdown website and publish it as GitHub pages. Functions usethis::use_pkgdown_travis(), pkgdown::deploy_site_github() and travis::use_travis_deploy() are really helpful.
NOTE: some steps should be done outside RStudio so read all the instructions before applying them.

Can't update XLSX file with pivot table with last CRAN version 4.1.4

Expected Behavior

Keep pivot table pointing at a 'Data' sheet OK after updating this data.

Actual Behavior

I load a file ('FileWithPivot' in the following) with 2 sheets: data ('Data'), and a pivot table. I update the data with a new table 'UPData' (same format, column names, with only dates, strings and numbers, consisting in a few thousands of lines + 20 columns)):

wb <- loadWorkbook(FileWithPivot)
removeWorksheet(wb, 'Data')
addWorksheet(wb, 'Data')
writeData(wb, 'Data', UPData, colNames=T)
openXL(wb)

The result is an XLSX file, with updated Data, but pivot table broken.

The same code + data works fine with previous version of open xlsx 4.1.0.1 (R version 3.6.1), but the pivot table gets broken with 4.1.2 (CRAN version).

Steps to Reproduce the Problem

(please attach an example xlsx file if possible)

sessionInfo()

• Version of openxlsx: 4.1.2
• Version of R: 3.6.2

textWrap gives corrupted sheet

Hey,

When I use wrapText=TRUE I obtain a corrupted file:

wb <- createWorkbook()

addWorksheet(wb, "Expenditure", gridLines = FALSE) 

writeData(wb, sheet = 1, USPersonalExpenditure, rowNames = TRUE)

bodyStyle <- createStyle(fontSize=10, border="TopBottomLeftRight", borderColour = "black" , wrapText=TRUE)
headerStyle <- createStyle(fontSize=14, border="TopBottomLeftRight", borderColour = "black", textDecoration="bold")

addStyle(wb, sheet = 1, bodyStyle, rows = 2:6, cols = 1:6, gridExpand = TRUE)
addStyle(wb, sheet = 1, headerStyle, rows = 1, cols = 1:6, gridExpand = TRUE)

setColWidths(wb, 1, cols=1, widths = 21) ## set column width for row names column

saveWorkbook(wb, "addStyleExample.xlsx", overwrite = TRUE)

I attached a file with and without text wrapping for your convenience.

addStyleExample_wrapF.xlsx

addStyleExample_wrapT.xlsx

Error in saveWorkbook

I am loading a public workbook available at https://docs.google.com/spreadsheets/d/1rXIiT1zSYhFegSdAvE0yJX16q-bvXVNpYIYdd5YgjhI/export?format=xlsx. Loading is successful. However, I am not able to save it. The following error message is issued:

Error in comment_list[[i]]$style[[j]] : subscript out of bounds

The code that I am using is below.

wb <- openxlsx::loadWorkbook(
   file = "https://docs.google.com/spreadsheets/d/1rXIiT1zSYhFegSdAvE0yJX16q-bvXVNpYIYdd5YgjhI/export?format=xlsx")
openxlsx::saveWorkbook(wb = wb, file = 'tmp-febr-planilha-modelo-geral.xlsx', overwrite = T)

cloneWorksheet returns an error

Expected Behavior

Clone a sheet on a created workbook

Actual Behavior

library(openxlsx, lib.loc = "C:/Program Files/R/R-3.3.3/library")

wb <- createWorkbook("Fred")
addWorksheet(wb, "Sheet 1")
cloneWorksheet(wb, "Sheet 2", clonedSheet = "Sheet 1")
Error in wb$cloneWorksheet(sheetName = sheetName, clonedSheet = clonedSheet) :
could not find function "validateSheet"�

The function validateSheet() is called by:

wb$cloneWorksheet
Class method definition for method cloneWorksheet()
function (sheetName, clonedSheet)
{
clonedSheet = validateSheet(clonedSheet)
if (!missing(sheetName)) {
if (grepl(pattern = ":", x = sheetName))
stop("colon not allowed in sheet names in Excel")�
........
}

sessionInfo()

R version 3.3.3 (2017-03-06)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows Server x64 (build 14393)

locale:
[1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United States.1252 LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C LC_TIME=English_United States.1252

attached base packages:
[1] stats graphics grDevices utils datasets methods base

other attached packages:
[1] openxlsx_4.1.4

loaded via a namespace (and not attached):
[1] tools_3.3.1 Rcpp_1.0.2 stringi_1.4.3 zip_2.0.4 �

• Version of openxlsx: 4.1.4
• Version of R: 3.3.3

Thanks for looking into it.

David

Writing NA's as a custom string

Desired behaviour

When writing confidentialised data, with suppressed rows encoded as NA's, I want to fill each NA cell with an "S" character.

Actual Behavior

Because this mixes numeric and character types, I cannot create this data in R and write the whole column out while preserving the different types. Instead, I have to write out an "S" to each suppressed cell individually.

Proposed solution

Add an "na.string" parameter that defaults to NULL. If "keepNA" is TRUE, and "na.string" is not NULL, write na.string as a string to any cells containing NA's.

Protected excel corrupt

Describe the bug
Protected excel corrupt when loading and saving

To Reproduce
Steps to reproduce the behavior:

library(openxlsx)
wb <- createWorkbook()
addWorksheet(wb, "S1")
protectWorkbook(wb, protect = TRUE, password = "Password", lockStructure = TRUE)
saveWorkbook(wb, "WorkBook_Protection.xlsx",overwrite=TRUE)
# ok
# system2("open", "WorkBook_Protection.xlsx")
wb <- loadWorkbook(file = "WorkBook_Protection.xlsx")
saveWorkbook(wb, file = "WorkBook_Protection_1.xlsx", overwrite = TRUE)
# fail
# system2("open", "WorkBook_Protection_1.xlsx")

R version 3.6.0 (2019-04-26)
Platform: x86_64-redhat-linux-gnu (64-bit)
Running under: Oracle Linux Server 7.7
Matrix products: default
BLAS/LAPACK: /usr/lib64/R/lib/libRblas.so
locale:
[1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C LC_TIME=en_US.UTF-8
[4] LC_COLLATE=en_US.UTF-8 LC_MONETARY=en_US.UTF-8 LC_MESSAGES=en_US.UTF-8
[7] LC_PAPER=en_US.UTF-8 LC_NAME=C LC_ADDRESS=C
[10] LC_TELEPHONE=C LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] openxlsx_4.1.4.9000
loaded via a namespace (and not attached):
[1] compiler_3.6.0 tools_3.6.0 Rcpp_1.0.3 stringi_1.4.6 zip_2.0.4

check.names behavior

ycphs, thank you for taking over maintenance of this package!

There is a bug-report/feature-request on the previous repo that I think merits revisiting: awalker89/openxlsx#102, namely:

When loading a table from an Excel worksheet using read.xlsx, spaces in column names are still converted to dots (.), even when check.names = FALSE is specified. The reason is most likely due to the fact that column names are modified using the function clean_names at loading. For example, I saw the following line in readWorkbook.R:

m <- .Call("openxlsx_readWorkbook", v, r, string_refs, isDate, nRows, colNames, skipEmptyRows, origin, clean_names, PACKAGE = "openxlsx")

I am not the OP.

---

My suggestion was to provide a slow transitional change of the default behavior:

1. In package .onLoad (or .onAttach?), mention the default change. This message should stay throughout transition period.

2a. In readWorkbook, if check.names not specified and "bad column name" found, convert as before but provide a warning. (If check.names is being specified, then no warning, just do as it asks.)

2b. On a subsequent transitional version, if check.names is again not specified, then provide a warning but do not convert the column name.

Alex stated a concern that "changing this could upset a lot of people", though I do not completely agree. I replied:

If you change the default behavior from FALSE to TRUE, then you shouldn't be impacting current users: if they don't specify check.names and are not trying to keep spacey-names, then they won't notice a difference. If they do specify it and haven't raised an issue yet, then it perhaps doesn't impact them (at all or enough to be the squeaky wheel).

---

I think more of the comments there are also noteworthy:

• @pooranis said

  Can this behavior be modified slightly? I think perhaps having a separate option, e.g. clean.names, which defaults to the current behavior of replacing spaces would be helpful for users who are used to this. And then check.names could behave as usual, and setting both to FALSE will turn off any column renaming

  (While I do not agree with clean.names, it's another opinion.)

• @WilDoane said

  this behavior changes the user's data, irreversibly ...

  Opening a workbook and saving it out again should be (IMHO) an identity operation: same data out as in, barring other user caused changes.

Percentage class on column with cell contaning 0 values

Describe the bug
Setting column class to percentage in a dataframe with multiple columns causes Excel to throw error on opening if the column itself contains one or more 0 values

To Reproduce

library(openxlsx)

test <- data.frame(
  val = c('alpha', 'beta', 'gamma', 'delta'),
  pcnt = c(0, 0.1, 0.2, 1)
  )
class(test$pcnt) <- "percentage"

wb <- createWorkbook()
addWorksheet(wb, "PCNT Test")
writeData(wb, sheet = "PCNT Test", x = test, startRow = 1, colNames = FALSE)

openXL(wb)

if val column is removed no corruption problem is detected by excel on file opening. Note on the error is "Number stored as text"

Expected behavior
No Warning on Excel opening and correct percentage render

Screenshots
Wrong

Correct

Desktop (please complete the following information):

• OS: Windows/Linux

sessionInfo()
`
R version 3.6.0 (2019-04-26)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 16299)

Matrix products: default

locale:
[1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United States.1252 LC_MONETARY=English_United States.1252 LC_NUMERIC=C
[5] LC_TIME=English_United States.1252

attached base packages:
[1] stats graphics grDevices utils datasets methods base

other attached packages:
[1] openxlsx_4.1.3
`

makeHyperlinkString for external files should have sheet, row and col as optional parameters

When using the makeHyperlinkString function for an external file, the parameter sheet is a "must-have" parameter, not an optional one.

When passing the string to writeFormula, the hyperlink text=link does get the sheet name added to the filename like this:
=HYPERLINK("[G:/foldername/filename.ext]'Sheetname'!rowcol

I can not reproduce it now anymore, but on the first run like that, my excel file got corrupted. Excel error report said, the file could be corrected by removing a named area.

The hyperlink string should only pass the filename without sheet information.

Fix Excel file corruption when multiple data validation on a sheet

Adding multiple data validation to a worksheet produces corrupt Excel 2016 files.

Original issue is here: awalker89/openxlsx#266 with test code in which tkunstek produced a pull request.

I have incorporated that particular commit into my own repository and it works ok. Bit of a git/github newbie, so can you look at pulling the relevant fix into this repository.

Thanks

sparkline in template gives corrupted sheet

Describe the bug
When using an existing XLSX as a template file to fill and if this file has sparklines graphs in it, opening, updating and saving to a new XLSX file fives corrupted sheets.

This is true on all those scenarios:

1. R/openxlsx is used to manipulate the set of cells/region on which sparkline graphs are picking up data

2. R/openxlsx is used to manipulate the XLSX template but without touching those cells where sparkline graphs are defined

3. R/openxls is used to open, copy and then save a copy of the XLSX template, without touching any cell inside it

sessionInfo()
R version 3.6.0 (2019-04-26)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Debian GNU/Linux 9 (stretch)

Matrix products: default
BLAS/LAPACK: /usr/lib/libopenblasp-r0.2.19.so

locale:
[1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8
[5] LC_MONETARY=en_US.UTF-8 LC_MESSAGES=C LC_PAPER=en_US.UTF-8 LC_NAME=C
[9] LC_ADDRESS=C LC_TELEPHONE=C LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C

attached base packages:
[1] stats graphics grDevices utils datasets methods base

other attached packages:
[1] forcats_0.4.0 stringr_1.4.0 dplyr_0.8.3 purrr_0.3.2 readr_1.3.1 tidyr_1.0.0 tibble_2.1.3 ggplot2_3.2.0
[9] tidyverse_1.2.1

loaded via a namespace (and not attached):
[1] zip_2.0.4 Rcpp_1.0.1 cellranger_1.1.0 pillar_1.4.2 compiler_3.6.0 tools_3.6.0 packrat_0.5.0
[8] zeallot_0.1.0 lubridate_1.7.4 jsonlite_1.6 lifecycle_0.1.0 nlme_3.1-139 gtable_0.3.0 lattice_0.20-38
[15] pkgconfig_2.0.2 rlang_0.4.1 openxlsx_4.1.3 cli_1.1.0 rstudioapi_0.10 haven_2.1.1 withr_2.1.2
[22] xml2_1.2.0 httr_1.4.1 generics_0.0.2 vctrs_0.2.0 hms_0.4.2 grid_3.6.0 tidyselect_0.2.5
[29] glue_1.3.1 R6_2.4.0 readxl_1.3.1 modelr_0.1.4 magrittr_1.5 backports_1.1.4 scales_1.0.0
[36] rvest_0.3.5 assertthat_0.2.1 colorspace_1.4-1 stringi_1.4.3 lazyeval_0.2.2 munsell_0.5.0 broom_0.5.2
[43] crayon_1.3.4

packageVersion("openxlsx")
[1] ‘4.1.3’

hope this helps! have a nice day!