CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 2 of 2 FirstFirst 12
Results 16 to 25 of 25

Thread: Excel writing time is inconsistent.

  1. #16
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,360

    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.

  2. #17
    2kaud's Avatar
    2kaud is offline Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    7,319

    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++17 Compiler: Microsoft VS2019 (16.8.2)

  3. #18
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,889

    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.

  4. #19
    Join Date
    Nov 2020
    Location
    Chennai
    Posts
    7

    Re: Excel writing time is inconsistent.

    Quote Originally Posted by 2kaud View Post
    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.

  5. #20
    2kaud's Avatar
    2kaud is offline Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    7,319

    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++17 Compiler: Microsoft VS2019 (16.8.2)

  6. #21
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,889

    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.

  7. #22
    2kaud's Avatar
    2kaud is offline Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    7,319

    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++17 Compiler: Microsoft VS2019 (16.8.2)

  8. #23
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,844

    Re: Excel writing time is inconsistent.

    Quote Originally Posted by George1111 View Post
    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

  9. #24
    2kaud's Avatar
    2kaud is offline Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    7,319

    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++17 Compiler: Microsoft VS2019 (16.8.2)

Page 2 of 2 FirstFirst 12

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)