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