COM errors reading date cells in Excel files

[Philip Nienhuis]

Hi,

While finishing more complete xlsread and xlswrite functions for Octave
using the COM interface in the Windows package, I found that I get COM
errors when the requested data range in Excel worksheets contain numeric
values formatted as date or time strings.
I get:

  warning: cannot convert COM variant of type `7' to octave object

for each encountered date formatted non-empty cell, followed by

error: octave_base_value::resize (): wrong type argument `<unknown type>'

As these are COM errors I suspect the Windows package contains a bug in
the sense that Octave gets confused by the date formatting.
Formatting the relevant cells beforehand as numeric data avoids these
COM errors, so this is no real show stopper. But it IS a bit
inconvenient when processing lots of spreadsheets from third parties.

BTW Comparing this with Matlab r2007a at my employer's office, I saw
that Matlab converts date formatted cells to text strings(?), consistent
with
http://www.mathworks.com/access/helpdesk/help/techdoc/index.html?/access/helpdesk/help/techdoc/ref/xlsread.html&http://www.google.com/search?q=Matlab+Excel+date+formatted+cell&sourceid=mozilla-search&start=0&start=0&ie=utf-8&oe=utf-8&client=mozilla&rls=org.mozilla:en-US:unofficial

I've played around with English & Dutch Excel versions, to no avail -
Excel language version makes no difference.

FYI:
Excerpt of "pkg list":
windows *|   1.0.8
..\Octave\3.2.0_gcc-4.3.0\share\octave\packages\windows-1.0.8

Octave 3.2.0 compiled for MingW (also tried 3.2.2 with same results)

Windows XP SP2 & WinXP SP3 US-English

Thanks,

Philip

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Octave-dev mailing list
Octave-dev@...
https://lists.sourceforge.net/lists/listinfo/octave-dev

[Philip Nienhuis]

Philip Nienhuis wrote:

> Hi,
>
> While finishing more complete xlsread and xlswrite functions for Octave
> using the COM interface in the Windows package, I found that I get COM
> errors when the requested data range in Excel worksheets contain numeric
> values formatted as date or time strings.
> I get:
>
>  warning: cannot convert COM variant of type `7' to octave object
>
> for each encountered date formatted non-empty cell, followed by
>
> error: octave_base_value::resize (): wrong type argument `<unknown type>'
>
> As these are COM errors I suspect the Windows package contains a bug in
> the sense that Octave gets confused by the date formatting.

Fixed.
Turns out that in __COM__.cc Excel cell type VT_DATA
(declared/enumerated in wtypes.h) had to be added to the switch() stanza
as follows:
:
        case VT_DATE:
                retval = octave_value(var->dblVal);
                break;
:
somewhere around lines 390-410.
(I'm sorry, I have little experience making proper patch files;
hopefully the attached one made invoking "diff -rcs <files>" serves the
purpose.)

Please can the Windows package maintainer(s) apply this change?

> BTW Comparing this with Matlab r2007a at my employer's office, I saw
> that Matlab converts date formatted cells to text strings(?), consistent
> with
> http://www.mathworks.com/access/helpdesk/help/techdoc/index.html?/access/helpdesk/help/techdoc/ref/xlsread.html&http://www.google.com/search?q=Matlab+Excel+date+formatted+cell&sourceid=mozilla-search&start=0&start=0&ie=utf-8&oe=utf-8&client=mozilla&rls=org.mozilla:en-US:unofficial 

I think octave as it now stands behaves better than Matlab in this
respect. After all, Octave can inject (pre-prepared*) date numbers into
Excel cells formatted as dates, and Excel will display those cells
properly as dates. The other way round, reading date cells from Excel,
should work inversely as expected and return date numbers; octave now
does do this.

* Octave & Matlab have 1-1-0000 (or 0-1-0000?) as base while Excel
features 1-1-1900 (and erroneously assumes 1900 to be a leap year).

We might even discuss on-the-fly conversion of Excel & Octave/Matlab
dates, so that users don't have to add or subtract 693961 (1-Jan-1900 to
28-Feb-1900) or 693960 (from 1-March-1900 on) but have the COM interface
worry about that.


On a related note, I almost have xlsread.m and xlswrite.m script files +
support script files finished. How (or to who) can I contribute them in
practice?


Thank you,

Philip



*** __COM__.old Wed Jun 24 21:36:14 2009
--- __COM__.cc Fri Oct 23 12:22:55 2009
***************
*** 404,409 ****
--- 404,412 ----
  case VT_R8:
  retval = octave_value(var->dblVal);
  break;
+ case VT_DATE:
+ retval = octave_value(var->dblVal);
+ break;
  case VT_R4:
  retval = octave_value(var->fltVal);
  break;

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Octave-dev mailing list
Octave-dev@...
https://lists.sourceforge.net/lists/listinfo/octave-dev

[Philip Nienhuis]

Hi,

Here's another patch o __COM__.cc dealing with reading from Excel cells
containing results of erroneous operations (e.g. division by zero or
attempts to take the root of text strings).
I've chosen to treat those cells just like empty cells.

This patch is combined with yesterday's patch about date formatted cells.

Can the Windows package maintainer(s) apply this patch, please?

Thanks,

Philip


*** __COM__.old Wed Jun 24 21:36:14 2009
--- __COM__.cc Fri Oct 23 23:59:39 2009
***************
*** 404,415 ****
--- 404,421 ----
  case VT_R8:
  retval = octave_value(var->dblVal);
  break;
+ case VT_DATE:
+ retval = octave_value(var->dblVal);
+ break;
  case VT_R4:
  retval = octave_value(var->fltVal);
  break;
  case VT_EMPTY:
  retval = octave_value(Matrix());
  break;
+ case VT_ERROR:
+ retval = octave_value(Matrix());
+ break;
  default:
  if (var->vt & VT_ARRAY)
  {

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Octave-dev mailing list
Octave-dev@...
https://lists.sourceforge.net/lists/listinfo/octave-dev

[Michael Goffioul-2]

Commited. Thanks.

Michael.


On Sat, Oct 24, 2009 at 4:11 PM, Philip Nienhuis <pr.nienhuis@...> wrote:

> Hi,
>
> Here's another patch o __COM__.cc dealing with reading from Excel cells
> containing results of erroneous operations (e.g. division by zero or
> attempts to take the root of text strings).
> I've chosen to treat those cells just like empty cells.
>
> This patch is combined with yesterday's patch about date formatted cells.
>
> Can the Windows package maintainer(s) apply this patch, please?
>
> Thanks,
>
> Philip
>
>
> *** __COM__.old Wed Jun 24 21:36:14 2009
> --- __COM__.cc  Fri Oct 23 23:59:39 2009
> ***************
> *** 404,415 ****
> --- 404,421 ----
>        case VT_R8:
>                retval = octave_value(var->dblVal);
>                break;
> +       case VT_DATE:
> +               retval = octave_value(var->dblVal);
> +               break;
>        case VT_R4:
>                retval = octave_value(var->fltVal);
>                break;
>        case VT_EMPTY:
>                retval = octave_value(Matrix());
>                break;
> +       case VT_ERROR:
> +               retval = octave_value(Matrix());
> +               break;
>        default:
>                if (var->vt & VT_ARRAY)
>                {
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> http://p.sf.net/sfu/devconference
> _______________________________________________
> Octave-dev mailing list
> Octave-dev@...
> https://lists.sourceforge.net/lists/listinfo/octave-dev
>
>

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Octave-dev mailing list
Octave-dev@...
https://lists.sourceforge.net/lists/listinfo/octave-dev