diff options
Diffstat (limited to 'internal/xls/xls.go')
| -rw-r--r-- | internal/xls/xls.go | 364 |
1 files changed, 364 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 +} |