R help - Sep 2016 - Writing data onto xlsx file without cell formatting

Hi again,

I have been following above suggestion to export data from R to xlsx
file using XLconnect. However recently I am facing Java memory
allocation problem with large dataset (looks like a known issue with
this package) and therefore decided to move to using "xlsx" package.

Now I started facing that same problem of losing my existing formating
when I use xlsx package for data export. Can someone help me with some
pointer on how can I preserve the cell formating after exporting
data.frame to some existing xlsx file using "xlsx" package.

Thanks for your time.

On Mon, Jul 11, 2016 at 10:43 AM, Ismail SEZEN <sezenismail at gmail.com>
wrote:
> I think, this is what you are looking for:
>
>
http://stackoverflow.com/questions/11228942/write-from-r-into-template-in-excel-while-preserving-formatting
>
> On 11 Jul 2016, at 03:43, Christofer Bogaso <bogaso.christofer at
gmail.com>
> wrote:
>
> Hi again,
>
> I am trying to write a data frame to an existing Excel file (xlsx)
> from row 5 and column 6 of the 1st Sheet. I was going through a
> previous instruction which is available here :
>
>
http://stackoverflow.com/questions/32632137/using-write-xlsx-in-r-how-to-write-in-a-specific-row-or-column-in-excel-file
>
> However trouble is that it is modifying/removing formatting of all the
> affected cells. I have predefined formatting of those cells where data
> to be pasted, and I dont want to modify or remove that formatting.
>
> Any idea if I need to pass some additional argument.
>
> Appreciate your valuable feedback.
>
> Thanks,
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> 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.
>
>

I use the "openxlsx" package to handle spreadsheets.


Jim Holtman
Data Munger Guru

What is the problem that you are trying to solve?
Tell me what you want to do, not how you want to do it.

On Mon, Sep 26, 2016 at 5:56 PM, Christofer Bogaso <
bogaso.christofer at gmail.com> wrote:
> Hi again,
>
> I have been following above suggestion to export data from R to xlsx
> file using XLconnect. However recently I am facing Java memory
> allocation problem with large dataset (looks like a known issue with
> this package) and therefore decided to move to using "xlsx"
package.
>
> Now I started facing that same problem of losing my existing formating
> when I use xlsx package for data export. Can someone help me with some
> pointer on how can I preserve the cell formating after exporting
> data.frame to some existing xlsx file using "xlsx" package.
>
> Thanks for your time.
>
> On Mon, Jul 11, 2016 at 10:43 AM, Ismail SEZEN <sezenismail at
gmail.com>
> wrote:
> > I think, this is what you are looking for:
> >
> > http://stackoverflow.com/questions/11228942/write-from-
> r-into-template-in-excel-while-preserving-formatting
> >
> > On 11 Jul 2016, at 03:43, Christofer Bogaso <bogaso.christofer at
gmail.com
> >
> > wrote:
> >
> > Hi again,
> >
> > I am trying to write a data frame to an existing Excel file (xlsx)
> > from row 5 and column 6 of the 1st Sheet. I was going through a
> > previous instruction which is available here :
> >
> > http://stackoverflow.com/questions/32632137/using-
> write-xlsx-in-r-how-to-write-in-a-specific-row-or-column-in-excel-file
> >
> > However trouble is that it is modifying/removing formatting of all the
> > affected cells. I have predefined formatting of those cells where data
> > to be pasted, and I dont want to modify or remove that formatting.
> >
> > Any idea if I need to pass some additional argument.
> >
> > Appreciate your valuable feedback.
> >
> > Thanks,
> >
> > ______________________________________________
> > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> > 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.
> >
> >
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> 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.
>
	[[alternative HTML version deleted]]

I don't know if "openxlsx" will solve Christofer's problem,
but it
solved a problem I encountered recently reading a large data set:


       read.xls{gdata} read 20734 obs. of 141966 rows and stopped 
without warning.  read.xls{fImport} and read_excel{readxl} both read 
65536 rows.  I couldn't get xls.reader{ProjectTemplate} to work.


       read.xlsx{openxlsx} read the file I wanted to read.


       Spencer Graves


On 9/26/2016 5:09 PM, jim holtman wrote:
> I use the "openxlsx" package to handle spreadsheets.
>
>
> Jim Holtman
> Data Munger Guru
>
> What is the problem that you are trying to solve?
> Tell me what you want to do, not how you want to do it.
>
> On Mon, Sep 26, 2016 at 5:56 PM, Christofer Bogaso <
> bogaso.christofer at gmail.com> wrote:
>
>> Hi again,
>>
>> I have been following above suggestion to export data from R to xlsx
>> file using XLconnect. However recently I am facing Java memory
>> allocation problem with large dataset (looks like a known issue with
>> this package) and therefore decided to move to using "xlsx"
package.
>>
>> Now I started facing that same problem of losing my existing formating
>> when I use xlsx package for data export. Can someone help me with some
>> pointer on how can I preserve the cell formating after exporting
>> data.frame to some existing xlsx file using "xlsx" package.
>>
>> Thanks for your time.
>>
>> On Mon, Jul 11, 2016 at 10:43 AM, Ismail SEZEN <sezenismail at
gmail.com>
>> wrote:
>>> I think, this is what you are looking for:
>>>
>>> http://stackoverflow.com/questions/11228942/write-from-
>> r-into-template-in-excel-while-preserving-formatting
>>> On 11 Jul 2016, at 03:43, Christofer Bogaso <bogaso.christofer
at gmail.com
>>>
>>> wrote:
>>>
>>> Hi again,
>>>
>>> I am trying to write a data frame to an existing Excel file (xlsx)
>>> from row 5 and column 6 of the 1st Sheet. I was going through a
>>> previous instruction which is available here :
>>>
>>> http://stackoverflow.com/questions/32632137/using-
>> write-xlsx-in-r-how-to-write-in-a-specific-row-or-column-in-excel-file
>>> However trouble is that it is modifying/removing formatting of all
the
>>> affected cells. I have predefined formatting of those cells where
data
>>> to be pasted, and I dont want to modify or remove that formatting.
>>>
>>> Any idea if I need to pass some additional argument.
>>>
>>> Appreciate your valuable feedback.
>>>
>>> Thanks,
>>>
>>> ______________________________________________
>>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more,
see
>>> 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.
>>>
>>>
>> ______________________________________________
>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>> 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.
>>
> 	[[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> 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.

openxlsx is not solving my problem either. It is corrupting my xlsx file.

I have a large data.frame, which I want to export to an existing xlsx
file, without formatting that existing file. With XLconnect there is
an option "setStyleAction(wb,XLC$"STYLE_ACTION.NONE")" which
does it
so. I am looking for a similar codeline for xlsx package which will
enable me to save my data.frame in my existing file without formatting
my xlsx file.

Thanks,

On Tue, Sep 27, 2016 at 3:39 AM, jim holtman <jholtman at gmail.com>
wrote:
> I use the "openxlsx" package to handle spreadsheets.
>
>
> Jim Holtman
> Data Munger Guru
>
> What is the problem that you are trying to solve?
> Tell me what you want to do, not how you want to do it.
>
> On Mon, Sep 26, 2016 at 5:56 PM, Christofer Bogaso
> <bogaso.christofer at gmail.com> wrote:
>>
>> Hi again,
>>
>> I have been following above suggestion to export data from R to xlsx
>> file using XLconnect. However recently I am facing Java memory
>> allocation problem with large dataset (looks like a known issue with
>> this package) and therefore decided to move to using "xlsx"
package.
>>
>> Now I started facing that same problem of losing my existing formating
>> when I use xlsx package for data export. Can someone help me with some
>> pointer on how can I preserve the cell formating after exporting
>> data.frame to some existing xlsx file using "xlsx" package.
>>
>> Thanks for your time.
>>
>> On Mon, Jul 11, 2016 at 10:43 AM, Ismail SEZEN <sezenismail at
gmail.com>
>> wrote:
>> > I think, this is what you are looking for:
>> >
>> >
>> >
http://stackoverflow.com/questions/11228942/write-from-r-into-template-in-excel-while-preserving-formatting
>> >
>> > On 11 Jul 2016, at 03:43, Christofer Bogaso
>> > <bogaso.christofer at gmail.com>
>> > wrote:
>> >
>> > Hi again,
>> >
>> > I am trying to write a data frame to an existing Excel file (xlsx)
>> > from row 5 and column 6 of the 1st Sheet. I was going through a
>> > previous instruction which is available here :
>> >
>> >
>> >
http://stackoverflow.com/questions/32632137/using-write-xlsx-in-r-how-to-write-in-a-specific-row-or-column-in-excel-file
>> >
>> > However trouble is that it is modifying/removing formatting of all
the
>> > affected cells. I have predefined formatting of those cells where
data
>> > to be pasted, and I dont want to modify or remove that formatting.
>> >
>> > Any idea if I need to pass some additional argument.
>> >
>> > Appreciate your valuable feedback.
>> >
>> > Thanks,
>> >
>> > ______________________________________________
>> > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more,
see
>> > 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.
>> >
>> >
>>
>> ______________________________________________
>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>> 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.
>
>

On 9/26/2016 2:56 PM, Christofer Bogaso wrote:
> Hi again,
>
> I have been following above suggestion to export data from R to xlsx
> file using XLconnect. However recently I am facing Java memory
> allocation problem with large dataset (looks like a known issue with
> this package) and therefore decided to move to using "xlsx"
package.
>
> Now I started facing that same problem of losing my existing formating
> when I use xlsx package for data export. Can someone help me with some
> pointer on how can I preserve the cell formating after exporting
> data.frame to some existing xlsx file using "xlsx" package.
>
> Thanks for your time.
>
> On Mon, Jul 11, 2016 at 10:43 AM, Ismail SEZEN <sezenismail at
gmail.com> wrote:
>> I think, this is what you are looking for:
>>
>>
http://stackoverflow.com/questions/11228942/write-from-r-into-template-in-excel-while-preserving-formatting
>>
>> On 11 Jul 2016, at 03:43, Christofer Bogaso <bogaso.christofer at
gmail.com>
>> wrote:
>>
>> Hi again,
>>
>> I am trying to write a data frame to an existing Excel file (xlsx)
>> from row 5 and column 6 of the 1st Sheet. I was going through a
>> previous instruction which is available here :
>>
>>
http://stackoverflow.com/questions/32632137/using-write-xlsx-in-r-how-to-write-in-a-specific-row-or-column-in-excel-file
>>
>> However trouble is that it is modifying/removing formatting of all the
>> affected cells. I have predefined formatting of those cells where data
>> to be pasted, and I dont want to modify or remove that formatting.
>>
>> Any idea if I need to pass some additional argument.
>>
>> Appreciate your valuable feedback.
>>
>> Thanks,
>>
>> ______________________________________________
>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>> 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.
>>
>>
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> 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.
>
It would help the list to help you if you gave a reproducible example. 
In the absence of that, at least show the actual code you are using to 
write to the Excel (.xlsx) sheet.

But maybe reading about the "create" argument on page 13 of this
linked
document will help:

https://cran.r-project.org/web/packages/xlsx/xlsx.pdf


Dan

-- 
Daniel Nordlund
Port Townsend, WA  USA

Hi Daniel,

Am attaching an example xlsx file which I need to modify.

I have tried with below code :

library(xlsx)
Data = data.frame(1:20)
wb <- loadWorkbook("asd.xlsx")   ### Assume I saved asd.xlsx in the
working directory
addDataFrame(Data, sheet = getSheets(wb)[['Sheet1']], startRow=5,
startColumn=2, row.names=F, col.names=F)
saveWorkbook(wb, "asd.xlsx")

Basically I am trying to modify the 2nd column with the data of 'Data'
however I do not want to disturb the formatting there in any extend.
However above code removing the cell color, which I do not want.

What could be right code for doing so?

Thanks and regards,

PS: not very sure if R forum would accept my attachment, if not that
file is available in https://ufile.io/50944

On Tue, Sep 27, 2016 at 12:06 PM, Daniel Nordlund <djnordlund at
gmail.com> wrote:
> On 9/26/2016 2:56 PM, Christofer Bogaso wrote:
>>
>> Hi again,
>>
>> I have been following above suggestion to export data from R to xlsx
>> file using XLconnect. However recently I am facing Java memory
>> allocation problem with large dataset (looks like a known issue with
>> this package) and therefore decided to move to using "xlsx"
package.
>>
>> Now I started facing that same problem of losing my existing formating
>> when I use xlsx package for data export. Can someone help me with some
>> pointer on how can I preserve the cell formating after exporting
>> data.frame to some existing xlsx file using "xlsx" package.
>>
>> Thanks for your time.
>>
>> On Mon, Jul 11, 2016 at 10:43 AM, Ismail SEZEN <sezenismail at
gmail.com>
>> wrote:
>>>
>>> I think, this is what you are looking for:
>>>
>>>
>>>
http://stackoverflow.com/questions/11228942/write-from-r-into-template-in-excel-while-preserving-formatting
>>>
>>> On 11 Jul 2016, at 03:43, Christofer Bogaso <bogaso.christofer
at gmail.com>
>>> wrote:
>>>
>>> Hi again,
>>>
>>> I am trying to write a data frame to an existing Excel file (xlsx)
>>> from row 5 and column 6 of the 1st Sheet. I was going through a
>>> previous instruction which is available here :
>>>
>>>
>>>
http://stackoverflow.com/questions/32632137/using-write-xlsx-in-r-how-to-write-in-a-specific-row-or-column-in-excel-file
>>>
>>> However trouble is that it is modifying/removing formatting of all
the
>>> affected cells. I have predefined formatting of those cells where
data
>>> to be pasted, and I dont want to modify or remove that formatting.
>>>
>>> Any idea if I need to pass some additional argument.
>>>
>>> Appreciate your valuable feedback.
>>>
>>> Thanks,
>>>
>>> ______________________________________________
>>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more,
see
>>> 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.
>>>
>>>
>>
>> ______________________________________________
>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>> 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.
>>
>
> It would help the list to help you if you gave a reproducible example. In
> the absence of that, at least show the actual code you are using to write
to
> the Excel (.xlsx) sheet.
>
> But maybe reading about the "create" argument on page 13 of this
linked
> document will help:
>
> https://cran.r-project.org/web/packages/xlsx/xlsx.pdf
>
>
> Dan
>
> --
> Daniel Nordlund
> Port Townsend, WA  USA
>
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> 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.