1package formula23import "strings"45// Sheet names in references: Sheet2!A1, 'Q3 plan'!B2:C9.67// SheetKey is how sheet names compare: ignoring case, as in Sheets.8func SheetKey(name string) string { return strings.ToUpper(name) }910// QuoteSheet writes a sheet name as a formula needs it: bare when it reads11// as a plain identifier (Sheet2), otherwise in single quotes with quotes12// doubled, e.g. 'Q3 plan'.13func QuoteSheet(name string) string {14 bare := name != "" && (isLetter(name[0]) || name[0] == '_') && !LooksLikeCell(strings.ToUpper(name))15 for i := 0; bare && i < len(name); i++ {16 c := name[i]17 bare = isLetter(c) || isDigit(c) || c == '_' || c == '.'18 }19 if bare {20 return name21 }22 return "'" + strings.ReplaceAll(name, "'", "''") + "'"23}2425// LooksLikeCell reports whether an upper-case name reads as a cell in26// Excel, A1 to XFD1048576 or R1C1, so a sheet of that name is quoted27// everywhere its formulas may go. Sheet1 doesn't: SHEET is no column.28func LooksLikeCell(k string) bool {29 digits := strings.TrimLeft(k, "ABCDEFGHIJKLMNOPQRSTUVWXYZ")30 if n := len(k) - len(digits); n >= 1 && n <= 3 && digits != "" && strings.Trim(digits, "0123456789") == "" {31 return true32 }33 return (strings.HasPrefix(k, "R") || strings.HasPrefix(k, "C")) && LooksLikeRef(k)34}3536// quotedName reads a quoted sheet name starting at the quote at src[i] and37// returns it unquoted with the index just past the closing quote.38func quotedName(src string, i int) (string, int, bool) {39 var b strings.Builder40 for j := i + 1; j < len(src); j++ {41 if src[j] != '\'' {42 b.WriteByte(src[j])43 continue44 }45 if j+1 < len(src) && src[j+1] == '\'' {46 b.WriteByte('\'')47 j++48 continue49 }50 return b.String(), j + 1, b.Len() > 051 }52 return "", len(src), false53}5455// SplitSheet splits a reference such as "Sheet2!A1:B3" or "'Q3 plan'!B2"56// into the sheet name, unquoted, and the rest. Without a sheet, sheet is57// "" and rest is s.58func SplitSheet(s string) (sheet, rest string) {59 s = strings.TrimSpace(s)60 if strings.HasPrefix(s, "'") {61 if name, end, ok := quotedName(s, 0); ok && end < len(s) && s[end] == '!' {62 return name, s[end+1:]63 }64 return "", s65 }66 if i := strings.LastIndexByte(s, '!'); i > 0 {67 return s[:i], s[i+1:]68 }69 return "", s70}7172// Qualified writes r on sheet as a formula would: Sheet2!A1:B3.73func Qualified(sheet string, r Rect) string {74 return QuoteSheet(sheet) + "!" + r.String()75}