 |
|
| .NET DotNet Forum Index » VB.NET Forum (Visual Basic .NET) » Speed issue creating Excel spreadsheet... |
|
Page 1 of 1 |
|
| Author |
Message |
| Stephen Plotnick... |
 Posted: Thu Oct 29, 2009 11:19 pm |
|
|
|
Guest
|
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. |
|
|
| Back to top |
|
|
|
| Tom Shelton... |
| Posted: Fri Oct 30, 2009 12:03 am |
|
|
|
Guest
|
On 2009-10-30, Stephen Plotnick <thepla at (no spam) thepla.com> wrote:
Quote: 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 |
|
|
| Back to top |
|
|
|
| eBob.com... |
| Posted: Fri Oct 30, 2009 9:09 am |
|
|
|
Guest
|
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 at (no spam) thepla.com> wrote in message
news:B74D6F4F-2B1B-4BD1-9B21-075079727FFB at (no spam) microsoft.com...
Quote: 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.
|
|
|
| Back to top |
|
|
|
| Ato_Zee... |
| Posted: Fri Oct 30, 2009 9:43 am |
|
|
|
Guest
|
On 30-Oct-2009, "eBob.com" <fakename at (no spam) totallybogus.com> wrote:
Quote: 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. |
|
|
| Back to top |
|
|
|
| Family Tree Mike... |
| Posted: Fri Oct 30, 2009 10:09 am |
|
|
|
Guest
|
"Stephen Plotnick" wrote:
Quote: 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 |
|
|
| Back to top |
|
|
|
| Tom Shelton... |
| Posted: Fri Oct 30, 2009 11:02 am |
|
|
|
Guest
|
On 2009-10-30, Family Tree Mike <FamilyTreeMike at (no spam) discussions.microsoft.com> wrote:
Quote:
"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.
And COM interop is slower then simply writing out an xml document. There are
a few limitations, but, for the most part it's a piece of cake to generate xml
spreadsheets. And most of the time, the user has no idea :)
It took me maybe an hour to write a basic XmlSpreadsheet wrapper class
(air-code):
Dim spreadSheet As New XmlSpreadsheet()
spreadSheet.Workbook.WorkSheets(0).Name = "Hello"
Dim cells() As Cell = {New Cell(CellType.String, "John"), New Cell(CellType.String, "Sue")}
Dim r As New Row()
r.Cells.AddRange(cells)
spreadSheet.WorkBook.WorkSheets(0).Rows.Add(r)
File.WriteAllText("myspread.xls", spreadSheet.ToXml())
--
Tom Shelton |
|
|
| Back to top |
|
|
|
| Family Tree Mike... |
| Posted: Fri Oct 30, 2009 12:01 pm |
|
|
|
Guest
|
"Tom Shelton" wrote:
Quote: On 2009-10-30, Family Tree Mike <FamilyTreeMike at (no spam) discussions.microsoft.com> wrote:
Are you using early or late binding? Late binding is considerably slower
than early binding.
And COM interop is slower then simply writing out an xml document. There are
a few limitations, but, for the most part it's a piece of cake to generate xml
spreadsheets. And most of the time, the user has no idea :)
It took me maybe an hour to write a basic XmlSpreadsheet wrapper class
(air-code):
Dim spreadSheet As New XmlSpreadsheet()
spreadSheet.Workbook.WorkSheets(0).Name = "Hello"
Dim cells() As Cell = {New Cell(CellType.String, "John"), New Cell(CellType.String, "Sue")}
Dim r As New Row()
r.Cells.AddRange(cells)
spreadSheet.WorkBook.WorkSheets(0).Rows.Add(r)
File.WriteAllText("myspread.xls", spreadSheet.ToXml())
--
Tom Shelton
.
I agree that using the office xml format is a better way to go in many
respects. If the original poster though is not comfortable with XML, an
easier speedup may be to switch from COM.
Mike |
|
|
| Back to top |
|
|
|
| OmegaSquared... |
| Posted: Fri Oct 30, 2009 12:08 pm |
|
|
|
Guest
|
If automatic recalculation is specified and there are lots of heavy-duty
calculations being performed with references to the cells that are being
updated, this can slow down the cell by cell update process.
If this is the case, you might try turning off automatic recalculation until
after the updates are complete.
Cheers,
Randy |
|
|
| Back to top |
|
|
|
| Michel Posseth [MCP]... |
| Posted: Fri Oct 30, 2009 2:55 pm |
|
|
|
Guest
|
This can be done much easier and faster , just bind a datasource to a web
datagrid
output the result to a .xls fuile an call process start on that file
you would have few thousands of row in seconds on your screen
need some example code ?? let me know
HTH
Michel Posseth
"Tom Shelton" <tom_shelton at (no spam) comcastXXXXXXX.net> schreef in bericht
news:%23PVsLLYWKHA.4816 at (no spam) TK2MSFTNGP06.phx.gbl...
Quote: On 2009-10-30, Family Tree Mike <FamilyTreeMike at (no spam) discussions.microsoft.com
wrote:
"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.
And COM interop is slower then simply writing out an xml document. There
are
a few limitations, but, for the most part it's a piece of cake to generate
xml
spreadsheets. And most of the time, the user has no idea :)
It took me maybe an hour to write a basic XmlSpreadsheet wrapper class
(air-code):
Dim spreadSheet As New XmlSpreadsheet()
spreadSheet.Workbook.WorkSheets(0).Name = "Hello"
Dim cells() As Cell = {New Cell(CellType.String, "John"), New
Cell(CellType.String, "Sue")}
Dim r As New Row()
r.Cells.AddRange(cells)
spreadSheet.WorkBook.WorkSheets(0).Rows.Add(r)
File.WriteAllText("myspread.xls", spreadSheet.ToXml())
--
Tom Shelton |
|
|
| Back to top |
|
|
|
| Stephen Plotnick... |
| Posted: Wed Nov 04, 2009 7:17 pm |
|
|
|
Guest
|
Wow, thanks for all the replies. Was at hospital with brother almost
immediately after writing post.
Not sure what binding I'm using and surely would struggle with XML at this
time.
I always had screen turned off but put it on to see what was taking so long.
There are not any calculations in the spreadsheet. I take a text file and
parse each record a literally paste one cell at a time. Do a little color
formatting, etc.
Please send me some code or reply; would be very helpful.
"Michel Posseth [MCP]" <msdn at (no spam) posseth.com> wrote in message
news:C3B30768-5C44-4B6C-A514-D64506163E88 at (no spam) microsoft.com...
Quote: This can be done much easier and faster , just bind a datasource to a web
datagrid
output the result to a .xls fuile an call process start on that file
you would have few thousands of row in seconds on your screen
need some example code ?? let me know
HTH
Michel Posseth
"Tom Shelton" <tom_shelton at (no spam) comcastXXXXXXX.net> schreef in bericht
news:%23PVsLLYWKHA.4816 at (no spam) TK2MSFTNGP06.phx.gbl...
On 2009-10-30, Family Tree Mike
FamilyTreeMike at (no spam) discussions.microsoft.com> wrote:
"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.
And COM interop is slower then simply writing out an xml document. There
are
a few limitations, but, for the most part it's a piece of cake to
generate xml
spreadsheets. And most of the time, the user has no idea :)
It took me maybe an hour to write a basic XmlSpreadsheet wrapper class
(air-code):
Dim spreadSheet As New XmlSpreadsheet()
spreadSheet.Workbook.WorkSheets(0).Name = "Hello"
Dim cells() As Cell = {New Cell(CellType.String, "John"), New
Cell(CellType.String, "Sue")}
Dim r As New Row()
r.Cells.AddRange(cells)
spreadSheet.WorkBook.WorkSheets(0).Rows.Add(r)
File.WriteAllText("myspread.xls", spreadSheet.ToXml())
--
Tom Shelton
|
|
|
| Back to top |
|
|
|
|
|
All times are GMT - 5 Hours
The time now is Sat Dec 05, 2009 3:00 pm
|
|