Creating xlsx with multiple sheets

3 views (last 30 days)
Adnan Jayyousi
Adnan Jayyousi on 28 Jun 2022
Commented: Voss on 28 Jun 2022
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');

Accepted Answer

Voss
Voss on 28 Jun 2022
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).

More Answers (1)

Jon
Jon on 28 Jun 2022
Edited: Jon on 28 Jun 2022
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.
  1 Comment
Adnan Jayyousi
Adnan Jayyousi on 28 Jun 2022
Thanks,
But how can I use the diffrent sheets with diffrent data ?

Sign in to comment.

Tags

Products


Release

R2021b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by