spread

https://git.tonybtw.com/spread.git git://git.tonybtw.com/spread.git

6,615 bytes raw

package spread

import (
	"archive/zip"
	"bytes"
	"encoding/csv"
	"encoding/xml"
	"fmt"
	"io"
	"path/filepath"
	"sort"
	"strconv"
	"strings"
)

// ParseBOM turns an uploaded file into line items, dispatching on extension
// and falling back to CSV. Supported: .csv, .tsv, .txt, .xlsx.
func ParseBOM(filename string, data []byte) ([]LineItem, error) {
	switch strings.ToLower(filepath.Ext(filename)) {
	case ".xlsx":
		rows, err := readXLSX(data)
		if err != nil {
			return nil, err
		}
		return rowsToItems(rows)
	default:
		rows, err := readCSV(data)
		if err != nil {
			return nil, err
		}
		return rowsToItems(rows)
	}
}

func readCSV(data []byte) ([][]string, error) {
	data = bytes.TrimPrefix(data, []byte("\xef\xbb\xbf")) // strip UTF-8 BOM
	r := csv.NewReader(bytes.NewReader(data))
	r.FieldsPerRecord = -1
	r.TrimLeadingSpace = true
	if bytes.Count(data, []byte{'\t'}) > bytes.Count(data, []byte{','}) {
		r.Comma = '\t'
	}
	return r.ReadAll()
}

// --- column mapping -------------------------------------------------------

// fieldAliases maps a normalized header token to the canonical field it
// feeds. Headers are matched case-insensitively with punctuation stripped.
var fieldAliases = map[string]string{
	"partnumber": "pn", "part": "pn", "partno": "pn", "pn": "pn",
	"mpn": "pn", "manufacturerpartnumber": "pn", "mfgpn": "pn", "mfrpartnumber": "pn",
	"description": "desc", "desc": "desc", "name": "desc", "partdescription": "desc", "component": "desc",
	"manufacturer": "mfr", "mfr": "mfr", "mfg": "mfr", "brand": "mfr", "maker": "mfr",
	"quantity": "qty", "qty": "qty", "quantityperbom": "qty", "extendedqty": "qty", "count": "qty",
	"unitprice": "price", "price": "price", "cost": "price", "unitcost": "price",
	"currentprice": "price", "currentunitprice": "price", "market": "price",
	"marketprice": "price", "eachprice": "price", "priceeach": "price",
}

func normHeader(s string) string {
	var b strings.Builder
	for _, r := range strings.ToLower(s) {
		if (r >= 'a' && r <= 'z') || (r >= '0' && r <= '9') {
			b.WriteRune(r)
		}
	}
	return b.String()
}

func parseNumber(s string) float64 {
	s = strings.TrimSpace(s)
	s = strings.NewReplacer("$", "", ",", "", " ", "", "€", "", "£", "").Replace(s)
	if s == "" {
		return 0
	}
	f, _ := strconv.ParseFloat(s, 64)
	return f
}

// rowsToItems finds the header row, maps its columns, and reads the rest.
func rowsToItems(rows [][]string) ([]LineItem, error) {
	hdr := -1
	var colFor map[int]string
	for i, row := range rows {
		m := map[int]string{}
		for j, cell := range row {
			if f, ok := fieldAliases[normHeader(cell)]; ok {
				m[j] = f
			}
		}
		// A valid header needs at least a part/description column plus a price.
		hasName, hasPrice := false, false
		for _, f := range m {
			if f == "pn" || f == "desc" {
				hasName = true
			}
			if f == "price" {
				hasPrice = true
			}
		}
		if hasName && hasPrice {
			hdr, colFor = i, m
			break
		}
	}
	if hdr < 0 {
		return nil, fmt.Errorf("could not find a header row with a part/description column and a price column")
	}

	var items []LineItem
	line := 0
	for _, row := range rows[hdr+1:] {
		var li LineItem
		for j, cell := range row {
			cell = strings.TrimSpace(cell)
			switch colFor[j] {
			case "pn":
				li.PartNumber = cell
			case "desc":
				li.Description = cell
			case "mfr":
				li.Manufacturer = cell
			case "qty":
				li.Quantity = parseNumber(cell)
			case "price":
				li.UnitPrice = parseNumber(cell)
			}
		}
		if li.PartNumber == "" && li.Description == "" {
			continue // blank/spacer row
		}
		if li.Quantity <= 0 {
			li.Quantity = 1
		}
		if li.Description == "" {
			li.Description = li.PartNumber
		}
		line++
		li.Line = line
		items = append(items, li)
	}
	if len(items) == 0 {
		return nil, fmt.Errorf("no line items found below the header row")
	}
	return items, nil
}

// --- minimal XLSX reader (stdlib only) ------------------------------------

type xlsxSST struct {
	SI []struct {
		T string   `xml:"t"`
		R []string `xml:"r>t"`
	} `xml:"si"`
}

type xlsxSheet struct {
	Rows []struct {
		Cells []struct {
			Ref     string   `xml:"r,attr"`
			Type    string   `xml:"t,attr"`
			V       string   `xml:"v"`
			InlineT string   `xml:"is>t"`
			InlineR []string `xml:"is>r>t"`
		} `xml:"c"`
	} `xml:"sheetData>row"`
}

// colIndex converts a cell reference's column letters ("B12" -> 1).
func colIndex(ref string) int {
	n := 0
	for i := 0; i < len(ref); i++ {
		c := ref[i]
		switch {
		case c >= 'A' && c <= 'Z':
			n = n*26 + int(c-'A'+1)
		case c >= 'a' && c <= 'z':
			n = n*26 + int(c-'a'+1)
		default:
			return n - 1
		}
	}
	return n - 1
}

func readXLSX(data []byte) ([][]string, error) {
	zr, err := zip.NewReader(bytes.NewReader(data), int64(len(data)))
	if err != nil {
		return nil, fmt.Errorf("not a valid .xlsx file: %w", err)
	}

	var shared []string
	var sheetName string
	for _, f := range zr.File {
		if f.Name == "xl/sharedStrings.xml" {
			var sst xlsxSST
			if err := unmarshalZip(f, &sst); err != nil {
				return nil, err
			}
			for _, si := range sst.SI {
				if si.T != "" {
					shared = append(shared, si.T)
				} else {
					shared = append(shared, strings.Join(si.R, ""))
				}
			}
		}
	}
	// Pick the first worksheet (lexicographically) for the demo.
	var sheetFiles []string
	for _, f := range zr.File {
		if strings.HasPrefix(f.Name, "xl/worksheets/") && strings.HasSuffix(f.Name, ".xml") {
			sheetFiles = append(sheetFiles, f.Name)
		}
	}
	if len(sheetFiles) == 0 {
		return nil, fmt.Errorf("no worksheet found in .xlsx")
	}
	sort.Strings(sheetFiles)
	sheetName = sheetFiles[0]

	var sheet xlsxSheet
	for _, f := range zr.File {
		if f.Name == sheetName {
			if err := unmarshalZip(f, &sheet); err != nil {
				return nil, err
			}
		}
	}

	var out [][]string
	for _, row := range sheet.Rows {
		var cells []string
		for _, c := range row.Cells {
			idx := colIndex(c.Ref)
			if idx < 0 {
				idx = len(cells)
			}
			for len(cells) <= idx {
				cells = append(cells, "")
			}
			var val string
			switch c.Type {
			case "s": // shared string
				if i, err := strconv.Atoi(strings.TrimSpace(c.V)); err == nil && i >= 0 && i < len(shared) {
					val = shared[i]
				}
			case "inlineStr":
				if c.InlineT != "" {
					val = c.InlineT
				} else {
					val = strings.Join(c.InlineR, "")
				}
			default:
				val = c.V
			}
			cells[idx] = val
		}
		out = append(out, cells)
	}
	return out, nil
}

func unmarshalZip(f *zip.File, v any) error {
	rc, err := f.Open()
	if err != nil {
		return err
	}
	defer rc.Close()
	b, err := io.ReadAll(rc)
	if err != nil {
		return err
	}
	return xml.Unmarshal(b, v)
}

1	`package spread`
2
3	`import (`
4	`"archive/zip"`
5	`"bytes"`
6	`"encoding/csv"`
7	`"encoding/xml"`
8	`"fmt"`
9	`"io"`
10	`"path/filepath"`
11	`"sort"`
12	`"strconv"`
13	`"strings"`
14	`)`
15
16	`// ParseBOM turns an uploaded file into line items, dispatching on extension`
17	`// and falling back to CSV. Supported: .csv, .tsv, .txt, .xlsx.`
18	`func ParseBOM(filename string, data []byte) ([]LineItem, error) {`
19	`switch strings.ToLower(filepath.Ext(filename)) {`
20	`case ".xlsx":`
21	`rows, err := readXLSX(data)`
22	`if err != nil {`
23	`return nil, err`
24	`}`
25	`return rowsToItems(rows)`
26	`default:`
27	`rows, err := readCSV(data)`
28	`if err != nil {`
29	`return nil, err`
30	`}`
31	`return rowsToItems(rows)`
32	`}`
33	`}`
34
35	`func readCSV(data []byte) ([][]string, error) {`
36	`data = bytes.TrimPrefix(data, []byte("\xef\xbb\xbf")) // strip UTF-8 BOM`
37	`r := csv.NewReader(bytes.NewReader(data))`
38	`r.FieldsPerRecord = -1`
39	`r.TrimLeadingSpace = true`
40	`if bytes.Count(data, []byte{'\t'}) > bytes.Count(data, []byte{','}) {`
41	`r.Comma = '\t'`
42	`}`
43	`return r.ReadAll()`
44	`}`
45
46	`// --- column mapping -------------------------------------------------------`
47
48	`// fieldAliases maps a normalized header token to the canonical field it`
49	`// feeds. Headers are matched case-insensitively with punctuation stripped.`
50	`var fieldAliases = map[string]string{`
51	`"partnumber": "pn", "part": "pn", "partno": "pn", "pn": "pn",`
52	`"mpn": "pn", "manufacturerpartnumber": "pn", "mfgpn": "pn", "mfrpartnumber": "pn",`
53	`"description": "desc", "desc": "desc", "name": "desc", "partdescription": "desc", "component": "desc",`
54	`"manufacturer": "mfr", "mfr": "mfr", "mfg": "mfr", "brand": "mfr", "maker": "mfr",`
55	`"quantity": "qty", "qty": "qty", "quantityperbom": "qty", "extendedqty": "qty", "count": "qty",`
56	`"unitprice": "price", "price": "price", "cost": "price", "unitcost": "price",`
57	`"currentprice": "price", "currentunitprice": "price", "market": "price",`
58	`"marketprice": "price", "eachprice": "price", "priceeach": "price",`
59	`}`
60
61	`func normHeader(s string) string {`
62	`var b strings.Builder`
63	`for _, r := range strings.ToLower(s) {`
64	`if (r >= 'a' && r <= 'z') \|\| (r >= '0' && r <= '9') {`
65	`b.WriteRune(r)`
66	`}`
67	`}`
68	`return b.String()`
69	`}`
70
71	`func parseNumber(s string) float64 {`
72	`s = strings.TrimSpace(s)`
73	`s = strings.NewReplacer("$", "", ",", "", " ", "", "€", "", "£", "").Replace(s)`
74	`if s == "" {`
75	`return 0`
76	`}`
77	`f, _ := strconv.ParseFloat(s, 64)`
78	`return f`
79	`}`
80
81	`// rowsToItems finds the header row, maps its columns, and reads the rest.`
82	`func rowsToItems(rows [][]string) ([]LineItem, error) {`
83	`hdr := -1`
84	`var colFor map[int]string`
85	`for i, row := range rows {`
86	`m := map[int]string{}`
87	`for j, cell := range row {`
88	`if f, ok := fieldAliases[normHeader(cell)]; ok {`
89	`m[j] = f`
90	`}`
91	`}`
92	`// A valid header needs at least a part/description column plus a price.`
93	`hasName, hasPrice := false, false`
94	`for _, f := range m {`
95	`if f == "pn" \|\| f == "desc" {`
96	`hasName = true`
97	`}`
98	`if f == "price" {`
99	`hasPrice = true`
100	`}`
101	`}`
102	`if hasName && hasPrice {`
103	`hdr, colFor = i, m`
104	`break`
105	`}`
106	`}`
107	`if hdr < 0 {`
108	`return nil, fmt.Errorf("could not find a header row with a part/description column and a price column")`
109	`}`
110
111	`var items []LineItem`
112	`line := 0`
113	`for _, row := range rows[hdr+1:] {`
114	`var li LineItem`
115	`for j, cell := range row {`
116	`cell = strings.TrimSpace(cell)`
117	`switch colFor[j] {`
118	`case "pn":`
119	`li.PartNumber = cell`
120	`case "desc":`
121	`li.Description = cell`
122	`case "mfr":`
123	`li.Manufacturer = cell`
124	`case "qty":`
125	`li.Quantity = parseNumber(cell)`
126	`case "price":`
127	`li.UnitPrice = parseNumber(cell)`
128	`}`
129	`}`
130	`if li.PartNumber == "" && li.Description == "" {`
131	`continue // blank/spacer row`
132	`}`
133	`if li.Quantity <= 0 {`
134	`li.Quantity = 1`
135	`}`
136	`if li.Description == "" {`
137	`li.Description = li.PartNumber`
138	`}`
139	`line++`
140	`li.Line = line`
141	`items = append(items, li)`
142	`}`
143	`if len(items) == 0 {`
144	`return nil, fmt.Errorf("no line items found below the header row")`
145	`}`
146	`return items, nil`
147	`}`
148
149	`// --- minimal XLSX reader (stdlib only) ------------------------------------`
150
151	`type xlsxSST struct {`
152	`SI []struct {`
153	T string `xml:"t"`
154	R []string `xml:"r>t"`
155	} `xml:"si"`
156	`}`
157
158	`type xlsxSheet struct {`
159	`Rows []struct {`
160	`Cells []struct {`
161	Ref string `xml:"r,attr"`
162	Type string `xml:"t,attr"`
163	V string `xml:"v"`
164	InlineT string `xml:"is>t"`
165	InlineR []string `xml:"is>r>t"`
166	} `xml:"c"`
167	} `xml:"sheetData>row"`
168	`}`
169
170	`// colIndex converts a cell reference's column letters ("B12" -> 1).`
171	`func colIndex(ref string) int {`
172	`n := 0`
173	`for i := 0; i < len(ref); i++ {`
174	`c := ref[i]`
175	`switch {`
176	`case c >= 'A' && c <= 'Z':`
177	`n = n*26 + int(c-'A'+1)`
178	`case c >= 'a' && c <= 'z':`
179	`n = n*26 + int(c-'a'+1)`
180	`default:`
181	`return n - 1`
182	`}`
183	`}`
184	`return n - 1`
185	`}`
186
187	`func readXLSX(data []byte) ([][]string, error) {`
188	`zr, err := zip.NewReader(bytes.NewReader(data), int64(len(data)))`
189	`if err != nil {`
190	`return nil, fmt.Errorf("not a valid .xlsx file: %w", err)`
191	`}`
192
193	`var shared []string`
194	`var sheetName string`
195	`for _, f := range zr.File {`
196	`if f.Name == "xl/sharedStrings.xml" {`
197	`var sst xlsxSST`
198	`if err := unmarshalZip(f, &sst); err != nil {`
199	`return nil, err`
200	`}`
201	`for _, si := range sst.SI {`
202	`if si.T != "" {`
203	`shared = append(shared, si.T)`
204	`} else {`
205	`shared = append(shared, strings.Join(si.R, ""))`
206	`}`
207	`}`
208	`}`
209	`}`
210	`// Pick the first worksheet (lexicographically) for the demo.`
211	`var sheetFiles []string`
212	`for _, f := range zr.File {`
213	`if strings.HasPrefix(f.Name, "xl/worksheets/") && strings.HasSuffix(f.Name, ".xml") {`
214	`sheetFiles = append(sheetFiles, f.Name)`
215	`}`
216	`}`
217	`if len(sheetFiles) == 0 {`
218	`return nil, fmt.Errorf("no worksheet found in .xlsx")`
219	`}`
220	`sort.Strings(sheetFiles)`
221	`sheetName = sheetFiles[0]`
222
223	`var sheet xlsxSheet`
224	`for _, f := range zr.File {`
225	`if f.Name == sheetName {`
226	`if err := unmarshalZip(f, &sheet); err != nil {`
227	`return nil, err`
228	`}`
229	`}`
230	`}`
231
232	`var out [][]string`
233	`for _, row := range sheet.Rows {`
234	`var cells []string`
235	`for _, c := range row.Cells {`
236	`idx := colIndex(c.Ref)`
237	`if idx < 0 {`
238	`idx = len(cells)`
239	`}`
240	`for len(cells) <= idx {`
241	`cells = append(cells, "")`
242	`}`
243	`var val string`
244	`switch c.Type {`
245	`case "s": // shared string`
246	`if i, err := strconv.Atoi(strings.TrimSpace(c.V)); err == nil && i >= 0 && i < len(shared) {`
247	`val = shared[i]`
248	`}`
249	`case "inlineStr":`
250	`if c.InlineT != "" {`
251	`val = c.InlineT`
252	`} else {`
253	`val = strings.Join(c.InlineR, "")`
254	`}`
255	`default:`
256	`val = c.V`
257	`}`
258	`cells[idx] = val`
259	`}`
260	`out = append(out, cells)`
261	`}`
262	`return out, nil`
263	`}`
264
265	`func unmarshalZip(f *zip.File, v any) error {`
266	`rc, err := f.Open()`
267	`if err != nil {`
268	`return err`
269	`}`
270	`defer rc.Close()`
271	`b, err := io.ReadAll(rc)`
272	`if err != nil {`
273	`return err`
274	`}`
275	`return xml.Unmarshal(b, v)`
276	`}`