[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
* [Jython]'s capabilities [http://www.manaware.net/jython-guide/read-an-excel-file.html]
* 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]
* [Eagle], see [http://eagle.to/cgi-bin/fossil/artifact?filename=Eagle/Library/Tests/excel2.eagle&ci=tip] in the test suite
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
.
[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
[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
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/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.
----
[SRIV] Sept 18,2010 I wrote a quick .ods writer, i'll post it on the wiki. [http://wiki.tcl.tk/27147]
----
!!!!!!
%| [Category Application] | [Category Windows] |%
!!!!!!