Addons/tables/taraxml

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:

• • arc/zip
  • general/pcall
  • xml/xslt

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.