Re: xlsread in Octave 3.6.4

[Philip Nienhuis]

Markus wrote:
> Am 2013-11-02 23:43, schrieb Philip Nienhuis:
> > Markus wrote:
> > > Am 2013-11-02 21:11, schrieb Philip Nienhuis:
> > > > Hi Markus,
> > > >
> > > > Markus wrote:
> > > > > <snip>
> > > > >
> > > > > I've pushed everything to my github repo[1] again (just the relevant
> > > > > files for io).
> > > > > hopefully i don't broke it now (That is quite possible) because i copy
> > > > > and paste a lot.
> > > > > Furthermore i've added some nice benchmarks at the end of the
> > > > > README.md[2]
> > > > >
> > > > > cheers
> > > > > markus
> > > > >
> > > > >
> > > > > [1] https://github.com/markuman/xlsxread
> > > > > [2] https://github.com/markuman/xlsxread#benchmark
> > > >
> > > > It seems you didn't fix the latest sources that I committed but rather
> > > > patched a local source?
> > > > But never mind, I again reformatted to Octave coding standards (wasn't
> > > > much work) and re-added the xls.limits assignment needed to avoid cell
> > > > errors in xls2oct.
> > > > I also had to (again) fix undefined references in __OCT_xlsx2oct__.m:
> > > > L. 117, 126: refs to "@__colnr", should be to "@__col_str_to_number" -
> > > > do you still have __colnr.m in your source tree?
> > >
> > > uh, maybe you're right. i develope with single file xlsxread.m and i
> > > guess i made often copy & paste errors.
> > >
> > > > As regards your remark "setup is a nightmare": All true.
> > > > But I made a function to make it easier for users, did you catch that?
> > > > (chk_spreadsheet_support.m); it checks all interfaces and adds all
> > > > required Java classes to the javaclasspath. All you have to do is
> > > > download them, put them together into one subdir and tell it to
> > > > chk_spreadsheet_support.m.
> > >
> > > yes, i did it with chk_spreadsheet_support. refering to
> > > https://wiki.archlinux.org/index.php/Octave#Reading_Microsoft_Excel_Spreadsheets
> > >
> > >
> > > but xlsread failed as long as JExcelAPI .jar file was added to java
> > > path. even when i force another interface.
> >
> > Hey.... I thought I had fixed that bug a while ago... I'll check later.

Fixed again, but I found another concealed bug. Hmmm....

> > <snip>
> > > > Before hitting "Send": I'm really impressed. Your xlsxread stuff is
> > > > really blazingly fast. testOOXML sheet #2 takes 2.5 secs with OCT,
> > > > 12.2 with POI, 34+ with UNO (LibreOffice 4.1.2) (all on WinXP)
> > >
> > > and matlab run out of memory (on linux 8GB ram). this is the most
> > > impressiv part for me.
> >
> > On my Win7 64b 8 GB RAM box, Octave & COM (=Excel 2013) takes just 6,5
> > secs to read example.xlsx. I can't test xlsxread (I have no
> > Octave-3.6.4 installed there yet); but UNO (LibreOffice 4.0) took a
> > leisurely 780 secs (15 mins). (Java) POI bailed out (a bug in old
> > io-1.2.2 I think).
>
> xlsxread test would be realy interesting on the same PC (What CPU do you
> have in this PC?).

Later today I'll try io-1.2.4rc1 there :-) I'll let you know.
It is some core i5 (Lenovo X230).

> > > next weeks i'll try implement to read just a range from xlsx files.
> >
> > I figured that a temporary kludge could be to just read all data
> > (xlsxread is fast enough), and then just select the requested
> > rectangle from the full data matrix.
>
> This way is the fastest for matlab on linux too and it could be
> internaly managed the same way in xlsxread.

It would be the easier to do when all non-numeric data has been read and 
filled in the data cell array as well.

> But there is one way to do it even faster. A function which generate a
> corresponding regexp pattern. There are some python codes in the www
> doing this. I start month ago such function (__getrange.m attached), but
> it failed (just valid for a range between A1 to Z9), is very worse and
> not trivial (for me).

Python... nothing against it, apart from the fact that it's another 
dependency and fewer dependencies are better.

> > BYW I think reading strings would also be nice. I already had a brief
> > look on how to do that (it is a 2 or 3-step procedure as the strings
> > are kept in some other sheet). Vectorizing the procedure will be the
> > real challenge.
>
> Step 1 is to identifiy the string position (It's one more regexp line i
> guess). Step 2 is reading all strings (the other xml file) and fill the
> output cell at the given position with the strings.

That's what I figured.

> I would do this with
> regexp again :)

Beware, regexp isn't always the fastest. I found that strfind() and 
index() are often much faster for easy patterns.

BTW, I hit an issue on XP that regexp performance can degrade sharply as 
soon as string length is over ~400,000 characters. It may be related to 
that specific Octave-3.6.4 regexp version though.

> > The io package spreadsheet I/O stuff supports a SPSH_OPTS argument
> > (actually a struct with fields that can be true (1) or false (0)). For
> > strings we can add a new field "oct_get_strings" or so and in the
> > _OCT_xlsx2oct__.m file we can have some if-endif's that test it and if
> > "yes"/true also read the strings. That way we can keep the current
> > speed if only numeric data are requested.

Thinking a bit more, once we have string reading implemented the default 
would be to read all data and the option to just read the numeric data 
(faster).

> I'll take a look into it in the next weeks. I have to spend more time
> for university stuff at the moment.

Of course, this is a volunteer project.

I think all improvements from now on are something for io-2.0.0. I 
already have some code for it lying around; an important goal is to 
significantly simplify the code exposed to users, another to simplify 
the rest.

Philip