ExtendedQtableview

Overview

This is based on a table UI element from an expensive piece of software from my work that is extremely handy for a manufacturing environment for tracking work/showing data. I thought I'd recreate it.

My implementation of this will remain performant even with tens of thousands of rows of data.

Features:

1. Accepts SQL - uses SQLITE3 to read/write to tables
2. Expansion rows with additional tables
3. Columns that support checkboxes
4. Header Filtering - w/ filter footer
5. In-column searching (just type in the column to find what you want)
6. Export to Excel
7. Optional Footer Row
8. Optional add/remove rows
9. Optional Datetime columns w/ QCalendar
10. Optional double click to edit table(s)
11. Sorting/movable columns

Use setup_table function to create table

Create Table

setup_table arguments (NOTE: these arguments do NOT need to be passed in at time of table initialization. The parameters/argument can be changed with separate functions later.

ExtentedQtableview.setup_table(maintable_data, maintable_headers, columns_with_checkboxes, checked_indexes_rows, sub_table_data, editable_columns, datetime_columns, footer, footer_values, subtable_col_checkboxes, sub_table_headers_labels, expandable_rows, add_mainrow_option, del_mainrow_option, add_subrow_option, del_subrow_option, subtable_datetime_columns, dbleclick_edit_only, use_sql, sql_maintable_path, sql_maintable_name, sql_maintable_query, sql_subtable_path, sql_subtable_name, sql_subtable_query)

• maintable_data: ---- List[List[str]]
  

  Accepts list of strings representing each row of table, IE: [[row1, row1, row1], [row2, row2, row2]]

• maintable_headers: ---- List[str]
  

  Accepts list of strings for header labels, IE: [label1, label2, label3]

• columns_with_checkboxes: ---- List[int]
  

  Accepts list of integers for columns you want to have checkboxes, IE: [1, 2, 5]

• checked_indexes_rows: ---- Dict[int: List[int]]
  

  Accepts a dictionary representing which rows you want to have checked for the columns with checkboxes, key = column number, values = row numbers 
        IE:  {1: [4, 5, 6], 2: [1, 2, 6], 5: [1, 3, 7]}

• sub_table_data: ---- List[List[List[str]]]
  

  Accepts a list of strings representing each row of the subtable for EACH row of the maintable, IE:  
        [ 
        [[1sub1, 1sub1, 1sub1], [1sub2, 1sub2 1sub2]], 
        [[2sub1, 2sub1, 2sub1], [2sub2, 2sub2, 2sub2]] 
        ] 
  If using subtables, this argument MUST be equal to the number of rows on the main table, even if the list is blank such as []. 
  For Example if your maintable has 3 rows and you are using expandable rows, then at minimum you need to pass [[], [], []] into this parameter

• editable_columns: ---- List[int]
  

  Accepts list of integers representing which columns you want to be editable, IE:  [1, 2, 5]

• datetime_columns: ---- List[int]
  

  Accepts list of integers representing which columns you want to use datetime and have a calendar popup, IE:  [1, 2, 5]

• footer: ---- bool (True/False)
  

  Accepts True or False bool to enable/disable footer

• footer_values: ---- Dict[int: str]
  

  Accepts a dictionary for which columns you want to have a footer, keys are columns indexes and values can be "total" or "sum", 
             IE: {1: "sum", 2: "total"} 
        "sum" = will sum integers/float values in the column together 
        "total" = will add up total columns or total boxes checked in column if the column is a checkbox column 
    Note: These values on the table will change dynamically based on any rows filtered

• subtable_col_checkboxes: ---- List[int]

  Accepts list of integers representing which columns in sub-tables to have checkboxes, IE: [0, 1, 3]

• sub_table_headers_labels: ---- List[str]

  Accepts list of strings representing header labels for sub-tables, IE: [header1, header2, header3]

• expandable_rows: ---- bool (True/False)

  Accepts True or False bool.  This MUST be enabled to use sub-tables!

• add_mainrow_option: ---- bool (True/False)

  Accepts True or False bool to enable option to add rows to main table

• del_mainrow_option: ---- bool (True/False)

  Accepts True or False bool to enable option for deletion of rows to main table

• add_subrow_option: ---- bool (True/False)

  Accepts True or False bool to enable option to add rows to sub-tables

• del_subrow_option: ---- bool (True/False)

  Accepts True or False bool to enable option for deletion of rows to sub-tables

• subtable_datetime_columns ---- List[int]

  Accepts list of integers representing which columns in the sub-tables you want to be datetime with calendar popups, IE: [1, 5, 7]

• dbleclick_edit_only: ---- bool (True/False)

  Accepts True or False bool to enable editing for the main table on a separate pop-up rather than on the table itself.  
  (useful to prevent accidental changes to the main table.

• use_sql: ---- bool (True/False

  Accepts True or False to enable the use of SQL tables.  This MUST be set to True to use SQL tables

• sql_maintable_path: ---- str

  Accepts string representing full path to the SQL database file for the main table data

• sql_maintable_name: ---- str

  Accepts string representing name of SQL table to grab from the SQL database file

• sql_maintable_query: ---- str

  Accepts query to do a specific SQL query to grab specific data from the SQL table. NOTE: THIS HAS NOT BEEN TESTED

• sql_subtable_path: ---- str

  Accepts string representing full path to SQL database file for the sub-table data

• sql_subtable_name: ---- str

  Accepts string representing SQL table name from the SQL database file listed in the sql_subtable_path argument

• sql_subtable_query ---- str

  Accepts query to do a specific SQL query to grab specific data from the sub-table SQL table. NOTE: THIS HAS NOT BEEN TESTED 
  

Modify Table Functions

If you've already setup your table instance, with say the following:

import ExtendedQtableview
table = ExtendedQtableview.setup_table()

table.loadnew_maintable_sql(maintable_name, maintable_sql_path, maintable_query, subtable_sql_name, subtable_sql_path, subtable_headers, subtable_query, keep_existing_filter)

• maintable_name: ---- str --- Required
  

  Name of SQL table to get from database

• maintable_sql_path: ---- str ---- Required
  

  Directory path to SQL database file

• maintable_query: ---- str
  

  SQL query to pass to get specific data from table  NOTE: This has NOT been tested

• subtable_sql_name: ----- str
  

  Name of SQL sub table to get from database

• subtable_sql_path: ---- str
  

  Directory path to SQL database file

• subtable_headers: ---- List[str] ---- Required if passing in subtable SQL name/path
  

   List of column header names for the sub tables

• subtable_query: ---- str
  

  SQL query to pass to get specific data from table  NOTE: This has NOT been tested

Note: that you do not need to pass subtable data in when using this function, it can be done with a separate function later.


table.loadnew_maintable_list(maintable_data, keep_existing_filter)

• maintable_data: ---- List[str]
  

  Accepts list of strings representing each row of table, IE: [[row1, row1, row1], [row2, row2, row2]]

• keep_existing_filter: ---- bool (True/False)
  

  Whether you want to keep any existing filters applied to the current table or not.  
  This is useful if you want the new table to be filtered from the get go.

table.loadnew_subtable_list(subtable_data)

• subtable_data: ---- List[List[List[str]]]
  

  Accepts a list of strings representing each row of the subtable for EACH row of the maintable, IE:  
        [ 
        [[1sub1, 1sub1, 1sub1], [1sub2, 1sub2 1sub2]], 
        [[2sub1, 2sub1, 2sub1], [2sub2, 2sub2, 2sub2]] 
        ] 
  If using subtables, this argument MUST be equal to the number of rows on the main table, even if the list is blank such as []. 
  For Example if your maintable has 3 rows and you are using expandable rows, then at minimum you need to pass [[], [], []] into this parameter

table.loadnew_subtable_sql(subtable_sql_name, subtable_sql_path, subtable_headers, subtable_query)

• subtable_sql_name: ---- str ---- Required
  

  Table name listed in the SQL database

• subtable_sql_path: ---- str ---- Required
  

  Directory path to SQL database file

• subtable_headers ---- List[str] ---- Required
  

  Accepts list of strings representing each name of the header columns

• subtable_query: ---- str
  

  Query to run on SQL database to get specific data from the table.  NOTE: This has not been tested

table.loadnew_checkboxed_rows(checked_rows)

• checked_rows ---- Dict[int: List[int]]
  

  Accepts a dictionary representing which rows you want to have checked for the columns with checkboxes, key = column number, values = row numbers 
        IE:  {1: [4, 5, 6], 2: [1, 2, 6], 5: [1, 3, 7]}

table.loadnew_columns_with_checkboxes(columns_with_checkboxes)

• columns_with_checkboxes: ---- List[int]
  

  Change columns you want designated on the main table to be checkbox columns

Note: If using expandable rows, you will NEED to add +1 to your column indexes for the keys. (this doesn't need to be done if providing this data during the initialization of the table with setup_table())


table.loadnew_edible_columns(editable_columns)

• editable_columns: ---- List[int]
  

  Change which columns you want to be user editable

Note: If using expandable rows, you will NEED to add +1 to your column indexes for the keys. (this doesn't need to be done if providing this data during the initialization of the table with setup_table())


table.loadnew_datetime_columns(datetime_columns)

• datetime_columns: ---- List[int]
  

  Change which columns you want to be date time.  If columns are editable, will provide a calendar popup to let user change dates.

Note: If using expandable rows, you will NEED to add +1 to your column indexes for the keys. (this doesn't need to be done if providing this data during the initialization of the table with setup_table())


table.loadnew_footervalues(footer_values)

• footer_values ---- Dict[int: str]
  

  Accepts a dictionary for which columns you want to have a footer, keys are columns indexes and values can be "total" or "sum", 
             IE: {1: "sum", 2: "total"} 
        "sum" = will sum integers/float values in the column together 
        "total" = will add up total columns or total boxes checked in column if the column is a checkbox column 
    Note: These values on the table will change dynamically based on any rows filtered

Note: If using expandable rows, you will NEED to add +1 to your column indexes for the keys. (this doesn't need to be done if providing this data during the initialization of the table with setup_table())


table.loadnew_subtable_headers(subtable_headers)

• subtable_headers: ---- List[str]
  

  Change column headers for the sub-tables

table.loadnew_subtable_col_checkboxes(subtable_col_checkboxes)

• subtable_col_checkboxes ---- List[int]
  

  Change which column(s) in the sub tables you want to be checkbox columns

table.update_add_mainrow_option(add_mainrow_option)

• add_mainrow_option: ---- bool (True/False)
  

  Change if you want to allow user to add rows to main table

table.update_del_mainrow_option(del_mainrow_option)

• del_mainrow_option: ---- bool (True/False)
  

  Change if you want to allow user to delete rows from main table

table.update_add_subrow_option(add_subrow_option)

• add_subrow_option: ---- bool (True/False)
  

  Change if you want to allow user to add rows to the sub-tables

table.update_del_subrow_option(del_subrow_option

• del_subrow_option ---- bool (True/False)
  

  Change if you want to allow user to delete rows from the sub-tables

table.loadnew_subtable_datetime(subtable_datetime_columns)

• subtable_datetime_columns: ---- List[int]
  

  Change which column(s) you want the sub-table to be datetime, which will allow user to update date via calendar popup.

table.update_dblclick_edit_only(dblclick_edit_only)

• dblclick_edit_only: ---- bool (True/False)
  

   Enable to allow editing of the main table through a separate screen rather than on the table itself.  Useful to prevent accidental changes.

table.useFooter(footer)

• footer: ---- bool (True/False)
  

  Enable/Disable using a footer

table.loadnew_headers(headers)

• headers ---- List[str]
  

  Change the column headers on the main table

table.update_using_sql(value)

• value: ---- bool (True/False)
  

  Change on whether to use SQL tables.  Obviously must be enabled to pull data from SQL database and use tables

table.use_expandable_rows(value)

• value ---- bool (True/False)
  

  Enable/Disable using expandable rows with sub-tables

table.clear_table(keep_filter)

• keep_filter ---- bool (True/False)
  

  Function clears table and pass in whether you want to retain the existing filter or not when loading in new table

Using existing QTableView Functions

If you've already setup your table instance, with say the following:

import ExtendedQtableview
table = ExtendedQtableview.setup_table()

To use existing QTableView functions, just do table.table_view.(existing qtableview function)()
For example:

table.table_view.resizeColumnsToContents()

Other Notes:

• If you want to make other actions happen when clicking on specific columns, such as opening a PDF. Override/rewrite the doubleclick_tableChange() function to make an action happen when double clicking a certain column.


• If using SQL w/ expandable rows and you provide a sub-table name and SQL directory path. The table MUST contain a "maintable_index" column. Where the maintable_index represents the row index of the maintable. For example, if your sub-table on the first row of the main table should have 10 rows in it, then you will have 10 rows on the SQL table with the number 1 in the maintable_index column.
  
  It's very possible you could get this data in some other way, but I would recommend setting up the tables on the SQL file for the sub tables in this way, for example:

  maintable_index    column1    column2
        1             data1      data2
        1             data3     something1
        2             test1      test2
        2             data52     data0
  



• If using SQL w/ expandable rows and you provide a sub-table name and SQL directory path. If the table name does not exist in the SQL database, a blank table will automatically be created that has a "maintable_index" column in the above method.


• If using SQL. Note that SQlite3 will automatically create .db file at your chosen pathway if the .db file doesn't exist. You can also use the create_sql_table_from_excel() and create_blank_sql_table() functions within the SQL_table.py to create the .db SQL database files and/or tables.


• If you are using checkboxes in columns and say if a checkbox is checked and you want it to perform a certain action, such as moving that row to another table, you can add/rewrite the checkboxstateChange() function to do that. It captures which checkbox changed and then use the addMainRow() or delMainRow() functions or whatever you are looking to do.
  
  (Note: if adding row programmatically from a different table, add to the addMainRow() function to accept that data to add to the new row).


• If you want to add a different type of editor for the columns (such as a button that opens a combobox that then edits table). You will need to add to these functions:
  

  createEditor()  (for creating the button)
  setEditorData()  (for setting the data to be in the editor)
  updateEditorGeometry()  (for setting size of editor within the cell) 

  And you may or may not want to send a signal to the handleDateeditKeyPress() function if you make a change in the editor and want to forcibly close it and commit the data say on the index change of a QCombobox


• If want to change the look of the dialog popups for changing the main table or sub table data, you can rewrite them in the initUI() functions in the addRowMaintable_window() and sub_table_window() classes.
  
  Depending on how you adjust these, you may have to change the sub_table_items_changed() or the addMainRowUpdate() functions. Keep in mind that the addRowMaintable_window() popup is used for both the feature for "double click to edit only" and for creation of new rows. If you want it to be different, I recommend coding a new QDialog class and have 1 of the features call it instead.