We sculpt a toolbar on PyQt, export data to Excel and HTML

In the previous part, I talked about creating a module for running SQL queries and the shell in which these modules are launched. After a short work with queries, an obvious question arises - how to use the result of the sample, except for how to look on the screen?

For this, it is worth making additional tools for exporting and copying data. We will export to a file in Excel format, and copy to the system buffer in HTML format.

But first, we stick a toolbar to our main window.
')

Toolbar

Let me remind you that our application is designed to be simple, universal and extensible. To make the toolbar also universal and extensible, we will move its definition to the configuration file, and the functions performed will be in external modules that are not explicitly imported into the toolbar module. Thus, adding a new button and a function will be reduced to prescribing them in the configuration file and adding the module to the program directory.


For toolbars in Qt there is a ready-made QToolBar class, from it we will generate our ToolBar. Now we need only one toolbar, but we will lay down the possibility of adding several panels to the program. Each panel needs its own configuration file with its own set of buttons, so we will pass the file name with the parameter when creating the toolbar.
The configuration file will be traditionally in the Ini format and UTF-8 encoded.

 class ToolBar(QToolBar): def __init__(self, iniFile, parent=None): super(ToolBar, self).__init__(parent) ini = QSettings(iniFile, QSettings.IniFormat) ini.setIniCodec("utf-8") 

The syntax for defining buttons in our hands, in the simplest case, we need three things:

- text on the button
- the module containing the button function
- button function

Let us determine that the function of the button takes one parameter - the current child window. What exactly the module will do with it is the task of the button module, and the taskbar task is limited only to its call.

Create such a file tools.ini:

 [Tools] 001=Export to Excel,exportview,"exportToExcel" 002=Copy as HTML,exportview,"copyAsHtml" 

Now, in python, we parse the definitions from the Ini file:

  ini.beginGroup("Tools") #      for key in sorted(ini.childKeys()): #    list, .. ini   #  ,   v = ini.value(key) title = v[0] params = v[1:] #      QAction,    a = self.addAction(title) #      [, ]   QAction a.params = params #          a.triggered.connect(self.execAction) ini.endGroup() 

The execution method assigned to all buttons will import the required module and call the function assigned to the button from it. To prevent us from registering each module in the import list of the toolbar, we will use the importlib library. It remains only to find out what button was pressed and from which QAction the signal came - the standard QObject.sender () method is responsible for this, then take the parameters stored in it and do what is intended in the module (whatever that may be).

  def execAction(self): try: params = self.sender().params module = importlib.import_module(params[0]) func = params[1] win = self.focusTaskWindow() exec("module.%s(win)" % func) except: print(str(sys.exc_info()[1])) return 

It remains to add our panel to our main window (module tasktree.py)

  self.tools = ToolBar("tools.ini",self) self.addToolBar(self.tools) 

We can start and check if the panel has appeared:



Maybe not as nice as the first picture, the main thing that works.

Tool Functions Module

Now is the time to make a module with button functions. We will have one module, because the export and copy functions will work with one data source and according to the same rules, there is no sense in spreading them across different modules.


Our functions will work with the QTableView data tables that we used in the modules to view the query results. To preserve the independence of the modules, we will determine the necessary component “on the fly” - either this is the current (focused) component of QTableView in the current window, or the first required class among the children of the current window.

 def focusItemView(win): if win == None: return None w = win.focusWidget() if w != None and isinstance(w, QTableView): return w views = win.findChildren(QTableView) if type(views) == type([]) and len(views)>0: return views[0] return None 

From the table we get a list of selected cells. If nothing is selected, then we force everything.

  indexes = view.selectionModel().selectedIndexes() if len(indexes) == 0: indexes = view.selectAll() indexes = view.selectionModel().selectedIndexes() if len(indexes) == 0: return; 

Probably, you already know that in Qt you do not receive an array of data directly, instead you work with indexes in the model. The QModelIndex index is a simple structure and points to a specific data position (row () row and column (), and in the hierarchy an indication of the parent index parent ()). Having obtained the index, you can get the data from it by the data () method

We received a list of indices of selected cells in the model, but the indices in this list follow in the order in which the user selected them, and not sorted by rows and columns. It will be more convenient for us to work not with a list, but with a dictionary (position → index) and sorted lists of affected rows and columns.

 def sortedIndexes(indexes): d = ob() # - d.indexes = { (i.row(), i.column()):i for i in indexes } d.rows = sorted(list(set([ i[0] for i in d.indexes ]))) d.columns = sorted(list(set([ i[1] for i in d.indexes ]))) return d 

Another thing to keep in mind is that by default QTableView allows you to select unlinked cells, so the list of indexes may contain cells that are almost randomly located:



Therefore, in d.rows there is each row used, in d.columns there is each column used, but their combination is not necessarily in d.indexes.

We also need a list of column names for greater beauty, which are displayed in QTableView. Get them from the model using the headerData method:

  headers = { col:model.headerData(col, Qt.Horizontal) for col in d.columns } 

Until now, the code for export and copying was the same, but now the differences have gone.

Export to Excel

To export to Excel files, I used the xlsxwriter package. It is installed, as usual, via pip:

 pip3 install xlsxwriter 

The package documentation is quite detailed and understandable, with examples, so I will not dwell on it. The point is that the record goes to the cells addressed by the row and column numbers. If you need additional formatting, then you need to define the style and specify it when writing the cell.

The name of the xlsx file to which we will export will be requested from the user, Qt has such a function. In PyQt, the function returns a list of the selected file name and the filter used. If the list of empty lines is returned, it means that the user has refused the choice.

  fileName = QFileDialog.getSaveFileName(None, 'Save Excel file', title,'Excel files (*.xlsx)') if fileName == ('',''): return 

Export proper:

  workbook = xlsxwriter.Workbook(fileName[0]) worksheet = workbook.add_worksheet() bold = workbook.add_format({'bold': True}) dateFormat = 'dd.MM.yyyy' date = workbook.add_format({'num_format': dateFormat}) realCol = 0 for col in d.columns: worksheet.write(0, realCol, headers[col], bold) realRow = 1 for row in d.rows: if (row, col) in d.indexes: try: v = d.indexes[(row,col)].data(Qt.EditRole) if isinstance(v, QDateTime): if v.isValid() and v.toPyDateTime() > datetime.datetime(1900,1,1): v = v.toPyDateTime() worksheet.write_datetime(realRow, realCol, v, date) else: v = v.toString(dateFormat) worksheet.write(realRow, realCol, v) else: worksheet.write(realRow, realCol, v) except: print(str(sys.exc_info()[1])) realRow += 1 realCol += 1 workbook.close() 

Dancing around QDateTime was added due to a different understanding of the date / time in Python, Qt and Excel - first, the xlsxwriter package can work with the Python datetime, but does not know how to use QDateTime from Qt, so you have to convert it additionally with a special function toPyDateTime; secondly, Excel can work only with dates from 01/01/1900, and everything that was before that time for Excel is just a string.

Result of export to Excel:

Copy to system buffer in HTML format

It is not always necessary to have a separate file with a selection, often, especially when there is little data, it is more convenient to copy it in a table form into the system clipboard, and then paste it into the right place, be it Excel, Word, web page editor or something else .

The most universal way to copy tabular data through a buffer is the usual HTML format. On Windows, * nix and MacOS are very different ways of working with the buffer (not to mention the fact that there are several), so it's good that Qt hides implementation details from us.

All we need is to create a QMimeData object, fill it through the setHtml method with a fragment of HTML markup, and give it to the system clipboard, which is available through QApplication

  mime = QMimeData() mime.setHtml(html) clipboard = QApplication.clipboard() clipboard.setMimeData(mime) 

The table is collected line by line, starting with the headers.

  html = '<table><tbody>\n' headers = { col:model.headerData(col, Qt.Horizontal) for col in d.columns } html += '<tr>' for c in d.columns: html += '<th>%s</th>' % headers[c] html += '</tr>\n' for r in d.rows: html += '<tr>' for c in d.columns: if (r, c) in d.indexes: v = d.indexes[(r,c)].data(Qt.DisplayRole) html += '<td>%s</td>' % v else: html += '<td></td>' html += '</tr>' html += '</tbody></table>' 

Result inserted in Word:

Here, the borders of the table are visible only due to the “ Show text borders ” setting enabled in Word; in fact, they are invisible. In order for the table to be copied with explicit borders, you need to change the style of the table in the table tag. I leave it to you.

Conclusion

So, we got a way to add new functions to our tool, and the functions are added and work no matter what data sources we will use and how to display them - the modules that work with the data, do not know anything about toolbars and their functions, toolbars are not connected neither with the data modules, nor with the functions of the buttons, and the functions of the buttons, not knowing about the toolbars or the data modules, are simply trying to process the current visual component in a manner known to them.

The sources used in the examples, as before, are posted on github under the MIT license.

Start - we grind ourselves a tool on PyQt
Continued - Cut XML by XQuery Markup