File Exchange

image thumbnail

Read and Write Open Document Format (ODF) Spreadsheet (.ods)

version 1.5.0.0 (6.85 KB) by Paul
Wrappers for the matlab xlsread / xlswrite commands that read ODF Spreadsheet files (.ods).

6 Downloads

Updated 15 Nov 2013

View License

On a pc the wrapper will simply call xlsread / xlswrite, but on a mac or unix platform will read an Open Document Format (ODF) spreadsheet, for example as saved by Open Office.

The wrapper uses java commands from the ODFDOM project which provides java libraries for reading Open Document Format files. The library is not provided here, to download see:

http://incubator.apache.org/odftoolkit/

Once the odfdom.jar file is download it must be added to the javaclasspath (see Matlab documentation on importing java libraries).

This wrapper was developed and tested only on a Mac platform (OSX 10.6.4) with Matlab 2010b using ODFDOM version 0.8.6 (only slight testing with 0.8.7), but should work on other platforms. It could be easily modified to read .ods on a pc or accept variable number of input arguments as xlsread does (currently all xlsread inputs must be specified).

Cite As

Paul (2020). Read and Write Open Document Format (ODF) Spreadsheet (.ods) (https://www.mathworks.com/matlabcentral/fileexchange/28411-read-and-write-open-document-format-odf-spreadsheet-ods), MATLAB Central File Exchange. Retrieved .

Comments and Ratings (19)

Many thanks Paul, for the very useful code!

Iio, I had the same problem as you, but I don't think the code loops at "odsCells = odsTable.getCellRangeByPosition(brng,erng);". Instead, I think the problem is that getCellRangeByPosition in java reads an awful lot of cells, as explained here: https://issues.apache.org/jira/browse/ODFTOOLKIT-215

The easiest way to work around this is to use java method getCellByPosition instead. I changed the final part of the code (line 67 onwards) as follows, which for me solved the problem:

% get the range of cells specified
i = strfind(rng,':');
if isempty(i)
brng=rng; erng=rng;
else
brng = rng(1:i(1)-1); erng = rng(i(1)+1:end);
end
odsCell = odsTable.getCellByPosition(brng);
bc=getColumnIndex(odsCell); br=getRowIndex(odsCell);
odsCell = odsTable.getCellByPosition(erng);
ec=getColumnIndex(odsCell);er=getRowIndex(odsCell);

% iterate over the range and assign string and numeric values to outputs.
% only assign "float" and "string" types.
strs = cell(er-br+1,ec-bc+1);num = nan(er-br+1,ec-bc+1);
for r=br:er
for c=bc:ec
odsCell = odsTable.getCellByPosition(c,r);
type = char(odsCell.getValueType());
if ~isempty(type) && strcmpi(type,'float')
d=double(odsCell.getDoubleValue());
if ~isempty(d)%this is because somehow empty cells can have type float
num(r-br+1,c-bc+1) = d;
end
elseif ~isempty(type) && strcmpi(type,'string')
d = char(odsCell.getStringValue());
if ~isempty(d)%this is because somehow empty cells can have type string
strs{r-br+1,c-bc+1} = d;
end
end % if ~isempty
end % for each column
end % for each row

% trim the read to include only non-empty cells
estrs=zeros(size(strs));
for j=1:numel(strs)
estrs(j)=isempty(strs{j});
end
[row,col] = find(~estrs);
strs = strs(min(row):max(row),min(col):max(col));

% for strict compatibility with PC xlsread (default).
% use anything other than 'basic' or '' for mode to return full num matrix
% which has NaNs in all string locations for range being read.
[row,col] = find(~isnan(num));
if ignore_hdrs
if isempty(row)
num=[];
else
num = num(min(row):max(row),min(col):max(col));
end
end

lio

Thank for the code.
The programm is looping at line
"odsCells = odsTable.getCellRangeByPosition(brng,erng);" for some of my ODS files (taht I can provide you).
I'm on Mac OSX, Matlab 2009

Marcello

It is not intended to wprk with opeoffice on a windows machine, is it?

Ia am a Windows user, but do not have MO. How can I work around it?

Paul

Hi John,
Yeah, I have the same problem here with the new version. Sorry, don't have time to debug it currently. I've verified ODFDOM versions 0.8.6 and 0.8.7, so you'll have to use an old version for now. They are still available for download on the same site:
http://incubator.apache.org/odftoolkit/odfdom/index.html
under the heading "The Earlier Release".

Hi, Paul

I am trying to read in an OpenOffice .ods spreadsheet on my Linux workstation. I get this error:
Error using myxlsread (line 54)
Can not read TankTest.ods MATLAB:subscripting:classHasNoPropertyOrMethod

It fails, apparently at:
odsDoc = OdfSpreadsheetDocument.loadDocument(infile);

I have downloaded the latest version of ODFDOM (odfdom-java-0.8.9-incubating.jar) and added it to my MATLAB java class path. I checked with the command javaclasspath, and it is there. Any suggestions on what to try next?

Paul

Hi Jake, hmmm, good point... maybe I should be :) Just submitted fix.

Jake Hughey

Hi Paul,
On line 23, the call to myxlswrite should probably be a call to xlswrite, unless you're trying to punish windows users by putting them in an infinite loop ;-).

Paul

Thanks Beat. Turns out both row_col_to_cellID and cellID_to_row_col in myxlswrite were not correct. I fixed and verified them more thoroughly (update just submitted). Also updated the link to ODFDOM in Description.

Great idea. I guess the URL for the jar changed to

http://incubator.apache.org/odftoolkit/

To make it work with the rng start cell given and a number of columns > 26 I had to change row_col_to_cellID() to calculate the column name via num2abc() (see file exchange submission). The original code results e.g. in BH instead of AH.

Paul

Typo in last comment... I have NOT tested extensively on pc, but simply verified that it basically works.

Paul

Hi Claire,
hmmm, it should work on PC with the ispc commented as you did. Are you sure that you edited classpath.txt (the one that Matlab is using, you can tell with 'which classpath.txt') and added the odfdom.jar location to that file? See my comment on 13 Aug 2010. If not, can you step through and figure out which try block specifically the error is coming from and then maybe I can help? I have tested extensively on PC, but I have verified that it basically works.

Also, I'm updating the package again with a couple more bug fixes since this most recent update, so try the new files also (after they post).

Hi Paul, in fact I work on a pc BUT with openoffice documents. I commented the option ispc but the error is now
'Can not read HyetoHydro_v5.ods MATLAB:undefinedVarOrClass'

Does it mean that this great tool is not usable on a windows PC to work with openoffice documents?

thanks!

Claire

Felix

Thx a lot,
you've made work much easier for me!

This tool is indispensible, keep up the good work!
I notice one difference between the unix and pc implementations:
On a pc, when a spreadsheet uses the first row for column names, the num output starts with row two. The unix version inserts a row of NaNs. This requires wrapping the wrapper in conditionals for dual platform implementations. Just thought I'd plug for a minor fix. Again, thanks!

Hey Paul, this is GREAT! Always reading csv files was getting old. Do you think that without too much work we could reverse this and have an odswrite? I'd be happy to help.

Paul

Hmm, I can't reproduce this error. You'll need to define the other arguments also, but your error is before this. You can change the error line in the m file to:

error(['Can not read ' infile ' ' ME.identifier]);

and see what the error is. I'm guessing that you don't have the javaclasspath set correctly. If this is the case, the error will say something like 'MATLAB:undefinedVarOrClass' To fix this:

edit classpath.txt

and add the full directory and name of the odfdom.jar file to the bottom of this file, for example:

/Users/name/Documents/MATLAB/lib/odfdom.jar

Thanks, it's geat. But ...!
I put on my directory a file test.ods with OpenOffice
When I want to read it:
>> NUMERIC=myxlsread('test')
??? Error using ==> myxlsread at 39
Can not read test.ods

Whereis the mistake?

Thanks

Updates

1.5.0.0

Fixed pc infinite loop.

1.4.0.0

Fixed bugs in myxlswrite with row_col_to_cellID and cellID_to_row_col internal functions that map integer row and column numbers to spreadsheet row and column string identifiers.

1.3.0.0

Fixed a bug introduced to xlsread with header option, added document close calls to end of xlsread and xlswrite, this most likely was the source of a "memory leak" problem for read.

1.2.0.0

The package has finally been updated with myxlswrite for writing .ods files. Also myxlsread now defaults to the same behavior as xlsread which ignores column and row headers in the numeric output (bug pointed out by Alex in comments, thanks).

MATLAB Release Compatibility
Created with R2010b
Compatible with any release
Platform Compatibility
Windows macOS Linux