search

ggreen86 / XLSX-Workbook-Class

VFP Class to Create an XLSX Workbook without Excel Automation or Installed
45 stars 16 forks source link

the output is very slow #100

Closed yoncen99 closed 5 months ago

yoncen99 commented 9 months ago

When the record exceeds tens of thousands, the output is very slow and has not been completed for several hours. Please optimize it, thank you.

ggreen86 commented 9 months ago

What methods are you using to create the output? Thank you.

From: yoncen99 @.> Sent: Wednesday, November 15, 2023 1:01 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: Subscribed @.***> Subject: [ggreen86/XLSX-Workbook-Class] the output is very slow (Issue #100)

When the record exceeds tens of thousands, the output is very slow and has not been completed for several hours. Please optimize it, thank you.

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/100, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33JQUWFM5C7AZL6I3Z3YERK75AVCNFSM6AAAAAA7L6GY4KVHI2DSMVQWIX3LMV43ASLTON2WKOZRHE4TIMJQGM2TMMA. You are receiving this because you are subscribed to this thread.Message ID: @.**@.>>

yoncen99 commented 9 months ago

What methods are you using to create the output? Thank you. From: yoncen99 @.> Sent: Wednesday, November 15, 2023 1:01 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: Subscribed @.> Subject: [ggreen86/XLSX-Workbook-Class] the output is very slow (Issue #100) When the record exceeds tens of thousands, the output is very slow and has not been completed for several hours. Please optimize it, thank you. — Reply to this email directly, view it on GitHub<#100>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33JQUWFM5C7AZL6I3Z3YERK75AVCNFSM6AAAAAA7L6GY4KVHI2DSMVQWIX3LMV43ASLTON2WKOZRHE4TIMJQGM2TMMA. You are receiving this because you are subscribed to this thread.Message ID: @*.**@*.***>> Code: Local loListener loListener = Newobject('vfpxworkbookxlsx', 'vfpxworkbookxlsx.vcx') loListener.SaveTabletoWorkbook(tcCurTable, lcFileName, .T., .T.) Release loListener

You can try outputting 100000 records to Excel and see if the output fails without a prompt, and the system is not responding.

ggreen86 commented 9 months ago

Hello—

If you want to write a table or a grid directly to a workbook, then you should use the following methods (note the EX in the name):

SaveGridToWorkbookEx() SaveTableToWorkbookEx()

These methods are designed to write directly to the workbook xml files and have been optimized for this.

The method that you are using (SaveTableToWorkbook) will first write the table contents to internal cursors; then it has the option to save directly. This method is designed to allow the developer to write a table to the cursors and then modify the contents before saving to the workbook file. So, it will take considerably longer to save the workbook.

I did a test with the below code (note I am using SaveTableToWorkbookEx) and saved a table with 20 columns and 36K rows to a workbook slightly less than 30 seconds. The columns are also mixed data types – numeric, character, Boolean, and date/datetime.

LOCAL loExcel, lcTable, lcExcel, lnTime lcTable = GETFILE("DBF") IF FILE(lcTable) lcExcel = FORCEEXT(lcTable, "xlsx") loExcel = NEWOBJECT("VFPxWorkbookXLSX", "e:\my work\foxpro\projects\workbookxlsx\VFPxWorkbookXLSX.vcx") lnTime = SECONDS() loExcel.SaveTableToWorkbookEx(lcTable, lcExcel, .NULL., .T.) ?SECONDS()-lnTime ENDIF

Greg

From: yoncen99 @.> Sent: Wednesday, November 15, 2023 7:06 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] the output is very slow (Issue #100)

What methods are you using to create the output? Thank you. From: yoncen99 @.> Sent: Wednesday, November 15, 2023 1:01 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: Subscribed @.> Subject: [ggreen86/XLSX-Workbook-Class] the output is very slow (Issue #100https://github.com/ggreen86/XLSX-Workbook-Class/issues/100) When the record exceeds tens of thousands, the output is very slow and has not been completed for several hours. Please optimize it, thank you. — Reply to this email directly, view it on GitHub<#100https://github.com/ggreen86/XLSX-Workbook-Class/issues/100>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33JQUWFM5C7AZL6I3Z3YERK75AVCNFSM6AAAAAA7L6GY4KVHI2DSMVQWIX3LMV43ASLTON2WKOZRHE4TIMJQGM2TMMA. You are receiving this because you are subscribed to this thread.Message ID: @.@.***>> Code: Local loListener loListener = Newobject('vfpxworkbookxlsx', 'vfpxworkbookxlsx.vcx') loListener.SaveTabletoWorkbook(tcCurTable, lcFileName, .T., .T.) Release loListener

You can try outputting 100000 records to Excel and see if the output fails without a prompt, and the system is not responding.

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/100#issuecomment-1813493298, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33LSUEWUQY65HLF5MADYEVKHPAVCNFSM6AAAAAA7L6GY4KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJTGQ4TGMRZHA. You are receiving this because you commented.Message ID: @.**@.>>

yoncen99 commented 9 months ago

When calling the SaveTableToWorkbookEx method, an error message is displayed: the zip file is missing or empty.

yoncen99 commented 9 months ago

When using the SaveTableToWorkbookEx method instead, if there are too many records, it will still be slow. Compared to the copy to method, it is much slower. For users, an output time of less than 1 minute is acceptable, but more than 1 minute is not very friendly.

jhernancanom commented 9 months ago

Please, friend yoncen99: be patient !!!

This utility that Greg provide us is for helping us, not for we start to criticize it, specifically if our data/database is big.

yoncen99 commented 9 months ago

Please, friend yoncen99: be patient !!!

This utility that Greg provide us is for helping us, not for we start to criticize it, specifically if our data/database is big.

Yes. I didn't criticize him, just gave feedback on the test results and asked him to improve. Thank you. Thank you for the work he has done for vfp developers.

ggreen86 commented 9 months ago

The code is writing to the TEMP directory for the current user; please ensure that this is a local directory for the user and not a network drive. I use the function SYS(2023) to determine this.

From: yoncen99 @.> Sent: Thursday, November 16, 2023 10:31 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] the output is very slow (Issue #100)

Please, friend yoncen99: be patient !!!

This utility that Greg provide us is for helping us, not for we start to criticize it, specifically if our data/database is big.

Yes. I didn't criticize him, just gave feedback on the test results and asked him to improve. Thank you. Thank you for the work he has done for vfp developers.

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/100#issuecomment-1815694317, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33L4HGN4E2TE6NJ5FFLYE3K5ZAVCNFSM6AAAAAA7L6GY4KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJVGY4TIMZRG4. You are receiving this because you commented.Message ID: @.**@.>>

ggreen86 commented 9 months ago

I do not get the error – the table is successfully exported to XLSX format. You will have to debug this as I cannot duplicate your problem. You can try sending the table to me; just remove any sensitive data from it first.

From: yoncen99 @.> Sent: Thursday, November 16, 2023 7:10 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] the output is very slow (Issue #100)

When calling the SaveTableToWorkbookEx method, an error message is displayed: the zip file is missing or empty.

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/100#issuecomment-1815526274, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33OOVHT353OYEOA7J73YE2TNXAVCNFSM6AAAAAA7L6GY4KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJVGUZDMMRXGQ. You are receiving this because you commented.Message ID: @.**@.>>

ggreen86 commented 9 months ago

You will have to translate the form.

From: yoncen99 @.> Sent: Thursday, November 16, 2023 7:47 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] the output is very slow (Issue #100)

[image]https://user-images.githubusercontent.com/150887477/283634859-766d3b17-3465-4393-8825-73cd05285f4b.png

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/100#issuecomment-1815559349, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33JXEWWEFFXDQP75P2LYE2XZVAVCNFSM6AAAAAA7L6GY4KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJVGU2TSMZUHE. You are receiving this because you commented.Message ID: @.**@.>>

yoncen99 commented 9 months ago

Using the SaveTableToWorkbookEx method for output is also not feasible. The system has been outputting the state, but the file has not been generated. If only a few hundred pieces of data are output, there is no problem.

ggreen86 commented 9 months ago

If you want me to try and find the issue that you are having, you will need to provide a sample table and the code that you are using to save to xlsx format. I do not have any issues when writing a large table to xlsx format on my laptop.

As I said before, make sure that your TEMP directory is a local file. Also, try saving to a local file (xlsx) if you are trying to save to a network share. Another consideration is if the table that you are writing to is on a network drive, this will also take more time.

From: yoncen99 @.> Sent: Sunday, November 19, 2023 7:04 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] the output is very slow (Issue #100)

Using the SaveTableToWorkbookEx method for output is also not feasible. The system has been outputting the state, but the file has not been generated. If only a few hundred pieces of data are output, there is no problem.

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/100#issuecomment-1818024347, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33MU34HBRRDOEJB2NNTYFKNARAVCNFSM6AAAAAA7L6GY4KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJYGAZDIMZUG4. You are receiving this because you commented.Message ID: @.**@.>>

yoncen99 commented 9 months ago

If you want me to try and find the issue that you are having, you will need to provide a sample table and the code that you are using to save to xlsx format. I do not have any issues when writing a large table to xlsx format on my laptop. As I said before, make sure that your TEMP directory is a local file. Also, try saving to a local file (xlsx) if you are trying to save to a network share. Another consideration is if the table that you are writing to is on a network drive, this will also take more time. From: yoncen99 @.> Sent: Sunday, November 19, 2023 7:04 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] the output is very slow (Issue #100) Using the SaveTableToWorkbookEx method for output is also not feasible. The system has been outputting the state, but the file has not been generated. If only a few hundred pieces of data are output, there is no problem. — Reply to this email directly, view it on GitHub<#100 (comment)>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33MU34HBRRDOEJB2NNTYFKNARAVCNFSM6AAAAAA7L6GY4KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJYGAZDIMZUG4. You are receiving this because you commented.Message ID: @.**@.>>

If you want me to try and find the issue that you are having, you will need to provide a sample table and the code that you are using to save to xlsx format. I do not have any issues when writing a large table to xlsx format on my laptop. As I said before, make sure that your TEMP directory is a local file. Also, try saving to a local file (xlsx) if you are trying to save to a network share. Another consideration is if the table that you are writing to is on a network drive, this will also take more time. From: yoncen99 @.> Sent: Sunday, November 19, 2023 7:04 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] the output is very slow (Issue #100) Using the SaveTableToWorkbookEx method for output is also not feasible. The system has been outputting the state, but the file has not been generated. If only a few hundred pieces of data are output, there is no problem. — Reply to this email directly, view it on GitHub<#100 (comment)>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33MU34HBRRDOEJB2NNTYFKNARAVCNFSM6AAAAAA7L6GY4KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJYGAZDIMZUG4. You are receiving this because you commented.Message ID: @.**@.>>

May I ask how to send the file to you via email? Thanks.

ggreen86 commented 9 months ago

Please attach the table to the email from github as a zip file. In the table, do a replace on any field contents that is sensitive before attaching. Also, please provide the codepage that you are using.

From: yoncen99 @.> Sent: Tuesday, November 28, 2023 9:07 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] the output is very slow (Issue #100)

If you want me to try and find the issue that you are having, you will need to provide a sample table and the code that you are using to save to xlsx format. I do not have any issues when writing a large table to xlsx format on my laptop. As I said before, make sure that your TEMP directory is a local file. Also, try saving to a local file (xlsx) if you are trying to save to a network share. Another consideration is if the table that you are writing to is on a network drive, this will also take more time. From: yoncen99 @.> Sent: Sunday, November 19, 2023 7:04 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] the output is very slow (Issue #100https://github.com/ggreen86/XLSX-Workbook-Class/issues/100) Using the SaveTableToWorkbookEx method for output is also not feasible. The system has been outputting the state, but the file has not been generated. If only a few hundred pieces of data are output, there is no problem. — Reply to this email directly, view it on GitHub<#100 (comment)https://github.com/ggreen86/XLSX-Workbook-Class/issues/100#issuecomment-1818024347>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33MU34HBRRDOEJB2NNTYFKNARAVCNFSM6AAAAAA7L6GY4KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJYGAZDIMZUG4. You are receiving this because you commented.Message ID: @.@.>>

If you want me to try and find the issue that you are having, you will need to provide a sample table and the code that you are using to save to xlsx format. I do not have any issues when writing a large table to xlsx format on my laptop. As I said before, make sure that your TEMP directory is a local file. Also, try saving to a local file (xlsx) if you are trying to save to a network share. Another consideration is if the table that you are writing to is on a network drive, this will also take more time. From: yoncen99 @.> Sent: Sunday, November 19, 2023 7:04 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] the output is very slow (Issue #100https://github.com/ggreen86/XLSX-Workbook-Class/issues/100) Using the SaveTableToWorkbookEx method for output is also not feasible. The system has been outputting the state, but the file has not been generated. If only a few hundred pieces of data are output, there is no problem. — Reply to this email directly, view it on GitHub<#100 (comment)https://github.com/ggreen86/XLSX-Workbook-Class/issues/100#issuecomment-1818024347>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33MU34HBRRDOEJB2NNTYFKNARAVCNFSM6AAAAAA7L6GY4KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJYGAZDIMZUG4. You are receiving this because you commented.Message ID: @.@.>>

May I ask how to send the file to you via email? Thanks.

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/100#issuecomment-1831076720, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33PCHBI5PFGPZLOFEUTYG2KFZAVCNFSM6AAAAAA7L6GY4KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMZRGA3TMNZSGA. You are receiving this because you commented.Message ID: @.**@.>>

SeregaKR commented 9 months ago

Can there be an error due to the final filename? In my case, if I name the output file smth like "report_form_12.11_11.12.xlsx" I get an error in my program, because the generated file was "report_form_12.11_11.xlsx"

The version I'm using now is R39

ggreen86 commented 8 months ago

Sergei—

There is not an error in the class. The file name choice is the problem. When I create the target file on the file system, I create it as a zip file in the directory that is provided during the initial create of the workbook. The program uses the VFP command FORCEEXT() to ensure the target file is created with a zip extension. Since you file name below has two period in the name, the VFP command FORCEEXT() interprets the 11_11.12 as being the current extension and forces the file to be named “report_form_12.zip” which is later renamed to “report_form_12.xlsx”.

This behavior is part of VFP and I cannot change it. I would have to write my own FORCEEXT method which I would rather not due since it may introduce other bugs that would have to be coded for.

You will need to ensure that only one period “.” Is in the file name which separates the root name from the extension name.

Greg

From: Sergei @.> Sent: Monday, December 11, 2023 6:22 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] the output is very slow (Issue #100)

Can there be an error due to the final filename? In my case, if I name the output file smth like "report_form_12.11_11.12.xlsx" I get an error in my program, because the generated file was "report_form_12.11_11.xlsx"

The version I'm using now is R39

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/100#issuecomment-1849873332, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33O5QQT2Z5VNDO3JFV3YI3UHLAVCNFSM6AAAAAA7L6GY4KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNBZHA3TGMZTGI. You are receiving this because you commented.Message ID: @.**@.>>

SeregaKR commented 8 months ago

Sergei— There is not an error in the class. The file name choice is the problem. When I create the target file on the file system, I create it as a zip file in the directory that is provided during the initial create of the workbook. The program uses the VFP command FORCEEXT() to ensure the target file is created with a zip extension. Since you file name below has two period in the name, the VFP command FORCEEXT() interprets the 11_11.12 as being the current extension and forces the file to be named “report_form_12.zip” which is later renamed to “report_form_12.xlsx”. This behavior is part of VFP and I cannot change it. I would have to write my own FORCEEXT method which I would rather not due since it may introduce other bugs that would have to be coded for.

Thank, you for the reply. I solved this problem by introducing random name with 20 Latin small letters and then using built in RENAME VFP function. But thank you for letting me know

ggreen86 commented 5 months ago

Closing Issue.