OtherEffects
11
Examples
file <- system.file("tests", "test_import.xlsx", package = "xlsx")
wb <- loadWorkbook(file)
sheet <- getSheets(wb)[["deletedFields"]]
ranges <- getRanges(wb)
res
<- readRange(ranges[[1]], sheet, colClasses="numeric") # read it
ranges[[1]]$getNameName()
# get its name
# see all the available java methods that you can call
.jmethods(ranges[[1]])
# create a new named range
firstCell <- sheet$getRow(14L)$getCell(4L)
lastCell
<- sheet$getRow(20L)$getCell(7L)
rangeName <- "Test2"
createRange(rangeName, firstCell, lastCell)
OtherEffects
Functions to do various spreadsheets effects.
Description
Functions to do various spreadsheets effects.
Usage
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")
setPrintArea(wb, sheetIndex, startColumn, endColumn, startRow,
endRow)
setZoom(sheet, numerator=100, denominator=100)
Rotate pdf page few degrees - rotate PDF page permanently in C#.net, ASP.NET, MVC, Ajax, WinForms, WPF
Empower Users to Change the Rotation Angle of PDF File Page Using C#
permanently rotate pdf pages; rotate single page in pdf
Rotate pdf page few degrees - VB.NET PDF Page Rotate Library: rotate PDF page permanently in vb.net, ASP.NET, MVC, Ajax, WinForms, WPF
PDF Document Page Rotation in Visual Basic .NET Class Application
rotate all pages in pdf; pdf reverse page order online
12
OtherEffects
Arguments
sheet
aWorksheet object.
startRow
anumeric value for the starting row.
endRow
anumeric value for the ending row.
startColumn a numeric value for the starting column.
endColumn
anumeric value for the ending column.
ind
anumeric value indicating which merged regionyou want to remove.
position
acharacter. Valid value are"PANE_LOWER_LEFT","PANE_LOWER_RIGHT",
"PANE_UPPER_LEFT", "PANE_UPPER_RIGHT".
colIndex
anumeric vector specifiying the columns you want to auto size.
rowSplit
anumeric value for the row to split.
colSplit
anumeric value for the column to split.
xSplitPos
anumeric value for the horizontal position ofsplit in 1/20 of a point.
ySplitPos
anumeric value for the vertical position of split in 1/20 ofa point.
wb
aWorkbook object.
sheetIndex
anumeric value for the worksheet index.
numerator
anumeric value representing the numerator of the zoomratio.
denominator a numeric value representing the denomiator of the zoomratio.
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 anumeric value to labelthemerged region. You shouldusethis value
as the ind ifyou 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
cat("Merge cells \n")
setCellValue(cells[[1,1]], "A title that spans 3 columns")
addMergedRegion(sheet1, 1, 1, 1, 3)
VB.NET Image: Using VB.NET Web Image Viewer to Process Web Images
types, including JPEG, PNG, GIF, BMP, TIFF, PDF, Word. btnRotate270 API can be used to rotate currently displayed image or document page 90 degrees
rotate pdf page by page; rotate individual pages in pdf reader
Process Images in Web Image Viewer | Online Tutorials
Commonly used document types are supported, including PDF, multi-page TIFF This JavaScript API is used to rotate the current image / page 90 degrees
how to permanently rotate pdf pages; how to rotate a pdf page in reader
Picture
13
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")
# Don't forget to save the workbook ...
Picture
Functions to manipulate images in a spreadsheet.
Description
Functions to manipulate images in a spreadsheet.
Usage
addPicture(file, sheet, scale=1, startRow=1, startColumn=1)
Arguments
file
the absolute path to the image file.
sheet
aworksheetobjectasreturnedbycreateSheetorby subsettinggetSheets.
The picturewill be addedonthis sheetatpositionstartRow,startColumn.
scale
anumeric specifying the scale factor for the image.
startRow
anumeric specifying the row of the upper left corner of the image.
startColumn a numeric specifying the column ofthe upperleft corner of the image.
Details
Fornow, the following image types are supported: dib,emf, jpeg,pict, png, wmf. Please note, that
scaling works correctly onlyforworkbooks with the default font size (Calibri 11ptfor.xlsx). If the
default font is changed the resized image can be streched vertically or horizontally.
Don’t know how to remove an existing image yet.
C# Image: C# Image Web Viewer Processing Tutorial; Process Web Doc
In order to rotate a current web image or by editing your C# project web page Default.aspx provide powerful & profession imaging controls, PDF document, tiff
pdf rotate page; rotate all pages in pdf file
VB.NET Image: Generate GS1-128/EAN-128 Barcode on Image & Document
resolution barcode.Rotate = Rotate.Rotate0 'set rotate barcode.DrawBarcode Its supported document types are PDF, Word and EAN-128 barcode on multi-page TIFF/PDF
reverse page order pdf; rotate pages in pdf online
14
PrintSetup
Value
addPicture a java object references pointing to the picture.
Author(s)
Adrian Dragulescu
Examples
file <- system.file("tests", "log_plot.jpeg", package = "xlsx")
wb <- createWorkbook()
sheet <- createSheet(wb, "Sheet1")
addPicture(file, sheet)
# don't forget to save the workbook!
PrintSetup
Function to manipulate print setup.
Description
Function to manipulate print setup.
Usage
printSetup(sheet, fitHeight=NULL,
fitWidth=NULL, copies=NULL, draft=NULL, footerMargin=NULL,
headerMargin=NULL, landscape=FALSE, pageStart=NULL, paperSize=NULL,
noColor=NULL)
Arguments
sheet
aworksheet objectWorksheet.
fitHeight
numeric value to set the number of pages high to fit the sheet in.
fitWidth
numeric value to set the number of pages wide to fit the sheet in.
copies
numeric value to set the number of copies.
draft
logical indicating if it’s a draft ornot.
footerMargin numeric value to set the footermargin.
headerMargin numeric value to set the header margin.
landscape
logical value to specify the paperorientation.
VB Imaging - Linear ITF-14 Barcode Generator
Spend a few seconds creating ITF-14 barcode in Resolution = 96 'set rotation barcode.Rotate = Rotate.Rotate0 barcode to Create ITF-14 in Certain PDF Page Area.
pdf rotate pages and save; how to rotate just one page in pdf
C# TIFF: Create Linear and 2D Barcodes on TIFF Document Using C#
C# TIFF PDF - 417 Creating, C# TIFF ISBN Creating. Creator Add-on for TIFF allows C# users to generate GS1-128/EAN-128 barcode on TIFF file page with high print
rotate pdf page and save; rotate pages in pdf
read.xlsx
15
pageStart
numeric value fromwhere to start the page numbering.
paperSize
charactertosetthe papersize. Validvalues are "A4_PAPERSIZE","A5_PAPERSIZE",
"ENVELOPE_10_PAPERSIZE","ENVELOPE_CS_PAPERSIZE","ENVELOPE_DL_PAPERSIZE",
"ENVELOPE_MONARCH_PAPERSIZE","EXECUTIVE_PAPERSIZE","LE-
GAL_PAPERSIZE", "LETTER_PAPERSIZE".
noColor
logical value to indicate if the prints should be color or not.
Details
Othersettings are available but not exposed. Please see the java docs.
Value
Areference to a java PrintSetup object.
Author(s)
Adrian Dragulescu
Examples
wb <- createWorkbook()
sheet <- createSheet(wb, "Sheet1")
ps
<- printSetup(sheet, landscape=TRUE, copies=3)
read.xlsx
Read the contents of a worksheet into an R data.frame
Description
Read the contents of a worksheet into an R data.frame.
Usage
read.xlsx(file, sheetIndex, sheetName=NULL, rowIndex=NULL,
colIndex=NULL, as.data.frame=TRUE, header=TRUE, colClasses=NA,
keepFormulas=FALSE, encoding="unknown", ...)
read.xlsx2(file, sheetIndex, sheetName=NULL, startRow=1,
startColumn=1, noRows=NULL, noColumns=NULL, as.data.frame=TRUE,
header=TRUE, colClasses="character")
16
read.xlsx
Arguments
file
the absolute path to the file which the data are to be read from.
sheetIndex
anumber representing the sheet index in the workbook.
sheetName
acharacter string with the sheet name.
rowIndex
anumeric vector indicating the rows you want to extract. If NULL, all rows
found will be extracted.
colIndex
anumeric vector indicating the cols you want to extract. If NULL, all columns
found will be extracted.
as.data.frame
alogical value indicating if the result should be coerced into a data.frame.
If FALSE, the result is a list with one element for each column.
header
alogicalvalueindicatingwhetherthe firstrowcorrespondingtothefirst element
of the rowIndex vector contains the names of the variables.
colClasses
For read.xlsx a character vector that represent the class of each column.
Recycled as necessary, or if the character vector is named, unspecified values
are takento be NA. For read.xlsx2 Acharacter vector, recycled if necessary.
Only numeric and character values are allowed.
keepFormulas a logicalvalue indicating if Excel formulas should be shownas text inR and not
evaluated before bringing themin.
encoding
encoding to be assumed for input strings. Seeread.table.
startRow
an numeric specifying the index ofstarting row.
startColumn an numeric specifying the index ofstarting column.
noRows
an numeric specifying how many rows to pull. If NULL, read all the rows in the
sheet.
noColumns
an numeric specifying how many columns to pull. If NULL, read all the colums
from the startRow.
...
other arguments to data.frame,forexample stringsAsFactors
Details
The read.xlsx function provides a high level API for reading data from an Excel 2007 work-
sheet. It calls several low level functions in the process. Its goal is to provide the conveniency of
read.tablebyborrowingfromitssignature.
The function pulls the value of each non empty cell in the worksheetintoa vector oftype list by
preserving the data type. If as.data.frame=TRUE, this vector of lists is then formatted into a
rectangular shape. Special care is needed forworksheets with ragged data.
The class type of the variable corresponding to one column in the worksheet is taken from the class
of the first non empty cell in that column. If you need to impose a specific class type on a variable,
use the colClasses argument.
Excel internally stores dates anddatetimes as numericvalues,anddoes not keep track oftime zones
and DST.When a datetime column is brought intoR,it is convertedto POSIXct class with a GMT
timezone. Occasional rounding errors may appear and the R and Excel string representation my
differ by one second. For read.xlsx2 bring in a datetime column as a numeric one and then
convert to class POSIXct or Date.
Row
17
The read.xlsx2 function does more work in Java so it achieves much better performance. The
result of read.xlsx2 will in general be different from read.xlsx. More performance gains
canbemade forparticular cases;readthe sourcecodetounderstandtheapproach in read.xlsx2.
Value
Adata.frame or a list, depending on the as.data.frame argument.
Author(s)
Adrian Dragulescu
See Also
write.xlsxforwritingxlsxdocuments. Seealso saveWorkbookforreadingaworkbook
into R for further processing andsaveWorkbook for saving a workbook to file.
Examples
## Not run:
file <- system.file("tests", "test_import.xlsx", package = "xlsx")
res <- read.xlsx(file, 1)
# read first sheet
head(res)
#
NA. Population Income Illiteracy Life.Exp Murder HS.Grad Frost
Area
# 1
Alabama
3615
3624
2.1
69.05
15.1
41.3
20
50708
# 2
Alaska
365
6315
1.5
69.31
11.3
66.7
152 566432
# 3
Arizona
2212
4530
1.8
70.55
7.8
58.1
15 113417
# 4
Arkansas
2110
3378
1.9
70.66
10.1
39.9
65
51945
# 5 California
21198
5114
1.1
71.71
10.3
62.6
20 156361
# 6
Colorado
2541
4884
0.7
72.06
6.8
63.9
166 103766
# >
# To convert an Excel datetime colum to POSIXct, do something like:
#
as.POSIXct((x-25569)
*
86400, tz="GMT", origin="1970-01-01")
# For Dates, use a conversion like:
#
as.Date(x-25569, origin="1970-01-01")
res2 <- read.xlsx2(file, 1)
# all columns returned characters
## End(Not run)
Row
Functions to manipulate rows of a worksheet.
Description
Functions to manipulate rows of a worksheet.
18
Row
Usage
createRow(sheet, rowIndex=1:5)
getRows(sheet, rowIndex=NULL)
removeRow(sheet, rows=NULL)
Arguments
sheet
aworksheetobjectasreturnedbycreateSheetorby subsettinggetSheets.
rowIndex
anumeric vectorspecifying the index of rows to create. For getRows, a NULL
value will return all non empty rows.
rows
alist of Row objects.
Details
removeRow is just a convenience wrapper to remove the rows from the sheet (before saving).
Internally it calls lapply.
Value
ForgetRows a listof java object references each pointingto a row. The list is namedwith the row
number.
Author(s)
Adrian Dragulescu
See Also
To extract the cells froma given row, seeCell.
Examples
file <- system.file("tests", "test_import.xlsx", package = "xlsx")
wb <- loadWorkbook(file)
sheets <- getSheets(wb)
sheet <- sheets[[2]]
rows
<- getRows(sheet)
# get all the rows
# see all the available java methods that you can call
.jmethods(rows[[1]])
# for example
rows[[1]]$getRowNum()
# zero based index in Java
Sheet
19
removeRow(sheet, rows)
# remove them all
Sheet
Functions to manipulate worksheets.
Description
Functions to manipulate worksheets.
Usage
createSheet(wb, sheetName="Sheet 1")
removeSheet(wb, sheetName="Sheet 1")
getSheets(wb)
Arguments
wb
aworkbook object as returned bycreateWorksheet or loadWorksheet.
sheetName
acharacter specifying the name ofthe worksheet to create, or remove.
Value
createSheet returns the created Sheet object.
getSheetsreturns alistofjavaobjectreferences each pointing to anworksheet. The list isnamed
with the sheet names.
Author(s)
Adrian Dragulescu
See Also
To extract rows from a given sheet,seeRow.
Examples
file <- system.file("tests", "test_import.xlsx", package = "xlsx")
wb <- loadWorkbook(file)
sheets <- getSheets(wb)
20
Workbook
sheet
<- sheets[[2]]
# extract the second sheet
# see all the available java methods that you can call
.jmethods(sheet)
# for example
sheet$getLastRowNum()
Workbook
Functions to manipulate Excel 2007 workbooks.
Description
Functions to manipulate Excel 2007 workbooks.
Usage
createWorkbook(type="xlsx")
loadWorkbook(file)
saveWorkbook(wb, file)
Arguments
type
aString, either xlsx for Excel 2007 OOXML format, or xls for Excel 95
binary format.
file
the absolute path to the file which the data are to be read from.
wb
aworkbook object as returned by createWorkbook orloadWorkbook.
Details
createWorkbook creates an empty workbook object.
loadWorkbook loads a workbook from a file.
saveWorkbook saves an existing workook to an Excel 2007 file.
Value
createWorkbook returns a java object reference pointing to an empty workbook object.
loadWorkbook creates a java object reference corresponding to the file to load.
Author(s)
Adrian Dragulescu
Documents you may be interested
Documents you may be interested