Source code

Download source archive ↓

src/openpyxl/worksheet/worksheet.py

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):
878        """
879        The print area for the worksheet, or None if not set. To set, supply a range
880        like 'A1:D4' or a list of ranges.
881        """
882        self._print_area.title = self.title
883        return str(self._print_area)
884
885
886    @print_area.setter
887    def print_area(self, value):
888        """
889        Range of cells in the form A1:D4 or list of ranges. Print area can be cleared
890        by passing `None` or an empty list
891        """
892        if not value:
893            self._print_area = PrintArea()
894        elif isinstance(value, str):
895            self._print_area = PrintArea.from_string(value)
896        elif hasattr(value, "__iter__"):
897            self._print_area = PrintArea.from_string(",".join(value))
898
899
900def _gutter(idx, offset, max_val):
901    """
902    When deleting rows and columns are deleted we rely on overwriting.
903    This may not be the case for a large offset on small set of cells:
904    range(cells_to_delete) > range(cell_to_be_moved)
905    """
906    gutter = range(max(max_val+1-offset, idx), min(idx+offset, max_val)+1)
907    return gutter