Functions to manipulate worksheets.
getSheets(wb)
createSheet(wb, sheetName = "Sheet1")
removeSheet(wb, sheetName = "Sheet1")a workbook object as returned by createWorksheet or
loadWorksheet.
a character specifying the name of the worksheet to create, or remove.
createSheet returns the created Sheet object.
getSheets returns a list of java object references each pointing to
an worksheet. The list is named with the sheet names.
To extract rows from a given sheet, see Row.
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 call
rJava::.jmethods(sheet)
#> [1] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isFormatColumnsLocked()"
#> [2] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isFormatRowsLocked()"
#> [3] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isInsertColumnsLocked()"
#> [4] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isInsertHyperlinksLocked()"
#> [5] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isInsertRowsLocked()"
#> [6] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isPivotTablesLocked()"
#> [7] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isSortLocked()"
#> [8] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isObjectsLocked()"
#> [9] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isScenariosLocked()"
#> [10] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isSelectLockedCellsLocked()"
#> [11] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isSelectUnlockedCellsLocked()"
#> [12] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isSheetLocked()"
#> [13] "public void org.apache.poi.xssf.usermodel.XSSFSheet.enableLocking()"
#> [14] "public void org.apache.poi.xssf.usermodel.XSSFSheet.disableLocking()"
#> [15] "public void org.apache.poi.xssf.usermodel.XSSFSheet.lockAutoFilter()"
#> [16] "public void org.apache.poi.xssf.usermodel.XSSFSheet.lockDeleteColumns()"
#> [17] "public void org.apache.poi.xssf.usermodel.XSSFSheet.lockDeleteRows()"
#> [18] "public void org.apache.poi.xssf.usermodel.XSSFSheet.lockFormatCells()"
#> [19] "public void org.apache.poi.xssf.usermodel.XSSFSheet.lockFormatColumns()"
#> [20] "public void org.apache.poi.xssf.usermodel.XSSFSheet.lockFormatRows()"
#> [21] "public void org.apache.poi.xssf.usermodel.XSSFSheet.lockInsertColumns()"
#> [22] "public void org.apache.poi.xssf.usermodel.XSSFSheet.lockInsertHyperlinks()"
#> [23] "public void org.apache.poi.xssf.usermodel.XSSFSheet.lockInsertRows()"
#> [24] "public void org.apache.poi.xssf.usermodel.XSSFSheet.lockPivotTables()"
#> [25] "public void org.apache.poi.xssf.usermodel.XSSFSheet.lockSort()"
#> [26] "public void org.apache.poi.xssf.usermodel.XSSFSheet.lockObjects()"
#> [27] "public void org.apache.poi.xssf.usermodel.XSSFSheet.lockScenarios()"
#> [28] "public void org.apache.poi.xssf.usermodel.XSSFSheet.lockSelectLockedCells()"
#> [29] "public void org.apache.poi.xssf.usermodel.XSSFSheet.lockSelectUnlockedCells()"
#> [30] "public int org.apache.poi.xssf.usermodel.XSSFSheet.addMergedRegion(org.apache.poi.ss.util.CellRangeAddress)"
#> [31] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setVerticallyCenter(boolean)"
#> [32] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setHorizontallyCenter(boolean)"
#> [33] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.getHorizontallyCenter()"
#> [34] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.getVerticallyCenter()"
#> [35] "public void org.apache.poi.xssf.usermodel.XSSFSheet.removeMergedRegion(int)"
#> [36] "public int org.apache.poi.xssf.usermodel.XSSFSheet.getNumMergedRegions()"
#> [37] "public org.apache.poi.ss.util.CellRangeAddress org.apache.poi.xssf.usermodel.XSSFSheet.getMergedRegion(int)"
#> [38] "public java.util.Iterator org.apache.poi.xssf.usermodel.XSSFSheet.rowIterator()"
#> [39] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setAutobreaks(boolean)"
#> [40] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setDisplayGuts(boolean)"
#> [41] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setDisplayZeros(boolean)"
#> [42] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isDisplayZeros()"
#> [43] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setFitToPage(boolean)"
#> [44] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setRowSumsBelow(boolean)"
#> [45] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setRowSumsRight(boolean)"
#> [46] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.getAutobreaks()"
#> [47] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.getDisplayGuts()"
#> [48] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.getFitToPage()"
#> [49] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.getRowSumsBelow()"
#> [50] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.getRowSumsRight()"
#> [51] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isPrintGridlines()"
#> [52] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setPrintGridlines(boolean)"
#> [53] "public org.apache.poi.ss.usermodel.PrintSetup org.apache.poi.xssf.usermodel.XSSFSheet.getPrintSetup()"
#> [54] "public org.apache.poi.xssf.usermodel.XSSFPrintSetup org.apache.poi.xssf.usermodel.XSSFSheet.getPrintSetup()"
#> [55] "public org.apache.poi.ss.usermodel.Header org.apache.poi.xssf.usermodel.XSSFSheet.getHeader()"
#> [56] "public org.apache.poi.ss.usermodel.Footer org.apache.poi.xssf.usermodel.XSSFSheet.getFooter()"
#> [57] "public double org.apache.poi.xssf.usermodel.XSSFSheet.getMargin(short)"
#> [58] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setMargin(short,double)"
#> [59] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.getProtect()"
#> [60] "public void org.apache.poi.xssf.usermodel.XSSFSheet.protectSheet(java.lang.String)"
#> [61] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.getScenarioProtect()"
#> [62] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setZoom(int)"
#> [63] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setZoom(int,int)"
#> [64] "public short org.apache.poi.xssf.usermodel.XSSFSheet.getTopRow()"
#> [65] "public short org.apache.poi.xssf.usermodel.XSSFSheet.getLeftCol()"
#> [66] "public org.apache.poi.xssf.usermodel.XSSFWorkbook org.apache.poi.xssf.usermodel.XSSFSheet.getWorkbook()"
#> [67] "public org.apache.poi.ss.usermodel.Workbook org.apache.poi.xssf.usermodel.XSSFSheet.getWorkbook()"
#> [68] "public static void org.apache.poi.xssf.usermodel.XSSFSheet.setCellComment(java.lang.String,org.apache.poi.xssf.usermodel.XSSFComment)"
#> [69] "public void org.apache.poi.xssf.usermodel.XSSFSheet.addHyperlink(org.apache.poi.xssf.usermodel.XSSFHyperlink)"
#> [70] "public java.lang.String org.apache.poi.xssf.usermodel.XSSFSheet.getActiveCell()"
#> [71] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setActiveCell(java.lang.String)"
#> [72] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.hasComments()"
#> [73] "public org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula org.apache.poi.xssf.usermodel.XSSFSheet.getSharedFormula(int)"
#> [74] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isAutoFilterLocked()"
#> [75] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isDeleteColumnsLocked()"
#> [76] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isDeleteRowsLocked()"
#> [77] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isFormatCellsLocked()"
#> [78] "public void org.apache.poi.xssf.usermodel.XSSFSheet.showInPane(int,int)"
#> [79] "public void org.apache.poi.xssf.usermodel.XSSFSheet.showInPane(short,short)"
#> [80] "public void org.apache.poi.xssf.usermodel.XSSFSheet.shiftRows(int,int,int,boolean,boolean)"
#> [81] "public void org.apache.poi.xssf.usermodel.XSSFSheet.shiftRows(int,int,int)"
#> [82] "public void org.apache.poi.xssf.usermodel.XSSFSheet.createFreezePane(int,int)"
#> [83] "public void org.apache.poi.xssf.usermodel.XSSFSheet.createFreezePane(int,int,int,int)"
#> [84] "public void org.apache.poi.xssf.usermodel.XSSFSheet.createSplitPane(int,int,int,int,int)"
#> [85] "public org.apache.poi.hssf.util.PaneInformation org.apache.poi.xssf.usermodel.XSSFSheet.getPaneInformation()"
#> [86] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setDisplayGridlines(boolean)"
#> [87] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isDisplayGridlines()"
#> [88] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setDisplayFormulas(boolean)"
#> [89] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isDisplayFormulas()"
#> [90] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setDisplayRowColHeadings(boolean)"
#> [91] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isDisplayRowColHeadings()"
#> [92] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setRowBreak(int)"
#> [93] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isRowBroken(int)"
#> [94] "public void org.apache.poi.xssf.usermodel.XSSFSheet.removeRowBreak(int)"
#> [95] "public int[] org.apache.poi.xssf.usermodel.XSSFSheet.getRowBreaks()"
#> [96] "public int[] org.apache.poi.xssf.usermodel.XSSFSheet.getColumnBreaks()"
#> [97] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setColumnBreak(int)"
#> [98] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isColumnBroken(int)"
#> [99] "public void org.apache.poi.xssf.usermodel.XSSFSheet.removeColumnBreak(int)"
#> [100] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setColumnGroupCollapsed(int,boolean)"
#> [101] "public void org.apache.poi.xssf.usermodel.XSSFSheet.groupColumn(int,int)"
#> [102] "public void org.apache.poi.xssf.usermodel.XSSFSheet.ungroupColumn(int,int)"
#> [103] "public void org.apache.poi.xssf.usermodel.XSSFSheet.groupRow(int,int)"
#> [104] "public void org.apache.poi.xssf.usermodel.XSSFSheet.ungroupRow(int,int)"
#> [105] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setRowGroupCollapsed(int,boolean)"
#> [106] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setDefaultColumnStyle(int,org.apache.poi.ss.usermodel.CellStyle)"
#> [107] "public void org.apache.poi.xssf.usermodel.XSSFSheet.autoSizeColumn(int,boolean)"
#> [108] "public void org.apache.poi.xssf.usermodel.XSSFSheet.autoSizeColumn(int)"
#> [109] "public org.apache.poi.xssf.usermodel.XSSFComment org.apache.poi.xssf.usermodel.XSSFSheet.getCellComment(int,int)"
#> [110] "public org.apache.poi.ss.usermodel.Comment org.apache.poi.xssf.usermodel.XSSFSheet.getCellComment(int,int)"
#> [111] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isSelected()"
#> [112] "public org.apache.poi.ss.usermodel.CellRange org.apache.poi.xssf.usermodel.XSSFSheet.setArrayFormula(java.lang.String,org.apache.poi.ss.util.CellRangeAddress)"
#> [113] "public org.apache.poi.ss.usermodel.CellRange org.apache.poi.xssf.usermodel.XSSFSheet.removeArrayFormula(org.apache.poi.ss.usermodel.Cell)"
#> [114] "public void org.apache.poi.xssf.usermodel.XSSFSheet.addValidationData(org.apache.poi.ss.usermodel.DataValidation)"
#> [115] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setRepeatingRows(org.apache.poi.ss.util.CellRangeAddress)"
#> [116] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setRepeatingColumns(org.apache.poi.ss.util.CellRangeAddress)"
#> [117] "public org.apache.poi.ss.usermodel.Row org.apache.poi.xssf.usermodel.XSSFSheet.createRow(int)"
#> [118] "public org.apache.poi.xssf.usermodel.XSSFRow org.apache.poi.xssf.usermodel.XSSFSheet.createRow(int)"
#> [119] "public void org.apache.poi.xssf.usermodel.XSSFSheet.removeRow(org.apache.poi.ss.usermodel.Row)"
#> [120] "public org.apache.poi.ss.usermodel.Row org.apache.poi.xssf.usermodel.XSSFSheet.getRow(int)"
#> [121] "public org.apache.poi.xssf.usermodel.XSSFRow org.apache.poi.xssf.usermodel.XSSFSheet.getRow(int)"
#> [122] "public int org.apache.poi.xssf.usermodel.XSSFSheet.getPhysicalNumberOfRows()"
#> [123] "public int org.apache.poi.xssf.usermodel.XSSFSheet.getFirstRowNum()"
#> [124] "public int org.apache.poi.xssf.usermodel.XSSFSheet.getLastRowNum()"
#> [125] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setColumnHidden(int,boolean)"
#> [126] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isColumnHidden(int)"
#> [127] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setRightToLeft(boolean)"
#> [128] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.isRightToLeft()"
#> [129] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setColumnWidth(int,int)"
#> [130] "public int org.apache.poi.xssf.usermodel.XSSFSheet.getColumnWidth(int)"
#> [131] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setDefaultColumnWidth(int)"
#> [132] "public int org.apache.poi.xssf.usermodel.XSSFSheet.getDefaultColumnWidth()"
#> [133] "public short org.apache.poi.xssf.usermodel.XSSFSheet.getDefaultRowHeight()"
#> [134] "public float org.apache.poi.xssf.usermodel.XSSFSheet.getDefaultRowHeightInPoints()"
#> [135] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setDefaultRowHeight(short)"
#> [136] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setDefaultRowHeightInPoints(float)"
#> [137] "public org.apache.poi.ss.usermodel.CellStyle org.apache.poi.xssf.usermodel.XSSFSheet.getColumnStyle(int)"
#> [138] "public java.lang.String org.apache.poi.xssf.usermodel.XSSFSheet.getSheetName()"
#> [139] "public org.apache.poi.ss.usermodel.DataValidationHelper org.apache.poi.xssf.usermodel.XSSFSheet.getDataValidationHelper()"
#> [140] "public org.apache.poi.ss.usermodel.AutoFilter org.apache.poi.xssf.usermodel.XSSFSheet.setAutoFilter(org.apache.poi.ss.util.CellRangeAddress)"
#> [141] "public org.apache.poi.xssf.usermodel.XSSFAutoFilter org.apache.poi.xssf.usermodel.XSSFSheet.setAutoFilter(org.apache.poi.ss.util.CellRangeAddress)"
#> [142] "public org.apache.poi.ss.usermodel.SheetConditionalFormatting org.apache.poi.xssf.usermodel.XSSFSheet.getSheetConditionalFormatting()"
#> [143] "public org.apache.poi.xssf.usermodel.XSSFSheetConditionalFormatting org.apache.poi.xssf.usermodel.XSSFSheet.getSheetConditionalFormatting()"
#> [144] "public org.apache.poi.ss.util.CellRangeAddress org.apache.poi.xssf.usermodel.XSSFSheet.getRepeatingRows()"
#> [145] "public org.apache.poi.ss.util.CellRangeAddress org.apache.poi.xssf.usermodel.XSSFSheet.getRepeatingColumns()"
#> [146] "public org.apache.poi.xssf.usermodel.XSSFTable org.apache.poi.xssf.usermodel.XSSFSheet.createTable()"
#> [147] "public java.util.List org.apache.poi.xssf.usermodel.XSSFSheet.getTables()"
#> [148] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setTabColor(int)"
#> [149] "public int org.apache.poi.xssf.usermodel.XSSFSheet.findEndOfRowOutlineGroup(int)"
#> [150] "public org.apache.poi.xssf.usermodel.helpers.ColumnHelper org.apache.poi.xssf.usermodel.XSSFSheet.getColumnHelper()"
#> [151] "public java.util.List org.apache.poi.xssf.usermodel.XSSFSheet.getDataValidations()"
#> [152] "public java.util.Iterator org.apache.poi.xssf.usermodel.XSSFSheet.iterator()"
#> [153] "public org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet org.apache.poi.xssf.usermodel.XSSFSheet.getCTWorksheet()"
#> [154] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setSelected(boolean)"
#> [155] "public org.apache.poi.ss.usermodel.Drawing org.apache.poi.xssf.usermodel.XSSFSheet.createDrawingPatriarch()"
#> [156] "public org.apache.poi.xssf.usermodel.XSSFDrawing org.apache.poi.xssf.usermodel.XSSFSheet.createDrawingPatriarch()"
#> [157] "public void org.apache.poi.xssf.usermodel.XSSFSheet.setForceFormulaRecalculation(boolean)"
#> [158] "public boolean org.apache.poi.xssf.usermodel.XSSFSheet.getForceFormulaRecalculation()"
#> [159] "public org.apache.poi.xssf.usermodel.XSSFComment org.apache.poi.xssf.usermodel.XSSFSheet.createComment()"
#> [160] "public org.apache.poi.xssf.usermodel.XSSFHyperlink org.apache.poi.xssf.usermodel.XSSFSheet.getHyperlink(int,int)"
#> [161] "public org.apache.poi.ss.usermodel.Footer org.apache.poi.xssf.usermodel.XSSFSheet.getOddFooter()"
#> [162] "public org.apache.poi.ss.usermodel.Footer org.apache.poi.xssf.usermodel.XSSFSheet.getEvenFooter()"
#> [163] "public org.apache.poi.ss.usermodel.Footer org.apache.poi.xssf.usermodel.XSSFSheet.getFirstFooter()"
#> [164] "public org.apache.poi.ss.usermodel.Header org.apache.poi.xssf.usermodel.XSSFSheet.getOddHeader()"
#> [165] "public org.apache.poi.ss.usermodel.Header org.apache.poi.xssf.usermodel.XSSFSheet.getEvenHeader()"
#> [166] "public org.apache.poi.ss.usermodel.Header org.apache.poi.xssf.usermodel.XSSFSheet.getFirstHeader()"
#> [167] "public int org.apache.poi.xssf.usermodel.XSSFSheet.getNumHyperlinks()"
#> [168] "public final java.util.List org.apache.poi.POIXMLDocumentPart.getRelations()"
#> [169] "public final org.apache.poi.openxml4j.opc.PackageRelationship org.apache.poi.POIXMLDocumentPart.getPackageRelationship()"
#> [170] "public final org.apache.poi.POIXMLDocumentPart org.apache.poi.POIXMLDocumentPart.createRelationship(org.apache.poi.POIXMLRelation,org.apache.poi.POIXMLFactory)"
#> [171] "public final org.apache.poi.POIXMLDocumentPart org.apache.poi.POIXMLDocumentPart.createRelationship(org.apache.poi.POIXMLRelation,org.apache.poi.POIXMLFactory,int)"
#> [172] "public final org.apache.poi.openxml4j.opc.PackagePart org.apache.poi.POIXMLDocumentPart.getPackagePart()"
#> [173] "public java.lang.String org.apache.poi.POIXMLDocumentPart.toString()"
#> [174] "public final org.apache.poi.POIXMLDocumentPart org.apache.poi.POIXMLDocumentPart.getParent()"
#> [175] "public final void org.apache.poi.POIXMLDocumentPart.addRelation(java.lang.String,org.apache.poi.POIXMLDocumentPart)"
#> [176] "public final org.apache.poi.POIXMLDocumentPart org.apache.poi.POIXMLDocumentPart.getRelationById(java.lang.String)"
#> [177] "public final java.lang.String org.apache.poi.POIXMLDocumentPart.getRelationId(org.apache.poi.POIXMLDocumentPart)"
#> [178] "public final native void java.lang.Object.wait(long) throws java.lang.InterruptedException"
#> [179] "public final void java.lang.Object.wait(long,int) throws java.lang.InterruptedException"
#> [180] "public final void java.lang.Object.wait() throws java.lang.InterruptedException"
#> [181] "public boolean java.lang.Object.equals(java.lang.Object)"
#> [182] "public native int java.lang.Object.hashCode()"
#> [183] "public final native java.lang.Class java.lang.Object.getClass()"
#> [184] "public final native void java.lang.Object.notify()"
#> [185] "public final native void java.lang.Object.notifyAll()"
#> [186] "public default java.util.Spliterator java.lang.Iterable.spliterator()"
#> [187] "public default void java.lang.Iterable.forEach(java.util.function.Consumer)"
# for example
sheet$getLastRowNum()
#> [1] 12