Addons/tables/taraxml
< Addons
Jump to navigation
Jump to search
User Guide | Installation | Development | Categories | Git | Build Log
tables/taraxml - Reading Excel 2007 OpenXML format workbooks.
- Provides verbs to read contents from *.xlsx files,
- Read one or more sheets,
- Specify sheets by index number or name.
Browse history, source and examples using SVN.
Verbs available
readxlxsheets v Reads the names and contents of one or more worksheets from an OpenXML format workbook readxlxsheetnames v Reads the names of worksheets in an OpenXML format workbook
Installation
Use JAL/Package Manager.
The tables/taraxml addon makes use of the following addons and they must therefore also be installed:
Load tables/taraxml addon with the following line
load 'tables/taraxml'
Examples of use
Retrieve names of worksheets:
TESTWKBK=: jpath '~addons/tables/taraxml/test/test.xlsx' readxlxsheetnames TESTWKBK ┌─────────────┬─────────────┬─────────────┬──────┬──────────────┬──────────┐ │Box Int array│Box Chr array│Box Mix array│Offset│Diff Mix array│Number Fmt│ └─────────────┴─────────────┴─────────────┴──────┴──────────────┴──────────┘
If no left argument is given readxlxsheetnames will return the first sheet
readxlxsheets fnme ┌─────────────┬─────────────────────────────────────────────────────────────────────────────────┐ │Box Int array│┌───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───┐│ │ ││0 │1 │2 │3 │4 │5 │6 │7 │8 │9 │10 │11 │12 │13 │14 │15 │16 │17 │18 │19 ││ │ │├───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┤│ │ ││20 │21 │22 │23 │24 │25 │26 │27 │28 │29 │30 │31 │32 │33 │34 │35 │36 │37 │38 │39 ││ │ │├───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┤│ │ ││40 │41 │42 │43 │44 │45 │46 │47 │48 │49 │50 │51 │52 │53 │54 │55 │56 │57 │58 │59 ││ │ │├───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┤│ │ ││60 │61 │62 │63 │64 │65 │66 │67 │68 │69 │70 │71 │72 │73 │74 │75 │76 │77 │78 │79 ││ │ │├───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┤│ │ ││80 │81 │82 │83 │84 │85 │86 │87 │88 │89 │90 │91 │92 │93 │94 │95 │96 │97 │98 │99 ││ │ │├───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┤│ │ ││100│101│102│103│104│105│106│107│108│109│110│111│112│113│114│115│116│117│118│119││ │ │├───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┤│ │ ││120│121│122│123│124│125│126│127│128│129│130│131│132│133│134│135│136│137│138│139││ │ │├───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┤│ │ ││140│141│142│143│144│145│146│147│148│149│150│151│152│153│154│155│156│157│158│159││ │ │├───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┤│ │ ││160│161│162│163│164│165│166│167│168│169│170│171│172│173│174│175│176│177│178│179││ │ │├───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┤│ │ ││180│181│182│183│184│185│186│187│188│189│190│191│192│193│194│195│196│197│198│199││ │ │├───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┤│ │ ││200│201│202│203│204│205│206│207│208│209│210│211│212│213│214│215│216│217│218│219││ │ │├───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┼───┤│ │ ││220│221│222│223│224│225│226│227│228│229│230│231│232│233│234│235│236│237│238│239││ │ │└───┴───┴───┴───┴───┴───┴───┴───┴───┴───┴───┴───┴───┴───┴───┴───┴───┴───┴───┴───┘│ └─────────────┴─────────────────────────────────────────────────────────────────────────────────┘
By default, values in worksheet cells retain their type in the returned boxed matrix
]tmp=: 'Box Mix array' readxlxsheets fnme
┌─────────────┬─────────────────────────────────┐
│Box Mix array│┌───────────────┬───────────────┐│
│ ││abcd │54 ││
│ │├───────────────┼───────────────┤│
│ ││鳴箏金粟柱 │2 ││
│ │├───────────────┼───────────────┤│
│ ││4.4 │abcd ││
│ │├───────────────┼───────────────┤│
│ ││54 │鳴箏金粟柱 ││
│ │└───────────────┴───────────────┘│
└─────────────┴─────────────────────────────────┘
datatype each _1{:: tmp
┌────────┬───────┐
│literal │integer│
├────────┼───────┤
│literal │integer│
├────────┼───────┤
│floating│literal│
├────────┼───────┤
│integer │literal│
└────────┴───────┘
But you can choose to return all contents as strings:
]tmp=: 2 readxlxsheets fnme;<1 ┌─────────────┬────────────────────────────────────┐ │Box Mix array│┌──────────────────┬───────────────┐│ │ ││abcd │54 ││ │ │├──────────────────┼───────────────┤│ │ ││鳴箏金粟柱 │2 ││ │ │├──────────────────┼───────────────┤│ │ ││4.4000000000000004│abcd ││ │ │├──────────────────┼───────────────┤│ │ ││54 │鳴箏金粟柱 ││ │ │└──────────────────┴───────────────┘│ └─────────────┴────────────────────────────────────┘
You can choose to return more than one worksheet:
]tmp=: 2 5 readxlxsheets fnme ┌─────────────┬────────────────────────────────────────┐ │Box Mix array│┌───────────────┬───────────────┐ │ │ ││abcd │54 │ │ │ │├───────────────┼───────────────┤ │ │ ││鳴箏金粟柱 │2 │ │ │ │├───────────────┼───────────────┤ │ │ ││4.4 │abcd │ │ │ │├───────────────┼───────────────┤ │ │ ││54 │鳴箏金粟柱 │ │ │ │└───────────────┴───────────────┘ │ ├─────────────┼────────────────────────────────────────┤ │Number Fmt │┌──┬──────┬──────┬─────┬──────────┬────┐│ │ ││5 │50000 │0.005 │5e_7 │500000000 │5e8 ││ │ │├──┼──────┼──────┼─────┼──────────┼────┤│ │ ││_5│_50000│_0.005│_5e_7│_500000000│_5e8││ │ │└──┴──────┴──────┴─────┴──────────┴────┘│ └─────────────┴────────────────────────────────────────┘
To see more examples of usage, open and inspect the test script.
Authors
Contributed by Ric Sherlock with much advice and assistance from Bill Lam and Oleg Kobchenko.
Suggestions and/or SVN improvements to the addon are welcome.
See Also
- tables/tara addon - Utility for reading and writing pre Excel 2007 workbooks.
- tables/csv addon - Utilities for working with comma-separated-value (CSV) files.