'''[http://office.microsoft.com/excel/%|%Excel]''', by [Microsoft], provides a
Spreadsheet. It is distributed individually and as a component of [Microsoft
Office].
** See Also **
[excel xml]:
[MS Excel XML to tablelist]:
[Tcom examples for Microsoft Excel]: [George Petasis]: An example of applying formatting
[How to launch a Tcl program from Excel]:
[OpenOffice]: spreadsheet component is called Calc
[Using Perl to get Excel]: [JBR]: I have written a tcl wrapper to make the writing of Excel files using perl's Spreadsheet:WriteExcel very managable.
[How can I find the number of active rows/read a particular cell/... in an Excel spreadsheet?]:
[http://www.ellogon.org/~petasis/tcl/Excel.tcl%|%excel] ,[George Petasis]: an [itcl] [class] that while ''most of the code is application specific, there are lots of functions that do interesting things, like opening/saving/creating spreadsheets, setting/reading cells, colouring cells, locking the spreadsheet, etc. Just look into the code and use whatever you need.'' See [https://groups.google.com/d/msg/comp.lang.tcl/kakvpc1I_tY/nteAMF2lLw8J%|%TCOM Excel: learning COM...] ,[comp.lang.tcl] ,2009-04-02
** Documentation **
[http://fox.wikis.com/wc.dll?Wiki~ExcelConstants%|%Excel Constants]:
** Description **
lLike 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.]]
** BIFF **
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.
** Non-Tcl Tools for Working with Excel **
[http://www.microsoft.com/en-us/download/details.aspx?id=10%|%Excel Viewer]: a freely-available program from [Microsoft]
[http://www.gnome.org/projects/gnumeric/%|%Gnumeric]: an open-source spreadsheet program with Excel support.
[OpenOffice]: can import Excel files;
[http://web.archive.org/web/20070806160718/http://www.45.free.net/~vitus/software/catdoc/%|%xls2csv] ,[Victor Wagner]:
Spreadsheet::ParseExcel and related [Perl]-coded modules: available through [CPAN]
[http://www.lexicon.net/sjmachin/xlrd.htm%|%xlrd] ,John Machin: a [Python] library which operates independently of any Excel installation. Elegantly portable Python-based xlrd
[http://sourceforge.net/projects/pyexcelerator%|%pyExcelerator]: Generating Excel 97+ files with Python 2.4+ (need decorators), importing Excel 95+ files, support for UNICODE in Excel files, using variety of formatting features and printing options, Excel files and OLE2 compound files dumper. No need in Windows/COM
[Apache POI]: The Java API for Microsoft Documents, The class library "Jakarta POI - HSSF - Java APIs with XML manipulate MS-Excel" can be used in combination with [TclJava].
[http://sf.net/projects/antiexcel/%|%antiexcel]]: renders Excel content as [plaintext]
** Tcl Tools for Working with Excel **
[DDE]:
[COM]: especially [tcom]
[CSV]:
[ODBC]: there are rumors of faults in the drivers, though
[http://chicago.sourceforge.net/xlhtml%|%xlhtml]: a command-line utility that can create XML output
[http://web.archive.org/web/20040423070017/http://www-user.rhrk.uni-kl.de/~bauerc/tclexcel.html%|%tclexcel] ,[Christoph Bauer]:
[http://www.greytrout.com%|%SpreadScript] (proprietary):
[SYLK]: Yet another rumor has it that Excel reads an old, but useful, format called sylk (SYmbolic LinK) which can be parsed in Tcl [SYLK]. What you do with the data is another thing.
[http://pyxlwriter.sourceforge.net/%|%pyXLWriter]: a [Python] library, alleged to be "a port of John McNamara's Perl Spreadsheet:WriteExcel ...";
[https://wiki.python.org/jython/PoiExample#Read_an_Excel_file%|%Jython: read an Excel File]: a [Jython] example
[http://www.cawt.tcl3d.org%|%CAWT] ,[Paul Obermeier]: [COM] Automation with Tcl
[Excel/BIFF2 writer in pure Tcl]:
[Eagle]: see [https://eagle.to/cgi-bin/fossil/artifact?filename=Eagle/Library/Tests/excel2.eagle&ci=tip] in the test suite
[excel xml]: is a package that creates excel xml files. it can be used with office 2003.
** Example: [COM] **
Example fragment of COM-based access (read) of Excel spreadsheet:
======
set cellVal [[$cells Item $msgNum $column] -get Value]
======
** Example: Create a Worksheet with DDE **
[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.
======none
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)]}
======
** Viewing CSV in Excel **
If you view a [csv%|%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
} ;# RS
======
See [Mapping words to integers] for a more general take.
** VBA Object Browser **
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]]
** Viewing HTML tables in Excel **
[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
======none
<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?
** Misc **
: "If you tab delimit the data and name it myfile.xls, current versions of excel will 'do the right thing'."
[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?
<<categories>> Application | Windows | Category Spreadsheet