[Octave-cvsupdate] SF.net SVN: octave:[6697] trunk/octave-forge/main/io/inst

[<prn...@us...>]

Revision: 6697
          http://octave.svn.sourceforge.net/octave/?rev=6697&view=rev
Author:   prnienhuis
Date:     2010-01-03 22:30:17 +0000 (Sun, 03 Jan 2010)

Log Message:
-----------
Fixed OOXML support. Updated calccelladdress.m to deal with OOXML's somewhat excessive column capacity (16384). parse_sp_range.m needs a related update too (will come next days).
Because of Java bugs I gave up on properly adding boolean values to .xls files in oct2xls when using POI; now 1 or 0 are written rather than TRUE or FALSE, resp.
Also all help texts have been updated.

Modified Paths:
--------------
    trunk/octave-forge/main/io/inst/calccelladdress.m
    trunk/octave-forge/main/io/inst/oct2xls.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
    trunk/octave-forge/main/io/inst/xlsopen.m
    trunk/octave-forge/main/io/inst/xlsread.m
    trunk/octave-forge/main/io/inst/xlswrite.m

Modified: trunk/octave-forge/main/io/inst/calccelladdress.m
===================================================================
--- trunk/octave-forge/main/io/inst/calccelladdress.m	2010-01-03 22:21:52 UTC (rev 6696)
+++ trunk/octave-forge/main/io/inst/calccelladdress.m	2010-01-03 22:30:17 UTC (rev 6697)
@@ -17,7 +17,8 @@
 ## calccelladdress - compute spreadsheet style cell address from
 ## row & column index.
 ## 
-## Max column index currently set to 1378 (ODS has max 1024)
+## Max column index currently set to 18278 (max ODS: 1024, OOXML: 16384).
+## Row limits for ODF and OOXML are 65536 and 1048576, resp.
 
 ## Author: Philip Nienhuis <prnienhuis at users.sf.net>
 ## Created: 2009-12-12
@@ -27,15 +28,19 @@
 
 	if (nargin < 4) error ("calccelladdress: not enough arguments.") endif
 	colnr = lcol + column - 1;
-	if (colnr> 1378) error ("Column nr > 1378"); endif
-	str = char (rem ((colnr-1), 26) + 'A');
-	if (colnr > 26 && colnr < 703) 
-		tmp = char (floor ((colnr - 27) / 26) + 'A');
+	if (colnr > 18278) error ("Column nr > 18278"); endif
+	rem1 = rem ((colnr-1), 26);
+	str = char (rem1 + 'A');								# A-Z; rightmost digit
+	if (colnr > 26 && colnr < 703)							# AA-ZZ
+		tmp = char (floor(colnr - rem1) / 26 - 1 + 'A');	# Leftmost digit
 		str = [tmp str];
-	elseif (colnr > 702 && colnr < 1379)
-		tmp = char (floor ((colnr - 703) / 26) + 'A');
-		str = ['A' tmp str];
-	endif
+	elseif (colnr > 702 && colnr < 18279)					# AAA-ZZZ
+		rem2 = rem ((colnr - 26 - rem1) - 1, 676);
+		str2 = char (rem2 / 26 + 'A');						# Middle digit
+		colnr = colnr -  rem2 - rem1;
+		str3 = char (colnr / 676 - 1 + 'A');				# Leftmost digit
+		str = [str3 str2 str];
+	endif	
 	celladdress = sprintf ("%s%d", str, trow + row - 1);
 
 endfunction
\ No newline at end of file

Modified: trunk/octave-forge/main/io/inst/oct2xls.m
===================================================================
--- trunk/octave-forge/main/io/inst/oct2xls.m	2010-01-03 22:21:52 UTC (rev 6696)
+++ trunk/octave-forge/main/io/inst/oct2xls.m	2010-01-03 22:30:17 UTC (rev 6697)
@@ -71,7 +71,7 @@
 
 ## Author: Philip Nienhuis
 ## Created: 2009-12-01
-## Latest update: 2009-12-30
+## Latest update: 2010-01-03 (OOXML support)
 
 function [ xls, rstatus ] = oct2xls (obj, xls, wsh, topleft='A1')
 
@@ -351,7 +351,7 @@
 
 ## Author: Philip Nienhuis
 ## Created: 2009-11-26
-## Last updated 2009-12-11
+## Last updated 2010-01-03
 
 function [ xls, rstatus ] = oct2jpoi2xls (obj, xls, wsh, topleftcell="A1")
 
@@ -440,12 +440,13 @@
 
 	obj(txtptr) = obj2(txtptr);							# Copy strings back into place
 	obj(lptr) = obj2(lptr);
+
 	typearr(txtptr) = ctype(2);							# ...and clean up 
 	typearr(emptr) = ctype(4);
-	typearr(lptr) = ctype(5);							# BOOLEAN
+	typearr(lptr) = ctype(5);							# BOOLEAN, temp NUMERIC
 
 	# Create formula evaluator (needed to be able to write boolean values!)
-	frm_eval = xls.workbook.getCreationHelper().createFormulaEvaluator();
+	frm_eval = xls.workbook.getCreationHelper ().createFormulaEvaluator ();
 
 	for ii=1:nrows
 		ll = ii + trow - 2;    		# Java POI's row count = 0-based
@@ -453,14 +454,10 @@
 		if (isempty (row)) row = sh.createRow (ll); endif
 		for jj=1:ncols
 			kk = jj + lcol - 2;		# POI's column count is also 0-based
-			cell = row.createCell (kk, typearr(ii,jj));
-			if (typearr(ii, jj) == ctype(5))
-				cell = row.createCell (kk, ctype(3));
-				# Provisionally we make do with formulas evaluated immediately 8-Z
-				if obj{ii, jj} bool = '(1=1)'; else bool = '(1=0)'; endif
-				cell.setCellFormula (bool); frm_eval.evaluateInCell (cell);
-			elseif ~(typearr(ii, jj) == 3)
-				# Just put text or number in cell
+			if (typearr(ii, jj) == ctype(4))
+				cell = row.createCell (kk, ctype(4));
+			else
+				cell = row.createCell (kk, typearr(ii,jj));
 				cell.setCellValue (obj{ii, jj});
 			endif
 		endfor

Modified: trunk/octave-forge/main/io/inst/xls2oct.m
===================================================================
--- trunk/octave-forge/main/io/inst/xls2oct.m	2010-01-03 22:21:52 UTC (rev 6696)
+++ trunk/octave-forge/main/io/inst/xls2oct.m	2010-01-03 22:30:17 UTC (rev 6697)
@@ -86,8 +86,9 @@
 ## @end deftypefn
 
 ## Author: Philip Nienhuis
-## Created: 2009-10-16
-## Latest update: 2009-12-30
+## Created: 2010-10-16
+## Latest update: 2009-01-03 (added OOXML support & cleaned up code. Excel 
+##                            INDIRECT function still not working OK)
 
 function [ rawarr, xls, rstatus ] = xls2oct (xls, wsh, datrange='')
 
@@ -397,24 +398,12 @@
 							rawarr (ii+1-firstrow, jj+1-lcol) = cell.getNumericCellValue ();
 						case ctype(2)		# 1 String
 							rawarr (ii+1-firstrow, jj+1-lcol) = char (cell.getRichStringCellValue ());
-#						case ctype(3)		# 2 Formula (if still at all needed).
-#							try				#  Provisionally we simply take the result
-#								rawarr (ii+1-firstrow, jj+1-lcol) = cell.getNumericCellValue ();
-#							catch
-#								# In case of errors we copy the formula as text into rawarr
-#								rawarr (ii+1-firstrow, jj+1-lcol) = ["=" cell.getCellFormula];
-#								type_of_cell = ctype (4);
-#								if (~jerror) 
-#									warning ("Java errors in worksheet formulas (converted to string)");
-#								endif
-#								++jerror; 
-#							end
 						case ctype(4)		# 3 Blank
 							# Blank; ignore until further notice
 						case ctype(5)		# 4 Boolean
 							rawarr (ii+1-firstrow, jj+1-lcol) = cell.getBooleanCellValue ();
 						otherwise			# 5 Error
-							# Error; ignore
+							# Formula (treated above) or error; ignore
 						endswitch
 					endif
 				endif

Modified: trunk/octave-forge/main/io/inst/xlsclose.m
===================================================================
--- trunk/octave-forge/main/io/inst/xlsclose.m	2010-01-03 22:21:52 UTC (rev 6696)
+++ trunk/octave-forge/main/io/inst/xlsclose.m	2010-01-03 22:30:17 UTC (rev 6697)
@@ -44,7 +44,7 @@
 
 ## Author: Philip Nienhuis
 ## Created: 2009-11-29
-## Latest update: 2009-12-11
+## Latest update: 2010-01-03 (checked OOXML support)
 
 function [ xls ] = xlsclose (xls)
 
@@ -60,16 +60,7 @@
 		# 51 = .xlsx - xlOpenXMLWorkbook (without macro's in 2007)
 		# 52 = .xlsm - xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007)
 		# 56 = .xls  - xlExcel8 (97-2003 format in Excel 2007)
-		#
-		# Probably to be incorporated as wb.SaveAs (filename, FileFormatNum)?
-		# where filename is properly canonicalized with proper extension.
-		# This functionality is not tried or implemented yet as there are no
-		# checks if the installed Excel version can actually write the
-		# desired formats; e.g., stock Excel 97 cannot write .xlsx; & Excel
-		# always gives a pop-up when about to write non-native formats.
-		# Moreover, in case of .csv, .wks, .txt we'll have to do something
-		# about gracefully ignoring the worksheet pointer.
-		# All To Be Sorted Out some time....
+
 		unwind_protect
 			xls.app.Application.DisplayAlerts = 0;
 			if (xls.changed > 0)

Modified: trunk/octave-forge/main/io/inst/xlsfinfo.m
===================================================================
--- trunk/octave-forge/main/io/inst/xlsfinfo.m	2010-01-03 22:21:52 UTC (rev 6696)
+++ trunk/octave-forge/main/io/inst/xlsfinfo.m	2010-01-03 22:30:17 UTC (rev 6697)
@@ -42,6 +42,9 @@
 ## specified. This can sometimes be handy to get an idea of used cell ranges
 ## in each worksheet (the COM/Excel interface can't supply this information).
 ##
+## For use on OOXML spreadsheets one needs full POI support (see xlsopen) and
+## 'poi' needs to be specified for @var{reqintf}.
+##
 ## Examples:
 ##
 ## @example

Modified: trunk/octave-forge/main/io/inst/xlsopen.m
===================================================================
--- trunk/octave-forge/main/io/inst/xlsopen.m	2010-01-03 22:21:52 UTC (rev 6696)
+++ trunk/octave-forge/main/io/inst/xlsopen.m	2010-01-03 22:30:17 UTC (rev 6697)
@@ -28,12 +28,14 @@
 ## installed on your computer + proper javaclasspath set. These interfaces
 ## are referred to as COM, POI and JXL, resp., and are preferred in that
 ## order by default (depending on their presence).
+## For OOXML support, in addition to Apache POI support you also need the
+## following jars in your javaclasspath: poi-ooxml-schemas-3.5.jar,
+## xbean.jar and dom4j-1.6.1.jar (or later versions).
 ##
-## @var{filename} should be a valid .xls Excel file name; but if you use the
+## @var{filename} should be a valid .xls or xlsx Excel file name; but if you use the
 ## COM interface you can specify any extension that your installed Excel version
 ## can read AND write. If @var{filename} does not contain any directory path,
-## the file is saved in the current directory. (Support for .xlsx (Excel 2007
-## OOXML) based on Java and Apache POI will be added later).
+## the file is saved in the current directory.
 ##
 ## If @var{readwrite} is set to 0 (default value) or omitted, the Excel file
 ## is opened for reading. If @var{readwrite} is set to True or 1, an Excel
@@ -62,7 +64,7 @@
 
 ## Author: Philip Nienhuis
 ## Created: 2009-11-29
-## Last updated 2009-12-30
+## Last updated 2010-01-03 (Added OOXML support)
 
 function [ xls ] = xlsopen (filename, xwrite=0, reqinterface=[])
 
@@ -124,6 +126,7 @@
 
 # Supported interfaces determined; Excel file type check moved to seperate interfaces.
 	chk1 = strcmp (tolower (filename(end-3:end)), '.xls');
+	chk2 = strcmp (tolower (filename(end-4:end-1)), '.xls');
 	
 	xls = struct ("xtype", 'NONE', "app", [], "filename", [], "workbook", [], "changed", 0, "limits", []); 
 	
@@ -162,15 +165,19 @@
 		xls.filename = filename;
 	
 	elseif (xlsinterfaces.POI)
-		if (~chk1)
-			error ("Currently xls2oct / POI can only read reliably from .xls files")
+		if ~(chk1 || chk2)
+			error ("Unsupported file format for xls2oct / Apache POI.")
 		endif
 		xls.xtype = 'POI';
 		# Get handle to workbook
 		if (xwrite == 2)
-			wb = java_new ('org.apache.poi.hssf.usermodel.Workbook');
+			if (chk1)
+				wb = java_new ('org.apache.poi.hssf.usermodel.HSSFWorkbook');
+			elseif (chk2)
+				wb = java_new ('org.apache.poi.xssf.usermodel.XSSFWorkbook');
+			endif
+			xls.app = 'new_POI'
 		else
-			# Get handle to xls-file. OOXML not working here despite POI Javadocs
 			try
 				xlsin = java_new ('java.io.FileInputStream', filename);
 				wb = java_invoke ('org.apache.poi.ss.usermodel.WorkbookFactory', 'create', xlsin);

Modified: trunk/octave-forge/main/io/inst/xlsread.m
===================================================================
--- trunk/octave-forge/main/io/inst/xlsread.m	2010-01-03 22:21:52 UTC (rev 6696)
+++ trunk/octave-forge/main/io/inst/xlsread.m	2010-01-03 22:30:17 UTC (rev 6697)
@@ -66,6 +66,8 @@
 ## The optional last argument @var{reqintf} can be used to override 
 ## the automatic selection by xlsread of one interface out of the
 ## supported ones: COM/Excel, Java/Apache POI, or Java/JExcelAPI.
+## For reading from OOXML files a value of 'poi' must be specified
+## for @var{reqintf} (see help for xlsopen).
 ##
 ## Erroneous data and empty cells are set to NaN in @var{numarr} and
 ## turn up empty in @var{txtarr} and @var{rawarr}. Date/time values in

Modified: trunk/octave-forge/main/io/inst/xlswrite.m
===================================================================
--- trunk/octave-forge/main/io/inst/xlswrite.m	2010-01-03 22:21:52 UTC (rev 6696)
+++ trunk/octave-forge/main/io/inst/xlswrite.m	2010-01-03 22:30:17 UTC (rev 6697)
@@ -18,7 +18,7 @@
 ## @deftypefn {Function File} @var{rstatus} = xlswrite (@var{filename}, @var{arr})
 ## @deftypefnx {Function File} @var{rstatus} = xlswrite (@var{filename}, @var{arr}, @var{wsh})
 ## @deftypefnx {Function File} @var{rstatus} = xlswrite (@var{filename}, @var{arr}, @var{wsh}, @var{range})
-## @deftypefnx {Function File} @var{rstatus} = xlswrite (@var{filename}, @var{arr}, @var{wsh}, @var{range}, @var{reqintf)
+## @deftypefnx {Function File} @var{rstatus} = xlswrite (@var{filename}, @var{arr}, @var{wsh}, @var{range}, @var{reqintf})
 ## Add data in 1D/2D array @var{arr} to worksheet @var{wsh} in Excel
 ## spreadsheet file @var{filename} in range @var{range}.
 ##
@@ -63,6 +63,8 @@
 ## The optional last argument @var{reqintf} can be used to override 
 ## the automatic selection by xlsread of one interface out of the
 ## supported ones: COM/Excel, Java/Apache POI, or Java/JExcelAPI.
+## For writing to OOXML files (.xlsx) a value of 'poi' (case-insensitive)
+## must be specified for @var{reqintf}.
 ##
 ## xlswrite is a mere wrapper for various scripts which find out what
 ## Excel interface to use (COM, Java/POI) plus code to mimic the other
@@ -87,7 +89,7 @@
 
 ## Author: Philip Nienhuis
 ## Created: 2009-10-16
-## Latest update: 2009-12-11
+## Latest update: 2010-01-04  (Adapted range capacity checks to OOXML)
 
 function [ rstatus ] = xlswrite (filename, arr, arg3, arg4, arg5)
 
@@ -103,10 +105,14 @@
 	elseif (nargin == 2)
 		# Assume first worksheet and full worksheet starting at A1
 		wsh = 1;
-		range = "A1:IV65536";
+		if (strcmp (tolower (filename(end-4:end-1)), 'xls')
+			range = "A1:XFD1048576";	# OOXML has ridiculously large limits 
+		else
+			range = "A1:IV65536";		# Regular xls limits
+		endif
 	elseif (nargin == 3)
 		# Find out whether 3rd argument = worksheet or range
-		if (isnumeric(arg3) || (isempty(findstr(arg3, ':')) && ~isempty(arg3)))
+		if (isnumeric (arg3) || (isempty (findstr (arg3, ':')) && ~isempty (arg3)))
 			# Apparently a worksheet specified
 			wsh = arg3;
 			range = "A1:IV65536";		# FIXME for OOXML (larger sheet ranges)
@@ -132,8 +138,8 @@
 	[nr, nc] = size (arr);
 	if ((nr > nrows) || (nc > ncols))
 		# Array too big; truncate
-		nr = min(nrows, nr);
-		nc = min(ncols, nc);
+		nr = min (nrows, nr);
+		nc = min (ncols, nc);
 		warning ("xlswrite - array truncated to %d by %d to fit in range %s", ...
 				 nrows, ncols, range);
 	endif
@@ -142,6 +148,6 @@
 
 	[xls, rstatus] = oct2xls (arr(1:nr, 1:nc), xls, wsh, topleft);
 
-	xlsclose (xls);
+	xls = xlsclose (xls);
 
 endfunction


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.