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