52
CellBlock
13
create
If TRUE cells will be created if they don’t exist, if FALSE only existing cells will
be used. If cells don’t exist (on a new sheet forexample),youhave to use TRUE.
On an existing sheet with data, use TRUE if you want to blank out an existing
cell block. Use FALSE if you want to keep the styling of existing cells, but just
modify the value of the cell.
cellBlock
acell block object as returned byCellBlock.
rowStyle
aCellStyle object used to style the row.
colStyle
aCellStyle object used to style the column.
cellStyle
aCellStyle object.
border
aBorder object, as returned byBorder.
fill
aFill object, as returned byFill.
font
aFont object,as returned byFont.
colIndex
anumeric vector specifiying the columns you want relative tothestartColumn.
rowIndex
anumeric vector specifiying the rows you want relative to the startRow.
x
the data youwant to add to the cell block, a vectorora matrixdepending on the
function.
Details
Introduced in version 0.5.0 of the package, these functions speed up the creation and styling of
cells that are part of a "cell block" (a rectangular shaped group of cells). Use the functions above if
you want to create efficiently a complex sheet with many styles. A simple by-column styling can
be done by directly usingaddDataFrame. With the functionality provided here you can efficiently
style individual cells, see the example.
It is difficulttotreat NA’s consistently betweenR and Excel via Java. Mostlikely,users ofExcelwill
want to see NA’s as blank cells. In R character NA’s are simply characters, which for Excel means
"NA".
If you try to set more data to the block than you have cells in the block, only the existing cells will
be set.
Note that when modifying the style of a group of cells, the changes are made to the pairs defined
by (rowIndex, colIndex). This implies that the length of rowIndex and colIndex are the same
value. An exception is made when either rowIndex or colIndex have length one, when they will
be expanded internally to match the length of the other index.
Function CB.setMatrixData works for numeric or character matrices. If the matrix x is not of
numeric type it will be converted to a character matrix.
Value
ForCellBlocka cell block object.
ForCB.setColData,CB.setRowData,CB.setMatrixData,CB.setFill,CB.setFont,CB.setBorder
nothing as he modification to the workbook is done in place.
Author(s)
Adrian Dragulescu
VB.NET PDF: Basic SDK Concept of XDoc.PDF XDoc.PDF for .NET supports editing PDF document metadata, like Title, Subject, Author, Creator, Producer, Keywords, Created Date, and Last Modified Date.
pdf metadata; add metadata to pdf
38
14
CellProtection
Examples
wb <- createWorkbook()
sheet <- createSheet(wb, sheetName="CellBlock")
cb <- CellBlock(sheet, 7, 3, 1000, 60)
CB.setColData(cb, 1:100, 1)
# set a column
CB.setRowData(cb, 1:50, 1)
# set a row
# add a matrix, and style it
cs <- CellStyle(wb) + DataFormat("#,##0.00")
x <- matrix(rnorm(900*45), nrow=900)
CB.setMatrixData(cb, x, 10, 4, cellStyle=cs)
# highlight the negative numbers in red
fill <- Fill(foregroundColor = "red", backgroundColor="red")
ind <- which(x < 0, arr.ind=TRUE)
CB.setFill(cb, fill, ind[,1]+9, ind[,2]+3) # note the indices offset
# set the border on the top row of the Cell Block
border <- Border(color="blue", position=c("TOP", "BOTTOM"),
pen=c("BORDER_THIN", "BORDER_THICK"))
CB.setBorder(cb, border, 1:1000, 1)
# Dont forget to save the workbook ...
# saveWorkbook(wb, file)
CellProtection
Create a CellProtection object.
Description
Create a CellProtection object used forcell styles.
Usage
CellProtection(locked=TRUE, hidden=FALSE)
is.CellProtection(x)
Arguments
locked
alogical indicating the cell is locked.
hidden
alogical indicating the cell is hidden.
x
ACellProtection object, as returnedby CellProtection.
C# PDF - Read Barcode on PDF in C#.NET Bookmark: Edit Bookmark. Metadata: Edit, Delete Metadata. Watermark: Add Watermark to PDF. Form Process. Data: Read, Extract Field Data. Data: Auto Fill-in Field
search pdf metadata; acrobat pdf additional metadata
34
CellStyle
15
Value
CellProtection returns a list with components from the input argument, and a class attribute
"CellProtection". CellProtection objects are used when constructing cell styles.
is.CellProtection returns TRUE ifthe argumentis ofclass "CellProtection"and FALSE otherwise.
Author(s)
Adrian Dragulescu
See Also
CellStyleforusingtheaCellProtectionobject.
Examples
font <- CellProtection(locked=TRUE)
CellStyle
Functions to manipulate cells.
Description
Create and set cell styles.
Usage
## Default S3 method:
CellStyle(wb, dataFormat=NULL, alignment=NULL,
border=NULL, fill=NULL, font=NULL, cellProtection=NULL)
setCellStyle(cell, cellStyle)
getCellStyle(cell)
is.CellStyle(x)
Arguments
wb
aworkbook object as returned bycreateWorkbook orloadWorkbook.
dataFormat
aDataFormat object.
alignment
aAlignment object.
border
aBorder object.
fill
aFill object.
42
16
CellStyle
font
aFont object.
cellProtection aCellProtection object.
x
aCellStyle object.
cell
aCell object.
cellStyle
aCellStyle object.
...
arguments to CellStyle.default.
Details
setCellStyle sets the CellStyle to one Cell object.
You need to have a Workbook object to attach a CellStyle object to it.
Since OSX 10.5Apple droppedsupportfor AWT onthemain thread, so essentially you cannot use
any graphics classes inR onOSX10.5sinceR is single-threaded. (verbatimfromSimonUrbanek).
This implies that setting colors on Mac will not work as is! A set of about 50 basic colors are still
available please see the javadocs.
For Excel 95/2000/XP/2003 the choice of colors is limited. See INDEXED_COLORS_ for the list of
available colors.
Unspecified values forarguments are taken fromthe system locale.
Value
createCellStyle creates a CellStyle object.
is.CellStyle returns TRUE ifthe argument is of class "CellStyle"and FALSE otherwise.
Author(s)
Adrian Dragulescu
Examples
## Not run:
wb <- createWorkbook()
sheet <- createSheet(wb, "Sheet1")
rows <- createRow(sheet, rowIndex=1)
cell.1 <- createCell(rows, colIndex=1)[[1,1]]
setCellValue(cell.1, "Hello R!")
cs <- CellStyle(wb) +
Font(wb, heightInPoints=20, isBold=TRUE, isItalic=TRUE,
name="Courier New", color="orange") +
Fill(backgroundColor="lavender", foregroundColor="lavender",
pattern="SOLID_FOREGROUND") +
Alignment(h="ALIGN_RIGHT")
setCellStyle(cell.1, cellStyle1)
31
Comment
17
# you need to save the workbook now if you want to see this art
## End(Not run)
Comment
Functions to manipulate cell comments.
Description
Functions to manipulate cell comments.
Usage
createCellComment(cell, string="", author=NULL, visible=TRUE)
getCellComment(cell)
removeCellComment(cell)
Arguments
cell
aCell object.
string
astring for the comment.
author
astring with the author’s name
visible
alogical value. If TRUE the comment will be visible.
Details
These functions are not vectorized.
Value
createCellComment creates a Comment object.
getCellComment returns a the Comment object if it exists.
removeCellComment removes a comment from the given cell.
Author(s)
Adrian Dragulescu
See Also
Forcells,seeCell. To format cells,seeCellStyle.
38
18
DataFormat
Examples
wb <- createWorkbook()
sheet1 <- createSheet(wb, "Sheet1")
rows
<- createRow(sheet1, rowIndex=1:10)
# 10 rows
cells <- createCell(rows, colIndex=1:8)
# 8 columns
cell1 <- cells[[1,1]]
setCellValue(cell1, 1)
# add value 1 to cell A1
# create a cell comment
createCellComment(cell1, "Cogito", author="Descartes")
# extract the comments
comment <- getCellComment(cell1)
stopifnot(comment$getAuthor()=="Descartes")
stopifnot(comment$getString()$toString()=="Cogito")
# dont forget to save your workbook!
DataFormat
Create an DataFormat object.
Description
Create an DataFormat object,useful when working with cell styles.
Usage
DataFormat(x)
is.DataFormat(df)
Arguments
x
acharacter value specifying the data format.
df
An DataFormat object, as returned by DataFormat.
Details
Specifying the dataFormat argument allows you to format the cell. For example, "#,##0.00" cor-
responds to using a comma separatorforpowers of 1000 with two decimal places, "m/d/yyyy" can
be used to format dates and is the equivalent of R’s MM/DD/YYYY format. To format datetimes
use "m/d/yyyy h:mm:ss;@". To show negative values in red within parantheses with two decimals
and commas after power of 1000 use "#,##0.00_);[Red](#,##0.00)". I am not aware of an official
33
Fill
19
way to discover these strings. I find them out by recording a macro that formats a specific cell and
then checking out the resulting VBA code. Fromthere you can read the dataFormat code.
Value
DataFormatreturns a list one component dataFormat, and a class attribute "DataFormat". DataFor-
mat objects are used when constructing cell styles.
is.DataFormat returns TRUE if the argument is ofclass "DataFormat" and FALSE otherwise.
Author(s)
Adrian Dragulescu
See Also
CellStyleforusingtheaDataFormatobject.
Examples
df <- DataFormat("#,##0.00")
Fill
Create an Fill object.
Description
Create an Fill object, useful when working with cell styles.
Usage
Fill(foregroundColor="lightblue", backgroundColor="lightblue",
pattern="SOLID_FOREGROUND")
is.Fill(x)
Arguments
foregroundColor
a character vector specifiying the foreground color. Any color names as re-
turned bycolors can be used. Or, a hex character, e.g. "#FF0000" for red.
For Excel 95 workbooks, only a subset of colors is available, see the constant
INDEXED_COLORS_.
backgroundColor
a character vector specifiying the foreground color. Any color names as re-
turned bycolors can be used. Or, a hex character, e.g. "#FF0000" for red.
For Excel 95 workbooks, only a subset of colors is available, see the constant
INDEXED_COLORS_.
34
20
Font
pattern
acharacter vector specifying the fill pattern style. Valid values come from con-
stant FILL_STYLES_.
x
aFill object, as returned by Fill.
Value
Fillreturns alistwith components fromtheinputargument,anda class attribute"Fill". Fillobjects
are used when constructing cell styles.
is.Fill returns TRUE if the argument is of class "Fill"and FALSE otherwise.
Author(s)
Adrian Dragulescu
See Also
CellStyleforusingtheaFillobject.
Examples
fill <- Fill()
Font
Create a Font object.
Description
Create a Font object.
Usage
Font(wb, color=NULL, heightInPoints=NULL, name=NULL,
isItalic=FALSE, isStrikeout=FALSE, isBold=FALSE, underline=NULL,
boldweight=NULL)
is.Font(x)
Arguments
wb
aworkbook object as returned bycreateWorkbook orloadWorkbook.
color
acharacter specifiying the font color. Any color names as returned bycolors
can be used. Or, a hex character, e.g. "#FF0000" for red. For Excel 95 work-
books, only a subset of colors is available, see the constant INDEXED_COLORS_.
heightInPoints a numeric value specifying the font height. Usual values are 10, 12,14,etc.
Documents you may be interested
Documents you may be interested
