Coverage for /home/martinb/.local/share/virtualenvs/camcops/lib/python3.6/site-packages/openpyxl/cell/cell.py : 42%

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