Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 export large recordset to excel using asp

Author  Topic 

ajay
Starting Member

34 Posts

Posted - 2002-08-23 : 08:20:54
Hello all,


I need to export large recordset approx 5000 record currently to an excel file using an asp page.

After setting the contant type to
Response.ContentType = "application/vnd.ms-excel"
increased the connection timeout, used getrows method etc but
It's not happenin, it always said the internet explorer cannot download etc, code is ok for 2000 records it shows them in excel file.
but now it's not happenin.

I cannt use filesystemobject to write file...cant get permission to write file on the fly.

Help needed.

truly

Ajay

ajay

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-08-25 : 19:59:37
Does the user want to preview the data first?

This javascript function takes the contents of an html table (using the HTML DOM) which is produced by asp and puts it into an excel file...

I must admit I haven't tried this with 5000 rows, but you could try it. It's not fast..but it might be OK

function fnAutomateExcel(){
var oXL = new ActiveXObject("Excel.Application")
oXL.Visible = false

var oWB = oXL.Workbooks.Add()
var oSheet = oWB.ActiveSheet

mybody=document.getElementsByTagName("body").item(0)
mytable=mybody.getElementsByTagName("table").item(0)
mytablebody=mytable.getElementsByTagName("tbody").item(0)

for(var i = 0;i<mytablebody.getElementsByTagName("tr").length;i++){
myrow=mytablebody.getElementsByTagName("tr").item(i)
if(i == 0){
for(var x = 1;x<myrow.getElementsByTagName("th").length;x++){
mycel=myrow.getElementsByTagName("th").item(x)
oSheet.Cells(i+1, x).Value = mycel.childNodes.item(0).data
oSheet.Cells(i+1, x).Font.Bold = true
}
} else {
if(myrow.getElementsByTagName("td").length != 0){
for(var x = 1;x<myrow.getElementsByTagName("td").length;x++){
try{
mycel=myrow.getElementsByTagName("td").item(x)
oSheet.Cells(i+1, x).Value = mycel.getElementsByTagName("input").item(0).value
}
catch(error){
try{
mycel=myrow.getElementsByTagName("td").item(x)
oSheet.Cells(i+1, x).Value = mycel.childNodes.item(0).data
} catch(error) {
//this error means null value
oSheet.Cells(i+1, x).Value = ""
}
}
}
} else {
for(var x = 1;x<myrow.getElementsByTagName("th").length;x++){
mycel=myrow.getElementsByTagName("th").item(x)
oSheet.Cells(i+1, x).Value = mycel.childNodes.item(0).data
oSheet.Cells(i+1, x).Font.Bold = true
}
}
}

}

oSheet.Range(oSheet.Cells(1,1),oSheet.Cells(i,x)).EntireColumn.AutoFit

oXL.Visible = true
oXL.UserControl = true
}


--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page
   

- Advertisement -