Hide keyboard shortcuts

Hot-keys on this page

r m x p   toggle line displays

j k   next/prev highlighted chunk

0   (zero) top of page

1   (one) first highlighted chunk

1# Copyright (c) 2010-2020 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 COL_RANGE_RE, ROW_RANGE_RE 

30from openpyxl.formula.translate import Translator 

31 

32from .datavalidation import DataValidationList 

33from .page import ( 

34 PrintPageSetup, 

35 PageMargins, 

36 PrintOptions, 

37) 

38from .dimensions import ( 

39 ColumnDimension, 

40 RowDimension, 

41 DimensionHolder, 

42 SheetFormatProperties, 

43) 

44from .protection import SheetProtection 

45from .filters import AutoFilter 

46from .views import ( 

47 Pane, 

48 Selection, 

49 SheetViewList, 

50) 

51from .cell_range import MultiCellRange, CellRange 

52from .merge import MergedCellRange 

53from .properties import WorksheetProperties 

54from .pagebreak import RowBreak, ColBreak 

55from .scenario import ScenarioList 

56from .table import TableList 

57 

58 

59class Worksheet(_WorkbookChild): 

60 """Represents a worksheet. 

61 

62 Do not create worksheets yourself, 

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

64 

65 """ 

66 

67 _rel_type = "worksheet" 

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

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

70 

71 BREAK_NONE = 0 

72 BREAK_ROW = 1 

73 BREAK_COLUMN = 2 

74 

75 SHEETSTATE_VISIBLE = 'visible' 

76 SHEETSTATE_HIDDEN = 'hidden' 

77 SHEETSTATE_VERYHIDDEN = 'veryHidden' 

78 

79 # Paper size 

80 PAPERSIZE_LETTER = '1' 

81 PAPERSIZE_LETTER_SMALL = '2' 

82 PAPERSIZE_TABLOID = '3' 

83 PAPERSIZE_LEDGER = '4' 

84 PAPERSIZE_LEGAL = '5' 

85 PAPERSIZE_STATEMENT = '6' 

86 PAPERSIZE_EXECUTIVE = '7' 

87 PAPERSIZE_A3 = '8' 

88 PAPERSIZE_A4 = '9' 

89 PAPERSIZE_A4_SMALL = '10' 

90 PAPERSIZE_A5 = '11' 

91 

92 # Page orientation 

93 ORIENTATION_PORTRAIT = 'portrait' 

94 ORIENTATION_LANDSCAPE = 'landscape' 

95 

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

97 _WorkbookChild.__init__(self, parent, title) 

98 self._setup() 

99 

100 def _setup(self): 

101 self.row_dimensions = DimensionHolder(worksheet=self, 

102 default_factory=self._add_row) 

103 self.column_dimensions = DimensionHolder(worksheet=self, 

104 default_factory=self._add_column) 

105 self.row_breaks = RowBreak() 

106 self.col_breaks = ColBreak() 

107 self._cells = {} 

108 self._charts = [] 

109 self._images = [] 

110 self._rels = RelationshipList() 

111 self._drawing = None 

112 self._comments = [] 

113 self.merged_cells = MultiCellRange() 

114 self._tables = TableList() 

115 self._pivots = [] 

116 self.data_validations = DataValidationList() 

117 self._hyperlinks = [] 

118 self.sheet_state = 'visible' 

119 self.page_setup = PrintPageSetup(worksheet=self) 

120 self.print_options = PrintOptions() 

121 self._print_rows = None 

122 self._print_cols = None 

123 self._print_area = None 

124 self.page_margins = PageMargins() 

125 self.views = SheetViewList() 

126 self.protection = SheetProtection() 

127 

128 self._current_row = 0 

129 self.auto_filter = AutoFilter() 

130 self.paper_size = None 

131 self.formula_attributes = {} 

132 self.orientation = None 

133 self.conditional_formatting = ConditionalFormattingList() 

134 self.legacy_drawing = None 

135 self.sheet_properties = WorksheetProperties() 

136 self.sheet_format = SheetFormatProperties() 

137 self.scenarios = ScenarioList() 

138 

139 

140 @property 

141 def sheet_view(self): 

142 return self.views.sheetView[0] 

143 

144 

145 @property 

146 def selected_cell(self): 

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

148 

149 

150 @property 

151 def active_cell(self): 

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

153 

154 

155 @property 

156 def page_breaks(self): 

157 return (self.row_breaks, self.col_breaks) # legacy, remove at some point 

158 

159 

160 @property 

161 def show_gridlines(self): 

162 return self.sheet_view.showGridLines 

163 

164 

165 """ To keep compatibility with previous versions""" 

166 @property 

167 def show_summary_below(self): 

168 return self.sheet_properties.outlinePr.summaryBelow 

169 

170 @property 

171 def show_summary_right(self): 

172 return self.sheet_properties.outlinePr.summaryRight 

173 

174 

175 @property 

176 def freeze_panes(self): 

177 if self.sheet_view.pane is not None: 

178 return self.sheet_view.pane.topLeftCell 

179 

180 @freeze_panes.setter 

181 def freeze_panes(self, topLeftCell=None): 

182 if isinstance(topLeftCell, Cell): 

183 topLeftCell = topLeftCell.coordinate 

184 if topLeftCell == 'A1': 

185 topLeftCell = None 

186 

187 if not topLeftCell: 

188 self.sheet_view.pane = None 

189 return 

190 

191 row, column = coordinate_to_tuple(topLeftCell) 

192 

193 view = self.sheet_view 

194 view.pane = Pane(topLeftCell=topLeftCell, 

195 activePane="topRight", 

196 state="frozen") 

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

198 

199 if column > 1: 

200 view.pane.xSplit = column - 1 

201 if row > 1: 

202 view.pane.ySplit = row - 1 

203 view.pane.activePane = 'bottomLeft' 

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

205 if column > 1: 

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

207 view.pane.activePane = 'bottomRight' 

208 

209 if row > 1 and column > 1: 

210 sel = list(view.selection) 

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

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

213 view.selection = sel 

214 

215 

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

217 """ 

218 Returns a cell object based on the given coordinates. 

219 

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

221 

222 Calling `cell` creates cells in memory when they 

223 are first accessed. 

224 

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

226 :type row: int 

227 

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

229 :type column: int 

230 

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

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

233 

234 :rtype: openpyxl.cell.cell.Cell 

235 """ 

236 

237 if row < 1 or column < 1: 

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

239 

240 cell = self._get_cell(row, column) 

241 if value is not None: 

242 cell.value = value 

243 

244 return cell 

245 

246 

247 def _get_cell(self, row, column): 

248 """ 

249 Internal method for getting a cell from a worksheet. 

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

251 """ 

252 if not 0 < row < 1048577: 

253 raise ValueError("Row numbers must be between 1 and 1048576") 

254 coordinate = (row, column) 

255 if not coordinate in self._cells: 

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

257 self._add_cell(cell) 

258 return self._cells[coordinate] 

259 

260 

261 def _add_cell(self, cell): 

262 """ 

263 Internal method for adding cell objects. 

264 """ 

265 column = cell.col_idx 

266 row = cell.row 

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

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

269 

270 

271 def __getitem__(self, key): 

272 """Convenience access by Excel style coordinates 

273 

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

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

276 4:10. 

277 

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

279 

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

281 """ 

282 if isinstance(key, slice): 

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

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

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

286 

287 if isinstance(key, int): 

288 key = str(key 

289 ) 

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

291 

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

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

294 

295 if min_row is None: 

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

297 if min_col == max_col: 

298 cols = cols[0] 

299 return cols 

300 if min_col is None: 

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

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

303 if min_row == max_row: 

304 rows = rows[0] 

305 return rows 

306 if ":" not in key: 

307 return self._get_cell(min_row, min_col) 

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

309 max_row=max_row, max_col=max_col)) 

310 

311 

312 def __setitem__(self, key, value): 

313 self[key].value = value 

314 

315 

316 def __iter__(self): 

317 return self.iter_rows() 

318 

319 

320 def __delitem__(self, key): 

321 row, column = coordinate_to_tuple(key) 

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

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

324 

325 

326 @property 

327 def min_row(self): 

328 """The minimium row index containing data (1-based) 

329 

330 :type: int 

331 """ 

332 min_row = 1 

333 if self._cells: 

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

335 min_row = min(rows) 

336 return min_row 

337 

338 

339 @property 

340 def max_row(self): 

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

342 

343 :type: int 

344 """ 

345 max_row = 1 

346 if self._cells: 

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

348 max_row = max(rows) 

349 return max_row 

350 

351 

352 @property 

353 def min_column(self): 

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

355 

356 :type: int 

357 """ 

358 min_col = 1 

359 if self._cells: 

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

361 min_col = min(cols) 

362 return min_col 

363 

364 

365 @property 

366 def max_column(self): 

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

368 

369 :type: int 

370 """ 

371 max_col = 1 

372 if self._cells: 

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

374 max_col = max(cols) 

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 () 

434 

435 min_col = min_col or 1 

436 min_row = min_row or 1 

437 max_col = max_col or self.max_column 

438 max_row = max_row or self.max_row 

439 

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

441 

442 

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

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

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

446 if values_only: 

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

448 else: 

449 yield tuple(cells) 

450 

451 

452 @property 

453 def rows(self): 

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

455 

456 :type: generator 

457 """ 

458 return self.iter_rows() 

459 

460 

461 @property 

462 def values(self): 

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

464 

465 :type: generator 

466 """ 

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

468 yield row 

469 

470 

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

472 """ 

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

474 using indices of rows and columns. 

475 

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

477 

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

479 

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

481 :type min_col: int 

482 

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

484 :type min_row: int 

485 

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

487 :type max_col: int 

488 

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

490 :type max_row: int 

491 

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

493 :type values_only: bool 

494 

495 :rtype: generator 

496 """ 

497 

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

499 return () 

500 

501 min_col = min_col or 1 

502 min_row = min_row or 1 

503 max_col = max_col or self.max_column 

504 max_row = max_row or self.max_row 

505 

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

507 

508 

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

510 """ 

511 Get cells by column 

512 """ 

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

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

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

516 if values_only: 

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

518 else: 

519 yield tuple(cells) 

520 

521 

522 @property 

523 def columns(self): 

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

525 return self.iter_cols() 

526 

527 

528 def set_printer_settings(self, paper_size, orientation): 

529 """Set printer settings """ 

530 

531 self.page_setup.paperSize = paper_size 

532 self.page_setup.orientation = orientation 

533 

534 

535 def add_data_validation(self, data_validation): 

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

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

538 cell or range of cells it should apply to. 

539 """ 

540 self.data_validations.append(data_validation) 

541 

542 

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

544 """ 

545 Add a chart to the sheet 

546 Optionally provide a cell for the top-left anchor 

547 """ 

548 if anchor is not None: 

549 chart.anchor = anchor 

550 self._charts.append(chart) 

551 

552 

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

554 """ 

555 Add an image to the sheet. 

556 Optionally provide a cell for the top-left anchor 

557 """ 

558 if anchor is not None: 

559 img.anchor = anchor 

560 self._images.append(img) 

561 

562 

563 def add_table(self, table): 

564 """ 

565 Check for duplicate name in definedNames and other worksheet tables 

566 before adding table. 

567 """ 

568 

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

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

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

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

573 self._tables.add(table) 

574 

575 

576 @property 

577 def tables(self): 

578 return self._tables 

579 

580 

581 def add_pivot(self, pivot): 

582 self._pivots.append(pivot) 

583 

584 

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

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

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

588 max_col=end_column, max_row=end_row) 

589 self.merged_cells.add(cr) 

590 self._clean_merge_range(cr) 

591 

592 

593 def _clean_merge_range(self, mcr): 

594 """ 

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

596 and recreate the lost border information. 

597 Borders are then applied 

598 """ 

599 if not isinstance(mcr, MergedCellRange): 

600 mcr = MergedCellRange(self, mcr.coord) 

601 

602 cells = mcr.cells 

603 next(cells) # skip first cell 

604 for row, col in cells: 

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

606 mcr.format() 

607 

608 

609 @property 

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

611 def merged_cell_ranges(self): 

612 """Return a copy of cell ranges""" 

613 return self.merged_cells.ranges[:] 

614 

615 

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

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

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

619 max_col=end_column, max_row=end_row) 

620 

621 if cr.coord not in self.merged_cells: 

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

623 

624 self.merged_cells.remove(cr) 

625 

626 cells = cr.cells 

627 next(cells) # skip first cell 

628 for row, col in cells: 

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

630 

631 

632 def append(self, iterable): 

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

634 

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

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

637 

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

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

640 

641 Usage: 

642 

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

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

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

646 

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

648 

649 """ 

650 row_idx = self._current_row + 1 

651 

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

653 or isgenerator(iterable)): 

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

655 if isinstance(content, Cell): 

656 # compatible with write-only mode 

657 cell = content 

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

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

660 cell.parent = self 

661 cell.column = col_idx 

662 cell.row = row_idx 

663 else: 

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

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

666 

667 elif isinstance(iterable, dict): 

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

669 if isinstance(col_idx, str): 

670 col_idx = column_index_from_string(col_idx) 

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

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

673 

674 else: 

675 self._invalid_row(iterable) 

676 

677 self._current_row = row_idx 

678 

679 

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

681 """ 

682 Move either rows or columns around by the offset 

683 """ 

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

685 row_offset = 0 

686 col_offset = 0 

687 

688 # need to make affected ranges contiguous 

689 if row_or_col == 'row': 

690 cells = self.iter_rows(min_row=min_row) 

691 row_offset = offset 

692 key = 0 

693 else: 

694 cells = self.iter_cols(min_col=min_col) 

695 col_offset = offset 

696 key = 1 

697 cells = list(cells) 

698 

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

700 if min_row and row < min_row: 

701 continue 

702 elif min_col and column < min_col: 

703 continue 

704 

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

706 

707 

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

709 """ 

710 Insert row or rows before row==idx 

711 """ 

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

713 self._current_row = self.max_row 

714 

715 

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

717 """ 

718 Insert column or columns before col==idx 

719 """ 

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

721 

722 

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

724 """ 

725 Delete row or rows from row==idx 

726 """ 

727 

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

729 

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

731 

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

733 min_col = self.min_column 

734 max_col = self.max_column + 1 

735 for row in remainder: 

736 for col in range(min_col, max_col): 

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

738 del self._cells[row, col] 

739 self._current_row = self.max_row 

740 if not self._cells: 

741 self._current_row = 0 

742 

743 

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

745 """ 

746 Delete column or columns from col==idx 

747 """ 

748 

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

750 

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

752 

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

754 min_row = self.min_row 

755 max_row = self.max_row + 1 

756 for col in remainder: 

757 for row in range(min_row, max_row): 

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

759 del self._cells[row, col] 

760 

761 

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

763 """ 

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

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

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

767 Existing cells will be overwritten. 

768 Formulae and references will not be updated. 

769 """ 

770 if isinstance(cell_range, str): 

771 cell_range = CellRange(cell_range) 

772 if not isinstance(cell_range, CellRange): 

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

774 if not rows and not cols: 

775 return 

776 

777 down = rows > 0 

778 right = cols > 0 

779 

780 if rows: 

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

782 else: 

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

784 

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

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

787 

788 # rebase moved range 

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

790 

791 

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

793 """ 

794 Move a cell from one place to another. 

795 Delete at old index 

796 Rebase coordinate 

797 """ 

798 cell = self._get_cell(row, column) 

799 new_row = cell.row + row_offset 

800 new_col = cell.column + col_offset 

801 self._cells[new_row, new_col] = cell 

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

803 cell.row = new_row 

804 cell.column = new_col 

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

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

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

808 

809 

810 def _invalid_row(self, iterable): 

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

812 type(iterable)) 

813 ) 

814 

815 

816 def _add_column(self): 

817 """Dimension factory for column information""" 

818 

819 return ColumnDimension(self) 

820 

821 def _add_row(self): 

822 """Dimension factory for row information""" 

823 

824 return RowDimension(self) 

825 

826 

827 @property 

828 def print_title_rows(self): 

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

830 if self._print_rows: 

831 return self._print_rows 

832 

833 

834 @print_title_rows.setter 

835 def print_title_rows(self, rows): 

836 """ 

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

838 format `1:3` 

839 """ 

840 if rows is not None: 

841 if not ROW_RANGE_RE.match(rows): 

842 raise ValueError("Print title rows must be the form 1:3") 

843 self._print_rows = rows 

844 

845 

846 @property 

847 def print_title_cols(self): 

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

849 if self._print_cols: 

850 return self._print_cols 

851 

852 

853 @print_title_cols.setter 

854 def print_title_cols(self, cols): 

855 """ 

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

857 format ``A:C` 

858 """ 

859 if cols is not None: 

860 if not COL_RANGE_RE.match(cols): 

861 raise ValueError("Print title cols must be the form C:D") 

862 self._print_cols = cols 

863 

864 

865 @property 

866 def print_titles(self): 

867 if self.print_title_cols and self.print_title_rows: 

868 return ",".join([self.print_title_rows, self.print_title_cols]) 

869 else: 

870 return self.print_title_rows or self.print_title_cols 

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. 

878 """ 

879 return self._print_area 

880 

881 

882 @print_area.setter 

883 def print_area(self, value): 

884 """ 

885 Range of cells in the form A1:D4 or list of ranges 

886 """ 

887 if isinstance(value, str): 

888 value = [value] 

889 

890 self._print_area = [absolute_coordinate(v) for v in value] 

891 

892 

893def _gutter(idx, offset, max_val): 

894 """ 

895 When deleting rows and columns are deleted we rely on overwriting. 

896 This may not be the case for a large offset on small set of cells: 

897 range(cells_to_delete) > range(cell_to_be_moved) 

898 """ 

899 gutter = range(max(max_val+1-offset, idx), min(idx+offset, max_val)+1) 

900 return gutter