Creating a Dynamic Reports using ASP and Excel

I was doing a Google search on just this subject, and of all the references this one has been absolutely the most useful of all. I have had multiple requests from users asking if some of our web reports (Crystal) could instead be output to Excel files.

By using some formatting of dates and numerics in asp, plus applying a few style sheet elements I was able to provide a rather complex spreadsheet in exactly the format the user wanted.

Thanks again for this killer tip

Ray

dun use control to loop recordset
use below function is much more faster..
sStr=tmprs.GetString(,,"</td><td>","</td></tr><tr><td>"," ")

Hai all,
This was the query I posted previously:
If i run with SQL Server 2000, First time when i call through browser i will get empty excel sheet. Close and call through browser then also the same.

with out closing the browser if u run with another window then the result will display in the browser. Could u help in this.

I was able to find another way to come around this.By adding the following line to your code makes the display fine at the very first time.

Response.AddHeader "Content-Disposition","attachment;filename=somename.xls"

hi all,

after reading this article i got a clbetter idea of how to work with asp and excel but now i want to write in an exsiting excel sheet which is on server side. its a template..
and has a standard format.. so i want to write in that excel sheet in a paricualr
fields and then open that file to save on the client side.. can i do that?..
i visited many pages/sites.. but all that they are doing is.. creating a new excel
sheet,writing on it and then user can save it on his/her side.. but i want to
use a template that is already existing.
please help .......
Thanx in advance..
Deepali

i got the excel output to work A-OK, but am unable to apply CSS to the table cells - it just spits out a big arial mess...any advice??

upon further messing with it, it accepts old-school formatting, such the font tag with size and face attributes, but no CSS...

Fine i got what i am searching. But i want to do something more with this. I want to save the SQL query result automatically to a excel file and i like to give only the link for that excel file so that the user click and download it.

I can't get this to work could someone please send me a working html file so that i can edit the database source!! I would be very grateful!

I used the above MIME type.
Response.ContentType = "application/vnd.ms-excel"

But when I ask the page to generate excel report it is moving to the next page in excel format without displaying anything.
ie when i call through browser i get empty excel sheet.

can you explain why it happens

Lakhs of Thanks in advance,
TMBabu

thanks for the additional comments Anandha subha, it works excellent now using the code that you specified.

i hav generated a table report in web page using asp code
nw i need to do is tht whn i click a button to export to excel thn the report in the web page shud automatically export to excel file and it shud save itself with filename having currentdate as like "572004.xls".
can u able to send the code for this requirement ?
one thing it shud not prompt the user to give the file name to save in excel.
pls make it ASAP.
i too hav used this --
Response.ContentType = "application/vnd.ms-excel"
but system prompting user to enter the file name to save the file in excel.

Hi There,
This article has given a good information on the concerned topic. But I had a problem while setting the font size and width of the columns on the Excel spreadsheet. When I have given the column width as 22 in my ASP code, its not the font that I am getting on Excel spread sheet. Its the default 12 I see on my Excel. Below is part of the code.


for intCounter = 0 to ObjRS.fields.Count-1

Response.Write "<th align=""left"" font-size:""22pt"" bgcolor=""blue"" width=""100px"">" & ObjRS.fields(intCounter).Name & "</th>" & vbCRLF

next

Is there any specific reason of why its doing? Could you please give an explanation or any solution of setting the column width and the font size of the excel spread sheet on the ASP side??

I would really appreciate your ideas.

Thanks,
Varun

Ok, I have an asp page that sends a report to excel and works fine, I have a button in my page named "Excel", but when I click on this button the browser ask me two times if I want to open or save the file. The question is, is there a way to receive the question one time instead of two. The problem is that this behavior only happen in some Internet Explorer browsers, maybe there is something I have to configure in the browser
Thank, and excuse my poor english

I like the article on display html table information in excel and being able to use formulas, but, is ther a way to use the relative addressing scheme (RC..) rather than the absolute (A1..)

Thanks

Andy

Hi everybody!

I liked a lot this article!

Just one question, I'm starting with ASP .NET and I have to generate an Excel from a recordset like in the article example, is there an easy way to do it in .NET or it works exactly like in normal ASP.

Thanks in advance!

Hi

I have done an <B>Export</B> feature for my web page . I have assigned a name for the file before download through response.addheader...

It is working fine on Windows O.S.
But in Macintosh browsers like Safari and Mozilla, it gives a problem.
In Macintosh machine if we open the downloaded file, it opens as a Work book and will ask the user to save the file.
How can I make the file to download as a Worksheet
How can I solve this issue?

Regards
Pradeep

Hello,

I have read the article and the codes. I need to do something like this however i want to know whether we can create excel headers and footers by this system or not.I will be using ASP as my scripting language.

If it is possible then if somebody can help me with code will be very much appriciated.

Regards,

shoubhik

Another thing: by default, IE opens Excel pages inside a browser window, which can cause difficulties if the user wants to save the Excel page locally or make changes to it. To force IE to open the dynamic page in Excel, add this line of ASP below the Content-Type response:

Response.AddHeader "Content-Disposition", "attachment;filename=myfilename.xls"

(change myfilename to whatever you want the Excel file to be called).

Cheers,
Ryan

To create very robust and professional looking Excel reports. I first create a layout of the Column Headers, Page Layout, Color, fonts, etc. in the Excel product itself. I then save it as a web page using the File Save As... [Save as type:] [u]Web page[/u]

I then use a text editor to copy and paste the source code into an asp page. From there, I can add any looping logic to render the results of a dataset.

You can use asp code in many areas of the Excel source code to print a user's name, etc. on the Excel document itself.

I have a question for a preceding zeros

" <%= objrs"(i) %>

but the front has space before the number some of the system cant space it will be a different meaning of space 01 and 01.

How to code with printing only number 01 on excel ? I would like to use ASP to control the excel column formatting.

Best Regards,
Brian

I am able to download data from SQL server to Excel. But my problem is with text field. I am capuring the Enter key and converting it to br
replace(x.value,chr(13)& chr(10)," <br>")
but excel takes each line break as seperate record. Is there a way to display that in one single record ?
Thanks for the help.

Haritha.

Hello,

I have a similar script for creating an Excel spread sheet. How ever I need to display five different tables from my database in Excel. Does anyone know of any way of creating new sheets in the workbook, one for each table? Thank you.

Hi,

This article is the closest thing I have been able to find for what I am trying to do. What I need to do is not to import to excel from a DB, but vice versa. From excel (using ASP ... not .NET) import data to a table into sql server 2000 with the click of a form button. Thanks for the help! This article has put me on the right path. If anyone has any good ideas on a way to do this, I am open to suggestions.

Thanks again,

Johnny

How to make the report into [b]PIE[/b] or [b]BAR[/b] chart?

Pls help!

Nice way to begin coding. I found this link to other who might be wanting more info:
[b][u]http://support.microsoft.com/default.aspx?scid=kb;en-us;Q271572[/u][/b]

Hi All

I need a help im doing a project in asp. the problem is i have a search criteria and on clicking a go button i need to generate a excel sheet with data in it accessed from sql database. Plz send me some sample codes which will help me out and plz do it soon bcoz im in greate trouble

My mail id is ajithevn@mail.com

Thanks

Hi Every Body !

Export to excel data from ultrawebgrid rows, Each data row contain one worksheet in excel sheet like as
row1 data contain sheet1
row2 data contain sheet2
row3 data contain sheet3
like should i want from web form......to Excel sheet

this opertaion getting at a time on pressing one button.
we have pressing one button ultrawebgrid rows data to export excel sheet, In excel sheet each worksheet contain one grid row deatails, for example grid have 10 rows ,pressing any button, In excel sheet 10 worksheets will occure and each worksheet contain 1 row data of grid,

pls send me a solution for this example

Does anyone know how to freeze cells using the example posted above. I am having issues in doing so.

Thank you in advance,

Curt

does anyone know how to put the values in multiple worksheet in the same Excel workbook.
Specifying hte worksheet name before html row tag doesn't seem to work !!

Thank you

i have used the copde in this way

<%
Response.ContentType ="Application/vnd.excel"
Response.AddHeader "Content-Disposition", "attachment; filename=srinu.xls"
Response.Write "srinivas"
%>

now could you please tell me how to auto fit the coloumns in the excel sheet that i am going to get using this method, i need excelsheet whose autofit happened on the fly

please help me in this regard

autofit of the excelsheet usign MIMS can be done by using XML but it works only in excel-2000 version onwards
but i am using some old versions , so i need some other way that will hlp me in autofit the excel sheet on fly

Thanks in advance

ASP export to excel,
I had problem as tmbabu_78. I used sample code above but It could not get the result. when I use the link export to the file had code like the sample above included
<%Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=Export_to_excel.xls"
%>
but it not show any data in excel sheet, if you have any hint please tell me thank you very much.

Regards,

toi create multiple worksheets without using any come or third party component go to http://www.codetoad.com/asp_excel.asp

<Added>

to create multiple worksheets in excel doc without using any COM or third party component go to
http://www.codetoad.com/asp_excel.asp

to create multiple worksheets in excel doc without using any COM or third party component go to

http://www.codeproject.com/dotnet/ExportToExcel.asp

i want code download option so that i can test the code my self i can learn who to write the code with extra feature.

When we convert and dump things to an excel, what happens when the number of rows exceeds the permissible limits? I guess an excel sheet cannot hold more than 65000 odd entries. What exception, if any, is thrown? How do we handle this scenario?

Thanks and Regards,
Ravi

wat if we hav a certain criteria for the page, for example that page in onli showin all recored for company A, but in the DB there are recored of company B. C , D oso..

It's great

I have to authorize someone to edit my Excel web page on net, any suggestion.

I am getting blank Excel sheet. How can I sove this?

<Added>

I am getting blank Excel sheet. How can I solve this?

Hello,
Let me add that I have the same question as mahbub22. On most users machines, the Excel option works fine, but on some, we get the Excel headings only, with no data. We are all using IE 6. I am guessing a browser setting must be adjusted, but have not been able to find it. Any help is appreciated.
Thank you.

Hi all i am very new member of this group .. I have used same code which is shown in the article *Creating a Dynamic Reports using ASp and Excel*. It works very fine but i am facing problems . when i export 5000 recods into excel it work fine but when i export more than 30000 records in excel it give me Error like

*** internet explorer can not Download xyz.xls file from localhost . Internet Explorer was not able to open this internet site . The requested site is either unavailable or cannot be found. Please try again later.***

How i can resole this problem ..
If is there any other method to export into excel then let me know..

Looking forward to your reply n suggetion ..

Kindly Do somthing ..

Thankyou in advance.

Regard
asprajesh

Thanks for sharing. I have a problem here.
How could i do if i want it auto export into excel file and save in a path without any button click?

In the Excel sheet generated there is always a blank line at the top.

I don't know how to get rid of it.

Can you help me ?

Thanks.

Hi,
I'm using a very similar script and have run in to a problem that someone else mentioned a while back and I was wondering if anyone could help.
I've got a text field with line breaks in it and I'd like to translate these to the cell data that's being output.
If I use Replace(jobbie,Chr(10),"<br />") the excel sheet is output with an extra row for each of these line breaks. I've also tried Replace(jobbie,Chr(10),vbCrLf) and this doesn't work either...
Can anyone help?
Thanks

Hi
i used similar script but it dosent work on HTTPS can u suggest me what to do for using on HTTPS

Hi all

I have a record set having 20000 records. I want to export 10000 records in the first worksheet and the rest to the next worksheet. How do I go about it.

Looking forward to your reply n suggestion ..

Thanks
Divya

hii
any idea how can i generate a chart in excel...after downloading data from a asp page

Hi all


i want to create a new excel sheet dynamically while downloading data from oracle database to excel sheet if

<Added>

Hi all

I am downloading data from oracle database to an excel sheet.if the records are more that 65555 then i want to download the remaining records to new excel sheet,this new sheet should be created dynamiclly if records become more than 65555.

any one can help Pls..

Thanks in advance

Hi Every body


I need to generate an excel sheet using asp

i have an page with drop down list in search column,when i select a value from the drop down and i click search i am getting records
in the bottom of the page i have a button to generate excel.if i click i need to generate excel sheet with all the records inside the sheet.

can any one solve this bug.