[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]
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.''
----
!!!!!!
%| [Category Application] | [Category Windows] |%
!!!!!!