Displaying Excel data in forum posts

[jagnet]

awesome! I feel very honoured!

Updates

Can now paste data straight from an Excel sheet - the paste <textarea> accepts both CSV and Tab-Delimited data

Can set the first column to a header - PH renders this correctly with <th> elements, like so:

Name	Age	Weight (kg)	Height (cm)	City	Date
Alice	30	65	168	London	2025-03-03
Bob	25	80	180	Manchester	2025-03-02
Charlie	42	95	175	Birmingham	2025-03-01
David	38	72	185	Leeds	2025-02-28
Eve	29	58	160	Sheffield	2025-03-03
Frank	45	88	190	Liverpool	2025-03-02
Grace	33	60	172	Newcastle	2025-03-01

(I never knew that. Every day's a school day)

Table sorting

The editable preview table (if First row is header toggle is checked) can now sort columns by clicking on the header row cells.

It does its best to determine whether a column is numeric, a date, or just plain text, and sorts accordingly.

Multiple output formats

PistonHeads format remains the default. This always will be primarily aimed at PH users. Otherwise I'd have to change the name of it

However, it wasn't a huge leap to extend the logic to other markup formats. So I did.

So it can now output tables as HTML, Markdown, BBCode, Wiki, AsciiDoc, reStructuredText grid (love this one), Textile, and LaTeX.

[jagnet]

Update: SQLite Import

This was a fun little one to implement. Niche, but I like it.

You can now import data and run SQL queries from an SQLite database

Running SQLite in the browser with JavaScript... what the WASM is this?!

Sample Database

You can download a sample database - chinook.db - from https://www.sqlitetutorial.net/sqlite-sample-datab...

Unzip it and drop chinook.db into the SQLite upload area. The app lists the tables and fields and you can quickly generate a preview or row count for each table.

Running this SQL query against chinook.db ...

SELECT
 tracks.albumid AS 'Album ID',
        artists.name AS 'Artist',
 title AS 'Album Title',
 COUNT(trackid) AS 'Tracks'
FROM
 tracks
INNER JOIN 
 albums ON albums.albumid = tracks.albumid
LEFT JOIN 
 artists ON albums.artistid = artists.artistid
WHERE
 artist <> 'U2'
GROUP BY
 tracks.albumid
HAVING 
 COUNT(trackid) >= 25
ORDER BY 
 tracks DESC;

Generates this table:

Album ID	Artist	Album Title	Tracks
141	Lenny Kravitz	Greatest Hits	57
23	Chico Buarque	Minha Historia	34
73	Eric Clapton	Unplugged	30
229	Lost	Lost, Season 3	26
230	Lost	Lost, Season 1	25
251	The Office	The Office, Season 3	25

Don't like the column names but don't want to edit the SQL query? Toggle off First row is header, edit the row, toggle back on. Nice.

[skwdenyer]

jagnet said:

==Update: SQLite Import==

This was a fun little one to implement. Niche, but I like it.

You can now import data and run SQL queries from an SQLite database

Running SQLite in the browser with JavaScript... what the WASM is this?!

Sample Database

You can download a sample database - chinook.db - from https://www.sqlitetutorial.net/sqlite-sample-datab...

Unzip it and drop chinook.db into the SQLite upload area. The app lists the tables and fields and you can quickly generate a preview or row count for each table.

Running this SQL query against chinook.db ...

SELECT
 tracks.albumid AS 'Album ID',
        artists.name AS 'Artist',
 title AS 'Album Title',
 COUNT(trackid) AS 'Tracks'
FROM
 tracks
INNER JOIN 
 albums ON albums.albumid = tracks.albumid
LEFT JOIN 
 artists ON albums.artistid = artists.artistid
WHERE
 artist <> 'U2'
GROUP BY
 tracks.albumid
HAVING 
 COUNT(trackid) >= 25
ORDER BY 
 tracks DESC;

Generates this table:

Album ID	Artist	Album Title	Tracks
141	Lenny Kravitz	Greatest Hits	57
23	Chico Buarque	Minha Historia	34
73	Eric Clapton	Unplugged	30
229	Lost	Lost, Season 3	26
230	Lost	Lost, Season 1	25
251	The Office	The Office, Season 3	25

Don't like the column names but don't want to edit the SQL query? Toggle off First row is header, edit the row, toggle back on. Nice.

Brilliant. First LaTeX output (for those 3 PHers who might have use of that and find your site), now SQL support.

Now all you need to do is knock up a psuedo-API to interact with the forums to update a PH-formatted table in a post in response to changes in data in the SQL and we could suddenly have forum posts offering, say, F1 live timing Obviously there'd need to be an automation server somewhere, and some hosting costs, and and...

[jagnet]

skwdenyer said:

Brilliant. First LaTeX output (for those 3 PHers who might have use of that and find your site)...

That many

skwdenyer said:

Now all you need to do is knock up a psuedo-API to interact with the forums to update a PH-formatted table in a post in response to changes in data in the SQL and we could suddenly have forum posts offering, say, F1 live timing Obviously there'd need to be an automation server somewhere, and some hosting costs, and and...

I'll pencil that in for v3

V2 is well on the way with a better layout, more comprehensive table editing and merged cell handling. But like all these things, the first 90% is the easy bit. So it's temporarily on hold whilst other projects take priority. Not that PH tables support merged cells; but it felt rude not to add it, for the 1 PHer who might have use of LaTeX and merged cells.

[ChevronB19]

More importantly, has Charlie lost any weight yet?

(But seriously, thanks for doing this - I think the hamsters owe you a t-shirt or some stickers at least!)

[jagnet]

ChevronB19 said:

More importantly, has Charlie lost any weight yet?

He appears to be a diet non-responder

ChevronB19 said:

(But seriously, thanks for doing this - I think the hamsters owe you a t-shirt or some stickers at least!)

Very much one of those fun projects that gives the brain a good workout.

[MesoForm]

I'm not sure if this is something that can be fixed or just isn't worth the hassle, but I just tried this tool with this spreadsheet


That has a time formatted column which comes out as something a bit funky -

Runner	Difference	Rank
A	Sun Dec 31 1899 00:00:02 GMT+0000 (Greenwich Mean Time)	1	'=RANK.EQ(B2,B$2:B$92,1)
B	Sun Dec 31 1899 00:00:02 GMT+0000 (Greenwich Mean Time)	2
C	Sun Dec 31 1899 00:00:04 GMT+0000 (Greenwich Mean Time)	3
D	Sun Dec 31 1899 00:00:08 GMT+0000 (Greenwich Mean Time)	4
E	Sun Dec 31 1899 00:00:14 GMT+0000 (Greenwich Mean Time)	5
F	Sun Dec 31 1899 00:00:27 GMT+0000 (Greenwich Mean Time)	6
G	Sun Dec 31 1899 00:00:27 GMT+0000 (Greenwich Mean Time)	6
H	Sun Dec 31 1899 00:00:31 GMT+0000 (Greenwich Mean Time)	8
I	Sun Dec 31 1899 00:00:35 GMT+0000 (Greenwich Mean Time)	9
J	Sun Dec 31 1899 00:00:36 GMT+0000 (Greenwich Mean Time)	10

(saved as .odf spreadsheet from OpenOffice Calc)

Saving it as Excel 2007-365 gives

Runner	Difference	Rank
A	0.0000231481481481481	1	'=RANK.EQ(B2,B$2:B$92,1)
B	0.0000231481481712963	2
C	0.0000462962963194444	3
D	0.0000925925925925926	4
E	0.000162037037037037	5
F	0.0003125	6
G	0.0003125	6
H	0.000358796296319444	8
I	0.000405092592592593	9
J	0.000416666666666667	10

(Which has just solved the problem I've been having as it seems the first two records are different when every other format I've been seeing it as says they're the same!)

[jagnet]

It's the way the tool is reading the underlying XML data in the .xlsx file. Excel stores both calculated values and the formula and it's treating the formula as an additional column.

So the easiest way to strip the formulas is to save a copy as a CSV file. As that's a plain-text format, only the computed values will be saved. Then upload that.

v2 of the site will have column controls, so you'd just be able to delete the unwanted column to leave just the values. But in the meantime, saving as CSV is the easiest option.

I'll have to have a play around to see what the underlying data looks like for the difference column - i suspect it may be related to trying to determine text / date / number for sorting. See what it comes out as when uploaded as a CSV.

Edited by jagnet on Monday 18th August 13:24

[jagnet]

Yep, as purely CSV data:

Runner,Difference,Rank
A,00:02,1
B,00:02,2
C,00:04,3
D,00:08,4
E,00:14,5
F,00:27,6
G,00:27,6
H,00:31,8
I,00:35,9
J,00:36,10

the difference column is fine:

Runner	Difference	Rank
A	00:02	1
B	00:02	2
C	00:04	3
D	00:08	4
E	00:14	5
F	00:27	6
G	00:27	6
H	00:31	8
I	00:35	9
J	00:36	10

[MesoForm]

jagnet said:

See what it comes out as when uploaded as a CSV.

Using the option 'Save cell content as shown' when saving as .csv from OpenOffice I get

Runner	Difference	Rank
A	00:02	1	'=RANK.EQ(B2,B$2:B$92,1)
B	00:02	2
C	00:04	3
D	00:08	4
E	00:14	5
F	00:27	6
G	00:27	6
H	00:31	8
I	00:35	9
J	00:36	10

Unticking that box I get

Runner	Difference	Rank
A	0.0000231481481481481	1	'=RANK.EQ(B2,B$2:B$92,1)
B	0.0000231481481712963	2
C	0.0000462962963194444	3
D	0.0000925925925925926	4
E	0.000162037037037037	5
F	0.0003125	6
G	0.0003125	6
H	0.000358796296319444	8
I	0.000405092592592593	9
J	0.000416666666666667	10

[jagnet]

Very odd that it's still saving the formula in a CSV file and not giving you any option to not have it there.

What happens if you choose the "Paste your Data" tab instead of file upload, and just copy/paste the cells straight from the spreadsheet into the textarea?

[jagnet]

UPDATED TO V2

After many, many hours writing JavaScript, Version 2 is finally here! This has pretty much been a complete overhaul - almost every part of the tool has been rebuilt from the ground up.

The biggest change is the table editor itself, but there are improvements across the board.

If you're new to it, the wizard ( ? icon, bottom right ) will walk you through everything.

I've added an email address in the footer - if you've found a bug or having trouble with a spreadsheet not rendering properly, pop a copy over to me and I'll take a look.

Live table editing

This will now give you a lot more ability to adjust things.

• Column controls: add / remove / re-order / rename / sort
• Undo / redo
• Cell merging (click "Selection Mode" first)
• Text formatting: uppercase, lowercase, title case
• Table cleanup: delete empty rows, column, duplicate rows
• Transpose table

Table markup

Depending on the markup format:

• Escape special characters
• Table captions
• Column alignment

Markup parsers

These can handle a lot more edge cases, especially LaTeX and reST parsers. Who knew that a CSV cell can be split over multiple lines and still be called valid? Thank you reST CSV tables for that little discovery.

Wizard

Use the ? icon in the bottom right to launch the walkthrough wizard.

Need a blank grid to start?

Use the "Create a New Grid" option, or copy and paste this blank 3x3 CSV grid:

,,
,,
,,

@MesoForm - this should now handle your time difference column correctly. During import it was treating those values as JavaScript Date objects (which is correct in that they are within Excel, but unhelpful for this purpose). So it now handles them as text strings.

Edited by jagnet on Wednesday 27th August 06:58

[skwdenyer]

jagnet said:

Very odd that it's still saving the formula in a CSV file and not giving you any option to not have it there.

What happens if you choose the "Paste your Data" tab instead of file upload, and just copy/paste the cells straight from the spreadsheet into the textarea?

That “formula” is plain text - it has a leading single quote. So it is correct that it is being exported within the CSV

[skwdenyer]

[jagnet]

skwdenyer said:

That “formula” is plain text - it has a leading single quote. So it is correct that it is being exported within the CSV

Ah OK. I guess it makes sense retaining it in a CSV for portability.

[jagnet]

Updates

A few updates to the site over the last week or so.

Save Option
You can now save tables to and load them from your browser's local storage. Local storage versus the server is handy as it means your tables stay private, but the downside is that it's only available on the device you saved it to.

Possibly in the future I'll add server storage, if there's any demand for it.

Download as Excel
Now available at the bottom of the table editor page. Previously you had to switch view, but this is more convenient, more intuitive, and it also respects merged cells. Switch view is now only available on the 'Table Markup to Data' view.

Lightweight Version
Dedicated Excel to Pistonheads version at phables.co.uk/excel-to-pistonheads - this gets rid of all the different output format options and the alternative data import methods, as well as the wizard. There's a lot of DOM nodes in the base page before even loading a table, so this helps keep things a bit snappier on mobiles and when dealing with larger tables.

Added an SQLite-powered Backend
I'll be able to add some knowledgebase how-to articles in the future. As the frontend lets you upload an SQLite database, it felt rude not to use it for the backend.

Dark Mode
Now respects system preference setting

Bug Fixes

• Some minor cell merging bugs sorted
• Removed the JavaScript optimizations for larger tables - more trouble than they were worth.
• Fixed some memory leaks