Ignore total lines

[stephlocke]

Ignore total lines

[hadley]

How would you distinguish a total line from a data line?

[stephlocke]

In the underlying xml (for an .xlsx) it has a table relationship xml which
includes the attribute totalsRowCount.

<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
id="1" name="Table1" displayName="Table1" ref="A1:B4" totalsRowCount="1">
...
</table>

Not sure if that's helpful in terms of how you're interacting with the spreadsheets. Example excel turned to it's zip

[hadley]

Does the ref A1:B4 include the totals row?

I'm not currently using the tables element at all, but it might make sense to expose this too - it might be nice to have a way to print out all the tables in each worksheet. Do you happen to know if tables happen automatically or do you create them manually?

[stephlocke]

Row 4 is the Total row, yes

Tables aren't automatic - you make them with the Format as Table option.
It's incredibly handy functionality. It takes care of formatting, adds
declarative syntax e.g. MyTable[Mycolum], and since it's all basically
dynamic named ranges under the hood, it greatly reduces risk of errors in
formulae.

Steph Locke

@SteffLocke https://twitter.com/stefflocke | LinkedIn
http://uk.linkedin.com/in/stephanielocke/ | itsalocke.com | 07951 787 022
SQL Cardiff http://www.meetup.com/Cardiff-SQL-Server-User-Group | CaRdiff
R http://www.meetup.com/Cardiff-R-User-Group/ | diff.NET
http://www.meetup.com/diff-NET/ | SQLRelay http://sqlrelay.co.uk

On 9 April 2015 at 20:56, Hadley Wickham [email protected] wrote:

Does the ref A1:B4 include the totals row?

I'm not currently using the tables element at all, but it might make sense
to expose this too - it might be nice to have a way to print out all the
tables in each worksheet. Do you happen to know if tables happen
automatically or do you create them manually?

—
Reply to this email directly or view it on GitHub
#54 (comment).

[jennybc]

@stephlocke Will you share a good example?

How to provide a readxl reprex

We're in a much better position to address your issue if you can provide a reprex (reproducible example). Provide as much of this as you can:

• An actual xls or xlsx file. Preferably stripped down to the minimal size and complexity to demonstrate your point. And, obviously, stripped of any sensitive data.
• A small bit of R code that uses readxl on the provided xls or xlsx file and demonstrates your point.
  • Consider using the reprex package to prepare this. In addition to nice formatting, this ensures your reprex is self-contained. Currently GitHub-only, but going to CRAN soon.
• Any details about your environment that seem clearly relevant, such as operating system.
  reprex(..., si = TRUE)
  will append a standard summary, folded neatly away, at the bottom of your reprex.

How to provide the xls/xlsx file? In order of preference:

1. Attach the file directly to your issue. Instructions are always at the bottom of the issue or comment box.
2. Share via DropBox or Google Drive and provide the link in your issue.
3. Explain you absolutely cannot provide a relevant file via github.com and offer to provide privately.
4. Don't share a file and realize you're hoping for, e.g., a bug fix with no concrete example to go on.

[jennybc]

We're not going to implement this in readxl at this time. The focus is clearly on reading static data.

I think this functionality makes more sense to build into packages that target tricky spreadsheets and sheet logic, e.g. tidyxl and the rsheets packages.