CODEWITHSUNDEEP
matlabxls
S_S
S_S

Reputation: 1402

MATLAB xlswrite name data

How can I assign a name to a matrix being written from MATLAB to Excel using xlswrite?

This is equivalent to opening the xls sheet, selecting the data and right-click->define name. Here I can assign a string to the matrix. How can I do this from MATLAB?

Another application I use reads the data from the xls file based on named range.

Upvotes: 0

Views: 590

Answers (1)

Hoki
Hoki

Reputation: 11802

You cannot do that from the function xlswrite as far as i know. You have to use an excel COM server from within Matlab.

The example below is derived from the Mathworks example, but adapted to your need. Note that you won't need to use xlswrite this way, you input your data directly into the excel sheet.

%// First, open an Excel Server.
e = actxserver('Excel.Application');

%// Insert a new workbook.
eWorkbook = e.Workbooks.Add;
e.Visible = 1;

%// Make the first sheet active.
eSheets = e.ActiveWorkbook.Sheets;

eSheet1 = eSheets.get('Item', 1);
eSheet1.Activate;

%// Name the range you want:
e.Names.Add( 'TestRange' , '=Sheet1!$A$1:$B$2' ) ;

%// since you are here, directly put the MATLAB array into the range.
A = [1 2; 3 4];
eActivesheetRange = e.Activesheet.get('Range', 'TestRange');
eActivesheetRange.Value = A ;

%// Now, save the workbook.
eWorkbook.SaveAs('myfile.xls');

Edit to answer your question in comment:

If you want to programatically define the range address, you have to build the address as a string. The row index is easy enough, but excel activeX in Matlab does not allow referencing cells/column/range by index, so there is a bit more fiddling to get the address of the column right (in case you overflow on the AAA... ranges.

The bit below should let you do that:

%// test data
A = rand(4,32);
%// input the upper left start of the range you want
sheetName = 'Sheet1' ;
startCol = 'Y' ;
startRow = 4 ;

%// calculate the end coordinates of the range
endRow = startRow + size(A,1)-1 ;
%// a bit more fiddling for the columns, as they could have many "characters"
cols = double(startCol)-64 ;
colIndex = sum(cols .* 26.^(length(cols)-1:-1:0)) ; %// index of starting column
endCol = eSheet1.Columns.Item(colIndex+size(A,2)-1).Address ;
endCol = endCol(2:strfind(endCol, ':')-1) ;         %// column string reference

%// build range string in excel style
rngString = sprintf('=%s!$%s$%d:$%s$%d',sheetName,startCol,startRow,endCol,endRow) ;

myRange = eSheet1.Range(rngString) ; %// reference a range object
myRange.Value = A ;                  %// assign your values

%// Add the named range
e.Names.Add( 'TestRange' , myRange ) ;

The test data deliberately overflow from column Y to column BD to make sure that the address translation could handle (i) adding a character, and (ii) overflowing from Ax to By. This seems to work good, but if your data arrays are not that wide then you don't need to worry about it anyway.

Upvotes: 1

Related Questions