OtherEffects

From xlsx v0.6.5 by Cole Arendt

Functions to do various spreadsheets effects.

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.

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

sheet: a Worksheet object.
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")
startRow: a numeric value for the starting row.
endRow: a numeric value for the ending row.
startColumn: a numeric value for the starting column.
endColumn: a numeric value for the ending column.
ind: a numeric value indicating which merged region you want to remove.
colIndex: a numeric vector specifiying the columns you want to auto size.
rowSplit: a numeric value for the row to split.
colSplit: a numeric value for the column to split.
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.
position: a character. Valid value are "PANE_LOWER_LEFT", "PANE_LOWER_RIGHT", "PANE_UPPER_LEFT", "PANE_UPPER_RIGHT".
colWidth: a numeric value to specify the width of the column. The units are in 1/256ths of a character width.
wb: a Workbook object.
sheetIndex: a numeric value for the worksheet index.
numerator: a numeric value representing the numerator of the zoom ratio.
denominator: a numeric value representing the denomiator of the zoom ratio.

Details

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.

Value

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

Examples

# NOT RUN {

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

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

  ## Set zoom 2:1
  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 ...

# }

Documentation reproduced from package xlsx, version 0.6.5, License: GPL-3

Community examples

Looks like there are no examples yet.