469,281 Members | 2,450 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,281 developers. It's quick & easy.

Excel Slow in opening

All,
I am using the ASP code below to save some data from a SQL Server database
via ADO as an Excel spreadsheet
strReportName = Request.QueryString("ReportName")

If len(strReportName) > 0 then

stResultsSql = Session.Contents("sSQL")

Set objComm = CreateObject("ADODB.Command")

objComm.ActiveConnection = objConn

objComm.CommandText = stResultsSql

objComm.CommandType = adCmdText

set objRS = objComm.Execute

set objComm = Nothing

If Err.number = 0 Then

If Not objRs.EOF Then

With Response

.Clear

.AddHeader "Content-disposition", "attachment; filename="
& strReportName & ".xls"

.ContentType = "application/vnd.ms-excel""

.Write "<html
xmlns:x=""urn:schemas-microsoft-com:office:excel""><head>"

.Write "<!--[if gte mso 9]><xml>"

.Write "<x:excelworkbook>"

.Write "<x:excelworksheets>"

.Write "<x:excelworksheet>"

.Write "<x:name>" & strReportName & "</x:name>"

.Write "<x:worksheetoptions>"

.Write "<x:print>"

.Write "<x:validprinterinfo/>"

.Write "</x:print>"

.Write "</x:worksheetoptions>"

.Write "</x:excelworksheet>"

.Write "</x:excelworksheets>"

.Write "</x:excelworkbook>"

.Write "</xml>"

.Write "<![endif]--> "

.Write "</head><body>"

Set objRsFields = objRs.Fields

.Write "<table border=1><tr>"

For Each objField In objRsFields

.Write("<td><P1><B>" & objField.Name &
"</B></P1></td>")

.Next

.Write("</tr>")

Do While Not objRs.EOF

.Write "<tr>"

For Each objField In objRsFields

If (NOT ISNULL(objField.Value) And
Len(objField.Value) > 0 ) Then

strVal = objField.value

Select case objField.Type
Case 200 'adVarChar

'May have leading
zero's

If Mid(strVal,1,1) =
"0" then 'Excel will by default truncate leading zero's

.Write("<TD
nowrap align=left><P2>=T(""" & strVal & """)</P2></TD>")

ElseIf
IsNumeric(strVal) Then

'A
numeric value that could be formatted as sientific value by Excel
..Write("<TD nowrap align=left><P2>=T(""" & strVal & """)</P2></TD>")
ElseIf
Len(Trim(strVal)) = 0 then
..Write("<td><P2>&nbsp</2P></td>")

ElseIf
Len(CStr(strVal)) = 0 then
..Write("<td><P2>&nbsp</P2></td>")
Else

'No
leading Zero hence text is OK Excel will not Truncate or not numeric
..Write("<TD nowrap align=left><P2>" & strVal & "</P2></TD>")

End If
Case Else

.Write("<TD
nowrap align=left><P2>" & strVal & "</P2></TD>")

End Select
ElseIf ISNULL(objField.Value)
Then
..Write("<td><P2>&nbsp</P2></td>")
End If

strVal = ""
Next

.Write("</tr>")

objRs.MoveNext

.Flush

Loop

.Write "</table></body></html>"

End With
Else

Response.Write "No data returned Plesae try again"

End If

objRs.Close

Set objRs = Nothing
End If

Else

Response.Redirect "../WebReportsDBDown.htm"

End If

%>

The problem I face is that the excel sheet that user at my site save is very
slow in opening and despite using <TD nowrap> some of the cells on the
spreadseet will not adjust their width to fit the widest data in the column!

When users try to open the excelspread sheet they complain it is very slow,
for even just 500 rows.

They notice that it says HTML opening filename.xls but it is very slow

Any ideas why the spreadsheet is slow.

Thanks

Jawahar

Jul 19 '05 #1
1 8477
check out http://www.greggriffiths.org/webdev/both/excel/

Jawahar Rajan wrote:
All,
I am using the ASP code below to save some data from a SQL Server database
via ADO as an Excel spreadsheet
strReportName = Request.QueryString("ReportName")

If len(strReportName) > 0 then

stResultsSql = Session.Contents("sSQL")

Set objComm = CreateObject("ADODB.Command")

objComm.ActiveConnection = objConn

objComm.CommandText = stResultsSql

objComm.CommandType = adCmdText

set objRS = objComm.Execute

set objComm = Nothing

If Err.number = 0 Then

If Not objRs.EOF Then

With Response

.Clear

.AddHeader "Content-disposition", "attachment; filename="
& strReportName & ".xls"

.ContentType = "application/vnd.ms-excel""

.Write "<html
xmlns:x=""urn:schemas-microsoft-com:office:excel""><head>"

.Write "<!--[if gte mso 9]><xml>"

.Write "<x:excelworkbook>"

.Write "<x:excelworksheets>"

.Write "<x:excelworksheet>"

.Write "<x:name>" & strReportName & "</x:name>"

.Write "<x:worksheetoptions>"

.Write "<x:print>"

.Write "<x:validprinterinfo/>"

.Write "</x:print>"

.Write "</x:worksheetoptions>"

.Write "</x:excelworksheet>"

.Write "</x:excelworksheets>"

.Write "</x:excelworkbook>"

.Write "</xml>"

.Write "<![endif]--> "

.Write "</head><body>"

Set objRsFields = objRs.Fields

.Write "<table border=1><tr>"

For Each objField In objRsFields

.Write("<td><P1><B>" & objField.Name &
"</B></P1></td>")

.Next

.Write("</tr>")

Do While Not objRs.EOF

.Write "<tr>"

For Each objField In objRsFields

If (NOT ISNULL(objField.Value) And
Len(objField.Value) > 0 ) Then

strVal = objField.value

Select case objField.Type

Case 200 'adVarChar

'May have leading
zero's

If Mid(strVal,1,1) =
"0" then 'Excel will by default truncate leading zero's

.Write("<TD
nowrap align=left><P2>=T(""" & strVal & """)</P2></TD>")

ElseIf
IsNumeric(strVal) Then

'A
numeric value that could be formatted as sientific value by Excel

.Write("<TD nowrap align=left><P2>=T(""" & strVal & """)</P2></TD>")

ElseIf
Len(Trim(strVal)) = 0 then

.Write("<td><P2>&nbsp</2P></td>")

ElseIf
Len(CStr(strVal)) = 0 then

.Write("<td><P2>&nbsp</P2></td>")

Else

'No
leading Zero hence text is OK Excel will not Truncate or not numeric

.Write("<TD nowrap align=left><P2>" & strVal & "</P2></TD>")

End If

Case Else

.Write("<TD
nowrap align=left><P2>" & strVal & "</P2></TD>")

End Select

ElseIf ISNULL(objField.Value)
Then

.Write("<td><P2>&nbsp</P2></td>")

End If

strVal = ""

Next

.Write("</tr>")

objRs.MoveNext

.Flush

Loop

.Write "</table></body></html>"

End With

Else

Response.Write "No data returned Plesae try again"

End If

objRs.Close

Set objRs = Nothing

End If

Else

Response.Redirect "../WebReportsDBDown.htm"

End If

%>

The problem I face is that the excel sheet that user at my site save is very
slow in opening and despite using <TD nowrap> some of the cells on the
spreadseet will not adjust their width to fit the widest data in the column!

When users try to open the excelspread sheet they complain it is very slow,
for even just 500 rows.

They notice that it says HTML opening filename.xls but it is very slow

Any ideas why the spreadsheet is slow.

Thanks

Jawahar


Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Vikram | last post: by
10 posts views Thread by Mark Day | last post: by
50 posts views Thread by diffuser78 | last post: by
16 posts views Thread by Phil Stanton | last post: by
4 posts views Thread by =?Utf-8?B?THluZXJz?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.