1package sheet23import (4 "errors"5 "fmt"6 "slices"7 "strconv"8 "strings"910 "src.rest/cat/012/internal/formula"11)1213// Tables, as Sheets' Format > Convert to table and Excel's tables: a14// named range whose first row names its columns, so formulas read it by15// column name (Sales[Amount], see tablebind.go). A table grows as rows16// are typed or pasted just below it, and its columns' names follow its17// header row: renaming a header renames the column in every formula18// that reads it (tablesync.go). Tables belong to the sheet's view state,19// so each change to one is an undo step with the rest of the view.20//21// A region's table (a notebook cell's output sent to a sheet, or a22// linked file) is a table too, by the region's name: app[status] reads23// its status column. Its rows and columns are its source's, so the24// commands that rename, resize, style or remove a table leave it alone.2526// Table is a table on a sheet.27type Table struct {28 Name string // as the user spelled it; formulas match it in any case29 Range Rect // header row included30 // Cols are the columns' names, left to right: the header row's text31 // as last settled, never blank and never the same twice.32 Cols []string33 // Banded shades every other data row; Header styles the header row.34 Banded, Header bool35}3637// equal reports whether two tables are the same.38func (t Table) equal(u Table) bool {39 return t.Name == u.Name && t.Range == u.Range && slices.Equal(t.Cols, u.Cols) && t.Banded == u.Banded && t.Header == u.Header40}4142// clone is a copy of t sharing nothing with it.43func (t Table) clone() Table {44 t.Cols = slices.Clone(t.Cols)45 return t46}4748// Data is the table's data rows, below the header row.49func (t Table) Data() Rect {50 d := t.Range51 d.From.Row++52 return d53}5455// Col returns the index of the column named name, ignoring case, or -1.56func (t Table) Col(name string) int {57 return slices.IndexFunc(t.Cols, func(c string) bool { return strings.EqualFold(c, name) })58}5960// Errors of tables.61var (62 ErrNoTable = errors.New("There's no table by that name")63 ErrTableOverlap = errors.New("A table can't overlap another table, a region or a pivot table")64)6566// tablePrefix is reserved for regions' names in formulas (nu.sales).67const tablePrefix = regionPrefix6869// ValidTableName checks that name can name a table: a named range's70// rules, except that only a name Excel reads as a cell is refused, so71// Table1 is one; and not starting with nu., which regions' names use.72func ValidTableName(name string) error {73 if err := checkName(name, formula.LooksLikeCell); err != nil {74 return err75 }76 if strings.HasPrefix(nameKey(name), nameKey(tablePrefix)) {77 return errors.New("A table's name can't start with " + tablePrefix)78 }79 return nil80}8182// checkTableName reports why name can't name a table, besides the one83// with key self (when renaming one).84func (w *Workbook) checkTableName(name, self string) error {85 if err := ValidTableName(name); err != nil {86 return err87 }88 k := nameKey(name)89 if n, ok := w.LookupName(name); ok {90 return fmt.Errorf("%s already names %s", n.Name, n.Ref())91 }92 if _, t, ok := w.Table(name); ok && k != self {93 return fmt.Errorf("There's already a table named %s", t.Name)94 }95 if _, r, ok := w.Region(name); ok {96 return fmt.Errorf("%s names a notebook output or a linked file", r.Name)97 }98 return nil99}100101// Tables returns the sheet's tables, in the order they were made.102func (s *Sheet) Tables() []Table {103 out := make([]Table, len(s.view.tables))104 for i, t := range s.view.tables {105 out[i] = t.clone()106 }107 return out108}109110// HasTables reports whether the sheet has tables, so what draws it can111// skip looking for them.112func (s *Sheet) HasTables() bool { return len(s.view.tables) > 0 }113114// Table finds a table by name, ignoring case, on any sheet. Regions'115// tables aren't among them: see TableInfo.116func (w *Workbook) Table(name string) (*Sheet, Table, bool) {117 k := nameKey(name)118 for _, s := range w.sheets {119 if i := s.tableIndex(k); i >= 0 {120 return s, s.view.tables[i].clone(), true121 }122 }123 return nil, Table{}, false124}125126func (s *Sheet) tableIndex(k string) int {127 return slices.IndexFunc(s.view.tables, func(t Table) bool { return nameKey(t.Name) == k })128}129130// TableAt returns the table holding the cell at a.131func (s *Sheet) TableAt(a Addr) (Table, bool) {132 for _, t := range s.view.tables {133 if t.Range.Contains(a) {134 return t.clone(), true135 }136 }137 return Table{}, false138}139140// NextTableName is a name no table has yet: Table1, Table2 ...141func (w *Workbook) NextTableName() string {142 for i := 1; ; i++ {143 name := "Table" + strconv.Itoa(i)144 if w.checkTableName(name, "") == nil {145 return name146 }147 }148}149150// copiedTables are ts as a copied sheet holds them, each renamed to a151// name no table has: Sales_2, Sales_3 ...152func (w *Workbook) copiedTables(ts []Table) []Table {153 out := make([]Table, len(ts))154 taken := map[string]bool{}155 for i, t := range ts {156 t = t.clone()157 for n := 2; ; n++ {158 name := t.Name + "_" + strconv.Itoa(n)159 if !taken[nameKey(name)] && w.checkTableName(name, "") == nil {160 t.Name, taken[nameKey(name)] = name, true161 break162 }163 }164 out[i] = t165 }166 return out167}168169// checkTableRange reports why r can't hold the table with key self:170// it overlaps another table, a region or a pivot table.171func (s *Sheet) checkTableRange(r Rect, self string) error {172 for _, t := range s.view.tables {173 if nameKey(t.Name) != self && overlaps(t.Range, r) {174 return ErrTableOverlap175 }176 }177 if _, _, ok := s.InRegion(r); ok || s.InPivot(r) {178 return ErrTableOverlap179 }180 return nil181}182183// tableRange is r as a table's range: a header row and at least one184// row of data.185func tableRange(r Rect) Rect {186 if r.To.Row == r.From.Row && r.To.Row < MaxRows-1 {187 r.To.Row++188 }189 return r190}191192// CreateTable makes a table named name of r, whose first row names its193// columns, with its header styled and no bands, as one undo step. Blank or repeated names in the header row194// are replaced with ones of their own (Column2, Amount2), in the cells195// too; a range of one row gains an empty row of data.196func (s *Sheet) CreateTable(name string, r Rect) error {197 if err := s.wb.checkTableName(name, ""); err != nil {198 return err199 }200 r = tableRange(r)201 if r.To.Row == r.From.Row {202 return errors.New("A table needs a row below its header row")203 }204 if err := s.checkTableRange(r, ""); err != nil {205 return err206 }207 s.change("make table "+name, r, func() {208 t := Table{Name: name, Range: r, Header: true}209 t.Cols = s.headerNames(t)210 s.putTables(append(s.Tables(), t))211 })212 return nil213}214215// RenameTable renames a table, as one undo step, and every formula216// that reads it with it.217func (w *Workbook) RenameTable(old, name string) error {218 s, t, ok := w.Table(old)219 if !ok {220 return ErrNoTable221 }222 if err := w.checkTableName(name, nameKey(old)); err != nil {223 return err224 }225 if t.Name == name {226 return nil227 }228 s.change("rename table "+t.Name, t.Range, func() {229 w.renameTableInFormulas(nameKey(old), name)230 s.editTable(nameKey(old), func(t *Table) { t.Name = name })231 })232 return nil233}234235// ResizeTable points a table at r, as one undo step. Its columns take236// their names from r's first row.237func (w *Workbook) ResizeTable(name string, r Rect) error {238 s, t, ok := w.Table(name)239 if !ok {240 return ErrNoTable241 }242 r = tableRange(r)243 if err := s.checkTableRange(r, nameKey(name)); err != nil {244 return err245 }246 s.change("resize table "+t.Name, r, func() {247 s.editTable(nameKey(name), func(t *Table) {248 t.Range = r249 t.Cols = s.headerNames(*t)250 })251 })252 return nil253}254255// SetTableStyle sets whether a table's data rows are banded and its256// header row styled, as one undo step.257func (w *Workbook) SetTableStyle(name string, banded, header bool) error {258 s, t, ok := w.Table(name)259 if !ok {260 return ErrNoTable261 }262 s.change("style table "+t.Name, t.Range, func() {263 s.editTable(nameKey(name), func(t *Table) { t.Banded, t.Header = banded, header })264 })265 return nil266}267268// RemoveTable removes a table, keeping its cells, as one undo step.269// Formulas that read it read the same cells by address instead, as270// Excel's Convert to range leaves them.271func (w *Workbook) RemoveTable(name string) error {272 s, t, ok := w.Table(name)273 if !ok {274 return ErrNoTable275 }276 s.change("remove table "+t.Name, t.Range, func() {277 w.unbindTable(nameKey(name))278 st := s.Tables()279 st = slices.DeleteFunc(st, func(u Table) bool { return nameKey(u.Name) == nameKey(name) })280 s.putTables(st)281 })282 return nil283}284285// editTable changes the table with key k with fn, recording the change.286func (s *Sheet) editTable(k string, fn func(*Table)) {287 st := s.Tables()288 if i := slices.IndexFunc(st, func(t Table) bool { return nameKey(t.Name) == k }); i >= 0 {289 fn(&st[i])290 s.putTables(st)291 }292}293294// putTables replaces the sheet's tables, recording the view for undo,295// and has the formulas that read the tables changed recalculated. A296// filter on a table's range follows the table as it grows or shrinks.297// Every change to tables goes through here.298func (s *Sheet) putTables(st []Table) {299 if slices.EqualFunc(st, s.view.tables, Table.equal) {300 return301 }302 s.recordView()303 s.followFilter(s.view.tables, st)304 w := s.wb305 if w.hist.open != nil {306 w.hist.dirty = append(w.hist.dirty, w.tableUsers(s.view.tables, st)...)307 }308 s.view.tables = st309}310311// followFilter moves the filter along with the table whose range it312// has, when the table's range changes.313func (s *Sheet) followFilter(before, after []Table) {314 f := s.view.filter315 if f == nil {316 return317 }318 for _, t := range before {319 i := slices.IndexFunc(after, func(u Table) bool { return nameKey(u.Name) == nameKey(t.Name) })320 if t.Range != f.Range || i < 0 || after[i].Range == t.Range {321 continue322 }323 nf := f.clone()324 nf.Range = after[i].Range325 for c := range nf.Cols {326 if c > nf.Range.To.Col {327 delete(nf.Cols, c)328 }329 }330 s.view.filter = nf331 s.hidden.valid = false332 return333 }334}335336// tableUsers are the formulas reading any table in either list whose337// range, columns or name differ between them.338func (w *Workbook) tableUsers(before, after []Table) []loc {339 var out []loc340 add := func(k string) {341 for u := range w.nameUsers[k] {342 out = append(out, u)343 }344 }345 for _, t := range before {346 i := slices.IndexFunc(after, func(u Table) bool { return nameKey(u.Name) == nameKey(t.Name) })347 if i < 0 || !after[i].equal(t) {348 add(nameKey(t.Name))349 }350 }351 for _, t := range after {352 if !slices.ContainsFunc(before, func(u Table) bool { return nameKey(u.Name) == nameKey(t.Name) }) {353 add(nameKey(t.Name))354 }355 }356 return out357}