J
Jawahar Rajan
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
ffice: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 "<xrint>"
.Write "<x:validprinterinfo/>"
.Write "</xrint>"
.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> </2P></td>")
ElseIf
Len(CStr(strVal)) = 0 then
..Write("<td><P2> </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> </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
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
ffice: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
rint>".Write "<x:validprinterinfo/>"
.Write "</x
rint>".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> </2P></td>")
ElseIf
Len(CStr(strVal)) = 0 then
..Write("<td><P2> </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> </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