Speed issue creating Excel spreadsheet

  • Thread starter Thread starter Stephen Plotnick
  • Start date Start date
S

Stephen Plotnick

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.
 
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.
Click to expand...

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 :)
 
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 said:
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.
Click to expand...

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

Mike
 
Are you using early or late binding? Late binding is considerably slower
than early binding.
Click to expand...

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 said:
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())
Click to expand...

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
 
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
 
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
 
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.
 
Back
Top