4

I have a 174603 rows and 178 column dataframe, which I'm importing to Excel using openxlsx::saveWorkbook, (Using this package to obtain the aforementioned format of cells, with colors, header styles and so on). But the process is extremely slow, (depending on the amount of memory used by the machine it can take from 7 to 17 minutes!!) and I need a way to reduce this significantly (Doesn't need to be seconds, but anything bellow 5 min would be OK)

I've already searched other questions but they all seem to focus either in exporting to R (I have no problem with this) or writing non-formatted files to R (using write.csv and other options of the like)

Apparently I can't use xlsx package because of the settings on my computer (industrial computer, Check comments on This question)

Any suggestions regarding packages or other functionalities inside this package to make this run faster would be highly appreciated.

Aaron Parrilla
  • 446
  • 2
  • 10
  • Do you really have a `data.frame` in R or is it already a `workbook`-object? If it is a `data.frame`, `write.xlsx` should be faster than `saveWorkbook` – Julian_Hn Jul 18 '19 at 07:14
  • 1
    @Juialn_Hn why do you think `write.xlsx` is faster than `saveWorkbook`? Afaik `write.xlsx` calls `saveWorkbook` and thus cannot be faster. – AEF Jul 18 '19 at 07:23
  • @Julian_Hn The user selects the file from a fileInput and I read it as data frame `file1 – Aaron Parrilla Jul 18 '19 at 07:48
  • @AEF: you are right. I had it remembered the other way around. – Julian_Hn Jul 18 '19 at 20:11

2 Answers2

3

This question has some time ,but I had the same problem as you and came up with a solution worth mentioning. There is package called writexl that has implemented a way to export a data frame to Excel using the C library libxlsxwriter. You can export to excel using the next code:

library(writexl)
writexl::write_xlsx(df, "Excel.xlsx",format_headers = TRUE)

The parameter format_headers only apply centered and bold titles, but I had edited the C code of the its source in github writexl library made by ropensci. You can download it or clone it. Inside src folder you can edit write_xlsx.c file.

For example in the part that he is inserting the header format

//how to format headers (bold + center)

lxw_format * title = workbook_add_format(workbook);

format_set_bold(title);

format_set_align(title, LXW_ALIGN_CENTER);

you can add this lines to add background color to the header

format_set_pattern (title, LXW_PATTERN_SOLID);

format_set_bg_color(title, 0x8DC4E4);

There are lots of formating you can do searching in the libxlsxwriter library

When you have finished editing that file and given you have the source code in a folder called writexl, you can build and install the edited package by

shell("R CMD build writexl")
install.packages("writexl_1.2.tar.gz", repos = NULL)

Exporting again using the first chunk of code will generate the Excel with formats and faster than any other library I know about.

Hope this helps.

Community
  • 1
  • 1
Sebastián Alcívar
  • 63
  • 1
  • 8
-1

Have you tried ;

write.table(GroupsAlldata, file = 'Groupsalldata.txt')

in order to obtain it in txt format.
Then on Excel, you can simply transfer you can 'text to column' to put your data into a table good luck

Mohammed Ahmed
  • 59
  • 1
  • 1
  • 7