[R] WriteXLS problem

[Marc Schwartz]

Hi,

Apologies for coming to this thread late, but I just got the posts early this morning, so there was a delay in receipt for some reason.

As per the other comments, there is a limit in Excel 2003 of 64k rows and as far as I can tell from MS' own specs, that has not changed:

  http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx

Another comment is that your initial syntax for using WriteXLS() was incorrect, as David noted. You need to pass the name of the data frame object as a quoted character vector, not the object itself. So it should have been:

  WriteXLS("todo2009", "todo2009.xls")

If you are only exporting one large data frame to Excel and 'xlsx' is not efficient enough in writing an Excel 2007 file, you will need to use ?write.csv. As you suspected in your initial post, you can then import the CSV file into OpenOffice or similar and save it as an Excel 2007 format file, which supports a larger number of rows. With Excel 2007, the row limit was increased, as noted, to 1,048,576 rows:

  http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx

and that has not changed with Excel 2010:

  http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx

Presumably, a real database application becomes a preferred storage option if you need to exceed those limits.

dataframes2xls, which writes Excel 2003 format files, will have the same limitations as WriteXLS, since the limitation is on the Excel side of the process as was noted in the information that David provided below.

HTH,

Marc Schwartz


On Sep 7, 2010, at 1:45 AM, Kenneth Roy Cabrera Torres wrote:

> Dears Dejian and David:
> 
> Thank you for your help.
> 
> Maybe dataframes2xls package have the same problem.
> 
> But "xlsx" take too much time to write it down.
> 
> Kenneth.
> 
> El lun, 06-09-2010 a las 20:56 -0400, David Winsemius escribió:
>> On Sep 6, 2010, at 8:09 PM, Dejian Zhao wrote:
>> 
>>> The maximum number of rows in excel 2003 or below is 65535, less  
>>> than your number of rows, so if you export your data into "xls"  
>>> files, probably you cannot see all your data in excel. Exel 2007 can  
>>> hold as many as 1048575 lines, thus "xlsx" file is a better choice.
>> 
>> The maximum number of rows in more editions of Excel.2003 was  
>> increased to a million. You may be correct about the Perl module that  
>> underlies WriteXLS, however. Here is an extract from the CPAN page for  
>> that module:
>> 
>> http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel.pm#LIMITATIONS
>> 
>> LIMITATIONS
>> The following limits are imposed by Excel:
>> 
>> Description Limit ----------------------------------- ------
>>  Maximum number of chars in a string 32767
>>  Maximum number of columns 256
>>  Maximum number of rows 65536
>>  Maximum chars in a sheet name 31
>>  Maximum chars in a header/footer 254
>> The minimum file size is 6K due to the OLE overhead. The maximum file  
>> size is approximately 7MB (7087104 bytes) of BIFF data. This can be  
>> extended by installing Takanori Kawai's OLE::Storage_Lite module http://search.cpan.org/search?dist=OLE-Storage_Lite 
>>  see the bigfile.pl example in the examples directory of the distro.