Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/openpyxl/cell/cell.py: 72%

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)