MATLAB: GUIDE editable box, Excel to Matlab Column conversion

cell arraysexcelguide

As most know Excel uses the naming of columns using A,B,C…and so forth, whereas in Matlab it is numerical 1,2,3… In my program a user can choose ANY excel document and convert it to a .mat(specifically gets the {raw} file which in turn each column will be analyzed. I cannot seem to find the code/logic that when using converts that letter to now Ex. column 2 and saves that column separately so I can make calculations on it. Another example is the user enters A into the editable text box, and the code then takes A knows it is an editable text box in GUIDE the user can enter any column from there Excel sheet Ex. B, and then the code column 1 in Matlab and gets that column and saves it separately. In addition the number of columns will not exceed Z in excel and 26 in Matlab, as well as I am analyzing cell arrays. Thank you so much!!

Best Answer

Tessa - if you assume that the Excel columns contain alphabetic characters in the set {A,B,C,...,Z}, then you can try using the following conversion

 function [matCol] = exceToMatlab(excelCol)
 numChars = length(excelCol);
 matCol = 0;
 for k=1:length(excelCol)
    % convert the character to an integer: A->1, B->2, etc.
    charAsInt = int64(upper(excelCol(k))) - 64;  
    % add to our running total
    matCol = matCol + charAsInt * (26 ^ (numChars - k));
 end

In the above, we assume that each character can be mapped to the integers 1 through 26, and then, depending upon which position the character is in, we multiply it by 26 to the exponent of that character position less one. So,

 A  --> 1 * 26 ^ (1 - 1) = 1 * 26^0 = 1
 B  --> 2 * 26 ^ (1 - 1) = 2 * 26^0 = 2
 ...
 Z  --> 26 * 26 ^ (1 - 1) = 26 * 26^0 = 26
 AA --> 1 * 26 ^ (2 - 1) + 1 * 26 ^ (2 - 2) = 1 * 26^1 +  1 * 26^0 = 27
 AB --> 1 * 26 ^ (2 - 1) + 2 * 26 ^ (2 - 2) = 1 * 26^1 +  2 * 26^0 = 28
 etc.

Try the above and see what happens!

Related Solutions

MATLAB: How to save xcel file in two coloum in matlab using xlswrite

Rohit - what can you tell us about the data in the text and edit fields? Are they numeric or do they contain characters? If a combination of the two, you may want to create a 12x2 cell array where each element from the first column corresponds to one of the text boxes, and each element in the second column corresponds to one of the edit boxes. Something like the following should do

 data = cell(12,2);
 data{1,1} = char(get(handle.text1,'String')); % convert to number if needed with str2double
 data{2,1} = char(get(handle.text2,'String'));
 % etc.

 data{1,2} = char(get(handles.edit1,'String'));
 % etc.

Then use xlswrite to save to file

 myFilename = 'myExcelFile.xls';
 xlswrite(myFileName,data);

Try the above and see what happens!

MATLAB: Dynamic ranges using xlswrite

You can do this by dynamically creating the character string that you send to xlswrite to specify xl range.

Something like this:

>> firstRow = 1;
>> lastRow = 5;
>> firstCol = 'C';
>> lastCol = 'AY';
>> cellRange = [firstCol,num2str(firstRow),':',lastCol,num2str(lastRow)]
cellRange =
C1:AY5

The only thing left, is to figure out what "lastCol" is based on the column number. I wrote a function to do this myself a little while back. I know there are other versions floating around as well.

For example:

>> xlcolumnletter(51)
ans =
AY

Here's the code:

function colLetter = xlcolumnletter(colNumber)
% Excel formats columns using letters.
% This function returns the letter combination that corresponds to a given
% column number.
% Limited to 702 columns
if( colNumber > 26*27 )
    error('XLCOLUMNLETTER: Requested column number is larger than 702. Need to revise method to work with 3 character columns');
else
    % Start with A-Z letters
    atoz        = char(65:90)';
      % Single character columns are first
      singleChar  = cellstr(atoz);
      % Calculate double character columns
      n           = (1:26)';
      indx        = allcomb(n,n);
      doubleChar  = cellstr(atoz(indx));
      % Concatenate
      xlLetters   = [singleChar;doubleChar];
      % Return requested column
      colLetter   = xlLetters{colNumber};
  end

Best Answer

Related Solutions

MATLAB: How to save xcel file in two coloum in matlab using xlswrite

MATLAB: Dynamic ranges using xlswrite

Related Question