Solved

Creating an EXCEL spread sheet

Posted on 1999-01-07
4
200 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
[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
  • 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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

It is becoming increasingly popular to have a front-page slider on a web site. Nearly every TV website,  magazine or online news has one on their site, and even some e-commerce sites have one. Today you can use sliders with Joomla, WordPress or …
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
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…

738 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