Writing data to an excel workbook

[Guest]

I'm pulling data from a Sql 2k5 table, looping through cols & rows and
populating the sheet.cells object, eventually I'm getting a famous Excel
error: Excel Exception HRESULT: 0x800A03EC. The code I'm getting the error
in is pasted into this post, I've read, and experienced, that this is VERY
slow. I read an article that some guy put the data into an ArrayList then
populated the cells that way but didn't show any code so it didn't help much.
I'd appreciate any help since I think I'll probably be fired soon if I don't
get this done ASAP; not to put that on anyone elses shoulders just some
advice would be great. At this point it's almost faster to do this by hand,
I'd really like to speed this up.

Code throwing the error:

foreach (Object obj in ColData)
{
string rowData = obj.ToString();
oSheet.Cells[iRow, colNum] = rowData;
colNum++;
}
iRow++;

Explaination: ColData at this point is an ArrayList, I'm putting the data
into a string thinking that maybe just passing in a string directly would be
better, NOPE. iRow is a variable that is dynamic, it won't be the same all
the time. It will never be less than zero and colNum is equal to the number
assigned to column the ColumnName is in. This is a complicated application
since it is a backend app to a WebApp, it appears that the data is being
written to the proper cells but this error is killing me, it is also
EXTREMELY SLOW.

Thanks all.

 

[NickHK]

In VBA, you can dump the whole array in one go. Can't say about you chosen
language.
Certainly faster than writing a cell at a time.

Private Sub CommandButton1_Click()
Dim TempStr() As String

TextBox1.Text = "Line 1" & vbNewLine & "Line 2" & vbNewLine & "Line 3" &
vbNewLine & "Line 4" & vbNewLine & "Line 5"

TempStr = Split(TextBox1.Text, vbNewLine)

Range("A1").Resize(15).Value = Application.Transpose(TempStr)
'Or
Range("A1").Resize(UBound(TempStr) + 1).Value =
Application.Transpose(TempStr)

End Sub

NickhK

 

[gimme_this_gimme_that]

Sweet example NickHK.

Is there a delimiter you can use for cells as well?

 

[NickHK]

Not sure I understand the question.

Dim arr as variant
arr=range("A1:A10")

NickHK
P.S. Tom's Transpose posting is getting a good airing at the moment.

 

[gimme_this_gimme_that]

Oh yeah. Agreed. Tom's solution is right on the mark.

Never mind. No question.

 

[Guest]

Thanks, this is a stand alone C# app that will be running on a server. It's
got a monitor on it that will show spreads that are in one of four states
(Pending, Processing, Complete, Failed). I'm the developer and do what I'm
asked to do and this is the first time I'm using the Excel object to do this
sort of thing. I appreciate everybody's comments, thank you all.

Kevin
--
Kevin C. Brown
Developer

In VBA, you can dump the whole array in one go. Can't say about you chosen
language.
Certainly faster than writing a cell at a time.

Private Sub CommandButton1_Click()
Dim TempStr() As String

TextBox1.Text = "Line 1" & vbNewLine & "Line 2" & vbNewLine & "Line 3" &
vbNewLine & "Line 4" & vbNewLine & "Line 5"

TempStr = Split(TextBox1.Text, vbNewLine)

Range("A1").Resize(15).Value = Application.Transpose(TempStr)
'Or
Range("A1").Resize(UBound(TempStr) + 1).Value =
Application.Transpose(TempStr)

End Sub

NickhK

I'm pulling data from a Sql 2k5 table, looping through cols & rows and
populating the sheet.cells object, eventually I'm getting a famous Excel
error: Excel Exception HRESULT: 0x800A03EC. The code I'm getting the error
in is pasted into this post, I've read, and experienced, that this is VERY
slow. I read an article that some guy put the data into an ArrayList then
populated the cells that way but didn't show any code so it didn't help much.
I'd appreciate any help since I think I'll probably be fired soon if I don't
get this done ASAP; not to put that on anyone elses shoulders just some
advice would be great. At this point it's almost faster to do this by hand,
I'd really like to speed this up.

Code throwing the error:

foreach (Object obj in ColData)
{
string rowData = obj.ToString();
oSheet.Cells[iRow, colNum] = rowData;
colNum++;
}
iRow++;

Explaination: ColData at this point is an ArrayList, I'm putting the data
into a string thinking that maybe just passing in a string directly would be
better, NOPE. iRow is a variable that is dynamic, it won't be the same all
the time. It will never be less than zero and colNum is equal to the number
assigned to column the ColumnName is in. This is a complicated application
since it is a backend app to a WebApp, it appears that the data is being
written to the proper cells but this error is killing me, it is also
EXTREMELY SLOW.

Thanks all.