Re: Spreadsheet translation

[Philip Nienhuis]

OK Allen,

Windhorn, Allen E [ACIM/LSA/MKT] wrote:
> Philip,
>
> > -----Original Message-----
> > From: Help-octave On Behalf Of  PhilipNienhuis
> >
> > Did you read the spreadsheet with option 'struct ("formulas_as_text", 1)' ?
>
> Yes.
>
> > If yes, please try another spreadsheet I/O "interface". It looks to me like
> > you have no additional SW installed so you fall back to the OCT interface.
> > That hasn't received much testing with formula output, so I'm not very
> > surprised that large parts of the spreadsheet cell XML are left in place.
>
> Yes, I believe I am using the OCT interface, it was supposed to be best for
> Windows.

? who says so?
OCT is good enough, but Excel itself ("COM" interface) is by far the 
fastest. But in case of COM errors you're left with zombie Excel 
invocations that may keep a hold on you spreadsheet file (kill them off 
in Task Manager), in case of OCT errors you may merely have leaked file 
handles.

> > ...Chances are that with additional spreadsheet I/O SW like Apache POI, or,
> > if you are on Windows, just the OF windows package and MS-Excel, you'd
> > able to extract the formulae in a format more suitable for what you want to
> > achieve.
>
> I tried the COM interface:
>
> options.formulas_as_text = 1;

... then you need to convey "options" as an argument to xls2oct. But 
apparently you did.

> xlh = xlsopen('GENDH_Dissection01.xlsm', 0, 'COM'); % Open read only 
> Octave complains that Java is installed but unusable

Yeah that's a known one. Do you have 64-bit Octave installed (I guess 
so)? Octave & Java bit widths should match.

> ... OK, it complained, but when I looked, it still produced the correct
> formulas, so that's sorted for the time being... I would like to know how
> to make Java work though, in case I need it for something else.
>
> I don't know what the "OF windows package" is -- is it different from COM?

It's the same (Octave-forge windows package supplies ActiveX / COM 
functionality).

> And how would I find out more about the other Java packages (assuming I
> could get any to work with our Java installation)?

E.g., on the wiki (https://wiki.octave.org/IO_package).

There's a READ-XLS.htm file in the doc/ subdirectory of the io package 
but I think the wiki is a bit clearer.
If you have the required Java .jar files together in some subdirectory, 
you can do:

chk_spreadsheet_support ('/full/path/to/that/subdirectory')
  ## followed by
javaclasspath
  ## to see which Java class libs are loaded

and it that path happens to be in your %USERPROFILE% (e.g.,
C:\user\<your_login_name>\Java)
that will even happen automagically when loading the io package.

Philip