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

[skyripper]

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.




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.

 

[skyripper]

Is there a reason you cant just use Excel if you are keeping it internal?

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.

 

[skyripper]

Can Excel connect to the the database directly over ODBC?

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)

Can you scrape the web pages using something like Python and write out a spreadsheet?

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.

 

[skyripper]

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.

 

[skyripper]

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

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]

...

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.