make cell ranges use absolute references

[GoogleCodeExporter]

What steps will reproduce the problem?
On Jun 19, 2014 1:50 PM, "Stephen Stamatis" wrote:
Hi Adrian,

First, let me say thank you for providing this package to the R community. It 
has certainly made my life easier!

For a recent project, I have found that using named ranges makes the excel 
followup work easier. The R routine in the package called: createRange() in the 
NamedRanges.R file is relevant.

It seems that by default, the ranges are created as relative(i.e. without $'s) 
, rather than as absolute(i.e. with $'s) . The resulting ranges work as 
expected for further processing in R as the references are all relative to cell 
A1. However upon opening the document in Excel, the ranges move with the active 
cell and therefore break.

I am not sure if this is designed behavior, but it seems like it is a bug to me.

You can force the references to be created as absolute by using the following 
form of the call to POI CellReference

CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) 

In your code, simply putting in TRUE, TRUE for the arguments worked for my test 
cases, but there are certainly other and likely better approaches. Perhaps the 
most obvious one to recommend if you were so inclined to address this issue, is 
that you could add the row and col absolute arguments to the createRange 
function definition. Defaulting them to FALSE should not break existing code.

Either way, I found a work around for me needs.  Again I wanted to thank you 
for sharing this work and wish you the best of luck.

All the best,
Stephen

Original issue reported on code.google.com by [email protected] on 26 Jun 2014 at 6:40

[mac471]

Stephen,

I realize this is an old post but I ran into this issue as well and believe that it is a bug. My interim solution was to create my own function that supplies two additional logical arguments: absRowP and absColP that are passed to CellReference.

There is an additional bug when creating nameFormula. If sheetName has embedded spaces, it will cause the function to fail. The solution is to put single quotes around the sheet name.

Just some suggestions.

xlsxCreateRange <- function (rangeName, firstCell, lastCell, absRowP=TRUE, absColP=TRUE) 
{
  sheet <- firstCell$getSheet()
  sheetName <- sheet$getSheetName()
  firstCellRef <- .jnew("org/apache/poi/ss/util/CellReference", 
                        as.integer(firstCell$getRowIndex()), as.integer(firstCell$getColumnIndex()),TRUE,TRUE)
  lastCellRef <- .jnew("org/apache/poi/ss/util/CellReference", 
                       as.integer(lastCell$getRowIndex()), as.integer(lastCell$getColumnIndex()),TRUE,TRUE)
  
  ## 12/30/2014: NOTE: we escape the sheet name with single quotes in case the sheet name has embedded blanks
  ## If it does, it causes this routine to fail
  nameFormula <- paste("'",sheetName, "'", "!", firstCellRef$formatAsString(), 
                       ":", lastCellRef$formatAsString(), sep = "")
  wb <- sheet$getWorkbook()
  range <- wb$createName()
  range$setNameName(rangeName)
  range$setRefersToFormula(nameFormula)
  range
}