Menu
▾
▴
[Octave-cvsupdate] SF.net SVN: octave:[7091] trunk/octave-forge/main/io/inst/getusedrange.m
|
From: <prn...@us...> - 2010-03-21 00:06:30
|
Revision: 7091
http://octave.svn.sourceforge.net/octave/?rev=7091&view=rev
Author: prnienhuis
Date: 2010-03-21 00:06:18 +0000 (Sun, 21 Mar 2010)
Log Message:
-----------
Made to work for all java-based spreadsheet I/O, save jOpenDocument (xls & ods). Ranges calculated with JExcelAPI may be somewhat inaccurate sometimes.
Modified Paths:
--------------
trunk/octave-forge/main/io/inst/getusedrange.m
Modified: trunk/octave-forge/main/io/inst/getusedrange.m
===================================================================
--- trunk/octave-forge/main/io/inst/getusedrange.m 2010-03-21 00:04:15 UTC (rev 7090)
+++ trunk/octave-forge/main/io/inst/getusedrange.m 2010-03-21 00:06:18 UTC (rev 7091)
@@ -14,19 +14,26 @@
## along with Octave; see the file COPYING. If not, see
## <http://www.gnu.org/licenses/>.
-## getusedrange - get used range from ODS spreadsheet
+## getusedrange - get used range from a spreadsheet
## Author: Philip Nienhuis <philip@JVC741>
-## Created: 2010-03-18 (First usable version)
+## Created: 2010-03-18 (First usable version) for ODS (java/OTK)
## Updates:
-##
+## 2010-03-20 Added Excel support (Java/POI)
-function [ trow, lrow, lcol, rcol ] = getusedrange (ods, ii)
+function [ trow, lrow, lcol, rcol ] = getusedrange (spptr, ii)
- if (strcmp (ods.xtype, 'OTK'))
- [ trow, lrow, lcol, rcol ] = getusedrange_otk (ods, ii);
+ if (strcmp (spptr.xtype, 'OTK'))
+ [ trow, lrow, lcol, rcol ] = getusedrange_otk (spptr, ii);
+ elseif (strcmp (spptr.xtype, 'JOD'))
+ error ('jOpenDocument not supported yet by getusedrange');
+# [ trow, lrow, lcol, rcol ] = getusedrange_jod (spptr, ii);
+ elseif (strcmp (spptr.xtype, 'POI'))
+ [ trow, lrow, lcol, rcol ] = getusedrange_poi (spptr, ii);
+ elseif (strcmp (spptr.xtype, 'JXL'))
+ [ trow, lrow, lcol, rcol ] = getusedrange_jxl (spptr, ii);
else
- error ('Only OTK interface implemented');
+ error ('Only OTK, JOD, POI and JXL interface implemented');
endif
endfunction
@@ -126,3 +133,113 @@
endif
endfunction
+
+## Copyright (C) 2010 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_poi - get range of occupied data cells from Excel using java/POI
+
+## Author: Philip <Philip@DESKPRN>
+## Created: 2010-03-20
+
+function [ trow, brow, lcol, rcol ] = getusedrange_poi (xls, ii)
+
+ persistent cblnk; cblnk = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_BLANK');
+
+ sh = xls.workbook.getSheetAt (ii-1); # Java POI starts counting at 0
+
+ trow = sh.getFirstRowNum ();
+ brow = sh.getLastRowNum ();
+ # Get column range
+ lcol = 1048577; # OOXML (xlsx) max. + 1
+ rcol = 0;
+ botrow = brow;
+ for jj=trow:brow
+ irow = sh.getRow (jj);
+ if (~isempty (irow))
+ scol = (irow.getFirstCellNum).intValue ();
+ lcol = min (lcol, scol);
+ ecol = (irow.getLastCellNum).intValue () - 1;
+ rcol = max (rcol, ecol);
+ # Keep track of lowermost non-empty row as getLastRowNum() is unreliable
+ if ~(irow.getCell(scol).getCellType () == cblnk && irow.getCell(ecol).getCellType () == cblnk)
+ botrow = jj;
+ endif
+ endif
+ endfor
+ if (lcol > 1048576)
+ # Empty sheet
+ trow = 0; brow = 0; lcol = 0; rcol = 0;
+ else
+ brow = min (brow, botrow) + 1; ++trow; ++lcol; ++rcol;
+ endif
+
+endfunction
+
+
+## Copyright (C) 2010 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_jxl - get occupied data cell range from Excel sheet
+## using java/JExcelAPI
+
+## Author: Philip <Philip@DESKPRN>
+## Created: 2010-03-20
+
+function [ trow, brow, lcol, rcol ] = getusedrange_jxl (xls, wsh)
+
+ persistent emptycell = (java_get ('jxl.CellType', 'EMPTY')).toString ();
+
+ sh = xls.workbook.getSheet (wsh - 1); # POI sheet count 0-based
+
+ brow = sh.getRows ();
+ rcol = sh.getColumns ();
+
+ if (brow == 0 || rcol == 0)
+ # Empty sheet
+ trow = 0; lcol = 0;
+ else
+ trow = brow + 1;
+ lcol = rcol + 1;
+ for ii=0:brow-1 # For loop coz we must check ALL rows for leftmost column
+ emptyrow = 1;
+ jj = 0;
+ while (jj < rcol && emptyrow) # While loop => only til first non-empty cell
+ cell = sh.getCell (jj, ii);
+ if ~(strcmp (char (cell.getType ()), emptycell))
+ lcol = min (lcol, jj + 1);
+ emptyrow = 0;
+ endif
+ ++jj;
+ endwhile
+ if ~(emptyrow) trow = min (trow, ii + 1); endif
+ endfor
+ endif
+
+endfunction
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|