Solved

Excel using ASP

Posted on 2010-11-30
5
403 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:kishan66
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 34242984
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

0
 

Author Comment

by:kishan66
ID: 34257479
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
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 34257722
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...
0
 

Author Comment

by:kishan66
ID: 34258218
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
0
 
LVL 29

Accepted Solution

by:
sammySeltzer earned 500 total points
ID: 34258638
ok, got it.

But why isn't what I provided not good enough then?

As stated, if you use wildcard in your select, SELECT * from yourtable.

Then this code:

         '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

Open in new window


that even if you add new columns to your database table, that column and its data will be populated in excel.

Isn't it what's going on so far?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question