how to use writetable for one sheet?

Question

arian hoseini 2023-11-27

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2052337-how-to-use-writetable-for-one-sheet

评论： arian hoseini 2023-11-28

i tried many things but still cant use writetable for one sheet...whatever i do it gives me last data only....i need all ten data in one single sheet and i need five first row to be empty i excel...

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 = 'D:\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

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

请先登录，再进行评论。

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

Answer 1

Chunru 2023-11-27

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2052337-how-to-use-writetable-for-one-sheet#answer_1360257

在 MATLAB Online 中打开

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 = '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

Warning: Added specified worksheet.

% 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

% Here is the .xlsx file

dir

. .. generate_sigma.xlsx

% Now read the .xlsx file one sheet by one sheet

for i=1:10

t{i} = readtable(excelFilePath, "sheet", i);

end

t

t = 1×10 cell array

{10×2 table} {10×2 table} {10×2 table} {10×2 table} {10×2 table} {10×2 table} {10×2 table} {10×2 table} {10×2 table} {10×2 table}

t{4}

ans = 10×2 table

a b ______ ______ 0.0004 0.04 0.0012 0.04 0.0024 0.04 0.04 0.036 0.12 0.032 0.2 0.028 2 0.004 2.8 0.002 3.2 0.0004 4 4e-05

5 个评论
显示 3更早的评论隐藏 3更早的评论

Chunru 2023-11-27

编辑：Chunru 2023-11-27

在 MATLAB Online 中打开

If you want to do that, you could specify the "Range" on writing table . You need to calculate where the starting corner of each table and save it. Could you explain why you want to do that?

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 = '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, 'Range', "A"+i*20);

% 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

dir

. .. generate_sigma.xlsx

% Now read the .xlsx file one sheet by one sheet

for i=1:10

t{i} = readtable(excelFilePath, "Range", "A"+i*20+":B"+(i*20+10));

end

t

t = 1×10 cell array

{10×2 table} {10×2 table} {10×2 table} {10×2 table} {10×2 table} {10×2 table} {10×2 table} {10×2 table} {10×2 table} {10×2 table}

t{4}

ans = 10×2 table

a b ______ ______ 0.0004 0.04 0.0012 0.04 0.0024 0.04 0.04 0.036 0.12 0.032 0.2 0.028 2 0.004 2.8 0.002 3.2 0.0004 4 4e-05

Chunru 2023-11-28

在 MATLAB Online 中打开

See below for writing the data into the same sheet (by columns). You need to make the column names unique in this case.

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 = 'generate_sigma.xlsx';
% Generate and write data to separate sheets
Tm =[];
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 = [Tm table(outputData(:,1,i), outputData(:,2,i), 'VariableNames', ["a"+i  "b"+i])];
    
    % Write the table to a separate sheet in the Excel file
    
end
Tm
Tm = 10×20 table
      a1        b1        a2        b2        a3        b3        a4        b4        a5        b5        a6        b6        a7        b7        a8        b8        a9        b9       a10      b10  
    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    _____    ______

    0.0001      0.01    0.0002      0.02    0.0003      0.03    0.0004      0.04    0.0005      0.05    0.0006      0.06    0.0007      0.07    0.0008      0.08    0.0009      0.09    0.001       0.1
    0.0003      0.01    0.0006      0.02    0.0009      0.03    0.0012      0.04    0.0015      0.05    0.0018      0.06    0.0021      0.07    0.0024      0.08    0.0027      0.09    0.003       0.1
    0.0006      0.01    0.0012      0.02    0.0018      0.03    0.0024      0.04     0.003      0.05    0.0036      0.06    0.0042      0.07    0.0048      0.08    0.0054      0.09    0.006       0.1
      0.01     0.009      0.02     0.018      0.03     0.027      0.04     0.036      0.05     0.045      0.06     0.054      0.07     0.063      0.08     0.072      0.09     0.081      0.1      0.09
      0.03     0.008      0.06     0.016      0.09     0.024      0.12     0.032      0.15      0.04      0.18     0.048      0.21     0.056      0.24     0.064      0.27     0.072      0.3      0.08
      0.05     0.007       0.1     0.014      0.15     0.021       0.2     0.028      0.25     0.035       0.3     0.042      0.35     0.049       0.4     0.056      0.45     0.063      0.5      0.07
       0.5     0.001         1     0.002       1.5     0.003         2     0.004       2.5     0.005         3     0.006       3.5     0.007         4     0.008       4.5     0.009        5      0.01
       0.7    0.0005       1.4     0.001       2.1    0.0015       2.8     0.002       3.5    0.0025       4.2     0.003       4.9    0.0035       5.6     0.004       6.3    0.0045        7     0.005
       0.8    0.0001       1.6    0.0002       2.4    0.0003       3.2    0.0004         4    0.0005       4.8    0.0006       5.6    0.0007       6.4    0.0008       7.2    0.0009        8     0.001
         1     1e-05         2     2e-05         3     3e-05         4     4e-05         5     5e-05         6     6e-05         7     7e-05         8     8e-05         9     9e-05       10    0.0001
writetable(Tm, excelFilePath);
t = readtable(excelFilePath)
t = 10×20 table
      a1        b1        a2        b2        a3        b3        a4        b4        a5        b5        a6        b6        a7        b7        a8        b8        a9        b9       a10      b10  
    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    ______    _____    ______

    0.0001      0.01    0.0002      0.02    0.0003      0.03    0.0004      0.04    0.0005      0.05    0.0006      0.06    0.0007      0.07    0.0008      0.08    0.0009      0.09    0.001       0.1
    0.0003      0.01    0.0006      0.02    0.0009      0.03    0.0012      0.04    0.0015      0.05    0.0018      0.06    0.0021      0.07    0.0024      0.08    0.0027      0.09    0.003       0.1
    0.0006      0.01    0.0012      0.02    0.0018      0.03    0.0024      0.04     0.003      0.05    0.0036      0.06    0.0042      0.07    0.0048      0.08    0.0054      0.09    0.006       0.1
      0.01     0.009      0.02     0.018      0.03     0.027      0.04     0.036      0.05     0.045      0.06     0.054      0.07     0.063      0.08     0.072      0.09     0.081      0.1      0.09
      0.03     0.008      0.06     0.016      0.09     0.024      0.12     0.032      0.15      0.04      0.18     0.048      0.21     0.056      0.24     0.064      0.27     0.072      0.3      0.08
      0.05     0.007       0.1     0.014      0.15     0.021       0.2     0.028      0.25     0.035       0.3     0.042      0.35     0.049       0.4     0.056      0.45     0.063      0.5      0.07
       0.5     0.001         1     0.002       1.5     0.003         2     0.004       2.5     0.005         3     0.006       3.5     0.007         4     0.008       4.5     0.009        5      0.01
       0.7    0.0005       1.4     0.001       2.1    0.0015       2.8     0.002       3.5    0.0025       4.2     0.003       4.9    0.0035       5.6     0.004       6.3    0.0045        7     0.005
       0.8    0.0001       1.6    0.0002       2.4    0.0003       3.2    0.0004         4    0.0005       4.8    0.0006       5.6    0.0007       6.4    0.0008       7.2    0.0009        8     0.001
         1     1e-05         2     2e-05         3     3e-05         4     4e-05         5     5e-05         6     6e-05         7     7e-05         8     8e-05         9     9e-05       10    0.0001

arian hoseini 2023-11-28

thank u sir

请先登录，再进行评论。

how to use writetable for one sheet?

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

采纳的回答

5 个评论
显示 3更早的评论隐藏 3更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

how to use writetable for one sheet?

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

采纳的回答

5 个评论 显示 3更早的评论隐藏 3更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

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

5 个评论
显示 3更早的评论隐藏 3更早的评论