API tools faq
paste
Nasyia

pyqt5_tableview

Nasyia
Jan 7th, 2022
1,164
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 14.33 KB | None | 0 0
raw download clone embed print report
  1. from PyQt5.QtCore import Qt
  2. from PyQt5.Qt import *
  3. from PyQt5.QtGui import *
  4. from PyQt5.QtWidgets import *
  5. from PyQt5 import QtCore, QtGui, QtWidgets, uic
  6. from PyQt5.QtSql import *
  7. from PyQt5 import QtPrintSupport as printSupport
  8. from docx import Document
  9. from docx.enum.table import WD_TABLE_DIRECTION
  10. import xlwt
  11. from xlwt import Workbook
  12. import xlsxwriter
  13. import sys
  14. class Window(QWidget):
  15.     def __init__(self):
  16.         super(QWidget, self).__init__()
  17.         self.setLayoutDirection(Qt.RightToLeft)
  18.         self.setLocale(QLocale(QLocale.Arabic, QLocale.Libya))
  19.        
  20.         self.printer = printSupport.QPrinter()
  21.         # Configure defaults:
  22.         self.printer.setOrientation(printSupport.QPrinter.Portrait)
  23.         self.printer.setPageSize(QtGui.QPageSize(QtGui.QPageSize.A4))
  24.         pageSizeDictionary = {"a2": QPrinter.A2, "a3": QPrinter.A3, "a4": QPrinter.A4}
  25.         # self.printer.setPageSize(pageSizeDictionary.get(self.size.lower(), QPrinter.A4))
  26.         self.printer.setPageMargins(15, 15, 15, 15, QPrinter.Millimeter)
  27.        
  28.         self.dpi = 72
  29.         self.documentWidth = 8.5 * self.dpi
  30.         self.documentHeight = 11 * self.dpi
  31.        
  32.        
  33.         self.database()
  34.         self.query = QSqlQuery()
  35.         self.queryModel = QSqlQueryModel()
  36.         self.productTableView = QTableView()
  37.        
  38.        
  39.         self.printPushButton = QPushButton('print table', self)
  40.         self.printPushButton.clicked.connect(self.printTableView)
  41.        
  42.         self.printPreviewPushButton = QPushButton('print preview', self)
  43.         self.printPreviewPushButton.clicked.connect(self.printPreviewTableView)
  44.        
  45.        
  46.         self.exportAsDOCXPushButton = QPushButton('export as docx', self)
  47.         self.exportAsDOCXPushButton.clicked.connect(self.exportAsDOCX)
  48.        
  49.         self.exportAsEXCELPushButton = QPushButton('export as excel', self)
  50.         self.exportAsEXCELPushButton.clicked.connect(self.exportAsExcel)
  51.        
  52.         self.insertToDatabasePushButton = QPushButton('insert data to database', self)
  53.         self.insertToDatabasePushButton.clicked.connect(self.insertToDatabase)
  54.        
  55.         self.gridLayout = QGridLayout(self)
  56.         self.gridLayout.addWidget(self.insertToDatabasePushButton, 0, 0)
  57.         self.gridLayout.addWidget(self.productTableView, 1, 0)
  58.        
  59.        
  60.         self.horizontalLayout = QHBoxLayout()
  61.         self.horizontalLayout.addWidget(self.printPushButton)
  62.         self.horizontalLayout.addWidget(self.printPreviewPushButton)
  63.         self.horizontalLayout.addWidget(self.exportAsDOCXPushButton)
  64.         self.horizontalLayout.addWidget(self.exportAsEXCELPushButton)
  65.        
  66.         self.gridLayout.addLayout(self.horizontalLayout,2,0)
  67.        
  68.         self.setLayout(self.gridLayout)
  69.        
  70.         self.productsTableView()
  71.        
  72.        
  73.     def productsTableView(self):
  74.         sqlQuery = 'SELECT * FROM product '
  75.         self.queryModel.setQuery(sqlQuery)
  76.         self.productTableView.setModel(self.queryModel)
  77.         self.queryModel.setHeaderData(0, Qt.Horizontal, 'رقم المنتج')
  78.         self.queryModel.setHeaderData(1, Qt.Horizontal, 'اسم المنتج')
  79.         self.queryModel.setHeaderData(2, Qt.Horizontal, 'الكمية')
  80.         self.productTableView.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeToContents)
  81.         self.productTableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
  82.         self.productTableView.setSortingEnabled(True)
  83.         self.productTableView.doubleClicked.connect(self.displaySelectedAdminRow)
  84.     def displaySelectedAdminRow(self):
  85.         rowCount = self.productTableView.model().rowCount()
  86.         columnCount = self.productTableView.model().columnCount()
  87.         print('number of rows %d' % (rowCount))
  88.         print('number of columns %d' % (columnCount))
  89.         data = []
  90.         for row in range(self.productTableView.model().rowCount()):
  91.             data.append([])
  92.             for column in range(self.productTableView.model().columnCount()):
  93.                 index = self.productTableView.model().index(row, column)
  94.                 # We suppose data are strings
  95.                 data[row].append(str(self.productTableView.model().data(index)))
  96. #         print(data[0])
  97.         theList = data[0]
  98.         output = ''
  99.         for i in range(len(theList),0,-1):
  100.             output += str(theList[i])
  101.             print(output)
  102.     def paintRequest(self,printer):
  103.         model = self.productTableView.model()
  104.         ##########
  105.         tableFormat = QtGui.QTextTableFormat()
  106.         tableFormat.setHeaderRowCount(1)
  107.         tableFormat.setAlignment(Qt.AlignHCenter)
  108.         tableFormat.setAlignment(Qt.AlignVCenter)
  109.         tableFormat.setCellPadding(2.0)
  110.         tableFormat.setCellSpacing(2.0)
  111.         tableFormat.setWidth(
  112.             QtGui.QTextLength(QtGui.QTextLength.PercentageLength, 100))
  113.         ##########
  114.         document = QtGui.QTextDocument()
  115.         document.setPageSize(QtCore.QSizeF(self.documentWidth, self.documentHeight))
  116.         document.setDefaultFont(QtGui.QFont("Console  , Verdana, Arial, Helvetica, sans-serif", 16))
  117.         document.setDefaultStyleSheet('body{ Background-color: red}')
  118.         document.setDocumentMargin(30.0)
  119.         # document.setDefaultTextOption(to)
  120.         cursor = QtGui.QTextCursor(document)
  121.         # charFormat = cursor.charFormat()
  122.         # charFormat.setFont(QtGui.QFont("Segoe UI Light", 28))
  123.         table = cursor.insertTable(
  124.             model.rowCount(), model.columnCount(), tableFormat)
  125.         for row in range(table.rows()):
  126.             for column in range(table.columns()):
  127.                 index = model.index(row, column)
  128.                 cursor.insertText(str(model.data(index)))
  129.                 cursor.movePosition(QtGui.QTextCursor.NextCell)
  130.  
  131.         document.print_(printer)    
  132.     def printTableView(self):
  133.         dialog = printSupport.QPrintDialog(self.printer, self)
  134.         if dialog.exec_() == QDialog.Accepted:
  135.             self.paintRequest(dialog.printer())
  136.     def printPreviewTableView(self):
  137.         dialog = printSupport.QPrintPreviewDialog()
  138.         dialog.paintRequested.connect(self.paintRequest)
  139.         dialog.exec_()
  140.     def exportAsDOCX(self):
  141.         rowCount = self.productTableView.model().rowCount()
  142.         columnCount = self.productTableView.model().columnCount()
  143.  
  144.         document = Document()
  145.  
  146.         document.add_paragraph('كشف بالمنتجات')
  147.  
  148.         adminsTable = document.add_table(rows=rowCount, cols=columnCount)
  149.         adminsTable.direction = WD_TABLE_DIRECTION.LTR
  150.  
  151.         rowCells = adminsTable.add_row().cells
  152.         headerCells = adminsTable.rows[0].cells
  153.         headerDataList = []
  154.         for columnIndex,column in enumerate(range(columnCount)):
  155.                 headerData = str(self.productTableView.model().headerData(column, Qt.Horizontal))
  156.                 headerCells[columnIndex].text = headerData
  157.         ############
  158.         #####
  159.         #####
  160.         ############
  161.         for row in range(self.productTableView.model().rowCount()):
  162.             for column in range(self.productTableView.model().columnCount()):
  163.                 rowCells = adminsTable.add_row().cells
  164.                 index = self.productTableView.model().index(row, column)
  165.                 bodyData = (str(self.productTableView.model().data(index)))
  166.                 print(bodyData)  
  167.         document.save('كشف بالمنتجات.docx')
  168.  
  169.     def exportAsExcel_1(self):
  170.         rowCount = self.productTableView.model().rowCount()
  171.         columnCount = self.productTableView.model().columnCount()
  172.         wb = Workbook(encoding='utf-8')
  173.         ###########
  174.         style = xlwt.XFStyle()
  175.         fnt = xlwt.Font()
  176.         fnt.Size = 16
  177.         fnt.name = 'Times New Roman'
  178.         fnt.bold = True
  179.         style.font = fnt
  180.        
  181.         alignment = xlwt.Alignment()
  182.         alignment.horz = xlwt.Alignment.HORZ_CENTER
  183.         # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
  184.         alignment.vert = xlwt.Alignment.VERT_CENTER
  185.         # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
  186.         alignment.dire = xlwt.Alignment.DIRECTION_RL
  187.         # Type: Direction_General, Direction_LR, Direction_RL
  188.         style.alignment = alignment  
  189.         style.font.height = 250  
  190.         ##############
  191.         borders = xlwt.Borders() # Create Borders
  192.         borders.left = xlwt.Borders.DASHED
  193. #             DASHED
  194. #             NO_LINE
  195. #             THIN
  196.         # May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK,
  197.         # DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED,
  198.         # THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.
  199.         borders.right = xlwt.Borders.DASHED
  200.         borders.top = xlwt.Borders.DASHED
  201.         borders.bottom = xlwt.Borders.DASHED
  202.         borders.left_colour = 0x40
  203.         borders.right_colour = 0x40
  204.         borders.top_colour = 0x40
  205.         borders.bottom_colour = 0x40
  206.         style.borders = borders # Add Borders to Style
  207.        
  208.         ####################
  209.         pattern = xlwt.Pattern() # Create the Pattern
  210.         pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
  211.         pattern.pattern_fore_colour = 2
  212.         # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue,
  213.         # 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
  214.         style.pattern = pattern # Add Pattern to Style
  215.        
  216. #         path, _ = QFileDialog.getSaveFileName(self, "Save File", "exports.xls", "XLS Files(*.xls *.txt)")
  217.         path = True
  218.         if path:
  219.             sheet1 = wb.add_sheet('exports')
  220.            
  221.            
  222.             #sheet1.write(3,0, "Some") # 3-> row, o-> column
  223.             for column in range(columnCount):
  224.                 header = str(self.productTableView.model().headerData(column, Qt.Horizontal))
  225.                 sheet1.write(0,column, header,style)
  226.  
  227.             for column in range(columnCount):
  228.                 for row in range(rowCount):
  229.                     index = self.productTableView.model().index(row, column)
  230.                     bodyData = str(self.productTableView.model().data(index))
  231.                     sheet1.write(row+1, column, bodyData,style)
  232.                     sheet1.col(column).width = 11111
  233.         wb.save('exports.xls')
  234.     def exportAsExcel(self):
  235.         rowCount = self.productTableView.model().rowCount()
  236.         columnCount = self.productTableView.model().columnCount()
  237.         from xlsxwriter.workbook import Workbook
  238.         workbook = Workbook('output2.xlsx')
  239.         worksheet = workbook.add_worksheet()
  240.         cell_format = workbook.add_format()
  241.         worksheet.right_to_left()
  242.         worksheet.set_paper(9) # A4
  243.         worksheet.center_horizontally()
  244.         worksheet.center_vertically()
  245.         worksheet.set_margins(worksheet.center_horizontally())
  246.         cell_format.set_border(3)
  247.         cell_format.set_bold(True)
  248. #         set_margins([left=0.7,] right=0.7,] top=0.75,] bottom=0.75]]])
  249.         worksheet.set_header('&CHello');
  250.         data = []
  251.         for column in range(columnCount):
  252.                 for row in range(rowCount):
  253.                     header = str(self.productTableView.model().headerData(column, Qt.Horizontal))
  254.                     index = self.productTableView.model().index(row, column)
  255.                     bodyData = str(self.productTableView.model().data(index))
  256.                     data.append(header)
  257.                     data.append(bodyData)
  258. #                     print(self.productTableView.model().headerData(column, Qt.Horizontal))
  259.                     worksheet.write(row , column, bodyData)
  260. #                     worksheet.write(row , column, bodyData)
  261. #                     worksheet.add_table('B3:F7',data)
  262. #         print(data)
  263.         workbook.close()
  264.     def database(self):
  265.         database = QSqlDatabase.addDatabase('QSQLITE')
  266.         database.setDatabaseName('./products.db')
  267.         if not database.open():
  268.             print(database.lastError().text())
  269.             sys.exit(1)  # Error code 1 - signifies error
  270.         else:
  271.             print('database is connected successfully')
  272.     def insertToDatabase(self):
  273.         # Creating a query for later execution using .prepare()
  274.         createTableQuery = QSqlQuery()
  275.         createTableQuery.exec(
  276.             """
  277.            CREATE TABLE product (
  278.                product_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
  279.                product_name VARCHAR(40) NOT NULL,
  280.                quantity INTEGER(10) NOT NULL
  281.            )
  282.            """
  283.         )
  284.         insertDataQuery = QSqlQuery()
  285.         insertDataQuery.prepare(
  286.             """
  287.            INSERT INTO product (
  288.                product_name,
  289.                quantity
  290.            )
  291.            VALUES (?, ?)
  292.            """
  293.         )
  294.  
  295.         # Sample data
  296.         data = [
  297.             ("أقلام رصاص", "10"),
  298.             ("أقلام حبر", "10"),
  299.             ("مبراة", "10"),
  300.             ("مسطرة", "10"),
  301.         ]
  302.  
  303.         # Use .addBindValue() to insert data
  304.         for product_name, quantity in data:
  305.             insertDataQuery.addBindValue(product_name)
  306.             insertDataQuery.addBindValue(quantity)
  307.             insertDataQuery.exec()
  308.         self.productsTableView()
  309. if __name__ == '__main__':
  310.     app = QtWidgets.QApplication(sys.argv)
  311.     app.setStyleSheet("""
  312.    QPushButton {padding:10px;font-size:16px;font-weight: 400;}
  313.    QTableView{
  314.    border:1px solid #999;
  315.    font-family: Console  , Verdana, Arial, Helvetica, sans-serif;
  316.    font-weight: 400;
  317.    font-size: 16px;
  318.    }
  319.    QTableView::item
  320. {
  321. border:1px solid #999;
  322.    color: #000;
  323.      text-align: center;
  324. }QHeaderView::section{border:1px solid #999;font-family: Console  , Verdana, Arial, Helvetica, sans-serif;
  325.    font-weight: 400;
  326.    font-size: 16px;}
  327.    """)
  328.     window = Window()
  329.     window.setFixedWidth(500)
  330.     window.setFixedHeight(300)
  331.     window.show()
  332.     sys.exit(app.exec_())
  333.     # • Table.cell(row_idx, col_idx)
  334.     # • Row.cells[col_idx]
  335.     # • Column.cells[col_idx]
  336.  
  337.  
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!