472,958 Members | 1,642 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 software developers and data experts.

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 8718
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Vikram | last post by:
Hi, Given below is my task. An user can open the xls file from my website (loaded on the top frame). After filling the Excel, he can click a send button at the bottom frame. By clicking the...
0
by: Navin | last post by:
hi, i am using the following code to generate a xls file using the content type now when the user opnes the file at his pc it takes long time to open..if the no of records in file is large...
1
by: ST | last post by:
Hi, I'm having problems opening up excel thru my code. It will write and saveas an excel file, but the application won't open on the user's computer, excel seems to be hidden, because it shows up...
10
by: Mark Day | last post by:
Hi All, I am using Access 2000 to generate over 40 Excel charts and pivot tables using early binding to the Excel 9.0 object library. I am finding that if I show the Excel object while processing...
50
by: diffuser78 | last post by:
I have just started to learn python. Some said that its slow. Can somebody pin point the issue. Thans
8
by: Radu | last post by:
Hi, I have the following problem: I open a recordset based on excel/csv files, but then I need to filter (in code) in order to extract only data pertaining to a specific person. This code is...
2
by: Mad Scientist Jr | last post by:
>From an asp.net web page I want the user to open the results of a SQL query in Excel, as automatically as possible (ie not having to loop through columns, rows, in code). For this,...
16
by: Phil Stanton | last post by:
I have a form with a button which is supposed to open an Excel file (With lots of Macros /VBA) in it. The Excel file gets it's data from the Access program Here is the code Private Sub...
4
by: =?Utf-8?B?THluZXJz?= | last post by:
Hello All, We have a VB.NET application writen using VS 2003. This application apens an excel file from a vendor, reads the data and performs whatever functions it needs. We recently upgraded our...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
2
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.