search

ivankokan / Excel2LaTeX

The Excel add-in for creating LaTeX tables
Other
1.27k stars 147 forks source link

Poor performance with more than 1000 cells #4

Closed krlmlr closed 8 years ago

krlmlr commented 8 years ago

noticed by a Stackoverflow user (see edits, revision 8).

Relevance aside, but could it be possible to speed up things? In particular, does the new StringBuilder have amortized linear complexity in the number of strings/characters involved? I'd really love to revert that SO edit ;-)

chelh commented 8 years ago

The StringBuilder doesn't seem to make that much of an improvement, at least on Windows. The big performance problem is with Range.Text and Range.DisplayFormat; Excel just seems to have a really bad implementation for these. I'll experiment with eliminating the Range.Text calls and reducing the Range.DisplayFormat calls to once per column, but I've tested those waters before with poor results.

I'm tempted to wontfix this, and just basically say “you can preserve your Excel formatting in LaTeX (Excel2LaTeX) or you can export a huge table (pgfplotstable), but you can't do both.”

krlmlr commented 8 years ago

Thanks. Just iterating over 160000 cells (10 full rows) and querying Text and Interior.Color takes 7 seconds on my machine, so I guess 22k cells per second is the best we can do performance-wise; that doesn't include any transformation or concatenation yet. Let's leave it at that.

chelh commented 8 years ago

Okay, scratch what I said about Range.Text and Range.DisplayFormat being the bottlenecks. It appears that speed increases tenfold if I comment out the following line in AddCell():

If Len(txt) > 0 Then bRequiresDifferentVlineFormat = RequiresDifferentVlineFormat(rCell)

So I can definitely investigate RequiresDifferentVlineFormat() more, and at the very least skip over it if the entire table shows no variance in vlines (which should be the case for most large tables).

chelh commented 8 years ago

My recent commit seems to have done the trick (5000 cells now convert in 2 seconds, was 1 minute before).

krlmlr commented 8 years ago

Awesome! Are you going to edit the SO answer again?