1package fileio23import (4 "bufio"5 "context"6 "encoding/binary"7 "errors"8 "fmt"9 "io"10 "math"11 "os"12 "strings"1314 "src.rest/cat/012/internal/sheet"15)1617// Lotus 1-2-3 worksheets (.wks from Release 1A, .wk1 from Release 2) are18// a stream of little-endian records: a 16-bit opcode, a 16-bit length19// and the payload. This reader follows the LibreOffice and Gnumeric20// import filters.2122const (23 wkBOF = 0x0024 wkEOF = 0x0125 wkRange = 0x06 // the active area26 wkColW = 0x08 // column width27 wkName = 0x0B // a named range28 wkBlank = 0x0C29 wkInteger = 0x0D30 wkNumber = 0x0E31 wkLabel = 0x0F32 wkFormula = 0x1033 wkString = 0x33 // the text result of the formula before it34)3536// wk1Version tells .wks (2,048 rows) from .wk1 (8,192 rows), which37// encode relative row offsets in formulas with different widths.38type wk1Version int3940const (41 wksFile wk1Version = iota42 wk1File43)4445func importWK1(ctx context.Context, name string, opt Options) (*Result, error) {46 prog := opt.Progress47 f, err := os.Open(name)48 if err != nil {49 return nil, err50 }51 defer f.Close()52 defer context.AfterFunc(ctx, func() { f.Close() })() // unblocks a read from a pipe53 var size int6454 if st, err := f.Stat(); err == nil {55 size = st.Size()56 }57 cr := &countingReader{r: f}58 s, notes, rows, err := readWK1(ctx, bufio.NewReader(cr), func(cells int) {59 prog.setRows(cells)60 prog.setFrac(cr.n, size)61 })62 if err != nil {63 return nil, err64 }65 return &Result{Sheet: s, Rows: rows, Notes: notes}, nil66}6768// wk1Reader holds the state of one import.69type wk1Reader struct {70 b *builder71 version wk1Version72 // pending is a formula whose value was kept, waiting for a STRING73 // record with its text result.74 pending sheet.Addr75 hasString bool76 names int77 maxRow int78 buf []byte // the payload of the record being read79}8081// readWK1 reads a worksheet record by record. progress gets the number82// of cells read so far.83func readWK1(ctx context.Context, r io.Reader, progress func(cells int)) (*sheet.Sheet, []string, int, error) {84 w := &wk1Reader{b: newBuilder(ctx, -1), version: wk1File} // the format's own 8192 x 25685 cells := 086 for i := 0; ; i++ {87 op, data, err := w.next(r, i)88 if err != nil {89 return nil, nil, 0, err90 }91 if op == wkEOF {92 break93 }94 if w.record(op, data) {95 cells++96 }97 if i%512 == 0 {98 if err := ctx.Err(); err != nil {99 return nil, nil, 0, err100 }101 progress(cells)102 }103 }104 progress(cells)105 var notes []string106 if w.names > 0 {107 notes = append(notes, count(w.names, "named range", "named ranges")+" not imported")108 }109 s, notes := w.b.finish(notes)110 return s, notes, w.maxRow + 1, nil111}112113// next reads record i, returning its opcode and payload, which is only114// valid until the next call. The end of the file reads as an EOF record:115// a worksheet may lack one, and 1-2-3 keeps what was read.116func (w *wk1Reader) next(r io.Reader, i int) (uint16, []byte, error) {117 var head [4]byte118 if _, err := io.ReadFull(r, head[:]); err != nil {119 switch {120 case i == 0:121 return 0, nil, errors.New("not a Lotus 1-2-3 worksheet: the file is empty")122 case errors.Is(err, io.EOF), errors.Is(err, io.ErrUnexpectedEOF):123 return wkEOF, nil, nil124 }125 return 0, nil, err126 }127 op := binary.LittleEndian.Uint16(head[0:])128 n := int(binary.LittleEndian.Uint16(head[2:]))129 if i == 0 && (op != wkBOF || n != 2) {130 return 0, nil, errors.New("not a Lotus 1-2-3 worksheet: it doesn't start with a BOF record")131 }132 if cap(w.buf) < n {133 w.buf = make([]byte, n)134 }135 data := w.buf[:n]136 if _, err := io.ReadFull(r, data); err != nil {137 return 0, nil, fmt.Errorf("record %d (opcode 0x%02X) is cut off", i+1, op)138 }139 return op, data, nil140}141142// record handles one record, reporting whether it was a cell.143func (w *wk1Reader) record(op uint16, d []byte) bool {144 switch op {145 case wkBOF:146 if len(d) >= 2 && binary.LittleEndian.Uint16(d) == 0x0404 {147 w.version = wksFile148 }149 return false150 case wkColW:151 if len(d) >= 3 {152 col := int(binary.LittleEndian.Uint16(d))153 if col < sheet.MaxCols && d[2] > 0 {154 w.b.s.LoadColWidth(col, int(d[2])+1)155 }156 }157 return false158 case wkName:159 w.names++160 return false161 case wkBlank, wkInteger, wkNumber, wkLabel, wkFormula, wkString:162 default:163 return false164 }165 if len(d) < 5 {166 return false167 }168 format := d[0]169 a := sheet.Addr{Col: int(binary.LittleEndian.Uint16(d[1:])), Row: int(binary.LittleEndian.Uint16(d[3:]))}170 body := d[5:]171 f := lotusFormat(format)172 w.maxRow = max(w.maxRow, a.Row)173 switch op {174 case wkBlank:175 w.b.put(a, "", f, sheet.Style{})176 case wkInteger:177 if len(body) < 2 {178 return false179 }180 w.b.number(a, float64(int16(binary.LittleEndian.Uint16(body))), f, sheet.Style{})181 case wkNumber:182 if len(body) < 8 {183 return false184 }185 v := math.Float64frombits(binary.LittleEndian.Uint64(body))186 w.b.number(a, fromExcelSerial(v, f), f, sheet.Style{})187 case wkLabel:188 w.label(a, body)189 case wkFormula:190 return w.formula(a, f, body)191 case wkString:192 if w.hasString && w.pending == a {193 w.b.text(a, lics(cstring(body)), sheet.Format{}, sheet.Style{})194 }195 w.hasString = false196 return false197 }198 return true199}200201// formula stores a formula: its cached value, the bytecode's size and202// the bytecode. A formula that can't be translated keeps its value, or203// the text in the STRING record that may follow.204func (w *wk1Reader) formula(a sheet.Addr, f sheet.Format, body []byte) bool {205 if len(body) < 10 {206 return false207 }208 v := math.Float64frombits(binary.LittleEndian.Uint64(body))209 size := int(binary.LittleEndian.Uint16(body[8:]))210 code := body[10:]211 if size < len(code) {212 code = code[:size]213 }214 w.hasString = false215 text, ok := lotusFormula(code, a, w.version)216 keep := func() {217 w.b.number(a, fromExcelSerial(v, f), f, sheet.Style{})218 w.pending, w.hasString = a, true219 }220 if !ok {221 w.b.kept(a, text, keep)222 return true223 }224 w.b.formula(a, "="+text, f, sheet.Style{}, keep)225 return true226}227228// label stores a label, whose first character sets its alignment: '229// left, " right, ^ centered, \ repeated to fill the cell.230func (w *wk1Reader) label(a sheet.Addr, body []byte) {231 s := lics(cstring(body))232 if s == "" {233 return234 }235 var st sheet.Style236 switch s[0] {237 case '\'':238 s = s[1:]239 case '"':240 s, st.Align = s[1:], sheet.AlignRight241 case '^':242 s, st.Align = s[1:], sheet.AlignCenter243 case '\\':244 s = s[1:]245 if s != "" {246 width := w.b.s.ColWidth(a.Col) - 1247 s = strings.Repeat(s, max(width/len([]rune(s)), 1))248 }249 case '|':250 s = s[1:]251 }252 w.b.text(a, s, sheet.Format{}, st)253}254255// cstring is the NUL-terminated string at the start of b.256func cstring(b []byte) string {257 if i := indexByte(b, 0); i >= 0 {258 b = b[:i]259 }260 return string(b)261}262263func indexByte(b []byte, c byte) int {264 for i, x := range b {265 if x == c {266 return i267 }268 }269 return -1270}271272// lics decodes the Lotus International Character Set as Latin-1, which273// it matches for the accented letters people used.274func lics(s string) string {275 ascii := true276 for i := 0; i < len(s); i++ {277 if s[i] >= 0x80 {278 ascii = false279 break280 }281 }282 if ascii {283 return s284 }285 r := make([]rune, len(s))286 for i := 0; i < len(s); i++ {287 r[i] = rune(s[i])288 }289 return string(r)290}291292// lotusFormat maps a cell's format byte: bit 7 is protection, bits 4-6293// the type and bits 0-3 decimals, or for type 7 a special format.294func lotusFormat(b byte) sheet.Format {295 typ, n := (b>>4)&7, int(b&0x0F)296 dec := min(n, sheet.MaxDecimals)297 switch typ {298 case 0: // fixed299 pat := "0"300 if dec > 0 {301 pat += "." + strings.Repeat("0", dec)302 }303 return sheet.Format{Kind: sheet.FmtCustom, Pattern: pat}304 case 1:305 return sheet.Format{Kind: sheet.FmtScientific, Decimals: dec}306 case 2:307 return sheet.Format{Kind: sheet.FmtCurrency, Decimals: dec}308 case 3:309 return sheet.Format{Kind: sheet.FmtPercent, Decimals: dec}310 case 4:311 return sheet.Format{Kind: sheet.FmtNumber, Decimals: dec}312 case 7:313 switch n {314 case 2:315 return sheet.Format{Kind: sheet.FmtDate, Pattern: "d-mmm-yy"}316 case 3:317 return sheet.Format{Kind: sheet.FmtDate, Pattern: "d-mmm"}318 case 4:319 return sheet.Format{Kind: sheet.FmtDate, Pattern: "mmm-yy"}320 case 7:321 return sheet.Format{Kind: sheet.FmtTime, Pattern: "h:mm:ss AM/PM"}322 case 8:323 return sheet.Format{Kind: sheet.FmtTime, Pattern: "h:mm AM/PM"}324 case 9:325 return sheet.Format{Kind: sheet.FmtDate, Pattern: "mm/dd/yy"}326 case 10:327 return sheet.Format{Kind: sheet.FmtDate, Pattern: "mm/dd"}328 case 11:329 return sheet.Format{Kind: sheet.FmtTime, Pattern: "hh:mm:ss"}330 case 12:331 return sheet.Format{Kind: sheet.FmtTime, Pattern: "hh:mm"}332 }333 }334 return sheet.Format{} // general, +/-, text (shows formulas), hidden, default335}