1

Been using R for around two years now and this is the first time i've encountered this issue. I have a data.frame with approx 31,000 rows and 400 cols and I want to export to an excel sheet using write_xlsx

When I try this I get the following error:

write_xlsx(data, "FILEPATH")

[ERROR][libxlsxwriter/packager.c:1142]: Error in writing member in the zipfile

[ERROR] workbook_close(): Zip ZIP_ERRNO error while creating xlsx file 'FILEPATH'. System error = No error

Error: Error in libxlsxwriter: 'Zip generic error ZIP_ERRNO while creating the xlsx file.'

I think this error started when I inadvertently tried to open an Excel file that R was writing before it had actually completed. I have shut down R and restarted and also re-installed the writexl package to no avail.

The data i'm attempting to export is approx 150mb. When I try to write a smaller file (1.5mb) this works but when I try to open the excel file it says it is locked for editing by R and can open be opened 'read only'.

I have exported much larger data than this in the past with no issues so cannot figure this out, any help appreciated.

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
pdunbar
  • 33
  • 1
  • 5
  • Why do not use this : ` write.xlsx(write_xlsx(data, "FILEPATH.xlsx") – Kian Apr 15 '21 at 10:52
  • When I try the above it responds "could not find function "write.xlsx". I also tried write_xlsx(write_xlsx(data, "FILEPATH.xlsx")) but get the same error. Maybe i'm missing something but don't see how nesting it like this can work? – pdunbar Apr 15 '21 at 13:07
  • did you use `library(xlsx)`? you must run library(xlsx) then ` write.xlsx(write_xlsx(data, "FILEPATH.xlsx")` – Kian Apr 16 '21 at 01:55
  • 1
    What OS are you using and is it possible that you are running out of free space in the tmp directory? – jmcnamara Apr 19 '21 at 22:09

2 Answers2

1

What OS are you using and is it possible that you are running out of free space in the tmp directory? – jmcnamara yesterday

I think you've cracked it. I had returned to work on Monday, ran the script again and, sure enough, it worked first time. Only difference is I had deleted some content from the directory I was working in because I had got a disk full notice!

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
pdunbar
  • 33
  • 1
  • 5
0

I don't know write_xlsx but I would suggest a simple alternative as the following:

library(rio)
library(xlsx)
export(data, "exportname.xlsx")

However in your code might you need to add the file name as the following:

library(writexl)
write_xlsx(data, "FILEPATH// file name.xlsx")

Might this source could be helpful: https://datatofish.com/export-dataframe-to-excel-in-r/

LucaCoding
  • 65
  • 12