search

gristlabs / grist-core

Grist is the evolution of spreadsheets.
https://www.getgrist.com
Apache License 2.0
7.23k stars 321 forks source link

Could Excel 'Tables' be 'first class citizens' in import and export ? #947

Open jgranduel opened 6 months ago

jgranduel commented 6 months ago

Hi,

after discovering Power Query a few years ago, I use Excel tables whenever I can instead of classical ranges. They provide many advantages and make Excel dataset closer to databases. Tables are named. When importing data from Excel using Power Query, tables are fully recognized as like sheets. A few library recognize them, like Apache POI or ExcelJS for the ones I know.

Would it be possible for grist to recognize tables and load them directly into SQLite tables ? and on the other way round, could Grist tables be exported as tables ?

Thanks for this project!

dsagal commented 6 months ago

We do use ExcelJS for exports already, so that direction may be easier. I personally have little experience with Excel tables. Is there any downside to always exporting Grist data as "tables"? Or should it be an option?

For imports, we use openpyxl (on Python side). Don't know if that supports tables, or how you'd want to present the options. I'd guess that data in a "table" would also be available as part of the sheet that contains this table, so would it create confusion to the user what's being imported where?

jgranduel commented 6 months ago

Hi,

thanks a lot for taking my question into consideration. ExcelJS handles Excel tables correctly as far as I know (I often run a little script from NodeJS to extract Excel tables into json).

I don't see any downside from my point of view. I am not an expert in any case. I have just noticed that there is a payload in XML which makes the size of the resulting Excel a little bigger. That might vary with the data length of course. Power Query only creates tables, but reads cells, ranges, tables or whole sheets. People who don't know tables might be suprised by the layout, or some constraints (not being able to merge cells for instance), but that's really a minor thing and tables have been here for almost 15 years... All together, I think it should really be the default option.

I don't use Python. I tried nevertheless to load a workbook with openpyxl and it works well (I followed this example on SOF).

So my conclusion : I do hope you can implement tables in Grist 😉 !