Coverage for /var/srv/projects/api.amasfac.comuna18.com/tmp/venv/lib/python3.9/site-packages/openpyxl/workbook/workbook.py: 33%

1# Copyright (c) 2010-2022 openpyxl 

2 

3"""Workbook is the top-level container for all document information.""" 

4from copy import copy 

5 

6from openpyxl.compat import deprecated 

7from openpyxl.worksheet.worksheet import Worksheet 

8from openpyxl.worksheet._read_only import ReadOnlyWorksheet 

9from openpyxl.worksheet._write_only import WriteOnlyWorksheet 

10from openpyxl.worksheet.copier import WorksheetCopy 

11 

12from openpyxl.utils import quote_sheetname 

13from openpyxl.utils.indexed_list import IndexedList 

14from openpyxl.utils.datetime import WINDOWS_EPOCH, MAC_EPOCH 

15from openpyxl.utils.exceptions import ReadOnlyWorkbookException 

16 

17from openpyxl.writer.excel import save_workbook 

18 

19from openpyxl.styles.cell_style import StyleArray 

20from openpyxl.styles.named_styles import NamedStyle 

21from openpyxl.styles.differential import DifferentialStyleList 

22from openpyxl.styles.alignment import Alignment 

23from openpyxl.styles.borders import DEFAULT_BORDER 

24from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL 

25from openpyxl.styles.fonts import DEFAULT_FONT 

26from openpyxl.styles.protection import Protection 

27from openpyxl.styles.colors import COLOR_INDEX 

28from openpyxl.styles.named_styles import NamedStyleList 

29from openpyxl.styles.table import TableStyleList 

30 

31from openpyxl.chartsheet import Chartsheet 

32from .defined_name import DefinedName, DefinedNameList 

33from openpyxl.packaging.core import DocumentProperties 

34from openpyxl.packaging.relationship import RelationshipList 

35from .child import _WorkbookChild 

36from .protection import DocumentSecurity 

37from .properties import CalcProperties 

38from .views import BookView 

39 

40 

41from openpyxl.xml.constants import ( 

42 XLSM, 

43 XLSX, 

44 XLTM, 

45 XLTX 

46) 

47 

48INTEGER_TYPES = (int,) 

49 

50class Workbook(object): 

51 """Workbook is the container for all other parts of the document.""" 

52 

53 _read_only = False 

54 _data_only = False 

55 template = False 

56 path = "/xl/workbook.xml" 

57 

58 def __init__(self, 

59 write_only=False, 

60 iso_dates=False, 

61 ): 

62 self._sheets = [] 

63 self._pivots = [] 

64 self._active_sheet_index = 0 

65 self.defined_names = DefinedNameList() 

66 self._external_links = [] 

67 self.properties = DocumentProperties() 

68 self.security = DocumentSecurity() 

69 self.__write_only = write_only 

70 self.shared_strings = IndexedList() 

71 

72 self._setup_styles() 

73 

74 self.loaded_theme = None 

75 self.vba_archive = None 

76 self.is_template = False 

77 self.code_name = None 

78 self.epoch = WINDOWS_EPOCH 

79 self.encoding = "utf-8" 

80 self.iso_dates = iso_dates 

81 

82 if not self.write_only: 

83 self._sheets.append(Worksheet(self)) 

84 

85 self.rels = RelationshipList() 

86 self.calculation = CalcProperties() 

87 self.views = [BookView()] 

88 

89 

90 def _setup_styles(self): 

91 """Bootstrap styles""" 

92 

93 self._fonts = IndexedList() 

94 self._fonts.add(DEFAULT_FONT) 

95 

96 self._alignments = IndexedList([Alignment()]) 

97 

98 self._borders = IndexedList() 

99 self._borders.add(DEFAULT_BORDER) 

100 

101 self._fills = IndexedList() 

102 self._fills.add(DEFAULT_EMPTY_FILL) 

103 self._fills.add(DEFAULT_GRAY_FILL) 

104 

105 self._number_formats = IndexedList() 

106 self._date_formats = {} 

107 self._timedelta_formats = {} 

108 

109 self._protections = IndexedList([Protection()]) 

110 

111 self._colors = COLOR_INDEX 

112 self._cell_styles = IndexedList([StyleArray()]) 

113 self._named_styles = NamedStyleList() 

114 self.add_named_style(NamedStyle(font=copy(DEFAULT_FONT), border=copy(DEFAULT_BORDER), builtinId=0)) 

115 self._table_styles = TableStyleList() 

116 self._differential_styles = DifferentialStyleList() 

117 

118 

119 @property 

120 def epoch(self): 

121 if self._epoch == WINDOWS_EPOCH: 

122 return WINDOWS_EPOCH 

123 return MAC_EPOCH 

124 

125 

126 @epoch.setter 

127 def epoch(self, value): 

128 if value not in (WINDOWS_EPOCH, MAC_EPOCH): 

129 raise ValueError("The epoch must be either 1900 or 1904") 

130 self._epoch = value 

131 

132 

133 @property 

134 def read_only(self): 

135 return self._read_only 

136 

137 @property 

138 def data_only(self): 

139 return self._data_only 

140 

141 @property 

142 def write_only(self): 

143 return self.__write_only 

144 

145 

146 @property 

147 def excel_base_date(self): 

148 return self.epoch 

149 

150 @property 

151 def active(self): 

152 """Get the currently active sheet or None 

153 

154 :type: :class:`openpyxl.worksheet.worksheet.Worksheet` 

155 """ 

156 try: 

157 return self._sheets[self._active_sheet_index] 

158 except IndexError: 

159 pass 

160 

161 @active.setter 

162 def active(self, value): 

163 """Set the active sheet""" 

164 if not isinstance(value, (_WorkbookChild, INTEGER_TYPES)): 

165 raise TypeError("Value must be either a worksheet, chartsheet or numerical index") 

166 if isinstance(value, INTEGER_TYPES): 

167 self._active_sheet_index = value 

168 return 

169 #if self._sheets and 0 <= value < len(self._sheets): 

170 #value = self._sheets[value] 

171 #else: 

172 #raise ValueError("Sheet index is outside the range of possible values", value) 

173 if value not in self._sheets: 

174 raise ValueError("Worksheet is not in the workbook") 

175 if value.sheet_state != "visible": 

176 raise ValueError("Only visible sheets can be made active") 

177 

178 idx = self._sheets.index(value) 

179 self._active_sheet_index = idx 

180 

181 

182 def create_sheet(self, title=None, index=None): 

183 """Create a worksheet (at an optional index). 

184 

185 :param title: optional title of the sheet 

186 :type title: str 

187 :param index: optional position at which the sheet will be inserted 

188 :type index: int 

189 

190 """ 

191 if self.read_only: 

192 raise ReadOnlyWorkbookException('Cannot create new sheet in a read-only workbook') 

193 

194 if self.write_only : 

195 new_ws = WriteOnlyWorksheet(parent=self, title=title) 

196 else: 

197 new_ws = Worksheet(parent=self, title=title) 

198 

199 self._add_sheet(sheet=new_ws, index=index) 

200 return new_ws 

201 

202 

203 def _add_sheet(self, sheet, index=None): 

204 """Add an worksheet (at an optional index).""" 

205 

206 if not isinstance(sheet, (Worksheet, WriteOnlyWorksheet, Chartsheet)): 

207 raise TypeError("Cannot be added to a workbook") 

208 

209 if sheet.parent != self: 

210 raise ValueError("You cannot add worksheets from another workbook.") 

211 

212 if index is None: 

213 self._sheets.append(sheet) 

214 else: 

215 self._sheets.insert(index, sheet) 

216 

217 

218 def move_sheet(self, sheet, offset=0): 

219 """ 

220 Move a sheet or sheetname 

221 """ 

222 if not isinstance(sheet, Worksheet): 

223 sheet = self[sheet] 

224 idx = self._sheets.index(sheet) 

225 del self._sheets[idx] 

226 new_pos = idx + offset 

227 self._sheets.insert(new_pos, sheet) 

228 

229 

230 def remove(self, worksheet): 

231 """Remove `worksheet` from this workbook.""" 

232 idx = self._sheets.index(worksheet) 

233 localnames = self.defined_names.localnames(scope=idx) 

234 for name in localnames: 

235 self.defined_names.delete(name, scope=idx) 

236 self._sheets.remove(worksheet) 

237 

238 

239 @deprecated("Use wb.remove(worksheet) or del wb[sheetname]") 

240 def remove_sheet(self, worksheet): 

241 """Remove `worksheet` from this workbook.""" 

242 self.remove(worksheet) 

243 

244 

245 def create_chartsheet(self, title=None, index=None): 

246 if self.read_only: 

247 raise ReadOnlyWorkbookException("Cannot create new sheet in a read-only workbook") 

248 cs = Chartsheet(parent=self, title=title) 

249 

250 self._add_sheet(cs, index) 

251 return cs 

252 

253 

254 @deprecated("Use wb[sheetname]") 

255 def get_sheet_by_name(self, name): 

256 """Returns a worksheet by its name. 

257 

258 :param name: the name of the worksheet to look for 

259 :type name: string 

260 

261 """ 

262 return self[name] 

263 

264 def __contains__(self, key): 

265 return key in self.sheetnames 

266 

267 

268 def index(self, worksheet): 

269 """Return the index of a worksheet.""" 

270 return self.worksheets.index(worksheet) 

271 

272 

273 @deprecated("Use wb.index(worksheet)") 

274 def get_index(self, worksheet): 

275 """Return the index of the worksheet.""" 

276 return self.index(worksheet) 

277 

278 def __getitem__(self, key): 

279 """Returns a worksheet by its name. 

280 

281 :param name: the name of the worksheet to look for 

282 :type name: string 

283 

284 """ 

285 for sheet in self.worksheets + self.chartsheets: 

286 if sheet.title == key: 

287 return sheet 

288 raise KeyError("Worksheet {0} does not exist.".format(key)) 

289 

290 def __delitem__(self, key): 

291 sheet = self[key] 

292 self.remove(sheet) 

293 

294 def __iter__(self): 

295 return iter(self.worksheets) 

296 

297 

298 @deprecated("Use wb.sheetnames") 

299 def get_sheet_names(self): 

300 return self.sheetnames 

301 

302 @property 

303 def worksheets(self): 

304 """A list of sheets in this workbook 

305 

306 :type: list of :class:`openpyxl.worksheet.worksheet.Worksheet` 

307 """ 

308 return [s for s in self._sheets if isinstance(s, (Worksheet, ReadOnlyWorksheet, WriteOnlyWorksheet))] 

309 

310 @property 

311 def chartsheets(self): 

312 """A list of Chartsheets in this workbook 

313 

314 :type: list of :class:`openpyxl.chartsheet.chartsheet.Chartsheet` 

315 """ 

316 return [s for s in self._sheets if isinstance(s, Chartsheet)] 

317 

318 @property 

319 def sheetnames(self): 

320 """Returns the list of the names of worksheets in this workbook. 

321 

322 Names are returned in the worksheets order. 

323 

324 :type: list of strings 

325 

326 """ 

327 return [s.title for s in self._sheets] 

328 

329 def create_named_range(self, name, worksheet=None, value=None, scope=None): 

330 """Create a new named_range on a worksheet""" 

331 defn = DefinedName(name=name, localSheetId=scope) 

332 if worksheet is not None: 

333 defn.value = "{0}!{1}".format(quote_sheetname(worksheet.title), value) 

334 else: 

335 defn.value = value 

336 

337 self.defined_names.append(defn) 

338 

339 

340 def add_named_style(self, style): 

341 """ 

342 Add a named style 

343 """ 

344 self._named_styles.append(style) 

345 style.bind(self) 

346 

347 

348 @property 

349 def named_styles(self): 

350 """ 

351 List available named styles 

352 """ 

353 return self._named_styles.names 

354 

355 

356 @deprecated("Use workbook.defined_names.definedName") 

357 def get_named_ranges(self): 

358 """Return all named ranges""" 

359 return self.defined_names.definedName 

360 

361 

362 @deprecated("Use workbook.defined_names.append") 

363 def add_named_range(self, named_range): 

364 """Add an existing named_range to the list of named_ranges.""" 

365 self.defined_names.append(named_range) 

366 

367 

368 @deprecated("Use workbook.defined_names[name]") 

369 def get_named_range(self, name): 

370 """Return the range specified by name.""" 

371 return self.defined_names[name] 

372 

373 

374 @deprecated("Use del workbook.defined_names[name]") 

375 def remove_named_range(self, named_range): 

376 """Remove a named_range from this workbook.""" 

377 del self.defined_names[named_range] 

378 

379 

380 @property 

381 def mime_type(self): 

382 """ 

383 The mime type is determined by whether a workbook is a template or 

384 not and whether it contains macros or not. Excel requires the file 

385 extension to match but openpyxl does not enforce this. 

386 

387 """ 

388 ct = self.template and XLTX or XLSX 

389 if self.vba_archive: 

390 ct = self.template and XLTM or XLSM 

391 return ct 

392 

393 

394 def save(self, filename): 

395 """Save the current workbook under the given `filename`. 

396 Use this function instead of using an `ExcelWriter`. 

397 

398 .. warning:: 

399 When creating your workbook using `write_only` set to True, 

400 you will only be able to call this function once. Subsequents attempts to 

401 modify or save the file will raise an :class:`openpyxl.shared.exc.WorkbookAlreadySaved` exception. 

402 """ 

403 if self.read_only: 

404 raise TypeError("""Workbook is read-only""") 

405 if self.write_only and not self.worksheets: 

406 self.create_sheet() 

407 save_workbook(self, filename) 

408 

409 

410 @property 

411 def style_names(self): 

412 """ 

413 List of named styles 

414 """ 

415 return [s.name for s in self._named_styles] 

416 

417 

418 def copy_worksheet(self, from_worksheet): 

419 """Copy an existing worksheet in the current workbook 

420 

421 .. warning:: 

422 This function cannot copy worksheets between workbooks. 

423 worksheets can only be copied within the workbook that they belong 

424 

425 :param from_worksheet: the worksheet to be copied from 

426 :return: copy of the initial worksheet 

427 """ 

428 if self.__write_only or self._read_only: 

429 raise ValueError("Cannot copy worksheets in read-only or write-only mode") 

430 

431 new_title = u"{0} Copy".format(from_worksheet.title) 

432 to_worksheet = self.create_sheet(title=new_title) 

433 cp = WorksheetCopy(source_worksheet=from_worksheet, target_worksheet=to_worksheet) 

434 cp.copy_worksheet() 

435 return to_worksheet 

436 

437 

438 def close(self): 

439 """ 

440 Close workbook file if open. Only affects read-only and write-only modes. 

441 """ 

442 if hasattr(self, '_archive'): 

443 self._archive.close() 

444 

445 

446 def _duplicate_name(self, name): 

447 """ 

448 Check for duplicate name in defined name list and table list of each worksheet. 

449 Names are not case sensitive. 

450 """ 

451 name = name.lower() 

452 for sheet in self.worksheets: 

453 for t in sheet.tables: 

454 if name == t.lower(): 

455 return True 

456 

457 if name in self.defined_names: 

458 return True 

459