asked on

Excel using ASP

How can i create an Excel sheet at client side using ASP.

I tried to use CreateObject ("Excel.Application") & CreateObject("OWC.SpreadSheet") but they just throw-ed error :"
ActiveX component can't create object: 'Excel.Application'
ActiveX component can't create object: 'OWC.Spreadsheet'

if i use Server.CreateObject it thorws
Server.CreateObject Failed..

can u pls tell me how create a simple excel sheet using ASP

sammySeltzer

This is probably the simplest code I can show you.

I have something much juicier but very complicated.

So, why not try starting with this?

Let me know if this is somethin you can use.

<%@ LANGUAGE="VBSCRIPT" %>

   <HTML>
   <HEAD>
   <TITLE>Create Tab Delimited Text File</TITLE>
   </HEAD>
   <body>

         <%

         'Create a randome Filename
          nRandom = Int((1000000 - 1 + 1) * Rnd + 1000000)
          fileExcel = "t" & CStr(nRandom) & ".xls"

         'Replace 'MyWeb' with your virtual directory name or just the
         'slash if it is at the wwwroot.
          filePath= Server.mapPath("\MyWeb")
          filename=filePath & "\" & fileExcel
          Set fs = Server.CreateObject("Scripting.FileSystemObject")
          Set MyFile = fs.CreateTextFile(filename, True)

         'Open the connection and retrieve data from the database
          Set cn = Server.CreateObject("ADODB.Connection")


          cn.Open "DSN=yourDSN" ' Or dsn-less connection if that's your preference
          Set rs = cn.Execute("SELECT field1, field2,...fieldn FROM yourtable") 'Add where clause if applicable

          strLine="" 'Initialize the variable for storing the filednames

          For each x in rs.fields
            'Separate field names with tab so that these appear in
            'different columns in Excel
            strLine= strLine & x.name & chr(9)
          Next
         'Write this string into the file
          MyFile.writeline strLine

         'Retrieve the values from the database and write into the database
          Do while Not rs.EOF
          strLine=""
          for each x in rs.Fields
            strLine= strLine & x.value & chr(9)
          next
          MyFile.writeline strLine
          rs.MoveNext
       Loop

       'Clean up
       MyFile.Close
       Set MyFile=Nothing
       Set fs=Nothing

       'Show a link to the Excel File.
       link="<A HREF=" & fileExcel & ">Open Excel</a>"
       Response.write link
          %>

   </BODY>
   </HTML>

Open in new window

kishan66

ASKER

Hi sammySeltzer,

That works great by adding couple of statements like Rs.MoveFirst & rs.MoveNext

But i would prefer creating a Excel object and add data to cells.
Reason:-
(i) If there are new columns that has to be added or modified i have to change the
table design, which i dont want to.
(ii) I want to use an exsisting Excel(as a template) which will only have the column headers ...and i shall dump the values from the database at what ever columns i need in that Excel template and save as newExcel.xls

Do you think is it possible from ASP?

Thanks

sammySeltzer

Well, I have some code that you can use to add more columns to the db from asp.

Then all you would do is instead of selecting individual columns as I showed:

SELECT field1, field2,...fieldn FROM yourtable

You just select all the columns SELECT * from yourtable...

kishan66

ASKER

sammySeltzer,

May be i was not clear .

All i want is to add data from databaseto an existing Excel and save the excel file.

Thats all

ASKER CERTIFIED SOLUTION

sammySeltzer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial