aboutsummaryrefslogtreecommitdiffstats
path: root/internal/xls
diff options
context:
space:
mode:
Diffstat (limited to 'internal/xls')
-rw-r--r--internal/xls/xls.go364
-rw-r--r--internal/xls/xls_test.go99
2 files changed, 463 insertions, 0 deletions
diff --git a/internal/xls/xls.go b/internal/xls/xls.go
new file mode 100644
index 0000000..402f9f4
--- /dev/null
+++ b/internal/xls/xls.go
@@ -0,0 +1,364 @@
+// Package xls reads the first worksheet of a legacy Excel 97-2003 (BIFF8)
+// workbook into a grid of strings. It covers the cell records Multicase
+// exports use; formatting, formulas beyond their cached value and dates are
+// out of scope.
+package xls
+
+import (
+ "bytes"
+ "encoding/binary"
+ "errors"
+ "fmt"
+ "io"
+ "math"
+ "os"
+ "strconv"
+ "unicode/utf16"
+
+ "github.com/richardlehane/mscfb"
+ "golang.org/x/text/encoding/charmap"
+)
+
+const (
+ recBOF = 0x0809
+ recEOF = 0x000A
+ recBoundSheet = 0x0085
+ recSST = 0x00FC
+ recContinue = 0x003C
+ recLabelSST = 0x00FD
+ recLabel = 0x0204
+ recNumber = 0x0203
+ recRK = 0x027E
+ recMulRK = 0x00BD
+ recFormula = 0x0006
+ recString = 0x0207
+ recBoolErr = 0x0205
+)
+
+// ReadFile returns the first worksheet of the workbook at path as rows of
+// cell strings. Numbers are rendered without exponent or trailing zeros.
+func ReadFile(path string) ([][]string, error) {
+ f, err := os.Open(path)
+ if err != nil {
+ return nil, err
+ }
+ defer f.Close()
+
+ doc, err := mscfb.New(f)
+ if err != nil {
+ return nil, fmt.Errorf("not an Excel 97-2003 file: %w", err)
+ }
+ for entry, err := doc.Next(); err == nil; entry, err = doc.Next() {
+ if entry.Name != "Workbook" && entry.Name != "Book" {
+ continue
+ }
+ stream, err := io.ReadAll(entry)
+ if err != nil {
+ return nil, err
+ }
+ return ParseWorkbook(stream)
+ }
+ return nil, errors.New("workbook stream not found")
+}
+
+type record struct {
+ typ uint16
+ data []byte
+}
+
+func splitRecords(stream []byte) ([]record, []int, error) {
+ var recs []record
+ var offsets []int
+ for pos := 0; pos+4 <= len(stream); {
+ typ := binary.LittleEndian.Uint16(stream[pos:])
+ n := int(binary.LittleEndian.Uint16(stream[pos+2:]))
+ if pos+4+n > len(stream) {
+ return nil, nil, fmt.Errorf("truncated record 0x%04x at offset %d", typ, pos)
+ }
+ recs = append(recs, record{typ, stream[pos+4 : pos+4+n]})
+ offsets = append(offsets, pos)
+ pos += 4 + n
+ }
+ return recs, offsets, nil
+}
+
+// ParseWorkbook parses a raw BIFF8 workbook stream.
+func ParseWorkbook(stream []byte) ([][]string, error) {
+ recs, offsets, err := splitRecords(stream)
+ if err != nil {
+ return nil, err
+ }
+
+ var sst []string
+ sheetOffset := -1
+ for i := 0; i < len(recs); i++ {
+ r := recs[i]
+ switch r.typ {
+ case recBoundSheet:
+ if sheetOffset < 0 && len(r.data) >= 4 {
+ sheetOffset = int(binary.LittleEndian.Uint32(r.data))
+ }
+ case recSST:
+ segments := [][]byte{r.data}
+ for i+1 < len(recs) && recs[i+1].typ == recContinue {
+ i++
+ segments = append(segments, recs[i].data)
+ }
+ if sst, err = parseSST(segments); err != nil {
+ return nil, err
+ }
+ }
+ if r.typ == recEOF {
+ break
+ }
+ }
+ if sheetOffset < 0 {
+ return nil, errors.New("workbook has no worksheets")
+ }
+
+ start := -1
+ for i, off := range offsets {
+ if off == sheetOffset && recs[i].typ == recBOF {
+ start = i
+ break
+ }
+ }
+ if start < 0 {
+ return nil, errors.New("worksheet BOF not found")
+ }
+
+ var grid [][]string
+ set := func(p pos, v string) {
+ for len(grid) <= p.row {
+ grid = append(grid, nil)
+ }
+ for len(grid[p.row]) <= p.col {
+ grid[p.row] = append(grid[p.row], "")
+ }
+ grid[p.row][p.col] = v
+ }
+
+ for i := start + 1; i < len(recs) && recs[i].typ != recEOF; i++ {
+ d := recs[i].data
+ if len(d) < 6 && recs[i].typ != recString {
+ continue
+ }
+ switch recs[i].typ {
+ case recLabelSST:
+ idx := int(binary.LittleEndian.Uint32(d[6:]))
+ if idx >= len(sst) {
+ return nil, fmt.Errorf("shared string index %d out of range", idx)
+ }
+ set(cell(d), sst[idx])
+ case recLabel:
+ s, err := readString(d[6:])
+ if err != nil {
+ return nil, err
+ }
+ set(cell(d), s)
+ case recNumber:
+ set(cell(d), formatNumber(math.Float64frombits(binary.LittleEndian.Uint64(d[6:]))))
+ case recRK:
+ set(cell(d), formatNumber(decodeRK(binary.LittleEndian.Uint32(d[6:]))))
+ case recMulRK:
+ p := cell(d)
+ for off := 4; off+6 <= len(d)-2; off += 6 {
+ set(p, formatNumber(decodeRK(binary.LittleEndian.Uint32(d[off+2:]))))
+ p.col++
+ }
+ case recFormula:
+ p := cell(d)
+ res := d[6:14]
+ if binary.LittleEndian.Uint16(res[6:]) != 0xFFFF {
+ set(p, formatNumber(math.Float64frombits(binary.LittleEndian.Uint64(res))))
+ } else if res[0] == 0 && i+1 < len(recs) && recs[i+1].typ == recString {
+ s, err := readString(recs[i+1].data)
+ if err != nil {
+ return nil, err
+ }
+ set(p, s)
+ }
+ case recBoolErr:
+ if d[7] == 0 {
+ set(cell(d), strconv.Itoa(int(d[6])))
+ }
+ }
+ }
+ return grid, nil
+}
+
+type pos struct{ row, col int }
+
+func cell(d []byte) pos {
+ return pos{int(binary.LittleEndian.Uint16(d)), int(binary.LittleEndian.Uint16(d[2:]))}
+}
+
+// decodeRK decodes a BIFF RK number. The integer form is a signed 30-bit
+// value: it must be shifted as int32, or negative amounts come out as
+// 2^30 minus their magnitude.
+func decodeRK(rk uint32) float64 {
+ var v float64
+ if rk&0x02 != 0 {
+ v = float64(int32(rk) >> 2)
+ } else {
+ v = math.Float64frombits(uint64(rk&0xFFFFFFFC) << 32)
+ }
+ if rk&0x01 != 0 {
+ v /= 100
+ }
+ return v
+}
+
+func formatNumber(f float64) string {
+ return strconv.FormatFloat(f, 'f', -1, 64)
+}
+
+// readString reads an XLUnicodeString (16-bit length) from b.
+func readString(b []byte) (string, error) {
+ if len(b) < 3 {
+ return "", errors.New("short string")
+ }
+ n := int(binary.LittleEndian.Uint16(b))
+ wide := b[2]&0x01 != 0
+ size := n
+ if wide {
+ size *= 2
+ }
+ if len(b) < 3+size {
+ return "", errors.New("short string")
+ }
+ return decodeChars(b[3:3+size], wide), nil
+}
+
+func decodeChars(b []byte, wide bool) string {
+ if !wide {
+ // "Compressed" BIFF8 strings are the low bytes of UTF-16, i.e. Latin-1.
+ s, _ := charmap.ISO8859_1.NewDecoder().Bytes(b)
+ return string(s)
+ }
+ u := make([]uint16, len(b)/2)
+ for i := range u {
+ u[i] = binary.LittleEndian.Uint16(b[2*i:])
+ }
+ return string(utf16.Decode(u))
+}
+
+// sstReader walks an SST record and its CONTINUE records. Character data
+// that crosses a record boundary restarts with a fresh option byte.
+type sstReader struct {
+ segs [][]byte
+ seg int
+ pos int
+}
+
+func (r *sstReader) next() error {
+ for r.pos >= len(r.segs[r.seg]) {
+ if r.seg+1 >= len(r.segs) {
+ return io.ErrUnexpectedEOF
+ }
+ r.seg++
+ r.pos = 0
+ }
+ return nil
+}
+
+func (r *sstReader) bytes(n int) ([]byte, error) {
+ var out []byte
+ for n > 0 {
+ if err := r.next(); err != nil {
+ return nil, err
+ }
+ cur := r.segs[r.seg]
+ take := min(n, len(cur)-r.pos)
+ out = append(out, cur[r.pos:r.pos+take]...)
+ r.pos += take
+ n -= take
+ }
+ return out, nil
+}
+
+func (r *sstReader) chars(n int, wide bool) (string, error) {
+ var buf bytes.Buffer
+ for n > 0 {
+ if r.pos >= len(r.segs[r.seg]) {
+ if r.seg+1 >= len(r.segs) {
+ return "", io.ErrUnexpectedEOF
+ }
+ r.seg++
+ r.pos = 1
+ wide = r.segs[r.seg][0]&0x01 != 0
+ continue
+ }
+ cur := r.segs[r.seg]
+ width := 1
+ if wide {
+ width = 2
+ }
+ take := min(n, (len(cur)-r.pos)/width)
+ if take == 0 {
+ return "", errors.New("malformed shared string")
+ }
+ buf.WriteString(decodeChars(cur[r.pos:r.pos+take*width], wide))
+ r.pos += take * width
+ n -= take
+ }
+ return buf.String(), nil
+}
+
+func (r *sstReader) u16() (int, error) {
+ b, err := r.bytes(2)
+ if err != nil {
+ return 0, err
+ }
+ return int(binary.LittleEndian.Uint16(b)), nil
+}
+
+func (r *sstReader) u32() (int, error) {
+ b, err := r.bytes(4)
+ if err != nil {
+ return 0, err
+ }
+ return int(binary.LittleEndian.Uint32(b)), nil
+}
+
+func parseSST(segs [][]byte) ([]string, error) {
+ r := &sstReader{segs: segs}
+ if _, err := r.u32(); err != nil {
+ return nil, err
+ }
+ unique, err := r.u32()
+ if err != nil {
+ return nil, err
+ }
+ out := make([]string, 0, unique)
+ for range unique {
+ n, err := r.u16()
+ if err != nil {
+ return nil, err
+ }
+ flags, err := r.bytes(1)
+ if err != nil {
+ return nil, err
+ }
+ runs, ext := 0, 0
+ if flags[0]&0x08 != 0 {
+ if runs, err = r.u16(); err != nil {
+ return nil, err
+ }
+ }
+ if flags[0]&0x04 != 0 {
+ if ext, err = r.u32(); err != nil {
+ return nil, err
+ }
+ }
+ s, err := r.chars(n, flags[0]&0x01 != 0)
+ if err != nil {
+ return nil, err
+ }
+ if _, err := r.bytes(4*runs + ext); err != nil {
+ return nil, err
+ }
+ out = append(out, s)
+ }
+ return out, nil
+}
diff --git a/internal/xls/xls_test.go b/internal/xls/xls_test.go
new file mode 100644
index 0000000..71ed21a
--- /dev/null
+++ b/internal/xls/xls_test.go
@@ -0,0 +1,99 @@
+package xls
+
+import (
+ "encoding/binary"
+ "math"
+ "reflect"
+ "testing"
+)
+
+func rkInt(n int32, div100 bool) uint32 {
+ rk := uint32(n)<<2 | 0x02
+ if div100 {
+ rk |= 0x01
+ }
+ return rk
+}
+
+func TestDecodeRK(t *testing.T) {
+ tests := []struct {
+ rk uint32
+ want float64
+ }{
+ {rkInt(1460, false), 1460},
+ {rkInt(-677083, true), -6770.83},
+ {rkInt(-1, false), -1},
+ {rkInt(4670, true), 46.7},
+ // Float form: the top 30 bits of an IEEE double.
+ {uint32(math.Float64bits(-11800) >> 32), -11800},
+ }
+ for _, tt := range tests {
+ if got := decodeRK(tt.rk); math.Abs(got-tt.want) > 1e-9 {
+ t.Errorf("decodeRK(%#x) = %v, want %v", tt.rk, got, tt.want)
+ }
+ }
+}
+
+type builder struct{ b []byte }
+
+func (w *builder) rec(typ uint16, data []byte) {
+ w.b = binary.LittleEndian.AppendUint16(w.b, typ)
+ w.b = binary.LittleEndian.AppendUint16(w.b, uint16(len(data)))
+ w.b = append(w.b, data...)
+}
+
+func cellHead(row, col uint16) []byte {
+ b := binary.LittleEndian.AppendUint16(nil, row)
+ b = binary.LittleEndian.AppendUint16(b, col)
+ return binary.LittleEndian.AppendUint16(b, 0) // xf
+}
+
+func TestParseWorkbook(t *testing.T) {
+ // SST with two strings; the second ("Bærum", stored as UTF-16) is split
+ // across a CONTINUE record, which restarts with its own option byte.
+ sst := binary.LittleEndian.AppendUint32(nil, 2)
+ sst = binary.LittleEndian.AppendUint32(sst, 2)
+ sst = append(sst, 5, 0, 0)
+ sst = append(sst, "Konto"...)
+ sst = append(sst, 5, 0, 1, 'B', 0, 0xE6, 0)
+ cont := []byte{0, 'r', 'u', 'm'}
+
+ var sheet builder
+ sheet.rec(recBOF, make([]byte, 16))
+ sheet.rec(recLabelSST, binary.LittleEndian.AppendUint32(cellHead(0, 0), 0))
+ sheet.rec(recLabelSST, binary.LittleEndian.AppendUint32(cellHead(0, 1), 1))
+ sheet.rec(recRK, binary.LittleEndian.AppendUint32(cellHead(1, 0), rkInt(1460, false)))
+ mulrk := binary.LittleEndian.AppendUint16(nil, 1)
+ mulrk = binary.LittleEndian.AppendUint16(mulrk, 1)
+ for _, v := range []uint32{rkInt(-677083, true), rkInt(5, false)} {
+ mulrk = binary.LittleEndian.AppendUint16(mulrk, 0)
+ mulrk = binary.LittleEndian.AppendUint32(mulrk, v)
+ }
+ mulrk = binary.LittleEndian.AppendUint16(mulrk, 2)
+ sheet.rec(recMulRK, mulrk)
+ sheet.rec(recNumber, binary.LittleEndian.AppendUint64(cellHead(2, 0), math.Float64bits(44587.125)))
+ sheet.rec(recEOF, nil)
+
+ // The globals substream, with the BOUNDSHEET offset patched once its
+ // own length is known.
+ var globals builder
+ globals.rec(recBOF, make([]byte, 16))
+ globals.rec(recBoundSheet, append(make([]byte, 6), 1, 0, 'S'))
+ globals.rec(recSST, sst)
+ globals.rec(recContinue, cont)
+ globals.rec(recEOF, nil)
+ binary.LittleEndian.PutUint32(globals.b[20+4:], uint32(len(globals.b)))
+
+ grid, err := ParseWorkbook(append(globals.b, sheet.b...))
+ if err != nil {
+ t.Fatal(err)
+ }
+ want := [][]string{
+ {"Konto", "Bærum"},
+ {"1460", "-6770.83", "5"},
+ {"44587.125"},
+ }
+ if !reflect.DeepEqual(grid, want) {
+ t.Fatalf("grid = %q, want %q", grid, want)
+ }
+}
generated by cgit v1.3.1 (git 2.54.0) at 2026-11-06 14:32:28 +0000