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

2 次查看（过去 30 天）
Gali Musa
Gali Musa 2017-9-10
编辑： Sarah Mohamed 2017-9-13
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
  1 个评论
Cedric
Cedric 2017-9-10
编辑：Cedric 2017-9-10
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.

请先登录，再进行评论。

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

采纳的回答

Sarah Mohamed
Sarah Mohamed 2017-9-13
编辑：Sarah Mohamed 2017-9-13
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

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by