Solved

Excel using ASP

Posted on 2010-11-30
5
396 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
  • 3
  • 2
5 Comments
 
LVL 28

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 28

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 28

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
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/…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now