Interfaces/Excel

There are several ways to read and write Excel and other spreadsheet data from J.

Clipboard

You can use the clipboard to transfer cell selections to and from J. This is convenient for occasional use. The standard utilities wdclipread and wdclipwrite read and write the clipboard, while clipfmt and clipunfmt in the format script, convert to and from clipboard format.

For example, in J:

   load 'format'
   wdclipwrite clipfmt i.3 4

This can now be pasted into a spreadsheet.

To go the other way, select and copy a region in the spreadsheet, then in J:

   clipunfmt wdclipread''
┌─────────┬──┐
│Sales    │80│
├─────────┼──┤
│Expenses │50│
├─────────┼──┤
│Net Sales│30│
└─────────┴──┘

CSV

The csv (comma separated values) script has utilities to read and write csv files. Most spreadsheets can be saved in csv format (one workbook at a time), and this can be read into J.

   load 'csv'
   3 {. readcsv jpath '~system/examples/data/tdata.csv'
┌──────────────┬───┬────┬────────┬────────┬──────┐
│NAME          │SEX│DEPT│DOB     │DOH     │SALARY│
├──────────────┼───┼────┼────────┼────────┼──────┤
│Macdonald B   │F  │D101│19590621│19780515│32591 │
├──────────────┼───┼────┼────────┼────────┼──────┤
│Genereaux S   │F  │D103│19450327│19660207│95415 │
└──────────────┴───┴────┴────────┴────────┴──────┘

A boxed table can be written out:

   dat writecsv jpath '~temp/data.csv'

Note that the csv package has general-purpose utilities. In practice, there may be more efficient methods, depending on the data. For example, if the data is a boxed table of formatted numbers, then the following savecsv verb is much faster than writecsv:

savecsv=: 4 : 0
dat=. ; x ,each ','
ndx=. (-{:$x) {:\ I. dat = ','
dat=. LF ndx } dat
dat fwrites y
)

   D=:  (8!:0) 0.01 * _1e5+?100 100$1e6
   F1=: jpath '~temp/t1.csv'
   timex &> 'D savecsv F1';'D writecsv F1'
0.012352 0.271404

Tara

The Tara package reads and writes files in Excel format. It supports the biff8 format used in Excel 97/2000/XP/2003, as well as formats used in earlier versions of Excel. It can also work with spreadsheets like Gnumeric that support Excel formats.

This provides direct access to the Excel file from J, and is the best method for reading and writing data.

OLE

Under Windows, an Excel spreadsheet can be accessed via OLE. This is not as convenient as Tara for reading and writing data, but has the advantage that Excel methods can also be called directly. For more information, see the J help, User Manual|OLE & OCX, and supporting macros and scripts in directory ~system/examples/ole/excel.

OLEExcel

OLEExcel is Devon McCormick's code to read and write a spreadsheet using OLE.

Excel Addon

The excel addon reads spreadsheets using OLE.

OOo

The OpenOffice script provides an infra-structure to use OLE IDispatch interface which is needed for OpenOffice.org office suite. It also works with Excel or other OLE automation server.