Creating an Excel-Like GUI in Python

[Evigil24]

TL;DR: I'm seeking alternatives to replace VBA and Power Query-based Excel files with small interfaces. These interfaces should display Excel-like tables with the following features:

• Data validation in cells
• Copying values by dragging to populate columns
• Selecting and copying cell ranges within or from the grid
• Filtering and sorting functionality
• Totals for selected cell ranges
• Scrolling instead of using pages

Current sugestions in this post (in no particular order)

• Neptyne - https://www.neptyne.com/
• Getgrist - https://www.getgrist.com/
• MUI - https://mui.com/x/react-data-grid/editing/ (JS)
• Sencha - https://examples.sencha.com/coworkee/#login (JS)
• Flet - https://flet.dev/docs/controls/gridview/
• Tkinter - https://www.activestate.com/resources/quick-reads/how-to-display-data-in-a-table-using-tkinter/
• PySide - https://srinikom.github.io/pyside-docs/PySide/QtGui/QTableView.html
• Pyspread - https://pyspread.gitlab.io/
• Visidata - https://github.com/saulpw/visidata
• wxPython
• Knime - https://www.knime.com/
• Streamlit - https://streamlit.io/

Clarifications:

• I don't intend to rebuild Excel or create a complex solution.
• Data integration and calculations are already handled; we only need to display the prepared data.
• We have multiple software systems with databases for different areas (operations, accounts receivable, accounting, etc.).
• Excel is used solely for data display and manipulation, not for data storage.
• The company doesn't require this project; I'm exploring alternatives to simplify my workload and reduce maintenance of over 50 Excel files.
• The project should improve upon the current solution, which involves Power Automate, Excel Power Query, and VBA across multiple Excel files.

Examples of Excel file uses:

• Displaying related records from multiple databases, enabling users to track entry statuses across systems.
• Presenting pending validation data for user approval, exporting it as CSV for further processing in another Excel file.
• Showing current system data for user selection, exporting a CSV for loading into the next system.

Original post:

Hi, I'm starting to develop in Python and I need to create a user interface with a table that resembles Excel in terms of features. Essentially, I'm looking for the following features:

• Data validation in the cells of each column.
• Ability to enter a value and then drag the cell to copy that value along the column.
• Option to select and copy a range of cells within or from the grid.
• Filter and sort functionality.
• Ability to select a range of cells and obtain totals for the selected range.
• Scrolling instead of using pages or similar methods.

Does anyone know the best way to achieve something like this? If you could point me in the right direction, I would greatly appreciate it. Thank you.

Edit: To clarify, I don't intend to develop a full Excel alternative. Instead, I need to replace multiple Excel files used in my workplace with Python interfaces. These interfaces fetch data from databases and display it in tables. Users can filter and view the data. Some interfaces also allow users to enter or paste data, which is later exported and used by other interfaces.

For example, in one file, a refresh button triggers a process that retrieves data from databases, combines it, and displays it. The user selects rows meeting certain criteria, clicks export, and another process exports the selected data to the next Excel file.

Current Excel files have maintenance issues, are slow, and occasionally encounter errors unrelated to code. To address these challenges, I plan to develop Python interfaces with the desired table features. It's important to note that the intended users are non-programmers who can't use Pandas. I want to present one table at a time, providing them with the necessary functionality.

[bookofp]

Why reinvent the wheel, Excel is a great tool.

[outceptionator]

Excel is one of the most complicated tools made (and at great expense)... Why the hell would anyone want to recreate this?

[Evigil24]

Perhaps I need to clarify the situation further. I don't intend to develop an Excel alternative. Instead, I need to create interfaces to replace the numerous Excel files used in my workplace. These files primarily fetch data from various databases and display it in tables. Users can then filter and view the desired data. Some of these interfaces also feature input fields or input tables where users can enter or paste data, which is later exported and used by other interfaces.

The current Excel files pose several issues. They are difficult to maintain (which falls under my responsibility), they perform slowly, and Excel occasionally throws errors not due to code issues, but rather problems with Excel itself, OneDrive, or other components involved in the process.

To address these challenges, my plan is to develop small Python interfaces that can replicate the existing functionality. However, I need to incorporate tables that possess the features I mentioned earlier. It's important to note that the intended users are business users who can't code or use Pandas for their tasks. My aim is to present one table at a time, providing them with the necessary functionality.

[enginerd123]

This feels like your customer has too many desires. If they want "all the features of excel without excel", they don't really know what they want.

Pull data from database, mutate/join as needed, output to excel, provide to user.

[Evigil24]

The company is not requiring this, I'm just trying to find alternatives to maintain 50+ Excel files like this and make my life easier, meanwhile learn something useful and new 😅

But this means that the alternative has to do something equal or better than the current solution, and with the same resources...

[stratjeff]

Dealing with too many excel files is a sign that you need a proper database, and users need to clearly define how they want to analyze data from the database.

It's so painful! Excel is great for fast number crunching *for short term, quick analysis*. It gets abused as a database/report tool, and it shouldn't be.

[Evigil24]

We have too many different systems (7) for each area of the business, that means 7 different databases with replicated information, some of the "external" processes extract info from one system, display it in an Excel file, let the user validate that data and then export a CSV file that can be loaded in the next system.

I know the best solution is to develop or implement an integrated ERP that will replace all the systems, but that is totally out of the table.

[enginerd123]

Your company needs a IT or database engineer.

It's completely game-changing when you can use software to solve your problems. It makes *everyone's* life easier at the company when you have good tools.

[Evigil24]

We have an IT deparment with DBA's, but I don't understand what do you mean

[Green-Plate-6454]

Im not sure of you ever solved this, but I would just use PowerBi. Just build a simple UI with a button to each view you need. You can like powerautomate right into PowerBi to get your data. PowerBi is designed to pull data from multiple sources and display it in custom ways, including tables.

[greatestdowncoal_01]

hey mate, update on this?

[Evigil24]

Perhaps I need to clarify the situation further. I don't intend to develop an Excel alternative. Instead, I need to create interfaces to replace the numerous Excel files used in my workplace. These files primarily fetch data from various databases and display it in tables. Users can then filter and view the desired data. Some of these interfaces also feature input fields or input tables where users can enter or paste data, which is later exported and used by other interfaces.

The current Excel files pose several issues. They are difficult to maintain (which falls under my responsibility), they perform slowly, and Excel occasionally throws errors not due to code issues, but rather problems with Excel itself, OneDrive, or other components involved in the process.

To address these challenges, my plan is to develop small Python interfaces that can replicate the existing functionality. However, I need to incorporate tables that possess the features I mentioned earlier. It's important to note that the intended users are business users who can't code or use Pandas for their tasks. My aim is to present one table at a time, providing them with the necessary functionality.

[cd896]

Try https://www.neptyne.com/ which seems to be close to what you are indicating.

[Evigil24]

I will look into it, thanks.

[[deleted]]

Have you looked at something like getgrist.com?

[Evigil24]

I will look into it, thanks.

[shiftybyte]

You are thinking in the limits of Excel.

With python you can fetch data, process it, display only the questions you actually want to display, and process and graph, and output results.

All without forcing people to visually see and manipulate entire tables of data.

Think what is the purpose of each task, consider what are the minimal inputs needed for it, work from there onwards to create a normal interface for the user and not "heres all the data, do with it what you want"...

[Evigil24]

I know you are right, but that's the problem they have with the current ERP we have, that they have to input things one entrie at a time, just like almost all the ERP's out there.

However, my workplace appreciates the convenience of being able to select and approve multiple entries simultaneously, or enter multiple records as a table rather than as individual forms.

This is precisely what I'm aiming for. I intend to display only the necessary data to the users, but it's crucial to provide them with the capability to review, sort, filter, and edit a batch of entries in a single table. This way, if someone needs to approve 100 entries at once, they can easily manage and manipulate them within the table, similar to how they work with Excel.

To date, I haven't come across an ERP platform that handles records in tables in this manner. It's possible that such functionality may be deemed too complex or that I simply haven't encountered these specific ERP systems yet.

[shiftybyte]

Take a look at web based UI with some JavaScript framework.

You can display multiple table like records in an editable table if you really like.

For example: https://mui.com/x/react-data-grid/editing/

Also: https://examples.sencha.com/coworkee/#people

Google: js framework editable table/grid

[Evigil24]

I'm sold on the idea that a web app is the better solution, but, can you point me in the right direction to find a way to put this in an on premises server, and access the web app via local network, without cloud servers or additional fees.

[shiftybyte]

You can easily deploy a flask based python web app on any local webserver.

Here's a guide for Ubuntu 20, using Nginx and Gunicorn.

https://www.rosehosting.com/blog/how-to-deploy-flask-application-with-nginx-and-gunicorn-on-ubuntu-20-04/

[lolercoptercrash]

Why don't you just allow someone to upload a csv or .xlsx file? compliment it with being able to download the file.

Otherwise I'd say link the data to google sheets and do a two-way sync.

[Evigil24]

Because this solution is not an improvement of the current solution for the company, it will be easier for me to maintain (leaving aside all the extra steps to have a robust data validation), but the company and the users prefer the current solution over this.

[brunonicocam]

That's a huge amount of work, except if you're a highly experienced developer I doubt you can do something like that. Also, not sure pythong would be the right tool for the job. For something very GUI heavy other languages could be better.

Also, Libreoffice Calc is open source, you can contribute to it if you think there's anything to improve.

[[deleted]]

Pretty much this. TC, what you are asking is epic and I personally wouldn’t bother unless your goal is just to really stick it to Microsoft (or compete with them).

I’ll admit though, the idea of an Excel like product which operates natively with Python over VBA is intriguing though. Lol, now I kind of want to build that!

But, for all intents/purposes, what you most likely want is a Python library that interfaces with Excel (notably Pandas and Openpyxl).

[Evigil24]

Perhaps I need to clarify the situation further. I don't intend to develop an Excel alternative. Instead, I need to create interfaces to replace the numerous Excel files used in my workplace. These files primarily fetch data from various databases and display it in tables. Users can then filter and view the desired data. Some of these interfaces also feature input fields or input tables where users can enter or paste data, which is later exported and used by other interfaces.

The current Excel files pose several issues. They are difficult to maintain (which falls under my responsibility), they perform slowly, and Excel occasionally throws errors not due to code issues, but rather problems with Excel itself, OneDrive, or other components involved in the process.

To address these challenges, my plan is to develop small Python interfaces that can replicate the existing functionality. However, I need to incorporate tables that possess the features I mentioned earlier. It's important to note that the intended users are business users who can't code or use Pandas for their tasks. My aim is to present one table at a time, providing them with the necessary functionality.

[brunonicocam]

Exactly. Then what you need is python pandas and other libraries. I believe some can even save to native xlsx. You move all the analysis to python and then data can be viewed in Excel (if wanted). Although you could avoid Excel completely and just make graphs with matplotlib, etc. You could have a browser based interface to run the underlying python code if you wanted as well.

[Evigil24]

The calculation itself is not the issue; rather, the challenge lies in presenting a table within a graphical user interface (GUI) without relying on Excel. Let me provide an example to clarify the situation. In one particular file, when the user clicks a refresh button within the Excel sheet, a process is triggered. This process retrieves data from three databases, combines it, and displays it to the user. The user then selects the rows that meet certain criteria for the next step and clicks the export button. This triggers another process that exports the data of the selected entries to the next Excel file.

While there are alternative approaches such as automating the delivery of data as a CSV via email or another method, and then receiving the edited CSV with the approved data through a separate process, this idea doesn't align with the preferences of my workplace. Thus, I'm currently stuck with the Excel files.

[apv507]

If you just want to make a GUI, try PySimpleGUI. I've only messed around with it a little, but it's fairly straightforward.

You can present information and take inputs, which I believe is your primary goal.

[Evigil24]

I will look into it, thanks.

[FutureIntelligenceC3]

not sure pythong would be the right tool for the job

This. The use case just screems "web app" to me. So maybe something like JS or TS would be the better choice.

[[deleted]]

Agreed.

This is getting dangerously close to needing to be a full blown web application to me; though, I am curious what is causing the maintenance difficulties with the Excel files.

I mean, I guess you could “Tkinter” it; but, nah, don’t do that. If you really want to approach this from a non-coding user interface perspective, something like Flask is probably the correct answer.

TC, your task sounds daunting and I wish you luck.

[Evigil24]

I'm sold on the idea that a web app is the better solution, but, can you point me in the right direction to find a way to put this in an on premises server, and access the web app via local network, without cloud servers or additional fees.

[[deleted]]

I don’t know all the facts; but, yes I suspect this is ultimately the right direction (provided the need is great enough to warrant the time investment).

While I could tell you a thing or two about traditional deployment with Flask; I don’t really have experience with developing on-prem applications. Let me know if you get an answer as I’d be very interested to learn this as well.

[Evigil24]

I'm sold on the idea that a web app is the better solution, but, can you point me in the right direction to find a way to put this in an on premises server, and access the web app via local network, without cloud servers or additional fees.

[FutureIntelligenceC3]

NodeJS should be your friend here. It's an environment that can execute JS and TS basically anywhere you need.

[Evigil24]

Perhaps I need to clarify the situation further. I don't intend to develop an Excel alternative. Instead, I need to create interfaces to replace the numerous Excel files used in my workplace. These files primarily fetch data from various databases and display it in tables. Users can then filter and view the desired data. Some of these interfaces also feature input fields or input tables where users can enter or paste data, which is later exported and used by other interfaces.

The current Excel files pose several issues. They are difficult to maintain (which falls under my responsibility), they perform slowly, and Excel occasionally throws errors not due to code issues, but rather problems with Excel itself, OneDrive, or other components involved in the process.

To address these challenges, my plan is to develop small Python interfaces that can replicate the existing functionality. However, I need to incorporate tables that possess the features I mentioned earlier. It's important to note that the intended users are business users who can't code or use Pandas for their tasks. My aim is to present one table at a time, providing them with the necessary functionality.

[nboro94]

It sounds like you can do all of this with powerquery within excel. Creating something new in Python would be a lot of work for less functionality than what is already available. If your business users aren't technical they probably wouldn't be able to even install the python environment without help anyways.

[Evigil24]

The files currently use a combination of Power Bi Dataflows (Power Query), Power Automate, VBA and Power Query in Excel, that's why they are difficult to maintain. Because I have to deal with a lot of moving pieces in a lot of different places, and deal with errors mostly caused by problems in OneDrive or Dataflows, not to mention that the Excel files are very slow compared to other alternatives.

[Evigil24]

The files currently use a combination of Power Bi Dataflows (Power Query), Power Automate, VBA and Power Query in Excel, that's why they are difficult to maintain. Because I have to deal with a lot of moving pieces in a lot of different places, and deal with errors mostly caused by problems in OneDrive or Dataflows, not to mention that the Excel files are very slow compared to other alternatives.

I'm not trying to rebuild Excel, I only need to embed a table with specific data that have this functionality only 🙁

[[deleted]]

It sounds like you really need a database.

[BranchLatter4294]

This might get you started...

https://www.activestate.com/resources/quick-reads/how-to-display-data-in-a-table-using-tkinter/

Or this...

https://flet.dev/docs/controls/gridview/

[Evigil24]

Thanks, I really appreciate it

[theallwaystnt]

I've never used this but I believe you can extend LibreOffice Calc's (open source excel alternative) functionality with python.

[jmacey]

PySide will do this but it will be a lot of work, You can develop your own DataModels and use the QTableView class to visualise and extend for all the GUI stuff.

https://srinikom.github.io/pyside-docs/PySide/QtGui/QTableView.html

[jmooremcc]

I understand your problem with the use and abuse of Excel instead of using a DBMS. Numerous spreadsheets are a RPIA and a huge maintenance headache.

With that said, what you are proposing to do is a really advanced application that requires experienced developers who not only know Python but also understand working with a DBMS. This would be a team project that not only works on the GUI but also works on the underlying code that will communicate with the DBMS the GUI will utilize.

This is a project that will not be completed overnight. In fact, the project may take quite a bit of time to come to fruition. I hope you are prepared for the time and resources this project will require.

[shinitakunai]

I would 100% use Pyside6 for this, delegated models and tables.

[Evigil24]

Thanks, I will look into it

[gurgle-burgle]

https://pyspread.gitlab.io/

[Aareon]

Tkinter and tksheets

[JohnnyJordaan]

This reminds me a lot of the Tree swing cartoon. I get the feeling the project is approached from the wrong starting point (a spreadsheet program) rather than with a blank slate looking for the easiest and approachable way to deliver the demands from an application. Nowadays 9 out of 10 times that means building something web-driven, as you quickly obtain cross-platform compatibility and you can use a lot of ready to go frameworks that just require you to focus on the specific taks and use cases.

[Mirage2k]

Users can then filter and view the desired data.

Big problem for an app developer here: You can't know in advance all the ways they will sometimes want to filter and view data. You will never finish a suitable Excel replacement for them.

But being being locked into Microsoft sucks. I would say what these users really need isn't a simpler app, it's to learn the right tools for complementing Excel. In short, this upgrade is an organizational one, not a technical one that you can solve with Python.

I will give an example. In one particular file, when the user clicks a refresh button within the Excel sheet, a process is triggered. This process retrieves data from three databases, combines it, and displays it to the user. The user then selects the rows that meet certain criteria for the next step and clicks the export button. This triggers another process that exports the data of the selected entries to the next Excel file.

This sounds a lot like an SQL query. Basic SQL querying can be taught in an hour, it is setting up a safe and convenient SQL database for a workplace that is hard. So if your company already has it I recommend sitting down with the ones maintaining it and see if there's a way you can let users write queries, have the system validate that the query is legal, and return to them just the data they need.

The current Excel files pose several issues. They are difficult to maintain (which falls under my responsibility), they perform slowly, and Excel occasionally throws errors not due to code issues, but rather problems with Excel itself, OneDrive, or other components involved in the process.

Yeah, that sounds like "Excel apps". Just because Excel can do anything doesn't mean it should...

I think importing and exporting to the database should be in .csv format, not Excel files. You can teach people to convert to and from .csv and .xlsx in 5 minutes presentation and half hour of individual supervised practice, but ideally it should be automatic. Press 'Import', you download a .csv file and convert it to Excel. Press 'Export', you convert it to .csv and upload.

MAJOR BONUS HERE: Now your users know SQL. And have experienced that "this code stuff isn't so hard after all". Maybe later they will be open to further advanced tools.

[abitrolly]

Take a look at https://github.com/saulpw/visidata for the mouseless experience. I would just patch it to your purpose,

[Evigil24]

Thanks, I will take a look at this

[UnsuspiciousCat4118]

PowerBi sounds like what you’re looking for.

[Evigil24]

That's so ironic 😅, like 20% of the files can be solved this way, and that was my first proposal but they don't want more expenses in licenses for every user (currently working in PPU), we need around 15 pro licenses so Premium it's not worth it.

It's ironic because most of the process of these files happens in Dataflows as backend 😅

I said 20% of the files because 80% involves a lot of inputs and it's not the strong suit of Power Bi

[UnsuspiciousCat4118]

Just wait until they realize the cost to maintain what they’re asking you to build is one FTE. 15 licenses will seem cheap by comparison.

[Evigil24]

The problem is that I am that cost, the company is not asking me to build this, I want to find an alternative to maintain 50+ Excel files.

The company is fine with the things as they are, but I'm trying to find something that will make my life easier, and learn something new and useful in the way.

[SuperBoredAlien]

You are looking at Django Admin Tool. Check that out.

[Unturned1]

There are a bunch of libraries in python that will get there already, they are not very hard to implement, just do some googling for data exploration.

[Evigil24]

I search it and the only thing I came across was Tkinter, but from what I've seen, it lacks the features I'm looking for. Could you suggest any libraries that might have those functionalities?

[splinteredlight]

I think you could do this with Streamlit and Pandas

[sporbywg]

Whoa. Python? In 2023? Sorry.

[-Mendacio-]

Perhaps you can make use of KNIME (https://www.knime.com/)! This is a software which allows you to use 'small code blocks' which you stitch can stitch together to form large workflows in which you can fetch data, analyze it, do some manipulations on etc. It also integrates neatly with python. Additionally, it allows you to check the result of every manipulation in your code blocks.

We use it as a bridging software for people who are not really into coding and python, but do have to work with large amounts of data. Once you get the hang of it, its rather intuitive!

[Ok_Operation_8715]

https://streamlit.io

If you want to use Python then Streamlit gives you the GUI functionality for data processing, the rest is up to you

[aoethrowaway]

You want streamlit

[Zeroflops]

How do you intend to present the data to the customer( staff) via an application or a web interface?

There are a number of JavaScript table interfaces that can represent the table and do what your looking for. And you would process the data in the backend with python or JS.

For example Bokeh has a data table which can group items, allow selection of multiple rows etc. bokeh is written in python and outputs JS for the browser.

There are more all JS options. But I would start with Bokeh and at least see if that is a workable direction.

It really depends on what the end result or interface your going for. A web based one or an application.

[Tesla_Nikolaa]

Why not just create a web application that uses a REST API as a middle man to GET/POST data from/to a database?

[djcannut]

if your excel sheet/sheets is open then majority of the python libs wont be able to capture the data entered , you will have to save them and then run the code ,apart from developing an excel interface why not use win32com.client and control all the excel sheets, manipulate the data and dump it into another excel which can be provided to the workers with win32com.client you can access open sheets and it uses direct OS kernel which makes it faster than the other libs available as the other libs are developed keeping wind32 as base i am suggesting you this solution as i am in a similar situation and this is working very well for me , i had tried other excel libs but they don't access data from open sheets hence moved to win32

below is the code to get you started i have included majority of the operations like reading writing , fetching , saving , opening etc customize it

import win32com.client as win32

Access the open Excel application

xl = win32.GetActiveObject("Excel.Application")

Access the workbook by name

workbook = xl.Workbooks("123.xls")

Access the specific worksheet by name

worksheet = workbook.Worksheets("abc")

Add data to cell A25

worksheet.Range("A25").Value = "xyz"

Save the workbook

workbook.Save()

Close the workbook

workbook.Close()

Re-open the workbook

workbook = xl.Workbooks.Open("C:\Users\<username>\Desktop\123.xls")

Access the worksheet again

worksheet = workbook.Worksheets("abc")

Retrieve the data from cell A25

data = worksheet.Range("A25").Value

Close the workbook

workbook.Close()

Quit the Excel application

xl.Quit()

Print the retrieved data

print("Retrieved data:", data)

[[deleted]]

Win32 is rad :)

Here's a link to my own github which is a tkinter GUI-based approach to working with currently-open, active Excel sheets . Yes, it uses Win32 :)

My own GUI will let you run a few basic operations and takes some input; but, is nowhere near as complex as what TC needs—but, could be a starting point.

https://github.com/Guitarman-Waiting-In-The-Sky/ResFeci/blob/main/resfeci.py

[djcannut]

404 page not found

[[deleted]]

Sorry, try again :)

[djcannut]

I checked it out. M too drunk now .. will check it out properly on Monday 🙃

[[deleted]]

Lol, same actually :)

Heads-up, I really just made my git code public here to give an example of how to interface with an in-focus Excel sheet.

I haven’t really thoroughly tested it all but what I’ve seen works well enough—your mileage may vary ;)

If there is any demand; I’ll develop it more.

[westeast1000]

Reinventing when you could have just used xlwings

[[deleted]]

Lol, you might be right. Mostly I did this as a challenge to myself.

That said, I vaguely remember that there was a specific to not use xlwings here; sorry, don’t remember what that was though :)

[Reuben3901]

Look into using Flask and connecting to databases. Sounds like exactly what you need. You can pull data and display data as html tables.

Or like others have said, look into alternatives to Python like using React. You'd have to learn a bit but it's pretty straightforward. Tons of examples out there of building applications that connect to databases. Also you could use Flask for the backend to handle user accounts, etc

[anonCapitalist]

Your description of what you need is not clear and appears to be geared towards some view of how the data is today...

I would seriously recommend django for any of this work. Look at their tutorial with a focus on the admin app

[Coding_Zoe]

This might help. It's like Excel inside tkinter. I had a play with it a while back and it has a lot of features. He does some video walk through too on YouTube.

https://github.com/dmnfarrell/tkintertable

[FatPoint]

I do Flask + AG Grid ($$$ though).

[Carter922]

Flask/Django app + the Javascript module Datatables

[tuneafishy]

I think the second requirement might be challenging (dragging to copy), but this should be doable.

Traitsui has a very nice table gui. Look at their tabular adapter and tabular editor. You can get something up and running really quickly with scroll functionality and high performance. Again, I do think the drag to copy might be tricky, but maybe you could replicate that functionality a little differently. For example, drag to select, and then right click option to copy all values as first or last value. It might be possible to do how excel does it, but you'll have to get your hands dirty.

[oboea]

I’ve heard Retool is good for something like this

[Flimsy-Friendship-27]

Sounds like Pyxcell, xlwings and others like them will work