Solved

Creating an EXCEL spread sheet

Posted on 1999-01-07
4
197 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Powershell Find Folders 7 53
Ned Perl Snippet to Read Files in Directory 5 69
Make a file test.vbs using vbscript 5 73
Transform normalized CSV to line in powershell 7 38
In this tutorial I will show you how to provide a dynamic RTF document on your website generated with data from your database. For this tutorial you will need Microsoft Word or WordPad, WhizBase and Microsoft Access. In this tutorial I will show …
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
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…

822 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