README.md

xlsxtractr : Extract Things From Excel (xlsx) Files

Inspired by this SO question.

The following functions are implemented:

• extract_formulas: Extract formulas from an Excel workbook sheet
• read_xlsx: Read in an Excel document for various extractions

The following data sets are included:

• system.file("extdata/wb.xlsx", package="xlsxtractr"): sample xlsx file

Installation

devtools::install_git("https://gitlab.com/hrbrmstr/xlsxtractr.git")

# OR

devtools::install_github("hrbrmstr/xlsxtractr")

Usage

library(xlsxtractr)
library(purrr)

# current verison
packageVersion("xlsxtractr")

## [1] '0.1.0'

doc <- read_xlsx(system.file("extdata/wb.xlsx", package="xlsxtractr"))

class(doc)

## [1] "xlsx"

print(doc)

## /Library/Frameworks/R.framework/Versions/3.3/Resources/library/xlsxtractr/extdata/wb.xlsx:
##   A Microsoft Excel xlsx document with 3 sheets.

length(doc)

## [1] 3

extract_formulas(doc, 1)
``$

    ## # \text{A} \text{tibble}: 3  \times  3
    ##   \text{sheet}  \text{cell}          \text{f}
    ##   <\text{dbl}> <\text{chr}>      <\text{chr}>
    ## 1     1    \text{A4} \text{SUM}(\text{A1}:\text{A3})
    ## 2     1    \text{B4} \text{SUM}(\text{B1}:\text{B3})
    ## 3     1    \text{D4} \text{SUM}(\text{A4}:\text{B4})

$`` r
extract_formulas(doc, 2)
``$

    ## # \text{A} \text{tibble}: 3  \times  3
    ##   \text{sheet}  \text{cell}            \text{f}
    ##   <\text{dbl}> <\text{chr}>        <\text{chr}>
    ## 1     2   \text{J11}  \text{SUM}(\text{J8}:\text{J10})
    ## 2     2   \text{K11}  \text{SUM}(\text{K8}:\text{K10})
    ## 3     2   \text{M11} \text{SUM}(\text{J11}:\text{K11})

$`` r
extract_formulas(doc, 3) # no formula
``$

    ## # \text{A} \text{tibble}: 0  \times  0

$`` r
map_df(seq_along(doc), ~extract_formulas(doc, .))
``$

    ## # \text{A} \text{tibble}: 6  \times  3
    ##   \text{sheet}  \text{cell}            \text{f}
    ##   <\text{int}> <\text{chr}>        <\text{chr}>
    ## 1     1    \text{A4}   \text{SUM}(\text{A1}:\text{A3})
    ## 2     1    \text{B4}   \text{SUM}(\text{B1}:\text{B3})
    ## 3     1    \text{D4}   \text{SUM}(\text{A4}:\text{B4})
    ## 4     2   \text{J11}  \text{SUM}(\text{J8}:\text{J10})
    ## 5     2   \text{K11}  \text{SUM}(\text{K8}:\text{K10})
    ## 6     2   \text{M11} \text{SUM}(\text{J11}:\text{K11})

### \text{Test} \text{Results}

$`` r
library(xlsxtractr)
library(testthat)

date()

## [1] "Fri Sep 16 09:56:30 2016"

test_dir("tests/")

## testthat results ========================================================================================================
## OK: 0 SKIPPED: 0 FAILED: 0
## 
## DONE ===================================================================================================================