Re: [R] Writing data onto xlsx file without cell formatting

[Spencer Graves]

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.christo...@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 
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 

Re: [R] Writing data onto xlsx file without cell formatting

[Nordlund, Dan (DSHS/RDA)]

Christofer,

I do not really use the xlsx package, so this may not be the 'best' approach.  
And based on your spreadsheet, and the code that you supplied, I am not sure 
what you really intended.  But here is some sample code that will update cells 
in your spreadsheet without changing the formatting.

library(xlsx)
Data = data.frame(1:20)
wb <- loadWorkbook("asd.xlsx")   ### Assume I saved asd.xlsx in the working 
directory
sheet <- getSheets(wb)[['Sheet1']]
cb <- CellBlock(sheet, startRow=5, startColumn=2, noRows=20, noColumns=1, 
create=FALSE)
CB.setColData(cb, x=Data[,1], colIndex=1)
saveWorkbook(wb, "asd.xlsx")


The code uses the CellBlock() function to define the region you want to write 
data to (I could have defined the cell block region to contain more rows and 
columns).  Then the CB.setColData() function writes the data to the column 
within the defined cell block identified by the the colIndex parameter.  
colIndex indexes into the cell block region.  So, even though the we want to 
write to column 2, since column 2 is the first column in the cell block we need 
to set colIndex=1.  You need to change the rowOffset value if you don't want to 
start writing in the first row of the cell block region.   I didn't look around 
much to see if the addDataFrame() function could be used with a cell block 
region.

You can play with the various functions and parameters to decide if the xlsx 
package will meet your needs 


Hope this is helpful,

Dan

Daniel Nordlund, PhD
Research and Data Analysis Division
Services & Enterprise Support Administration
Washington State Department of Social and Health Services

> -Original Message-
> From: R-help [mailto:r-help-boun...@r-project.org] On Behalf Of Christofer
> Bogaso
> Sent: Tuesday, September 27, 2016 9:46 AM
> To: Daniel Nordlund
> Cc: r-help
> Subject: Re: [R] Writing data onto xlsx file without cell formatting
> 
> 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 <djnordl...@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
> >> <sezenism...@gmail.com>
> >> wrote:
> >>>
> >>> I think, this is what you are looking for:
> >>>
> >>>
> >>> http://stackoverflow.com/questions/11228942/write-from-r-into-
> templa
> >>> te-in-excel-while-preserving-formatting
> >>>
> >>> On 11 Jul 2016, at 03:43, Christofer Bogaso
> >>> <bogaso.christo...@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-ho
> >>> w-to-write-in-a-specific-row-or-column-in-excel-file
> >>>
> >>> However trouble is that it is modifying/removing formatting of all

Re: [R] Writing data onto xlsx file without cell formatting

[Christofer Bogaso]

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  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 
>> 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 
>>> 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@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@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@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@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.

Re: [R] Writing data onto xlsx file without cell formatting

[Daniel Nordlund]

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  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 
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@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@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@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.

Re: [R] Writing data onto xlsx file without cell formatting

[Christofer Bogaso]

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  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
>  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 
>> 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
>> > 
>> > 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@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@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@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.

Re: [R] Writing data onto xlsx file without cell formatting

[jim holtman]

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.christo...@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 
> 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  >
> > 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@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@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@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.

Re: [R] Writing data onto xlsx file without cell formatting

[Christofer Bogaso]

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  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 
> 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@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@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.

Re: [R] Writing data onto xlsx file without cell formatting

[Ismail SEZEN]

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  
> 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@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@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] Writing data onto xlsx file without cell formatting

[Christofer Bogaso]

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@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.