Excel , by Microsoft, provides a Spreadsheet. It is distributed individually and as a component of Microsoft Office.
Like Tcl! Excel has a long development history, and may well be one of Microsoft's most stable and usable products. It has a large suite of functions, is extensible via the VBA scripting language, and the interface has several handy shortcuts (such as double-clicking to copy calculations automatically). Although the calculation aspects of Excel are very closely mirrored in OpenOffice, the graph functionality in Excel is currently better than that of OpenOffice.
It amuses the archaeologically inclined that, as KBK observes, "The kernel of the Excel solver is the singular-value decomposition from Wilkinson [presumably Handbook for Automatic Computation Volume II - Linear Algebra]. That's essentially 1971-vintage Algol, rewritten first in Fortran and then in C."
[Annotate http://fox.wikis.com/wc.dll?Wiki~ExcelConstants~VFP , and also consider stealing John Machin's post as starting point for essay on why Excel is a terrible vehicle for data processing.]
The name of the native Excel file format apparently is BIFF. At some point, it'll be valuable to document the location of Microsoft references on BIFF, as well as whether the Perl and Python modules write formatted plaintext, or BIFF.
Example fragment of COM-based access (read) of Excel spreadsheet:
set cellVal [[$cells Item $msgNum $column] -get Value]
LV: From comp.lang.tcl, during June 2009, comes the following example. I trimmed the USENET header down a bit - see your favorite source of clt articles if you need the rest of the information.
From: Pete <[email protected]> Subject: Re: Accessing Excel from Tcl Newsgroups: comp.lang.tcl Date: Sun, 14 Jun 2009 16:45:15 -0600 On Thu, 11 Jun 2009 20:13:44 -0700 (PDT), S-Y. Chen wrote: > I need to access the Excel file through Tcl. Here's an example of using DDE to create a worksheet in Excel. Pete -- NN=01
# Playing with Excel and DDE
# Create a simple worksheet to calculate windchill value
# For a complete list of macro functions (such as OPEN, NEW, FORMULA etc.),
get the
# macrofun.exe program from Microsoft at:
http://support.microsoft.com/kb/q128185/
# It will extract a .cnt and .hlp file which you can place in any directory
you like
# If you put it in the same directory as Excel, make sure that, if there's
already a
# macrofun help file, you rename it so that it isn't overwritten (my system
didn't have one)
package require dde
# get a list of running apps
# This is also useful for seeing which book/worksheets are currently open
in Excel
# which must already be running when you try to access it via DDE
puts [dde services "" ""]
# A typical response might be:
# {excel {[Book1]Sheet1}} {excel {[Book1]Sheet2}} {excel {[Book1]Sheet3}}
{excel {[qsheet8.xla]Sheet1}} {excel System} {PROGMAN PROGMAN} {Shell
AppProperties} {Folders AppProperties} {PROGMAN PROGMAN}
# If this command doesn't return "Ready" then Excel isn't there
if {[dde request Excel System Status] != "Ready"} {
puts "Excel isn't running"
return
}
# This also returns a list of available book/worksheets
puts [dde request Excel System Topics]
### [:]: [Book1]Sheet1 [Book1]Sheet2 [Book1]Sheet3 [qsheet8.xla]Sheet1
System
# Create a new worksheet
# dde execute Excel System {[NEW(1)]}
# Open an existing file
# dde execute Excel System {[Open("powerform.xls")]}
# At the moment I don't know how to determine the name of this new book
# other than to get the services list and pick an entry from there.
# But, if you start excel from scratch it will already have a blank Book1
# so this example will just use the existing Book1
dde poke Excel {[Book1]Sheet1} "R2C1" "Temp:"
dde poke Excel {[Book1]Sheet1} "R2C3" "C"
dde poke Excel {[Book1]Sheet1} "R4C1" "Wind:"
dde poke Excel {[Book1]Sheet1} "R4C3" "km/hr"
dde poke Excel {[Book1]Sheet1} "R6C1" "Chill"
# Store the formula
dde poke Excel {[Book1]Sheet1} "R6C2" "=13.12 + 0.6215*B2 - 11.37*(B4^0.16)
+ 0.3965*B2*(B4^0.16)"
# Format the cell containing the formula
dde execute Excel System {[select("r6c2")]}
dde execute Excel System {[FORMAT.NUMBER("###0")]}
# Now set a temperature (in degrees C) and windspeed (in km/hr) and read
the result
dde poke Excel {[Book1]Sheet1} "R2C2" "-10"
dde poke Excel {[Book1]Sheet1} "R4C2" "20"
# NOTE that this returns the content of the cell but it also contains an
oddball character (a CR?)
# after the number and then a blank line
set chill [dde request Excel {[Book1]Sheet1} "R6C2"]
puts $chill
# Save this new workbook
# If the file already exists, excel will pop up a Save As dialog. If you
cancel that dialog
# TCL will report that "remote server cannot handle this command"
dde execute Excel System {[SAVE.AS("C:\tcl\bin\dde_chill.xls")]}
# And close the workbook - excel will still be running
dde execute Excel System {[CLOSE(1)]}If you view a CSV file in Excel, columns are labeled A..Z, AA..AZ, BA.. To convert these column labels to numeric indexes (0..), so as to access the data with lindex, here's a little helper:
proc excel'col2int col {
set abc {- A B C D E F G H I J K L M N O P Q R S T U V W X Y Z}
set int 0
foreach char [split $col ""] {
set int [expr {$int*26 + [lsearch $abc $char]}]
}
incr int -1 ;# one-letter columns start from A
} ;# RSSee Mapping words to integers for a more general take.
The VBA Object Browser (OB) provides access to "the procedures, objects, ... available for use in the active workbook ..."
To reach the OB, select "Tools/Macro/Visual Basic Editor". Once VB comes up, you should see a funny illustrated square icon near the right side of VB's standard toolbar; that's the OB, as its tooltip promises.
Also, "In Excel, do Alt-F11 to open the Visual Basic editor. Hit F2 for the command library. All the VBA methods and properties are available COM interfaces. If you can write a VBA macro to do it, you can do it via COM. Better yet, use the "record macro" feature, and then translate the VBA to Tcl/COM (after cleaning up the sloppy auto-generated code that Excel records, of course)." [ MKS in comp.lang.tcl, February 2007]
CL generates a lot of reports in such formats as PDF, .doc, and .xls. Convenient for some Excel work is an HTML image such as
<html>
<head>
<meta name=Title content="%s">
<meta name=Generator content="Microsoft Excel 10">
<style>
<!--table {}
.style0
{text-align:general;
vertical-align:bottom;
white-space:nowrap;
color:windowtext;
font-size:12.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
border:none;}
d
{padding-top:1px;
padding-right:1px;
padding-left:1px;
color:windowtext;
font-size:12.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
text-align:general;
vertical-align:bottom;
border:none;
white-space:nowrap;}
.xl19
{border:none;}
.xl20
{font-family:"Arial MT";
border:none;}
.xl21
{font-family:"Arial MT";
border:none;}
-->
</style>
</head>
<body>
<table border=0 cellpadding=0 cellspacing=0 width=17429 style='border-collapse:collapse;table-layout:fixed'>
<col class=xl19 width=115 span=3> <col class=xl19 width=115> <col class=xl19 width=115 span=252>
...
</table>
</body>
</html>RLH: That is a nifty thing there...thanks for the pointer. I was so focused on a binary .xls file that forest him me for the tree.
escargo: CL, is that something Excel can import? or just that its output looks like that. It's not clear to me what "Excel work" means in this context.
RLH: Excel seems to import it fine. I tried on Windows and OSX.
escargo: So is it supposed to be obvious what the data rows look like? It's not clear to me what all the column spans are about either.
RLH Maybe not obvious but it is a start. Play around with it and see what you can do?
CL has recently had success with "pipe-delimiting"; that is, writing tabular data as simply as possible, with '|' separating fields. Office workers seem to accept this as "spreadsheet format". Later, in fall 2004, CL's finding abundant headaches with CSV and tab- and pipe-delimiting, but is happy with results from HTML formatting into a <TABLE>.
Benny Riefenstahl: I have also have had success in opening simple HTML tables with Excel directly. This has the advantage of having a well-defined character encoding and makes it possible to use long multi-line text in cells.
RT: has "written code to create Excel files with fancy formatting and it's about 250 lines and not pretty." [supply ref]
Written by rmogster in 2009-05-06 (I think)
Does anyone know how to use tcl to read values from an excel spreadsheet and use the data to control a modelsim simulation?