1package fileio23import (4 "strconv"5 "strings"6)78// shiftFormula moves the relative references of an Excel formula by9// dCol columns and dRow rows, as Excel does to copy a shared formula10// from its first cell to the others: A1 and A$1 move, $A$1 doesn't, and11// whole columns (A:B) and rows (1:2) move along their axis. Text,12// quoted sheet names and function names stay as they are. A reference13// moved off the sheet becomes #REF!.14func shiftFormula(f string, dCol, dRow int) string {15 if dCol == 0 && dRow == 0 {16 return f17 }18 var b strings.Builder19 for i := 0; i < len(f); {20 c := f[i]21 switch {22 case c == '"':23 j := stringEnd(f, i)24 if j < 0 {25 j = len(f)26 }27 b.WriteString(f[i:j])28 i = j29 case c == '\'':30 j := quoteEnd(f, i)31 b.WriteString(f[i:j])32 i = j33 case c == '[':34 // A structured reference or an external book's index.35 j := strings.IndexByte(f[i:], ']')36 if j < 0 {37 j = len(f) - i - 138 }39 b.WriteString(f[i : i+j+1])40 i += j + 141 case isRefByte(c):42 j := i43 for j < len(f) && isRefByte(f[j]) {44 j++45 }46 if word := f[i:j]; j < len(f) && f[j] == '(' {47 b.WriteString(word) // a function48 } else {49 b.WriteString(shiftRef(word, dCol, dRow))50 }51 i = j52 default:53 b.WriteByte(c)54 i++55 }56 }57 return b.String()58}5960func isRefByte(c byte) bool { return isIdentByte(c) || c == ':' || c == '!' }6162// refPart is one side of a reference: a cell (A1), a column (A) or a63// row (1), each part with or without $.64type refPart struct {65 prefix string // a sheet name and !, if any66 col, row int // from 1; 0 when absent67 absCol, absRow bool68}6970// shiftRef moves a reference such as A1, $A1:B$2, Sheet1!A1 or A:B, and71// returns anything else (a name, a number) unchanged.72func shiftRef(word string, dCol, dRow int) string {73 sides := strings.Split(word, ":")74 if len(sides) > 2 {75 return word76 }77 var parts [2]refPart78 for i, s := range sides {79 p, ok := parseRefPart(s)80 if !ok {81 return word82 }83 parts[i] = p84 }85 first := parts[0]86 for _, p := range parts[1:len(sides)] {87 if (p.col > 0) != (first.col > 0) || (p.row > 0) != (first.row > 0) {88 return word // mixed kinds: not a reference89 }90 }91 if len(sides) == 1 && (first.col == 0 || first.row == 0) {92 return word // a lone column or row is a name or a number93 }94 out := make([]string, len(sides))95 for i, p := range parts[:len(sides)] {96 if p.col > 0 && !p.absCol {97 p.col += dCol98 }99 if p.row > 0 && !p.absRow {100 p.row += dRow101 }102 if first.col > 0 && (p.col < 1 || p.col > excelCols) || first.row > 0 && (p.row < 1 || p.row > excelRows) {103 return first.prefix + "#REF!"104 }105 out[i] = p.String()106 }107 return strings.Join(out, ":")108}109110// parseRefPart parses [Sheet!][$]COL[$]ROW, [$]COL or [$]ROW.111func parseRefPart(s string) (refPart, bool) {112 var p refPart113 if k := strings.LastIndexByte(s, '!'); k >= 0 {114 p.prefix, s = s[:k+1], s[k+1:]115 }116 i := 0117 dollar := i < len(s) && s[i] == '$'118 if dollar {119 i++120 }121 letters := i122 for i < len(s) && isLetter(s[i]) && i-letters < 3 {123 p.col = p.col*26 + int(upper(s[i])-'A'+1)124 i++125 }126 if i == letters {127 p.absRow = dollar // no column: a row, and the $ is the row's128 } else {129 p.absCol = dollar130 if i < len(s) && s[i] == '$' {131 p.absRow = true132 i++133 }134 }135 digits := i136 for i < len(s) && isDigitByte(s[i]) && i-digits < 7 {137 p.row = p.row*10 + int(s[i]-'0')138 i++139 }140 switch {141 case i != len(s), i == letters, p.col > excelCols, p.row > excelRows:142 return p, false143 case i > digits && p.row == 0, i == digits && p.absRow:144 return p, false // row 0, or a $ with no row after it145 }146 return p, true147}148149func (p refPart) String() string {150 var b strings.Builder151 b.WriteString(p.prefix)152 if p.col > 0 {153 if p.absCol {154 b.WriteByte('$')155 }156 b.WriteString(excelColName(p.col))157 }158 if p.row > 0 {159 if p.absRow {160 b.WriteByte('$')161 }162 b.WriteString(strconv.Itoa(p.row))163 }164 return b.String()165}166167// excelColName is the letters of column col, from 1 (A) to 16384 (XFD).168func excelColName(col int) string {169 var b [3]byte170 i := len(b)171 for col > 0 && i > 0 {172 col--173 i--174 b[i] = byte('A' + col%26)175 col /= 26176 }177 return string(b[i:])178}