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?
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 TCOM Excel: learning COM... ,comp.lang.tcl ,2009-04-02
Parsing Excel XLSX
Reading XLSX file (Excel OpenXML) using vfs::zip and tdom
ooxml - ECMA-376 Office Open XML File Formats (spreadsheet)

Documentation

Excel Constants

Description

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.]

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

Excel Viewer
a freely-available program from Microsoft
Gnumeric
an open-source spreadsheet program with Excel support.
OpenOffice
can import Excel files;
xls2csv ,Victor Wagner
Spreadsheet::ParseExcel and related Perl-coded modules
available through CPAN
xlrd ,John Machin
a Python library which operates independently of any Excel installation. Elegantly portable Python-based xlrd
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.
[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
xlhtml
a command-line utility that can create XML output
tclexcel ,Christoph Bauer
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.
pyXLWriter
a Python library, alleged to be "a port of John McNamara's Perl Spreadsheet:WriteExcel ...";
Jython: read an Excel File
a Jython example
CAWT ,Paul Obermeier
COM Automation with Tcl
Excel/BIFF2 writer in pure Tcl
Eagle
see [1 ] 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.

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 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

<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?