How to modify my MatLab script to correctly write to Excel?

Question

So I basically have a small script in MatLab which I'm using in a much larger program. This script essentially keeps writing data to an Excel file.

The script writes data horizontally across cells and is as follows:

function writevar(x,k,filename)
    col = char(64+k);
    N = size(x,1);
    Rg = sprintf([col '1:' col '%i' ],N);
    xlswrite(filename,x,Rg)
end

The script works perfectly when writing data between columns A-Z. However it crashes once it reaches column Z. How can I modify the script so that it can keep writing beyond column Z to AA, AB, etc (it needs to go right the way down to CR).

Thanks.

Your problem is the line char(64+k). Once you reach the ASCII code for Z (90) you will get the ASCII code of [ (91). You need to handle this case adding a second char on the left and reset the first one to 64 (ASCII for 'A'). This way you will get without problems up to ZZ. — user2261062
– user2261062, Commented Jul 5, 2016 at 13:09
Possible duplicate of Convert Excel Column Number to Column Name in Matlab — sco1
– sco1, Commented Jul 5, 2016 at 15:36

Mad Physicist · Accepted Answer · 2016-07-05 13:19:29Z

1

You are correctly processing a values of k in the range [1, 26]. Presumably column AA would be k = 27 in your input, so you need to do some special math on that. Here is a snippet that should convert k to a column name in the general case:

k = k - 1; % Convert to zero-based for ease of processing
col = '';
while k ~= 0
    % Prepend higher digits
    col = [char(mod(k, 26) + 65) col];
    k = floor(k / 26);
end

answered Jul 5, 2016 at 13:19

Mad Physicist

116k29 gold badges202 silver badges292 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paamand · Accepted Answer · 2016-07-05 14:03:16Z

1

One way to handle this (and see BlackAdder's comment) would be

function writevar(x,k,filename)
  if (k > 26) {
    col = [char(64+ceil(k/26)) char(64+mod(k,26))];
  else
    col = [char(64+k);
  end
  N = size(x,1);
  Rg = sprintf([col '1:' col '%i' ],N);
  xlswrite(filename,x,Rg)
end

edited Jul 5, 2016 at 14:03

answered Jul 5, 2016 at 13:20

Paamand

7361 gold badge6 silver badges18 bronze badges

3 Comments

Paamand Over a year ago

This solutions only goes to column 'ZZ'. Otherwise see Mad Physicist's answer.

Cypher236 Over a year ago

Thank you for your answer! It's still helpful to see how it's done appreciate it!

Mad Physicist Over a year ago

I strongly suspect that your code won't work. Last time I checked, curly braces are only used for cell arrays in MATLAB.

Collectives™ on Stack Overflow

How to modify my MatLab script to correctly write to Excel?

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related