[R] Writing data onto xlsx file without cell formatting

Tue Sep 27 20:27:29 CEST 2016

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-bounces at 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 <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-
> templa
> >>> te-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-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 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.
> ______________________________________________
> 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.