Re:Benchmarking Spreadsheet Systems
by sheetjs on Thursday December 12, 2019 @07:16PM
(#21777953)
Performance is affected by a myriad of factors, many of which aren't visible by looking at the worksheet.
For example, XLS/XLSX support a special "shared formula" representation which tells Excel that a formula is structurally similar to other formulae. This is not always written, depending on how the file was generated and other factors. Without that, Excel will take an "inefficient" calculation approach. You can't figure it out from the UI or by inspecting the formula text -- you actually have to dig into the file to see it.
Re:Benchmarking Spreadsheet Systems
by rolling_roland on Friday December 13, 2019 @03:34AM
(#21780035)
I was struggling with spreadsheet performance some time ago, trying to open csv with over million of rows, which made my excel cough. To help with this I built collection of tools for myself (https://blocksheet.io) so I could at least split my csv files to smaller ones and then edit with excel.
>In a similar vein, sorting causes problems on very small datasets (less than 10K rows):
Reading this made me wonder just how much excel prioritizes formulas and other fancy features over basic utilities such as sorting. For comparison, I just tried sorting csv with over 2 million rows on blocksheet and even though it took few seconds and made my laptop fan do extra jumps (it's a static site), it still managed to do it in reasonable time.
For me it's such a rare problem to run into huge spreadsheets, that it's a bit overkill to build my own software for it. So if anyone knows any good ones that deal well with rearranging large spreadsheets, I'd be happy to hear about them.
Re:Benchmarking Spreadsheet Systems
by conductr on Friday December 13, 2019 @01:09AM
(#21779475)
What I always remind people is, Microsoft has no intention of fixing these types of performance in Excel because they have other products to sell you if you have “big data”
Re:Benchmarking Spreadsheet Systems
by tln on Thursday December 12, 2019 @08:29PM
(#21778351)
I always regret it when opening something in Numbers. Excel and Gsheets have a lot of shared idioms that make gaining and keeping expertise far easier and those two platforms start up faster and have less annoying barriers to working at speed (eg, interstitials).
For example, XLS/XLSX support a special "shared formula" representation which tells Excel that a formula is structurally similar to other formulae. This is not always written, depending on how the file was generated and other factors. Without that, Excel will take an "inefficient" calculation approach. You can't figure it out from the UI or by inspecting the formula text -- you actually have to dig into the file to see it.
The OpenXML basis format for XSLX is an ISO standard. Most things are documented albeit in a somewhat obtuse way.
https://blog.jpalardy.com/posts/alternative-to-sort-uniq-c/
>In a similar vein, sorting causes problems on very small datasets (less than 10K rows):
Reading this made me wonder just how much excel prioritizes formulas and other fancy features over basic utilities such as sorting. For comparison, I just tried sorting csv with over 2 million rows on blocksheet and even though it took few seconds and made my laptop fan do extra jumps (it's a static site), it still managed to do it in reasonable time.
For me it's such a rare problem to run into huge spreadsheets, that it's a bit overkill to build my own software for it. So if anyone knows any good ones that deal well with rearranging large spreadsheets, I'd be happy to hear about them.