Board index » Web Programming » Excel Slow in opening
kvedros
 |
Excel Slow in opening
Web Programming344
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> </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 - |