File: sheet-geometry.html

package info (click to toggle)
r-cran-readxl 1.4.5-1
links: PTS, VCS
area: main
in suites: forky, sid, trixie
size: 4,044 kB
sloc: ansic: 4,854; cpp: 3,213; makefile: 2
file content (621 lines) | stat: -rw-r--r-- 33,805 bytes
<!DOCTYPE html>

<html>

<head>

<meta charset="utf-8" />
<meta name="generator" content="pandoc" />
<meta http-equiv="X-UA-Compatible" content="IE=EDGE" />

<meta name="viewport" content="width=device-width, initial-scale=1" />



<title>Sheet Geometry</title>

<script>// Pandoc 2.9 adds attributes on both header and div. We remove the former (to
// be compatible with the behavior of Pandoc < 2.8).
document.addEventListener('DOMContentLoaded', function(e) {
  var hs = document.querySelectorAll("div.section[class*='level'] > :first-child");
  var i, h, a;
  for (i = 0; i < hs.length; i++) {
    h = hs[i];
    if (!/^h[1-6]$/i.test(h.tagName)) continue;  // it should be a header h1-h6
    a = h.attributes;
    while (a.length > 0) h.removeAttribute(a[0].name);
  }
});
</script>

<style type="text/css">
code{white-space: pre-wrap;}
span.smallcaps{font-variant: small-caps;}
span.underline{text-decoration: underline;}
div.column{display: inline-block; vertical-align: top; width: 50%;}
div.hanging-indent{margin-left: 1.5em; text-indent: -1.5em;}
ul.task-list{list-style: none;}
</style>



<style type="text/css">
code {
white-space: pre;
}
.sourceCode {
overflow: visible;
}
</style>
<style type="text/css" data-origin="pandoc">
pre > code.sourceCode { white-space: pre; position: relative; }
pre > code.sourceCode > span { line-height: 1.25; }
pre > code.sourceCode > span:empty { height: 1.2em; }
.sourceCode { overflow: visible; }
code.sourceCode > span { color: inherit; text-decoration: inherit; }
div.sourceCode { margin: 1em 0; }
pre.sourceCode { margin: 0; }
@media screen {
div.sourceCode { overflow: auto; }
}
@media print {
pre > code.sourceCode { white-space: pre-wrap; }
pre > code.sourceCode > span { display: inline-block; text-indent: -5em; padding-left: 5em; }
}
pre.numberSource code
{ counter-reset: source-line 0; }
pre.numberSource code > span
{ position: relative; left: -4em; counter-increment: source-line; }
pre.numberSource code > span > a:first-child::before
{ content: counter(source-line);
position: relative; left: -1em; text-align: right; vertical-align: baseline;
border: none; display: inline-block;
-webkit-touch-callout: none; -webkit-user-select: none;
-khtml-user-select: none; -moz-user-select: none;
-ms-user-select: none; user-select: none;
padding: 0 4px; width: 4em;
color: #aaaaaa;
}
pre.numberSource { margin-left: 3em; border-left: 1px solid #aaaaaa; padding-left: 4px; }
div.sourceCode
{ }
@media screen {
pre > code.sourceCode > span > a:first-child::before { text-decoration: underline; }
}
code span.al { color: #ff0000; font-weight: bold; } 
code span.an { color: #60a0b0; font-weight: bold; font-style: italic; } 
code span.at { color: #7d9029; } 
code span.bn { color: #40a070; } 
code span.bu { color: #008000; } 
code span.cf { color: #007020; font-weight: bold; } 
code span.ch { color: #4070a0; } 
code span.cn { color: #880000; } 
code span.co { color: #60a0b0; font-style: italic; } 
code span.cv { color: #60a0b0; font-weight: bold; font-style: italic; } 
code span.do { color: #ba2121; font-style: italic; } 
code span.dt { color: #902000; } 
code span.dv { color: #40a070; } 
code span.er { color: #ff0000; font-weight: bold; } 
code span.ex { } 
code span.fl { color: #40a070; } 
code span.fu { color: #06287e; } 
code span.im { color: #008000; font-weight: bold; } 
code span.in { color: #60a0b0; font-weight: bold; font-style: italic; } 
code span.kw { color: #007020; font-weight: bold; } 
code span.op { color: #666666; } 
code span.ot { color: #007020; } 
code span.pp { color: #bc7a00; } 
code span.sc { color: #4070a0; } 
code span.ss { color: #bb6688; } 
code span.st { color: #4070a0; } 
code span.va { color: #19177c; } 
code span.vs { color: #4070a0; } 
code span.wa { color: #60a0b0; font-weight: bold; font-style: italic; } 
</style>
<script>
// apply pandoc div.sourceCode style to pre.sourceCode instead
(function() {
  var sheets = document.styleSheets;
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].ownerNode.dataset["origin"] !== "pandoc") continue;
    try { var rules = sheets[i].cssRules; } catch (e) { continue; }
    var j = 0;
    while (j < rules.length) {
      var rule = rules[j];
      // check if there is a div.sourceCode rule
      if (rule.type !== rule.STYLE_RULE || rule.selectorText !== "div.sourceCode") {
        j++;
        continue;
      }
      var style = rule.style.cssText;
      // check if color or background-color is set
      if (rule.style.color === '' && rule.style.backgroundColor === '') {
        j++;
        continue;
      }
      // replace div.sourceCode by a pre.sourceCode rule
      sheets[i].deleteRule(j);
      sheets[i].insertRule('pre.sourceCode{' + style + '}', j);
    }
  }
})();
</script>




<style type="text/css">body {
background-color: #fff;
margin: 1em auto;
max-width: 700px;
overflow: visible;
padding-left: 2em;
padding-right: 2em;
font-family: "Open Sans", "Helvetica Neue", Helvetica, Arial, sans-serif;
font-size: 14px;
line-height: 1.35;
}
#TOC {
clear: both;
margin: 0 0 10px 10px;
padding: 4px;
width: 400px;
border: 1px solid #CCCCCC;
border-radius: 5px;
background-color: #f6f6f6;
font-size: 13px;
line-height: 1.3;
}
#TOC .toctitle {
font-weight: bold;
font-size: 15px;
margin-left: 5px;
}
#TOC ul {
padding-left: 40px;
margin-left: -1.5em;
margin-top: 5px;
margin-bottom: 5px;
}
#TOC ul ul {
margin-left: -2em;
}
#TOC li {
line-height: 16px;
}
table {
margin: 1em auto;
border-width: 1px;
border-color: #DDDDDD;
border-style: outset;
border-collapse: collapse;
}
table th {
border-width: 2px;
padding: 5px;
border-style: inset;
}
table td {
border-width: 1px;
border-style: inset;
line-height: 18px;
padding: 5px 5px;
}
table, table th, table td {
border-left-style: none;
border-right-style: none;
}
table thead, table tr.even {
background-color: #f7f7f7;
}
p {
margin: 0.5em 0;
}
blockquote {
background-color: #f6f6f6;
padding: 0.25em 0.75em;
}
hr {
border-style: solid;
border: none;
border-top: 1px solid #777;
margin: 28px 0;
}
dl {
margin-left: 0;
}
dl dd {
margin-bottom: 13px;
margin-left: 13px;
}
dl dt {
font-weight: bold;
}
ul {
margin-top: 0;
}
ul li {
list-style: circle outside;
}
ul ul {
margin-bottom: 0;
}
pre, code {
background-color: #f7f7f7;
border-radius: 3px;
color: #333;
white-space: pre-wrap; 
}
pre {
border-radius: 3px;
margin: 5px 0px 10px 0px;
padding: 10px;
}
pre:not([class]) {
background-color: #f7f7f7;
}
code {
font-family: Consolas, Monaco, 'Courier New', monospace;
font-size: 85%;
}
p > code, li > code {
padding: 2px 0px;
}
div.figure {
text-align: center;
}
img {
background-color: #FFFFFF;
padding: 2px;
border: 1px solid #DDDDDD;
border-radius: 3px;
border: 1px solid #CCCCCC;
margin: 0 5px;
}
h1 {
margin-top: 0;
font-size: 35px;
line-height: 40px;
}
h2 {
border-bottom: 4px solid #f7f7f7;
padding-top: 10px;
padding-bottom: 2px;
font-size: 145%;
}
h3 {
border-bottom: 2px solid #f7f7f7;
padding-top: 10px;
font-size: 120%;
}
h4 {
border-bottom: 1px solid #f7f7f7;
margin-left: 8px;
font-size: 105%;
}
h5, h6 {
border-bottom: 1px solid #ccc;
font-size: 105%;
}
a {
color: #0033dd;
text-decoration: none;
}
a:hover {
color: #6666ff; }
a:visited {
color: #800080; }
a:visited:hover {
color: #BB00BB; }
a[href^="http:"] {
text-decoration: underline; }
a[href^="https:"] {
text-decoration: underline; }

code > span.kw { color: #555; font-weight: bold; } 
code > span.dt { color: #902000; } 
code > span.dv { color: #40a070; } 
code > span.bn { color: #d14; } 
code > span.fl { color: #d14; } 
code > span.ch { color: #d14; } 
code > span.st { color: #d14; } 
code > span.co { color: #888888; font-style: italic; } 
code > span.ot { color: #007020; } 
code > span.al { color: #ff0000; font-weight: bold; } 
code > span.fu { color: #900; font-weight: bold; } 
code > span.er { color: #a61717; background-color: #e3d2d2; } 
</style>




</head>

<body>




<h1 class="title toc-ignore">Sheet Geometry</h1>



<div class="sourceCode" id="cb1"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb1-1"><a href="#cb1-1" tabindex="-1"></a><span class="fu">library</span>(readxl)</span></code></pre></div>
<p><code>readxl::read_excel()</code> brings data from a rectangle of
cells into R as a data frame or, more specifically, a <a href="https://tibble.tidyverse.org/reference/tibble.html">tibble</a>.</p>
<p>The extent of the data rectangle can be determined in various
ways:</p>
<ul>
<li><strong>Discovered</strong>: By default, <code>read_excel()</code>
uses the smallest rectangle that contains the non-empty cells. It
“shrink wraps” the data.</li>
<li><strong>Bounded</strong>: The <code>skip</code> and
<code>n_max</code> arguments constrain <code>read_excel()</code>’s
discovery process with respect to rows. At least <code>skip</code>
spreadsheet rows will be skipped or ignored and at most
<code>n_max</code> spreadsheet rows will be considered as data. Compared
to the default of <strong>discovery</strong>, these arguments can only
lead to making the output tibble smaller.</li>
<li><strong>Set</strong>: The <code>range</code> argument is taken
literally, even if that means you will have leading or trailing rows or
columns filled with <code>NA</code>. If you ask for
<code>range = &quot;A1:D4&quot;</code>, you are guaranteed to get a tibble with 4
columns (A through D) and either 3 rows (<code>col_names = TRUE</code>,
default) or 4 rows (<code>col_names = FALSE</code>).</li>
<li><strong>Mixed</strong>: In typical use, <code>read_excel()</code>’s
geometry arguments often imply that certain limits are
<strong>discovered</strong> while others are <strong>bounded</strong> or
<strong>set</strong>. This will be more clear in the concrete examples
below.</li>
</ul>
<p>For now, here are a few ways <code>read_excel()</code> can look when
you take control of the geometry:</p>
<div class="sourceCode" id="cb2"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb2-1"><a href="#cb2-1" tabindex="-1"></a><span class="fu">read_excel</span>(<span class="st">&quot;yo.xlsx&quot;</span>, <span class="at">skip =</span> <span class="dv">5</span>)</span>
<span id="cb2-2"><a href="#cb2-2" tabindex="-1"></a><span class="fu">read_excel</span>(<span class="st">&quot;yo.xlsx&quot;</span>, <span class="at">n_max =</span> <span class="dv">100</span>)</span>
<span id="cb2-3"><a href="#cb2-3" tabindex="-1"></a><span class="fu">read_excel</span>(<span class="st">&quot;yo.xlsx&quot;</span>, <span class="at">skip =</span> <span class="dv">5</span>, <span class="at">n_max =</span> <span class="dv">100</span>)</span>
<span id="cb2-4"><a href="#cb2-4" tabindex="-1"></a><span class="fu">read_excel</span>(<span class="st">&quot;yo.xlsx&quot;</span>, <span class="at">range =</span> <span class="st">&quot;C1:E7&quot;</span>)</span>
<span id="cb2-5"><a href="#cb2-5" tabindex="-1"></a><span class="fu">read_excel</span>(<span class="st">&quot;yo.xlsx&quot;</span>, <span class="at">range =</span> <span class="fu">cell_rows</span>(<span class="dv">6</span><span class="sc">:</span><span class="dv">23</span>))</span>
<span id="cb2-6"><a href="#cb2-6" tabindex="-1"></a><span class="fu">read_excel</span>(<span class="st">&quot;yo.xlsx&quot;</span>, <span class="at">range =</span> <span class="fu">cell_cols</span>(<span class="st">&quot;B:D&quot;</span>))</span>
<span id="cb2-7"><a href="#cb2-7" tabindex="-1"></a><span class="fu">read_excel</span>(<span class="st">&quot;yo.xlsx&quot;</span>, <span class="at">range =</span> <span class="fu">anchored</span>(<span class="st">&quot;C4&quot;</span>, <span class="at">dim =</span> <span class="fu">c</span>(<span class="dv">3</span>, <span class="dv">2</span>)))</span></code></pre></div>
<div id="little-known-excel-facts" class="section level2">
<h2>Little known Excel facts</h2>
<p>readxl’s behavior and interface may be easier to understand if you
understand this about Excel:</p>
<blockquote>
<p>Cells you can see don’t necessarily exist. Cells that look blank
aren’t necessarily so.</p>
</blockquote>
<p>Among lots of other information, Excel files obviously must contain
information on each cell. Let’s use the word “item” to denote one
cell’s-worth of info.</p>
<p>Just because you see a cell on the screen in Excel, that doesn’t mean
there’s a corresponding item on file. Why? Because Excel presents a huge
gridded canvas for you to write on. Until you actually populate a cell,
though, it doesn’t really exist.</p>
<p>The stream of cell items describes the existing cells, going from
upper left to lower right, travelling by row. Blank cells simply do not
exist in it.</p>
<p>Ah, but what is a blank cell? Some cells appear blank to the naked
eye but are not considered so by Excel and, indeed, are represented by a
cell item. This happens when a cell has no content but does have an
associated format. This format could have been applied directly to a
single cell or, more often, indirectly via formatting applied to an
entire row or column. Once a human has spent some quality time with a
spreadsheet, many seemingly empty cells will bear a format and will thus
have an associated cell item.</p>
<div id="implications-for-readxl" class="section level3">
<h3>Implications for readxl</h3>
<p>readxl only reads cell items that have content. It ignores cell items
that exist strictly to convey formatting.</p>
<p>The tibble returned by readxl will often cover cells that are empty
in the spreadsheet, filled with <code>NA</code>. But only because there
was some other reason for the associated row or column to exist: actual
data or user-specified geometry.</p>
</div>
</div>
<div id="skip-and-n_max" class="section level2">
<h2><code>skip</code> and <code>n_max</code></h2>
<p><code>skip</code> and <code>n_max</code> are the “entry-level”
solution for controlling the data rectangle. They work only in the row
direction. Column-wise, you’re letting readxl discover which columns are
populated.</p>
<p>If you specify <code>range</code> (covered below), <code>skip</code>
and <code>n_max</code> are ignored.</p>
<div id="skip" class="section level3">
<h3><code>skip</code></h3>
<p>The <code>skip</code> argument tells <code>read_excel()</code> to
start looking for populated cells after skipping at least
<code>skip</code> rows. If the new start point begins with 1 or more
empty rows, <code>read_excel()</code> will skip even more before it
starts reading from the sheet.</p>
<p>Here’s a screen shot of the <code>geometry.xlsx</code> example sheet
that ships with readxl, accessible via
<code>readxl_example(&quot;geometry.xlsx&quot;)</code>.</p>
<p>&lt;img src=“img/geometry.png” alt=“Screenshot of the”geometry.xlsx”
example spreadsheet. The cells in the range B3:D6 are populated, each
with the string of the cell’s address. For example, the cell B3 contains
the string “B3”.” width=“70%” /&gt;</p>
<p>By default, <code>read_excel()</code> just discovers the data
rectangle:</p>
<div class="sourceCode" id="cb3"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb3-1"><a href="#cb3-1" tabindex="-1"></a><span class="fu">read_excel</span>(<span class="fu">readxl_example</span>(<span class="st">&quot;geometry.xlsx&quot;</span>))</span>
<span id="cb3-2"><a href="#cb3-2" tabindex="-1"></a><span class="co">#&gt; # A tibble: 3 × 3</span></span>
<span id="cb3-3"><a href="#cb3-3" tabindex="-1"></a><span class="co">#&gt;   B3    C3    D3   </span></span>
<span id="cb3-4"><a href="#cb3-4" tabindex="-1"></a><span class="co">#&gt;   &lt;chr&gt; &lt;chr&gt; &lt;chr&gt;</span></span>
<span id="cb3-5"><a href="#cb3-5" tabindex="-1"></a><span class="co">#&gt; 1 B4    C4    D4   </span></span>
<span id="cb3-6"><a href="#cb3-6" tabindex="-1"></a><span class="co">#&gt; 2 B5    C5    D5   </span></span>
<span id="cb3-7"><a href="#cb3-7" tabindex="-1"></a><span class="co">#&gt; 3 B6    C6    D6</span></span></code></pre></div>
<p>If you explicitly skip one row, note that <code>read_excel()</code>
still skips row 2, which is also empty, leading to the same result as
before:</p>
<div class="sourceCode" id="cb4"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb4-1"><a href="#cb4-1" tabindex="-1"></a><span class="fu">read_excel</span>(<span class="fu">readxl_example</span>(<span class="st">&quot;geometry.xlsx&quot;</span>), <span class="at">skip =</span> <span class="dv">1</span>)</span>
<span id="cb4-2"><a href="#cb4-2" tabindex="-1"></a><span class="co">#&gt; # A tibble: 3 × 3</span></span>
<span id="cb4-3"><a href="#cb4-3" tabindex="-1"></a><span class="co">#&gt;   B3    C3    D3   </span></span>
<span id="cb4-4"><a href="#cb4-4" tabindex="-1"></a><span class="co">#&gt;   &lt;chr&gt; &lt;chr&gt; &lt;chr&gt;</span></span>
<span id="cb4-5"><a href="#cb4-5" tabindex="-1"></a><span class="co">#&gt; 1 B4    C4    D4   </span></span>
<span id="cb4-6"><a href="#cb4-6" tabindex="-1"></a><span class="co">#&gt; 2 B5    C5    D5   </span></span>
<span id="cb4-7"><a href="#cb4-7" tabindex="-1"></a><span class="co">#&gt; 3 B6    C6    D6</span></span></code></pre></div>
<p>You can also use <code>skip</code> to skip over populated cells. In
real life, this is a mighty weapon against the explanatory text that
people like to include at the top of spreadsheets.</p>
<div class="sourceCode" id="cb5"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb5-1"><a href="#cb5-1" tabindex="-1"></a><span class="fu">read_excel</span>(<span class="fu">readxl_example</span>(<span class="st">&quot;geometry.xlsx&quot;</span>), <span class="at">skip =</span> <span class="dv">3</span>)</span>
<span id="cb5-2"><a href="#cb5-2" tabindex="-1"></a><span class="co">#&gt; # A tibble: 2 × 3</span></span>
<span id="cb5-3"><a href="#cb5-3" tabindex="-1"></a><span class="co">#&gt;   B4    C4    D4   </span></span>
<span id="cb5-4"><a href="#cb5-4" tabindex="-1"></a><span class="co">#&gt;   &lt;chr&gt; &lt;chr&gt; &lt;chr&gt;</span></span>
<span id="cb5-5"><a href="#cb5-5" tabindex="-1"></a><span class="co">#&gt; 1 B5    C5    D5   </span></span>
<span id="cb5-6"><a href="#cb5-6" tabindex="-1"></a><span class="co">#&gt; 2 B6    C6    D6</span></span></code></pre></div>
<p>Summary: <code>skip</code> tells <code>read_excel()</code> to skip
<em>at least this many</em> spreadsheet rows before reading
anything.</p>
</div>
<div id="n_max" class="section level3">
<h3><code>n_max</code></h3>
<p>The <code>n_max</code> argument tells <code>read_excel()</code> to
read at most <code>n_max</code> rows, once it has found the data
rectangle. Note that <code>n_max</code> is specifically about <em>the
data</em>. You still use <code>col_names</code> to express whether the
first spreadsheet row should be used to create column names (default is
<code>TRUE</code>).</p>
<p><code>n_max = 2</code> causes us to ignore the last data row – the
3rd one – in <code>geometry.xlsx</code>.</p>
<div class="sourceCode" id="cb6"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb6-1"><a href="#cb6-1" tabindex="-1"></a><span class="fu">read_excel</span>(<span class="fu">readxl_example</span>(<span class="st">&quot;geometry.xlsx&quot;</span>), <span class="at">n_max =</span> <span class="dv">2</span>)</span>
<span id="cb6-2"><a href="#cb6-2" tabindex="-1"></a><span class="co">#&gt; # A tibble: 2 × 3</span></span>
<span id="cb6-3"><a href="#cb6-3" tabindex="-1"></a><span class="co">#&gt;   B3    C3    D3   </span></span>
<span id="cb6-4"><a href="#cb6-4" tabindex="-1"></a><span class="co">#&gt;   &lt;chr&gt; &lt;chr&gt; &lt;chr&gt;</span></span>
<span id="cb6-5"><a href="#cb6-5" tabindex="-1"></a><span class="co">#&gt; 1 B4    C4    D4   </span></span>
<span id="cb6-6"><a href="#cb6-6" tabindex="-1"></a><span class="co">#&gt; 2 B5    C5    D5</span></span></code></pre></div>
<p><code>n_max</code> is an upper bound. It will never cause empty rows
to be included in the tibble. Note how we get 3 data rows here, even
though <code>n_max</code> is much greater.</p>
<div class="sourceCode" id="cb7"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb7-1"><a href="#cb7-1" tabindex="-1"></a><span class="fu">read_excel</span>(<span class="fu">readxl_example</span>(<span class="st">&quot;geometry.xlsx&quot;</span>), <span class="at">n_max =</span> <span class="dv">1000</span>)</span>
<span id="cb7-2"><a href="#cb7-2" tabindex="-1"></a><span class="co">#&gt; # A tibble: 3 × 3</span></span>
<span id="cb7-3"><a href="#cb7-3" tabindex="-1"></a><span class="co">#&gt;   B3    C3    D3   </span></span>
<span id="cb7-4"><a href="#cb7-4" tabindex="-1"></a><span class="co">#&gt;   &lt;chr&gt; &lt;chr&gt; &lt;chr&gt;</span></span>
<span id="cb7-5"><a href="#cb7-5" tabindex="-1"></a><span class="co">#&gt; 1 B4    C4    D4   </span></span>
<span id="cb7-6"><a href="#cb7-6" tabindex="-1"></a><span class="co">#&gt; 2 B5    C5    D5   </span></span>
<span id="cb7-7"><a href="#cb7-7" tabindex="-1"></a><span class="co">#&gt; 3 B6    C6    D6</span></span></code></pre></div>
</div>
</div>
<div id="range" class="section level2">
<h2><code>range</code></h2>
<p>The <code>range</code> argument is the most flexible way to control
geometry and is powered by the <a href="https://github.com/rsheets/cellranger#readme">cellranger</a>
package.</p>
<p>One huge difference from <code>skip</code> and <code>n_max</code> is
that <code>range</code> is taken literally! Even if it means the
returned tibble will have entire rows or columns consisting of
<code>NA</code>.</p>
<p>You can describe cell limits in a variety of ways:</p>
<p><strong>Excel-style range</strong>: Specify a fixed rectangle with
<code>range = &quot;A1:D4&quot;</code> or <code>range = &quot;R1C1:R4C4&quot;</code>. You
can even prepend the worksheet name like so:
<code>range = &quot;foofy!A1:D4&quot;</code> and it will be passed along to the
<code>sheet</code> argument.</p>
<p>The <code>deaths.xlsx</code> example sheet features junk rows both
before and after the data rectangle. The payoff for specifying the data
rectangle precisely is that we get the data frame we want, with correct
guesses for the column types.</p>
<div class="sourceCode" id="cb8"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb8-1"><a href="#cb8-1" tabindex="-1"></a><span class="fu">read_excel</span>(<span class="fu">readxl_example</span>(<span class="st">&quot;deaths.xlsx&quot;</span>), <span class="at">range =</span> <span class="st">&quot;arts!A5:F15&quot;</span>)</span>
<span id="cb8-2"><a href="#cb8-2" tabindex="-1"></a><span class="co">#&gt; # A tibble: 10 × 6</span></span>
<span id="cb8-3"><a href="#cb8-3" tabindex="-1"></a><span class="co">#&gt;   Name       Profession   Age `Has kids` `Date of birth`     `Date of death`    </span></span>
<span id="cb8-4"><a href="#cb8-4" tabindex="-1"></a><span class="co">#&gt;   &lt;chr&gt;      &lt;chr&gt;      &lt;dbl&gt; &lt;lgl&gt;      &lt;dttm&gt;              &lt;dttm&gt;             </span></span>
<span id="cb8-5"><a href="#cb8-5" tabindex="-1"></a><span class="co">#&gt; 1 David Bow… musician      69 TRUE       1947-01-08 00:00:00 2016-01-10 00:00:00</span></span>
<span id="cb8-6"><a href="#cb8-6" tabindex="-1"></a><span class="co">#&gt; 2 Carrie Fi… actor         60 TRUE       1956-10-21 00:00:00 2016-12-27 00:00:00</span></span>
<span id="cb8-7"><a href="#cb8-7" tabindex="-1"></a><span class="co">#&gt; 3 Chuck Ber… musician      90 TRUE       1926-10-18 00:00:00 2017-03-18 00:00:00</span></span>
<span id="cb8-8"><a href="#cb8-8" tabindex="-1"></a><span class="co">#&gt; 4 Bill Paxt… actor         61 TRUE       1955-05-17 00:00:00 2017-02-25 00:00:00</span></span>
<span id="cb8-9"><a href="#cb8-9" tabindex="-1"></a><span class="co">#&gt; # ℹ 6 more rows</span></span></code></pre></div>
<p>We repeat the screenshot of <code>geometry.xlsx</code> as a visual
reference.</p>
<p>&lt;img src=“img/geometry.png” alt=“Screenshot of the”geometry.xlsx”
example spreadsheet. The cells in the range B3:D6 are populated, each
with the string of the cell’s address. For example, the cell B3 contains
the string “B3”.” width=“70%” /&gt;</p>
<p>Going back to <code>geometry.xlsx</code>, here we specify a rectangle
that only partially overlaps the data. Note the use of default column
names, because the first row of cells is empty, and the leading column
of <code>NA</code>s.</p>
<div class="sourceCode" id="cb9"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb9-1"><a href="#cb9-1" tabindex="-1"></a><span class="fu">read_excel</span>(<span class="fu">readxl_example</span>(<span class="st">&quot;geometry.xlsx&quot;</span>), <span class="at">range =</span> <span class="st">&quot;A2:C4&quot;</span>)</span>
<span id="cb9-2"><a href="#cb9-2" tabindex="-1"></a><span class="co">#&gt; New names:</span></span>
<span id="cb9-3"><a href="#cb9-3" tabindex="-1"></a><span class="co">#&gt; • `` -&gt; `...1`</span></span>
<span id="cb9-4"><a href="#cb9-4" tabindex="-1"></a><span class="co">#&gt; • `` -&gt; `...2`</span></span>
<span id="cb9-5"><a href="#cb9-5" tabindex="-1"></a><span class="co">#&gt; • `` -&gt; `...3`</span></span>
<span id="cb9-6"><a href="#cb9-6" tabindex="-1"></a><span class="co">#&gt; # A tibble: 2 × 3</span></span>
<span id="cb9-7"><a href="#cb9-7" tabindex="-1"></a><span class="co">#&gt;   ...1  ...2  ...3 </span></span>
<span id="cb9-8"><a href="#cb9-8" tabindex="-1"></a><span class="co">#&gt;   &lt;lgl&gt; &lt;chr&gt; &lt;chr&gt;</span></span>
<span id="cb9-9"><a href="#cb9-9" tabindex="-1"></a><span class="co">#&gt; 1 NA    B3    C3   </span></span>
<span id="cb9-10"><a href="#cb9-10" tabindex="-1"></a><span class="co">#&gt; 2 NA    B4    C4</span></span></code></pre></div>
<p><strong>Specific range of rows or columns</strong>: Set exact limits
on just the rows or just the columns and allow the limits in the other
direction to be discovered. Example calls:</p>
<div class="sourceCode" id="cb10"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb10-1"><a href="#cb10-1" tabindex="-1"></a><span class="do">## rows only</span></span>
<span id="cb10-2"><a href="#cb10-2" tabindex="-1"></a><span class="fu">read_excel</span>(..., <span class="at">range =</span> <span class="fu">cell_rows</span>(<span class="dv">1</span><span class="sc">:</span><span class="dv">10</span>))</span>
<span id="cb10-3"><a href="#cb10-3" tabindex="-1"></a><span class="do">## is equivalent to</span></span>
<span id="cb10-4"><a href="#cb10-4" tabindex="-1"></a><span class="fu">read_excel</span>(..., <span class="at">range =</span> <span class="fu">cell_rows</span>(<span class="fu">c</span>(<span class="dv">1</span>, <span class="dv">10</span>)))</span>
<span id="cb10-5"><a href="#cb10-5" tabindex="-1"></a></span>
<span id="cb10-6"><a href="#cb10-6" tabindex="-1"></a><span class="do">## columns only</span></span>
<span id="cb10-7"><a href="#cb10-7" tabindex="-1"></a><span class="fu">read_excel</span>(..., <span class="at">range =</span> <span class="fu">cell_cols</span>(<span class="dv">1</span><span class="sc">:</span><span class="dv">26</span>))</span>
<span id="cb10-8"><a href="#cb10-8" tabindex="-1"></a><span class="do">## is equivalent to all of these</span></span>
<span id="cb10-9"><a href="#cb10-9" tabindex="-1"></a><span class="fu">read_excel</span>(..., <span class="at">range =</span> <span class="fu">cell_cols</span>(<span class="fu">c</span>(<span class="dv">1</span>, <span class="dv">26</span>)))</span>
<span id="cb10-10"><a href="#cb10-10" tabindex="-1"></a><span class="fu">read_excel</span>(..., <span class="at">range =</span> <span class="fu">cell_cols</span>(<span class="st">&quot;A:Z&quot;</span>))</span>
<span id="cb10-11"><a href="#cb10-11" tabindex="-1"></a><span class="fu">read_excel</span>(..., <span class="at">range =</span> <span class="fu">cell_cols</span>(LETTERS))</span>
<span id="cb10-12"><a href="#cb10-12" tabindex="-1"></a><span class="fu">read_excel</span>(..., <span class="at">range =</span> <span class="fu">cell_cols</span>(<span class="fu">c</span>(<span class="st">&quot;A&quot;</span>, <span class="st">&quot;Z&quot;</span>))</span></code></pre></div>
<p>We use <code>geometry.xlsx</code> to demonstrate setting hard limits
on the rows, running past the data, while allowing column limits to
discovered. Note the trailing rows of <code>NA</code>.</p>
<div class="sourceCode" id="cb11"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb11-1"><a href="#cb11-1" tabindex="-1"></a><span class="fu">read_excel</span>(<span class="fu">readxl_example</span>(<span class="st">&quot;geometry.xlsx&quot;</span>), <span class="at">range =</span> <span class="fu">cell_rows</span>(<span class="dv">4</span><span class="sc">:</span><span class="dv">8</span>))</span>
<span id="cb11-2"><a href="#cb11-2" tabindex="-1"></a><span class="co">#&gt; # A tibble: 4 × 3</span></span>
<span id="cb11-3"><a href="#cb11-3" tabindex="-1"></a><span class="co">#&gt;   B4    C4    D4   </span></span>
<span id="cb11-4"><a href="#cb11-4" tabindex="-1"></a><span class="co">#&gt;   &lt;chr&gt; &lt;chr&gt; &lt;chr&gt;</span></span>
<span id="cb11-5"><a href="#cb11-5" tabindex="-1"></a><span class="co">#&gt; 1 B5    C5    D5   </span></span>
<span id="cb11-6"><a href="#cb11-6" tabindex="-1"></a><span class="co">#&gt; 2 B6    C6    D6   </span></span>
<span id="cb11-7"><a href="#cb11-7" tabindex="-1"></a><span class="co">#&gt; 3 &lt;NA&gt;  &lt;NA&gt;  &lt;NA&gt; </span></span>
<span id="cb11-8"><a href="#cb11-8" tabindex="-1"></a><span class="co">#&gt; 4 &lt;NA&gt;  &lt;NA&gt;  &lt;NA&gt;</span></span></code></pre></div>
<p><strong>Anchored rectangle</strong>: Helper functions
<code>anchored()</code> and <code>cell_limits()</code> let you specify
limits via the corner(s) of the rectangle.</p>
<p>Here we get a 3 by 4 rectangle with cell C5 as the upper left
corner:</p>
<div class="sourceCode" id="cb12"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb12-1"><a href="#cb12-1" tabindex="-1"></a><span class="fu">read_excel</span>(</span>
<span id="cb12-2"><a href="#cb12-2" tabindex="-1"></a>  <span class="fu">readxl_example</span>(<span class="st">&quot;geometry.xlsx&quot;</span>),</span>
<span id="cb12-3"><a href="#cb12-3" tabindex="-1"></a>  <span class="at">col_names =</span> <span class="fu">paste</span>(<span class="st">&quot;var&quot;</span>, <span class="dv">1</span><span class="sc">:</span><span class="dv">4</span>, <span class="at">sep =</span> <span class="st">&quot;_&quot;</span>),</span>
<span id="cb12-4"><a href="#cb12-4" tabindex="-1"></a>  <span class="at">range =</span> <span class="fu">anchored</span>(<span class="st">&quot;C5&quot;</span>, <span class="fu">c</span>(<span class="dv">3</span>, <span class="dv">4</span>))</span>
<span id="cb12-5"><a href="#cb12-5" tabindex="-1"></a>)</span>
<span id="cb12-6"><a href="#cb12-6" tabindex="-1"></a><span class="co">#&gt; # A tibble: 3 × 4</span></span>
<span id="cb12-7"><a href="#cb12-7" tabindex="-1"></a><span class="co">#&gt;   var_1 var_2 var_3 var_4</span></span>
<span id="cb12-8"><a href="#cb12-8" tabindex="-1"></a><span class="co">#&gt;   &lt;chr&gt; &lt;chr&gt; &lt;lgl&gt; &lt;lgl&gt;</span></span>
<span id="cb12-9"><a href="#cb12-9" tabindex="-1"></a><span class="co">#&gt; 1 C5    D5    NA    NA   </span></span>
<span id="cb12-10"><a href="#cb12-10" tabindex="-1"></a><span class="co">#&gt; 2 C6    D6    NA    NA   </span></span>
<span id="cb12-11"><a href="#cb12-11" tabindex="-1"></a><span class="co">#&gt; 3 &lt;NA&gt;  &lt;NA&gt;  NA    NA</span></span></code></pre></div>
<p>Here we set C5 as the upper left corner and allow the other limits to
be discovered:</p>
<div class="sourceCode" id="cb13"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb13-1"><a href="#cb13-1" tabindex="-1"></a><span class="fu">read_excel</span>(</span>
<span id="cb13-2"><a href="#cb13-2" tabindex="-1"></a>  <span class="fu">readxl_example</span>(<span class="st">&quot;geometry.xlsx&quot;</span>),</span>
<span id="cb13-3"><a href="#cb13-3" tabindex="-1"></a>  <span class="at">col_names =</span> <span class="cn">FALSE</span>,</span>
<span id="cb13-4"><a href="#cb13-4" tabindex="-1"></a>  <span class="at">range =</span> <span class="fu">cell_limits</span>(<span class="fu">c</span>(<span class="dv">5</span>, <span class="dv">3</span>), <span class="fu">c</span>(<span class="cn">NA</span>, <span class="cn">NA</span>))</span>
<span id="cb13-5"><a href="#cb13-5" tabindex="-1"></a>)</span>
<span id="cb13-6"><a href="#cb13-6" tabindex="-1"></a><span class="co">#&gt; New names:</span></span>
<span id="cb13-7"><a href="#cb13-7" tabindex="-1"></a><span class="co">#&gt; • `` -&gt; `...1`</span></span>
<span id="cb13-8"><a href="#cb13-8" tabindex="-1"></a><span class="co">#&gt; • `` -&gt; `...2`</span></span>
<span id="cb13-9"><a href="#cb13-9" tabindex="-1"></a><span class="co">#&gt; # A tibble: 2 × 2</span></span>
<span id="cb13-10"><a href="#cb13-10" tabindex="-1"></a><span class="co">#&gt;   ...1  ...2 </span></span>
<span id="cb13-11"><a href="#cb13-11" tabindex="-1"></a><span class="co">#&gt;   &lt;chr&gt; &lt;chr&gt;</span></span>
<span id="cb13-12"><a href="#cb13-12" tabindex="-1"></a><span class="co">#&gt; 1 C5    D5   </span></span>
<span id="cb13-13"><a href="#cb13-13" tabindex="-1"></a><span class="co">#&gt; 2 C6    D6</span></span></code></pre></div>
</div>



<!-- code folding -->


<!-- dynamically load mathjax for compatibility with self-contained -->
<script>
  (function () {
    var script = document.createElement("script");
    script.type = "text/javascript";
    script.src  = "https://mathjax.rstudio.com/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML";
    document.getElementsByTagName("head")[0].appendChild(script);
  })();
</script>

</body>
</html>