## Copyright (C) 2012,2013,2014 Philip Nienhuis
##
## This program is free software; you can redistribute it and/or modify it under
## the terms of the GNU General Public License as published by the Free Software
## Foundation; either version 3 of the License, or (at your option) any later
## version.
##
## This program is distributed in the hope that it will be useful, but WITHOUT
## ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
## FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
## details.
##
## You should have received a copy of the GNU General Public License along with
## this program; if not, see <http://www.gnu.org/licenses/>.

## -*- texinfo -*- 
## @deftypefn {Function File} io_xls_testscript (@var{intf1})
## @deftypefnx {Function File} io_xls_testscript (@var{intf1}, @var{fname})
## @deftypefnx {Function File} io_xls_testscript (@var{intf1}, @var{fname}, @var{intf2})
## Try to check proper operation of XLS / XLSX spreadsheet scripts using
## interface @var{intf1}.
##
## @var{intf1} can be one of COM, POI, JXL, OXS, UNO, or OCT.  No checks
## are made as to whether the requested interface is supported at all.  If
## @var{fname} is supplied, that filename is used for the tests, otherwise
## filename "io-test.xls" is chosen by default.  This parameter is required
## to have e.g., POI distinguish between testing .xls (BIFF8) and .xlsx
## (OOXML) files.
##
## If @var{intf2} is supplied, that interface will be used for writing the
## spreadsheet file and @var{intf1} will be used for reading.  The OCT
## interface doesn't have write support (yet), so it will read spreadsheet
## files made by POI (if supported) unless another interface is supplied
## for @var{intf2}.
##
## As the tests are meant to be run interactively, no output arguments are
## returned. The results of all test steps are printed on the terminal.
##
## @seealso {test_spsh, io_ods_testscript}
##
## @end deftypefn

## Author: Philip Nienhuis <pr.nienhuis at users.sf.net>
## Created: 2012-02-25
## Updates:
## 2012-06-06 Adapted to COM implementation for "formulas_as_text" option
## 2012-09-04 Added delay between LibreOffice calls to avoid lockups with UNO
## 2013-04-21 Made it into a function
## 2013-12-12 Updated copyright strings
## 2013-12-19 Add option to write and read with different interfaces (needed for OCT)
## 2013-12-31 More extensive texinfo help text
## 2013-12-31 Style fixes
##     ''     Also delay a bit if write intf2 = UNO to avoid unzip errors
## 2014-01-01 Provisionally fall back to COM or POI for intf2
##     ''     Provide default test file name

function io_xls_testscript (intf, fname="io-test.xls", intf2=[])

  printf ("\nTesting .xls interface %s using file %s...\n", upper (intf), fname);

  isuno = false;
  dly = 0.25;
%  if (strcmpi (intf, 'oct'))
%  ## FIXME commented out/replaced until UNO & OXS OOXML write support is fixed
%##  if (isempty (intf2))
%    if (isempty (intf2) || ! strcmpi (intf2, "com"))
%      intf2 = 'poi';
%    endif
%    printf ("OCT interface has no write support enabled - writing is done with %s.\n", intf2);
%  endif
  ## If no intf2 is supplied, write with intf1
  if (isempty (intf2))
    intf2 = intf;
  endif
  ## Allow the OS some delay to accomodate for file operations (zipping etc.)
  if (strcmpi (intf, "uno") || strcmpi (intf2, "uno") ||
      strcmpi (intf, "oct") || strcmpi (intf2, "oct"));
    isuno = true;
  endif

  ## 1. Initialize test arrays
  printf ("\n 1. Initialize arrays.\n");
  arr1 = [ 1 2; 3 4.5];
  arr2 = {'r1c1', '=c2+d2'; '', 'r2c2'; true, -83.4};
  opts = struct ("formulas_as_text", 0);

  ## 2. Insert empty sheet
  printf ("\n 2. Insert first empty sheet.\n");
  xlswrite (fname, {''}, 'EmptySheet', 'b4', intf2); 
  if (isuno)
    sleep (dly);
  endif
  
  ## 3. Add data to test sheet
  printf ("\n 3. Add data to test sheet.\n");
  xlswrite (fname, arr1, 'Testsheet', 'c2:d3', intf2);
  if (isuno)
    sleep (dly);
  endif
  xlswrite (fname, arr2, 'Testsheet', 'd4:z20', intf2);
  if (isuno)
    sleep (dly);
    if (strcmpi (intf, "oct") || strcmpi (intf2, "oct"))
      ## Some more delay to give zip a breath
      sleep (dly); sleep (dly);
    endif
  endif
  
  ## 4. Insert another sheet
  printf ("\n 4. Add another sheet with just one number in A1.\n");
  xlswrite (fname, [1], 'JustOne', 'A1', intf2);
  if (isuno)
    sleep (dly);
  endif

  ## 5. Get sheet info & find sheet with data and data range
  printf ("\n 5. Explore sheet info.\n");
  [~, shts] = xlsfinfo (fname, intf);
  if (isuno)
    sleep (dly);
  endif
  shnr = strmatch ('Testsheet', shts(:, 1));      ## Note case!
  if (isempty (shnr))
    printf ("Worksheet with data not found - not properly written ... test failed.\n");
    return
  endif
  crange = shts{shnr, 2};                       ## Range can be unreliable
  if (strcmpi (crange, "A1:A1"))
    crange = ''
  endif

  ## 6. Read data back
  printf ("\n 6. Read data back.\n");
  [num, txt, raw, lims] = xlsread (fname, shnr, crange, intf);
  if (isuno)
    sleep (dly);
  endif
  
  ## First check: has anything been read at all?x
  if (isempty (raw))
    printf ("No data at all have been read... test failed.\n");
    return
  elseif (isempty (num))
    printf ("No numeric data have been read... test failed.\n");
    return
  elseif (isempty (txt))
    printf ("No text data have been read... test failed.\n");
    return
  endif

  ## 7. Here come the tests, part 1
  err = 0;
  printf ("\n 7. Tests part 1 (basic I/O):\n");
  try
    printf ("    ...Numeric array... ");
    assert (num(1:2, 1:3), [1, 2, NaN; 3, 4.5, NaN], 1e-10);
    assert (num(4:5, 1:3), [NaN, NaN, NaN; NaN, 1, -83.4], 1e-10);
    assert (num(3, 1:2), [NaN, NaN], 1e-10);
    # Just check if it's numeric, the value depends too much on cached results
    assert (isnumeric (num(3,3)), true);
    printf ("matches...\n");
  catch
    printf ("Hmmm.... error, see 'num'\n");
    err = 1;
  end_try_catch
  try
    printf ("    ...Cellstr array... ");
    assert (txt{1, 1}, 'r1c1');
    assert (txt{2, 2}, 'r2c2');
    printf ("matches...\n");
  catch
    printf ("Hmmm.... error, see 'txt'\n");
    err = 1;
  end_try_catch
  try
    printf ("    ...Boolean... ");
    assert (islogical (raw{5, 2}), true);              ## Fails on COM
    printf ("recovered...\n");
  catch
    try
      if (isnumeric (raw{5, 2}))
        printf ("returned as numeric '1' rather than logical TRUE.\n");
      else
        printf ("Hmmm.... error, see 'raw{5, 2}'\n");
      endif
    catch
      err = 1;
      printf ("Hmmm.... error....\n");
    end_try_catch
  end_try_catch
  if (err)
    ## Echo array to screen
    printf ("These are the data read back:\n");
    raw
    printf ("...and this what they should look like:\n");
    printf ("%s\n", ...
    "{\n  [1,1] =  1\n  [2,1] =  3\n  [3,1] = [](0x0)\n  [4,1] = [](0x0)\n  [5,1] = [](0x0)\n  [1,2] =  2\n  [2,2] =  4.5000\n  [3,2] = r1c1\n  [4,2] =\n  [5,2] =  1\n  [1,3] = [](0x0)\n  [2,3] = [](0x0)\n  [3,3] =  3\n  [4,3] = r2c2\n  [5,3] = -83.400\n}");
    
  endif
  
  ## Check if "formulas_as_text" option works:
  printf ("\n 8. Repeat reading, now return formulas as text\n");
  opts.formulas_as_text = 1;
  xls = xlsopen (fname, 0, intf);
  if (isuno)
    sleep (dly);
  endif
  raw = xls2oct (xls, shnr, crange, opts);
  if (isuno)
    sleep (dly);
  endif
  xls = xlsclose (xls);
  if (isuno)
    sleep (dly);
  endif
  clear xls;

  ## 9. Here come the tests, part 2.
  printf ("\n 9. Tests part 2 (read back formula):\n");

  try
    # Just check if it contains any string
    assert ( (ischar (raw{3, 3}) && ! isempty (raw(3, 3)) && raw{3, 3}(1) == "="), true); 
    printf ("    ...OK, formula recovered ('%s').\n", raw{3, 3});
  catch
    printf ("Hmmm.... error, see 'raw(3, 3)'");
    if (size (raw, 1) >= 3 && size (raw, 2) >= 3)
      if (isempty (raw{3, 3}))
        printf (" (empty, should be a string like '=c2+d2')\n");
      elseif (isnumeric (raw{3, 3}))
        printf (" (equals %f, should be a string like '=c2+d2')\n", raw{3, 3}); 
      else
        printf ("\n");
      endif
    else
      printf (".. raw{3, 3} doesn't even exist, array too small... Test failed.\n");
    endif
  end_try_catch
  
  ## 10. Clean up
  printf ("\n10. Cleaning up.....");
  delete (fname);
  clear arr1 arr2 xls num txt raw lims opts shnr shts crange intf2 err;
  printf (" OK\n");
  
  endfunction
