How to change the column name from bottom to top in xlswrite file and make the loop to calculate faster

I want to write the results obtained in Matlab into excel and here is the sample of hat i did. I did it but it gave me the results with the name of the column on the bottom of the cell. secondly i noticed the calculation took so long to finished. here is the code. Thank you in advance

for x =1:1:100
    y = 1+x
    w = 4+x
    s = x

filename = 'C:\Users\Name\Desktop\Performance.xlsx'; % the name of the file
Data_calculation= {y; 'y'};
sheet = 1;
xlRange =  ['A' ,num2str(x), '']; 
xlswrite(filename,Data_calculation,sheet,xlRange);
%-----------------------------------------------------------------% 1st

Data_calculation= { w; 'w'};
sheet = 1;
xlRange =  ['B' ,num2str(x), ''];
xlswrite(filename,Data_calculation,sheet,xlRange);
% %-----------------------------------------------------------------------2nd

Data_calculation= { s; 's'};
sheet = 1;
xlRange =  ['C' ,num2str(x), ''];
xlswrite(filename,Data_calculation,sheet,xlRange);
end

[Cedric]

Accessing Excel files takes a lot of time, because they have to be opened and closed (which is as lengthy as when you do it manually).

When we need to store an array in an Excel file, we create the array completely first, and only then we store it in an Excel file, with a single call to XLSWRITE.

The reason your approach is slow is that you access the Excel file three times on every iteration of your loop, hence performing 3000 open/edit/close instead of 1 (or max 3).

Try building relevant arrays in the loop, but extracting these calls to XLSWRITE from the loop. You can even concatenate the arrays and headers and perform a single call to XLSWRITE instead of 3.

Answer 1

[Sarah Mohamed]

The column names are written to the bottom of the spreadsheet as a result of two factors:

1. The column label, 'y', is being placed in the row below the data point:

Data_calculation = {y; 'y'}

2. The column label is written at each iteration of the loop, and...

xlRange = ['A', num2str(x), ''];

...here the starting location for “xlswrite” is specified as A1,A2,… etc. as the loop iterates. Because you are writing two rows – the data point, and the column label – the column label is being overwritten at each iteration except the last.

In your original code, this could be resolved by removing the column labels (‘y’, ‘w’, and ‘s’) from Data_calculation, and writing to the spreadsheet once before the for-loop with a row containing the column labels.

However, as Cedric commented, using xlswrite in this manner is quite inefficient. You can achieve the same result, using “xlswrite” only once, by creating a cell array with the desired data before writing to the file:

x = 1:10;
y = 1+x;
w = 4+x;
s = x;

performance = [{'y' 'w' 's'} ; num2cell([y' w' s'])];
filename = 'Performance2.xlsx';
xlswrite(filename,performance);

Where possible, it is usually a good bet to avoid for-loops in favor of vectorization. You can find more examples of this at the following page:

https://www.mathworks.com/help/matlab/matlab_prog/vectorization.html