Solved

Creating an EXCEL spread sheet

Posted on 1999-01-07
4
190 Views
Last Modified: 2013-12-25
I'm assigned the task of building a dynamic excel spreadsheet for the web.  Should be server side on an NT box running IIS.  I'm fluent in C but have ZERO knowledge about EXCEL.

Can any one point me in the right direction? Ideas DDE, OLE, out or process services (yuk), or anything.  

Sample code or links to samples (of any lanuguage) would be greatly appreciated!

Thank you!
0
Comment
Question by:robj
  • 2
  • 2
4 Comments
 
LVL 11

Expert Comment

by:mouatts
ID: 1830526
If you mean that you need to dynamically display/update an Excel Spreadsheet then the simplest approach is to use ODBC and most likely ASP.

Such an approach allows you 'talk' to the spreadsheet using SQL both for selection and for update meaning that your zero Excel knowledge is not going to be much of a hinderence.

A good starting point would be look at the examples delivered with IIS although I think most of these are Access based one normally only has to change the connection string and you are flying.

It does mean that you will be using VBScript but this is simple enough although at times frustrating for a C programmer (it doesn't do your street cred much good either in C circles:) )

HTH
Steve
0
 

Author Comment

by:robj
ID: 1830527
ODBC sounds great, but in the native IIS install there's no such dynamic EXCEL examples.  Wish you (or someone) could point me to a specific example.  It might be a lot more helpful than just saying use ODBC.  A number of people told me the same thing, ODBC or DDE, but when pressed to show me where they read it .. suddenly, no one seems to be able to find an example.

Sorry, just needs specifics.

Thanks.
0
 
LVL 11

Accepted Solution

by:
mouatts earned 100 total points
ID: 1830528
Using Excel is hardly any different to using Access or SQL Server, for example. There are only two differences.

Firstly you must set up an ODBC DSN within the ODBC Manager in the Control Panel. This should point to the excel spreadsheet that you wish to use.

Secondly some of the internal functions with excel are not present or different to those within another database.

To give a complete example is a bit long winded but there are some key bits of code that are required and similar will be seen in a access example.

Firstly whenever you need to connect to the database you will need the following code within your script (myexcel is the name that you gave the DSN in the ODBC manager)

Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "DSN=myexcel"

To query the database form a SQL string where the column names are the names of the headings and the table names are the names of the excel pages.

This string is then executed using the connection object previously created and the results returned to a results set object. EG

sql="select col1 from page1"
set myres=connection.execute(sql)

We can now use the results sets to get at the data.
Thus the expression:-

 myres("col1")

will return the value of the current col1 field.
If we want to move to the next record we use the movenext methos as follows.

myres.movenext

If we want to output the contents of the field to HTML there are two ways of doing this.

Firstly we could write
response.write (myres("col1")
or within HTML we could use
<P>The value of col1 is <%=myres("col1")%></P>

When we have finished with the database we do a
connection.close

If we wish to to an update we do the exactly the same as the read except that our SQL consist of an UPDATE statement.

If you compare the code snippets with the examples you will see the same constructions.

Whilst I'm not going to provide a complete example as it would take too long I will provide a handy function that I use to determine if a search is successful and when the end of a select loop is reached with an example of how it is used so that you are a little bit further down the road.


Function CheckRS(RS)
      On Error Resume Next
      bEOF = RS.EOF
      If Err Or bEOF Then
            CheckRS = False
      Else
            CheckRS = True
      End If
End Function

Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "DSN=myexcel"

sqlstmt="SELECT col1 FROM mytable"
set myres=connection.execute(sqlstmt)
WHILE CheckRS(myres)
   response.write myres("col1")
   myres.movenext
WEND
connection.close


HTH

Steve
0
 

Author Comment

by:robj
ID: 1830529
Appreciate you taking the time to layout the structure .. I'll try it!

Thanks.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I hope you'll find this tutorial useful and interesting. So let's try to extend Tcl with a new package.  For anyone more deeply interested please check out the book "Practical Programming in Tcl and Tk". It's really one of the best written books abo…
This article will show, step by step, how to integrate R code into a R Sweave document
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…

743 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

14 Experts available now in Live!

Get 1:1 Help Now