octave_packages/io-1.0.19/xlsclose.m

   1 ## Copyright (C) 2009,2010,2011,2012 Philip Nienhuis <prnienhuis at users.sf.net>
   2 ##
   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
   6 ## version.
   7 ##
   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
  11 ## details.
  12 ##
  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/>.
  15
  16 ## -*- texinfo -*-
  17 ## @deftypefn {Function File} [@var{xls}] = xlsclose (@var{xls})
  18 ## @deftypefnx {Function File} [@var{xls}] = xlsclose (@var{xls}, @var{filename})
  19 ## @deftypefnx {Function File} [@var{xls}] = xlsclose (@var{xls}, "FORCE")
  20 ## Close the Excel spreadsheet pointed to in struct @var{xls}, if needed
  21 ## write the file to disk. Based on information contained in @var{xls},
  22 ## xlsclose will determine if the file should be written to disk.
  23 ##
  24 ## If no errors occured during writing, the xls file pointer struct will be
  25 ## reset and -if COM interface was used- ActiveX/Excel will be closed.
  26 ## However if errors occurred, the file pinter will be untouched so you can
  27 ## clean up before a next try with xlsclose().
  28 ## Be warned that until xlsopen is called again with the same @var{xls} pointer
  29 ## struct, hidden Excel or Java applications with associated (possibly large)
  30 ## memory chunks are kept in memory, taking up resources.
  31 ## If (string) argument "FORCE" is supplied, the file pointer will be reset
  32 ## regardless, whether the possibly modified file has been saved successfully
  33 ## or not. Hidden Excel (COM) or OpenOffice.org (UNO) invocations may live on,
  34 ## possibly even impeding proper shutdown of Octave.
  35 ##
  36 ## @var{filename} can be used to write changed spreadsheet files to
  37 ## an other file than opened with xlsopen(); unfortunately this doesn't work
  38 ## with JXL (JExcelAPI) interface.
  39 ##
  40 ## You need MS-Excel (95 - 2010), and/or the Java package => 1.2.8 plus Apache
  41 ## POI > 3.5 and/or JExcelAPI and/or OpenXLS and/or OpenOffice.org or clones
  42 ## installed on your computer + proper javaclasspath set, to make this
  43 ## function work at all.
  44 ##
  45 ## @var{xls} must be a valid pointer struct made by xlsopen() in the same
  46 ## octave session.
  47 ##
  48 ## Examples:
  49 ##
  50 ## @example
  51 ##   xls1 = xlsclose (xls1);
  52 ##   (Close spreadsheet file pointed to in pointer struct xls1; xls1 is reset)
  53 ## @end example
  54 ##
  55 ## @seealso {xlsopen, xlsread, xlswrite, xls2oct, oct2xls, xlsfinfo}
  56 ##
  57 ## @end deftypefn
  58
  59 ## Author: Philip Nienhuis
  60 ## Created: 2009-11-29
  61 ## Updates:
  62 ## 2010-01-03 (checked OOXML support)
  63 ## 2010-08-25 See also: xlsopen (instead of xlsclose)
  64 ## 2010-10-20 Improved tracking of file changes and need to write it to disk
  65 ## 2010-10-27 Various changes to catch errors when writing to disk;
  66 ##     "      Added input arg "keepxls" for use with xlswrite.m to save the
  67 ##     "      untouched file ptr struct in case of errors rather than wipe it
  68 ## 2010-11-12 Replaced 'keepxls' by new filename arg; catch write errors and
  69 ##            always keep file pointer in case of write errors
  70 ## 2011-03-26 Added OpenXLS support
  71 ## 2011-05-18 Added experimental UNO support, incl. saving newly created files
  72 ## 2011-09-08 Bug fix in check for filename input arg
  73 ## 2012-01-26 Fixed "seealso" help string
  74
  75 function [ xls ] = xlsclose (xls, varargs)
  76
  77         force = 0;
  78
  79         if (nargin > 1)
  80                 for ii=2:nargin
  81                         if (strcmp (lower (varargin{ii}), "force"))
  82                                 # Close .ods anyway even if write errors occur
  83                                 force = 1;
  84                         elseif (~isempty (strfind (tolower (varargin{ii}), '.')))
  85                                 # Apparently a file name
  86                                 if (xls.changed == 0 || xls.changed > 2)
  87                                         printf ("File %s wasn't changed, new filename ignored.", xls.filename);
  88                                 elseif (strcmp (xls.xtype, 'JXL'))
  89                                         error ("JXL doesn't support changing filename, new filename ignored.");
  90                                 elseif ~((strcmp (xls.xtype, 'COM') || strcmp (xls.xtype, 'UNO')) && isempty (strfind ( lower (filename), '.xls')))
  91                                         # Excel/ActiveX && OOo (UNO bridge) will write any valid filetype; POI/JXL/OXS need .xls[x]
  92                                         error ('.xls or .xlsx extension lacking in filename %s', filename);
  93                                 else
  94                                         ### For multi-user environments, uncomment below AND relevant stanza in xlsopen
  95                                         # In case of COM, be sure to first close the open workbook
  96                                         #if (strcmp (xls.xtype, 'COM'))
  97                                         #        xls.app.Application.DisplayAlerts = 0;
  98                                         #        xls.workbook.close();
  99                                         #        xls.app.Application.DisplayAlerts = 0;
 100                                         #endif
 101                                         if (strcmp (xls.xtype, 'UNO'))
 102                                                 # If needed, turn filename into URL
 103                                                 if (~isempty (strmatch ("file:///", filename)) || ~isempty (strmatch ("http:///", filename))...
 104                                                         || ~isempty (strmatch ("ftp:///", filename)) || ~isempty (strmatch ("www:///", filename)))
 105                                                         # Seems in proper shape for OOo (at first sight)
 106                                                 else
 107                                                         # Transform into URL form
 108                                                         fname = canonicalize_file_name (strsplit (filename, filesep){end});
 109                                                         # On Windows, change backslash file separator into forward slash
 110                                                         if (strcmp (filesep, "\\"))
 111                                                                 tmp = strsplit (fname, filesep);
 112                                                                 flen = numel (tmp);
 113                                                                 tmp(2:2:2*flen) = tmp;
 114                                                                 tmp(1:2:2*flen) = '/';
 115                                                                 filename = [ 'file://' tmp{:} ];
 116                                                         endif
 117                                                 endif
 118                                         endif
 119           # Preprocessing / -checking ready. Assign filename arg to file ptr struct
 120                                         xls.filename = filename;
 121                                 endif
 122                         endif
 123                 endfor
 124         endif
 125
 126         if (strcmp (xls.xtype, 'COM'))
 127                 # If file has been changed, write it out to disk.
 128                 #
 129                 # Note: COM / VB supports other Excel file formats as FileFormatNum:
 130                 # 4 = .wks - Lotus 1-2-3 / Microsoft Works
 131                 # 6 = .csv
 132                 # -4158 = .txt
 133                 # 36 = .prn
 134                 # 50 = .xlsb - xlExcel12 (Excel Binary Workbook in 2007 with or without macro's)
 135                 # 51 = .xlsx - xlOpenXMLWorkbook (without macro's in 2007)
 136                 # 52 = .xlsm - xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007)
 137                 # 56 = .xls  - xlExcel8 (97-2003 format in Excel 2007)
 138                 # (see Excel Help, VB reference, Enumerations, xlFileType)
 139
 140                 # xls.changed = 0: no changes: just close;
 141                 #               1: existing file with changes: save, close.
 142                 #               2: new file with data added: save, close
 143                 #               3: new file, no added added (empty): close & delete on disk
 144
 145                 xls.app.Application.DisplayAlerts = 0;
 146                 try
 147                         if (xls.changed > 0 && xls.changed < 3)
 148                                 if (xls.changed == 2)
 149                                         # Probably a newly created, or renamed, Excel file
 150                                         printf ("Saving file %s ...\n", xls.filename);
 151                                         xls.workbook.SaveAs (canonicalize_file_name (xls.filename));
 152                                 elseif (xls.changed == 1)
 153                                         # Just updated existing Excel file
 154                                         xls.workbook.Save ();
 155                                 endif
 156                                 xls.changed = 0;
 157                                 xls.workbook.Close (canonicalize_file_name (xls.filename));
 158                         endif
 159                         xls.app.Quit ();
 160                         delete (xls.workbook);  # This statement actually closes the workbook
 161                         delete (xls.app);               # This statement actually closes down Excel
 162                 catch
 163                         xls.app.Application.DisplayAlerts = 1;
 164                 end_try_catch
 165
 166         elseif (strcmp (xls.xtype, 'POI'))
 167                 if (xls.changed > 0 && xls.changed < 3)
 168                         try
 169                                 xlsout = java_new ("java.io.FileOutputStream", xls.filename);
 170                                 bufout = java_new ("java.io.BufferedOutputStream", xlsout);
 171                                 if (xls.changed == 2) printf ("Saving file %s...\n", xls.filename); endif
 172                                 xls.workbook.write (bufout);
 173                                 bufout.flush ();
 174                                 bufout.close ();
 175                                 xlsout.close ();
 176                                 xls.changed = 0;
 177                         catch
 178 #                               xlsout.close ();
 179                         end_try_catch
 180                 endif
 181
 182         elseif (strcmp (xls.xtype, 'JXL'))
 183                 if (xls.changed > 0 && xls.changed < 3)
 184                         try
 185                                 if (xls.changed == 2) printf ("Saving file %s...\n", xls.filename); endif
 186                                 xls.workbook.write ();
 187                                 xls.workbook.close ();
 188                                 if (xls.changed == 3)
 189                                         # Upon entering write mode, JExcelAPI always makes a disk file
 190                                         # Incomplete new files (no data added) had better be deleted.
 191                                         xls.workbook.close ();
 192                                         delete (xls.filename);
 193                                 endif
 194                                 xls.changed = 0;
 195                         catch
 196                         end_try_catch
 197                 endif
 198
 199         elseif (strcmp (xls.xtype, 'OXS'))
 200                 if (xls.changed > 0 && xls.changed < 3)
 201                         try
 202                                 xlsout = java_new ("java.io.FileOutputStream", xls.filename);
 203                                 bufout = java_new ("java.io.BufferedOutputStream", xlsout);
 204                                 if (xls.changed == 2) printf ("Saving file %s...\n", xls.filename); endif
 205                                 xls.workbook.writeBytes (bufout);
 206                                 xls.workbook.close ();
 207                                 bufout.flush ();
 208                                 bufout.close ();
 209                                 xlsout.close ();
 210                                 xls.changed = 0;
 211                         catch
 212 #                               xlsout.close ();
 213                         end_try_catch
 214                 else
 215                         xls.workbook.close ();
 216                 endif
 217
 218         elseif (strcmp (xls.xtype, 'UNO'))
 219                 # Java & UNO bridge
 220                 try
 221                         if (xls.changed && xls.changed < 3)
 222                                 # Workaround:
 223                                 unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.frame.XModel');
 224                                 xModel = xls.workbook.queryInterface (unotmp);
 225                                 unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.util.XModifiable');
 226                                 xModified = xModel.queryInterface (unotmp);
 227                                 if (xModified.isModified ())
 228                                         unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.frame.XStorable');    # isReadonly() ?
 229                                         xStore = xls.app.xComp.queryInterface (unotmp);
 230                                         if (xls.changed == 2)
 231                                                 # Some trickery as Octave Java cannot create non-numeric arrays
 232                                                 lProps = javaArray ('com.sun.star.beans.PropertyValue', 1);
 233                                                 lProp = java_new ('com.sun.star.beans.PropertyValue', "Overwrite", 0, true, []);
 234                                                 lProps(1) = lProp;
 235                                                 # OK, store file
 236                                                 xStore.storeAsURL (xls.filename, lProps);
 237                                         else
 238                                                 xStore.store ();
 239                                         endif
 240                                 endif
 241                         endif
 242                         xls.changed = -1;               # Needed for check on properly shutting down OOo
 243                         # Workaround:
 244                         unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.frame.XModel');
 245                         xModel = xls.app.xComp.queryInterface (unotmp);
 246                         unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.util.XCloseable');
 247                         xClosbl = xModel.queryInterface (unotmp);
 248                         xClosbl.close (true);
 249                         unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.frame.XDesktop');
 250                         xDesk = xls.app.aLoader.queryInterface (unotmp);
 251                         xDesk.terminate();
 252                         xls.changed = 0;
 253                 catch
 254                         if (force)
 255                                 # Force closing OOo
 256                                 unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.frame.XDesktop');
 257                                 xDesk = xls.app.aLoader.queryInterface (unotmp);
 258                                 xDesk.terminate();
 259                         else
 260                                 warning ("Error closing xls pointer (UNO)");
 261                         endif
 262                         return
 263                 end_try_catch
 264
 265 #       elseif   <other interfaces here>
 266
 267         endif
 268
 269         if (xls.changed && xls.changed < 3)
 270                 warning (sprintf ("File %s could not be saved. Read-only or in use elsewhere?\nFile pointer preserved.", xls.filename));
 271                 if (force)
 272                         xls = [];
 273                 endif
 274         else
 275                 xls = [];
 276         endif
 277
 278 endfunction