1# Copyright (c) 2010-2019 openpyxl 2 3"""Worksheet is the 2nd-level container in Excel.""" 4 5 6# Python stdlib imports 7from itertools import islice, product, chain 8from operator import itemgetter 9from inspect import isgenerator 10 11# compatibility imports 12from openpyxl.compat import ( 13 deprecated, 14) 15 16try: 17 range = xrange 18except NameError: 19 pass 20 21# package imports 22from openpyxl.utils import ( 23 column_index_from_string, 24 get_column_letter, 25 range_boundaries, 26 coordinate_to_tuple, 27 absolute_coordinate, 28) 29from openpyxl.cell import Cell, MergedCell 30from openpyxl.formatting.formatting import ConditionalFormattingList 31from openpyxl.packaging.relationship import RelationshipList 32from openpyxl.workbook.child import _WorkbookChild 33from openpyxl.workbook.defined_name import COL_RANGE_RE, ROW_RANGE_RE 34from openpyxl.formula.translate import Translator 35 36from .datavalidation import DataValidationList 37from .page import ( 38 PrintPageSetup, 39 PageMargins, 40 PrintOptions, 41) 42from .dimensions import ( 43 ColumnDimension, 44 RowDimension, 45 DimensionHolder, 46 SheetFormatProperties, 47) 48from .protection import SheetProtection 49from .filters import AutoFilter 50from .views import ( 51 Pane, 52 Selection, 53 SheetViewList, 54) 55from .cell_range import MultiCellRange, CellRange 56from .merge import MergedCellRange 57from .properties import WorksheetProperties 58from .pagebreak import RowBreak, ColBreak 59from .scenario import ScenarioList 60 61 62class Worksheet(_WorkbookChild): 63 """Represents a worksheet. 64 65 Do not create worksheets yourself, 66 use :func:`openpyxl.workbook.Workbook.create_sheet` instead 67 68 """ 69 70 _rel_type = "worksheet" 71 _path = "/xl/worksheets/sheet{0}.xml" 72 mime_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" 73 74 BREAK_NONE = 0 75 BREAK_ROW = 1 76 BREAK_COLUMN = 2 77 78 SHEETSTATE_VISIBLE = 'visible' 79 SHEETSTATE_HIDDEN = 'hidden' 80 SHEETSTATE_VERYHIDDEN = 'veryHidden' 81 82 # Paper size 83 PAPERSIZE_LETTER = '1' 84 PAPERSIZE_LETTER_SMALL = '2' 85 PAPERSIZE_TABLOID = '3' 86 PAPERSIZE_LEDGER = '4' 87 PAPERSIZE_LEGAL = '5' 88 PAPERSIZE_STATEMENT = '6' 89 PAPERSIZE_EXECUTIVE = '7' 90 PAPERSIZE_A3 = '8' 91 PAPERSIZE_A4 = '9' 92 PAPERSIZE_A4_SMALL = '10' 93 PAPERSIZE_A5 = '11' 94 95 # Page orientation 96 ORIENTATION_PORTRAIT = 'portrait' 97 ORIENTATION_LANDSCAPE = 'landscape' 98 99 def __init__(self, parent, title=None): 100 _WorkbookChild.__init__(self, parent, title) 101 self._setup() 102 103 def _setup(self): 104 self.row_dimensions = DimensionHolder(worksheet=self, 105 default_factory=self._add_row) 106 self.column_dimensions = DimensionHolder(worksheet=self, 107 default_factory=self._add_column) 108 self.row_breaks = RowBreak() 109 self.col_breaks = ColBreak() 110 self.page_breaks = (self.row_breaks, self.col_breaks) 111 self._cells = {} 112 self._charts = [] 113 self._images = [] 114 self._rels = RelationshipList() 115 self._drawing = None 116 self._comments = [] 117 self.merged_cells = MultiCellRange() 118 self._tables = [] 119 self._pivots = [] 120 self.data_validations = DataValidationList() 121 self._hyperlinks = [] 122 self.sheet_state = 'visible' 123 self.page_setup = PrintPageSetup(worksheet=self) 124 self.print_options = PrintOptions() 125 self._print_rows = None 126 self._print_cols = None 127 self._print_area = None 128 self.page_margins = PageMargins() 129 self.views = SheetViewList() 130 self.protection = SheetProtection() 131 132 self._current_row = 0 133 self.auto_filter = AutoFilter() 134 self.paper_size = None 135 self.formula_attributes = {} 136 self.orientation = None 137 self.conditional_formatting = ConditionalFormattingList() 138 self.legacy_drawing = None 139 self.sheet_properties = WorksheetProperties() 140 self.sheet_format = SheetFormatProperties() 141 self.scenarios = ScenarioList() 142 143 144 @property 145 def sheet_view(self): 146 return self.views.sheetView[0] 147 148 149 @property 150 def selected_cell(self): 151 return self.sheet_view.selection[0].sqref 152 153 154 @property 155 def active_cell(self): 156 return self.sheet_view.selection[0].activeCell 157 158 @property 159 def show_gridlines(self): 160 return self.sheet_view.showGridLines 161 162 163 """ To keep compatibility with previous versions""" 164 @property 165 def show_summary_below(self): 166 return self.sheet_properties.outlinePr.summaryBelow 167 168 @property 169 def show_summary_right(self): 170 return self.sheet_properties.outlinePr.summaryRight 171 172 173 @property 174 def freeze_panes(self): 175 if self.sheet_view.pane is not None: 176 return self.sheet_view.pane.topLeftCell 177 178 @freeze_panes.setter 179 def freeze_panes(self, topLeftCell=None): 180 if isinstance(topLeftCell, Cell): 181 topLeftCell = topLeftCell.coordinate 182 if topLeftCell == 'A1': 183 topLeftCell = None 184 185 if not topLeftCell: 186 self.sheet_view.pane = None 187 return 188 189 row, column = coordinate_to_tuple(topLeftCell) 190 191 view = self.sheet_view 192 view.pane = Pane(topLeftCell=topLeftCell, 193 activePane="topRight", 194 state="frozen") 195 view.selection[0].pane = "topRight" 196 197 if column > 1: 198 view.pane.xSplit = column - 1 199 if row > 1: 200 view.pane.ySplit = row - 1 201 view.pane.activePane = 'bottomLeft' 202 view.selection[0].pane = "bottomLeft" 203 if column > 1: 204 view.selection[0].pane = "bottomRight" 205 view.pane.activePane = 'bottomRight' 206 207 if row > 1 and column > 1: 208 sel = list(view.selection) 209 sel.insert(0, Selection(pane="topRight", activeCell=None, sqref=None)) 210 sel.insert(1, Selection(pane="bottomLeft", activeCell=None, sqref=None)) 211 view.selection = sel 212 213 214 def cell(self, row, column, value=None): 215 """ 216 Returns a cell object based on the given coordinates. 217 218 Usage: cell(row=15, column=1, value=5) 219 220 Calling `cell` creates cells in memory when they 221 are first accessed. 222 223 :param row: row index of the cell (e.g. 4) 224 :type row: int 225 226 :param column: column index of the cell (e.g. 3) 227 :type column: int 228 229 :param value: value of the cell (e.g. 5) 230 :type value: numeric or time or string or bool or none 231 232 :rtype: openpyxl.cell.cell.Cell 233 """ 234 235 if row < 1 or column < 1: 236 raise ValueError("Row or column values must be at least 1") 237 238 cell = self._get_cell(row, column) 239 if value is not None: 240 cell.value = value 241 242 return cell 243 244 245 def _get_cell(self, row, column): 246 """ 247 Internal method for getting a cell from a worksheet. 248 Will create a new cell if one doesn't already exist. 249 """ 250 coordinate = (row, column) 251 if not coordinate in self._cells: 252 cell = Cell(self, row=row, column=column) 253 self._add_cell(cell) 254 return self._cells[coordinate] 255 256 257 def _add_cell(self, cell): 258 """ 259 Internal method for adding cell objects. 260 """ 261 column = cell.col_idx 262 row = cell.row 263 self._current_row = max(row, self._current_row) 264 self._cells[(row, column)] = cell 265 266 267 def __getitem__(self, key): 268 """Convenience access by Excel style coordinates 269 270 The key can be a single cell coordinate 'A1', a range of cells 'A1:D25', 271 individual rows or columns 'A', 4 or ranges of rows or columns 'A:D', 272 4:10. 273 274 Single cells will always be created if they do not exist. 275 276 Returns either a single cell or a tuple of rows or columns. 277 """ 278 if isinstance(key, slice): 279 if not all([key.start, key.stop]): 280 raise IndexError("{0} is not a valid coordinate or range".format(key)) 281 key = "{0}:{1}".format(key.start, key.stop) 282 283 if isinstance(key, int): 284 key = str(key 285 ) 286 min_col, min_row, max_col, max_row = range_boundaries(key) 287 288 if not any([min_col, min_row, max_col, max_row]): 289 raise IndexError("{0} is not a valid coordinate or range".format(key)) 290 291 if not min_row: 292 cols = tuple(self.iter_cols(min_col, max_col)) 293 if min_col == max_col: 294 cols = cols[0] 295 return cols 296 if not min_col: 297 rows = tuple(self.iter_rows(min_col=min_col, min_row=min_row, 298 max_col=self.max_column, max_row=max_row)) 299 if min_row == max_row: 300 rows = rows[0] 301 return rows 302 if ":" not in key: 303 return self._get_cell(min_row, min_col) 304 return tuple(self.iter_rows(min_row=min_row, min_col=min_col, 305 max_row=max_row, max_col=max_col)) 306 307 308 def __setitem__(self, key, value): 309 self[key].value = value 310 311 312 def __iter__(self): 313 return self.iter_rows() 314 315 316 def __delitem__(self, key): 317 row, column = coordinate_to_tuple(key) 318 if (row, column) in self._cells: 319 del self._cells[(row, column)] 320 321 322 @property 323 def min_row(self): 324 """The minimium row index containing data (1-based) 325 326 :type: int 327 """ 328 min_row = 1 329 if self._cells: 330 rows = set(c[0] for c in self._cells) 331 min_row = min(rows) 332 return min_row 333 334 335 @property 336 def max_row(self): 337 """The maximum row index containing data (1-based) 338 339 :type: int 340 """ 341 max_row = 1 342 if self._cells: 343 rows = set(c[0] for c in self._cells) 344 max_row = max(rows) 345 return max_row 346 347 348 @property 349 def min_column(self): 350 """The minimum column index containing data (1-based) 351 352 :type: int 353 """ 354 min_col = 1 355 if self._cells: 356 cols = set(c[1] for c in self._cells) 357 min_col = min(cols) 358 return min_col 359 360 361 @property 362 def max_column(self): 363 """The maximum column index containing data (1-based) 364 365 :type: int 366 """ 367 max_col = 1 368 if self._cells: 369 cols = set(c[1] for c in self._cells) 370 max_col = max(cols) 371 return max_col 372 373 374 def calculate_dimension(self): 375 """Return the minimum bounding range for all cells containing data (ex. 'A1:M24') 376 377 :rtype: string 378 """ 379 if self._cells: 380 rows = set() 381 cols = set() 382 for row, col in self._cells: 383 rows.add(row) 384 cols.add(col) 385 max_row = max(rows) 386 max_col = max(cols) 387 min_col = min(cols) 388 min_row = min(rows) 389 else: 390 return "A1:A1" 391 392 return '%s%d:%s%d' % ( 393 get_column_letter(min_col), min_row, 394 get_column_letter(max_col), max_row 395 ) 396 397 398 @property 399 def dimensions(self): 400 """Returns the result of :func:`calculate_dimension`""" 401 return self.calculate_dimension() 402 403 404 def iter_rows(self, min_row=None, max_row=None, min_col=None, max_col=None, values_only=False): 405 """ 406 Produces cells from the worksheet, by row. Specify the iteration range 407 using indices of rows and columns. 408 409 If no indices are specified the range starts at A1. 410 411 If no cells are in the worksheet an empty tuple will be returned. 412 413 :param min_col: smallest column index (1-based index) 414 :type min_col: int 415 416 :param min_row: smallest row index (1-based index) 417 :type min_row: int 418 419 :param max_col: largest column index (1-based index) 420 :type max_col: int 421 422 :param max_row: largest row index (1-based index) 423 :type max_row: int 424 425 :param values_only: whether only cell values should be returned 426 :type values_only: bool 427 428 :rtype: generator 429 """ 430 431 if self._current_row == 0 and not any([min_col, min_row, max_col, max_row ]): 432 return () 433 434 min_col = min_col or 1 435 min_row = min_row or 1 436 max_col = max_col or self.max_column 437 max_row = max_row or self.max_row 438 439 return self._cells_by_row(min_col, min_row, max_col, max_row, values_only) 440 441 442 def _cells_by_row(self, min_col, min_row, max_col, max_row, values_only=False): 443 for row in range(min_row, max_row + 1): 444 cells = (self.cell(row=row, column=column) for column in range(min_col, max_col + 1)) 445 if values_only: 446 yield tuple(cell.value for cell in cells) 447 else: 448 yield tuple(cells) 449 450 451 @property 452 def rows(self): 453 """Produces all cells in the worksheet, by row (see :func:`iter_rows`) 454 455 :type: generator 456 """ 457 return self.iter_rows() 458 459 460 @property 461 def values(self): 462 """Produces all cell values in the worksheet, by row 463 464 :type: generator 465 """ 466 for row in self.iter_rows(values_only=True): 467 yield row 468 469 470 def iter_cols(self, min_col=None, max_col=None, min_row=None, max_row=None, values_only=False): 471 """ 472 Produces cells from the worksheet, by column. Specify the iteration range 473 using indices of rows and columns. 474 475 If no indices are specified the range starts at A1. 476 477 If no cells are in the worksheet an empty tuple will be returned. 478 479 :param min_col: smallest column index (1-based index) 480 :type min_col: int 481 482 :param min_row: smallest row index (1-based index) 483 :type min_row: int 484 485 :param max_col: largest column index (1-based index) 486 :type max_col: int 487 488 :param max_row: largest row index (1-based index) 489 :type max_row: int 490 491 :param values_only: whether only cell values should be returned 492 :type values_only: bool 493 494 :rtype: generator 495 """ 496 497 if self._current_row == 0 and not any([min_col, min_row, max_col, max_row]): 498 return () 499 500 min_col = min_col or 1 501 min_row = min_row or 1 502 max_col = max_col or self.max_column 503 max_row = max_row or self.max_row 504 505 return self._cells_by_col(min_col, min_row, max_col, max_row, values_only) 506 507 508 def _cells_by_col(self, min_col, min_row, max_col, max_row, values_only=False): 509 """ 510 Get cells by column 511 """ 512 for column in range(min_col, max_col+1): 513 cells = (self.cell(row=row, column=column) 514 for row in range(min_row, max_row+1)) 515 if values_only: 516 yield tuple(cell.value for cell in cells) 517 else: 518 yield tuple(cells) 519 520 521 @property 522 def columns(self): 523 """Produces all cells in the worksheet, by column (see :func:`iter_cols`)""" 524 return self.iter_cols() 525 526 527 def set_printer_settings(self, paper_size, orientation): 528 """Set printer settings """ 529 530 self.page_setup.paperSize = paper_size 531 self.page_setup.orientation = orientation 532 533 534 def add_data_validation(self, data_validation): 535 """ Add a data-validation object to the sheet. The data-validation 536 object defines the type of data-validation to be applied and the 537 cell or range of cells it should apply to. 538 """ 539 self.data_validations.append(data_validation) 540 541 542 def add_chart(self, chart, anchor=None): 543 """ 544 Add a chart to the sheet 545 Optionally provide a cell for the top-left anchor 546 """ 547 if anchor is not None: 548 chart.anchor = anchor 549 self._charts.append(chart) 550 551 552 def add_image(self, img, anchor=None): 553 """ 554 Add an image to the sheet. 555 Optionally provide a cell for the top-left anchor 556 """ 557 if anchor is not None: 558 img.anchor = anchor 559 self._images.append(img) 560 561 562 def add_table(self, table): 563 self._tables.append(table) 564 565 566 def add_pivot(self, pivot): 567 self._pivots.append(pivot) 568 569 570 def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None): 571 """ Set merge on a cell range. Range is a cell range (e.g. A1:E1) """ 572 cr = CellRange(range_string=range_string, min_col=start_column, min_row=start_row, 573 max_col=end_column, max_row=end_row) 574 self.merged_cells.add(cr) 575 self._clean_merge_range(cr) 576 577 578 def _clean_merge_range(self, cr): 579 """ 580 Remove all but the top left-cell from a range of merged cells 581 and recreate the lost border information. 582 Borders are then applied 583 """ 584 mcr = MergedCellRange(self, cr.coord) 585 cells = chain.from_iterable(mcr.rows) 586 next(cells) # skip first cell 587 588 for row, col in cells: 589 self._cells[row, col] = MergedCell(self, row, col) 590 mcr.format() 591 592 593 @property 594 @deprecated("Use ws.merged_cells.ranges") 595 def merged_cell_ranges(self): 596 """Return a copy of cell ranges""" 597 return self.merged_cells.ranges[:] 598 599 600 def unmerge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None): 601 """ Remove merge on a cell range. Range is a cell range (e.g. A1:E1) """ 602 cr = CellRange(range_string=range_string, min_col=start_column, min_row=start_row, 603 max_col=end_column, max_row=end_row) 604 605 if cr.coord not in self.merged_cells: 606 raise ValueError("Cell range {0} is not merged".format(cr.coord)) 607 608 self.merged_cells.remove(cr) 609 610 cells = chain.from_iterable(cr.rows) 611 next(cells) # skip first cell 612 613 for row, col in cells: 614 del self._cells[(row, col)] 615 616 617 def append(self, iterable): 618 """Appends a group of values at the bottom of the current sheet. 619 620 * If it's a list: all values are added in order, starting from the first column 621 * If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters) 622 623 :param iterable: list, range or generator, or dict containing values to append 624 :type iterable: list|tuple|range|generator or dict 625 626 Usage: 627 628 * append(['This is A1', 'This is B1', 'This is C1']) 629 * **or** append({'A' : 'This is A1', 'C' : 'This is C1'}) 630 * **or** append({1 : 'This is A1', 3 : 'This is C1'}) 631 632 :raise: TypeError when iterable is neither a list/tuple nor a dict 633 634 """ 635 row_idx = self._current_row + 1 636 637 if (isinstance(iterable, (list, tuple, range)) 638 or isgenerator(iterable)): 639 for col_idx, content in enumerate(iterable, 1): 640 if isinstance(content, Cell): 641 # compatible with write-only mode 642 cell = content 643 if cell.parent and cell.parent != self: 644 raise ValueError("Cells cannot be copied from other worksheets") 645 cell.parent = self 646 cell.column = col_idx 647 cell.row = row_idx 648 else: 649 cell = Cell(self, row=row_idx, column=col_idx, value=content) 650 self._cells[(row_idx, col_idx)] = cell 651 652 elif isinstance(iterable, dict): 653 for col_idx, content in iterable.items(): 654 if isinstance(col_idx, str): 655 col_idx = column_index_from_string(col_idx) 656 cell = Cell(self, row=row_idx, column=col_idx, value=content) 657 self._cells[(row_idx, col_idx)] = cell 658 659 else: 660 self._invalid_row(iterable) 661 662 self._current_row = row_idx 663 664 665 def _move_cells(self, min_row=None, min_col=None, offset=0, row_or_col="row"): 666 """ 667 Move either rows or columns around by the offset 668 """ 669 reverse = offset > 0 # start at the end if inserting 670 row_offset = 0 671 col_offset = 0 672 673 # need to make affected ranges contiguous 674 if row_or_col == 'row': 675 cells = self.iter_rows(min_row=min_row) 676 row_offset = offset 677 key = 0 678 else: 679 cells = self.iter_cols(min_col=min_col) 680 col_offset = offset 681 key = 1 682 cells = list(cells) 683 684 for row, column in sorted(self._cells, key=itemgetter(key), reverse=reverse): 685 if min_row and row < min_row: 686 continue 687 elif min_col and column < min_col: 688 continue 689 690 self._move_cell(row, column, row_offset, col_offset) 691 692 693 def insert_rows(self, idx, amount=1): 694 """ 695 Insert row or rows before row==idx 696 """ 697 self._move_cells(min_row=idx, offset=amount, row_or_col="row") 698 self._current_row = self.max_row 699 700 701 def insert_cols(self, idx, amount=1): 702 """ 703 Insert column or columns before col==idx 704 """ 705 self._move_cells(min_col=idx, offset=amount, row_or_col="column") 706 707 708 def delete_rows(self, idx, amount=1): 709 """ 710 Delete row or rows from row==idx 711 """ 712 713 remainder = _gutter(idx, amount, self.max_row) 714 715 self._move_cells(min_row=idx+amount, offset=-amount, row_or_col="row") 716 717 # calculating min and max col is an expensive operation, do it only once 718 min_col = self.min_column 719 max_col = self.max_column + 1 720 for row in remainder: 721 for col in range(min_col, max_col): 722 if (row, col) in self._cells: 723 del self._cells[row, col] 724 self._current_row = self.max_row 725 if not self._cells: 726 self._current_row = 0 727 728 729 def delete_cols(self, idx, amount=1): 730 """ 731 Delete column or columns from col==idx 732 """ 733 734 remainder = _gutter(idx, amount, self.max_column) 735 736 self._move_cells(min_col=idx+amount, offset=-amount, row_or_col="column") 737 738 # calculating min and max row is an expensive operation, do it only once 739 min_row = self.min_row 740 max_row = self.max_row + 1 741 for col in remainder: 742 for row in range(min_row, max_row): 743 if (row, col) in self._cells: 744 del self._cells[row, col] 745 746 747 def move_range(self, cell_range, rows=0, cols=0, translate=False): 748 """ 749 Move a cell range by the number of rows and/or columns: 750 down if rows > 0 and up if rows < 0 751 right if cols > 0 and left if cols < 0 752 Existing cells will be overwritten. 753 Formulae and references will not be updated. 754 """ 755 if isinstance(cell_range, str): 756 cell_range = CellRange(cell_range) 757 if not isinstance(cell_range, CellRange): 758 raise ValueError("Only CellRange objects can be moved") 759 if not rows and not cols: 760 return 761 762 down = rows > 0 763 right = cols > 0 764 765 if rows: 766 cells = sorted(cell_range.rows, reverse=down) 767 else: 768 cells = sorted(cell_range.cols, reverse=right) 769 770 for row, col in chain.from_iterable(cells): 771 self._move_cell(row, col, rows, cols, translate) 772 773 # rebase moved range 774 cell_range.shift(row_shift=rows, col_shift=cols) 775 776 777 def _move_cell(self, row, column, row_offset, col_offset, translate=False): 778 """ 779 Move a cell from one place to another. 780 Delete at old index 781 Rebase coordinate 782 """ 783 cell = self._get_cell(row, column) 784 new_row = cell.row + row_offset 785 new_col = cell.column + col_offset 786 self._cells[new_row, new_col] = cell 787 del self._cells[(cell.row, cell.column)] 788 cell.row = new_row 789 cell.column = new_col 790 if translate and cell.data_type == "f": 791 t = Translator(cell.value, cell.coordinate) 792 cell.value = t.translate_formula(row_delta=row_offset, col_delta=col_offset) 793 794 795 def _invalid_row(self, iterable): 796 raise TypeError('Value must be a list, tuple, range or generator, or a dict. Supplied value is {0}'.format( 797 type(iterable)) 798 ) 799 800 801 def _add_column(self): 802 """Dimension factory for column information""" 803 804 return ColumnDimension(self) 805 806 def _add_row(self): 807 """Dimension factory for row information""" 808 809 return RowDimension(self) 810 811 812 @property 813 def print_title_rows(self): 814 """Rows to be printed at the top of every page (ex: '1:3')""" 815 if self._print_rows: 816 return self._print_rows 817 818 819 @print_title_rows.setter 820 def print_title_rows(self, rows): 821 """ 822 Set rows to be printed on the top of every page 823 format `1:3` 824 """ 825 if rows is not None: 826 if not ROW_RANGE_RE.match(rows): 827 raise ValueError("Print title rows must be the form 1:3") 828 self._print_rows = rows 829 830 831 @property 832 def print_title_cols(self): 833 """Columns to be printed at the left side of every page (ex: 'A:C')""" 834 if self._print_cols: 835 return self._print_cols 836 837 838 @print_title_cols.setter 839 def print_title_cols(self, cols): 840 """ 841 Set cols to be printed on the left of every page 842 format ``A:C` 843 """ 844 if cols is not None: 845 if not COL_RANGE_RE.match(cols): 846 raise ValueError("Print title cols must be the form C:D") 847 self._print_cols = cols 848 849 850 @property 851 def print_titles(self): 852 if self.print_title_cols and self.print_title_rows: 853 return ",".join([self.print_title_rows, self.print_title_cols]) 854 else: 855 return self.print_title_rows or self.print_title_cols 856 857 858 @property 859 def print_area(self): 860 """ 861 The print area for the worksheet, or None if not set. To set, supply a range 862 like 'A1:D4' or a list of ranges. 863 """ 864 return self._print_area 865 866 867 @print_area.setter 868 def print_area(self, value): 869 """ 870 Range of cells in the form A1:D4 or list of ranges 871 """ 872 if isinstance(value, str): 873 value = [value] 874 875 self._print_area = [absolute_coordinate(v) for v in value] 876 877 878def _gutter(idx, offset, max_val): 879 """ 880 When deleting rows and columns are deleted we rely on overwriting. 881 This may not be the case for a large offset on small set of cells: 882 range(cells_to_delete) > range(cell_to_be_moved) 883 """ 884 gutter = range(max(max_val+1-offset, idx), min(idx+offset, max_val)+1) 885 return gutter 886