Movatterモバイル変換

openxlsx2

This R package is a modern reinterpretation of the widely usedpopularopenxlsx package. Similar to its predecessor, itsimplifies the creation of xlsx files by providing a clean interface forwriting, designing and editing worksheets. Based on a powerful XMLlibrary and focusing on modern programming flows in pipes or chains,openxlsx2 allows to break many new ground.

Installation

You can install the stable version ofopenxlsx2with:

install.packages('openxlsx2')

You can install the development version ofopenxlsx2fromGitHub with:

# install.packages("remotes")remotes::install_github("JanMarvin/openxlsx2")

Or fromr-universewith:

# Enable repository from janmarvinoptions(repos =c(janmarvin ='https://janmarvin.r-universe.dev',CRAN ='https://cloud.r-project.org'))# Download and install openxlsx2 in Rinstall.packages('openxlsx2')

Introduction

openxlsx2 aims to be the swiss knife for working withthe openxml spreadsheet formats xlsx, xlsm and (limited) xlsb (otherformats of other spreadsheet software are not supported). We offer twodifferent variants how to work withopenxlsx2.

• The first one is to simply work with R objects. It is possible toread (read_xlsx())and write (write_xlsx())data from and to files. We offer a number of options in the commands tosupport various features of the openxml format, including reading andwriting named ranges and tables. Furthermore, there are several ways toread certain information of an openxml spreadsheet without having openedit in a spreadsheet software before, e.g. to get the contained sheetnames or tables.
• As a second variantopenxlsx2 offers the work with socalledwbWorkbookobjects. Here an openxml file is read into a correspondingwbWorkbook object (wb_load())or a new one is created (wb_workbook()).Afterwards the object can be further modified using various functions.For example, worksheets can be added or removed, the layout of cells orentire worksheets can be changed, and cells can be modified (overwrittenor rewritten). Afterwards thewbWorkbook objects can bewritten as openxml files and processed by suitable spreadsheetsoftware.

Many examples how to work withopenxlsx2 are in ourmanualpages, thebookand in ourvignettes.You can find them under:

vignette(package ="openxlsx2")

Example

This is a basic example which shows you how to solve a commonproblem:

library(openxlsx2)# read xlsx or xlsm filespath<-system.file("extdata/openxlsx2_example.xlsx",package ="openxlsx2")read_xlsx(path)#>     Var1 Var2 <NA>  Var3  Var4       Var5         Var6    Var7     Var8#> 3   TRUE    1   NA     1     a 2023-05-29 3209324 This #DIV/0! 01:27:15#> 4   TRUE   NA   NA #NUM!     b 2023-05-23         <NA>       0 14:02:57#> 5   TRUE    2   NA  1.34     c 2023-02-01         <NA> #VALUE! 23:01:02#> 6  FALSE    2   NA  <NA> #NUM!       <NA>         <NA>       2 17:24:53#> 7  FALSE    3   NA  1.56     e       <NA>         <NA>    <NA>     <NA>#> 8  FALSE    1   NA   1.7     f 2023-03-02         <NA>     2.7 08:45:58#> 9     NA   NA   NA  <NA>  <NA>       <NA>         <NA>    <NA>     <NA>#> 10 FALSE    2   NA    23     h 2023-12-24         <NA>      25     <NA>#> 11 FALSE    3   NA  67.3     i 2023-12-25         <NA>       3     <NA>#> 12    NA    1   NA   123  <NA> 2023-07-31         <NA>     122     <NA># or import workbookswb<-wb_load(path)wb#> A Workbook object.#>#> Worksheets:#>  Sheets: Sheet1, Sheet2#>  Write order: 1, 2# read a data framewb_to_df(wb)#>     Var1 Var2 <NA>  Var3  Var4       Var5         Var6    Var7     Var8#> 3   TRUE    1   NA     1     a 2023-05-29 3209324 This #DIV/0! 01:27:15#> 4   TRUE   NA   NA #NUM!     b 2023-05-23         <NA>       0 14:02:57#> 5   TRUE    2   NA  1.34     c 2023-02-01         <NA> #VALUE! 23:01:02#> 6  FALSE    2   NA  <NA> #NUM!       <NA>         <NA>       2 17:24:53#> 7  FALSE    3   NA  1.56     e       <NA>         <NA>    <NA>     <NA>#> 8  FALSE    1   NA   1.7     f 2023-03-02         <NA>     2.7 08:45:58#> 9     NA   NA   NA  <NA>  <NA>       <NA>         <NA>    <NA>     <NA>#> 10 FALSE    2   NA    23     h 2023-12-24         <NA>      25     <NA>#> 11 FALSE    3   NA  67.3     i 2023-12-25         <NA>       3     <NA>#> 12    NA    1   NA   123  <NA> 2023-07-31         <NA>     122     <NA># and savetemp<-temp_xlsx()if (interactive())wb_save(wb, temp)## or create one yourselfwb<-wb_workbook()# add a worksheetwb$add_worksheet("sheet")# add some datawb$add_data("sheet", cars)# open it in your default spreadsheet softwareif (interactive()) wb$open()

Authors and contributions

For a full list of all authors that have made this package possibleand for whom we are greatful, please see:

system.file("AUTHORS",package ="openxlsx2")

If you feel like you should be included on this list, please let usknow. If you have something to contribute, you are welcome. If somethingis not working as expected, open issues or if you have solved an issue,open a pull request. Please be respectful and be aware that we arevolunteers doing this for fun in our unpaid free time. We will work onproblems when we have time or need.

License

This package is licensed under the MIT license and is based onopenxlsx (byAlexander Walker and Philipp Schauberger; COPYRIGHT 2014-2022) andpugixml (byArseny Kapoulkine; COPYRIGHT 2006-2025). Both released under the MITlicense.