Adding multiple excel sheets to one excel workbook

I have an excel workbook that does some data processing and calculations on data currently have to manually add all the data to the work book.

I would like to be able to auto populate the excel work book with the data that i need for the calculations. I currently have 3 excel files with the data i need (lets call them results1, reults 2 and results 3). I have to add this data into the one excel workbook (lets call it data_processing), I want to add each results data into its own specific sheet in the work book. So i have muliptle sheets in the data_proessing workbook i want 3 sheets to be the results and then the other sheets i have already made and they take the data from the reults sheets and do some calculations.

If i put all the excel files into one locatin is there a way to get matlab to autopopulate the excel workbook with the data and in the specific sheet i want it to go to?

Thanks!!

Answer 1

[Walter Roberson]

Sure, you can writetable() or writematrix() or writecell() specifying 'Sheet' .

The hardest part would probably be figuring out what the appropriate sheet name would be for a given file.

inputdir = 'appropriate directory name';
outputfile = 'appropriate file name';   %not inside inputdir

dinfo = dir( fullfile(inputdir, '*.xlsx') );
filenames = fullfile( {dinfo.folder}, {dinfo.name} );

for K = 1 : length(filenames)
    thisfile = filenames{K};
    [~, basename, ~] = fileparts(thisfile);
    sheetname = ['sheet_for_', basename];   %adjust as appropriate
    thiscontent = readcell(thisfile);
    writecell(thiscontent, outputfile, 'Sheet', sheetname);
end

[Courtney Rich]

Is there a away that instead of doing a loop i just specifically call out each of the file names that i want it to read and then what the sheet name is cale dthat i want it to put the data? Since its only 3 files and i need them in specific spots.

Thanks!

[Walter Roberson]

filenames = {'results1.xlsx', 'reults 2.xlsx', 'results 3.xlsx'};
sheetnames = {'StarTrek', 'StarWars', 'DancingWithTheStars'};

for K = 1 : length(filenames)
    thisfile = filenames{K};
    sheetname = sheetnames{K};
    thiscontent = readcell(thisfile);
    writecell(thiscontent, outputfile, 'Sheet', sheetname);
end