Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/openpyxl/worksheet/worksheet.py: 48%

1# Copyright (c) 2010-2024 openpyxl 

2 

3"""Worksheet is the 2nd-level container in Excel.""" 

4 

5 

6# Python stdlib imports 

7from itertools import chain 

8from operator import itemgetter 

9from inspect import isgenerator 

10from warnings import warn 

11 

12# compatibility imports 

13from openpyxl.compat import ( 

14 deprecated, 

15) 

16 

17# package imports 

18from openpyxl.utils import ( 

19 column_index_from_string, 

20 get_column_letter, 

21 range_boundaries, 

22 coordinate_to_tuple, 

23) 

24from openpyxl.cell import Cell, MergedCell 

25from openpyxl.formatting.formatting import ConditionalFormattingList 

26from openpyxl.packaging.relationship import RelationshipList 

27from openpyxl.workbook.child import _WorkbookChild 

28from openpyxl.workbook.defined_name import ( 

29 DefinedNameDict, 

30) 

31 

32from openpyxl.formula.translate import Translator 

33 

34from .datavalidation import DataValidationList 

35from .page import ( 

36 PrintPageSetup, 

37 PageMargins, 

38 PrintOptions, 

39) 

40from .dimensions import ( 

41 ColumnDimension, 

42 RowDimension, 

43 DimensionHolder, 

44 SheetFormatProperties, 

45) 

46from .protection import SheetProtection 

47from .filters import AutoFilter 

48from .views import ( 

49 Pane, 

50 Selection, 

51 SheetViewList, 

52) 

53from .cell_range import MultiCellRange, CellRange 

54from .merge import MergedCellRange 

55from .properties import WorksheetProperties 

56from .pagebreak import RowBreak, ColBreak 

57from .scenario import ScenarioList 

58from .table import TableList 

59from .formula import ArrayFormula 

60from .print_settings import ( 

61 PrintTitles, 

62 ColRange, 

63 RowRange, 

64 PrintArea, 

65) 

66 

67 

68class Worksheet(_WorkbookChild): 

69 """Represents a worksheet. 

70 

71 Do not create worksheets yourself, 

72 use :func:`openpyxl.workbook.Workbook.create_sheet` instead 

73 

74 """ 

75 

76 _rel_type = "worksheet" 

77 _path = "/xl/worksheets/sheet{0}.xml" 

78 mime_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" 

79 

80 BREAK_NONE = 0 

81 BREAK_ROW = 1 

82 BREAK_COLUMN = 2 

83 

84 SHEETSTATE_VISIBLE = 'visible' 

85 SHEETSTATE_HIDDEN = 'hidden' 

86 SHEETSTATE_VERYHIDDEN = 'veryHidden' 

87 

88 # Paper size 

89 PAPERSIZE_LETTER = '1' 

90 PAPERSIZE_LETTER_SMALL = '2' 

91 PAPERSIZE_TABLOID = '3' 

92 PAPERSIZE_LEDGER = '4' 

93 PAPERSIZE_LEGAL = '5' 

94 PAPERSIZE_STATEMENT = '6' 

95 PAPERSIZE_EXECUTIVE = '7' 

96 PAPERSIZE_A3 = '8' 

97 PAPERSIZE_A4 = '9' 

98 PAPERSIZE_A4_SMALL = '10' 

99 PAPERSIZE_A5 = '11' 

100 

101 # Page orientation 

102 ORIENTATION_PORTRAIT = 'portrait' 

103 ORIENTATION_LANDSCAPE = 'landscape' 

104 

105 def __init__(self, parent, title=None): 

106 _WorkbookChild.__init__(self, parent, title) 

107 self._setup() 

108 

109 def _setup(self): 

110 self.row_dimensions = DimensionHolder(worksheet=self, 

111 default_factory=self._add_row) 

112 self.column_dimensions = DimensionHolder(worksheet=self, 

113 default_factory=self._add_column) 

114 self.row_breaks = RowBreak() 

115 self.col_breaks = ColBreak() 

116 self._cells = {} 

117 self._charts = [] 

118 self._images = [] 

119 self._rels = RelationshipList() 

120 self._drawing = None 

121 self._comments = [] 

122 self.merged_cells = MultiCellRange() 

123 self._tables = TableList() 

124 self._pivots = [] 

125 self.data_validations = DataValidationList() 

126 self._hyperlinks = [] 

127 self.sheet_state = 'visible' 

128 self.page_setup = PrintPageSetup(worksheet=self) 

129 self.print_options = PrintOptions() 

130 self._print_rows = None 

131 self._print_cols = None 

132 self._print_area = PrintArea() 

133 self.page_margins = PageMargins() 

134 self.views = SheetViewList() 

135 self.protection = SheetProtection() 

136 self.defined_names = DefinedNameDict() 

137 

138 self._current_row = 0 

139 self.auto_filter = AutoFilter() 

140 self.conditional_formatting = ConditionalFormattingList() 

141 self.legacy_drawing = None 

142 self.sheet_properties = WorksheetProperties() 

143 self.sheet_format = SheetFormatProperties() 

144 self.scenarios = ScenarioList() 

145 

146 

147 @property 

148 def sheet_view(self): 

149 return self.views.active 

150 

151 

152 @property 

153 def selected_cell(self): 

154 return self.sheet_view.selection[0].sqref 

155 

156 

157 @property 

158 def active_cell(self): 

159 return self.sheet_view.selection[0].activeCell 

160 

161 

162 @property 

163 def array_formulae(self): 

164 """Returns a dictionary of cells with array formulae and the cells in array""" 

165 result = {} 

166 for c in self._cells.values(): 

167 if c.data_type == "f": 

168 if isinstance(c.value, ArrayFormula): 

169 result[c.coordinate] = c.value.ref 

170 return result 

171 

172 

173 @property 

174 def show_gridlines(self): 

175 return self.sheet_view.showGridLines 

176 

177 

178 @property 

179 def freeze_panes(self): 

180 if self.sheet_view.pane is not None: 

181 return self.sheet_view.pane.topLeftCell 

182 

183 

184 @freeze_panes.setter 

185 def freeze_panes(self, topLeftCell=None): 

186 if isinstance(topLeftCell, Cell): 

187 topLeftCell = topLeftCell.coordinate 

188 if topLeftCell == 'A1': 

189 topLeftCell = None 

190 

191 if not topLeftCell: 

192 self.sheet_view.pane = None 

193 return 

194 

195 row, column = coordinate_to_tuple(topLeftCell) 

196 

197 view = self.sheet_view 

198 view.pane = Pane(topLeftCell=topLeftCell, 

199 activePane="topRight", 

200 state="frozen") 

201 view.selection[0].pane = "topRight" 

202 

203 if column > 1: 

204 view.pane.xSplit = column - 1 

205 if row > 1: 

206 view.pane.ySplit = row - 1 

207 view.pane.activePane = 'bottomLeft' 

208 view.selection[0].pane = "bottomLeft" 

209 if column > 1: 

210 view.selection[0].pane = "bottomRight" 

211 view.pane.activePane = 'bottomRight' 

212 

213 if row > 1 and column > 1: 

214 sel = list(view.selection) 

215 sel.insert(0, Selection(pane="topRight", activeCell=None, sqref=None)) 

216 sel.insert(1, Selection(pane="bottomLeft", activeCell=None, sqref=None)) 

217 view.selection = sel 

218 

219 

220 def cell(self, row, column, value=None): 

221 """ 

222 Returns a cell object based on the given coordinates. 

223 

224 Usage: cell(row=15, column=1, value=5) 

225 

226 Calling `cell` creates cells in memory when they 

227 are first accessed. 

228 

229 :param row: row index of the cell (e.g. 4) 

230 :type row: int 

231 

232 :param column: column index of the cell (e.g. 3) 

233 :type column: int 

234 

235 :param value: value of the cell (e.g. 5) 

236 :type value: numeric or time or string or bool or none 

237 

238 :rtype: openpyxl.cell.cell.Cell 

239 """ 

240 

241 if row < 1 or column < 1: 

242 raise ValueError("Row or column values must be at least 1") 

243 

244 cell = self._get_cell(row, column) 

245 if value is not None: 

246 cell.value = value 

247 

248 return cell 

249 

250 

251 def _get_cell(self, row, column): 

252 """ 

253 Internal method for getting a cell from a worksheet. 

254 Will create a new cell if one doesn't already exist. 

255 """ 

256 if not 0 < row < 1048577: 

257 raise ValueError(f"Row numbers must be between 1 and 1048576. Row number supplied was {row}") 

258 coordinate = (row, column) 

259 if not coordinate in self._cells: 

260 cell = Cell(self, row=row, column=column) 

261 self._add_cell(cell) 

262 return self._cells[coordinate] 

263 

264 

265 def _add_cell(self, cell): 

266 """ 

267 Internal method for adding cell objects. 

268 """ 

269 column = cell.col_idx 

270 row = cell.row 

271 self._current_row = max(row, self._current_row) 

272 self._cells[(row, column)] = cell 

273 

274 

275 def __getitem__(self, key): 

276 """Convenience access by Excel style coordinates 

277 

278 The key can be a single cell coordinate 'A1', a range of cells 'A1:D25', 

279 individual rows or columns 'A', 4 or ranges of rows or columns 'A:D', 

280 4:10. 

281 

282 Single cells will always be created if they do not exist. 

283 

284 Returns either a single cell or a tuple of rows or columns. 

285 """ 

286 if isinstance(key, slice): 

287 if not all([key.start, key.stop]): 

288 raise IndexError("{0} is not a valid coordinate or range".format(key)) 

289 key = "{0}:{1}".format(key.start, key.stop) 

290 

291 if isinstance(key, int): 

292 key = str(key 

293 ) 

294 min_col, min_row, max_col, max_row = range_boundaries(key) 

295 

296 if not any([min_col, min_row, max_col, max_row]): 

297 raise IndexError("{0} is not a valid coordinate or range".format(key)) 

298 

299 if min_row is None: 

300 cols = tuple(self.iter_cols(min_col, max_col)) 

301 if min_col == max_col: 

302 cols = cols[0] 

303 return cols 

304 if min_col is None: 

305 rows = tuple(self.iter_rows(min_col=min_col, min_row=min_row, 

306 max_col=self.max_column, max_row=max_row)) 

307 if min_row == max_row: 

308 rows = rows[0] 

309 return rows 

310 if ":" not in key: 

311 return self._get_cell(min_row, min_col) 

312 return tuple(self.iter_rows(min_row=min_row, min_col=min_col, 

313 max_row=max_row, max_col=max_col)) 

314 

315 

316 def __setitem__(self, key, value): 

317 self[key].value = value 

318 

319 

320 def __iter__(self): 

321 return self.iter_rows() 

322 

323 

324 def __delitem__(self, key): 

325 row, column = coordinate_to_tuple(key) 

326 if (row, column) in self._cells: 

327 del self._cells[(row, column)] 

328 

329 

330 @property 

331 def min_row(self): 

332 """The minimum row index containing data (1-based) 

333 

334 :type: int 

335 """ 

336 min_row = 1 

337 if self._cells: 

338 min_row = min(self._cells)[0] 

339 return min_row 

340 

341 

342 @property 

343 def max_row(self): 

344 """The maximum row index containing data (1-based) 

345 

346 :type: int 

347 """ 

348 max_row = 1 

349 if self._cells: 

350 max_row = max(self._cells)[0] 

351 return max_row 

352 

353 

354 @property 

355 def min_column(self): 

356 """The minimum column index containing data (1-based) 

357 

358 :type: int 

359 """ 

360 min_col = 1 

361 if self._cells: 

362 min_col = min(c[1] for c in self._cells) 

363 return min_col 

364 

365 

366 @property 

367 def max_column(self): 

368 """The maximum column index containing data (1-based) 

369 

370 :type: int 

371 """ 

372 max_col = 1 

373 if self._cells: 

374 max_col = max(c[1] for c in self._cells) 

375 return max_col 

376 

377 

378 def calculate_dimension(self): 

379 """Return the minimum bounding range for all cells containing data (ex. 'A1:M24') 

380 

381 :rtype: string 

382 """ 

383 if self._cells: 

384 rows = set() 

385 cols = set() 

386 for row, col in self._cells: 

387 rows.add(row) 

388 cols.add(col) 

389 max_row = max(rows) 

390 max_col = max(cols) 

391 min_col = min(cols) 

392 min_row = min(rows) 

393 else: 

394 return "A1:A1" 

395 

396 return f"{get_column_letter(min_col)}{min_row}:{get_column_letter(max_col)}{max_row}" 

397 

398 

399 @property 

400 def dimensions(self): 

401 """Returns the result of :func:`calculate_dimension`""" 

402 return self.calculate_dimension() 

403 

404 

405 def iter_rows(self, min_row=None, max_row=None, min_col=None, max_col=None, values_only=False): 

406 """ 

407 Produces cells from the worksheet, by row. Specify the iteration range 

408 using indices of rows and columns. 

409 

410 If no indices are specified the range starts at A1. 

411 

412 If no cells are in the worksheet an empty tuple will be returned. 

413 

414 :param min_col: smallest column index (1-based index) 

415 :type min_col: int 

416 

417 :param min_row: smallest row index (1-based index) 

418 :type min_row: int 

419 

420 :param max_col: largest column index (1-based index) 

421 :type max_col: int 

422 

423 :param max_row: largest row index (1-based index) 

424 :type max_row: int 

425 

426 :param values_only: whether only cell values should be returned 

427 :type values_only: bool 

428 

429 :rtype: generator 

430 """ 

431 

432 if self._current_row == 0 and not any([min_col, min_row, max_col, max_row ]): 

433 return iter(()) 

434 

435 

436 min_col = min_col or 1 

437 min_row = min_row or 1 

438 max_col = max_col or self.max_column 

439 max_row = max_row or self.max_row 

440 

441 return self._cells_by_row(min_col, min_row, max_col, max_row, values_only) 

442 

443 

444 def _cells_by_row(self, min_col, min_row, max_col, max_row, values_only=False): 

445 for row in range(min_row, max_row + 1): 

446 cells = (self.cell(row=row, column=column) for column in range(min_col, max_col + 1)) 

447 if values_only: 

448 yield tuple(cell.value for cell in cells) 

449 else: 

450 yield tuple(cells) 

451 

452 

453 @property 

454 def rows(self): 

455 """Produces all cells in the worksheet, by row (see :func:`iter_rows`) 

456 

457 :type: generator 

458 """ 

459 return self.iter_rows() 

460 

461 

462 @property 

463 def values(self): 

464 """Produces all cell values in the worksheet, by row 

465 

466 :type: generator 

467 """ 

468 for row in self.iter_rows(values_only=True): 

469 yield row 

470 

471 

472 def iter_cols(self, min_col=None, max_col=None, min_row=None, max_row=None, values_only=False): 

473 """ 

474 Produces cells from the worksheet, by column. Specify the iteration range 

475 using indices of rows and columns. 

476 

477 If no indices are specified the range starts at A1. 

478 

479 If no cells are in the worksheet an empty tuple will be returned. 

480 

481 :param min_col: smallest column index (1-based index) 

482 :type min_col: int 

483 

484 :param min_row: smallest row index (1-based index) 

485 :type min_row: int 

486 

487 :param max_col: largest column index (1-based index) 

488 :type max_col: int 

489 

490 :param max_row: largest row index (1-based index) 

491 :type max_row: int 

492 

493 :param values_only: whether only cell values should be returned 

494 :type values_only: bool 

495 

496 :rtype: generator 

497 """ 

498 

499 if self._current_row == 0 and not any([min_col, min_row, max_col, max_row]): 

500 return iter(()) 

501 

502 min_col = min_col or 1 

503 min_row = min_row or 1 

504 max_col = max_col or self.max_column 

505 max_row = max_row or self.max_row 

506 

507 return self._cells_by_col(min_col, min_row, max_col, max_row, values_only) 

508 

509 

510 def _cells_by_col(self, min_col, min_row, max_col, max_row, values_only=False): 

511 """ 

512 Get cells by column 

513 """ 

514 for column in range(min_col, max_col+1): 

515 cells = (self.cell(row=row, column=column) 

516 for row in range(min_row, max_row+1)) 

517 if values_only: 

518 yield tuple(cell.value for cell in cells) 

519 else: 

520 yield tuple(cells) 

521 

522 

523 @property 

524 def columns(self): 

525 """Produces all cells in the worksheet, by column (see :func:`iter_cols`)""" 

526 return self.iter_cols() 

527 

528 

529 @property 

530 def column_groups(self): 

531 """ 

532 Return a list of column ranges where more than one column 

533 """ 

534 return [cd.range for cd in self.column_dimensions.values() if cd.min and cd.max > cd.min] 

535 

536 

537 def set_printer_settings(self, paper_size, orientation): 

538 """Set printer settings """ 

539 

540 self.page_setup.paperSize = paper_size 

541 self.page_setup.orientation = orientation 

542 

543 

544 def add_data_validation(self, data_validation): 

545 """ Add a data-validation object to the sheet. The data-validation 

546 object defines the type of data-validation to be applied and the 

547 cell or range of cells it should apply to. 

548 """ 

549 self.data_validations.append(data_validation) 

550 

551 

552 def add_chart(self, chart, anchor=None): 

553 """ 

554 Add a chart to the sheet 

555 Optionally provide a cell for the top-left anchor 

556 """ 

557 if anchor is not None: 

558 chart.anchor = anchor 

559 self._charts.append(chart) 

560 

561 

562 def add_image(self, img, anchor=None): 

563 """ 

564 Add an image to the sheet. 

565 Optionally provide a cell for the top-left anchor 

566 """ 

567 if anchor is not None: 

568 img.anchor = anchor 

569 self._images.append(img) 

570 

571 

572 def add_table(self, table): 

573 """ 

574 Check for duplicate name in definedNames and other worksheet tables 

575 before adding table. 

576 """ 

577 

578 if self.parent._duplicate_name(table.name): 

579 raise ValueError("Table with name {0} already exists".format(table.name)) 

580 if not hasattr(self, "_get_cell"): 

581 warn("In write-only mode you must add table columns manually") 

582 self._tables.add(table) 

583 

584 

585 @property 

586 def tables(self): 

587 return self._tables 

588 

589 

590 def add_pivot(self, pivot): 

591 self._pivots.append(pivot) 

592 

593 

594 def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None): 

595 """ Set merge on a cell range. Range is a cell range (e.g. A1:E1) """ 

596 if range_string is None: 

597 cr = CellRange(range_string=range_string, min_col=start_column, min_row=start_row, 

598 max_col=end_column, max_row=end_row) 

599 range_string = cr.coord 

600 mcr = MergedCellRange(self, range_string) 

601 self.merged_cells.add(mcr) 

602 self._clean_merge_range(mcr) 

603 

604 

605 def _clean_merge_range(self, mcr): 

606 """ 

607 Remove all but the top left-cell from a range of merged cells 

608 and recreate the lost border information. 

609 Borders are then applied 

610 """ 

611 cells = mcr.cells 

612 next(cells) # skip first cell 

613 for row, col in cells: 

614 self._cells[row, col] = MergedCell(self, row, col) 

615 mcr.format() 

616 

617 

618 @property 

619 @deprecated("Use ws.merged_cells.ranges") 

620 def merged_cell_ranges(self): 

621 """Return a copy of cell ranges""" 

622 return self.merged_cells.ranges[:] 

623 

624 

625 def unmerge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None): 

626 """ Remove merge on a cell range. Range is a cell range (e.g. A1:E1) """ 

627 cr = CellRange(range_string=range_string, min_col=start_column, min_row=start_row, 

628 max_col=end_column, max_row=end_row) 

629 

630 if cr.coord not in self.merged_cells: 

631 raise ValueError("Cell range {0} is not merged".format(cr.coord)) 

632 

633 self.merged_cells.remove(cr) 

634 

635 cells = cr.cells 

636 next(cells) # skip first cell 

637 for row, col in cells: 

638 del self._cells[(row, col)] 

639 

640 

641 def append(self, iterable): 

642 """Appends a group of values at the bottom of the current sheet. 

643 

644 * If it's a list: all values are added in order, starting from the first column 

645 * If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters) 

646 

647 :param iterable: list, range or generator, or dict containing values to append 

648 :type iterable: list|tuple|range|generator or dict 

649 

650 Usage: 

651 

652 * append(['This is A1', 'This is B1', 'This is C1']) 

653 * **or** append({'A' : 'This is A1', 'C' : 'This is C1'}) 

654 * **or** append({1 : 'This is A1', 3 : 'This is C1'}) 

655 

656 :raise: TypeError when iterable is neither a list/tuple nor a dict 

657 

658 """ 

659 row_idx = self._current_row + 1 

660 

661 if (isinstance(iterable, (list, tuple, range)) 

662 or isgenerator(iterable)): 

663 for col_idx, content in enumerate(iterable, 1): 

664 if isinstance(content, Cell): 

665 # compatible with write-only mode 

666 cell = content 

667 if cell.parent and cell.parent != self: 

668 raise ValueError("Cells cannot be copied from other worksheets") 

669 cell.parent = self 

670 cell.column = col_idx 

671 cell.row = row_idx 

672 else: 

673 cell = Cell(self, row=row_idx, column=col_idx, value=content) 

674 self._cells[(row_idx, col_idx)] = cell 

675 

676 elif isinstance(iterable, dict): 

677 for col_idx, content in iterable.items(): 

678 if isinstance(col_idx, str): 

679 col_idx = column_index_from_string(col_idx) 

680 cell = Cell(self, row=row_idx, column=col_idx, value=content) 

681 self._cells[(row_idx, col_idx)] = cell 

682 

683 else: 

684 self._invalid_row(iterable) 

685 

686 self._current_row = row_idx 

687 

688 

689 def _move_cells(self, min_row=None, min_col=None, offset=0, row_or_col="row"): 

690 """ 

691 Move either rows or columns around by the offset 

692 """ 

693 reverse = offset > 0 # start at the end if inserting 

694 row_offset = 0 

695 col_offset = 0 

696 

697 # need to make affected ranges contiguous 

698 if row_or_col == 'row': 

699 cells = self.iter_rows(min_row=min_row) 

700 row_offset = offset 

701 key = 0 

702 else: 

703 cells = self.iter_cols(min_col=min_col) 

704 col_offset = offset 

705 key = 1 

706 cells = list(cells) 

707 

708 for row, column in sorted(self._cells, key=itemgetter(key), reverse=reverse): 

709 if min_row and row < min_row: 

710 continue 

711 elif min_col and column < min_col: 

712 continue 

713 

714 self._move_cell(row, column, row_offset, col_offset) 

715 

716 

717 def insert_rows(self, idx, amount=1): 

718 """ 

719 Insert row or rows before row==idx 

720 """ 

721 self._move_cells(min_row=idx, offset=amount, row_or_col="row") 

722 self._current_row = self.max_row 

723 

724 

725 def insert_cols(self, idx, amount=1): 

726 """ 

727 Insert column or columns before col==idx 

728 """ 

729 self._move_cells(min_col=idx, offset=amount, row_or_col="column") 

730 

731 

732 def delete_rows(self, idx, amount=1): 

733 """ 

734 Delete row or rows from row==idx 

735 """ 

736 

737 remainder = _gutter(idx, amount, self.max_row) 

738 

739 self._move_cells(min_row=idx+amount, offset=-amount, row_or_col="row") 

740 

741 # calculating min and max col is an expensive operation, do it only once 

742 min_col = self.min_column 

743 max_col = self.max_column + 1 

744 for row in remainder: 

745 for col in range(min_col, max_col): 

746 if (row, col) in self._cells: 

747 del self._cells[row, col] 

748 self._current_row = self.max_row 

749 if not self._cells: 

750 self._current_row = 0 

751 

752 

753 def delete_cols(self, idx, amount=1): 

754 """ 

755 Delete column or columns from col==idx 

756 """ 

757 

758 remainder = _gutter(idx, amount, self.max_column) 

759 

760 self._move_cells(min_col=idx+amount, offset=-amount, row_or_col="column") 

761 

762 # calculating min and max row is an expensive operation, do it only once 

763 min_row = self.min_row 

764 max_row = self.max_row + 1 

765 for col in remainder: 

766 for row in range(min_row, max_row): 

767 if (row, col) in self._cells: 

768 del self._cells[row, col] 

769 

770 

771 def move_range(self, cell_range, rows=0, cols=0, translate=False): 

772 """ 

773 Move a cell range by the number of rows and/or columns: 

774 down if rows > 0 and up if rows < 0 

775 right if cols > 0 and left if cols < 0 

776 Existing cells will be overwritten. 

777 Formulae and references will not be updated. 

778 """ 

779 if isinstance(cell_range, str): 

780 cell_range = CellRange(cell_range) 

781 if not isinstance(cell_range, CellRange): 

782 raise ValueError("Only CellRange objects can be moved") 

783 if not rows and not cols: 

784 return 

785 

786 down = rows > 0 

787 right = cols > 0 

788 

789 if rows: 

790 cells = sorted(cell_range.rows, reverse=down) 

791 else: 

792 cells = sorted(cell_range.cols, reverse=right) 

793 

794 for row, col in chain.from_iterable(cells): 

795 self._move_cell(row, col, rows, cols, translate) 

796 

797 # rebase moved range 

798 cell_range.shift(row_shift=rows, col_shift=cols) 

799 

800 

801 def _move_cell(self, row, column, row_offset, col_offset, translate=False): 

802 """ 

803 Move a cell from one place to another. 

804 Delete at old index 

805 Rebase coordinate 

806 """ 

807 cell = self._get_cell(row, column) 

808 new_row = cell.row + row_offset 

809 new_col = cell.column + col_offset 

810 self._cells[new_row, new_col] = cell 

811 del self._cells[(cell.row, cell.column)] 

812 cell.row = new_row 

813 cell.column = new_col 

814 if translate and cell.data_type == "f": 

815 t = Translator(cell.value, cell.coordinate) 

816 cell.value = t.translate_formula(row_delta=row_offset, col_delta=col_offset) 

817 

818 

819 def _invalid_row(self, iterable): 

820 raise TypeError('Value must be a list, tuple, range or generator, or a dict. Supplied value is {0}'.format( 

821 type(iterable)) 

822 ) 

823 

824 

825 def _add_column(self): 

826 """Dimension factory for column information""" 

827 

828 return ColumnDimension(self) 

829 

830 def _add_row(self): 

831 """Dimension factory for row information""" 

832 

833 return RowDimension(self) 

834 

835 

836 @property 

837 def print_title_rows(self): 

838 """Rows to be printed at the top of every page (ex: '1:3')""" 

839 if self._print_rows: 

840 return str(self._print_rows) 

841 

842 

843 @print_title_rows.setter 

844 def print_title_rows(self, rows): 

845 """ 

846 Set rows to be printed on the top of every page 

847 format `1:3` 

848 """ 

849 if rows is not None: 

850 self._print_rows = RowRange(rows) 

851 

852 

853 @property 

854 def print_title_cols(self): 

855 """Columns to be printed at the left side of every page (ex: 'A:C')""" 

856 if self._print_cols: 

857 return str(self._print_cols) 

858 

859 

860 @print_title_cols.setter 

861 def print_title_cols(self, cols): 

862 """ 

863 Set cols to be printed on the left of every page 

864 format ``A:C` 

865 """ 

866 if cols is not None: 

867 self._print_cols = ColRange(cols) 

868 

869 

870 @property 

871 def print_titles(self): 

872 titles = PrintTitles(cols=self._print_cols, rows=self._print_rows, title=self.title) 

873 return str(titles) 

874 

875 

876 @property 

877 def print_area(self):