Prev: vb.net and sql reporting services report
Next: System.IO.File.Exists in Windows 7
From: Stephen Plotnick on 30 Oct 2009 01:19
I developed a program in VB 2008 that takes a flat text file with 10 column
and several rows (Around 1000 now but it could get much larger).

All 1000 go onto tab 1, from there based on a value in a cell the record is
also written to another tab. There are around 5 other tabs.

My program goes real slow, can take 20 minutes. I turned off visual=no so I
could see it getting built. I can see on cell at a time going into the
spreadsheet at a snail's pace. I am parsing the text file into each cell and
placing into the spreadsheet.

My question is it seems real slow to me. I would think the cells being
placed with the parsed data would fly into the spreadsheet and should take a
few seconds. Is my time normal? If not I surely can paste code where I'm
parsing a putting data into spreadsheet.

Thanks in advance.



From: Tom Shelton on 30 Oct 2009 02:03
On 2009-10-30, Stephen Plotnick <thepla(a)thepla.com> wrote:
> I developed a program in VB 2008 that takes a flat text file with 10 column
> and several rows (Around 1000 now but it could get much larger).
>
> All 1000 go onto tab 1, from there based on a value in a cell the record is
> also written to another tab. There are around 5 other tabs.
>
> My program goes real slow, can take 20 minutes. I turned off visual=no so I
> could see it getting built. I can see on cell at a time going into the
> spreadsheet at a snail's pace. I am parsing the text file into each cell and
> placing into the spreadsheet.
>
> My question is it seems real slow to me. I would think the cells being
> placed with the parsed data would fly into the spreadsheet and should take a
> few seconds. Is my time normal? If not I surely can paste code where I'm
> parsing a putting data into spreadsheet.
>
> Thanks in advance.

If this is going to excel 2003 or 2007, why not just output your spreadsheet
as SpreadsheetML and then fire up excel? Unless you need to embed scripting,
there is not much that you can't do once you understand the office schema....

If there is a feature I don't know how to represent in XML and can't quite
figure out from the reference schema, I will just create a simple spreadsheet
with that feature and save it as xml. Then you can open in it in a simple
text editor and see how it was implemented :)

--
Tom Shelton
From: eBob.com on 30 Oct 2009 11:09
I generate a spreadsheet of thousands of rows and it sure doesn't have a
problem such as you describe. I'd recommend using Task Manager to see if
the delay is CPU or I/O. I'd also look to see if maybe you are causing a
lot of paging. And finally I'd comment out the code which is making the
Excel calls to see if the problem is in your code or in Excel.

Good Luck, Bob

"Stephen Plotnick" <thepla(a)thepla.com> wrote in message
news:B74D6F4F-2B1B-4BD1-9B21-075079727FFB(a)microsoft.com...
>I developed a program in VB 2008 that takes a flat text file with 10 column
>and several rows (Around 1000 now but it could get much larger).
>
> All 1000 go onto tab 1, from there based on a value in a cell the record
> is also written to another tab. There are around 5 other tabs.
>
> My program goes real slow, can take 20 minutes. I turned off visual=no so
> I could see it getting built. I can see on cell at a time going into the
> spreadsheet at a snail's pace. I am parsing the text file into each cell
> and placing into the spreadsheet.
>
> My question is it seems real slow to me. I would think the cells being
> placed with the parsed data would fly into the spreadsheet and should take
> a few seconds. Is my time normal? If not I surely can paste code where I'm
> parsing a putting data into spreadsheet.
>
> Thanks in advance.
>
>
>


From: Ato_Zee on 30 Oct 2009 11:43

On 30-Oct-2009, "eBob.com" <fakename(a)totallybogus.com> wrote:

> > My question is it seems real slow to me. I would think the cells being
> > placed with the parsed data would fly into the spreadsheet and should
> > take
> > a few seconds. Is my time normal?

I also do large Excel spreadsheets, with lots of conditionals, and
also ran into the speed issue. Same in XP Pro 32bit, 64bit,
Win7 32bit.
Ended up going to an Intel Extreme processor (the larger
processor cache makes a difference), 4GB Crucial
(3.19GB useable), Asus mobo, fixed size pagefile on
a different drive D:\, to the OS drive C:\.
Processor made the biggest difference, followed by
RAM size, fixed size pagefile marginal, but useful
improvement.
TURN OFF screen updating.
Can't understand why the MS dipsticks in Redmond
can't code in 64bit, but I guess we understand that
from the mess they made of Vista.
Time to put Balmar out to graze?
It beggars belief that large Excel spreadsheets need a
gaming LAN Party class machine.
From: Family Tree Mike on 30 Oct 2009 12:09


"Stephen Plotnick" wrote:

> I developed a program in VB 2008 that takes a flat text file with 10 column
> and several rows (Around 1000 now but it could get much larger).
>
> All 1000 go onto tab 1, from there based on a value in a cell the record is
> also written to another tab. There are around 5 other tabs.
>
> My program goes real slow, can take 20 minutes. I turned off visual=no so I
> could see it getting built. I can see on cell at a time going into the
> spreadsheet at a snail's pace. I am parsing the text file into each cell and
> placing into the spreadsheet.
>
> My question is it seems real slow to me. I would think the cells being
> placed with the parsed data would fly into the spreadsheet and should take a
> few seconds. Is my time normal? If not I surely can paste code where I'm
> parsing a putting data into spreadsheet.
>
> Thanks in advance.
>
>

Are you using early or late binding? Late binding is considerably slower
than early binding.

Mike

 |  Next  |  Last
Pages: 1 2
Prev: vb.net and sql reporting services report
Next: System.IO.File.Exists in Windows 7