'''[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 [Parsing Excel XLSX]: [Reading XLSX file (Excel OpenXML) using vfs::zip and tdom]: ** Documentation ** [http://fox.wikis.com/wc.dll?Wiki~ExcelConstants%|%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 ** [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