How can I write into an excel file column wise?

Hi,

I have a code that outputs 7 values each time and I want that values to be written column wise in excel e.g. first 7 values to be written in column 1 from A1 to A7, next 7 values in column 2 from B1 to B7 and so on.

How can I achieve this?

Any help would be much appreciated. Thanks.

Answer 1

[dpb]

for col=1,N
  V=yourColumnOutputFunction(...
  xlswrite(file,sheet,[xlsAddr(1,col) ':' xlsAddr(size(V,1),col)]);
end

where xlsAddr is my helper utility function

function rnge=xlsAddr(row,col)
% Build Excel cell address from row, column
%
% RNGE=XLSADDR(COL,ROW) will return an Excel cell address 
% formed from the input ROW,COL values.  Either input may be
% string or numeric and will be converted to canonical form

if isnumeric(col)
  if ~isscalar(col),  error('Input Column Not Scalar'), end
  rnge=num2str('A'+[fix(col/26) rem(col,26)]-1,'%c%c');
  rnge(rnge=='@')=[];   % cleanup for single character
else
  rnge=col;
end
if isnumeric(row)
  if ~isscalar(row),  error('Input Row Not Scalar'), end
  rnge=[rnge num2str(row,'%d')];
else
  row=num2str(row,'%d');
  if ~all(ismember(row,'0':'9')),  error('Invalid Excel Address: Row not numeric'), end
  rnge=[rnge row];
end

There's a complement,

function [row,col]=xlsRowCol(rnge,r1,c1)
% Return row, column from Excel range address and optional offset
%
% [ROW,COL]=XLSADDR(RNGE) will return a ROW,COL array index values
% formed from the input Excel cell range expression.  Default addressing
% is one-based array indexing.
%
% [ROW,COL]=XLSADDR(RNGEA:RNGEB) will return a ROW,COL array index values
% formed from the input Excel cell range expression as 2D array by row.
%
% [ROW,COL]=XLSADDR(RNGE,R1,C1) will use optional R1, C1 values as base
% indices for the returned ROW,COL array index values

    switch nargin
      case 1
        r1 = 0;
        c1 = 0;
      case 2
        c1 = 0;
    end

    rnge=char(split(rnge,':'));         % split out the ranges if exist
    m=size(rnge,1);
    row=zeros(m,1); col=zeros(m,1);
    for i=1:m
      cstr=rnge(i,isletter(rnge(i,:)));    % pull out column letters only
      if length(cstr)>2, error('Input Column Too Long'), end
      bArr=[1 26];                  % hardcode base vector since not general
      b=bArr(1:length(cstr)).';     % base vector for specific input length
      col(i)=(cstr-'@')*b;

      rstr=rnge(i,ismember(rnge(i,:),'0':'9'));  % pull out row numbers only
      row(i)=str2num(rstr);                  % and convert to numeric row
    end
    % convert to reference origin before returning
    row=row+r1;
    col=col+c1;
  end

[Ed Callway]

DB, thanx for the code, got me out of a hole today!

It didn't seem to work for large # of columns, updated the col math with some help from stackoverflow

function rnge=xlsAddr(row,col)
% Build Excel cell address from row, column
%   originally from https://www.mathworks.com/matlabcentral/answers/399196-how-can-i-write-into-an-excel-file-column-wise
% updated by Ed 2020 Mar with loop from stackoverflow to handle more columns
%   https://stackoverflow.com/questions/181596/how-to-convert-a-column-number-e-g-127-into-an-excel-column-e-g-aa
% RNGE=XLSADDR(COL,ROW) will return an Excel cell address 
% formed from the input ROW,COL values.  Either input may be
% string or numeric and will be converted to canonical form

% make the column part, it's funky alphabet math, close to base 26 with some mods
  if isnumeric(col)     % great if the column input is a number
    if ~isscalar(col),  error('Input Column Not Scalar'), end   % give up if not really a number
    d = int32(col);                 % start with the requested # of columns, int32 handles a LOT!
    rnge = '';                      % and a blank output string
    while (d > 0);                  % keep turning number into alphabetically named columns until nothing left
      m = mod(d - 1, 26);           % get remainder after dividing by 26 = alphabet
      rnge = [char(65 + m) , rnge]; % turn that into a letter starting with 'A'=65, prepend to existing output string
      d = int32((d - m) / 26);      % remove the amount you took out, divide by 26 and loop again...maybe
    end
  else
    rnge=col;                       % if col input wasn't a pure number, ASSUME it is already a perfect col add format like 'CM' and pass it on
  end
  
% make the row part, just numbers so easier
  if isnumeric(row)                 % great if the row input is a number
    if ~isscalar(row),  error('Input Row Not Scalar'), end   % give up if not really a number
    rnge=[rnge num2str(row,'%d')];  % convert row number to string, append to col add just made above
  else
    row=num2str(row,'%d');          % not a straight number, try converting to a string
    if ~all(ismember(row,'0':'9')),  error('Invalid Excel Address: Row not numeric'), end   % if digits other than 0..9, fail out
    rnge=[rnge row];                % append row to col add just made above
  end
end  % function xlsAddr