xlsx (version 0.6.1)

OtherEffects: Functions to do various spreadsheets effects.

Description

Functions to do various spreadsheets effects.

Usage

addAutoFilter(sheet, cellRange)
addMergedRegion(sheet, startRow, endRow, startColumn, endColumn)
removeMergedRegion(sheet, ind)
autoSizeColumn(sheet, colIndex)
createFreezePane(sheet, rowSplit, colSplit, startRow=NULL,
  startColumn=NULL)
createSplitPane(sheet, xSplitPos=2000, ySplitPos=2000,
  startRow=1, startColumn=1, position="PANE_LOWER_LEFT")
setColumnWidth(sheet, colIndex, colWidth)
setPrintArea(wb, sheetIndex, startColumn, endColumn, startRow,
  endRow)
setZoom(sheet, numerator=100, denominator=100)

Arguments

cellRange

a string specifying the cell range. For example a standard area ref (e.g. "B1:D8"). May be a single cell ref (e.g. "B5") in which case the result is a 1 x 1 cell range. May also be a whole row range (e.g. "3:5"), or a whole column range (e.g. "C:F")

colIndex

a numeric vector specifiying the columns you want to auto size.

colSplit

a numeric value for the column to split.

colWidth

a numeric value to specify the width of the column. The units are in 1/256ths of a character width.

denominator

a numeric value representing the denomiator of the zoom ratio.

endColumn

a numeric value for the ending column.

endRow

a numeric value for the ending row.

ind

a numeric value indicating which merged region you want to remove.

numerator

a numeric value representing the numerator of the zoom ratio.

position

a character. Valid value are "PANE_LOWER_LEFT", "PANE_LOWER_RIGHT", "PANE_UPPER_LEFT", "PANE_UPPER_RIGHT".

rowSplit

a numeric value for the row to split.

sheet

a Worksheet object.

sheetIndex

a numeric value for the worksheet index.

startColumn

a numeric value for the starting column.

startRow

a numeric value for the starting row.

xSplitPos

a numeric value for the horizontal position of split in 1/20 of a point.

ySplitPos

a numeric value for the vertical position of split in 1/20 of a point.

wb

a Workbook object.

Value

addMergedRegion returns a numeric value to label the merged region. You should use this value as the ind if you want to removeMergedRegion.

Details

Function autoSizeColumn expands the column width to match the column contents thus removing the ###### that you get when cell contents are larger than cell width.

You may need other functionality that is not exposed. Take a look at the java docs and the source code of these functions for how you can implement it in R.

Examples

# NOT RUN {
  wb <- createWorkbook()
  sheet1 <- createSheet(wb, "Sheet1")
  rows   <- createRow(sheet1, 1:10)              # 10 rows
  cells  <- createCell(rows, colIndex=1:8)       # 8 columns

  cat("Merge cells \n")
  setCellValue(cells[[1,1]], "A title that spans 3 columns")
  addMergedRegion(sheet1, 1, 1, 1, 3)

  cat("Set zoom 2:1 \n")
  setZoom(sheet1, 200, 100)

  sheet2 <- createSheet(wb, "Sheet2")
  rows  <- createRow(sheet2, 1:10)              # 10 rows
  cells <- createCell(rows, colIndex=1:8)       # 8 columns
  #createFreezePane(sheet2, 1, 1, 1, 1)
  createFreezePane(sheet2, 5, 5, 8, 8)
  
  sheet3 <- createSheet(wb, "Sheet3")
  rows  <- createRow(sheet3, 1:10)              # 10 rows
  cells <- createCell(rows, colIndex=1:8)       # 8 columns
  createSplitPane(sheet3, 2000, 2000, 1, 1, "PANE_LOWER_LEFT")

  # set the column width of first column to 25 characters wide
  setColumnWidth(sheet1, 1, 25)

  # add a filter on the 3rd row, columns C:E
  addAutoFilter(sheet1, "C3:E3")

  # Don't forget to save the workbook ...  
 
# }