adjust.go

   1 // Copyright 2016 - 2019 The excelize Authors. All rights reserved. Use of
   2 // this source code is governed by a BSD-style license that can be found in
   3 // the LICENSE file.
   4 //
   5 // Package excelize providing a set of functions that allow you to write to
   6 // and read from XLSX files. Support reads and writes XLSX file generated by
   7 // Microsoft Excel™ 2007 and later. Support save file without losing original
   8 // charts of XLSX. This library needs Go version 1.8 or later.
   9
  10 package excelize
  11
  12 import "strings"
  13
  14 type adjustDirection bool
  15
  16 const (
  17         columns adjustDirection = false
  18         rows    adjustDirection = true
  19 )
  20
  21 // adjustHelper provides a function to adjust rows and columns dimensions,
  22 // hyperlinks, merged cells and auto filter when inserting or deleting rows or
  23 // columns.
  24 //
  25 // sheet: Worksheet name that we're editing
  26 // column: Index number of the column we're inserting/deleting before
  27 // row: Index number of the row we're inserting/deleting before
  28 // offset: Number of rows/column to insert/delete negative values indicate deletion
  29 //
  30 // TODO: adjustCalcChain, adjustPageBreaks, adjustComments,
  31 // adjustDataValidations, adjustProtectedCells
  32 //
  33 func (f *File) adjustHelper(sheet string, dir adjustDirection, num, offset int) error {
  34         xlsx := f.workSheetReader(sheet)
  35
  36         if dir == rows {
  37                 f.adjustRowDimensions(xlsx, num, offset)
  38         } else {
  39                 f.adjustColDimensions(xlsx, num, offset)
  40         }
  41         f.adjustHyperlinks(xlsx, sheet, dir, num, offset)
  42         if err := f.adjustMergeCells(xlsx, dir, num, offset); err != nil {
  43                 return err
  44         }
  45         if err := f.adjustAutoFilter(xlsx, dir, num, offset); err != nil {
  46                 return err
  47         }
  48
  49         checkSheet(xlsx)
  50         checkRow(xlsx)
  51         return nil
  52 }
  53
  54 // adjustColDimensions provides a function to update column dimensions when
  55 // inserting or deleting rows or columns.
  56 func (f *File) adjustColDimensions(xlsx *xlsxWorksheet, col, offset int) {
  57         for rowIdx := range xlsx.SheetData.Row {
  58                 for colIdx, v := range xlsx.SheetData.Row[rowIdx].C {
  59                         cellCol, cellRow, _ := CellNameToCoordinates(v.R)
  60                         if col <= cellCol {
  61                                 if newCol := cellCol + offset; newCol > 0 {
  62                                         xlsx.SheetData.Row[rowIdx].C[colIdx].R, _ = CoordinatesToCellName(newCol, cellRow)
  63                                 }
  64                         }
  65                 }
  66         }
  67 }
  68
  69 // adjustRowDimensions provides a function to update row dimensions when
  70 // inserting or deleting rows or columns.
  71 func (f *File) adjustRowDimensions(xlsx *xlsxWorksheet, row, offset int) {
  72         for i, r := range xlsx.SheetData.Row {
  73                 if newRow := r.R + offset; r.R >= row && newRow > 0 {
  74                         f.ajustSingleRowDimensions(&xlsx.SheetData.Row[i], newRow)
  75                 }
  76         }
  77 }
  78
  79 // ajustSingleRowDimensions provides a function to ajust single row dimensions.
  80 func (f *File) ajustSingleRowDimensions(r *xlsxRow, num int) {
  81         r.R = num
  82         for i, col := range r.C {
  83                 colName, _, _ := SplitCellName(col.R)
  84                 r.C[i].R, _ = JoinCellName(colName, num)
  85         }
  86 }
  87
  88 // adjustHyperlinks provides a function to update hyperlinks when inserting or
  89 // deleting rows or columns.
  90 func (f *File) adjustHyperlinks(xlsx *xlsxWorksheet, sheet string, dir adjustDirection, num, offset int) {
  91         // short path
  92         if xlsx.Hyperlinks == nil || len(xlsx.Hyperlinks.Hyperlink) == 0 {
  93                 return
  94         }
  95
  96         // order is important
  97         if offset < 0 {
  98                 for rowIdx, linkData := range xlsx.Hyperlinks.Hyperlink {
  99                         colNum, rowNum, _ := CellNameToCoordinates(linkData.Ref)
 100
 101                         if (dir == rows && num == rowNum) || (dir == columns && num == colNum) {
 102                                 f.deleteSheetRelationships(sheet, linkData.RID)
 103                                 if len(xlsx.Hyperlinks.Hyperlink) > 1 {
 104                                         xlsx.Hyperlinks.Hyperlink = append(xlsx.Hyperlinks.Hyperlink[:rowIdx],
 105                                                 xlsx.Hyperlinks.Hyperlink[rowIdx+1:]...)
 106                                 } else {
 107                                         xlsx.Hyperlinks = nil
 108                                 }
 109                         }
 110                 }
 111         }
 112
 113         if xlsx.Hyperlinks == nil {
 114                 return
 115         }
 116
 117         for i := range xlsx.Hyperlinks.Hyperlink {
 118                 link := &xlsx.Hyperlinks.Hyperlink[i] // get reference
 119                 colNum, rowNum, _ := CellNameToCoordinates(link.Ref)
 120
 121                 if dir == rows {
 122                         if rowNum >= num {
 123                                 link.Ref, _ = CoordinatesToCellName(colNum, rowNum+offset)
 124                         }
 125                 } else {
 126                         if colNum >= num {
 127                                 link.Ref, _ = CoordinatesToCellName(colNum+offset, rowNum)
 128                         }
 129                 }
 130         }
 131 }
 132
 133 // adjustAutoFilter provides a function to update the auto filter when
 134 // inserting or deleting rows or columns.
 135 func (f *File) adjustAutoFilter(xlsx *xlsxWorksheet, dir adjustDirection, num, offset int) error {
 136         if xlsx.AutoFilter == nil {
 137                 return nil
 138         }
 139
 140         rng := strings.Split(xlsx.AutoFilter.Ref, ":")
 141         firstCell := rng[0]
 142         lastCell := rng[1]
 143
 144         firstCol, firstRow, err := CellNameToCoordinates(firstCell)
 145         if err != nil {
 146                 return err
 147         }
 148
 149         lastCol, lastRow, err := CellNameToCoordinates(lastCell)
 150         if err != nil {
 151                 return err
 152         }
 153
 154         if (dir == rows && firstRow == num && offset < 0) || (dir == columns && firstCol == num && lastCol == num) {
 155                 xlsx.AutoFilter = nil
 156                 for rowIdx := range xlsx.SheetData.Row {
 157                         rowData := &xlsx.SheetData.Row[rowIdx]
 158                         if rowData.R > firstRow && rowData.R <= lastRow {
 159                                 rowData.Hidden = false
 160                         }
 161                 }
 162                 return nil
 163         }
 164
 165         if dir == rows {
 166                 if firstRow >= num {
 167                         firstCell, _ = CoordinatesToCellName(firstCol, firstRow+offset)
 168                 }
 169                 if lastRow >= num {
 170                         lastCell, _ = CoordinatesToCellName(lastCol, lastRow+offset)
 171                 }
 172         } else {
 173                 if lastCol >= num {
 174                         lastCell, _ = CoordinatesToCellName(lastCol+offset, lastRow)
 175                 }
 176         }
 177
 178         xlsx.AutoFilter.Ref = firstCell + ":" + lastCell
 179         return nil
 180 }
 181
 182 // adjustMergeCells provides a function to update merged cells when inserting
 183 // or deleting rows or columns.
 184 func (f *File) adjustMergeCells(xlsx *xlsxWorksheet, dir adjustDirection, num, offset int) error {
 185         if xlsx.MergeCells == nil {
 186                 return nil
 187         }
 188
 189         for i, areaData := range xlsx.MergeCells.Cells {
 190                 rng := strings.Split(areaData.Ref, ":")
 191                 firstCell := rng[0]
 192                 lastCell := rng[1]
 193
 194                 firstCol, firstRow, err := CellNameToCoordinates(firstCell)
 195                 if err != nil {
 196                         return err
 197                 }
 198
 199                 lastCol, lastRow, err := CellNameToCoordinates(lastCell)
 200                 if err != nil {
 201                         return err
 202                 }
 203
 204                 adjust := func(v int) int {
 205                         if v >= num {
 206                                 v += offset
 207                                 if v < 1 {
 208                                         return 1
 209                                 }
 210                                 return v
 211                         }
 212                         return v
 213                 }
 214
 215                 if dir == rows {
 216                         firstRow = adjust(firstRow)
 217                         lastRow = adjust(lastRow)
 218                 } else {
 219                         firstCol = adjust(firstCol)
 220                         lastCol = adjust(lastCol)
 221                 }
 222
 223                 if firstCol == lastCol && firstRow == lastRow {
 224                         if len(xlsx.MergeCells.Cells) > 1 {
 225                                 xlsx.MergeCells.Cells = append(xlsx.MergeCells.Cells[:i], xlsx.MergeCells.Cells[i+1:]...)
 226                                 xlsx.MergeCells.Count = len(xlsx.MergeCells.Cells)
 227                         } else {
 228                                 xlsx.MergeCells = nil
 229                         }
 230                 }
 231
 232                 if firstCell, err = CoordinatesToCellName(firstCol, firstRow); err != nil {
 233                         return err
 234                 }
 235
 236                 if lastCell, err = CoordinatesToCellName(lastCol, lastRow); err != nil {
 237                         return err
 238                 }
 239
 240                 areaData.Ref = firstCell + ":" + lastCell
 241         }
 242         return nil
 243 }