function writeexcel(file, varargin)
% Usage: writeexcel(file, varlist)
%
% Writes data to an Excel xml file on any system (NO Excel or ActiveX required!).
%
% Scalars will end up on a sheet called "Scalars" as name (in the first column) and
% value (in the second column) pairs. Arrays and cell arrays are saved on individual
% sheets, named by the variable name.
%
% If you find any errors, please let me know at peder at axensten dot se.
% This is still pretty much work in progress, see 'to do', below.
%
% ARGUMENTS:
% file File name path. If prefixed with a '>' (see example, below), the data will
% be added to an existing file (that was generated by this macro!).
% varlist List of variables, expressions, and/or strings. Variables and expressions
% will be written to the file, expressions must be preceded by a '\name', below.
% A special case is if a variable of type structure is called DocumentProperties,
% its field names and values will set the document properties (see the example).
% Strings can be of the following:
% - '\name' sets the name of the next variable or expression, names must be unique.
%
% EXAMPLE:
% DocumentProperties= struct('Title','My Title','Author','Me','Subject','very interesting');
% astring= 'Hi ya!';
% ascalar= pi;
% anarray= [1 2 3; 4 5 6];
% acellarr= {'abc', 'def'; pi, NaN; -Inf Inf};
% writeexcel('Workbook.xml', anarray, ascalar, astring, acellarr, DocumentProperties);
% writeexcel('>Workbook.xml', '\another scalar', 2*pi/5);
%
% HISTORY:
% Version 1.0, first working version, 2007-09-04.
% Version 1.1, released 2007-09-06:
% - Fixed a bug when saving vectors.
% - Fixed a bug when adding scalars when there previously were none.
% - Now UTF-8 works.
% - Added check for possible fopen problems.
% - Added better input argument check.
% - Can now add document metadata through structure named DocumentProperties.
% - Writing structures too, columns named by fieldnames (the struct class).
% - Writing symbolic expressions too (the sym class).
% Version 1.2, released 2007-09-18:
% - Fixed mlint warnings.
% - Fixed problem with certain characters in meta data.
% - Removed all regexprep calls for better compatibility.
% - Removed automatic .xls suffix.
%
% TO DO:
% - '%format' or {'%form1', '%form2', etc.} to control how columns will be shown (numbers will be
% stored with 'all' decimals, but the column formats will be set)
%
% COPYRIGHT: (c) 2007 Peder Axensten. Use at own risk.
%
% KEYWORDS: save, export, write, Microsoft, Excel, xml
%
% REQS: Does NOT require Excel, Active X, or a Windows system!
%
% REFERENCE: http://msdn2.microsoft.com/en-us/library/aa140066(office.10).aspx
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% Check the arguments.
if(nargin < 1), error('At least one arguments!'); end
% Get the name of the calling function, if there is one.
thesource= dbstack;
if(numel(thesource) < 2)
thesource= sprintf('%s, on %s', 'Command Window', datestr(now));
else
thesource= sprintf('%s, line %u, on %s', thesource(2).file, thesource(2).line, datestr(now));
end
% Initialise.
scalarsend= sprintf('\t\t<!-- END OF SCALARS HERE! -->\n');
worksheetsstart=sprintf('\t<!-- START OF WORKSHEETS HERE! -->\n');
worksheetsend= sprintf('\t<!-- END OF WORKSHEETS HERE! -->\n');
docpropsend= sprintf('\t\t<!-- END OF DOCUMENTPROPERTIES HERE! -->\n');
tags.docprop= sprintf('%%s\t\t<%%s>%%s</%%s>\n');
tags.doc= sprintf('%s', ...
'<?xml version="1.0" encoding="UTF-8"?>\n', ...
'<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"\n', ...
'\txmlns:html="http://www.w3.org/TR/REC-html40"\n', ...
'\txmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">\n', ...
'\t<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">\n', ...
sprintf(tags.docprop, '', 'AppName', 'MATLAB', 'AppName'), ...
sprintf(tags.docprop, '', 'Category', 'MATLAB generated data', 'Category'), ...
docpropsend, ...
'\t</DocumentProperties>\n', ...
'\t<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">\n', ...
'\t\t<WindowHeight>15000</WindowHeight>\n', ...
'\t\t<WindowWidth>13000</WindowWidth>\n', ...
'\t\t<WindowTopX>10</WindowTopX>\n', ...
'\t\t<WindowTopY>40</WindowTopY>\n', ...
'\t</ExcelWorkbook>\n', ...
'\t<Styles>\n', ...
['\t\t<Style ss:ID="ColHead"><Font ss:Bold="1"/>', ...
'<Alignment ss:Horizontal="Center"/></Style>\n'], ...
'\t\t<Style ss:ID="RowHead"><Font ss:Bold="1"/></Style>\n', ...
'\t</Styles>\n', ...
worksheetsstart, ...
'%s', ...
worksheetsend, ...
'</Workbook>\n');
tags.sheet= ['%s\t<Worksheet ss:Name="%s"><Table>\n\t\t<!-- Added by %s -->\n', ...
'%s\t</Table></Worksheet>\n'];
tags.row= '%s\t\t<Row>\n%s\t\t</Row>\n';
tags.col= '%s\t\t\t<Cell><Data ss:Type="%s">%s</Data></Cell>\n';
tags.scalar= sprintf(tags.col, '', 'String', '%s');
tags.scalar= sprintf(tags.col, tags.scalar, '%s', '%s');
tags.scalar= sprintf(tags.row, '%s', [sprintf('\t\t\t<!-- Added by %%s. -->\n') tags.scalar]);
tags.funcol= '%s\t\t\t<Cell ss:Formula="=%s"><Data ss:Type="Number"/></Cell>\n';
% Create the sheets.
argname0= '';
thesheets= '';
thescalars= '';
docprops= '';
for arglist= 1:length(varargin) % Loop thru the arguments.
therows= '';
argval= varargin{arglist};
argname= inputname(1+arglist);
if(~isempty(argname0))
argname= argname0;
end
if(isempty(argname)) % This argument is not a variable
if(argval(1) == '\') % It's a string defining the name of next expression.
argname0= argval(2:end);
elseif(ischar(argval)) % It's some other kind of string.
warning('pax:writeexcel:argcheck', 'Unrecognized string in argument %d: "%s"', arglist+1, argval);
else % It's some other expression.
warning('pax:writeexcel:argcheck', 'Unnamed expression in argument %d (class %s).', ...
arglist+1, class(argval));
end
elseif(strcmp('DocumentProperties', argname) && isstruct(argval) && (numel(argval) == 1))
% This argument is a variable called DocumentProperties.
fields= fieldnames(argval);
for n= 1:length(fields)
if(~ischar(argval.(fields{n})))
error('All field values must be strings in DocumentProperties!');
end
docprops= sprintf(tags.docprop, docprops,fields{n}, cleanup(argval.(fields{n})), fields{n});
end
else
% This argument is a variable to be saved.
argval= do2cell(argval, arglist+1);
if(numel(argval) == 1)
% This is a scalarm, add it to the scalars' sheet.
[kind, val]= oneitem(argval{1, 1}, arglist+1, 1, 1);
thescalars= sprintf(tags.scalar, thescalars, thesource, argname, kind, val);
else
% Not a scalar: give it a sheet of its own.
for row= 1:size(argval, 1)
thecols= '';
for col= 1:size(argval, 2)
[kind, val]= oneitem(argval{row, col}, arglist+1, row, col);
thecols= sprintf(tags.col, thecols, kind, val);
end
therows= sprintf(tags.row, therows, thecols);
end
thesheets= sprintf(tags.sheet, thesheets, argname, thesource, therows);
end
argname0= '';
end
end
% Save the file.
if(file(1) == '>')
% We are adding data to a previous file.
file= file(2:end);
[fh, err]= fopen(file, 'r', 'native', 'UTF-8');
if(~isempty(err)), error([err ': ' file(2:end)]); end
xls= fread(fh, '*char')';
fclose(fh);
% Add the new scalars to xls!
if(~isempty(thescalars) && isempty(findstr(xls, scalarsend)))
% We have scalars now, but there were none stored in the file previously.
thescalars= sprintf(tags.sheet, '', 'Scalars', '(check each row for source)', ...
[thescalars scalarsend]);
xls= strrep(xls, worksheetsstart, thescalars);
else
% Add present scalars to previous.
xls= strrep(xls, scalarsend, [thescalars scalarsend]);
end
% Add the new sheets to xls!
xls= strrep(xls, worksheetsend, [thesheets worksheetsend]);
else
% There is no previous file.
if(length(thescalars)+length(thesheets) == 0)
% There is nothing to save at the moment, create an empty xml file.
thescalars= sprintf(tags.scalar, '', thesource, '', 'String', '');
end
if(~isempty(thescalars))
% There are scalars, at their sheet.
thescalars= sprintf(tags.sheet, '', 'Scalars', '(check each row for source)', ...
[thescalars scalarsend]);
end
% Complete the xml document.
xls= sprintf(tags.doc, [thescalars thesheets]);
end
% Add the new document properties to xls!
xls= strrep(xls, docpropsend, [docprops docpropsend]);
% Save the results.
[fh, err]= fopen(file, 'w', 'native', 'UTF-8');
if(~isempty(err)), error([err ': ' file]); end
fprintf(fh, xls);
fclose(fh);
% Check that all worksheet names are unique!
names= regexpi(xls, '<Worksheet ss:Name="[^""]*">', 'match');
if(size(names, 2) ~= size(unique(names), 2))
warning('pax:writeexcel:argcheck', ...
[ 'There are multiple work-sheets with the same name. ' ...
'Excel will probably not open this file. ' ...
'Check that the same variable name is not used more than once.']);
end
end
function val= do2cell(val, argnum)
err= ['Problem in argument ' num2str(argnum) ': Number of dimensions must not exceed %d for %s'];
switch class(val)
case 'cell'
if(ndims(val) > 2), error(err, 2, 'cell arrays'); end
case 'struct'
if(numel(val) > length(val)), error(err, 1, 'structures'); end
val= cat(2, fieldnames(val), squeeze(struct2cell(val)))';
case 'char'
if(ndims(val) > 3), error(err, 3, 'char arrays'); end
val= cellstr(val);
case 'function_handle'
if(ndims(val) > 2), error(err, 2, 'function handles'); end
val= cellstr(func2str(val));
case {'double', 'single'}
if(ndims(val) > 2), error(err, 2, 'numeric arrays'); end
val= num2cell(val);
case 'sym'
if(ndims(val) > 2), error(err, 2, 'symbolic expressions'); end
val= squeeze(cell(val));
case {'int8', 'uint8', 'int16', 'uint16', 'int32', 'uint32', 'int64', 'uint64'}
if(ndims(val) > 2), error(err, 2, 'numeric arrays'); end
val= num2cell(val);
otherwise
error(['Problem in argument ' num2str(argnum) ': Unrecognised class ' class(val)]);
end
end
function [kind, val]= oneitem(val, argnum, row, col)
switch class(val)
case 'char'
kind= 'String';
case 'function_handle'
kind= 'String';
val= func2str(val);
case 'logical'
kind= 'Boolean';
val= num2str(val);
case {'double','single','int8','uint8','int16','uint16','int32','uint32','int64','uint64'}
if(numel(val) > 1)
error('Problem in argument %d row %d col %d: Cell array may not contain an array', ...
argnum, row, col);
end
if(isnan(val))
kind= 'String';
val= '–';
elseif(isinf(val))
kind= 'String';
if(val < 0), val= '-∞';
else val= '∞';
end
else
kind= 'Number';
val= num2str(val);
end
case {'cell','struct'}
error('Problem in argument %d row %d col %d: Cell array may not contain %s', ...
argnum, row, col, class(val));
otherwise
error('Problem in argument %d row %d col %d: Unrecognised class %s', ...
argnum, row, col, class(val));
end
val= cleanup(val);
end
function s= cleanup(s)
s= strrep(s, '<', '<');
s= strrep(s, '>', '>');
end
