[EKB]'s Intro to Excel: Excel is the spreadsheet component in [Microsoft Office]. Like other spreadsheet programs, such as [OpenOffice].org's Calc, it is somewhat limited, but very flexible. This can lead to use of spreadsheets when other, and better, tools are available [http://www.burns-stat.com/pages/Tutor/spreadsheet_addiction.html]. However, the flexibility can make it easy to develop quick solutions to problems, and the spreadsheet solution may turn out to be enough to do the job - like Tcl! Excel has a long development history, and (in this author's opinion) is the most stable and usable of the MS Office suite. 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). Again in this author's opinion, although the calculation aspects of Excel are very closely mirrored in http://OpenOffice.org/, the graph functionality in Excel is currently better than that in OpenOffice.

----
Ways to get to .xls data:
   * [DDE]
   * [COM], especially [tcom]
   * [CSV]
   * [ODBC] (there are rumors of faults in the drivers, though)
   * [Victor Wagner]'s xls2csv [http://www.45.free.net/~vitus/software/catdoc/]
   * xhHtml [http://chicago.sourceforge.net/xlhtml] is a command-line utility that can create XML output;
   * Spreadsheet::ParseExcel and related [Perl]-coded modules available through [CPAN];
   * Christoph Bauer's tclexcel [http://www.rhrk.uni-kl.de/~bauerc/tclexcel.html] or via the wayback machine: [http://web.archive.org/web/20040423070017/http://www-user.rhrk.uni-kl.de/~bauerc/tclexcel.html] 
   * http://www.greytrout.com/
   * [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.
   * the [Python]-based http://sourceforge.net/projects/pyxlwriter/ , alleged to be "a port of John McNamara's Perl Spreadsheet:WriteExcel ...";
   * John Machin's elegantly portable Python-based xlrd [http://www.lexicon.net/sjmachin/xlrd.htm], which operates independently of any Excel installation
   * the pyExcelerator [http://sourceforge.net/projects/pyexcelerator]
   * [http://www.posoft.de/html/extTcomExcel.html%|%TcomExcel%|%] from [Paul Obermeier]
   * [Apache POI]
   * [Excel/BIFF2 writer in pure Tcl]


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.


There is a freely-available Excel viewer
from Microsoft (MS Windows only)
[http://office.microsoft.com/downloads/2000/xlviewer.aspx];
For Linux/Unix, Gnumeric [http://www.gnome.org/projects/gnumeric/]
and StarOffice [http://www.staroffice.com/] can import Excel files;

----
[peterc] 2008-07-17: Can anyone suggest something which handles conversions from XLS to CSV where fields are multi-line (eg, paragraphed text)? Microsoft's own export function is rubbish at this. (Even something which converts an in-field newline to a tab or space would be a step up.)
----

I have written a tcl wrapper [Using Perl to get Excel] to make the writing of excel files using perl's Spreadsheet:WriteExcel very managable - [JBR].

----

More information is available through http://www.wotsit.org/ [[find more precise URL]]. 
http://www.wotsit.org/download.asp?f=xls

Links directly to the fileformat are not allowed anymore on wotsit. So you have to go
to "Spreadsheet/Database" and look for "XLS".


----
[["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.

----
The class library "Jakarta POI - HSSF - Java APIs with XML manipulate MS-Excel" (http://jakarta.apache.org/poi/hssf/) can be used in combination with [TclJava].
----
Example fragment of COM-based access (read) of Excel spreadsheet:

    set cellVal [[$cells Item $msgNum $column] -get Value]
----
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.
----


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


----
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]]
----
"[How to launch a Tcl program from Excel]"




----
[RT] has "written code to create Excel files with fancy formatting
and it's about 250 lines and not pretty."  [[supply ref]]
----
[George Petasis] An example of applying formatting can be found in [Tcom examples for Microsoft Excel]
----
[[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.]]
----
[excel xml] is a package that creates excel xml files. it can be used with office 2003.
----
Antiexcel [http://sf.net/projects/antiexcel/]
renders Excel content as [plaintext].
xlhtml [http://chicago.sf.net/xlhtml/] is also pertinent.



----
[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;}
   td
        {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] - So, [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?
----
"[How can I find the number of active rows/read a particular cell/... in an Excel spreadsheet?]"

----
[LV] From comp.lang.tcl, [Georgios Petasis] points us to http://www.ellogon.org/~petasis/tcl/Excel.tcl
which he says is 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.''
----
[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 <el_suprem...@yahoo.co.uk>
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)]}



======

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

----

====================

Does anyone know of any resources explaining how to write to/create an open
office spreadsheet (.ods) using tcl?

I have imported data using Mk4tcl but would like to avoid the intermediate 
step of exporting to an excel file format using the above.


!!!!!!
%| [Category Application] | [Category Windows] |%
!!!!!!