Writing strings into excel using Matlab?

Question

I am writing a cell array of string into Excel from Matlab. I have a cell array data{} that I am trying to write into Matlab. It should writting three large lengths of strings to excel since the strcmp passes 3 times. Currently it is only writing the last set of strings into excel. data = { {1x25} {1x35} {1x20} } looks like this. Also I would like to be able to write the data into three cells instead of getting copyied into as many cells as there are lines in the element of the cell array. Is this possible to do with Matlab to excel?

done = {}

for i = 1:3
   q = strcmp(x_gene_ID{i},locus_tags{i});
   if q ==1
       done{end+1} = data{i};
       disp(done);

   end


end


w = xlswrite('data.xlsx',done','E2:E400');

Ok that helps I am aware the cell array's are larger than 3 cell range. I am trying to get the Nx1 cell array to fit into one cell in Excel because It needs to correspond to information in an adjacent cell. Is this at all possible to do?

A     B      C        D                   E  
w   Rv0146  na  Rv0039c (i want the cell array1 to go here)
s   Rv0156  na  Rv0029c (i want the cell array2 to go here)
s   Rv0156  na  Rv0029c (i want the cell array2 to go here)

Here is what I am looking to do in excel

It appears each cell in data is a cell array of strings. I don't think XLSWRITE will allow you to put an entire cell array of strings into one cell of a spreadsheet. It will try to put each cell of the array into its own cell of the spreadsheet. — gnovice
– gnovice, Commented Jun 23, 2010 at 20:21
Would you suggest using another language or do you think this is a problem that Matlab cannot do? — Ben Fossen
– Ben Fossen, Commented Jun 23, 2010 at 21:09

Community · Accepted Answer · 2017-02-08 14:27:31Z

5

UPDATED ANSWER:

If I understand correctly, it appears that your variable data is a cell array where each cell contains a 1-by-N (or perhaps N-by-1) cell array of strings. If you want to try and fit each of these cell arrays of strings into one cell of a spreadsheet, you are going to need to format each into a single long string first.

Here's an example of how you could format the cell arrays of strings by concatenating them together with a newline between them:

data = {{'hello' 'hi' 'hey'} ...              %# Sample cell array of 1-by-N
        {'world' 'earth' 'everyone'} ...      %#   cell arrays of strings
        {'blah' 'blah'}};
data = cellfun(@(x) {strcat(x,{char(10)})},data);  %# Add newline characters
                                                   %#   to the string ends
data = cellfun(@(x) {deblank([x{:}])},data);  %# Concatenate the inner cells and
                                              %#   remove the trailing newlines

Now that each cell array of strings is just a single long string, each string can be written to a cell of an Excel spreadsheet as follows:

xlswrite('data.xls',data(:),'Sheet1','E2');  %# Write the data to cells E2 to E4

And here's what the resulting spreadsheet looks like:

alt text

If you use a space ' ' instead of a newline character, here's what the spreadsheet looks like (after adjusting the row and column widths):

alt text

Functions used in the above code: CELLFUN, STRCAT, CHAR, DEBLANK, XLSWRITE.

edited Feb 8, 2017 at 14:27

CommunityBot

11 silver badge

answered Jun 23, 2010 at 19:57

gnovice

126k16 gold badges259 silver badges364 bronze badges

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

4 Comments

Ben Fossen Over a year ago

I have updated my code to fix the problem but am still having problems when I write to excel.

JudoWill Over a year ago

I would also suggest using ExcelCol for finding the correct column headers. You can download it from the FileExchange here: mathworks.com/matlabcentral/fileexchange/… It makes dealing with Excel so much easier.

Ben Fossen Over a year ago

I have made a few things more clear about what I am looking for. I will check out ExcelCol

gnovice Over a year ago

@Ben: I've updated my answer to address your newest edit. Hopefully this helps out more.

Collectives™ on Stack Overflow

Writing strings into excel using Matlab?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related