Creating xlsx with multiple sheets

Hello everyone,

I have the following code, that outputs a xlsx file, with two columns of data, one of these columns is date&time in an hourly format for full year.

I am trying to output the files, such as that the xlsx file will have multiple sheet, every sheet will hold one month's data.

the names of the sheets will be , for example :

"January2021", "Feb2021"..."June2022" - as defined in the t1:t2 ranges.

Thanks in advance.

%% Define date&time arrays :

t1 = datetime(2021,6,21,1,0,0);
t2 = datetime(2022,6,21,24,0,0);
dt = (t1:hours(1):t2)'

%holidyas date array

Holidays = [datetime(2022,3,27,0,0,0) datetime(2022,3,28,0,0,0) datetime(2022,4,2,0,0,0) datetime(2022,4,3,0,0,0) datetime(2022,4,14,0,0,0) datetime(2022,4,15,0,0,0) datetime(2022,5,16,0,0,0) datetime(2022,5,17,0,0,0) datetime(2021,9,6,0,0,0) datetime(2021,9,7,0,0,0) datetime(2021,9,15,0,0,0) datetime(2021,9,16,0,0,0) datetime(2021,9,20,0,0,0) datetime(2021,9,27,0,0,0)]
Holidays = Holidays'

DateNtime=zeros(numel(dt),2); %preallocating

%% All Seasons Logic :

%% Hours between 00:00 - 16:00 "Off Peak" - Days of week. (All Seasons)

for i=1:numel(dt)

    if hour(dt(i))>=0 & hour(dt(i))<=16 & ismember(day(dt(i),'dayofweek'),[1 2 3 4 5])  
        switch month(dt(i))
            case {12,1,2}  %% Season #1
                DateNtime(i,2)=0.2307;
            case {3,4,5,10,11}  %% Season #2
                DateNtime(i,2)=0.2243;
            case {6,7,8,9}  %% Season #3
                DateNtime(i,2)=0.2701;
end
end
end

%% On Peak - Days of week only (All Seasons)

for i=1:numel(dt)

    if hour(dt(i))>=17 & hour(dt(i))<=21 & ismember(day(dt(i),'dayofweek'),[1 2 3 4 5])  
        switch month(dt(i))
            case {12,1,2} %% Season #1
                DateNtime(i,2)=0.7170;
            case {3,4,5,10,11} %% Season #2
                DateNtime(i,2)=0.2578;
            case {6,7,8,9} %% Season #3
                DateNtime(i,2)=1.0789;
end
end
end

%% Season #1 Logic :


%% Hours between 00:00 - 16:00 "Off Peak" - Days of week. (Season#1)

for i=1:numel(dt)

    if hour(dt(i))>=0 & hour(dt(i))<=16 & ismember(day(dt(i),'dayofweek'),[6 7 ])  
        switch month(dt(i))
            case {12,1,2}  %% Season #1
                DateNtime(i,2)=0.2307;

end
end
end

%% Hours between 22:00 - 23:00 "Off Peak" - All Week . (Season#1 only)

for i=1:numel(dt)

    if hour(dt(i))>=22 & hour(dt(i))<=23 & ismember(day(dt(i),'dayofweek'),[1 2 3 4 5 6 7])  
        switch month(dt(i))
            case {12,1,2}  %% Season #1
                DateNtime(i,2)=0.2307;
end
end
end

%% Hour 23:00 "Off Peak" - Days of week. (Season#1)

for i=1:numel(dt)

    if hour(dt(i))== 23 & ismember(day(dt(i),'dayofweek'),[1 2 3 4 5])  
        switch month(dt(i))
            case {12,1,2}  %% Season #1
                DateNtime(i,2)=0.2307;

end
end
end

%% Weekends On Peak (Season #1)

for i=1:numel(dt)

if hour(dt(i))>=17 & hour(dt(i))<=21 & ismember(day(dt(i),'dayofweek'),[6 7])  

        switch month(dt(i))

            case {12,1,2}  %% Season #1
                DateNtime(i,2)=0.7170;

end
end
end


%% Holidays On Peak  (Seasons #1)

doy = day (Holidays(:,:),'dayofyear')
for i=1:numel(dt)

    if hour(dt(i))>=17 & hour(dt(i))<=21 & ismember(day(dt(i),'dayofyear'),doy)  
        switch month(dt(i))
            case {12,1,2}  %% Season #1
                DateNtime(i,2)=0.7170;

end
end
end

%% Holidays Off Peak 00:00 - 16:00  (Seasons #1)

doy = day (Holidays(:,:),'dayofyear')
for i=1:numel(dt)

    if hour(dt(i))>=00 & hour(dt(i))<=16 & ismember(day(dt(i),'dayofyear'),doy)  
        switch month(dt(i))
            case {12,1,2}  %% Season #1
                DateNtime(i,2)=0.7170;

end
end
end

%% Holidays Off Peak 22:00 - 23:00  (Seasons #1)

doy = day (Holidays(:,:),'dayofyear')
for i=1:numel(dt)

    if hour(dt(i))>=22 & hour(dt(i))<=23 & ismember(day(dt(i),'dayofyear'),doy)  
        switch month(dt(i))
            case {12,1,2}  %% Season #1
                DateNtime(i,2)=0.7170;

end
end
end



%% Hour 23:00 "Off Peak" - Days of week. (Season#2 & Season #3)

for i=1:numel(dt)

    if hour(dt(i))== 23 & ismember(day(dt(i),'dayofweek'),[1 2 3 4 5])  
        switch month(dt(i))
            case {3,4,5,10,11} %% Season #2
                DateNtime(i,2)=0.2243;
            case {6,7,8,9} %% Season #3
                DateNtime(i,2)=0.2701;
end
end
end



%% Weekends. (Season #2 & Season #3)

for i=1:numel(dt)

    if ismember(day(dt(i),'dayofweek'),[6 7])  
        switch month(dt(i))

            case {3,4,5,10,11} %% Season #2 
                DateNtime(i,2)=0.2243;
            case {6,7,8,9} %% Season #3
                DateNtime(i,2)=0.2701;
end
end
end




%% Hour 22:00 "On Peak" - Days of week. (Season#2 & Season #3)

for i=1:numel(dt)

    if hour(dt(i)) == 22 & ismember(day(dt(i),'dayofweek'),[1 2 3 4 5])  
        switch month(dt(i))
            case {3,4,5,10,11} %% Season #2
                DateNtime(i,2)=0.2578;
            case {6,7,8,9} %% Season #3
                DateNtime(i,2)=1.0789;
end
end
end

%% Holidays (Seasons #2 and #3)

doy = day (Holidays(:,:),'dayofyear')
for i=1:numel(dt)

    if ismember(day(dt(i),'dayofyear'),doy)  
        switch month(dt(i))

            case {3,4,5,10,11}  %% Season #2
                DateNtime(i,2)=0.2243;
            case {6,7,8,9}  %% Season #3
                DateNtime(i,2)=0.2701;
end
end
end



%% Creat Table to export :


A = dt(:,1);
B = DateNtime(:,2);
newTable = table(A,B);
writetable(newTable,'TIME_OF_USE.xlsx');

Answer 1

[Voss]

Here's one way, demonstrated with random data (random DateNtime(:,2)):

%% Define date&time arrays :
t1 = datetime(2021,6,21,1,0,0);
t2 = datetime(2022,6,21,24,0,0);
dt = (t1:hours(1):t2)';

DateNtime=zeros(numel(dt),2); %preallocating

% random data:
DateNtime(:,2) = rand(numel(dt),1);

%% Create Tables to export :
[months,~,jj] = unique([year(dt) month(dt)],'rows');
N = size(months,1);
sheet_names = datetime(months(:,1),months(:,2),ones(N,1),'Format','MMMMyyyy');
for ii = 1:N
    idx = jj == ii;
    A = dt(idx,1);
    B = DateNtime(idx,2);
    newTable = table(A,B);
    writetable(newTable,'TIME_OF_USE.xlsx','Sheet',char(sheet_names(ii)));
end

sheetnames('TIME_OF_USE.xlsx')

ans = 13×1 string array
    "June2021"
    "July2021"
    "August2021"
    "September2021"
    "October2021"
    "November2021"
    "December2021"
    "January2022"
    "February2022"
    "March2022"
    "April2022"
    "May2022"
    "June2022"

This uses logical indexing, which you can also use to simplify the rest of your code (setting DateNtime(:,2) based on seasons/hours/days of week/holidays).

[Adnan Jayyousi]

Thanks !

[Voss]

You're welcome!

Answer 2

[Jon]

Use the Sheets name value pair in writetable

For example:

writetable(newTable,'TIME_OF_USE.xlsx','Sheet','mySheetName');

You will probably have to generate the sheetname programatically, rather than hardcoding it as I show above.

[Adnan Jayyousi]

Thanks,

But how can I use the diffrent sheets with diffrent data ?