Major performance improvements using ChilkatXML

[ggreen86]

Major performance improvements using ChilkatXML

[SeregaKR]

Personally I'm definitely interested! Please upload it if you can! We have to get some information from Excel files at work. It's a long and painful process especially when we have to parse through 300 - 500 files a time.

[bzaner]

Dan, Since you are intimately involved with the class and I am just trying to use it for the minimal purpose of producing an xlsx file with there fields from a dbf. I want to rename the fields with 20 character field names and I want to change the format of 2 of the fields to $. I also want to distribute the code within the system I sell and including the correct other modules was something of a process. It seemed like a simple task initially but turned out to be almost impossible for me so, I used another method that doesn’t complete the task but produces the xlsx formatted file. Can you help? Thanks, Byron

…

_______________________________ Construction Software… …turning your time into profit Direct: 702-703-4836 Toll Free: 888-394-0252 http://www.priosoft.com <https://www.facebook.com/priosoft/> f <https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2fwww.linkedin.com%2fcompany%2f1545521%3ftrk%3dtyah&data=01%7c01%7csakris%40064d.mgd.microsoft.com%7ce6313a46af2f4df9d42108d352be3a92%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=d8N7U2LHl2tvFcxzVSJxPh9r4YM%2fxHU%2fEVv719fq07o%3d> <https://na01.safelinks.protection.outlook.com/?url=http%3a%2f%2ftwitter.com%2fpriosoft&data=01%7c01%7csakris%40064d.mgd.microsoft.com%7ce6313a46af2f4df9d42108d352be3a92%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=RLbGW3sfRJcBF25ZpTn5ue6MbG6xTWtmDTbUjXFOWhU%3d> <https://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fplus.google.com%2f105070244357160453152%2fposts&data=01%7c01%7csakris%40064d.mgd.microsoft.com%7ce6313a46af2f4df9d42108d352be3a92%7c72f988bf86f141af91ab2d7cd011db47%7c1&sdata=33ZP%2b5GfXP1oQbxDxP3AYglacR4Kff1MvImO8aM%2fqcQ%3d> <https://www.youtube.com/c/priosoft> From: [email protected] <[email protected]> On Behalf Of Dan Lauer Sent: Monday, March 19, 2018 9:34 PM To: ggreen86/XLXS-Workbook-Class <[email protected]> Cc: Subscribed <[email protected]> Subject: [ggreen86/XLXS-Workbook-Class] Major performance improvements using ChilkatXML (#14) First off, thanks to all involved in developing this class. It's very cool, and the work you've done is anything but trivial. It's amazing. For what it's worth, I began investigating this class because I needed to consume a fairly data intensive .xlsx file containing product profiles for ecommerce. The manufacturer provides the data to merchants in this format only. The are currently about 4000 rows and 76 columns with very heavy string content in many columns. In my initial test, with the class as provided, .openXLSXWorkbook() took at least an hour and 30 minutes on a quad core i7 with 16gb RAM and a 1TB SSD drive. So I started poking around. I subclassed and replaced a fairly small amount of code in .readSharedStringsXML() and .readSheetXML(). I replaced the approach that uses STREXTRACT() to parse the XML with calls to the excellent and speedy Chilkat <https://www.chilkatsoft.com/products.asp> XML parser. I'm now able the process the identical .openXLSXWorkbook() call in an average of 52 seconds. Yes...that's correct...1:30:00 to 00:00:52. Identical results. Happy to share the code changes with anyone that's interested. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#14> , or mute the thread <https://github.com/notifications/unsubscribe-auth/AgfnXqNuBnoXP8h2qWbLikY8mKY_7NtSks5tgFyUgaJpZM4SxKAN> . <https://github.com/notifications/beacon/AgfnXgGDL_Vv-NeQqOsgvZcpGsGOJWVvks5tgFyUgaJpZM4SxKAN.gif>

[DanLauer]

@bzaner Send me your contact info at [email protected]. I'll respond with mine and we can work something out.

@SeregaKR I'll work on a more presentable version with commented changes to submit.

[DanLauer]

@bzaner Have you looked at tabletoexcel.prg in the download .zip file? Seems like that gets you a long way to where you want to go.

[CHamlinPNP]

@DanLauer can you please share with me the changes you made to use the Chilkat libraries. I also use them and would be greatly interested in seeing what you have done. I also changed the CreateExcel method to use the Chilkat zipping methods for great speed increase.

[ggreen86]

I am getting a new release ready (Release 32) which adds more functionality. But this release has drastically changed how the XML files are now being processed during opening xlsx files. I have abandoned the use of STREXTRACT() and now using pointers to the XML string (via SYS(2600)) to parse it with vast speed improvements.

[ggreen86]

Please see Release 32. This release provides methods that you can override by creating a subclass of this class and implement your own code for handling of zip files.