1 ## Copyright (C) 2012 Philip Nienhuis <pr.nienhuis at users.sf.net>
3 ## This program is free software; you can redistribute it and/or modify it under
4 ## the terms of the GNU General Public License as published by the Free Software
5 ## Foundation; either version 3 of the License, or (at your option) any later
8 ## This program is distributed in the hope that it will be useful, but WITHOUT
9 ## ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
10 ## FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
13 ## You should have received a copy of the GNU General Public License along with
14 ## this program; if not, see <http://www.gnu.org/licenses/>.
16 ## (Internal function) Check proper operation of XLS spreadsheet scripts.
17 ## Before running, a character variable 'intf' should be initialized with
18 ## a value of 'com', 'poi', 'jxl', 'oxs', or 'uno'.
20 ## Author: Philip Nienhuis
21 ## Created: 2012-02-25
23 ## 2012-06-06 Adapted to COM implementation for "formulas_as_text" option
26 printf ("\nTesting .xls interface %s ...\n", intf);
28 if (strcmp (lower (intf), 'oxs'));
29 printf ("OXS interface has no write support enabled - writing is done with POI.\n");
35 ## 1. Initialize test arrays
36 printf ("\n 1. Initialize arrays.\n");
38 arr2 = {'r1c1', '=c2+d2'; '', 'r2c2'; true, -83.4};
39 opts = struct ("formulas_as_text", 0);
41 ## 2. Insert empty sheet
42 printf ("\n 2. Insert first empty sheet.\n");
43 xlswrite ('io-test.xls', {''}, 'EmptySheet', 'b4', intf2);
45 ## 3. Add data to test sheet
46 printf ("\n 3. Add data to test sheet.\n");
47 xlswrite ('io-test.xls', arr1, 'Testsheet', 'c2:d3', intf2);
48 xlswrite ('io-test.xls', arr2, 'Testsheet', 'd4:z20', intf2);
50 ## 4. Insert another sheet
51 printf ("\n 4. Add another sheet with just one number in A1.\n");
52 xlswrite ('io-test.xls', [1], 'JustOne', 'A1', intf2);
54 ## 5. Get sheet info & find sheet with data and data range
55 printf ("\n 5. Explore sheet info.\n");
56 [~, shts] = xlsfinfo ('io-test.xls', intf);
57 shnr = strmatch ('Testsheet', shts(:, 1)); # Note case!
58 crange = shts{shnr, 2};
61 printf ("\n 6. Read data back.\n");
62 [num, txt, raw, lims] = xlsread ('io-test.xls', shnr, crange, intf);
64 ## 7. Here come the tests, part 1
65 printf ("\n 7. Tests part 1 (basic I/O):\n");
67 printf (" ...Numeric array... ");
68 assert (num(1:2, 1:3), [1, 2, NaN; 3, 4.5, NaN], 1e-10);
69 assert (num(4:5, 1:3), [NaN, NaN, NaN; NaN, 1, -83.4], 1e-10);
70 assert (num(3, 1:2), [NaN, NaN], 1e-10);
71 # Just check if it's numeric, the value depends too much on cached results
72 assert (isnumeric (num(3,3)), true);
73 printf ("matches...\n");
75 printf ("Hmmm.... error, see 'num'\n"); keyboard
78 printf (" ...Cellstr array... ");
79 assert (txt{1, 1}, 'r1c1');
80 assert (txt{2, 2}, 'r2c2');
81 printf ("matches...\n");
83 printf ("Hmmm.... error, see 'txt'\n"); keyboard
86 printf (" ...Boolean... ");
87 assert (islogical (raw{5, 2}), true); # Fails on COM
88 printf ("recovered...\n");
90 if (isnumeric (raw{5, 2}))
91 printf ("returned as numeric '1' rather than logical TRUE.\n");
93 printf ("Hmmm.... error, see 'raw{5, 2}'\n"); keyboard
97 ## Check if "formulas_as_text" option works:
98 printf ("\n 8. Repeat reading, now return formulas as text\n");
99 opts.formulas_as_text = 1;
100 xls = xlsopen ('io-test.xls', 0, intf);
101 raw = xls2oct (xls, shnr, crange, opts);
102 xls = xlsclose (xls);
104 ## 9. Here come the tests, part 2.
105 printf ("\n 9. Tests part 2 (read back formula):\n");
108 # Just check if it contains any string
109 assert ( (ischar (raw{3, 3}) && ~isempty (raw(3, 3)) && raw{3, 3}(1) == "="), true);
110 printf (" ...OK, formula recovered ('%s').\n", raw{3, 3});
112 printf ("Hmmm.... error, see 'raw(3, 3)'");
113 if (isnumeric (raw{3, 3}))
114 printf (" (equals %f, should be a string like '=c2+d2')\n", raw{3, 3});
121 printf ("\n10. Cleaning up.....");
122 delete 'io-test.xls';
123 clear arr1 arr2 xls num txt raw lims opts shnr shts crange intf2;