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