--- /dev/null
+## Copyright (C) 2009,2010,2011,2012 Philip Nienhuis <pr.nienhuis at users.sf.net>
+##
+## 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} [@var{filetype}] = xlsfinfo (@var{filename} [, @var{reqintf}])
+## @deftypefnx {Function File} [@var{filetype}, @var{sh_names}] = xlsfinfo (@var{filename} [, @var{reqintf}])
+## @deftypefnx {Function File} [@var{filetype}, @var{sh_names}, @var{fformat}] = xlsfinfo (@var{filename} [, @var{reqintf}])
+## Query Excel spreadsheet file @var{filename} for some info about its
+## contents.
+##
+## If @var{filename} is a recognizable Excel spreadsheet file,
+## @var{filetype} returns the string "Microsoft Excel Spreadsheet", or
+## @'' (empty string) otherwise.
+##
+## If @var{filename} is a recognizable Excel spreadsheet file, optional
+## argument @var{sh_names} contains a list (cell array) of sheet
+## names (and in case Excel is installed: sheet types) contained in
+## @var{filename}, in the order (from left to right) in which they occur
+## in the sheet stack.
+##
+## Optional return value @var{fformat} currently returns @'' (empty
+## string) unless @var{filename} is a readable Excel 97-2003 .xls file or
+## an Excel 2007 .xlsx / .xlsb file in which case @var{fformat} is set to
+## "xlWorkbookNormal". Excel 95 .xls files can only be read through the JXL
+## (JExcelAPI) or UNO (OpenOffice.org) Java-based interfaces.
+##
+## If no return arguments are specified the sheet names are echoed to the
+## terminal screen; in case of Java interfaces for each sheet the actual
+## occupied data range is echoed as well. The occupied cell range will have
+## to be determined behind the scenes first; this can take some time for the
+## Java-based interfaces.
+##
+## If multiple xls interfaces have been installed, @var{reqintf} can be
+## specified. This can sometimes be handy, e.g. to get an idea of occupied
+## cell ranges in each worksheet using different interfaces (due to cached
+## info and/or different treatment of empty but formatted cells, each
+## interfaces may give different results).
+##
+## For use on OOXML spreadsheets one needs full POI and/or UNO support (see
+## xlsopen) and 'poi' or 'uno' needs to be specified for @var{reqintf}. For
+## Excel 95 file use 'jxl' or 'uno'.
+##
+## Examples:
+##
+## @example
+## exist = xlsfinfo ('test4.xls');
+## (Just checks if file test4.xls is a readable Excel file)
+## @end example
+##
+## @example
+## [exist, names] = xlsfinfo ('test4.xls');
+## (Checks if file test4.xls is a readable Excel file and return a
+## list of sheet names and -types)
+## @end example
+##
+## @seealso {oct2xls, xlsread, xls2oct, xlswrite}
+##
+## @end deftypefn
+
+## Author: Philip Nienhuis <prnienhuis@users.sourceforge.net>
+## Created: 2009-10-27
+## Updates:
+## 2009-01-01 Echo sheet names to screen, request interface type)
+## 2010-03-21 Better tabulated output; occupied date range per sheet echoed
+## for Java interfaces (though it may be a bit off in case of JXL)
+## 2010-05-31 Added remark about delays when determining occupied data range
+## 2010-08-25 Improved help text (Excel file types)
+## 2010-10-06 Added ";" to str2 declaration
+## '' Added occupied range echo for COM interface (may be a bit off too)
+## 2010-10-10 Made output arg2 contain only address ranges (or other sheet type names)
+## 2010-11-01 Added other file type strings for return arg #3 (fformat)
+## 2011-03-26 Added OpenXLS support
+## 2011-05-18 Experimental UNO support
+## 2011-09-08 Some code simplifications
+## 2012-01-26 Fixed "seealso" help string
+## 2012-02-25 Added info on occupied ranges to sh_names outarg for all interfaces
+
+function [ filetype, sh_names, fformat ] = xlsfinfo (filename, reqintf=[])
+
+ persistent str2; str2 = ' '; # 33 spaces
+ persistent lstr2; lstr2 = length (str2);
+
+ xls = xlsopen (filename, 0, reqintf);
+ if (isempty (xls)); return; endif
+
+ toscreen = nargout < 1;
+
+ xlWorksheet = -4167; xlChart = 4;
+ # If any valid xls-pointer struct has been returned, it must be a valid Excel spreadsheet
+ filetype = 'Microsoft Excel Spreadsheet'; fformat = '';
+
+ if (strcmp (xls.xtype, 'COM'))
+ # See if desired worksheet number or name exists
+ sh_cnt = xls.workbook.Sheets.count;
+ sh_names = cell (sh_cnt, 2);
+ ws_cnt = 0; ch_cnt = 0; o_cnt = 0;
+ for ii=1:sh_cnt
+ sh_names(ii, 1) = xls.workbook.Sheets(ii).Name;
+ if (xls.workbook.Sheets(ii).Type == xlWorksheet)
+ [tr, lr, lc, rc] = getusedrange (xls, ++ws_cnt);
+ if (tr)
+ sh_names(ii, 2) = sprintf ("%s:%s", calccelladdress (tr, lc), calccelladdress (lr, rc));
+ else
+ sh_names(ii, 2) = "Empty";
+ endif
+ elseif (xls.workbook.Sheets(ii).Type == xlChart)
+ sh_names(ii, 2) = sprintf ("Chart"); ++ch_cnt;
+ else
+ sh_names(ii, 2) = 'Other_type'; ++o_cnt;
+ endif
+ endfor
+ if (ws_cnt > 0 || ch_cnt > 0)
+ if (strcmpi (xls.filename(end-2:end), 'xls'))
+ fformat = "xlWorkbookNormal";
+ elseif (strcmpi (xls.filename(end-2:end), 'csv'))
+ fformat = "xlCSV"; # Works only with COM
+ elseif (strcmpi (xls.filename(end-3:end-1), 'xls'))
+ fformat = "xlOpenXMLWorkbook";
+ elseif (strmatch ('htm', lower (xls.filename(end-3:end))))
+ fformat = "xlHtml"; # Works only with COM
+ else
+ fformat = '';
+ endif
+ endif
+
+ elseif (strcmp (xls.xtype, 'POI'))
+ persistent cblnk; cblnk = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_BLANK');
+ sh_cnt = xls.workbook.getNumberOfSheets();
+ sh_names = cell (sh_cnt, 2); nsrows = zeros (sh_cnt, 1);
+ for ii=1:sh_cnt
+ sh = xls.workbook.getSheetAt (ii-1); # Java POI starts counting at 0
+ sh_names(ii, 1) = char (sh.getSheetName());
+ # Java POI doesn't distinguish between worksheets and graph sheets
+ [tr, lr, lc, rc] = getusedrange (xls, ii);
+ if (tr)
+ sh_names(ii, 2) = sprintf ("%s:%s", calccelladdress (tr, lc), calccelladdress (lr, rc));
+ else
+ sh_names(ii, 2) = "Empty";
+ endif
+ endfor
+ if (sh_cnt > 0)
+ if (strcmpi (xls.filename(end-2:end), 'xls'))
+ fformat = "xlWorkbookNormal";
+ elseif (strcmpi (xls.filename(end-3:end-1), 'xls'))
+ fformat = "xlOpenXMLWorkbook";
+ else
+ fformat = '';
+ endif
+ endif
+
+ elseif (strcmp (xls.xtype, 'JXL'))
+ sh_cnt = xls.workbook.getNumberOfSheets ();
+ sh_names = cell (sh_cnt, 2); nsrows = zeros (sh_cnt, 1);
+ sh_names(:,1) = char (xls.workbook.getSheetNames ());
+ for ii=1:sh_cnt
+ [tr, lr, lc, rc] = getusedrange (xls, ii);
+ if (tr)
+ sh_names(ii, 2) = sprintf ("%s:%s", calccelladdress (tr, lc), calccelladdress (lr, rc));
+ else
+ sh_names(ii, 2) = "Empty";
+ endif
+ endfor
+ if (sh_cnt > 0) fformat = "xlWorkbookNormal"; else, fformat = ''; endif
+
+ elseif (strcmp (xls.xtype, 'OXS'))
+ sh_cnt = xls.workbook.getNumWorkSheets ();
+ sh_names = cell (sh_cnt, 2); nsrows = zeros (sh_cnt, 1);
+ for ii=1:sh_cnt
+ sh = xls.workbook.getWorkSheet (ii-1); # OpenXLS starts counting at 0
+ sh_names(ii, 1) = char (sh.getSheetName());
+ # OpenXLS doesn't distinguish between worksheets and graph sheets
+ [tr, lr, lc, rc] = getusedrange (xls, ii);
+ if (tr)
+ sh_names(ii, 2) = sprintf ("%s:%s", calccelladdress (tr, lc), calccelladdress (lr, rc));
+ else
+ sh_names(ii, 2) = "Empty or Chart";
+ endif
+ endfor
+ if (sh_cnt > 0); fformat = "xlWorkbookNormal"; else; fformat = ''; endif
+
+ elseif (strcmp (xls.xtype, 'UNO'))
+ sheets = xls.workbook.getSheets ();
+ sheetnames = sheets.getElementNames (); # A Java object, NOT a cell array
+ sh_cnt = numel (sheetnames);
+ sh_names = cell (sh_cnt, 2);
+ for ii=1:sh_cnt
+ sh_names(ii, 1) = sheetnames(ii);
+ [ tr, lr, lc, rc ] = getusedrange (xls, ii);
+ if (tr)
+ sh_names(ii, 2) = sprintf ("%s:%s", calccelladdress (tr, lc), calccelladdress (lr, rc));
+ else
+ sh_names(ii, 2) = "Empty or Chart";
+ endif
+ endfor
+ if (sh_cnt > 0); fformat = "xlWorkbookNormal"; else; fformat = ''; endif
+
+# elseif <Other Excel interfaces below>
+
+ else
+ error (sprintf ("xlsfinfo: unknown Excel .xls interface - %s.", xls.xtype));
+
+ endif
+
+ if (toscreen)
+ # Echo sheet names to screen
+ for ii=1:sh_cnt
+ str1 = sprintf ("%3d: %s", ii, sh_names{ii, 1});
+ if (index (sh_names{ii, 2}, ":"))
+ str3 = ['(Used range ~ ' sh_names{ii, 2} ')'];
+ else
+ str3 = sh_names{ii, 2};
+ endif
+ printf ("%s%s%s\n", str1, str2(1:lstr2-length (sh_names{ii, 1})), str3);
+ endfor
+ endif
+
+ xlsclose (xls);
+
+endfunction
git://git.creatis.insa-lyon.fr / CreaPhase.git / blobdiff