How to save multiple tables in seperate sheets in Excel

Hello - I am running multiple files in MATLAB and would like to save the outputs from regionprops into one large Excel file. With my current code, which is below, it will only save as seperate .txt files. Help is appreciatied - thank you!

figure,imshow(BW);
stats = regionprops(BW,'MajorAxisLength');
t = struct2table(stats);
path = 'C:\Users\Test' ;
filename = [path, filesep, 'GFP',num2str(k)] ;
writetable(t,filename);
end

k stands for k=1:length(Files)

[Benjamin Drewry]

Victoria,

Just use the writetable function with the name-value pair 'Sheet', number.

i.e. writetable(t, 'fName.xlsx', 'Sheet', 4);

Thanks,

Ben

Answer 1

[Christopher Wallace]

Hi Victoria,

Have you looked into the documentation for the Matlab command 'xlswrite' ?

https://www.mathworks.com/help/matlab/ref/xlswrite.html

?

You should be able to do something along the lines of:

xlswrite(filename, data, 'Sheet1')

But since the xlswrite function only accepts two-dimensional numeric, character array, or string array, or, if each cell contains a single element, a cell array, you'll have to reformat your data before putting it in.

?

If you'd like to add a new sheet just change the name of the sheet in the xlswrite parameters. If it the sheet doesn't exist it will be created.

?

Best Regards,

Chris

[Victoria]

Great - I was able to save it to an excel file now. Thank you!

My last problem is that I still can't figure out how to do another sheet (or preferably, another column). I am running many pictures at a time, and the last picture overrides the previous excel file, so I'm just left with the data from the last image I run. Is there a way to automatically do this instead of manually changing the name. Again, inputting the data to another column would be nicer than another sheet.

Here is my code now:

      C = table2cell(t);
  xlFilename = 'C:\Users\test...';
  xlRange = strcat('C',int2str(k));
  xlswrite(xlFilename,C,'num2str(k)',xlRange);
  path = 'C:\Users\...' ;
  filename = [path, filesep, 'TxRed',num2str(k)] ;
end

[Victoria]

I know there is the 'append' command, but I'm not too sure on how to use that in my code.

[Victoria]

I have figured out how to write it in seperate sheets on the same Excel file. However, I can only export it to my currect directory. I'd still really like to know if I can do the same column, but especially figure out a way so that it can save to a specfic directory (like the path in my initial question). Thanks! Here is my current code:

t = struct2table(stats);
C = table2cell(t);
xlswrite('test.xlsx',C,int2str(k)) %Saved in current directory
end

[Christopher Wallace]

How about:

xlswrite([path, '\test.xlsx'], C, int2str(k))

[Paolo]

Use:

 xlswrite(fullfile(path,'test.xlsx'), C, int2str(k))

By the way Victoria, there is no need to convert the table to a cell when you can specify the sheet to write to when using writetable:

 writetable(fullfile(path,filename),'Sheet',k)

[Victoria]

Thank you both - both your codes have works for saving it in the correct path. The last thing - it is possible to save them in rows rather than new sheets? Thanks!

[Christopher Wallace]

Is 'MajorAxisLength' the only property you're wanting to record in the file? So you would have 'MajorAxisLength' in the top row followed by the data for each image in a new row below?

Consider taking the write function out of the for-loop and adding k to the stats variable

stats(k) = regionprops(BW,'MajorAxisLength');

then do the write once the for-loop is complete.

adding Paolo's answer in as well you would end up with something like:

      figure,imshow(BW);
      stats(k) = regionprops(BW,'MajorAxisLength');
      t = struct2table(stats);
end

path = 'C:\Users\Test';
writetable(t,fullfile(path,filename));

[Victoria]

I got this error when I tried that:

Unable to perform assignment because the indices on the left side are not compatible with the size of the right side.

And yes, Major Axis Length is the only one I'd like right now.

Thanks for the help

[Christopher Wallace]

Can you post the full code?

Answer 2

[Peter Perkins]

Victoria, I really recommend that you not use xlswrite. As Paolo suggests, writetable can definitely write to sheets in an Excel file.