OtherEffects: Functions to do various spreadsheets effects.

Description Usage Arguments Details Value Author(s) Examples

Description

Functions to do various spreadsheets effects.

Usage

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
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.

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.

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
  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 ...  
 

colearendt/xlsx documentation built on Oct. 11, 2018, 12:23 a.m.