Cool, now it's optimized and it manages to export up to a million lines in 30 seconds. What do you propose to do when the next customer wants to copy 1.5 million lines?
> What do you propose to do when the next customer wants to copy 1.5 million lines?
Thankfully, that can't happen :). XLSX has a row limit of 1,048,576 rows.
In all seriousness, I don't have an answer for you other than that the developer that's looking to shove >1M excel rows around should find a better solution than the clipboard. I've done more than my fair share of work pushing excel spreadsheets around (and I've even worked on Excel add-ins). There are plenty of performant ways of accessing large volumes of Excel data, none of them involve the clipboard. Most of them even work on non-Windows platforms.
The arbitrary row & column limits in Excel (XLS was pretty small, XLSX is quite a bit bigger, but still arbitrary) are annoying and I cannot begin to tell you how many hours over the years I've wasted due to this limitation or Excel silently truncating large CSV files on import.
Indeed - it would be very possible to make a version of Excel which can deal with infinite-sized sheets by leaving most of the data on disk and only loading a few screenfulls at once.
"Very possible" reads to me a bit cheat-y, in that you could do so but you probably don't want or need to. Paging a workbook out to disk in this way necessitates keeping a cell-to-cell relational map for everything and then paging it back in when you then need to pointer-chase through a set of updates--well, you've just reinvented virtual memory but worse, haven't you?
Excel is an intensely business-critical product and has a lot of smart folks working on it. There are tradeoffs you're eliding and I'm confident that they know of them.
Editing a text file can be done in such a way that editing/adding/removing lines has no effect on any untouched lines. In an Excel spreadsheet, changing a single cell can result in a cascade of updates all over the place, in a contrived scenario it could result in all other cells being updated.
