-
Notifications
You must be signed in to change notification settings - Fork 0
/
table_widget.py
389 lines (332 loc) · 11.5 KB
/
table_widget.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
"""
/***************************************************************************
Name : ExcelTableWidget
Description : A read-only table view for browsing MS Excel data.
Date : 07/July/2019
email : gkahiu@gmail.com
***************************************************************************/
/***************************************************************************
* *
* This program is free software; you can redistribute it and/or modify *
* it under the terms of the GNU General Public License as published by *
* the Free Software Foundation; either version 2 of the License, or *
* (at your option) any later version. *
* *
***************************************************************************/
"""
import itertools
import string
from datetime import datetime
from PyQt4.QtGui import (
QAbstractItemView,
QMessageBox,
QProgressBar,
QTabWidget,
QTableWidget,
QTableWidgetItem,
QVBoxLayout,
QWidget
)
from PyQt4.QtCore import (
QFile,
QFileInfo,
QSize,
Qt
)
# Flag for checking whether import of xlrd library succeeded
XLRD_AVAIL = True
try:
from xlrd import (
open_workbook,
xldate_as_tuple,
XLDateError
)
except ImportError as ie:
XLRD_AVAIL = False
def _ascii_lbl_gen():
"""Generator function for producing Excel like columns i.e. A...Z,
AA...AZ etc.
"""
n = 1
while True:
for group in itertools.product(string.ascii_uppercase, repeat=n):
yield ''.join(group)
n += 1
def uppercase_labels(n):
"""
Generates a sequential repeating pair of uppercase characters.
:param n: Number of items to be generated.
:type n: int
:return: A list containing a sequential repeating pair of uppercase
characters.
:rtype: list
"""
return list(itertools.islice(_ascii_lbl_gen(), n))
class ExcelSheetView(QTableWidget):
"""A widget for displaying Excel sheet data."""
def __init__(self, **kwargs):
super(QTableWidget, self).__init__(**kwargs)
self._nrows = 20
self._ncols = 20
self._ws = None
# Default ISO format for the date
self._date_format = '%Y-%m-%d'
# Init default view
self._init_ui()
@property
def sheet(self):
"""
:return: Returns object containing worksheet data.
:rtype: xlrd.sheet.Sheet
"""
return self._ws
def _init_ui(self):
# Set default options
self.setEditTriggers(QAbstractItemView.NoEditTriggers)
self.setSelectionMode(QAbstractItemView.SingleSelection)
self.setSelectionBehavior(QAbstractItemView.SelectRows)
self._update_view()
def _update_view(self):
# Set number of rows and columns, as well as horizontal labels.
self.setRowCount(self._nrows)
self.setColumnCount(self._ncols)
hlabels = uppercase_labels(self._ncols)
self.setHorizontalHeaderLabels(hlabels)
def load_worksheet(self, xsheet):
"""
Loads worksheet data into the table.
:param xsheet: Object containing worksheet data.
:type xsheet: xlrd.sheet.Sheet
"""
if not xsheet:
QMessageBox.critical(
self.parentWidget(),
self.tr('Null Sheet'),
self.tr('Sheet object is None, cannot be loaded.')
)
return
self._ws = xsheet
self._ncols = self._ws.ncols
self._nrows = self._ws.nrows
# Update view
self._update_view()
# Add cell values
tbi = None
for r in range(self._nrows):
for c in range(self._ncols):
tbi = QTableWidgetItem()
cell = self._ws.cell(r, c)
val = cell.value
# Number
if cell.ctype == 2:
val = str(float(val))
# Date/time
elif cell.ctype == 3:
try:
dt = xldate_as_tuple(val, self._ws.book.datemode)
val_dt = datetime(*dt)
# Apply formatting for the date/time
val = val_dt.strftime(self._date_format)
except XLDateError as de:
self.format_error_cell(tbi)
val = u'DATE ERROR!'
# Boolean
elif cell.ctype == 4:
val = 'True' if val == 1 else 'False'
# Error with Excel codes
elif cell.ctype == 5:
self.format_error_cell(tbi)
val = u'ERROR - {0}'.format(str(val))
tbi.setText(val)
self.setItem(r, c, tbi)
def format_error_cell(self, tbi):
"""
Formats error cell by changing text color to red.
:param tbi: Table widget item to format.
:type tbi: QTableWidgetItem
"""
tbi.setTextColor(Qt.red)
@property
def date_format(self):
"""
:return: Returns the format used to render the date/time in the
table. The default formatting will return the date in ISO 8601 format
i.e. 'YYYY-MM-DD' where the format is '%Y-%m-%d'.
:rtype: str
"""
return self._date_format
@date_format.setter
def date_format(self, format):
"""
Sets the format used to render the date/time in the table. The format
needs to be in a format that is understood by Python's 'strftime()'
function.
:param format: Format for rendering date/time
:type format: str
"""
self._date_format = format
class WorksheetInfo(object):
"""
Container for info on Sheet object, container widget, index in tab widget
and name of the worksheet.
"""
def __init__(self):
self.idx = -1
self.ws_widget = None
self.name = ''
self.ws = None
class ExcelWorkbookView(QWidget):
"""A read-only table view for browsing MS Excel data."""
def __init__(self, *args, **kwargs):
super(QWidget, self).__init__(*args, **kwargs)
self._init_ui()
# Optional args
self._dt_format = kwargs.pop('date_format', '%Y-%m-%d')
# Map for sheet widgets
self._ws_info = {}
# Reset the view
self.reset_view()
# Check availability of XLRD library
if not XLRD_AVAIL:
QMessageBox.critical(
self,
self.tr('Missing Dependency'),
self.tr(
'\'xlrd\' library is missing.\nExcel data cannot be loaded '
'without this library. Please install it and try again.'
)
)
self.setEnabled(False)
return
@property
def date_format(self):
"""
:return: Returns the format used to render the date/time in the
view. The default formatting will return the date in ISO 8601 format
i.e. 'YYYY-MM-DD' where the format is '%Y-%m-%d'.
:rtype: str
"""
return self._dt_format
@date_format.setter
def date_format(self, format):
"""
Sets the format used to render the date/time in the view. The format
needs to be in a format that is understood by Python's 'strftime()'
function.
:param format: Format for rendering date/time
:type format: str
"""
self._dt_format = format
def worksheet_info(self, idx):
"""
:param idx:
:return: Returns a WorksheetInfo object containing references to the
ExcelWorksheetView, xlrd.sheet.Sheet and name of the sheet, will
return None if the index does not exist.
:rtype: WorksheetInfo
"""
return self._ws_info.get(idx, None)
def current_worksheet_info(self):
"""
:return: Returns the WorksheetInfo object for the current displayed
tab. None if the view is empty.
:rtype:WorksheetInfo
"""
curr_idx = self._tbw.currentIndex()
return self.worksheet_info(curr_idx)
@property
def progress_bar(self):
"""
:return: Returns the progress bar for showing progress when Excel
data is being added to the table.
:rtype: QProgressBar
"""
return self._pg_par
def sizeHint(self):
return QSize(480, 360)
def clear_view(self):
# Removes and deletes all sheet widgets and resets the widget registry index.
self._tbw.clear()
self._ws_info.clear()
def reset_view(self):
# Clears the view and add an empty default sheet.
self.clear_view()
self._add_default_sheet()
def _add_default_sheet(self):
# Add a default/empty sheet to the view.
def_sheet = ExcelSheetView()
self._tbw.addTab(def_sheet, self.tr('Sheet 1'))
def _init_ui(self):
# Set up layout and widgets
self._vl = QVBoxLayout()
self._tbw = QTabWidget()
self._tbw.setTabShape(QTabWidget.Triangular)
self._tbw.setTabPosition(QTabWidget.South)
self._tbw.setStyleSheet('QTabBar::tab:selected { color: green; }')
self._vl.addWidget(self._tbw)
self._pg_par = QProgressBar()
self._pg_par.setVisible(False)
self._vl.addWidget(self._pg_par)
self.setLayout(self._vl)
def add_xlrd_sheet(self, xsheet):
"""
Adds data contained in a sheet object to the view.
:param xsheet: Object containing worksheet data.
:type xsheet: xlrd.sheet.Sheet
"""
worksheet = ExcelSheetView()
worksheet.date_format = self._dt_format
name = xsheet.name
idx = self._tbw.addTab(worksheet, name)
self._tbw.setTabToolTip(idx, name)
worksheet.load_worksheet(xsheet)
# Add worksheet info to collection
wsi = WorksheetInfo()
wsi.name = name
wsi.idx = idx
wsi.ws_widget = worksheet
wsi.ws = xsheet
self._ws_info[wsi.idx] = wsi
def load_workbook(self, path):
"""
Loads the workbook contained in the specified file to the view.
:param path: Path to fil containing workbook data.
:type path: str
"""
xfile = QFile(path)
if not xfile.exists():
QMessageBox.critical(
self,
self.tr('Invalid path'),
u'\'{0}\' {1}'.format(
path,
self.tr('does not exist.')
)
)
return
# Check permissions
xfileinfo = QFileInfo(xfile)
if not xfileinfo.isReadable():
QMessageBox.critical(
self,
self.tr('Unreadable file'),
u'{0} {1}'.format(
path,
self.tr('is not readable.')
)
)
return
# Clear view
self.clear_view()
# Show progress bar
self._pg_par.setVisible(True)
pg_val = 0
# Add sheets
wb = open_workbook(path)
self._pg_par.setRange(0, wb.nsheets)
for s in wb.sheets():
self.add_xlrd_sheet(s)
# Update progress bar
pg_val += 1
self._pg_par.setValue(pg_val)
self._pg_par.setVisible(False)