Display a spreadsheet on a webpage or create a spreadsheet from an opensource API

skyripper

skyripper

skyripper

Associate
Joined
19 Jul 2011
Posts
2,362
We have an big ERP application and like most ERPs it has huge great tables of data that we need to report and get out. Collating, sorting and manipulating this data is bread and butter for us. We have a variety of reporting tools we can use, but they're clunky and limited.

What I'd like to do is take a bunch of data and display it like a spreadsheet on screen. Specifically I'd like to be able to format cells by colour (to highlight important values), provide a useable filter at the top of the sheet for the user (to sort, filter by values etc.). The things that finance departments always do with Excel.

hBaM0FGm.png.jpg


I've had some success with writing out XML files (in an openXML spreadsheet format), but the coding for this was quite tough (we're limited to the development tools and language provided by the ERP), and users have to jump thru hoops to get these loaded into Excel - "Why can't you just make an excel spreadsheet?" they say.

Anyone know any go-to APIs or open source HTML widgets/libraries I can go research? What I can't do is use any cloud based services to create these (it needs to stay within network - I can however copy libraries / CSS / HTML source if needed), and it can't rely on any software loaded on the client or server to generate it beyond what we already have (which rules out using a local install of Office and APIing that to produce a sheet).

Ideas?!

I can make code to produce HTML but I am not a particularly proficient HTML coder.
 
james.miller said:
Is there a reason you cant just use Excel if you are keeping it internal?
Click to expand...
The ERP has a web front end, runs on AIX, and is outside of the development teams control.

If there is a way of interacting with a local Excel install just from html in a browser, then I could leverage that.
 
Can Excel connect to the the database directly over ODBC?

Can you scrape the web pages using something like Python and write out a spreadsheet?
 
Hades said:
Can Excel connect to the the database directly over ODBC?
Click to expand...
No access to the database outside of the application. (well there is an ODBC connector provided, but allowing a connection from the client machine with Excel back to the database across the WAN, thru firewalls etc - not really an option)
Hades said:
Can you scrape the web pages using something like Python and write out a spreadsheet?
Click to expand...
We don't need to scrape the data from webpages - we already have the data. Its the displaying it on a webpage in a spreadsheet like format (or creating a real excel sheet from the data) that I'd like to do.
 
Last time I looked at this (which is how I ended up creating openXML spreadsheets), I found that modern excel formats are actually ZIP files (renamed to .xlsx) containing a bunch of directories and XML files within them. However thats where I hit a bit of a wall, because the format isnt exactly easy to reverse engineer, even though I can create XML files, directories, zip them up and rename the resulting file. So technically achievable, but huge effort needed to do so.
 
ThePainBarrier said:
How about using Google Charts?
It has a Table Chart that looks similar to a spreadsheet.

Would mean you need to be able to access the data and build the table in code...

https://developers.google.com/chart/interactive/docs/gallery/table
Click to expand...

That looks like a good direction to start researching. I don't mind writing code to create the HTML / JavaScript (it will take a while, but theres no deadline for this). So presumably the browser renders all this itself as long as it has access to the https://www.gstatic.com/charts/loader.js.
 
skyripper said:
Last time I looked at this (which is how I ended up creating openXML spreadsheets), I found that modern excel formats are actually ZIP files (renamed to .xlsx) containing a bunch of directories and XML files within them. However thats where I hit a bit of a wall, because the format isnt exactly easy to reverse engineer, even though I can create XML files, directories, zip them up and rename the resulting file. So technically achievable, but huge effort needed to do so.
Click to expand...
Languages such as Python have packages to read and write excel files and csv files.
 
skyripper said:
That looks like a good direction to start researching. I don't mind writing code to create the HTML / JavaScript (it will take a while, but theres no deadline for this). So presumably the browser renders all this itself as long as it has access to the https://www.gstatic.com/charts/loader.js.
Click to expand...

Yes, all the hard work is done in the loader.js as far as I am aware. You just supply the data and it renders it.

Found this site which has tutorials for every Chart type:

https://www.tutorialspoint.com/googlecharts/

The code is amazingly simple.
I came acrosss it in a PHP course on Udemy where we used Google Charts to display various charts using data supplied from a MySQL database.
 
skyripper said:
No access to the database outside of the application. (well there is an ODBC connector provided, but allowing a connection from the client machine with Excel back to the database across the WAN, thru firewalls etc - not really an option)
Click to expand...

Jeez they dont want to make it too easy for you do they?

In my last place we had an SQL database for all things MIS related. clients connected to this and this pulled data from the ERP database, which was also SQL. I had full reign over the MIS database, it's structure, all the reports and everything in between. It was very simple and it worked very well. Sorry that doesnt help you much lol
 
Front a front-end point of view, will something like Datatables be of any use? You define the columns and data source (JSON IIRC) and it'll give you sorting and searching etc and you should be able to fire in your own logic for highlighting etc.
 
ThePainBarrier said:
...
Click to expand...

It worked.

Taken our ERP dev environment, pulled data from the database using code, whacked it about into a JSON format, wrapped it in some example code from the GoogleCharts site, threw it onto a page in the ERP, and voila... A sortable, scrollable spreadsheet where I can pick colours or formats for cells, rows, columns etc. just by applying a CSS style. If our application was a bit more flexible we could probably have two way communication to the sheet as well, dynamically updating values and stuff. But thats for real developers to worry about. Right now, you've given me the final tool needed to solve a complex problem that involves too much pulling data from different places, comparing it, dumping it to Excel, formatting it and then eyeballing it.

I doff my cap to you!
Cheers!

@Dup - I'll be looking at your suggestion too to see if thats offers an any easier pathway or some features not in the Google Visualiser APIs.
 
You must log in or register to reply here.
Back
Top Bottom