ropensci / writexl Goto Github PK

View Code? Open in Web Editor NEW

207.0 16.0 17.0 748 KB

Portable, light-weight data frame to xlsx exporter for R

Home Page: https://docs.ropensci.org/writexl

License: Other

R 0.45% C 99.55%

libxlsxwriter r xlsx excel rstats r-package

writexl's Introduction

writexl

Portable, light-weight data frame to xlsx exporter based on libxlsxwriter. No Java or Excel required.

Wraps the libxlsxwriter library to create files in Microsoft Excel 'xlsx' format.

Installation

install.packages("writexl")

Getting started

Currently the package only has write_xlsx() to export a data frame to xlsx.

library(writexl)
library(readxl)
tmp <- writexl::write_xlsx(iris)
readxl::read_xlsx(tmp)

# A tibble: 150 x 5
   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
          <dbl>       <dbl>        <dbl>       <dbl>   <chr>
 1          5.1         3.5          1.4         0.2  setosa
 2          4.9         3.0          1.4         0.2  setosa
 3          4.7         3.2          1.3         0.2  setosa
 4          4.6         3.1          1.5         0.2  setosa
 5          5.0         3.6          1.4         0.2  setosa
 6          5.4         3.9          1.7         0.4  setosa
 7          4.6         3.4          1.4         0.3  setosa
 8          5.0         3.4          1.5         0.2  setosa
 9          4.4         2.9          1.4         0.2  setosa
10          4.9         3.1          1.5         0.1  setosa
# ... with 140 more rows

Most data types should roundtrip with readxl:

library(nycflights13)
out <- readxl::read_xlsx(writexl::write_xlsx(flights))
all.equal(out, flights)
## TRUE

Performance is a bit better than openxlsx implementation:

library(microbenchmark)
library(nycflights13)
microbenchmark(
  writexl = writexl::write_xlsx(flights, tempfile()),
  openxlsx = openxlsx::write.xlsx(flights, tempfile()),
  times = 5
)
## Unit: seconds
##      expr       min        lq      mean    median        uq       max neval
##   writexl  8.884712  8.904431  9.103419  8.965643  9.041565  9.720743     5
##  openxlsx 17.166818 18.072527 19.171003 18.669805 18.756661 23.189206     5

Also the output xlsx files are smaller:

writexl::write_xlsx(flights, tmp1 <- tempfile())
file.info(tmp1)$size
## 29157282

openxlsx::write.xlsx(flights, tmp2 <- tempfile())
file.info(tmp2)$size
## 35962067

writexl's People

Contributors

Stargazers

Watchers

Forkers

renkun-ken zhaoxiaohe applied-statistic-using-r skyformat99 zeehio onesandzeroes minghao2016 drag05 jimsforks kraaijenbrink ehrlichd mkim0710 rix133 yuge tobwen mjdayer sondalex

writexl's Issues

C++ API

If we want to switch to Rcpp at some point, this cpp wrapper might be helpful.

Feature: write a list of data frames

I really like the openxlsx feature when you use a list of data frame as argument:

openxlsx::write.xlsx(list(ir = iris, cars = mtcars), "asdf.xlsx")

This code creates a workbook with 2 sheets with the respective names (ir and cars).

Thanks again for all your work!

Is it possible to have an option to keep NA values in the resulting Excel spreadsheet? Currently, Excel displays NAs as empty cells. Not sure if this is a problem with Excel itself (or depends on version), but if it is please close the issue.

Exposing some formatting options...

I regularly use openxlsx to generate spreadsheets for colleagues. These spreadsheets make use of such formatting options as: freeze panes, autofiltering, auto or manual column widths, and formatted dates and hyperlinks.

Would love to see these options in writexl.

For example...

library(openxlsx)
options("openxlsx.dateFormat" = "yyyy-mm-dd")

df <- data.frame(url = rep("https://ropensci.org/blog/technotes/2017/09/08/writexl-release", 5),
                 date = seq.Date(from = as.Date("2017-09-08"), by = 1, length.out = 5),
                 topic = paste("writexl package announced", 0:4, "days ago!"),
                 stringsAsFactors = FALSE)

class(df$url) <- "hyperlink"
wb <- createWorkbook()
urls <- which(names(df) == "url")
addWorksheet(wb, "test worksheet")

# Manual column widths
col_widths <- c(11, 10, 36)
setColWidths(wb, 1, cols = seq_along(df), widths = col_widths)

# Freeze panes
freezePane(wb, 1, firstRow = TRUE)

# Write and add autofilter
writeData(wb, 1, df, withFilter = TRUE)

# Change hyperlink display text
writeData(wb, sheet = 1, x = rep("this is a link", nrow(df)),
          startRow = 2, startCol = urls)

# Save output
saveWorkbook(wb, "C:/temp/test.xlsx")

Minor speedup opportunity in write_xlsx()

  if(any(nchar(names(x)) > 31)){
    warning("Truncating sheet name(s) to 31 characters")
    names(x) <- substring(names(x), 1, 29)
  }
  nm <- names(x)
  if(length(unique(nm)) <  length(nm)){
    warning("Deduplicating sheet names")
    names(x) <- make.unique(substring(names(x), 1, 28), sep = "_")
  }

Could be changed to:

  nm <- names(x)
  if(any(nchar(nm) > 31)){
    warning("Truncating sheet name(s) to 31 characters")
    names(x) <- substring(nm, 1, 29)
    nm <- names(x)
  }
  if(anyDuplicated(nm) != 0L){
    warning("Deduplicating sheet names")
    names(x) <- make.unique(substring(nm, 1, 28), sep = "_")
  }

but I didn't know how to (nor if I should) submit a pull request.

Return original data frame instead of file path

In readr::write_csv(), the function returns the data frame that was entered. This is useful if you are using the magrittr/dplyr pipe %>% to manipulate the data, and would like to save the data frame at an intermediary step (i.e. you write the csv file, and continue with the pipe operator to make more changes).

Currently in writexl::write_xlsx(), the function returns the full file path instead of the data frame. Is there a good reasons we shouldn't return the data frame instead, to yield the same advantages?

setwd("C:/rdata")
library(readr)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(writexl)

csv <- iris %>% 
  write_csv("iris.csv")

#csv is the data frame iris
head(csv)
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#> 1          5.1         3.5          1.4         0.2  setosa
#> 2          4.9         3.0          1.4         0.2  setosa
#> 3          4.7         3.2          1.3         0.2  setosa
#> 4          4.6         3.1          1.5         0.2  setosa
#> 5          5.0         3.6          1.4         0.2  setosa
#> 6          5.4         3.9          1.7         0.4  setosa

xlsx <- iris %>% 
  write_xlsx(path = "iris.xlsx")

#xlsx is the full file path  
xlsx
#> [1] "C:\\rdata\\iris.xlsx"

^{Created on 2018-09-21 by the reprex package (v0.2.1)}

Never ever remove Clippy!

Format of column names when using write_xlsx()

Hello,

when using write_xlsx(), the column names in Excel are formatted, in bold and centered. This generates issue when we want to import the Excel file into one of software applications. Wouldn't it be possible not to format this line? Or provide this option? Thanks!

Some characters cause invalid xlsx files

Some non-standard characters can cause invalid xlsx files.

(sorry no example at the moment - tk)

This can be avoided by changing the columns using iconv( to="UTF-8"). You may want to:

identify and escape offending characters
iconv (optionally) of character columns
document the behaviour

Expected encoding of libxlswriter

Lookup what encoding libxlsxwriter expects for strings.

Is it possible to add a row_names parameter?

I'd like to be able to read in a data frame's row names. I see col_names, but it would also be helpful to have row_names.

Apostrophe caused XML problems in workbook

Hello,
Not sure if this is a real issue, or how best to report it. A stray comma in an imported CSV caused problems when exported using write_xlsx. When I opened the xlsx file it ran a repair job; here is the log:

The repair scrubbed all other records on the problematic sheets in the workbook.

I simply removed the apostrophe from the imported data, so not a big problem, but I thought I should report it.
Thanks,
Anton

Generate file size is larger than the one with saved with Excel

writexl::write_xlsx seems to generate not optimized xlsx file from size point of view.

For example, it generated 90MB file size xlsx file but if I opened up the file in Excel and resaved the file without changing anything the file size went down to 4MB.

Feature request: protect worksheets

Is a protect-worksheet-function planned? That seems to be possible with the libxlsxwriter library:

https://libxlsxwriter.github.io/worksheet_8h.html#a1b49e135d4debcdb25941f2f40f04cb0

Thanks again for your great work.

No warning/error exporting to a directory that doesn't exist or to a read-only existing file

Not sure if this is an issue across operating systems, however, with Windows (10), if you use writexl::write_xlsx to create a file in a directory that doesn't exist, no warning or error is thrown. Also if you attempt to overwrite a read-only file, no error or warning is thrown.

This deviates from quite a few other packages, such as;

Base write.csv
data.table::fwrite
haven::write_sav.

Not saying that all package behavior should align, but to me it would be ideal if a warning/ error was thrown.

Thanks for such a useful package!

newline characters get converted to hex strings

When trying to write strings that contain newline symbols,
they get translated to an hex representation.

For example:

> v1 <- data.frame(CONFIGITEM_NUMBER = 1161014163, Note = "Nuevo disco SSD\r\n\r\nMon")
> v1
  CONFIGITEM_NUMBER                       Note
1        1161014163 Nuevo disco SSD\r\n\r\nMon
> write_xlsx(v1, "file.xlsx")

The carriage return symbol gets converted to _x000D_.
And the newline symbol gets converted to a blank space.

CONFIGITEM_NUMBER	Note
1161014163	Nuevo disco SSD_x000D_ _x000D_ Mon

I'm not sure if this is caused by libxlsxwriter or by writexl.

Is it possible to release 32 bit version on CRAN?

Good day all,

My company's existing server only only the installation of 32 bit packages and I was wondering if it is possible for the developers to kindly release a 32 bit version on CRAN since there are only 64 bit available?

worksheet name

Is it possible to specify the worksheet name with write_xlsx? Currently, it just names the sheet as "Sheet1".

Empty string - remove space

For character column types that also have NA values, NA values show with a space (" ") in the excel output and aren't empty. Original error was addressed in libxlsxwriter here: jmcnamara/libxlsxwriter#121

This space can be removed now in this line of code

writexl/src/write_xlsx.c

Lines 157 to 158 in 5ef8247

 else // xlsx does string not supported it seems? 

 assert_lxw(worksheet_write_string(sheet, cursor, j, " ", NULL));

Please remove clippy

Please reconsider whether clippy adds anything to this otherwise excellent package

Create a site with `pkgdown` and GitPages

Please, create a website for this package by using pkgdown package (the process is automatic) and publish it with GitPages.

Issue in write_xlsx?

When writing out a lot of xlsx files, writexl::write_xlsx will eventually fail and the R session will become unusable.

Code used to produce the error:

install.packages("writexl")

# package ‘writexl’ successfully unpacked and MD5 sums checked
# The downloaded binary packages are in  C:\Users\XXXXXX\downloaded_packages

for(i in 1:1000) {
    print(i)
    writexl::write_xlsx(iris, "c:/temmp/asdsad.xlsx")
}

For me it fails after 510 write outs. The same issue occurs when you attempt to write a lot of worksheets as nested data.frames in a list (similar amount: ~500-600).

Error:

Error: Error in writexl: failed to create workbook

I'm on Windows 10, with 32 gig of RAM.

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_Australia.1252  LC_CTYPE=English_Australia.1252   
[3] LC_MONETARY=English_Australia.1252 LC_NUMERIC=C                      
[5] LC_TIME=English_Australia.1252    

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

loaded via a namespace (and not attached):
[1] compiler_3.6.0

Once I force the error, I can't access anything:

sessionInfo()
Error in gzfile(file, "rb") : cannot open the connection
In addition: Warning message:
In gzfile(file, "rb") :
  cannot open compressed file 'C:/Program Files/R/R-3.6.0/library/stats/Meta/package.rds', probable reason 'Too many open files'

Task manager doesn't indicate lack of RAM or CPU or DISK:

I have access to RStudio Server (running Red Hat with 100gig of RAM and ~16 cores). I can't re-produce the error even when looping a million times. Perhaps a Windows issue?

Let me know if you require any further info.

write_xlsx fails silently if no path argument supplied

write_xlsx(yyy) does nothing, gives no error.

write_xlsx(yyy, "yyy.xlsx") works as expected: I end up with a spreadsheet named yyy.xlsx with the object yyy as its data.

If the path argument is required, please throw an error when it is not supplied.

I think it would be better if you made path optional and used the object name supplied for the x argument as the filename. For example, if I run write_xlsx(yyy), then the result would be a spreadsheet named yyy.xlsx with the object yyy as its data.

libxlsxwriter compilation error

When I try to install writexl in R 3.5.2, I get a compilation error. I noticed that another issue (#21) describes problems with compilation that may have been related to compilation flags, but with no clear resolution. My compilation error is not the same as in that issue, however.

trying URL 'https://cran.r-project.org/src/contrib/writexl_1.1.tar.gz'
Content type 'application/x-gzip' length 209069 bytes (204 KB)
==================================================
downloaded 204 KB

* installing *source* package ‘writexl’ ...
** package ‘writexl’ successfully unpacked and MD5 sums checked
** libs
rm -f writexl.so libxlsxwriter/libstatxlsxwriter.a write_xlsx.o libxlsxwriter/app.o libxlsxwriter/format.o libxlsxwriter/theme.o libxlsxwriter/chart.o libxlsxwriter/hash_table.o libxlsxwriter/utility.o libxlsxwriter/content_types.o libxlsxwriter/packager.o libxlsxwriter/workbook.o libxlsxwriter/core.o libxlsxwriter/relationships.o libxlsxwriter/worksheet.o libxlsxwriter/custom.o libxlsxwriter/shared_strings.o libxlsxwriter/xmlwriter.o libxlsxwriter/drawing.o libxlsxwriter/styles.o tmpfileplus/tmpfileplus.o libxlsxwriter/chartsheet.o minizip/ioapi.o minizip/zip.o
/usr/bin/gcc -I"/home/viking/.Renv/versions/3.5.2/lib64/R/include" -DNDEBUG -Iinclude -DNOCRYPT -DNOUNCRYPT  -I'/home/viking/.Renv/versions/3.5.2/include'   -fvisibility=hidden -fpic  -g -O2  -c write_xlsx.c -o write_xlsx.o
/usr/bin/gcc -I"/home/viking/.Renv/versions/3.5.2/lib64/R/include" -DNDEBUG -Iinclude -DNOCRYPT -DNOUNCRYPT  -I'/home/viking/.Renv/versions/3.5.2/include'   -fvisibility=hidden -fpic  -g -O2  -c libxlsxwriter/app.c -o libxlsxwriter/app.o
/usr/bin/gcc -I"/home/viking/.Renv/versions/3.5.2/lib64/R/include" -DNDEBUG -Iinclude -DNOCRYPT -DNOUNCRYPT  -I'/home/viking/.Renv/versions/3.5.2/include'   -fvisibility=hidden -fpic  -g -O2  -c libxlsxwriter/format.c -o libxlsxwriter/format.o
/usr/bin/gcc -I"/home/viking/.Renv/versions/3.5.2/lib64/R/include" -DNDEBUG -Iinclude -DNOCRYPT -DNOUNCRYPT  -I'/home/viking/.Renv/versions/3.5.2/include'   -fvisibility=hidden -fpic  -g -O2  -c libxlsxwriter/theme.c -o libxlsxwriter/theme.o
/usr/bin/gcc -I"/home/viking/.Renv/versions/3.5.2/lib64/R/include" -DNDEBUG -Iinclude -DNOCRYPT -DNOUNCRYPT  -I'/home/viking/.Renv/versions/3.5.2/include'   -fvisibility=hidden -fpic  -g -O2  -c libxlsxwriter/chart.c -o libxlsxwriter/chart.o
/usr/bin/gcc -I"/home/viking/.Renv/versions/3.5.2/lib64/R/include" -DNDEBUG -Iinclude -DNOCRYPT -DNOUNCRYPT  -I'/home/viking/.Renv/versions/3.5.2/include'   -fvisibility=hidden -fpic  -g -O2  -c libxlsxwriter/hash_table.c -o libxlsxwriter/hash_table.o
/usr/bin/gcc -I"/home/viking/.Renv/versions/3.5.2/lib64/R/include" -DNDEBUG -Iinclude -DNOCRYPT -DNOUNCRYPT  -I'/home/viking/.Renv/versions/3.5.2/include'   -fvisibility=hidden -fpic  -g -O2  -c libxlsxwriter/utility.c -o libxlsxwriter/utility.o
/usr/bin/gcc -I"/home/viking/.Renv/versions/3.5.2/lib64/R/include" -DNDEBUG -Iinclude -DNOCRYPT -DNOUNCRYPT  -I'/home/viking/.Renv/versions/3.5.2/include'   -fvisibility=hidden -fpic  -g -O2  -c libxlsxwriter/content_types.c -o libxlsxwriter/content_types.o
/usr/bin/gcc -I"/home/viking/.Renv/versions/3.5.2/lib64/R/include" -DNDEBUG -Iinclude -DNOCRYPT -DNOUNCRYPT  -I'/home/viking/.Renv/versions/3.5.2/include'   -fvisibility=hidden -fpic  -g -O2  -c libxlsxwriter/packager.c -o libxlsxwriter/packager.o
In file included from include/xlsxwriter/third_party/zip.h:59:0,
                 from include/xlsxwriter/packager.h:17,
                 from libxlsxwriter/packager.c:11:
include/xlsxwriter/third_party/ioapi.h:141:51: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 typedef voidpf   (ZCALLBACK *open_file_func)      OF((voidpf opaque, const char* filename, int mode));
                                                   ^~
include/xlsxwriter/third_party/ioapi.h:142:51: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 typedef uLong    (ZCALLBACK *read_file_func)      OF((voidpf opaque, voidpf stream, void* buf, uLong size));
                                                   ^~
include/xlsxwriter/third_party/ioapi.h:143:51: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 typedef uLong    (ZCALLBACK *write_file_func)     OF((voidpf opaque, voidpf stream, const void* buf, uLong size));
                                                   ^~
include/xlsxwriter/third_party/ioapi.h:144:51: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 typedef int      (ZCALLBACK *close_file_func)     OF((voidpf opaque, voidpf stream));
                                                   ^~
include/xlsxwriter/third_party/ioapi.h:145:51: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 typedef int      (ZCALLBACK *testerror_file_func) OF((voidpf opaque, voidpf stream));
                                                   ^~
include/xlsxwriter/third_party/ioapi.h:147:51: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 typedef long     (ZCALLBACK *tell_file_func)      OF((voidpf opaque, voidpf stream));
                                                   ^~
include/xlsxwriter/third_party/ioapi.h:148:51: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 typedef long     (ZCALLBACK *seek_file_func)      OF((voidpf opaque, voidpf stream, uLong offset, int origin));
                                                   ^~
include/xlsxwriter/third_party/ioapi.h:154:5: error: unknown type name ‘open_file_func’
     open_file_func      zopen_file;
     ^~~~~~~~~~~~~~
include/xlsxwriter/third_party/ioapi.h:155:5: error: unknown type name ‘read_file_func’
     read_file_func      zread_file;
     ^~~~~~~~~~~~~~
include/xlsxwriter/third_party/ioapi.h:156:5: error: unknown type name ‘write_file_func’
     write_file_func     zwrite_file;
     ^~~~~~~~~~~~~~~
include/xlsxwriter/third_party/ioapi.h:157:5: error: unknown type name ‘tell_file_func’
     tell_file_func      ztell_file;
     ^~~~~~~~~~~~~~
include/xlsxwriter/third_party/ioapi.h:158:5: error: unknown type name ‘seek_file_func’
     seek_file_func      zseek_file;
     ^~~~~~~~~~~~~~
include/xlsxwriter/third_party/ioapi.h:159:5: error: unknown type name ‘close_file_func’
     close_file_func     zclose_file;
     ^~~~~~~~~~~~~~~
include/xlsxwriter/third_party/ioapi.h:160:5: error: unknown type name ‘testerror_file_func’
     testerror_file_func zerror_file;
     ^~~~~~~~~~~~~~~~~~~
include/xlsxwriter/third_party/ioapi.h:164:51: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 typedef ZPOS64_T (ZCALLBACK *tell64_file_func)    OF((voidpf opaque, voidpf stream));
                                                   ^~
include/xlsxwriter/third_party/ioapi.h:165:51: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 typedef long     (ZCALLBACK *seek64_file_func)    OF((voidpf opaque, voidpf stream, ZPOS64_T offset, int origin));
                                                   ^~
include/xlsxwriter/third_party/ioapi.h:166:51: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 typedef voidpf   (ZCALLBACK *open64_file_func)    OF((voidpf opaque, const void* filename, int mode));
                                                   ^~
include/xlsxwriter/third_party/ioapi.h:170:5: error: unknown type name ‘open64_file_func’
     open64_file_func    zopen64_file;
     ^~~~~~~~~~~~~~~~
include/xlsxwriter/third_party/ioapi.h:171:5: error: unknown type name ‘read_file_func’
     read_file_func      zread_file;
     ^~~~~~~~~~~~~~
include/xlsxwriter/third_party/ioapi.h:172:5: error: unknown type name ‘write_file_func’
     write_file_func     zwrite_file;
     ^~~~~~~~~~~~~~~
include/xlsxwriter/third_party/ioapi.h:173:5: error: unknown type name ‘tell64_file_func’
     tell64_file_func    ztell64_file;
     ^~~~~~~~~~~~~~~~
include/xlsxwriter/third_party/ioapi.h:174:5: error: unknown type name ‘seek64_file_func’
     seek64_file_func    zseek64_file;
     ^~~~~~~~~~~~~~~~
include/xlsxwriter/third_party/ioapi.h:175:5: error: unknown type name ‘close_file_func’
     close_file_func     zclose_file;
     ^~~~~~~~~~~~~~~
include/xlsxwriter/third_party/ioapi.h:176:5: error: unknown type name ‘testerror_file_func’
     testerror_file_func zerror_file;
     ^~~~~~~~~~~~~~~~~~~
include/xlsxwriter/third_party/ioapi.h:180:28: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 void fill_fopen64_filefunc OF((zlib_filefunc64_def* pzlib_filefunc_def));
                            ^~
include/xlsxwriter/third_party/ioapi.h:181:26: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 void fill_fopen_filefunc OF((zlib_filefunc_def* pzlib_filefunc_def));
                          ^~
include/xlsxwriter/third_party/ioapi.h:187:5: error: unknown type name ‘open_file_func’
     open_file_func      zopen32_file;
     ^~~~~~~~~~~~~~
include/xlsxwriter/third_party/ioapi.h:188:5: error: unknown type name ‘tell_file_func’
     tell_file_func      ztell32_file;
     ^~~~~~~~~~~~~~
include/xlsxwriter/third_party/ioapi.h:189:5: error: unknown type name ‘seek_file_func’
     seek_file_func      zseek32_file;
     ^~~~~~~~~~~~~~
include/xlsxwriter/third_party/ioapi.h:200:21: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 voidpf call_zopen64 OF((const zlib_filefunc64_32_def* pfilefunc,const void*filename,int mode));
                     ^~
include/xlsxwriter/third_party/ioapi.h:201:22: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 long    call_zseek64 OF((const zlib_filefunc64_32_def* pfilefunc,voidpf filestream, ZPOS64_T offset, int origin));
                      ^~
include/xlsxwriter/third_party/ioapi.h:202:23: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 ZPOS64_T call_ztell64 OF((const zlib_filefunc64_32_def* pfilefunc,voidpf filestream));
                       ^~
In file included from include/xlsxwriter/packager.h:17:0,
                 from libxlsxwriter/packager.c:11:
include/xlsxwriter/third_party/zip.h:129:32: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern zipFile ZEXPORT zipOpen OF((const char *pathname, int append));
                                ^~
include/xlsxwriter/third_party/zip.h:130:34: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern zipFile ZEXPORT zipOpen64 OF((const void *pathname, int append));
                                  ^~
include/xlsxwriter/third_party/zip.h:150:33: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern zipFile ZEXPORT zipOpen2 OF((const char *pathname,
                                 ^~
include/xlsxwriter/third_party/zip.h:155:36: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern zipFile ZEXPORT zipOpen2_64 OF((const void *pathname,
                                    ^~
include/xlsxwriter/third_party/zip.h:160:40: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern int ZEXPORT zipOpenNewFileInZip OF((zipFile file,
                                        ^~
include/xlsxwriter/third_party/zip.h:171:42: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern int ZEXPORT zipOpenNewFileInZip64 OF((zipFile file,
                                          ^~
include/xlsxwriter/third_party/zip.h:200:41: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern int ZEXPORT zipOpenNewFileInZip2 OF((zipFile file,
                                         ^~
include/xlsxwriter/third_party/zip.h:213:44: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern int ZEXPORT zipOpenNewFileInZip2_64 OF((zipFile file,
                                            ^~
include/xlsxwriter/third_party/zip.h:229:41: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern int ZEXPORT zipOpenNewFileInZip3 OF((zipFile file,
                                         ^~
include/xlsxwriter/third_party/zip.h:246:44: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern int ZEXPORT zipOpenNewFileInZip3_64 OF((zipFile file,
                                            ^~
include/xlsxwriter/third_party/zip.h:272:41: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern int ZEXPORT zipOpenNewFileInZip4 OF((zipFile file,
                                         ^~
include/xlsxwriter/third_party/zip.h:293:44: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern int ZEXPORT zipOpenNewFileInZip4_64 OF((zipFile file,
                                            ^~
include/xlsxwriter/third_party/zip.h:320:40: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern int ZEXPORT zipWriteInFileInZip OF((zipFile file,
                                        ^~
include/xlsxwriter/third_party/zip.h:327:38: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern int ZEXPORT zipCloseFileInZip OF((zipFile file));
                                      ^~
include/xlsxwriter/third_party/zip.h:332:41: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern int ZEXPORT zipCloseFileInZipRaw OF((zipFile file,
                                         ^~
include/xlsxwriter/third_party/zip.h:336:43: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern int ZEXPORT zipCloseFileInZipRaw64 OF((zipFile file,
                                           ^~
include/xlsxwriter/third_party/zip.h:346:29: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern int ZEXPORT zipClose OF((zipFile file,
                             ^~
include/xlsxwriter/third_party/zip.h:353:44: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘OF’
 extern int ZEXPORT zipRemoveExtraInfoBlock OF((char* pData, int* dataLen, short sHeader));
                                            ^~
libxlsxwriter/packager.c: In function ‘lxw_packager_new’:
libxlsxwriter/packager.c:108:25: warning: implicit declaration of function ‘zipOpen’; did you mean ‘popen’? [-Wimplicit-function-declaration]
     packager->zipfile = zipOpen(packager->filename, 0);
                         ^~~~~~~
                         popen
libxlsxwriter/packager.c:108:23: warning: assignment makes pointer from integer without a cast [-Wint-conversion]
     packager->zipfile = zipOpen(packager->filename, 0);
                       ^
libxlsxwriter/packager.c: In function ‘_add_file_to_zip’:
libxlsxwriter/packager.c:1021:13: warning: implicit declaration of function ‘zipOpenNewFileInZip4_64’ [-Wimplicit-function-declaration]
     error = zipOpenNewFileInZip4_64(self->zipfile,
             ^~~~~~~~~~~~~~~~~~~~~~~
libxlsxwriter/packager.c:1048:17: warning: implicit declaration of function ‘zipWriteInFileInZip’ [-Wimplicit-function-declaration]
         error = zipWriteInFileInZip(self->zipfile,
                 ^~~~~~~~~~~~~~~~~~~
libxlsxwriter/packager.c:1063:17: warning: implicit declaration of function ‘zipCloseFileInZip’ [-Wimplicit-function-declaration]
         error = zipCloseFileInZip(self->zipfile);
                 ^~~~~~~~~~~~~~~~~
libxlsxwriter/packager.c: In function ‘lxw_create_package’:
libxlsxwriter/packager.c:1177:17: warning: implicit declaration of function ‘zipClose’; did you mean ‘pclose’? [-Wimplicit-function-declaration]
     zip_error = zipClose(self->zipfile, NULL);
                 ^~~~~~~~
                 pclose
make: *** [/home/viking/.Renv/versions/3.5.2/lib64/R/etc/Makeconf:162: libxlsxwriter/packager.o] Error 1
ERROR: compilation failed for package ‘writexl’
* removing ‘/home/viking/.Renv/versions/3.5.2/lib64/R/library/writexl’

The downloaded source packages are in
        ‘/tmp/Rtmp7dBQJ8/downloaded_packages’
Updating HTML index of packages in '.Library'
Making 'packages.html' ... done
Warning message:
In install.packages("writexl", repos = "https://cran.r-project.org") :
  installation of package ‘writexl’ had non-zero exit status

row names disappear

Clippy does not display correctly with dark theme

I am really excited about the new clippy feature, however it does not display correctly when you run Rstudio with a dark theme :(

Clippy: really?!?

Please, PLEASE remove clippy from the man pages. It's really annoying and improves nothing.

Thanks! 😄

Consider improving error message when a named sheet name exceeds the max character length supported by Excel

When trying to pass a named list of dataframes into writexl::write_xlsx(), the error thrown is Error: Error in writexl: failed to create workbook.

It would be useful if the error message indicated that the sheet name should be shortened.

library(dplyr)

my_datasets <- list("my_really_really_verbose_sheet_name" = mtcars,
                    "my_short_sheet_name" = storms)

writexl::write_xlsx(x = my_datasets,
                    path = "foo.xlsx")
#> Error: Error in writexl: failed to create workbook

^{Created on 2020-10-29 by the reprex package (v0.3.0)}

S3: integer64s become zeros in Excel file

Here’s a reproducible example using the bit64 package to create an int64:

library(tidyverse)
library(bit64) # as.integer64
library(writexl)
library(readxl)
d <- tibble(a=pi, b=3L, c=as.integer64(3))
d
write_xlsx(d, "saveint64.xlsx")
read_xlsx("saveint64.xlsx")

"c" is written to the file as 1.482197E-323 (near zero) instead of the expected 3.

Please remove "clippy"

This is extremely annoying, and "limiting" the discussion is not in the least bit helpful.

Just get rid of it!

Can't understand clippy in the dark

failure to write list of data.frames

Can't write the following list of data frames (attached in a zipped rds file) into an excel file and NO idea why.

xxxx <- readRDS(unzip("failur.zip",files = "failur.rds"))
names(xxxx)
dim(xxxx[[1]])
writexl::write_xlsx(xxxx, path = "test.xlsx")

All I am getting is :
Error: Error in writexl: failed to create workbook

Help would be greatly appreciated.

Have nice day
Witek

PS. I am on Windows.

> packageVersion("readxl")
[1] ‘1.3.1’

> 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

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

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

failur.zip

Support export to specific worksheet

It would be awesome to have support for writing to a specific worksheet within a workbook. In rio, we currently do that using some complex openxlsx code that either adds a named worksheet or overwrites that sheet if it exists. Basically, current behavior is:

# export to first sheet in new workbook
rio::export(mtcars, "mtcars.xlsx")

# export to a named sheet in new or existing workbook
rio::export(mtcars, "mtcars.xlsx", which = "sheetname")

Would be awesome to switch over to writexl for that.

linux install problems

Hello,
Using 64 bit linux and Rstudio.
R is version 3.5
I get error when I try to install writexl
Any idea why am I getting this error:
...
tmpfileplus/tmpfileplus.c:239:6: warning: assignment to ‘FILE *’ {aka ‘struct IO_FILE *’} from ‘int’ makes pointer from integer without a cast [-Wint-conversion]
fp = FDOPEN(fd, "w+b");
^
tmpfileplus/tmpfileplus.c: In function ‘tmpfileplus’:
tmpfileplus/tmpfileplus.c:290:18: error: ‘P_tmpdir’ undeclared (first use in this function); did you mean ‘tmpdir’?
tempdirs[i++] = P_tmpdir;
^~~~~~~~
tmpdir
make: *** [/usr/lib64/R/etc/Makeconf:159: tmpfileplus/tmpfileplus.o] Error 1
ERROR: compilation failed for package ‘writexl’
...

Any help welcome.

Numerical Fields Written to File Incorrect

I wrote the following data.frame to an Excel file using write_xlsx, and the sum of each of the columns does not match if I sum the columns in R. The sum from the Excel file is correct up to 10 decimal places and after that it does not match, and looks like it is truncated after about 15.

d <- data.frame(x = rnorm(10000), y = rnorm(10000), z = rnorm(10000))

'xl_formula' fails when there are any NAs in otherwise valid vector

xl_formula checks that all values in the vector start with an '=' to ensure that every value is a valid formula. However, if there are any NAs in the vector, even if every non na value starts with '=', the function call fails. Manually setting the class of a column to "c('xl_formula', 'xl_object)" has the behavior that all NAs are blank in the excel output, which to me seems to be the desired behavior.

I think this could be fixed by adding 'na.rm = T' to the 'all' call that checks for '=' at the start of every value.

reprex below:

formula <- paste0('=A', seq_len(5))

writexl::xl_formula(formula)
#>  [:xl_formula:]
#> =A1
#> =A2
#> =A3
#> =A4
#> =A5

formula[1] <- NA

writexl::xl_formula(formula)
#> Error in writexl::xl_formula(formula): Formulas must start with '='

supply name of sheet to be written to

It wold be helpful to be able to name the sheet an output is written into (instead of having the default name "Sheet1".

Similar to openxlsx::write.xlsx(..., sheetName = "my_sheet_name")

Error in libxlsxwriter: 'String exceeds Excel's limit of 32,767 characters.'

Hi, thanks for your package.

I encontered a error when output a dataframe, the characters of some cells of which exceeds 32676.

The programm stopped.

Is there any optin to labed the exceeded cells, e.g. with "NA", but not stop the programm?

MRAN refuses to install writexl :(

My fault entirely I know - but I have begun working with MRAN - and now can't use this great package!

Package which is only available in source form, and may need compilation of C/C++/Fortran: ‘writexl’ These will not be installed

Anyone have any suggestions (I am rather stuck with MRAN for work reasons at the moment)

Cheers
zedleb

Microsoft R Open 3.4.1
The enhanced R distribution from Microsoft
Microsoft packages Copyright (C) 2017 Microsoft Corporation

Using the Intel MKL for parallel mathematical computing(using 2 cores).

Default CRAN mirror snapshot taken on 2017-09-01.
See: https://mran.microsoft.com/.

option to not coerce dates to strings

It would be really useful to be able to keep dates as dates that can be read by excel. xlsx files are not really a great way to store data, but they are a great way to feed data into excel. keeping dates as excel readable dates would make a lot of sense in a bunch of use cases.

btw for now I am using this as a workaround:

dplyr::mutate(df, date = as.numeric(date - as.Date(0, origin="1899-12-30", tz='UTC')))

where df is my dataframe and date is my column that contains dates.

Installation Error: Could not find tools necessary to compile a package

Here is the error message from my friend @XuliangSUFE, he is not fimilar with GitHub, so I help him open an issue.

8. stop("Could not find tools necessary to compile a package", call. = FALSE) 
7. check_build_tools(debug = debug) 
6. pkgbuild::local_build_tools(required = TRUE) 
5. install(source, dependencies = dependencies, upgrade = upgrade, force = force, quiet = quiet, build = build, build_opts = build_opts, repos = repos, type = type, ...) 
4. FUN(X[[i]], ...) 
3. vapply(remotes, install_remote, ..., FUN.VALUE = character(1)) 
2. install_remotes(remotes, auth_token = auth_token, host = host, dependencies = dependencies, upgrade = upgrade, force = force, quiet = quiet, build = build, build_opts = build_opts, repos = repos, type = type, ...) 
1. install_github("ropensci/writexl")

And the session information is here.

R version 3.6.0 (2019-04-26)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows >= 8 x64 (build 9200)

Matrix products: default

locale:
[1] LC_COLLATE=Chinese (Simplified)_China.936 
[2] LC_CTYPE=Chinese (Simplified)_China.936   
[3] LC_MONETARY=Chinese (Simplified)_China.936
[4] LC_NUMERIC=C                              
[5] LC_TIME=Chinese (Simplified)_China.936    

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

other attached packages:
 [1] readxl_1.3.1    forcats_0.4.0   stringr_1.4.0   dplyr_0.8.0.1  
 [5] purrr_0.3.2     readr_1.3.1     tidyr_0.8.3     tibble_2.1.1   
 [9] ggplot2_3.1.1   tidyverse_1.2.1 rio_0.5.16      usethis_1.5.0  
[13] devtools_2.0.2 
loaded via a namespace (and not attached):
 [1] Rcpp_1.0.1        lubridate_1.7.4   here_0.1          lattice_0.20-38  
 [5] prettyunits_1.0.2 ps_1.3.0          assertthat_0.2.1  rprojroot_1.3-2  
 [9] digest_0.6.18     R6_2.4.0          cellranger_1.1.0  plyr_1.8.4       
[13] backports_1.1.4   evaluate_0.13     httr_1.4.0        pillar_1.3.1     
[17] rlang_0.3.4       lazyeval_0.2.2    curl_3.3          rstudioapi_0.10  
[21] data.table_1.12.2 callr_3.2.0       rmarkdown_1.12    desc_1.2.0       
[25] foreign_0.8-71    munsell_0.5.0     broom_0.5.2       compiler_3.6.0   
[29] modelr_0.1.4      xfun_0.6          base64enc_0.1-3   pkgconfig_2.0.2  
[33] pkgbuild_1.0.3    htmltools_0.3.6   tidyselect_0.2.5  crayon_1.3.4     
[37] withr_2.1.2       grid_3.6.0        nlme_3.1-139      jsonlite_1.6     
[41] gtable_0.3.0      magrittr_1.5      scales_1.0.0      zip_2.0.1        
[45] cli_1.1.0         stringi_1.4.3     fs_1.3.0          remotes_2.0.4    
[49] xml2_1.2.0        generics_0.0.2    openxlsx_4.1.0    tools_3.6.0      
[53] glue_1.3.1        hms_0.4.2         yaml_2.2.0        processx_3.3.0   
[57] pkgload_1.0.2     colorspace_1.4-1  sessioninfo_1.1.1 rvest_0.3.3      
[61] memoise_1.1.0     knitr_1.22        haven_2.1.0

I am not sure whether the error happens from writexl or pkgbuild?

datatime column only save UTC timezone.

I change time zone in R object. but writexl function save to UTC timezone.

So, I use as.character before save but it's confuse.

Is this the intended behavior?

Unclear error message with sheet names > 31 characters

Is there a way to make the error message clearer?

sheets <- list(
  a234567890123456789012345678901 = data.frame(a = 1),
  a2345678901234567890123456789012 = data.frame(a = 1)
)

writexl::write_xlsx(sheets, tempfile())
#> Error: Error in writexl: failed to create workbook
writexl::write_xlsx(sheets[1], tempfile())
writexl::write_xlsx(sheets[2], tempfile())
#> Error: Error in writexl: failed to create workbook

^{Created on 2019-12-09 by the reprex package (v0.3.0)}

Data validation

Hi!

Thanks for this package! Very useful! We generate XLSX files for our users, where it would be useful to be able to activate the Data validation features of Excel to limit the possible inputs. In combination with #4 this would provide everything we could wish for :)

Thanks!
Best,
Bela

Support writing formulas

Would it be possible to support writing formulas, too? Maybe for R's formula type.

Support writing to .XLS

Hi,
Wondering if there is any scope in this project to support writing to xls format?
Thanks
Kyle

Multiple data frame in a single sheet

Is it possible to add the possibility of writing several data frames in the same sheet
(maybe with the possibility of separating these data frames by a few empty lines, or to give
some indication about where in the sheet the data frame should go)?

Thanks a lot

Error in libxlsxwriter : 'Worksheet row or column index out of range.'

When I write large .xlsx files (more than 1,048,576 rows), I got an error message saying that 'Worksheet row or column index out of range.' I tested my code on a remote server in CentOS 7.0 system as well as a win10 machine, but both give the same error. The "use_zip64" argument doesn't work either. I'm writing to ask if this is a problem with the data size and if it can be solved. I believe it might be a problem with the source C package "libxlsxwriter".

Error in closing member in the zipfile

Hello developers,
I received an error while writing a list of large data.frames to an output file.

> library(writexl)
> writexl::write_xlsx(
  x = list(
    Matrix_processed = mat_processed,
    Matrix_signal_intensities =  mu
  ),
  path = "GEO_submission/GA_illumina_submission_matrices.xlsx",
  col_names = TRUE,
  format_headers = TRUE
)
[ERROR][libxlsxwriter/packager.c:1151]: Error in closing member in the zipfile
[ERROR] workbook_close(): Zip ZIP_BADZIPFILE error while creating xlsx file 'GEO_submission/GA_illumina_submission_matrices.xlsx'. This may require the use_zip64 option for large files. System error = Success

Both mat_processed and mu dataframes are of ~2100 mb object.size.

Partially written output xlsx file is of the size 2GB. I am guessing this is the upper size limit for the function?

> devtools::session_info(pkgs = "writexl")
─ Session info ────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 setting  value                       
 version  R version 3.6.0 (2019-04-26)
 os       Pop!_OS 18.04 LTS           
 system   x86_64, linux-gnu           
 ui       RStudio                     
 language en_US:en                    
 collate  en_US.UTF-8                 
 ctype    en_US.UTF-8                 
 tz       Europe/Berlin               
 date     2020-03-12                  

─ Packages ────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 package * version date       lib source        
 writexl * 1.2     2019-11-27 [1] CRAN (R 3.6.0)

[1] /home/anand/R/x86_64-pc-linux-gnu-library/3.6
[2] /usr/local/lib/R/site-library
[3] /usr/lib/R/site-library
[4] /usr/lib/R/library

Add an append option

Would it be possible (I don't know the original libxlsxwriter library) to implement an "append" option to write the files?

The use case is that in many occasions we need to add rows to an existing file.

	else // xlsx does string not supported it seems?
	assert_lxw(worksheet_write_string(sheet, cursor, j, " ", NULL));