Menu
▾
▴
[Octave-cvsupdate] SF.net SVN: octave:[8188] trunk/octave-forge/main/io/inst
|
From: <prn...@us...> - 2011-03-28 20:29:34
|
Revision: 8188
http://octave.svn.sourceforge.net/octave/?rev=8188&view=rev
Author: prnienhuis
Date: 2011-03-28 20:29:28 +0000 (Mon, 28 Mar 2011)
Log Message:
-----------
OpenXLS support added
Modified Paths:
--------------
trunk/octave-forge/main/io/inst/getusedrange.m
trunk/octave-forge/main/io/inst/xls2oct.m
trunk/octave-forge/main/io/inst/xlsclose.m
trunk/octave-forge/main/io/inst/xlsfinfo.m
Modified: trunk/octave-forge/main/io/inst/getusedrange.m
===================================================================
--- trunk/octave-forge/main/io/inst/getusedrange.m 2011-03-28 20:28:49 UTC (rev 8187)
+++ trunk/octave-forge/main/io/inst/getusedrange.m 2011-03-28 20:29:28 UTC (rev 8188)
@@ -1,4 +1,4 @@
-## Copyright (C) 2010 Philip Nienhuis, pr....@us...
+## Copyright (C) 2010,2011 Philip Nienhuis, pr....@us...
##
## 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
@@ -16,8 +16,9 @@
## -*- texinfo -*-
## @deftypefn {Function File} [ @var{toprow#}, @var{bottomrow#}, @var{leftcol#}, @var{rightcol#} ] = getusedrange (@var{spptr}, @var{shindex#})
-## Find occupied data range in a specific worksheet in a spreadsheet
-## (either MS-Excel or OOo Calc).
+## Find occupied data range in worksheet @var{shindex#} in a spreadsheet
+## pointed to in struct @var{spptr} (either MS-Excel or
+## OpenOffice Calc).
##
## @var{shindex#} must be numeric and is 1-based. @var{spptr} can either
## refer to an MS-Excel spreadsheet (spptr returned by xlsopen) or an
@@ -60,9 +61,9 @@
## 2010-08-27 Added checks for input arguments
## " Indentation changed from tab to doublespace
## 2010-10-07 Added COM support (at last!)
-## 2010-03-04 Textual adaptations in header
+## 2011-03-26 Added OpenXLS support
##
-## Last subfunc update: 2010-12-01 (COM)
+## Last subfunc update: 2011-03-26 (OXS)
function [ trow, lrow, lcol, rcol ] = getusedrange (spptr, ii)
@@ -79,6 +80,8 @@
[ trow, lrow, lcol, rcol ] = getusedrange_poi (spptr, ii);
elseif (strcmp (spptr.xtype, 'JXL'))
[ trow, lrow, lcol, rcol ] = getusedrange_jxl (spptr, ii);
+ elseif (strcmp (spptr.xtype, 'OXS'))
+ [ trow, lrow, lcol, rcol ] = getusedrange_oxs (spptr, ii);
else
error ('Only OTK, JOD, POI and JXL interface implemented');
endif
@@ -114,12 +117,16 @@
## 2010-11-13 Catched jOpenDocument bug (1.2bx) where string cells have no office:value-type
## attrib set (by JOD). Somehow OTK is more robust as it catches these cells;
## Currently this fix is just commented.
+## 2011-03-23 Adapted to odfdom 0.8.7 (getXPath method call changed)
function [ trow, lrow, lcol, rcol ] = getusedrange_otk (ods, ii)
odfcont = ods.workbook; # Local copy just in case
-
- xpath = ods.app.getXPath;
+ if (strcmp (ods.odfvsn, '0.8.7'))
+ xpath = ods.workbook.getXPath;
+ else
+ xpath = ods.app.getXPath;
+ endif
# Create an instance of type NODESET for use in subsequent statement
NODESET = java_get ('javax.xml.xpath.XPathConstants', 'NODESET');
# Get table-rows in sheet no. wsh. Sheet count = 1-based (!)
@@ -483,3 +490,41 @@
endif
endfunction
+
+
+## Copyright (C) 2011 Philip Nienhuis <prnienhuis 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 2 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 Octave; see the file COPYING. If not, see
+## <http://www.gnu.org/licenses/>.
+
+## getusedrange_oxs - get occupied data cell range from Excel sheet
+## using java/OpenXLS
+
+## Author: Philip Nienhuis
+## Created: 2011-03-26
+
+function [ trow, brow, lcol, rcol ] = getusedrange_oxs (xls, wsh)
+
+ sh = xls.workbook.getWorkSheet (wsh - 1); # OXS sheet count 0-based
+ if (sh.getNumRows)
+ trow = sh.getFirstRow () + 1;
+ brow = sh.getLastRow ();
+ lcol = sh.getFirstCol () + 1;
+ rcol = sh.getLastCol ();
+ else
+ # Might be a chart sheet
+ trow = brow = lcol = rcol = 0;
+ endif
+
+endfunction
Modified: trunk/octave-forge/main/io/inst/xls2oct.m
===================================================================
--- trunk/octave-forge/main/io/inst/xls2oct.m 2011-03-28 20:28:49 UTC (rev 8187)
+++ trunk/octave-forge/main/io/inst/xls2oct.m 2011-03-28 20:29:28 UTC (rev 8188)
@@ -1,4 +1,4 @@
-## Copyright (C) 2009,2010 Philip Nienhuis <prnienhuis at users.sf.net>
+## Copyright (C) 2009,2010,2011 Philip Nienhuis <prnienhuis 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
@@ -114,8 +114,9 @@
## 2010-11-07 More rigorous input checks.
## 2010-11-12 Moved pointer check into main func
## 2010-11-13 Catch empty sheets when no range was specified
+## 2011-03-26 OpenXLS support added
##
-## Latest subfunc update: 2010-11-14 (poi)
+## Latest subfunc update: 2011-03-26 (OXS)
function [ rawarr, xls, rstatus ] = xls2oct (xls, wsh=1, datrange='', spsh_opts=[])
@@ -157,6 +158,9 @@
elseif (strcmp (xls.xtype, 'JXL'))
# Read xls file tru JExcelAPI
[rawarr, xls, rstatus] = xls2jxla2oct (xls, wsh, datrange, spsh_opts);
+ elseif (strcmp (xls.xtype, 'OXS'))
+ # Read xls file tru OpenXLS
+ [rawarr, xls, rstatus] = xls2oxs2oct (xls, wsh, datrange, spsh_opts);
# elseif ---- <Other interfaces here>
# Call to next interface
else
@@ -724,3 +728,112 @@
xls.limits = [lcol, rcol; firstrow, lastrow];
endfunction
+
+
+## Copyright (C) 2011 Philip Nienhuis <prnienhuis 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 2 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 Octave; see the file COPYING. If not, see
+## <http://www.gnu.org/licenses/>.
+
+## -*- texinfo -*-
+## @deftypefn {Function File} [@var{obj}, @var{rstatus}, @var{xls} ] = xls2oxs2oct (@var{xls})
+## @deftypefnx {Function File} [@var{obj}, @var{rstatus}, @var{xls} ] = xls2oxs2oct (@var{xls}, @var{wsh})
+## @deftypefnx {Function File} [@var{obj}, @var{rstatus}, @var{xls} ] = xls2oxs2oct (@var{xls}, @var{wsh}, @var{range})
+## Get cell contents in @var{range} in worksheet @var{wsh} in an Excel
+## file pointed to in struct @var{xls} into the cell array @var{obj}.
+## @var{range} can be a range or just the top left cell of the range.
+##
+## xls2oxs2oct should not be invoked directly but rather through xls2oct.
+##
+
+## Author: Philip Nienhuis
+## Created: 2011-03-26
+## Updates:
+
+function [ rawarr, xls, status ] = xls2oxs2oct (xls, wsh, cellrange=[], spsh_opts)
+
+ persistent ctype;
+ if (isempty (ctype))
+ ctype = cell (11, 1);
+ # Get enumerated cell types. Beware as they start at 0 not 1
+ ctype( 1) = (java_get ('com.extentech.ExtenXLS.CellHandle', 'TYPE_STRING'));
+ ctype( 2) = (java_get ('com.extentech.ExtenXLS.CellHandle', 'TYPE_FP'));
+ ctype( 3) = (java_get ('com.extentech.ExtenXLS.CellHandle', 'TYPE_INT'));
+ ctype( 4) = (java_get ('com.extentech.ExtenXLS.CellHandle', 'TYPE_FORMULA'));
+ ctype( 5) = (java_get ('com.extentech.ExtenXLS.CellHandle', 'TYPE_BOOLEAN'));
+ ctype( 6) = (java_get ('com.extentech.ExtenXLS.CellHandle', 'TYPE_DOUBLE'));
+ endif
+
+ status = 0;
+ wb = xls.workbook;
+
+ # Check if requested worksheet exists in the file & if so, get pointer
+ nr_of_sheets = wb.getNumWorkSheets ();
+ if (isnumeric (wsh))
+ if (wsh > nr_of_sheets), error (sprintf ("Worksheet # %d bigger than nr. of sheets (%d) in file %s", wsh, nr_of_sheets, xls.filename)); endif
+ sh = wb.getWorkSheet (wsh - 1); # OXS sheet count 0-based
+ printf ("(Reading from worksheet %s)\n", sh.getSheetName ());
+ else
+ try
+ sh = wb.getWorkSheet (wsh);
+ catch
+ error (sprintf ("Worksheet %s not found in file %s", wsh, xls.filename));
+ end_try_catch
+ end
+
+ if (isempty (cellrange))
+ # Get numeric sheet pointer (0-based)
+ wsh = sh.getTabIndex ();
+ # Get data rectangle row & column numbers (1-based)
+ [firstrow, lastrow, lcol, rcol] = getusedrange (xls, wsh+1);
+ if (firstrow == 0 && lastrow == 0)
+ # Empty sheet
+ rawarr = {};
+ printf ("Worksheet '%s' contains no data\n", shnames {wsh});
+ rstatus = 1;
+ return;
+ else
+ nrows = lastrow - firstrow + 1;
+ ncols = rcol - lcol + 1;
+ endif
+ else
+ # Translate range to row & column numbers (1-based)
+ [dummy, nrows, ncols, firstrow, lcol] = parse_sp_range (cellrange);
+ # Check for too large requested range against actually present range
+ lastrow = min (firstrow + nrows - 1, sh.getLastRow + 1 ());
+ nrows = min (nrows, sh.getLastRow () - firstrow + 1);
+ ncols = min (ncols, sh.getLastCol () - lcol + 1);
+ rcol = lcol + ncols - 1;
+ endif
+
+ # Read contents into rawarr
+ rawarr = cell (nrows, ncols); # create placeholder
+ for jj = lcol:rcol
+ for ii = firstrow:lastrow
+ try
+ scell = sh.getCell (ii-1, jj-1);
+ sctype = scell.getCellType ();
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getVal ();
+ if (sctype == ctype(2) || sctype == ctype(3) || sctype == ctype(6))
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getDoubleVal ();
+ endif
+ catch
+ end_try_catch
+ endfor
+ endfor
+
+ status = 1;
+ xls.limits = [lcol, rcol; firstrow, lastrow];
+
+endfunction
Modified: trunk/octave-forge/main/io/inst/xlsclose.m
===================================================================
--- trunk/octave-forge/main/io/inst/xlsclose.m 2011-03-28 20:28:49 UTC (rev 8187)
+++ trunk/octave-forge/main/io/inst/xlsclose.m 2011-03-28 20:29:28 UTC (rev 8188)
@@ -1,4 +1,4 @@
-## Copyright (C) 2009,2010 Philip Nienhuis <prnienhuis at users.sf.net>
+## Copyright (C) 2009,2010,2011 Philip Nienhuis <prnienhuis 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
@@ -9,7 +9,7 @@
## 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 Octave; see the file COPYING. If not, see
## <http://www.gnu.org/licenses/>.
@@ -35,7 +35,7 @@
## with JXL (JExcelAPI) interface.
##
## You need MS-Excel (95 - 2010), and/or the Java package > 1.2.6 plus Apache
-## POI > 3.5 and/or JExcelAPI installed on your computer + proper
+## POI > 3.5 and/or JExcelAPI and/or OpenXLS installed on your computer + proper
## javaclasspath set, to make this function work at all.
##
## @var{xls} must be a valid pointer struct made by xlsopen() in the same
@@ -65,6 +65,7 @@
## " untouched file ptr struct in case of errors rather than wipe it
## 2010-11-12 Replaced 'keepxls' by new filename arg; catch write errors and
## always keep file pointer in case of write errors
+## 2011-03-26 Added OpenXLS support
function [ xls ] = xlsclose (xls, filename=[])
@@ -136,8 +137,11 @@
if (xls.changed > 0 && xls.changed < 3)
try
xlsout = java_new ("java.io.FileOutputStream", xls.filename);
+ bufout = java_new ("java.io.BufferedOutputStream", xlsout);
if (xls.changed == 2) printf ("Saving file %s...\n", xls.filename); endif
- xls.workbook.write (xlsout);
+ xls.workbook.write (bufout);
+ bufout.flush ();
+ bufout.close ();
xlsout.close ();
xls.changed = 0;
catch
@@ -162,6 +166,25 @@
end_try_catch
endif
+ elseif (strcmp (xls.xtype, 'OXS'))
+ if (xls.changed > 0 && xls.changed < 3)
+ try
+ xlsout = java_new ("java.io.FileOutputStream", xls.filename);
+ bufout = java_new ("java.io.BufferedOutputStream", xlsout);
+ if (xls.changed == 2) printf ("Saving file %s...\n", xls.filename); endif
+ xls.workbook.writeBytes (bufout);
+ xls.workbook.close ();
+ bufout.flush ();
+ bufout.close ();
+ xlsout.close ();
+ xls.changed = 0;
+ catch
+# xlsout.close ();
+ end_try_catch
+ else
+ xls.workbook.close ();
+ endif
+
# elseif <other interfaces here>
endif
Modified: trunk/octave-forge/main/io/inst/xlsfinfo.m
===================================================================
--- trunk/octave-forge/main/io/inst/xlsfinfo.m 2011-03-28 20:28:49 UTC (rev 8187)
+++ trunk/octave-forge/main/io/inst/xlsfinfo.m 2011-03-28 20:29:28 UTC (rev 8188)
@@ -1,4 +1,4 @@
-## Copyright (C) 2009,2010 Philip Nienhuis <pr.nienhuis at users.sf.net>
+## Copyright (C) 2009,2010,2011 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
@@ -80,6 +80,7 @@
## " Added occupieded 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
function [ filetype, sh_names, fformat ] = xlsfinfo (filename, reqintf=[])
@@ -166,6 +167,22 @@
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 <Other Excel interfaces below>
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
