[R] Excel Export in a beauty way

[Marc Schwartz]

Hi,

I would just add, for myself, not for others who have developed R to  
Excel export packages, that the efficient export of data from R to a  
native Excel file is the priority. Formatting (columns, fonts, colors,  
etc.) in the resultant spreadsheets (for me) was not.

There are also some functional restrictions relative to what can be  
done in the process of creating the Excel file, where Excel is not  
itself running or available.

For example, using the Perl package that I do for the WriteXLS  
package, it is not possible to use the "AutoFit" capability to easily  
set all column widths wide enough to visually allow for the data  
contained within each. From the available Perl package documentation,  
this appears to be a "run time" only feature, which means that it  
would require Excel to be available and running and the user either  
going through the column formatting menu, or creating a macro to  
automate the process.

An alternative that I see would be to calculate, on a column-by-column  
basis, for each individual worksheet created, the maximum character  
length of all cell entries in the column and then set each column  
width individually. That would appear to work, but only in the case  
where the default font (Arial 10 without any formatting such as bold,  
italic, etc.) is being used. The challenge of course is that Arial is  
a variable width font, which means that this approach would be  
inexact. However, this may be something that I can look into adding in  
a future version, perhaps with some appropriate 'padding' in the  
column width setting that would result in minimal wasted space.

HTH,

Marc Schwartz


On Jun 3, 2009, at 7:54 AM, Erich Neuwirth wrote:

> If you are working on Windows and Excel installed on the same  
> machine as R,
> the rcom library by Thomas Baier gives you full programmability for  
> Excel From R.
>
> You can control all the effect you want.
>
> You also could use the RExcel addin for Excel
> which allows to transfer date from R into Excel with Excel menus.
> Then you can change everything you want in Excel manually or with  
> VBA macros.
>
>
> On Jun 3, 2009, at 2:29 PM, koj wrote:
>
>>
>> Hallo all,
>>
>> I`ve read a lot of things in this forum about an Excel export via  
>> R. It is
>> no problem to export my data frames via write.table or write.xls  
>> (xls or
>> csv), but some things are not very convenient for me: I always have  
>> to
>> adjust the column with to see all the numbers or the text and there  
>> is no
>> frame between the cells. And I missing the possibility to make some  
>> headers
>> bold or coloured. I`ve see the output from a Perl-Export and this  
>> is a very
>> nice thing, so my question: Is there really no possibility to  
>> produce a xls
>> or a xlsx - File with this nice features.
>>
>> Thank you very much in advance,
>>
>> Jens.
>> -- 
>> View this message in context: http://www.nabble.com/Excel-Export-in-a-beauty-way-tp23850532p23850532.html
>> Sent from the R help mailing list archive at Nabble.com.
>>
>> ______________________________________________
>> R-help at r-project.org mailing list
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
>>
>
> --
> Erich Neuwirth, University of Vienna
> Faculty of Computer Science
> Computer Supported Didactics Working Group
> Visit our SunSITE at http://sunsite.univie.ac.at
> Phone: +43-1-4277-39464 Fax: +43-1-4277-39459
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.