how to export data with writetable?

i wanna export whole output in 1 sheet and i wanna know how to export it in separated sheets...

clear all
clc

inputData = [0.001 0.1;
            0.003 0.1;
            0.006 0.1;
            0.1 0.09;
            0.3 0.08;
            0.5 0.07;
            5 0.01;
            7 0.005;
            8 0.001;
            10 0.0001];

numSets = 10; % Number of sets to generate

% Initialize an empty matrix to store the generated data
outputData = zeros(size(inputData, 1), 2, numSets);


for i = 1:numSets
    % Generate similar data with increasing amplitude
    scalingFactor = i * 0.1;
    generatedData = scalingFactor * inputData(:, 1);
    sigma = scalingFactor * inputData(:, 2);

    % Store the generated data in the output matrix
    outputData(:,:,i) = [generatedData,sigma];
        Tm=table(outputData(:,1,i),outputData(:,2,i),'VariableNames',{'a','b',});
    disp(Tm);
    writetable(Tm, 'C:\Users\arian\Desktop/generate sigma.xls' , 'Sheet', 1);
    loglog(outputData(:,1,i), outputData(:,2,i), 'DisplayName', sprintf('Generated Data Set %d', i));

end


% Display the generated data for each set
% figure
hold on
for i = 1:numSets

    disp(['Generated Data Set ', num2str(i)]);
    %disp(outputData(:,:,i));
    %figure(i)

    plot(outputData(:,1,i), outputData(:,2,i), 'DisplayName',["Generated Data Set "+i])
end
hold off
grid
legend('Location','best')

Answer 1

[Sai Teja G]

Hi Arian,

I understand that you want to export it into serperate sheets.

To export the output data to a single Excel sheet, you would typically create a table for each data set and then write each table to the Excel file in a loop, as you are doing. However, you are currently overwriting the same sheet with each iteration of the loop, which is why only the last data set appears in the Excel file.

To write all data sets to the same sheet, you would need to append each table side by side or one after another within the same sheet. To write data sets to separate sheets, you need to change the 'Sheet' parameter in the `writetable` function to write to a different sheet for each data set.

Here's how you can modify your code to write each data set to a separate sheet:

clear all
clc
inputData = [0.001 0.1;
    0.003 0.1;
    0.006 0.1;
    0.1 0.09;
    0.3 0.08;
    0.5 0.07;
    5 0.01;
    7 0.005;
    8 0.001;
    10 0.0001];
numSets = 10; % Number of sets to generate

% Initialize an empty matrix to store the generated data
outputData = zeros(size(inputData, 1), 2, numSets);

% Path to the Excel file
excelFilePath = 'C:\Users\arian\Desktop\generate_sigma.xlsx';

% Generate and write data to separate sheets
for i = 1:numSets
    % Generate similar data with increasing amplitude
    scalingFactor = i * 0.1;
    generatedData = scalingFactor * inputData(:, 1);
    sigma = scalingFactor * inputData(:, 2);
    
    % Store the generated data in the output matrix
    outputData(:,:,i) = [generatedData, sigma];
    
    % Create a table for the generated data
    Tm = table(outputData(:,1,i), outputData(:,2,i), 'VariableNames', {'a', 'b'});
    
    % Write the table to a separate sheet in the Excel file
    writetable(Tm, excelFilePath, 'Sheet', i);
    
    % Plot the data
    loglog(outputData(:,1,i), outputData(:,2,i), 'DisplayName', sprintf('Generated Data Set %d', i));
    hold on; % Keep the plot hold on for the next data set
end

% Adjust plot settings
grid on;
legend('Location', 'best');
hold off; % Release the plot hold

% Display the generated data for each set
for i = 1:numSets
    disp(['Generated Data Set ', num2str(i)]);
    disp(outputData(:,:,i));
end

In the above code, I have changed the `writetable` function to write each data set to a separate sheet by using the loop index `i` as the sheet number. Additionally, I've modified the file path to use a backward slash `\` (which is the escape character in MATLAB strings) or you can use a forward slash `/` to avoid escaping characters.

Please make sure that the file path is correct and that you have write permissions to the specified location. Also, the file extension is changed to `.xlsx`, as `.xls` is an older Excel format and may not support multiple sheets as well as the newer `.xlsx` format.

Hope it helps!

[Mathieu NOE]

fyi , `.xls` is an older Excel format that supports multiple sheets

[arian hoseini]

sorry...i didnt understand the same sheet...i mean how ?can u write that too so i can see the difference?

[Walter Roberson]

    writetable(Tm, excelFilePath);

If you were to use the above command, writetable would automatically write to the first sheet that it finds. If the file did not previously exist, it might create several sheets named things like sheet1 and sheet2 but only the first, sheet1 would be written to. Creation of the extra (unused) sheets when a file is first created is not certain and might depend upon whether writetable has the UseExcel option active.

   writetable(Tm, excelFilePath, 'Sheet', 3);    

If you were to use the above command, writetable would automatically write to the third sheet it finds. If the file did not previously exist, it could create several sheets named things like sheet1 and sheet2 but only the third sheet would be written to in this example. If the sheet number requested to write to is more than the number of sheets that exist in the file, it will create a new sheet named sheet followed by the sheet number. Creation of the extra (unused) sheets when a file is first created is not certain and might depend upon whether writetable has the UseExcel option active.

  writetable(Tm, excelFilePath, 'Sheet', 'disco');

If you were to use the above command, writetable would look for a sheet named disco and write to it if it finds it; if it did not exist then it will create the sheet and write to it. If the file it could create several sheets named things like sheet1 and sheet2 as well. Creation of the extra (unused) sheets when a file is first created is not certain and might depend upon whether writetable has the UseExcel option active.

The sheet names or sheet numbers that you pass in to writetable() do not need to be constants. You can validly use a loop control variable the way that @Sai Teja G showed, such as

  for i = 1 : 5
      writetable(Tm, excelFilePath, 'Sheet', i);
  end

to write to the first 5 sheets (creating sheets if necessary)

[arian hoseini]

thank u sir