MATLAB: How to extract and then write data in certain order from other excel files to excel file

dataexcelxlswrite

My excel workbook inserts seven worksheets. A,B and C columns in every worksheets are same. Next five columns are respectively values of longitude 25, 30, 35, 40 and 45 related to sheets(Lat30, Lat32.5 and so on). My data is normally 99888×8 table. Small data example orders as Data.xlsx file and I would like to do as Test.xlsx file.
A column: Year (from 1998 to 2017)
B column: Day of the year (from 1 to 365 or 366)
C column: Hour
D,E,F,G & H columns: TEC values
I'd like to write like test.xlsx file for every hour but I have no idea how I write like Test.xlsx file for every hour?

Best Answer

[~, sheets] = xlsfinfo('Data.xlsx');  %get list of sheets
lattables = cell(size(sheets));      %preallocate cell array of table
for sh = 1:numel(sheets)
    latitude = sscanf(strrep(sheets{sh}, '_', '.'), 'Lat%f');  %extract latitude from sheet name (replace _ by . so that Lat is followed by an actual number
    lattables{sh} = readtable('Data.xlsx', 'Sheet', sheets{sh});   %read sheet
    lattables{sh}.Latitude = repmat(latitude, height(lattables{sh}), 1); %append latitude column (all identical for the current table)    
end
fulltable = vertcat(lattables{:});  %once all are loaded, concatenate into a single table
fulltable = stack(fulltable, 4:8, 'ConstantVariables', [1:3, 9], 'NewDataVariableName', 'TEC', 'IndexVariableName', 'Longitude');
fulltable.Longitude = sscanf(strrep(strjoin(cellstr(fulltable.Longitude)), '_', '.'), 'Long%f ')
Related Question