Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Creating an EXCEL spread sheet

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
robj
Asked:
robj
  • 2
  • 2
1 Solution
 
mouattsCommented:
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
 
robjAuthor Commented:
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
 
mouattsCommented:
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
 
robjAuthor Commented:
Appreciate you taking the time to layout the structure .. I'll try it!

Thanks.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now