1 ## Copyright (C) 2009,2010,2011,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/>.
17 ## @deftypefn {Function File} [@var{filetype}] = xlsfinfo (@var{filename} [, @var{reqintf}])
18 ## @deftypefnx {Function File} [@var{filetype}, @var{sh_names}] = xlsfinfo (@var{filename} [, @var{reqintf}])
19 ## @deftypefnx {Function File} [@var{filetype}, @var{sh_names}, @var{fformat}] = xlsfinfo (@var{filename} [, @var{reqintf}])
20 ## Query Excel spreadsheet file @var{filename} for some info about its
23 ## If @var{filename} is a recognizable Excel spreadsheet file,
24 ## @var{filetype} returns the string "Microsoft Excel Spreadsheet", or
25 ## @'' (empty string) otherwise.
27 ## If @var{filename} is a recognizable Excel spreadsheet file, optional
28 ## argument @var{sh_names} contains a list (cell array) of sheet
29 ## names (and in case Excel is installed: sheet types) contained in
30 ## @var{filename}, in the order (from left to right) in which they occur
31 ## in the sheet stack.
33 ## Optional return value @var{fformat} currently returns @'' (empty
34 ## string) unless @var{filename} is a readable Excel 97-2003 .xls file or
35 ## an Excel 2007 .xlsx / .xlsb file in which case @var{fformat} is set to
36 ## "xlWorkbookNormal". Excel 95 .xls files can only be read through the JXL
37 ## (JExcelAPI) or UNO (OpenOffice.org) Java-based interfaces.
39 ## If no return arguments are specified the sheet names are echoed to the
40 ## terminal screen; in case of Java interfaces for each sheet the actual
41 ## occupied data range is echoed as well. The occupied cell range will have
42 ## to be determined behind the scenes first; this can take some time for the
43 ## Java-based interfaces.
45 ## If multiple xls interfaces have been installed, @var{reqintf} can be
46 ## specified. This can sometimes be handy, e.g. to get an idea of occupied
47 ## cell ranges in each worksheet using different interfaces (due to cached
48 ## info and/or different treatment of empty but formatted cells, each
49 ## interfaces may give different results).
51 ## For use on OOXML spreadsheets one needs full POI and/or UNO support (see
52 ## xlsopen) and 'poi' or 'uno' needs to be specified for @var{reqintf}. For
53 ## Excel 95 file use 'jxl' or 'uno'.
58 ## exist = xlsfinfo ('test4.xls');
59 ## (Just checks if file test4.xls is a readable Excel file)
63 ## [exist, names] = xlsfinfo ('test4.xls');
64 ## (Checks if file test4.xls is a readable Excel file and return a
65 ## list of sheet names and -types)
68 ## @seealso {oct2xls, xlsread, xls2oct, xlswrite}
72 ## Author: Philip Nienhuis <prnienhuis@users.sourceforge.net>
73 ## Created: 2009-10-27
75 ## 2009-01-01 Echo sheet names to screen, request interface type)
76 ## 2010-03-21 Better tabulated output; occupied date range per sheet echoed
77 ## for Java interfaces (though it may be a bit off in case of JXL)
78 ## 2010-05-31 Added remark about delays when determining occupied data range
79 ## 2010-08-25 Improved help text (Excel file types)
80 ## 2010-10-06 Added ";" to str2 declaration
81 ## '' Added occupied range echo for COM interface (may be a bit off too)
82 ## 2010-10-10 Made output arg2 contain only address ranges (or other sheet type names)
83 ## 2010-11-01 Added other file type strings for return arg #3 (fformat)
84 ## 2011-03-26 Added OpenXLS support
85 ## 2011-05-18 Experimental UNO support
86 ## 2011-09-08 Some code simplifications
87 ## 2012-01-26 Fixed "seealso" help string
88 ## 2012-02-25 Added info on occupied ranges to sh_names outarg for all interfaces
90 function [ filetype, sh_names, fformat ] = xlsfinfo (filename, reqintf=[])
92 persistent str2; str2 = ' '; # 33 spaces
93 persistent lstr2; lstr2 = length (str2);
95 xls = xlsopen (filename, 0, reqintf);
96 if (isempty (xls)); return; endif
98 toscreen = nargout < 1;
100 xlWorksheet = -4167; xlChart = 4;
101 # If any valid xls-pointer struct has been returned, it must be a valid Excel spreadsheet
102 filetype = 'Microsoft Excel Spreadsheet'; fformat = '';
104 if (strcmp (xls.xtype, 'COM'))
105 # See if desired worksheet number or name exists
106 sh_cnt = xls.workbook.Sheets.count;
107 sh_names = cell (sh_cnt, 2);
108 ws_cnt = 0; ch_cnt = 0; o_cnt = 0;
110 sh_names(ii, 1) = xls.workbook.Sheets(ii).Name;
111 if (xls.workbook.Sheets(ii).Type == xlWorksheet)
112 [tr, lr, lc, rc] = getusedrange (xls, ++ws_cnt);
114 sh_names(ii, 2) = sprintf ("%s:%s", calccelladdress (tr, lc), calccelladdress (lr, rc));
116 sh_names(ii, 2) = "Empty";
118 elseif (xls.workbook.Sheets(ii).Type == xlChart)
119 sh_names(ii, 2) = sprintf ("Chart"); ++ch_cnt;
121 sh_names(ii, 2) = 'Other_type'; ++o_cnt;
124 if (ws_cnt > 0 || ch_cnt > 0)
125 if (strcmpi (xls.filename(end-2:end), 'xls'))
126 fformat = "xlWorkbookNormal";
127 elseif (strcmpi (xls.filename(end-2:end), 'csv'))
128 fformat = "xlCSV"; # Works only with COM
129 elseif (strcmpi (xls.filename(end-3:end-1), 'xls'))
130 fformat = "xlOpenXMLWorkbook";
131 elseif (strmatch ('htm', lower (xls.filename(end-3:end))))
132 fformat = "xlHtml"; # Works only with COM
138 elseif (strcmp (xls.xtype, 'POI'))
139 persistent cblnk; cblnk = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_BLANK');
140 sh_cnt = xls.workbook.getNumberOfSheets();
141 sh_names = cell (sh_cnt, 2); nsrows = zeros (sh_cnt, 1);
143 sh = xls.workbook.getSheetAt (ii-1); # Java POI starts counting at 0
144 sh_names(ii, 1) = char (sh.getSheetName());
145 # Java POI doesn't distinguish between worksheets and graph sheets
146 [tr, lr, lc, rc] = getusedrange (xls, ii);
148 sh_names(ii, 2) = sprintf ("%s:%s", calccelladdress (tr, lc), calccelladdress (lr, rc));
150 sh_names(ii, 2) = "Empty";
154 if (strcmpi (xls.filename(end-2:end), 'xls'))
155 fformat = "xlWorkbookNormal";
156 elseif (strcmpi (xls.filename(end-3:end-1), 'xls'))
157 fformat = "xlOpenXMLWorkbook";
163 elseif (strcmp (xls.xtype, 'JXL'))
164 sh_cnt = xls.workbook.getNumberOfSheets ();
165 sh_names = cell (sh_cnt, 2); nsrows = zeros (sh_cnt, 1);
166 sh_names(:,1) = char (xls.workbook.getSheetNames ());
168 [tr, lr, lc, rc] = getusedrange (xls, ii);
170 sh_names(ii, 2) = sprintf ("%s:%s", calccelladdress (tr, lc), calccelladdress (lr, rc));
172 sh_names(ii, 2) = "Empty";
175 if (sh_cnt > 0) fformat = "xlWorkbookNormal"; else, fformat = ''; endif
177 elseif (strcmp (xls.xtype, 'OXS'))
178 sh_cnt = xls.workbook.getNumWorkSheets ();
179 sh_names = cell (sh_cnt, 2); nsrows = zeros (sh_cnt, 1);
181 sh = xls.workbook.getWorkSheet (ii-1); # OpenXLS starts counting at 0
182 sh_names(ii, 1) = char (sh.getSheetName());
183 # OpenXLS doesn't distinguish between worksheets and graph sheets
184 [tr, lr, lc, rc] = getusedrange (xls, ii);
186 sh_names(ii, 2) = sprintf ("%s:%s", calccelladdress (tr, lc), calccelladdress (lr, rc));
188 sh_names(ii, 2) = "Empty or Chart";
191 if (sh_cnt > 0); fformat = "xlWorkbookNormal"; else; fformat = ''; endif
193 elseif (strcmp (xls.xtype, 'UNO'))
194 sheets = xls.workbook.getSheets ();
195 sheetnames = sheets.getElementNames (); # A Java object, NOT a cell array
196 sh_cnt = numel (sheetnames);
197 sh_names = cell (sh_cnt, 2);
199 sh_names(ii, 1) = sheetnames(ii);
200 [ tr, lr, lc, rc ] = getusedrange (xls, ii);
202 sh_names(ii, 2) = sprintf ("%s:%s", calccelladdress (tr, lc), calccelladdress (lr, rc));
204 sh_names(ii, 2) = "Empty or Chart";
207 if (sh_cnt > 0); fformat = "xlWorkbookNormal"; else; fformat = ''; endif
209 # elseif <Other Excel interfaces below>
212 error (sprintf ("xlsfinfo: unknown Excel .xls interface - %s.", xls.xtype));
217 # Echo sheet names to screen
219 str1 = sprintf ("%3d: %s", ii, sh_names{ii, 1});
220 if (index (sh_names{ii, 2}, ":"))
221 str3 = ['(Used range ~ ' sh_names{ii, 2} ')'];
223 str3 = sh_names{ii, 2};
225 printf ("%s%s%s\n", str1, str2(1:lstr2-length (sh_names{ii, 1})), str3);