bill lam wrote:
> J can read/write excel files without excel for years. Dyalog also can
> but apparently no one yet have interest because it's too trivial to
> use com/.net to do the job. The trap is you must use window and buy
>the license for excel.
I'm sure J has excellent tools for reading and writing Excel files. But...
1) As you say, APL users have also had tools to read&write Excel files
directly, but the OLE Automation interface provides a really easy way to access
not only data but formatting information (use Excel as a report formatting
tool), to call Excel macros, etc etc. So you can't really compare the two.
Also, it has also been stable across many releases of Excel, and is independent
of the file format used. I can read&write both xls and xlsx files through it,
using the same code. So there are good reasons why APL users have decided to
use it. And APL users really are not "trapped", because:
2) If I go to Google and type "free xls xlsx reader ole", I get 3520 hits (and
27700 if I change ole to .net). I did not look carefully, but many of these
seem to be links to free tools that I can just slot straight in and use. So I'm
not limited to using tools written by fellow enthusiasts in Dyalog (this is the
main point of the OLE and .NET interfaces). With a day of work I could probably
even run J as an automation server and use the tools written for it as well, if
I decided Ric's tools were the best.
3) Most of the customers of our customers insist on using Excel/Windows anyway
(and beg vendors of applications to use Excel as a frontend, so Excel is always
there, "where the money is"). The OLE interface allows us to get/set data in a
running instance of Excel, where the customers have loaded and prepared data -
they don't need to save and then "process" the workbook. So this really is a
non issue for most customers. OK, some people prefer Linux - but will they then
also insist on Excel? Seems like a very rare case. Anyway, that will be solved
when we do the "Mono" bridge - won't be long now.
... So the idea that Dyalog users are "trapped" because they "bit the hook"
doesn't really go very far. They have more - and better, easier to use - "glue"
than anybody. That's probably the key selling point, actually.
/ Morten
P.S. The COM/NET stuff also works from the "inside": The following text is
copied from a file which implements a simple Excel Add-In using "VSTO". The
file is part of a Visual Studio "project", using our tools for saving APL code
in scripts (and if I debug it while APL is running inside Excel, the source
file is automatically updated in the project).
:Class LinReg
:Using System.Windows.Forms,system.windows.forms.dll
∇ FitAndChart;top;left;bot;right;cells;range
:Access Public
⎕SE.UCMD 'Load LinReg' ⍝ Load the Linear
Regression script
:If 1=1↑⍴data←Application.Selection.{6::⍬ ⋄ Value2}⍬ ⍝ Does selection contain
:AndIf (0=1↑0⍴data)∧1=≡data ⍝ a single row of numbers?
top left←Application.Selection.(Row Column) ⍝ Top Left of selection
bot right←top left+0 ¯1+⍴data ⍝ Bottom right
cells←⊂⎕A[left],(⍕top),':',⎕A[right],⍕bot ⍝ Include row below
selection
range←Application.ActiveSheet.Range[cells] ⍝ We are going to plot
this range
range.Value2←data⍪#.LinReg.Fit,data ⍝ Insert fitted data
c1←Application.Charts.Add ⍬ ⍝ Add a chart
c1.SetSourceData range 1 ⍝ Point to data
c1.(ChartType←xlLineMarkers) ⍝ Line Chart
c1.(SeriesCollection¨1 2).Name←'="Observations"' '="Fitted Curve"'
:Else
MessageBox.Show⊂'You must select a single row of numeric data.'
:EndIf
∇
∇ make app
:Access Public
:Implements Constructor
:Signature make Microsoft.Office.Interop.Excel.Application
Application←2 ⎕NQ 'app' 'OleQueryInterface' 'IDispatch' ⍝ /// Extract COM
interface from .NET handle
∇
:EndClass
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
