Movatterモバイル変換

Read, write, format Excel 2007 and Excel 97/2000/XP/2003 files

Description

Thexlsx package gives programatic control of Excel files using R. Ahigh level API allows the user to read a sheet of an xlsx document into adata.frame and write adata.frame to a file. Lower levelfunctionality permits the direct manipulation of sheets, rows and cells.For example, the user has control to set colors, fonts, data formats, addborders, hide/unhide sheets, add/remove rows, add/remove sheets, etc.

Details

Behind the scenes, thexlsx package uses a java library from theApache project,https://poi.apache.org/index.html. This Apache projectprovides a Java API to Microsoft Documents (Excel, Word, PowerPoint,Outlook, Visio, etc.) By using therJava package that links andJava, we can piggyback on the excellent work already done by the folks atthe Apache project and provide this functionality in R. Thexlsxpackage uses only a subset of the Apache POI project, namely the one dealingwith Excel files. All the necessary jar files are kept in packagexlsxjars that is imported by packagexlsx.

A collection of tests that can be used as examples are located in folder/tests/. They are a good source of examples of how to use thepackage.

Please seehttps://github.com/colearendt/xlsx/ for a Wiki and thedevelopment version. To report a bug, use the Issues page athttps://github.com/colearendt/xlsx/issues.

References

Apache POI project for Microsoft Excel format:https://poi.apache.org/components/spreadsheet/index.html.

The Java Doc detailing the classes:https://poi.apache.org/apidocs/index.html. This can be useful if youare looking for something that is not exposed in R as it may be available onthe Java side. Inspecting the source code for some the the functions inthis package can show you how to do it (even if you are Java shy.)

See Also

Workbook for ways to work withWorkbookobjects.

Examples

## Not run: library(xlsx)# example of reading xlsx sheetsfile <- system.file("tests", "test_import.xlsx", package = "xlsx")res <- read.xlsx(file, 2)  # read the second sheet# example of writing xlsx sheetsfile <- paste(tempfile(), "xlsx", sep=".")write.xlsx(USArrests, file=file)## End(Not run)

Functions to manipulate cells.

Description

Functions to manipulate cells.

Usage

createCell(row, colIndex = 1:5)getCells(row, colIndex = NULL, simplify = TRUE)setCellValue(cell, value, richTextString = FALSE, showNA = TRUE)getCellValue(cell, keepFormulas = FALSE, encoding = "unknown")

Arguments

Details

setCellValue writes the content of an R variable into the cell.Date andPOSIXct objects are passed in as numerical values.To format them as dates in Excel seeCellStyle.

These functions are not vectorized and should be used only for smallspreadsheets. UseCellBlock functionality to efficiently read/writeparts of a spreadsheet.

Value

createCell creates a matrix of lists, each element of the list beinga java object reference to an object of type Cell representing an emptycell. The dimnames of this matrix are taken from the names of the rows andthecolIndex variable.

getCells returns a list of java object references for all the cellsin the row ifcolIndex isNULL. If you want to extract only aspecific columns, setcolIndex to the column indices you areinterested.

getCellValue returns the value in the cell as an R object. Typeconversions are done behind the scene. This function is not vectorized.

Author(s)

Adrian Dragulescu

See Also

To format cells, seeCellStyle. For rows seeRow, for sheets seeSheet.

Examples

file <- system.file("tests", "test_import.xlsx", package = "xlsx")wb <- loadWorkbook(file)  sheets <- getSheets(wb)sheet <- sheets[['mixedTypes']]      # get second sheetrows  <- getRows(sheet)   # get all the rowscells <- getCells(rows)   # returns all non empty cellsvalues <- lapply(cells, getCellValue) # extract the values# write the months of the year in the first column of the spreadsheetind <- paste(2:13, ".2", sep="")mapply(setCellValue, cells[ind], month.name)##################################################################### make a new workbook with one sheet and 5x5 cellswb <- createWorkbook()sheet <- createSheet(wb, "Sheet1")rows  <- createRow(sheet, rowIndex=1:5)cells <- createCell(rows, colIndex=1:5) # populate the first column with Datesdays <- seq(as.Date("2013-01-01"), by="1 day", length.out=5)mapply(setCellValue, cells[,1], days)

Create and style a block of cells.

Description

Functions to create and style (not read) a block of cells. Use it toset/update cell values and cell styles in an efficient manner.

Usage

CellBlock(sheet, startRow, startColumn, noRows, noColumns, create = TRUE)is.CellBlock(cellBlock)## Default S3 method:CellBlock(sheet, startRow, startColumn, noRows, noColumns, create = TRUE)CB.setColData(  cellBlock,  x,  colIndex,  rowOffset = 0,  showNA = TRUE,  colStyle = NULL)CB.setRowData(  cellBlock,  x,  rowIndex,  colOffset = 0,  showNA = TRUE,  rowStyle = NULL)CB.setMatrixData(  cellBlock,  x,  startRow,  startColumn,  showNA = TRUE,  cellStyle = NULL)CB.setFill(cellBlock, fill, rowIndex, colIndex)CB.setFont(cellBlock, font, rowIndex, colIndex)CB.setBorder(cellBlock, border, rowIndex, colIndex)

Arguments

Details

Introduced in version 0.5.0 of the package, these functions speed up thecreation and styling of cells that are part of a "cell block" (a rectangularshaped group of cells). Use the functions above if you want to createefficiently a complex sheet with many styles. A simple by-column stylingcan be done by directly usingaddDataFrame. With thefunctionality provided here you can efficiently style individual cells, seethe example.

It is difficult to treatNA's consistently between R and Excel viaJava. Most likely, users of Excel will want to seeNA's as blankcells. In R characterNA's are simply characters, which for Excelmeans "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 madeto the pairs defined by(rowIndex, colIndex). This implies that thelength ofrowIndex andcolIndex are the same value. Anexception is made when eitherrowIndex orcolIndex have lengthone, when they will be expanded internally to match the length of the otherindex.

FunctionCB.setMatrixData works for numeric or character matrices.If the matrixx is not of numeric type it will be converted to acharacter matrix.

Value

ForCellBlock a cell block object.

ForCB.setColData,CB.setRowData,CB.setMatrixData,CB.setFill,CB.setFont,CB.setBorder nothing as hemodification to the workbook is done in place.

Author(s)

Adrian Dragulescu

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, 1:1000)  # Don't forget to save the workbook ...  # saveWorkbook(wb, file)

Functions to manipulate cells.

Description

Create and set cell styles.

Usage

CellStyle(  wb,  dataFormat = NULL,  alignment = NULL,  border = NULL,  fill = NULL,  font = NULL,  cellProtection = NULL)is.CellStyle(x)## Default S3 method:CellStyle(  wb,  dataFormat = NULL,  alignment = NULL,  border = NULL,  fill = NULL,  font = NULL,  cellProtection = NULL)setCellStyle(cell, cellStyle)getCellStyle(cell)

Arguments

Details

setCellStyle sets theCellStyle to oneCell object.

You need to have aWorkbook object to attach aCellStyleobject to it.

Since OS X 10.5 Apple dropped support for AWT on the main thread, soessentially you cannot use any graphics classes in R on OS X 10.5 since R issingle-threaded. (verbatim from Simon Urbanek). This implies that settingcolors on Mac will not work as is! A set of about 50 basic colors are stillavailable please see the javadocs.

For Excel 95/2000/XP/2003 the choice of colors is limited. SeeINDEXED_COLORS_ for the list of available colors.

Unspecified values for arguments are taken from the system locale.

Value

createCellStyle creates a CellStyle object.

is.CellStyle returnsTRUE if the argument is of class"CellStyle" andFALSE 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)  # you need to save the workbook now if you want to see this art## End(Not run)

CellStyle construction.

Description

Create cell styles.

Usage

## S3 method for class 'CellStyle'cs1 + object

Arguments

Details

The style of the argument object takes precedence over the style of argumentcs1.

Value

A CellStyle object.

Author(s)

Adrian Dragulescu

Examples

## Not run:   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)  # you need to save the workbook now if you want to see this art## End(Not run)

Functions to manipulate cell comments.

Description

These functions are not vectorized.

Usage

createCellComment(cell, string, author = NULL, visible = TRUE)removeCellComment(cell)getCellComment(cell)

Arguments

Value

createCellComment creates aComment object.

getCellComment returns a theComment object if it exists.

removeCellComment removes a comment from the given cell.

Author(s)

Adrian Dragulescu

See Also

For cells, seeCell. To format cells, seeCellStyle.

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")  # don't forget to save your workbook!

Functions to manipulate (contiguous) named ranges.

Description

These functions are provided for convenience only. Use directly the JavaAPI to access additional functionality.

Usage

createRange(rangeName, firstCell, lastCell)getRanges(wb)readRange(range, sheet, colClasses = "character")

Arguments

Value

getRanges returns the existing ranges as a list.

readRange reads the range into a data.frame.

createRange returns the created range object.

Author(s)

Adrian Dragulescu

Examples

file <- system.file("tests", "test_import.xlsx", package = "xlsx")wb <- loadWorkbook(file)sheet <- getSheets(wb)[["deletedFields"]]ranges <- getRanges(wb)# the call below fails on cran tests for MacOS.  You should see the# FAQ: https://code.google.com/p/rexcel/wiki/FAQ#res  <- readRange(ranges[[1]], sheet, colClasses="numeric") # read itranges[[1]]$getNameName()  # get its name# see all the available java methods that you can callrJava::.jmethods(ranges[[1]])# create a new named rangefirstCell <- sheet$getRow(14L)$getCell(4L)lastCell  <- sheet$getRow(20L)$getCell(7L)rangeName <- "Test2"# same issue on MacOS#createRange(rangeName, firstCell, lastCell)

Functions to do various spreadsheets effects.

Description

FunctionautoSizeColumn expands the column width to match the columncontents thus removing the ###### that you get when cell contents are largerthan 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

Details

You may need other functionality that is not exposed. Take a look at thejava docs and the source code of these functions for how you can implementit in R.

Value

addMergedRegion returns a numeric value to label the mergedregion. You should use this value as theind if you want toremoveMergedRegion.

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

Constants used in the project.

Description

Document some Apache POI constants used in the project.

Usage

HALIGN_STYLES_VALIGN_STYLES_BORDER_STYLES_FILL_STYLES_CELL_STYLES_INDEXED_COLORS_

Format

An object of classnumeric of length 7.

An object of classnumeric of length 4.

An object of classnumeric of length 14.

An object of classnumeric of length 19.

An object of classnumeric of length 44.

An object of classnumeric of length 48.

Value

A named vector.

Author(s)

Adrian Dragulescu

See Also

CellStyle for using thePOI_constants.

Functions to manipulate images in a spreadsheet.

Description

For now, the following image types are supported: dib, emf, jpeg, pict, png,wmf. Please note, that scaling works correctly only for workbooks with thedefault font size (Calibri 11pt for .xlsx). If the default font is changedthe resized image can be streched vertically or horizontally.

Usage

addPicture(file, sheet, scale = 1, startRow = 1, startColumn = 1)

Arguments

Details

Don't know how to remove an existing image yet.

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!

Function to manipulate print setup.

Description

Other settings are available but not exposed. Please see the java docs.

Usage

printSetup(  sheet,  fitHeight = NULL,  fitWidth = NULL,  copies = NULL,  draft = NULL,  footerMargin = NULL,  headerMargin = NULL,  landscape = FALSE,  pageStart = NULL,  paperSize = NULL,  noColor = NULL)

Arguments

Value

A reference to a java PrintSetup object.

Author(s)

Adrian Dragulescu

Examples

wb <- createWorkbook()sheet <- createSheet(wb, "Sheet1")ps   <- printSetup(sheet, landscape=TRUE, copies=3)

Functions to manipulate rows of a worksheet.

Description

removeRow is just a convenience wrapper to remove the rows from thesheet (before saving). Internally it callslapply.

Usage

createRow(sheet, rowIndex = 1:5)getRows(sheet, rowIndex = NULL)removeRow(sheet, rows = NULL)setRowHeight(rows, inPoints, multiplier = NULL)

Arguments

Value

ForgetRows a list of java object references each pointing toa row. The list is named with the row number.

Author(s)

Adrian Dragulescu

See Also

To extract the cells from a 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 callrJava::.jmethods(rows[[1]])# for examplerows[[1]]$getRowNum()   # zero based index in JavaremoveRow(sheet, rows)  # remove them all# create some rowrows  <- createRow(sheet, rowIndex=1:5)setRowHeight( rows, multiplier=3)  # 3 times bigger rows than the default

Functions to manipulate worksheets.

Description

Functions to manipulate worksheets.

Usage

getSheets(wb)createSheet(wb, sheetName = "Sheet1")removeSheet(wb, sheetName = "Sheet1")

Arguments

Value

createSheet returns the createdSheet object.

getSheets returns a list of java object references each pointing toan worksheet. The list is named 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)sheet  <- sheets[[2]]  # extract the second sheet# see all the available java methods that you can callrJava::.jmethods(sheet)# for examplesheet$getLastRowNum()

Functions to manipulate Excel 2007 workbooks.

Description

createWorkbook creates an empty workbook object.

Usage

createWorkbook(type = "xlsx")loadWorkbook(file, password = NULL)saveWorkbook(wb, file, password = NULL)

Arguments

Details

loadWorkbook loads a workbook from a file.

saveWorkbook saves an existing workook to an Excel 2007 file.

Reading or writing of password protected workbooks is supported for Excel2007 OOXML format only. Note that in Linux, LibreOffice is not able to readpassword protected spreadsheets.

Value

createWorkbook returns a java object reference pointing to anempty workbook object.

loadWorkbook creates a java object reference corresponding to thefile to load.

Author(s)

Adrian Dragulescu

See Also

write.xlsx for writing adata.frame to anxlsx file.read.xlsx for reading the content of axlsx worksheet into adata.frame. To extract worksheets andmanipulate them, seeWorksheet.

Examples

wb <- createWorkbook()# see all the available java methods that you can callrJava::.jmethods(wb)# for examplewb$getNumberOfSheets()   # no sheet yet!# loadWorkbook("C:/Temp/myFile.xls")

Add adata.frame to a sheet.

Description

Add adata.frame to a sheet, allowing for different column styles.Useful when constructing the spreadsheet from scratch.

Usage

addDataFrame(  x,  sheet,  col.names = TRUE,  row.names = TRUE,  startRow = 1,  startColumn = 1,  colStyle = NULL,  colnamesStyle = NULL,  rownamesStyle = NULL,  showNA = FALSE,  characterNA = "",  byrow = FALSE)

Arguments

Details

Starting with version 0.5.0 this function uses the functionality provided byCellBlock which results in a significant improvement in performancecompared with a cell by cell application ofsetCellValue andwith other previous atempts.

It is difficult to treatNA's consistently between R and Excel viaJava. Most likely, users of Excel will want to seeNA's as blankcells. In R characterNA's are simply characters, which for Excelmeans "NA".

The default formats for Date and DateTime columns can be changed via the twopackage optionsxlsx.date.format andxlsx.datetime.format.They need to be specified in Java date formathttps://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html.

Value

None. The modification to the workbook is done in place.

Author(s)

Adrian Dragulescu

Examples

  wb <- createWorkbook()  sheet  <- createSheet(wb, sheetName="addDataFrame1")  data <- data.frame(mon=month.abb[1:10], day=1:10, year=2000:2009,    date=seq(as.Date("1999-01-01"), by="1 year", length.out=10),    bool=c(TRUE, FALSE), log=log(1:10),    rnorm=10000*rnorm(10),    datetime=seq(as.POSIXct("2011-11-06 00:00:00", tz="GMT"), by="1 hour",      length.out=10))  cs1 <- CellStyle(wb) + Font(wb, isItalic=TRUE)           # rowcolumns  cs2 <- CellStyle(wb) + Font(wb, color="blue")  cs3 <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border()  # header  addDataFrame(data, sheet, startRow=3, startColumn=2, colnamesStyle=cs3,    rownamesStyle=cs1, colStyle=list(`2`=cs2, `3`=cs2))  # to change the default date format use something like this  # options(xlsx.date.format="dd MMM, yyyy")  # Don't forget to save the workbook ...  # saveWorkbook(wb, file)

Add a hyperlink to a cell.

Description

Add a hyperlink to a cell to point to an external resource.

Usage

addHyperlink(  cell,  address,  linkType = c("URL", "DOCUMENT", "EMAIL", "FILE"),  hyperlinkStyle = NULL)

Arguments

Details

The cell needs to have content before you add a hyperlink to it. Thecontents of the cells don't need to be the same as the address of thehyperlink. See the examples.

Value

None. The modification to the cell is done in place.

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  ## Add hyperlinks to a cell  cell <- cells[[1,1]]  address <- "https://poi.apache.org/"  setCellValue(cell, "click me!")  addHyperlink(cell, address)  # Don't forget to save the workbook ...

Force Refresh Pivot Tables and Formulae

Description

Functions to force formula calculation or refresh of pivottables when the Excel file is opened.

Usage

forceFormulaRefresh(file, output = NULL, verbose = FALSE)forcePivotTableRefresh(file, output = NULL, verbose = FALSE)

Arguments

Details

forcePivotTableRefresh forces pivot tables to be refreshed when the Excel file is opened.forceFormulaRefresh forces formulae to be recalculated when the Excel file is opened.

Value

Does not return any results

Author(s)

Tom Kwong

Examples

# Patch a file where its pivot tables are not recalculated when the file is opened## Not run: forcePivotTableRefresh("/tmp/file.xlsx")forcePivotTableRefresh("/tmp/file.xlsx", "/tmp/fixed_file.xlsx")## End(Not run)# Patch a file where its formulae are not recalculated when the file is opened## Not run: forceFormulaRefresh("/tmp/file.xlsx")forceFormulaRefresh("/tmp/file.xlsx", "/tmp/fixed_file.xlsx")## End(Not run)

Create an Alignment object.

Description

Create an Alignment object, useful when working with cell styles.

Usage

is.Alignment(x)Alignment(  horizontal = NULL,  vertical = NULL,  wrapText = FALSE,  rotation = 0,  indent = 0)

Arguments

Value

Alignment returns a list with components from the inputargument, and a class attribute "Alignment". Alignment objects are usedwhen constructing cell styles.

is.Alignment returnsTRUE if the argument is of class"Alignment" andFALSE otherwise.

Author(s)

Adrian Dragulescu

See Also

CellStyle for using the aAlignment object.

Examples

  # you can just use h for horizontal, since R does the matching for you  a1 <-  Alignment(h="ALIGN_CENTER", rotation=90) # centered and rotated!

Create an Border object.

Description

Create an Border object, useful when working with cell styles.

Usage

is.Border(x)Border(color = "black", position = "BOTTOM", pen = "BORDER_THIN")

Arguments

Details

The values for the color, position, or pen arguments are replicated to thelongest of them.

Value

Border returns a list with components from the inputargument, and a class attribute "Border". Border objects are used whenconstructing cell styles.

is.Border returnsTRUE if the argument is of class "Border"andFALSE otherwise.

Author(s)

Adrian Dragulescu

See Also

CellStyle for using the aBorder object.

Examples

  border <-  Border(color="red", position=c("TOP", "BOTTOM"),    pen=c("BORDER_THIN", "BORDER_THICK"))

Create a CellProtection object.

Description

Create a CellProtection object used for cell styles.

Usage

is.CellProtection(x)CellProtection(locked = TRUE, hidden = FALSE)

Arguments

Value

CellProtection returns a list with components from the inputargument, and a class attribute "CellProtection". CellProtection objectsare used when constructing cell styles.

is.CellProtection returnsTRUE if the argument is of class"CellProtection" andFALSE otherwise.

Author(s)

Adrian Dragulescu

See Also

CellStyle for using the aCellProtectionobject.

Examples

  font <-  CellProtection(locked=TRUE)

Create an DataFormat object.

Description

Create an DataFormat object, useful when working with cell styles.

Usage

is.DataFormat(df)DataFormat(x)

Arguments

Details

Specifying thedataFormat argument allows you to format the cell.For example, "#,##0.00" corresponds to using a comma separator for powers of1000 with two decimal places, "m/d/yyyy" can be used to format dates and isthe equivalent of 's MM/DD/YYYY format. To format datetimes use "m/d/yyyyh:mm:ss;@". To show negative values in red within parantheses with twodecimals and commas after power of 1000 use "#,##0.00_);[Red](#,##0.00)". Iam not aware of an official way to discover these strings. I find them outby recording a macro that formats a specific cell and then checking out theresulting VBA code. From there you can read thedataFormat code.

Value

DataFormat returns a list one component dataFormat, and aclass attribute "DataFormat". DataFormat objects are used when constructingcell styles.

is.DataFormat returnsTRUE if the argument is of class"DataFormat" andFALSE otherwise.

Author(s)

Adrian Dragulescu

See Also

CellStyle for using the aDataFormat object.

Examples

  df <-  DataFormat("#,##0.00")

Create an Fill object.

Description

Create an Fill object, useful when working with cell styles.

Usage

is.Fill(x)Fill(  foregroundColor = "lightblue",  backgroundColor = "lightblue",  pattern = "SOLID_FOREGROUND")

Arguments

Value

Fill returns a list with components from the input argument,and a class attribute "Fill". Fill objects are used when constructing cellstyles.

is.Fill returnsTRUE if the argument is of class "Fill" andFALSE otherwise.

Author(s)

Adrian Dragulescu

See Also

CellStyle for using the aFill object.

Examples

  fill <-  Fill()

Create a Font object.

Description

Create a Font object.

Usage

is.Font(x)Font(  wb,  color = NULL,  heightInPoints = NULL,  name = NULL,  isItalic = FALSE,  isStrikeout = FALSE,  isBold = FALSE,  underline = NULL,  boldweight = NULL)

Arguments

Details

Default values forNULL parameters are taken from Excel. So thedefault font color is black, the default font name is "Calibri", and thefont height in points is 11.

For Excel 95/2000/XP/2003, it is impossible to set the font to bold. Thislimitation may be removed in the future.

NOTE: You need to have aWorkbook object to attach aFontobject to it.

Value

Font returns a list with a java reference to aFontobject, and a class attribute "Font".

is.Font returnsTRUE if the argument is of class "Font" andFALSE otherwise.

Author(s)

Adrian Dragulescu

See Also

CellStyle for using the aFont object.

Examples

## Not run:   font <-  Font(wb, color="blue", isItalic=TRUE)## End(Not run)

Read the contents of a worksheet into an Rdata.frame.

Description

Theread.xlsx function provides a high level API for reading datafrom an Excel worksheet. It calls several low level functions in theprocess. Its goal is to provide the conveniency ofread.table by borrowing from its signature.

Usage

read.xlsx(  file,  sheetIndex,  sheetName = NULL,  rowIndex = NULL,  startRow = NULL,  endRow = NULL,  colIndex = NULL,  as.data.frame = TRUE,  header = TRUE,  colClasses = NA,  keepFormulas = FALSE,  encoding = "unknown",  password = NULL,  ...)read.xlsx2(  file,  sheetIndex,  sheetName = NULL,  startRow = 1,  colIndex = NULL,  endRow = NULL,  as.data.frame = TRUE,  header = TRUE,  colClasses = "character",  password = NULL,  ...)

Arguments

Details

The function pulls the value of each non empty cell in the worksheet into avector of typelist by preserving the data type. Ifas.data.frame=TRUE, this vector of lists is then formatted into arectangular shape. Special care is needed for worksheets with ragged data.

An attempt is made to guess the class type of the variable corresponding toeach column in the worksheet from the type of the first non empty cell inthat column. If you need to impose a specific class type on a variable, usethecolClasses argument. It is recommended to specify the columnclasses and not rely onR to guess them, unless in very simple cases.

Excel internally stores dates and datetimes as numeric values, and does notkeep track of time zones and DST. When a datetime column is brought into ,it is converted toPOSIXct class with aGMT timezone.Occasional rounding errors may appear and the and Excel stringrepresentation my differ by one second. Forread.xlsx2 bring in adatetime column as a numeric one and then convert to classPOSIXct orDate. Also rounding thePOSIXct column in R usually does thetrick too.

Theread.xlsx2 function does more work in Java so it achieves betterperformance (an order of magnitude faster on sheets with 100,000 cells ormore). The result ofread.xlsx2 will in general be different fromread.xlsx, because internallyread.xlsx2 usesreadColumns which is tailored for tabular data.

Reading of password protected workbooks is supported for Excel 2007 OOXMLformat only.

Value

A data.frame or a list, depending on theas.data.frameargument. If some of the columns are read as NA's it's an indication thatthecolClasses argument has not been set properly.

If the sheet is empty, returnNULL. If the sheet does not exist,return an error.

Author(s)

Adrian Dragulescu

See Also

write.xlsx for writingxlsx documents. SeealsoreadColumns for reading only a set of columns into R.

Examples

## Not run: file <- system.file("tests", "test_import.xlsx", package = "xlsx")res <- read.xlsx(file, 1)  # read first sheethead(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)## End(Not run)

Read a contiguous set of columns from sheet into an R data.frame

Description

Read a contiguous set of columns from sheet into an R data.frame. Uses theRInterface for speed.

Usage

readColumns(  sheet,  startColumn,  endColumn,  startRow,  endRow = NULL,  as.data.frame = TRUE,  header = TRUE,  colClasses = NA,  ...)

Arguments

Details

Use thereadColumns function when you want to read a rectangularblock of data from an Excel worksheet. If you request columns which areblank, these will be read in as empty character "" columns. Internally, theloop over columns is done in R, the loop over rows is done in Java, so thisfunction achieves good performance when number of rows >> number of columns.

Excel internally stores dates and datetimes as numeric values, and does notkeep track of time zones and DST. When a numeric column is formatted as adatetime, it will be converted intoPOSIXct class with aGMTtimezone. If you need aDate column, you need to specify explicitlyusingcolClasses argument.

For a numeric column Excels's errors and blank cells will be returned as NaNvalues. Excel's#N/A will be returned as NA. Formulas will beevaluated. For a chracter column, blank cells will be returned as "".

Value

A data.frame or a list, depending on theas.data.frameargument.

Author(s)

Adrian Dragulescu

See Also

read.xlsx2 for reading entire sheets. See alsoaddDataFrame for writing adata.frame to a sheet.

Examples

## Not run:   file <- system.file("tests", "test_import.xlsx", package = "xlsx")  wb     <- loadWorkbook(file)  sheets <- getSheets(wb)  sheet <- sheets[["all"]]  res <- readColumns(sheet, startColumn=3, endColumn=10, startRow=3,    endRow=7)  sheet <- sheets[["NAs"]]  res <- readColumns(sheet, 1, 6, 1,  colClasses=c("Date", "character",    "integer", rep("numeric", 2),  "POSIXct"))## End(Not run)

Read a contiguous set of rows into an R matrix

Description

Read a contiguous set of rows into an R character matrix. Uses theRInterface for speed.

Usage

readRows(sheet, startRow, endRow, startColumn, endColumn = NULL)

Arguments

Details

Use thereadRows function when you want to read a row or a blockblock of data from an Excel worksheet. Internally, the loop over rows isdone in R, and the loop over columns is done in Java, so this functionachieves good performance when number of rows << number of columns.

In general, you should prefer the functionreadColumns overthis one.

Value

A character matrix.

Author(s)

Adrian Dragulescu

See Also

read.xlsx2 for reading entire sheets. See alsoaddDataFrame for writing adata.frame to a sheet.

Examples

## Not run:   file <- system.file("tests", "test_import.xlsx", package = "xlsx")  wb     <- loadWorkbook(file)  sheets <- getSheets(wb)  sheet <- sheets[["all"]]  res <- readRows(sheet, startRow=3, endRow=7, startColumn=3, endColumn=10)## End(Not run)

Set Java Temp Directory

Description

Java sets the java temp directory to '/tmp' by default. However, this isusually not desirable in R. As a result, this function allows changing thatbehavior. Further, this function is fired on package load to ensure alltemp files are written to the R temp directory.

Usage

set_java_tmp_dir(tmp_dir = tempdir())get_java_tmp_dir()

Arguments

Details

On package load, we use 'getOption("xlsx.tempdir", tempdir())' for thedefault value, in case you want to have this value set by an option.

Value

The previous java temp directory (prior to any changes).

Write a data.frame to an Excel workbook.

Description

Write adata.frame to an Excel workbook.

Usage

write.xlsx(  x,  file,  sheetName = "Sheet1",  col.names = TRUE,  row.names = TRUE,  append = FALSE,  showNA = TRUE,  password = NULL)write.xlsx2(  x,  file,  sheetName = "Sheet1",  col.names = TRUE,  row.names = TRUE,  append = FALSE,  password = NULL,  ...)

Arguments

Details

This function provides a high level API for writing adata.frame toan Excel 2007 worksheet. It calls several low level functions in theprocess. Its goal is to provide the conveniency ofwrite.csv by borrowing from its signature.

Internally,write.xlsx uses a double loop in over all the elements ofthedata.frame so performance for very largedata.frame may bean issue. Please report if you experience slow performance. Dates andPOSIXct classes are formatted separately after the insertion. This alsoadds to processing time.

Ifx is not adata.frame it will be converted to one.

Functionwrite.xlsx2 usesaddDataFrame which speeds up theexecution compared towrite.xlsx by an order of magnitude for largespreadsheets (with more than 100,000 cells).

The default formats for Date and DateTime columns can be changed via the twopackage optionsxlsx.date.format andxlsx.datetime.format.They need to be specified in Java date formathttps://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html.

Writing of password protected workbooks is supported for Excel 2007 OOXMLformat only. Note that in Linux, LibreOffice is not able to read passwordprotected spreadsheets.

Author(s)

Adrian Dragulescu

See Also

read.xlsx for readingxlsx documents. SeealsoaddDataFrame for writing adata.frame to a sheet.

Examples

## Not run: file <- paste(tempdir(), "/usarrests.xlsx", sep="")res <- write.xlsx(USArrests, file)# to change the default date formatoldOpt <- options()options(xlsx.date.format="dd MMM, yyyy")write.xlsx(x, sheet) # where x is a data.frame with a Date column.options(oldOpt)      # revert back to defaults## End(Not run)