Prev: vb.net and sql reporting services report
Next: System.IO.File.Exists in Windows 7
From: Tom Shelton on 30 Oct 2009 13:02
On 2009-10-30, Family Tree Mike <FamilyTreeMike(a)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
From: Family Tree Mike on 30 Oct 2009 14:01


"Tom Shelton" wrote:

> On 2009-10-30, Family Tree Mike <FamilyTreeMike(a)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
From: OmegaSquared on 30 Oct 2009 14:08
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

From: Michel Posseth [MCP] on 30 Oct 2009 16:55
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(a)comcastXXXXXXX.net> schreef in bericht
news:%23PVsLLYWKHA.4816(a)TK2MSFTNGP06.phx.gbl...
> On 2009-10-30, Family Tree Mike <FamilyTreeMike(a)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

From: Stephen Plotnick on 4 Nov 2009 19:17
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(a)posseth.com> wrote in message
news:C3B30768-5C44-4B6C-A514-D64506163E88(a)microsoft.com...
> 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(a)comcastXXXXXXX.net> schreef in bericht
> news:%23PVsLLYWKHA.4816(a)TK2MSFTNGP06.phx.gbl...
>> On 2009-10-30, Family Tree Mike
>> <FamilyTreeMike(a)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
>
>

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