By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,710 Members | 1,613 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,710 IT Pros & Developers. It's quick & easy.

Excel Slow in opening

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.