# xls2txt: converting spreadsheets to text
## Purpose
`xls2txt` and `xsl2csv` allow converting spreadsheet files to text for
compatibility with terminals and command-line utilities (e.g. diff or
less). Despite the name, they should work with both excel (xls, xlsx
or xlsb) and OpenDocument (ods) files.
The two executables provided by this package broadly work the same
way, returning one line per row separated by a unix newline (`\n`) and
quoting field values if necessary, they differ only in their *default*
field (cell) separator:
- `xls2txt` separates cells with a TAB character
- `xls2csv` uses a comma (`,`)
The tab separator seems like a better default for readability and
compatibility with various unix utilities.
## Interface
Both utilities have the same parameter and options, differing only by
their default:
* `PATH` is a mandatory path to a spreadsheet file to convert.
* `--sheet` (`-s`) is the sheet to convert. By default, the first
sheet is converted.
`sheet` can be either the *name* of a sheet, or its position in the
workbook (starting at 1, though 0 will be treated as 1).
* `--record-separator` (`--rs`, `-r`) is the separator used between
sheet rows, it defaults to a unix newline for both utilities.
* `--field-separator` (`--fs`, `f`) is the separator used between
sheet cells, it defaults to a TAB character for `xsl2txt` and a
COMMA for `xls2csv`.
* `--formula` specifies the *formula display mode*:
- by default (`cached-value`), formulas are not displayed, if a
formula cell has a cached value that is displayed, otherwise the
cell is empty
- `if-empty` will show the cached value if there is one, but will
show the formula if there isn't one
- `always` will always display the formula of a formula cell, never
the cached value
* the converted data is written to stdout
* error messages can be written to stdout, including on success
e.g. if an error occurs while parsing formulas and the formula mode
is not the default, an error will be signaled then the formula mode
will be reset to default
* returns `0` if the entire conversion succeeded, `1` otherwise:
- no input file was provided
- the input file was not found or not recognized as a valid
spreadsheet file
- the provided record or field separator is invalid (it must be a
single ascii character)
- the specified sheet was not found
- an error was found in one of the sheet cells
- an problem occurred while writing data to stdout
## Recipes
### git text conversion
This allows viewing textual diffs of spreadsheet files using `git log`
or `git diff` rather than get an unhelpful "binary files differ":
1. create a `$HOME/.gitattributes` file, or set an arbitrary file as
the attributes file (`git config --global core.attributesFile
<filename>`)
2. in that file, associate the relevant spreadsheet extensions with
the proper category (hunk-header):
*.ods diff=spreadsheet
*.xls diff=spreadsheet
*.xlsx diff=spreadsheet
*.xlsb diff=spreadsheet
3. set `xls2txt` (or `xls2csv`), possibly configured as you desire, as
the diff text converter:
git config --global diff.spreadsheet.textconv xls2txt
## Changelog
### 1.1.0
* Add support for displaying formulas
- if there is no cached value for a formula cell
- or instead of the cached value
### 1.0.2
* Update dependencies to latest versions, bump edition
### 1.0.1
* Switch back to calamine proper as 0.16.1 guarantees sheets are
in-order
## Thanks
* [calamine](https://github.com/tafia/calamine) makes getting data out
of spreadsheet files a breeze
* [rust-csv](https://github.com/BurntSushi/rust-csv) is not strictly
necessary as generating value-separated data is fairly easy (as
opposed to consuming it), but it provides confidence that the output
will be properly quoted / escaped when necessary