-
November 11th, 2020, 09:29 PM
#16
Re: Excel writing time is inconsistent.
48k xml file turning into 10mb excel file? I wonder what that script is doing that takes an hour and generates an output that is more than 200x the size of the input.
Always use [code][/code] tags when posting code.
-
November 12th, 2020, 07:23 AM
#17
Re: Excel writing time is inconsistent.
The OP won't/hasn't posted the XML file and the VB script. So....
In these cases, I'd be looking to read/process the XML file separately to (outside of) Excel, to produce say a csv file that can be imported into Excel with all the data and then using VB script/macros if needed for any required formatting. Without further info, I'd be surprised if this would take more than a few minutes.
All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!
C++23 Compiler: Microsoft VS2022 (17.6.5)
-
November 13th, 2020, 07:46 PM
#18
Re: Excel writing time is inconsistent.
Are you processing images in the file ? This can be very deceiving. Sometimes you see a 10mm x 10mm image, but it is actually 20MB in size. That once happened to me as the customer did not understand that stretching an image down to a small size does not change the image size on disk. I had a similar experience in processing times.
-
November 24th, 2020, 01:39 AM
#19
Re: Excel writing time is inconsistent.
Originally Posted by 2kaud
To parse and generate the Excel file shouldn't take anywhere near 30 minutes - never mind over an hour.
Can you post an XML file, the VB script and a link to a generated Excel file. There's quicker ways of doing this.
I have shared the sample input XML, script, and generated excel in the following link. Please follow the steps written in ReadMe.txt attached in the same link to execute the excel writing.
I have done the Excel writing in my machine with the same data attached is taking more than 1 hr. Please suggest your input to improve the performance of excel writing.
Link,
https://drive.google.com/drive/folde...iB?usp=sharing
Thanks in the advance.
-
November 24th, 2020, 04:07 AM
#20
Re: Excel writing time is inconsistent.
Ah. The generated spreadsheet from the xml file contains 170 sheets with many sheets containing images. All are heavily formatted as Engineering description pages. My previous experience of improving Excel load times from xml files only included dealing with one sheet and text/numbers. The used technique wouldn't be applicable here. Unfortunately in this case I can't help further. Sorry.
Perhaps some VB guru could suggest improvements to the VB code to increase performance.
All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!
C++23 Compiler: Microsoft VS2022 (17.6.5)
-
November 24th, 2020, 08:22 AM
#21
Re: Excel writing time is inconsistent.
If possible, the 170 sheets will process quicker if they are NOT VISIBLE during creation. Not sure if you are able to set this as an option PRIOR to generating the sheets. This has a dramatic speed increase when populating Grids with data ie, Grid.visible = false property is set to increase speed of generation.
I think regardless, 170 sheets with image is going to take a long time.
Why not schedule the generation to happen at 6am before the workers arrive. That way it really doesn't matter how long it takes.
-
November 24th, 2020, 09:30 AM
#22
Re: Excel writing time is inconsistent.
You might get better performance creating the spreadsheet outside of Excel using a tool such as Aspose Cells. See https://products.aspose.com/cells Although this is not a free solution 30-day evaluation licences are available. They are available for C++, Java & .Net(VB/c#) and require programming knowledge to use.
All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!
C++23 Compiler: Microsoft VS2022 (17.6.5)
-
November 24th, 2020, 09:55 AM
#23
Re: Excel writing time is inconsistent.
Originally Posted by George1111
If possible, the 170 sheets will process quicker if they are NOT VISIBLE during creation. Not sure if you are able to set this as an option PRIOR to generating the sheets. ...
Sure it is possible! I use it (hide the Excel application) while generating the sheet data. And I use it from C++ code via Excel automation.
Victor Nijegorodov
-
November 24th, 2020, 11:39 AM
#24
Re: Excel writing time is inconsistent.
And I use it from C++ code via Excel automation.
That's another way to approach this issue. Create the required spreadsheet using Excel automation from C++/VB.net/c#
All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!
C++23 Compiler: Microsoft VS2022 (17.6.5)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|