Functions to manipulate cells.

createCell(row, colIndex = 1:5)

getCells(row, colIndex = NULL, simplify = TRUE)

setCellValue(cell, value, richTextString = FALSE, showNA = TRUE)

getCellValue(cell, keepFormulas = FALSE, encoding = "unknown")

Arguments

row: a list of row objects. See Row.
colIndex: a numeric vector specifying the index of columns.
simplify: a logical value. If TRUE, the result will be unlisted.
cell: a Cell object.
value: an R variable of length one.
richTextString: a logical value indicating if the value should be inserted into the Excel cell as rich text.
showNA: a logical value. If TRUE the cell will contain the "#N/A" value, if FALSE they will be skipped. The default value was chosen to remain compatible with previous versions of the function.
keepFormulas: a logical value. If TRUE the formulas will be returned as characters instead of being explicitly evaluated.
encoding: A character value to set the encoding, for example "UTF-8".

Value

createCell creates a matrix of lists, each element of the list being a java object reference to an object of type Cell representing an empty cell. The dimnames of this matrix are taken from the names of the rows and the colIndex variable. getCells returns a list of java object references for all the cells in the row if colIndex is NULL. If you want to extract only a specific columns, set colIndex to the column indices you are interested. getCellValue returns the value in the cell as an R object. Type conversions are done behind the scene. This function is not vectorized.

Details

setCellValue writes the content of an R variable into the cell. Date and POSIXct objects are passed in as numerical values. To format them as dates in Excel see CellStyle.

These functions are not vectorized and should be used only for small spreadsheets. Use CellBlock functionality to efficiently read/write parts of a spreadsheet.

Author

Adrian Dragulescu

Examples



file <- system.file("tests", "test_import.xlsx", package = "xlsx")

wb <- loadWorkbook(file)  
sheets <- getSheets(wb)

sheet <- sheets[['mixedTypes']]      # get second sheet
rows  <- getRows(sheet)   # get all the rows

cells <- getCells(rows)   # returns all non empty cells

values <- lapply(cells, getCellValue) # extract the values

# write the months of the year in the first column of the spreadsheet
ind <- paste(2:13, ".2", sep="")
mapply(setCellValue, cells[ind], month.name)
#> $`2.2`
#> NULL
#> 
#> $`3.2`
#> NULL
#> 
#> $`4.2`
#> NULL
#> 
#> $`5.2`
#> NULL
#> 
#> $`6.2`
#> NULL
#> 
#> $`7.2`
#> NULL
#> 
#> $`8.2`
#> NULL
#> 
#> $`9.2`
#> NULL
#> 
#> $`10.2`
#> NULL
#> 
#> $`11.2`
#> NULL
#> 
#> $`12.2`
#> NULL
#> 
#> $`13.2`
#> NULL
#> 

####################################################################
# make a new workbook with one sheet and 5x5 cells
wb <- createWorkbook()
sheet <- createSheet(wb, "Sheet1")
rows  <- createRow(sheet, rowIndex=1:5)
cells <- createCell(rows, colIndex=1:5) 

# populate the first column with Dates
days <- seq(as.Date("2013-01-01"), by="1 day", length.out=5)
mapply(setCellValue, cells[,1], days)
#> $`1`
#> NULL
#> 
#> $`2`
#> NULL
#> 
#> $`3`
#> NULL
#> 
#> $`4`
#> NULL
#> 
#> $`5`
#> NULL
#>