path: root/internal/xls/xls.go

// 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
}