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

1# Copyright (c) 2010-2023 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 absolute_coordinate, 

24) 

25from openpyxl.cell import Cell, MergedCell 

26from openpyxl.formatting.formatting import ConditionalFormattingList 

27from openpyxl.packaging.relationship import RelationshipList 

28from openpyxl.workbook.child import _WorkbookChild 

29from openpyxl.workbook.defined_name import ( 

30 DefinedNameDict, 

31) 

32 

33from openpyxl.formula.translate import Translator 

34 

35from .datavalidation import DataValidationList 

36from .page import ( 

37 PrintPageSetup, 

38 PageMargins, 

39 PrintOptions, 

40) 

41from .dimensions import ( 

42 ColumnDimension, 

43 RowDimension, 

44 DimensionHolder, 

45 SheetFormatProperties, 

46) 

47from .protection import SheetProtection 

48from .filters import AutoFilter 

49from .views import ( 

50 Pane, 

51 Selection, 

52 SheetViewList, 

53) 

54from .cell_range import MultiCellRange, CellRange 

55from .merge import MergedCellRange 

56from .properties import WorksheetProperties 

57from .pagebreak import RowBreak, ColBreak 

58from .scenario import ScenarioList 

59from .table import TableList 

60from .formula import ArrayFormula 

61from .print_settings import ( 

62 PrintTitles, 

63 ColRange, 

64 RowRange, 

65 PrintArea, 

66) 

67 

68 

69class Worksheet(_WorkbookChild): 

70 """Represents a worksheet. 

71 

72 Do not create worksheets yourself, 

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

74 

75 """ 

76 

77 _rel_type = "worksheet" 

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

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

80 

81 BREAK_NONE = 0 

82 BREAK_ROW = 1 

83 BREAK_COLUMN = 2 

84 

85 SHEETSTATE_VISIBLE = 'visible' 

86 SHEETSTATE_HIDDEN = 'hidden' 

87 SHEETSTATE_VERYHIDDEN = 'veryHidden' 

88 

89 # Paper size 

90 PAPERSIZE_LETTER = '1' 

91 PAPERSIZE_LETTER_SMALL = '2' 

92 PAPERSIZE_TABLOID = '3' 

93 PAPERSIZE_LEDGER = '4' 

94 PAPERSIZE_LEGAL = '5' 

95 PAPERSIZE_STATEMENT = '6' 

96 PAPERSIZE_EXECUTIVE = '7' 

97 PAPERSIZE_A3 = '8' 

98 PAPERSIZE_A4 = '9' 

99 PAPERSIZE_A4_SMALL = '10' 

100 PAPERSIZE_A5 = '11' 

101 

102 # Page orientation 

103 ORIENTATION_PORTRAIT = 'portrait' 

104 ORIENTATION_LANDSCAPE = 'landscape' 

105 

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

107 _WorkbookChild.__init__(self, parent, title) 

108 self._setup() 

109 

110 def _setup(self): 

111 self.row_dimensions = DimensionHolder(worksheet=self, 

112 default_factory=self._add_row) 

113 self.column_dimensions = DimensionHolder(worksheet=self, 

114 default_factory=self._add_column) 

115 self.row_breaks = RowBreak() 

116 self.col_breaks = ColBreak() 

117 self._cells = {} 

118 self._charts = [] 

119 self._images = [] 

120 self._rels = RelationshipList() 

121 self._drawing = None 

122 self._comments = [] 

123 self.merged_cells = MultiCellRange() 

124 self._tables = TableList() 

125 self._pivots = [] 

126 self.data_validations = DataValidationList() 

127 self._hyperlinks = [] 

128 self.sheet_state = 'visible' 

129 self.page_setup = PrintPageSetup(worksheet=self) 

130 self.print_options = PrintOptions() 

131 self._print_rows = None 

132 self._print_cols = None 

133 self._print_area = PrintArea() 

134 self.page_margins = PageMargins() 

135 self.views = SheetViewList() 

136 self.protection = SheetProtection() 

137 self.defined_names = DefinedNameDict() 

138 

139 self._current_row = 0 

140 self.auto_filter = AutoFilter() 

141 self.conditional_formatting = ConditionalFormattingList() 

142 self.legacy_drawing = None 

143 self.sheet_properties = WorksheetProperties() 

144 self.sheet_format = SheetFormatProperties() 

145 self.scenarios = ScenarioList() 

146 

147 

148 @property 

149 def sheet_view(self): 

150 return self.views.sheetView[0] 

151 

152 

153 @property 

154 def selected_cell(self): 

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

156 

157 

158 @property 

159 def active_cell(self): 

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

161 

162 

163 @property 

164 def array_formulae(self): 

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

166 result = {} 

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

168 if c.data_type == "f": 

169 if isinstance(c.value, ArrayFormula): 

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

171 return result 

172 

173 

174 @property 

175 def show_gridlines(self): 

176 return self.sheet_view.showGridLines 

177 

178 

179 @property 

180 def freeze_panes(self): 

181 if self.sheet_view.pane is not None: 

182 return self.sheet_view.pane.topLeftCell 

183 

184 

185 @freeze_panes.setter 

186 def freeze_panes(self, topLeftCell=None): 

187 if isinstance(topLeftCell, Cell): 

188 topLeftCell = topLeftCell.coordinate 

189 if topLeftCell == 'A1': 

190 topLeftCell = None 

191 

192 if not topLeftCell: 

193 self.sheet_view.pane = None 

194 return 

195 

196 row, column = coordinate_to_tuple(topLeftCell) 

197 

198 view = self.sheet_view 

199 view.pane = Pane(topLeftCell=topLeftCell, 

200 activePane="topRight", 

201 state="frozen") 

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

203 

204 if column > 1: 

205 view.pane.xSplit = column - 1 

206 if row > 1: 

207 view.pane.ySplit = row - 1 

208 view.pane.activePane = 'bottomLeft' 

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

210 if column > 1: 

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

212 view.pane.activePane = 'bottomRight' 

213 

214 if row > 1 and column > 1: 

215 sel = list(view.selection) 

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

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

218 view.selection = sel 

219 

220 

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

222 """ 

223 Returns a cell object based on the given coordinates. 

224 

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

226 

227 Calling `cell` creates cells in memory when they 

228 are first accessed. 

229 

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

231 :type row: int 

232 

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

234 :type column: int 

235 

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

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

238 

239 :rtype: openpyxl.cell.cell.Cell 

240 """ 

241 

242 if row < 1 or column < 1: 

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

244 

245 cell = self._get_cell(row, column) 

246 if value is not None: 

247 cell.value = value 

248 

249 return cell 

250 

251 

252 def _get_cell(self, row, column): 

253 """ 

254 Internal method for getting a cell from a worksheet. 

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

256 """ 

257 if not 0 < row < 1048577: 

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

259 coordinate = (row, column) 

260 if not coordinate in self._cells: 

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

262 self._add_cell(cell) 

263 return self._cells[coordinate] 

264 

265 

266 def _add_cell(self, cell): 

267 """ 

268 Internal method for adding cell objects. 

269 """ 

270 column = cell.col_idx 

271 row = cell.row 

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

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

274 

275 

276 def __getitem__(self, key): 

277 """Convenience access by Excel style coordinates 

278 

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

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

281 4:10. 

282 

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

284 

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

286 """ 

287 if isinstance(key, slice): 

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

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

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

291 

292 if isinstance(key, int): 

293 key = str(key 

294 ) 

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

296 

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

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

299 

300 if min_row is None: 

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

302 if min_col == max_col: 

303 cols = cols[0] 

304 return cols 

305 if min_col is None: 

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

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

308 if min_row == max_row: 

309 rows = rows[0] 

310 return rows 

311 if ":" not in key: 

312 return self._get_cell(min_row, min_col) 

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

314 max_row=max_row, max_col=max_col)) 

315 

316 

317 def __setitem__(self, key, value): 

318 self[key].value = value 

319 

320 

321 def __iter__(self): 

322 return self.iter_rows() 

323 

324 

325 def __delitem__(self, key): 

326 row, column = coordinate_to_tuple(key) 

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

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

329 

330 

331 @property 

332 def min_row(self): 

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

334 

335 :type: int 

336 """ 

337 min_row = 1 

338 if self._cells: 

339 rows = set(c[0] for c in self._cells) 

340 min_row = min(rows) 

341 return min_row 

342 

343 

344 @property 

345 def max_row(self): 

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

347 

348 :type: int 

349 """ 

350 max_row = 1 

351 if self._cells: 

352 rows = set(c[0] for c in self._cells) 

353 max_row = max(rows) 

354 return max_row 

355 

356 

357 @property 

358 def min_column(self): 

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

360 

361 :type: int 

362 """ 

363 min_col = 1 

364 if self._cells: 

365 cols = set(c[1] for c in self._cells) 

366 min_col = min(cols) 

367 return min_col 

368 

369 

370 @property 

371 def max_column(self): 

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

373 

374 :type: int 

375 """ 

376 max_col = 1 

377 if self._cells: 

378 cols = set(c[1] for c in self._cells) 

379 max_col = max(cols) 

380 return max_col 

381 

382 

383 def calculate_dimension(self): 

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

385 

386 :rtype: string 

387 """ 

388 if self._cells: 

389 rows = set() 

390 cols = set() 

391 for row, col in self._cells: 

392 rows.add(row) 

393 cols.add(col) 

394 max_row = max(rows) 

395 max_col = max(cols) 

396 min_col = min(cols) 

397 min_row = min(rows) 

398 else: 

399 return "A1:A1" 

400 

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

402 

403 

404 @property 

405 def dimensions(self): 

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

407 return self.calculate_dimension() 

408 

409 

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

411 """ 

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

413 using indices of rows and columns. 

414 

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

416 

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

418 

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

420 :type min_col: int 

421 

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

423 :type min_row: int 

424 

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

426 :type max_col: int 

427 

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

429 :type max_row: int 

430 

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

432 :type values_only: bool 

433 

434 :rtype: generator 

435 """ 

436 

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

438 return iter(()) 

439 

440 

441 min_col = min_col or 1 

442 min_row = min_row or 1 

443 max_col = max_col or self.max_column 

444 max_row = max_row or self.max_row 

445 

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

447 

448 

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

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

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

452 if values_only: 

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

454 else: 

455 yield tuple(cells) 

456 

457 

458 @property 

459 def rows(self): 

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

461 

462 :type: generator 

463 """ 

464 return self.iter_rows() 

465 

466 

467 @property 

468 def values(self): 

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

470 

471 :type: generator 

472 """ 

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

474 yield row 

475 

476 

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

478 """ 

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

480 using indices of rows and columns. 

481 

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

483 

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

485 

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

487 :type min_col: int 

488 

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

490 :type min_row: int 

491 

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

493 :type max_col: int 

494 

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

496 :type max_row: int 

497 

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

499 :type values_only: bool 

500 

501 :rtype: generator 

502 """ 

503 

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

505 return iter(()) 

506 

507 min_col = min_col or 1 

508 min_row = min_row or 1 

509 max_col = max_col or self.max_column 

510 max_row = max_row or self.max_row 

511 

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

513 

514 

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

516 """ 

517 Get cells by column 

518 """ 

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

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

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

522 if values_only: 

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

524 else: 

525 yield tuple(cells) 

526 

527 

528 @property 

529 def columns(self): 

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

531 return self.iter_cols() 

532 

533 

534 def set_printer_settings(self, paper_size, orientation): 

535 """Set printer settings """ 

536 

537 self.page_setup.paperSize = paper_size 

538 self.page_setup.orientation = orientation 

539 

540 

541 def add_data_validation(self, data_validation): 

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

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

544 cell or range of cells it should apply to. 

545 """ 

546 self.data_validations.append(data_validation) 

547 

548 

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

550 """ 

551 Add a chart to the sheet 

552 Optionally provide a cell for the top-left anchor 

553 """ 

554 if anchor is not None: 

555 chart.anchor = anchor 

556 self._charts.append(chart) 

557 

558 

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

560 """ 

561 Add an image to the sheet. 

562 Optionally provide a cell for the top-left anchor 

563 """ 

564 if anchor is not None: 

565 img.anchor = anchor 

566 self._images.append(img) 

567 

568 

569 def add_table(self, table): 

570 """ 

571 Check for duplicate name in definedNames and other worksheet tables 

572 before adding table. 

573 """ 

574 

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

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

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

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

579 self._tables.add(table) 

580 

581 

582 @property 

583 def tables(self): 

584 return self._tables 

585 

586 

587 def add_pivot(self, pivot): 

588 self._pivots.append(pivot) 

589 

590 

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

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

593 if range_string is None: 

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

595 max_col=end_column, max_row=end_row) 

596 range_string = cr.coord 

597 mcr = MergedCellRange(self, range_string) 

598 self.merged_cells.add(mcr) 

599 self._clean_merge_range(mcr) 

600 

601 

602 def _clean_merge_range(self, mcr): 

603 """ 

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

605 and recreate the lost border information. 

606 Borders are then applied 

607 """ 

608 cells = mcr.cells 

609 next(cells) # skip first cell 

610 for row, col in cells: 

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

612 mcr.format() 

613 

614 

615 @property 

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

617 def merged_cell_ranges(self): 

618 """Return a copy of cell ranges""" 

619 return self.merged_cells.ranges[:] 

620 

621 

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

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

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

625 max_col=end_column, max_row=end_row) 

626 

627 if cr.coord not in self.merged_cells: 

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

629 

630 self.merged_cells.remove(cr) 

631 

632 cells = cr.cells 

633 next(cells) # skip first cell 

634 for row, col in cells: 

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

636 

637 

638 def append(self, iterable): 

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

640 

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

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

643 

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

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

646 

647 Usage: 

648 

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

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

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

652 

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

654 

655 """ 

656 row_idx = self._current_row + 1 

657 

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

659 or isgenerator(iterable)): 

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

661 if isinstance(content, Cell): 

662 # compatible with write-only mode 

663 cell = content 

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

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

666 cell.parent = self 

667 cell.column = col_idx 

668 cell.row = row_idx 

669 else: 

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

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

672 

673 elif isinstance(iterable, dict): 

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

675 if isinstance(col_idx, str): 

676 col_idx = column_index_from_string(col_idx) 

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

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

679 

680 else: 

681 self._invalid_row(iterable) 

682 

683 self._current_row = row_idx 

684 

685 

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

687 """ 

688 Move either rows or columns around by the offset 

689 """ 

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

691 row_offset = 0 

692 col_offset = 0 

693 

694 # need to make affected ranges contiguous 

695 if row_or_col == 'row': 

696 cells = self.iter_rows(min_row=min_row) 

697 row_offset = offset 

698 key = 0 

699 else: 

700 cells = self.iter_cols(min_col=min_col) 

701 col_offset = offset 

702 key = 1 

703 cells = list(cells) 

704 

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

706 if min_row and row < min_row: 

707 continue 

708 elif min_col and column < min_col: 

709 continue 

710 

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

712 

713 

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

715 """ 

716 Insert row or rows before row==idx 

717 """ 

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

719 self._current_row = self.max_row 

720 

721 

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

723 """ 

724 Insert column or columns before col==idx 

725 """ 

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

727 

728 

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

730 """ 

731 Delete row or rows from row==idx 

732 """ 

733 

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

735 

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

737 

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

739 min_col = self.min_column 

740 max_col = self.max_column + 1 

741 for row in remainder: 

742 for col in range(min_col, max_col): 

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

744 del self._cells[row, col] 

745 self._current_row = self.max_row 

746 if not self._cells: 

747 self._current_row = 0 

748 

749 

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

751 """ 

752 Delete column or columns from col==idx 

753 """ 

754 

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

756 

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

758 

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

760 min_row = self.min_row 

761 max_row = self.max_row + 1 

762 for col in remainder: 

763 for row in range(min_row, max_row): 

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

765 del self._cells[row, col] 

766 

767 

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

769 """ 

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

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

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

773 Existing cells will be overwritten. 

774 Formulae and references will not be updated. 

775 """ 

776 if isinstance(cell_range, str): 

777 cell_range = CellRange(cell_range) 

778 if not isinstance(cell_range, CellRange): 

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

780 if not rows and not cols: 

781 return 

782 

783 down = rows > 0 

784 right = cols > 0 

785 

786 if rows: 

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

788 else: 

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

790 

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

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

793 

794 # rebase moved range 

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

796 

797 

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

799 """ 

800 Move a cell from one place to another. 

801 Delete at old index 

802 Rebase coordinate 

803 """ 

804 cell = self._get_cell(row, column) 

805 new_row = cell.row + row_offset 

806 new_col = cell.column + col_offset 

807 self._cells[new_row, new_col] = cell 

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

809 cell.row = new_row 

810 cell.column = new_col 

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

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

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

814 

815 

816 def _invalid_row(self, iterable): 

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

818 type(iterable)) 

819 ) 

820 

821 

822 def _add_column(self): 

823 """Dimension factory for column information""" 

824 

825 return ColumnDimension(self) 

826 

827 def _add_row(self): 

828 """Dimension factory for row information""" 

829 

830 return RowDimension(self) 

831 

832 

833 @property 

834 def print_title_rows(self): 

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

836 if self._print_rows: 

837 return str(self._print_rows) 

838 

839 

840 @print_title_rows.setter 

841 def print_title_rows(self, rows): 

842 """ 

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

844 format `1:3` 

845 """ 

846 if rows is not None: 

847 self._print_rows = RowRange(rows) 

848 

849 

850 @property 

851 def print_title_cols(self): 

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

853 if self._print_cols: 

854 return str(self._print_cols) 

855 

856 

857 @print_title_cols.setter 

858 def print_title_cols(self, cols): 

859 """ 

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

861 format ``A:C` 

862 """ 

863 if cols is not None: 

864 self._print_cols = ColRange(cols) 

865 

866 

867 @property 

868 def print_titles(self): 

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

870 return str(titles) 

871 

872 

873 @property 

874 def print_area(self): 

875 """ 

876 The print area for the worksheet, or None if not set. To set, supply a range 

877 like 'A1:D4' or a list of ranges.