OtherEffects {xlsx}

Functions to do various spreadsheets effects.

Description

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.

Author(s)

Adrian Dragulescu

Examples

  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 ...