Coverage for /var/srv/projects/api.amasfac.comuna18.com/tmp/venv/lib/python3.9/site-packages/tablib/formats/_xls.py: 19%
94 statements
« prev ^ index » next coverage.py v6.4.4, created at 2023-07-17 14:22 -0600
« prev ^ index » next coverage.py v6.4.4, created at 2023-07-17 14:22 -0600
1""" Tablib - XLS Support.
2"""
4from io import BytesIO
6import xlrd
7import xlwt
8from xlrd.xldate import xldate_as_datetime
10import tablib
12# special styles
13wrap = xlwt.easyxf("alignment: wrap on")
14bold = xlwt.easyxf("font: bold on")
17class XLSFormat:
18 title = 'xls'
19 extensions = ('xls',)
21 @classmethod
22 def detect(cls, stream):
23 """Returns True if given stream is a readable excel file."""
24 try:
25 xlrd.open_workbook(file_contents=stream)
26 return True
27 except Exception:
28 pass
29 try:
30 xlrd.open_workbook(file_contents=stream.read())
31 return True
32 except Exception:
33 pass
34 try:
35 xlrd.open_workbook(filename=stream)
36 return True
37 except Exception:
38 return False
40 @classmethod
41 def export_set(cls, dataset):
42 """Returns XLS representation of Dataset."""
44 wb = xlwt.Workbook(encoding='utf8')
45 ws = wb.add_sheet(dataset.title if dataset.title else 'Tablib Dataset')
47 cls.dset_sheet(dataset, ws)
49 stream = BytesIO()
50 wb.save(stream)
51 return stream.getvalue()
53 @classmethod
54 def export_book(cls, databook):
55 """Returns XLS representation of DataBook."""
57 wb = xlwt.Workbook(encoding='utf8')
59 for i, dset in enumerate(databook._datasets):
60 ws = wb.add_sheet(dset.title if dset.title else 'Sheet%s' % (i))
62 cls.dset_sheet(dset, ws)
64 stream = BytesIO()
65 wb.save(stream)
66 return stream.getvalue()
68 @classmethod
69 def import_set(cls, dset, in_stream, headers=True, skip_lines=0):
70 """Returns databook from XLS stream."""
72 dset.wipe()
74 xls_book = xlrd.open_workbook(file_contents=in_stream.read())
75 sheet = xls_book.sheet_by_index(0)
77 dset.title = sheet.name
79 def cell_value(value, type_):
80 if type_ == xlrd.XL_CELL_ERROR:
81 return xlrd.error_text_from_code[value]
82 elif type_ == xlrd.XL_CELL_DATE:
83 return xldate_as_datetime(value, xls_book.datemode)
84 return value
86 for i in range(sheet.nrows):
87 if i < skip_lines:
88 continue
89 if i == skip_lines and headers:
90 dset.headers = sheet.row_values(i)
91 else:
92 dset.append([
93 cell_value(val, typ)
94 for val, typ in zip(sheet.row_values(i), sheet.row_types(i))
95 ])
97 @classmethod
98 def import_book(cls, dbook, in_stream, headers=True):
99 """Returns databook from XLS stream."""
101 dbook.wipe()
103 xls_book = xlrd.open_workbook(file_contents=in_stream)
105 for sheet in xls_book.sheets():
106 data = tablib.Dataset()
107 data.title = sheet.name
109 for i in range(sheet.nrows):
110 if i == 0 and headers:
111 data.headers = sheet.row_values(0)
112 else:
113 data.append(sheet.row_values(i))
115 dbook.add_sheet(data)
117 @classmethod
118 def dset_sheet(cls, dataset, ws):
119 """Completes given worksheet from given Dataset."""
120 _package = dataset._package(dicts=False)
122 for i, sep in enumerate(dataset._separators):
123 _offset = i
124 _package.insert((sep[0] + _offset), (sep[1],))
126 for i, row in enumerate(_package):
127 for j, col in enumerate(row):
129 # bold headers
130 if (i == 0) and dataset.headers:
131 ws.write(i, j, col, bold)
133 # frozen header row
134 ws.panes_frozen = True
135 ws.horz_split_pos = 1
137 # bold separators
138 elif len(row) < dataset.width:
139 ws.write(i, j, col, bold)
141 # wrap the rest
142 else:
143 try:
144 if '\n' in col:
145 ws.write(i, j, col, wrap)
146 else:
147 ws.write(i, j, col)
148 except TypeError:
149 ws.write(i, j, col)