Creating xlsx with multiple sheets

Question

Adnan Jayyousi 2022-6-28

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1749870-creating-xlsx-with-multiple-sheets

评论： Voss 2022-6-28

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');

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Voss 2022-6-28

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1749870-creating-xlsx-with-multiple-sheets#answer_995805

在 MATLAB Online 中打开

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).

2 个评论
显示无隐藏无

Adnan Jayyousi 2022-6-28

Thanks !

Voss 2022-6-28

You're welcome!

请先登录，再进行评论。

Answer 2

Jon 2022-6-28

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1749870-creating-xlsx-with-multiple-sheets#answer_995775

编辑：Jon 2022-6-28

在 MATLAB Online 中打开

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 个评论
显示 -1更早的评论隐藏 -1更早的评论

Adnan Jayyousi 2022-6-28

Thanks,

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

请先登录，再进行评论。

Creating xlsx with multiple sheets

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

2 个评论
显示无隐藏无

更多回答（1 个）

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

Creating xlsx with multiple sheets

0 个评论 显示 -2更早的评论隐藏 -2更早的评论

采纳的回答

2 个评论 显示 无隐藏 无

更多回答（1 个）

1 个评论 显示 -1更早的评论隐藏 -1更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

2 个评论
显示无隐藏无

1 个评论
显示 -1更早的评论隐藏 -1更早的评论