Functions to manipulate Excel 2007 workbooks.

Source: R/Workbook.R

createWorkbook creates an empty workbook object.

createWorkbook(type = "xlsx")

loadWorkbook(file, password = NULL)

saveWorkbook(wb, file, password = NULL)

Arguments

type

a String, either xlsx for Excel 2007 OOXML format, or xls for Excel 95 binary format.

file

the path to the file you intend to read or write. Can be an xls or xlsx format.

password

a String with the password.

wb

a workbook object as returned by createWorkbook or loadWorkbook.

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.

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 Excel 2007 OOXML format only. Note that in Linux, LibreOffice is not able to read password protected spreadsheets.

See also

write.xlsx for writing a data.frame to an xlsx file. read.xlsx for reading the content of a xlsx worksheet into a data.frame. To extract worksheets and manipulate them, see Worksheet.

Author

Adrian Dragulescu

Examples



wb <- createWorkbook()

# see all the available java methods that you can call
rJava::.jmethods(wb)
#>   [1] "public org.apache.poi.ss.usermodel.Row$MissingCellPolicy org.apache.poi.xssf.usermodel.XSSFWorkbook.getMissingCellPolicy()"                                         
#>   [2] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.setMissingCellPolicy(org.apache.poi.ss.usermodel.Row$MissingCellPolicy)"                                     
#>   [3] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.setActiveSheet(int)"                                                                                         
#>   [4] "public int org.apache.poi.xssf.usermodel.XSSFWorkbook.getFirstVisibleTab()"                                                                                         
#>   [5] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.setFirstVisibleTab(int)"                                                                                     
#>   [6] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.setPrintArea(int,java.lang.String)"                                                                          
#>   [7] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.setPrintArea(int,int,int,int,int)"                                                                           
#>   [8] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.setRepeatingRowsAndColumns(int,int,int,int,int)"                                                             
#>   [9] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.setSelectedTab(int)"                                                                                         
#>  [10] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.setSheetName(int,java.lang.String)"                                                                          
#>  [11] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.setSheetOrder(java.lang.String,int)"                                                                         
#>  [12] "public org.apache.poi.xssf.model.SharedStringsTable org.apache.poi.xssf.usermodel.XSSFWorkbook.getSharedStringSource()"                                             
#>  [13] "public org.apache.poi.xssf.model.StylesTable org.apache.poi.xssf.usermodel.XSSFWorkbook.getStylesSource()"                                                          
#>  [14] "public org.apache.poi.xssf.model.ThemesTable org.apache.poi.xssf.usermodel.XSSFWorkbook.getTheme()"                                                                 
#>  [15] "public org.apache.poi.ss.usermodel.CreationHelper org.apache.poi.xssf.usermodel.XSSFWorkbook.getCreationHelper()"                                                   
#>  [16] "public org.apache.poi.xssf.usermodel.XSSFCreationHelper org.apache.poi.xssf.usermodel.XSSFWorkbook.getCreationHelper()"                                             
#>  [17] "public java.util.List org.apache.poi.xssf.usermodel.XSSFWorkbook.getAllEmbedds() throws org.apache.poi.openxml4j.exceptions.OpenXML4JException"                     
#>  [18] "public boolean org.apache.poi.xssf.usermodel.XSSFWorkbook.isSheetHidden(int)"                                                                                       
#>  [19] "public boolean org.apache.poi.xssf.usermodel.XSSFWorkbook.isSheetVeryHidden(int)"                                                                                   
#>  [20] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.setSheetHidden(int,int)"                                                                                     
#>  [21] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.setSheetHidden(int,boolean)"                                                                                 
#>  [22] "public org.apache.poi.xssf.model.CalculationChain org.apache.poi.xssf.usermodel.XSSFWorkbook.getCalculationChain()"                                                 
#>  [23] "public int org.apache.poi.xssf.usermodel.XSSFWorkbook.getActiveSheetIndex()"                                                                                        
#>  [24] "public org.apache.poi.xssf.usermodel.XSSFCellStyle org.apache.poi.xssf.usermodel.XSSFWorkbook.getCellStyleAt(short)"                                                
#>  [25] "public org.apache.poi.ss.usermodel.CellStyle org.apache.poi.xssf.usermodel.XSSFWorkbook.getCellStyleAt(short)"                                                      
#>  [26] "public org.apache.poi.ss.usermodel.Font org.apache.poi.xssf.usermodel.XSSFWorkbook.getFontAt(short)"                                                                
#>  [27] "public org.apache.poi.xssf.usermodel.XSSFFont org.apache.poi.xssf.usermodel.XSSFWorkbook.getFontAt(short)"                                                          
#>  [28] "public org.apache.poi.ss.usermodel.Name org.apache.poi.xssf.usermodel.XSSFWorkbook.getNameAt(int)"                                                                  
#>  [29] "public org.apache.poi.xssf.usermodel.XSSFName org.apache.poi.xssf.usermodel.XSSFWorkbook.getNameAt(int)"                                                            
#>  [30] "public int org.apache.poi.xssf.usermodel.XSSFWorkbook.getNameIndex(java.lang.String)"                                                                               
#>  [31] "public short org.apache.poi.xssf.usermodel.XSSFWorkbook.getNumCellStyles()"                                                                                         
#>  [32] "public short org.apache.poi.xssf.usermodel.XSSFWorkbook.getNumberOfFonts()"                                                                                         
#>  [33] "public int org.apache.poi.xssf.usermodel.XSSFWorkbook.getNumberOfNames()"                                                                                           
#>  [34] "public int org.apache.poi.xssf.usermodel.XSSFWorkbook.getNumberOfSheets()"                                                                                          
#>  [35] "public java.lang.String org.apache.poi.xssf.usermodel.XSSFWorkbook.getPrintArea(int)"                                                                               
#>  [36] "public org.apache.poi.ss.usermodel.Sheet org.apache.poi.xssf.usermodel.XSSFWorkbook.getSheet(java.lang.String)"                                                     
#>  [37] "public org.apache.poi.xssf.usermodel.XSSFSheet org.apache.poi.xssf.usermodel.XSSFWorkbook.getSheet(java.lang.String)"                                               
#>  [38] "public org.apache.poi.xssf.usermodel.XSSFSheet org.apache.poi.xssf.usermodel.XSSFWorkbook.getSheetAt(int)"                                                          
#>  [39] "public org.apache.poi.ss.usermodel.Sheet org.apache.poi.xssf.usermodel.XSSFWorkbook.getSheetAt(int)"                                                                
#>  [40] "public int org.apache.poi.xssf.usermodel.XSSFWorkbook.getSheetIndex(java.lang.String)"                                                                              
#>  [41] "public int org.apache.poi.xssf.usermodel.XSSFWorkbook.getSheetIndex(org.apache.poi.ss.usermodel.Sheet)"                                                             
#>  [42] "public java.lang.String org.apache.poi.xssf.usermodel.XSSFWorkbook.getSheetName(int)"                                                                               
#>  [43] "public boolean org.apache.poi.xssf.usermodel.XSSFWorkbook.isMacroEnabled()"                                                                                         
#>  [44] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.removeName(int)"                                                                                             
#>  [45] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.removeName(java.lang.String)"                                                                                
#>  [46] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.removePrintArea(int)"                                                                                        
#>  [47] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.removeSheetAt(int)"                                                                                          
#>  [48] "public org.apache.poi.xssf.usermodel.XSSFCellStyle org.apache.poi.xssf.usermodel.XSSFWorkbook.createCellStyle()"                                                    
#>  [49] "public org.apache.poi.ss.usermodel.CellStyle org.apache.poi.xssf.usermodel.XSSFWorkbook.createCellStyle()"                                                          
#>  [50] "public org.apache.poi.ss.usermodel.DataFormat org.apache.poi.xssf.usermodel.XSSFWorkbook.createDataFormat()"                                                        
#>  [51] "public org.apache.poi.xssf.usermodel.XSSFDataFormat org.apache.poi.xssf.usermodel.XSSFWorkbook.createDataFormat()"                                                  
#>  [52] "public org.apache.poi.ss.usermodel.Font org.apache.poi.xssf.usermodel.XSSFWorkbook.createFont()"                                                                    
#>  [53] "public org.apache.poi.xssf.usermodel.XSSFFont org.apache.poi.xssf.usermodel.XSSFWorkbook.createFont()"                                                              
#>  [54] "public org.apache.poi.ss.usermodel.Name org.apache.poi.xssf.usermodel.XSSFWorkbook.createName()"                                                                    
#>  [55] "public org.apache.poi.xssf.usermodel.XSSFName org.apache.poi.xssf.usermodel.XSSFWorkbook.createName()"                                                              
#>  [56] "public org.apache.poi.ss.usermodel.Sheet org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet()"                                                                  
#>  [57] "public org.apache.poi.xssf.usermodel.XSSFSheet org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet()"                                                            
#>  [58] "public org.apache.poi.ss.usermodel.Sheet org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(java.lang.String)"                                                  
#>  [59] "public org.apache.poi.xssf.usermodel.XSSFSheet org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(java.lang.String)"                                            
#>  [60] "public org.apache.poi.ss.usermodel.Font org.apache.poi.xssf.usermodel.XSSFWorkbook.findFont(short,short,short,java.lang.String,boolean,boolean,short,byte)"         
#>  [61] "public org.apache.poi.xssf.usermodel.XSSFFont org.apache.poi.xssf.usermodel.XSSFWorkbook.findFont(short,short,short,java.lang.String,boolean,boolean,short,byte)"   
#>  [62] "public java.util.List org.apache.poi.xssf.usermodel.XSSFWorkbook.getAllPictures()"                                                                                  
#>  [63] "public org.apache.poi.ss.usermodel.Name org.apache.poi.xssf.usermodel.XSSFWorkbook.getName(java.lang.String)"                                                       
#>  [64] "public org.apache.poi.xssf.usermodel.XSSFName org.apache.poi.xssf.usermodel.XSSFWorkbook.getName(java.lang.String)"                                                 
#>  [65] "public java.util.Iterator org.apache.poi.xssf.usermodel.XSSFWorkbook.iterator()"                                                                                    
#>  [66] "public boolean org.apache.poi.xssf.usermodel.XSSFWorkbook.isHidden()"                                                                                               
#>  [67] "public java.util.Collection org.apache.poi.xssf.usermodel.XSSFWorkbook.getCustomXMLMappings()"                                                                      
#>  [68] "public org.apache.poi.xssf.model.MapInfo org.apache.poi.xssf.usermodel.XSSFWorkbook.getMapInfo()"                                                                   
#>  [69] "public boolean org.apache.poi.xssf.usermodel.XSSFWorkbook.isStructureLocked()"                                                                                      
#>  [70] "public boolean org.apache.poi.xssf.usermodel.XSSFWorkbook.isWindowsLocked()"                                                                                        
#>  [71] "public boolean org.apache.poi.xssf.usermodel.XSSFWorkbook.isRevisionLocked()"                                                                                       
#>  [72] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.lockStructure()"                                                                                             
#>  [73] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.unLockStructure()"                                                                                           
#>  [74] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.lockWindows()"                                                                                               
#>  [75] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.unLockWindows()"                                                                                             
#>  [76] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.lockRevision()"                                                                                              
#>  [77] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.unLockRevision()"                                                                                            
#>  [78] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.addToolPack(org.apache.poi.ss.formula.udf.UDFFinder)"                                                        
#>  [79] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.setForceFormulaRecalculation(boolean)"                                                                       
#>  [80] "public boolean org.apache.poi.xssf.usermodel.XSSFWorkbook.getForceFormulaRecalculation()"                                                                           
#>  [81] "public org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook org.apache.poi.xssf.usermodel.XSSFWorkbook.getCTWorkbook()"                                   
#>  [82] "public int org.apache.poi.xssf.usermodel.XSSFWorkbook.addPicture(java.io.InputStream,int) throws java.io.IOException"                                               
#>  [83] "public int org.apache.poi.xssf.usermodel.XSSFWorkbook.addPicture(byte[],int)"                                                                                       
#>  [84] "public org.apache.poi.xssf.usermodel.XSSFSheet org.apache.poi.xssf.usermodel.XSSFWorkbook.cloneSheet(int)"                                                          
#>  [85] "public org.apache.poi.ss.usermodel.Sheet org.apache.poi.xssf.usermodel.XSSFWorkbook.cloneSheet(int)"                                                                
#>  [86] "public void org.apache.poi.xssf.usermodel.XSSFWorkbook.setHidden(boolean)"                                                                                          
#>  [87] "public static org.apache.poi.openxml4j.opc.OPCPackage org.apache.poi.POIXMLDocument.openPackage(java.lang.String) throws java.io.IOException"                       
#>  [88] "public static boolean org.apache.poi.POIXMLDocument.hasOOXMLHeader(java.io.InputStream) throws java.io.IOException"                                                 
#>  [89] "public org.apache.poi.openxml4j.opc.OPCPackage org.apache.poi.POIXMLDocument.getPackage()"                                                                          
#>  [90] "public org.apache.poi.POIXMLProperties org.apache.poi.POIXMLDocument.getProperties()"                                                                               
#>  [91] "public final void org.apache.poi.POIXMLDocument.write(java.io.OutputStream) throws java.io.IOException"                                                             
#>  [92] "public final java.util.List org.apache.poi.POIXMLDocumentPart.getRelations()"                                                                                       
#>  [93] "public final org.apache.poi.openxml4j.opc.PackageRelationship org.apache.poi.POIXMLDocumentPart.getPackageRelationship()"                                           
#>  [94] "public final org.apache.poi.POIXMLDocumentPart org.apache.poi.POIXMLDocumentPart.createRelationship(org.apache.poi.POIXMLRelation,org.apache.poi.POIXMLFactory)"    
#>  [95] "public final org.apache.poi.POIXMLDocumentPart org.apache.poi.POIXMLDocumentPart.createRelationship(org.apache.poi.POIXMLRelation,org.apache.poi.POIXMLFactory,int)"
#>  [96] "public final org.apache.poi.openxml4j.opc.PackagePart org.apache.poi.POIXMLDocumentPart.getPackagePart()"                                                           
#>  [97] "public java.lang.String org.apache.poi.POIXMLDocumentPart.toString()"                                                                                               
#>  [98] "public final org.apache.poi.POIXMLDocumentPart org.apache.poi.POIXMLDocumentPart.getParent()"                                                                       
#>  [99] "public final void org.apache.poi.POIXMLDocumentPart.addRelation(java.lang.String,org.apache.poi.POIXMLDocumentPart)"                                                
#> [100] "public final org.apache.poi.POIXMLDocumentPart org.apache.poi.POIXMLDocumentPart.getRelationById(java.lang.String)"                                                 
#> [101] "public final java.lang.String org.apache.poi.POIXMLDocumentPart.getRelationId(org.apache.poi.POIXMLDocumentPart)"                                                   
#> [102] "public final native void java.lang.Object.wait(long) throws java.lang.InterruptedException"                                                                         
#> [103] "public final void java.lang.Object.wait(long,int) throws java.lang.InterruptedException"                                                                            
#> [104] "public final void java.lang.Object.wait() throws java.lang.InterruptedException"                                                                                    
#> [105] "public boolean java.lang.Object.equals(java.lang.Object)"                                                                                                           
#> [106] "public native int java.lang.Object.hashCode()"                                                                                                                      
#> [107] "public final native java.lang.Class java.lang.Object.getClass()"                                                                                                    
#> [108] "public final native void java.lang.Object.notify()"                                                                                                                 
#> [109] "public final native void java.lang.Object.notifyAll()"                                                                                                              
#> [110] "public default java.util.Spliterator java.lang.Iterable.spliterator()"                                                                                              
#> [111] "public default void java.lang.Iterable.forEach(java.util.function.Consumer)"                                                                                        

# for example
wb$getNumberOfSheets()   # no sheet yet!
#> [1] 0

# loadWorkbook("C:/Temp/myFile.xls")