xlsx (version 0.6.5)

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

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

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

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

# }