ref: 59ed389a6eb7d405eb44d9644d69993e814dccdf
dir: /sys/src/cmd/aux/msexceltables.c/
/* msexceltables.c Steve Simon 5-Jan-2005 */
#include <u.h>
#include <libc.h>
#include <bio.h>
#include <ctype.h>
enum {
Tillegal = 0,
Tnumber, // cell types
Tlabel,
Tindex,
Tbool,
Terror,
Ver8 = 0x600, // only BIFF8 and BIFF8x files support unicode
Nwidths = 4096,
};
typedef struct Biff Biff;
typedef struct Col Col;
typedef struct Row Row;
struct Row {
Row *next; // next row
int r; // row number
Col *col; // list of cols in row
};
struct Col {
Col *next; // next col in row
int c; // col number
int f; // index into formating table (Xf)
int type; // type of value for union below
union { // value
int index; // index into string table (Strtab)
int error;
int bool;
char *label;
double number;
};
};
struct Biff {
Biobuf *bp; // input file
int op; // current record type
int len; // length of current record
};
// options
static int Nopad = 0; // disable padding cells to colum width
static int Trunc = 0; // truncate cells to colum width
static int All = 0; // dump all sheet types, Worksheets only by default
static char *Delim = " "; // field delimiter
static char *Sheetrange = nil; // range of sheets wanted
static char *Columnrange = nil; // range of collums wanted
static int Debug = 0;
// file scope
static int Defwidth = 10; // default colum width if non given
static int Biffver; // file vesion
static int Datemode; // date ref: 1899-Dec-31 or 1904-jan-1
static char **Strtab = nil; // label contents heap
static int Nstrtab = 0; // # of above
static int *Xf; // array of extended format indices
static int Nxf = 0; // # of above
static Biobuf *bo; // stdout (sic)
static int Doquote = 1; // quote text fields if they are rc(1) unfriendly
// table scope
static int Width[Nwidths]; // array of colum widths
static int Ncols = -1; // max colums in table used
static int Content = 0; // type code for contents of sheet
static Row *Root = nil; // one worksheet's worth of cells
static char *Months[] = { "Jan", "Feb", "Mar", "Apr", "May", "Jun",
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };
static char *Errmsgs[] = {
[0x0] "#NULL!", // intersection of two cell ranges is empty
[0x7] "#DIV/0!", // division by zero
[0xf] "#VALUE!", // wrong type of operand
[0x17] "#REF!", // illegal or deleted cell reference
[0x1d] "#NAME?", // wrong function or range name
[0x24] "#NUM!", // value range overflow
[0x2a] "#N/A!", // argument of function not available
};
int
wanted(char *range, int here)
{
int n, s;
char *p;
if (! range)
return 1;
s = -1;
p = range;
while(1){
n = strtol(p, &p, 10);
switch(*p){
case 0:
if(n == here)
return 1;
if(s != -1 && here > s && here < n)
return 1;
return 0;
case ',':
if(n == here)
return 1;
if(s != -1 && here > s && here < n)
return 1;
s = -1;
p++;
break;
case '-':
if(n == here)
return 1;
s = n;
p++;
break;
default:
sysfatal("%s malformed range spec", range);
break;
}
}
}
void
cell(int r, int c, int f, int type, void *val)
{
Row *row, *nrow;
Col *col, *ncol;
if(c > Ncols)
Ncols = c;
if((ncol = malloc(sizeof(Col))) == nil)
sysfatal("no memory");
ncol->c = c;
ncol->f = f;
ncol->type = type;
ncol->next = nil;
switch(type){
case Tnumber: ncol->number = *(double *)val; break;
case Tlabel: ncol->label = (char *)val; break;
case Tindex: ncol->index = *(int *)val; break;
case Tbool: ncol->bool = *(int *)val; break;
case Terror: ncol->error = *(int *)val; break;
default: sysfatal("can't happen error");
}
if(Root == nil || Root->r > r){
if((nrow = malloc(sizeof(Row))) == nil)
sysfatal("no memory");
nrow->col = ncol;
ncol->next = nil;
nrow->r = r;
nrow->next = Root;
Root = nrow;
return;
}
for(row = Root; row; row = row->next){
if(row->r == r){
if(row->col->c > c){
ncol->next = row->col;
row->col = ncol;
return;
}
else{
for(col = row->col; col; col = col->next)
if(col->next == nil || col->next->c > c){
ncol->next = col->next;
col->next = ncol;
return;
}
}
}
if(row->next == nil || row->next->r > r){
if((nrow = malloc(sizeof(Row))) == nil)
sysfatal("no memory");
nrow->col = ncol;
nrow->r = r;
nrow->next = row->next;
row->next = nrow;
return;
}
}
sysfatal("cannot happen error");
}
struct Tm *
bifftime(double num)
{
long long t = num;
/* Beware - These epochs are wrong, this
* is due to Excel still remaining compatible
* with Lotus-123, which incorrectly believed 1900
* was a leap year
*/
if(Datemode)
t -= 24107; // epoch = 1/1/1904
else
t -= 25569; // epoch = 31/12/1899
t *= 60*60*24;
return localtime((long)t);
}
void
numfmt(int fmt, int min, int max, double num)
{
char buf[1024];
struct Tm *tm;
if(fmt == 9)
snprint(buf, sizeof(buf),"%.0f%%", num);
else
if(fmt == 10)
snprint(buf, sizeof(buf),"%f%%", num);
else
if(fmt == 11 || fmt == 48)
snprint(buf, sizeof(buf),"%e", num);
else
if(fmt >= 14 && fmt <= 17){
tm = bifftime(num);
snprint(buf, sizeof(buf),"%d-%s-%d",
tm->mday, Months[tm->mon], tm->year+1900);
}
else
if((fmt >= 18 && fmt <= 21) || (fmt >= 45 && fmt <= 47)){
tm = bifftime(num);
snprint(buf, sizeof(buf),"%02d:%02d:%02d", tm->hour, tm->min, tm->sec);
}
else
if(fmt == 22){
tm = bifftime(num);
snprint(buf, sizeof(buf),"%02d:%02d:%02d %d-%s-%d",
tm->hour, tm->min, tm->sec,
tm->mday, Months[tm->mon], tm->year+1900);
}else
snprint(buf, sizeof(buf),"%g", num);
Bprint(bo, "%-*.*q", min, max, buf);
}
void
dump(void)
{
Row *r;
Col *c, *c1;
char *strfmt;
int i, n, last, min, max;
if(Doquote)
strfmt = "%-*.*q";
else
strfmt = "%-*.*s";
for(r = Root; r; r = r->next){
n = 1;
for(c = r->col; c; c = c->next){
n++;
if(! wanted(Columnrange, n))
continue;
if(c->c < 0 || c->c >= Nwidths || (min = Width[c->c]) == 0)
min = Defwidth;
if((c->next && c->c == c->next->c) || Nopad)
min = 0;
max = -1;
if(Trunc && min > 2)
max = min -2; // FIXME: -2 because of bug %q format ?
switch(c->type){
case Tnumber:
if(Xf == nil || Xf[c->f] == 0)
Bprint(bo, "%-*.*g", min, max, c->number);
else
numfmt(Xf[c->f], min, max, c->number);
break;
case Tlabel:
Bprint(bo, strfmt, min, max, c->label);
break;
case Tbool:
Bprint(bo, strfmt, min, max, (c->bool)? "True": "False");
break;
case Tindex:
if(c->index < 0 || c->index >= Nstrtab)
sysfatal("SST string out of range - corrupt file?");
Bprint(bo, strfmt, min, max, Strtab[c->index]);
break;
case Terror:
if(c->error < 0 || c->error >= nelem(Errmsgs) || !Errmsgs[c->error])
Bprint(bo, "#ERR=%d", c->index);
else
Bprint(bo, strfmt, min, max, Errmsgs[c->error]);
break;
default:
sysfatal("cannot happen error");
break;
}
last = 1;
for(i = n+1, c1 = c->next; c1; c1 = c1->next, i++)
if(wanted(Columnrange, i)){
last = 0;
break;
}
if(! last){
if(c->next->c == c->c) // bar charts
Bprint(bo, "=");
else{
Bprint(bo, "%s", Delim);
for(i = c->c; c->next && i < c->next->c -1; i++)
Bprint(bo, "%-*.*s%s", min, max, "", Delim);
}
}
}
if(r->next)
for(i = r->r; i < r->next->r; i++)
Bprint(bo, "\n");
}
Bprint(bo, "\n");
}
void
release(void)
{
Row *r, *or;
Col *c, *oc;
r = Root;
while(r){
c = r->col;
while(c){
if(c->type == Tlabel)
free(c->label);
oc = c;
c = c->next;
free(oc);
}
or = r;
r = r->next;
free(or);
}
Root = nil;
memset(Width, 0, sizeof(Width));
Ncols = -1;
}
void
skip(Biff *b, int len)
{
assert(len <= b->len);
if(Bseek(b->bp, len, 1) == -1)
sysfatal("seek failed - %r");
b->len -= len;
}
void
gmem(Biff *b, void *p, int n)
{
if(b->len < n)
sysfatal("short record %d < %d", b->len, n);
if(Bread(b->bp, p, n) != n)
sysfatal("unexpected EOF - %r");
b->len -= n;
}
void
xd(Biff *b)
{
uvlong off;
uchar buf[16];
int addr, got, n, i, j;
addr = 0;
off = Boffset(b->bp);
while(addr < b->len){
n = (b->len >= sizeof(buf))? sizeof(buf): b->len;
got = Bread(b->bp, buf, n);
Bprint(bo, " %6d ", addr);
addr += n;
for(i = 0; i < got; i++)
Bprint(bo, "%02x ", buf[i]);
for(j = i; j < 16; j++)
Bprint(bo, " ");
Bprint(bo, " ");
for(i = 0; i < got; i++)
Bprint(bo, "%c", isprint(buf[i])? buf[i]: '.');
Bprint(bo, "\n");
}
Bseek(b->bp, off, 0);
}
static int
getrec(Biff *b)
{
int c;
if((c = Bgetc(b->bp)) == -1)
return -1; // real EOF
b->op = c;
if((c = Bgetc(b->bp)) == -1)
sysfatal("unexpected EOF - %r");
b->op |= c << 8;
if((c = Bgetc(b->bp)) == -1)
sysfatal("unexpected EOF - %r");
b->len = c;
if((c = Bgetc(b->bp)) == -1)
sysfatal("unexpected EOF - %r");
b->len |= c << 8;
if(b->op == 0 && b->len == 0)
return -1;
if(Debug){
Bprint(bo, "op=0x%x len=%d\n", b->op, b->len);
xd(b);
}
return 0;
}
static uvlong
gint(Biff *b, int n)
{
int i, c;
uvlong vl, rc;
if(b->len < n)
return -1;
rc = 0;
for(i = 0; i < n; i++){
if((c = Bgetc(b->bp)) == -1)
sysfatal("unexpected EOF - %r");
b->len--;
vl = c;
rc |= vl << (8*i);
}
return rc;
}
double
grk(Biff *b)
{
int f;
uvlong n;
double d;
n = gint(b, 4);
f = n & 3;
n &= ~3LL;
if(f & 2){
d = n / 4.0;
}
else{
n <<= 32;
memcpy(&d, &n, sizeof(d));
}
if(f & 1)
d /= 100.0;
return d;
}
double
gdoub(Biff *b)
{
double d;
uvlong n = gint(b, 8);
memcpy(&d, &n, sizeof(n));
return d;
}
char *
gstr(Biff *b, int len_width)
{
Rune r;
char *buf, *p;
int nch, w, ap, ln, rt, opt;
enum {
Unicode = 1,
Asian_phonetic = 4,
Rich_text = 8,
};
if(b->len < len_width){
if(getrec(b) == -1)
sysfatal("starting STRING expected CONTINUE, got EOF");
if(b->op != 0x03c)
sysfatal("starting STRING expected CONTINUE, got op=0x%x", b->op);
}
ln = gint(b, len_width);
if(Biffver != Ver8){
if((buf = calloc(ln+1, sizeof(char))) == nil)
sysfatal("no memory");
gmem(b, buf, ln);
return buf;
}
if((buf = calloc(ln+1, sizeof(char)*UTFmax)) == nil)
sysfatal("no memory");
p = buf;
if(ln == 0)
return buf;
nch = 0;
*buf = 0;
opt = gint(b, 1);
if(opt & Rich_text)
rt = gint(b, 2);
else
rt = 0;
if(opt & Asian_phonetic)
ap = gint(b, 4);
else
ap = 0;
for(;;){
w = (opt & Unicode)? sizeof(Rune): sizeof(char);
while(b->len > 0){
r = gint(b, w);
p += runetochar(p, &r);
if(++nch >= ln){
if(rt)
skip(b, rt*4);
if(ap)
skip(b, ap);
return buf;
}
}
if(getrec(b) == -1)
sysfatal("in STRING expected CONTINUE, got EOF");
if(b->op != 0x03c)
sysfatal("in STRING expected CONTINUE, got op=0x%x", b->op);
opt = gint(b, 1);
}
}
void
sst(Biff *b)
{
int n;
skip(b, 4); // total # strings
Nstrtab = gint(b, 4); // # unique strings
if((Strtab = calloc(Nstrtab, sizeof(char *))) == nil)
sysfatal("no memory");
for(n = 0; n < Nstrtab; n++)
Strtab[n] = gstr(b, 2);
}
void
boolerr(Biff *b)
{
int r = gint(b, 2); // row
int c = gint(b, 2); // col
int f = gint(b, 2); // formatting ref
int v = gint(b, 1); // bool value / err code
int t = gint(b, 1); // type
cell(r, c, f, (t)? Terror: Tbool, &v);
}
void
rk(Biff *b)
{
int r = gint(b, 2); // row
int c = gint(b, 2); // col
int f = gint(b, 2); // formatting ref
double v = grk(b); // value
cell(r, c, f, Tnumber, &v);
}
void
mulrk(Biff *b)
{
int r = gint(b, 2); // row
int c = gint(b, 2); // first col
while(b->len >= 6){
int f = gint(b, 2); // formatting ref
double v = grk(b); // value
cell(r, c++, f, Tnumber, &v);
}
}
void
number(Biff *b)
{
int r = gint(b, 2); // row
int c = gint(b, 2); // col
int f = gint(b, 2); // formatting ref
double v = gdoub(b); // double
cell(r, c, f, Tnumber, &v);
}
void
label(Biff *b)
{
int r = gint(b, 2); // row
int c = gint(b, 2); // col
int f = gint(b, 2); // formatting ref
char *s = gstr(b, 2); // byte string
cell(r, c, f, Tlabel, s);
}
void
labelsst(Biff *b)
{
int r = gint(b, 2); // row
int c = gint(b, 2); // col
int f = gint(b, 2); // formatting ref
int i = gint(b, 2); // sst string ref
cell(r, c, f, Tindex, &i);
}
void
bof(Biff *b)
{
Biffver = gint(b, 2);
Content = gint(b, 2);
}
void
defcolwidth(Biff *b)
{
Defwidth = gint(b, 2);
}
void
datemode(Biff *b)
{
Datemode = gint(b, 2);
}
void
eof(Biff *b)
{
int i;
struct {
int n;
char *s;
} names[] = {
0x005, "Workbook globals",
0x006, "Visual Basic module",
0x010, "Worksheet",
0x020, "Chart",
0x040, "Macro sheet",
0x100, "Workspace file",
};
static int sheet = 0;
if(! wanted(Sheetrange, ++sheet)){
release();
return;
}
if(Ncols != -1){
if(All){
for(i = 0; i < nelem(names); i++)
if(names[i].n == Content){
Bprint(bo, "\n# contents %s\n", names[i].s);
dump();
}
}
else
if(Content == 0x10) // Worksheet
dump();
}
release();
USED(b);
}
void
colinfo(Biff *b)
{
int c;
int c1 = gint(b, 2);
int c2 = gint(b, 2);
int w = gint(b, 2);
if(c1 < 0)
sysfatal("negative column number (%d)", c1);
if(c2 >= Nwidths)
sysfatal("too many columns (%d > %d)", c2, Nwidths);
w /= 256;
if(w > 100)
w = 100;
if(w < 0)
w = 0;
for(c = c1; c <= c2; c++)
Width[c] = w;
}
void
xf(Biff *b)
{
int fmt;
static int nalloc = 0;
skip(b, 2);
fmt = gint(b, 2);
if(nalloc >= Nxf){
nalloc += 20;
if((Xf = realloc(Xf, nalloc*sizeof(int))) == nil)
sysfatal("no memory");
}
Xf[Nxf++] = fmt;
}
void
writeaccess(Biff *b)
{
Bprint(bo, "# author %s\n", gstr(b, 2));
}
void
codepage(Biff *b)
{
int codepage = gint(b, 2);
if(codepage != 1200) // 1200 == UTF-16
Bprint(bo, "# codepage %d\n", codepage);
}
void
xls2csv(Biobuf *bp)
{
int i;
Biff biff, *b;
struct {
int op;
void (*func)(Biff *);
} dispatch[] = {
0x000a, eof,
0x0022, datemode,
0x0042, codepage,
0x0055, defcolwidth,
0x005c, writeaccess,
0x007d, colinfo,
0x00bd, mulrk,
0x00fc, sst,
0x00fd, labelsst,
0x0203, number,
0x0204, label,
0x0205, boolerr,
0x027e, rk,
0x0809, bof,
0x00e0, xf,
};
b = &biff;
b->bp = bp;
while(getrec(b) != -1){
for(i = 0; i < nelem(dispatch); i++)
if(b->op == dispatch[i].op)
(*dispatch[i].func)(b);
skip(b, b->len);
}
}
void
usage(void)
{
fprint(2, "usage: %s [-Danqt] [-w worksheets] [-c columns] [-d delim] /mnt/doc/Workbook\n", argv0);
exits("usage");
}
void
main(int argc, char *argv[])
{
int i;
Biobuf bin, bout, *bp;
ARGBEGIN{
case 'D':
Debug = 1;
break;
case 'a':
All = 1;
break;
case 'q':
Doquote = 0;
break;
case 'd':
Delim = EARGF(usage());
break;
case 'n':
Nopad = 1;
break;
case 't':
Trunc = 1;
break;
case 'c':
Columnrange = EARGF(usage());
break;
case 'w':
Sheetrange = EARGF(usage());
break;
default:
usage();
break;
}ARGEND;
if(argc != 1)
usage();
bo = &bout;
quotefmtinstall();
Binit(bo, OWRITE, 1);
if(argc > 0) {
for(i = 0; i < argc; i++){
if((bp = Bopen(argv[i], OREAD)) == nil)
sysfatal("%s cannot open - %r", argv[i]);
xls2csv(bp);
Bterm(bp);
}
} else {
Binit(&bin, 0, OREAD);
xls2csv(&bin);
}
exits(0);
}