Source code

Download source archive ↓

src/openpyxl/cell/cell.py

1# Copyright (c) 2010-2024 openpyxl
2
3"""Manage individual cells in a spreadsheet.
4
5The Cell class is required to know its value and type, display options,
6and any other features of an Excel cell.  Utilities for referencing
7cells using Excel's 'A1' column/row nomenclature are also provided.
8
9"""
10
11__docformat__ = "restructuredtext en"
12
13# Python stdlib imports
14from copy import copy
15import datetime
16import re
17
18
19from openpyxl.compat import (
20    NUMERIC_TYPES,
21)
22
23from openpyxl.utils.exceptions import IllegalCharacterError
24
25from openpyxl.utils import get_column_letter
26from openpyxl.styles import numbers, is_date_format
27from openpyxl.styles.styleable import StyleableObject
28from openpyxl.worksheet.hyperlink import Hyperlink
29from openpyxl.worksheet.formula import DataTableFormula, ArrayFormula
30from openpyxl.cell.rich_text import CellRichText
31
32# constants
33
34TIME_TYPES = (datetime.datetime, datetime.date, datetime.time, datetime.timedelta)
35TIME_FORMATS = {
36    datetime.datetime:numbers.FORMAT_DATE_DATETIME,
37    datetime.date:numbers.FORMAT_DATE_YYYYMMDD2,
38    datetime.time:numbers.FORMAT_DATE_TIME6,
39    datetime.timedelta:numbers.FORMAT_DATE_TIMEDELTA,
40                }
41
42STRING_TYPES = (str, bytes, CellRichText)
43KNOWN_TYPES = NUMERIC_TYPES + TIME_TYPES + STRING_TYPES + (bool, type(None))
44
45ILLEGAL_CHARACTERS_RE = re.compile(r'[\000-\010]|[\013-\014]|[\016-\037]')
46ERROR_CODES = ('#NULL!', '#DIV/0!', '#VALUE!', '#REF!', '#NAME?', '#NUM!',
47               '#N/A')
48
49TYPE_STRING = 's'
50TYPE_FORMULA = 'f'
51TYPE_NUMERIC = 'n'
52TYPE_BOOL = 'b'
53TYPE_NULL = 'n'
54TYPE_INLINE = 'inlineStr'
55TYPE_ERROR = 'e'
56TYPE_FORMULA_CACHE_STRING = 'str'
57
58VALID_TYPES = (TYPE_STRING, TYPE_FORMULA, TYPE_NUMERIC, TYPE_BOOL,
59               TYPE_NULL, TYPE_INLINE, TYPE_ERROR, TYPE_FORMULA_CACHE_STRING)
60
61
62_TYPES = {int:'n', float:'n', str:'s', bool:'b'}
63
64
65def get_type(t, value):
66    if isinstance(value, NUMERIC_TYPES):
67        dt = 'n'
68    elif isinstance(value, STRING_TYPES):
69        dt = 's'
70    elif isinstance(value, TIME_TYPES):
71        dt = 'd'
72    elif isinstance(value, (DataTableFormula, ArrayFormula)):
73        dt = 'f'
74    else:
75        return
76    _TYPES[t] = dt
77    return dt
78
79
80def get_time_format(t):
81    value = TIME_FORMATS.get(t)
82    if value:
83        return value
84    for base in t.mro()[1:]:
85        value = TIME_FORMATS.get(base)
86        if value:
87            TIME_FORMATS[t] = value
88            return value
89    raise ValueError("Could not get time format for {0!r}".format(value))
90
91
92class Cell(StyleableObject):
93    """Describes cell associated properties.
94
95    Properties of interest include style, type, value, and address.
96
97    """
98    __slots__ = (
99        'row',
100        'column',
101        '_value',
102        'data_type',
103        'parent',
104        '_hyperlink',
105        '_comment',
106                 )
107
108    def __init__(self, worksheet, row=None, column=None, value=None, style_array=None):
109        super().__init__(worksheet, style_array)
110        self.row = row
111        """Row number of this cell (1-based)"""
112        self.column = column
113        """Column number of this cell (1-based)"""
114        # _value is the stored value, while value is the displayed value
115        self._value = None
116        self._hyperlink = None
117        self.data_type = 'n'
118        if value is not None:
119            self.value = value
120        self._comment = None
121
122
123    @property
124    def coordinate(self):
125        """This cell's coordinate (ex. 'A5')"""
126        col = get_column_letter(self.column)
127        return f"{col}{self.row}"
128
129
130    @property
131    def col_idx(self):
132        """The numerical index of the column"""
133        return self.column
134
135
136    @property
137    def column_letter(self):
138        return get_column_letter(self.column)
139
140
141    @property
142    def encoding(self):
143        return self.parent.encoding
144
145    @property
146    def base_date(self):
147        return self.parent.parent.epoch
148
149
150    def __repr__(self):
151        return "<Cell {0!r}.{1}>".format(self.parent.title, self.coordinate)
152
153    def check_string(self, value):
154        """Check string coding, length, and line break character"""
155        if value is None:
156            return
157        # convert to str string
158        if not isinstance(value, str):
159            value = str(value, self.encoding)
160        value = str(value)
161        # string must never be longer than 32,767 characters
162        # truncate if necessary
163        value = value[:32767]
164        if next(ILLEGAL_CHARACTERS_RE.finditer(value), None):
165            raise IllegalCharacterError(f"{value} cannot be used in worksheets.")
166        return value
167
168    def check_error(self, value):
169        """Tries to convert Error" else N/A"""
170        try:
171            return str(value)
172        except UnicodeDecodeError:
173            return u'#N/A'
174
175
176    def _bind_value(self, value):
177        """Given a value, infer the correct data type"""
178
179        self.data_type = "n"
180        t = type(value)
181        try:
182            dt = _TYPES[t]
183        except KeyError:
184            dt = get_type(t, value)
185
186        if dt is None and value is not None:
187            raise ValueError("Cannot convert {0!r} to Excel".format(value))
188
189        if dt:
190            self.data_type = dt
191
192        if dt == 'd':
193            if not is_date_format(self.number_format):
194                self.number_format = get_time_format(t)
195
196        elif dt == "s" and not isinstance(value, CellRichText):
197            value = self.check_string(value)
198            if len(value) > 1 and value.startswith("="):
199                self.data_type = 'f'
200            elif value in ERROR_CODES:
201                self.data_type = 'e'
202
203        self._value = value
204
205
206    @property
207    def value(self):
208        """Get or set the value held in the cell.
209
210        :type: depends on the value (string, float, int or
211            :class:`datetime.datetime`)
212        """
213        return self._value
214
215    @value.setter
216    def value(self, value):
217        """Set the value and infer type and display options."""
218        self._bind_value(value)
219
220    @property
221    def internal_value(self):
222        """Always returns the value for excel."""
223        return self._value
224
225    @property
226    def hyperlink(self):
227        """Return the hyperlink target or an empty string"""
228        return self._hyperlink
229
230
231    @hyperlink.setter
232    def hyperlink(self, val):
233        """Set value and display for hyperlinks in a cell.
234        Automatically sets the `value` of the cell with link text,
235        but you can modify it afterwards by setting the `value`
236        property, and the hyperlink will remain.
237        Hyperlink is removed if set to ``None``."""
238        if val is None:
239            self._hyperlink = None
240        else:
241            if not isinstance(val, Hyperlink):
242                val = Hyperlink(ref="", target=val)
243            val.ref = self.coordinate
244            self._hyperlink = val
245            if self._value is None:
246                self.value = val.target or val.location
247
248
249    @property
250    def is_date(self):
251        """True if the value is formatted as a date
252
253        :type: bool
254        """
255        return self.data_type == 'd' or (
256            self.data_type == 'n' and is_date_format(self.number_format)
257            )
258
259
260    def offset(self, row=0, column=0):
261        """Returns a cell location relative to this cell.
262
263        :param row: number of rows to offset
264        :type row: int
265
266        :param column: number of columns to offset
267        :type column: int
268
269        :rtype: :class:`openpyxl.cell.Cell`
270        """
271        offset_column = self.col_idx + column
272        offset_row = self.row + row
273        return self.parent.cell(column=offset_column, row=offset_row)
274
275
276    @property
277    def comment(self):
278        """ Returns the comment associated with this cell
279
280            :type: :class:`openpyxl.comments.Comment`
281        """
282        return self._comment
283
284
285    @comment.setter
286    def comment(self, value):
287        """
288        Assign a comment to a cell
289        """
290
291        if value is not None:
292            if value.parent:
293                value = copy(value)
294            value.bind(self)
295        elif value is None and self._comment:
296            self._comment.unbind()
297        self._comment = value
298
299
300class MergedCell(StyleableObject):
301
302    """
303    Describes the properties of a cell in a merged cell and helps to
304    display the borders of the merged cell.
305
306    The value of a MergedCell is always None.
307    """
308
309    __slots__ = ('row', 'column')
310
311    _value = None
312    data_type = "n"
313    comment = None
314    hyperlink = None
315
316
317    def __init__(self, worksheet, row=None, column=None):
318        super().__init__(worksheet)
319        self.row = row
320        self.column = column
321
322
323    def __repr__(self):
324        return "<MergedCell {0!r}.{1}>".format(self.parent.title, self.coordinate)
325
326    coordinate = Cell.coordinate
327    _comment = comment
328    value = _value
329
330
331def WriteOnlyCell(ws=None, value=None):
332    return Cell(worksheet=ws, column=1, row=1, value=value)