How write multiple data output in excel sheet using xlswrite command

21 次查看（过去 30 天）
Amit Christian
Amit Christian 2012-11-13
评论： Pratik Nikam 2020-8-19
Hi All,
I want to know how can we write multiple data strings in the same excel sheet for a design simulation. As We know that whenever we specify xlswrite command it starts writing from cell A1 of the excel sheet.
Now, my problem is quite specific. I am carrying out a design calculation for a treatment works. At the end of the design calculation I am storing the variables in the excelsheet using xlswrite command. Now for a set of values of particular variable A , I want to do all the design calculations and then store them in excel sheet in different rows. How can we do it. I tried to run for loop but it always stores the data in the first row of the excelsheet so only the last set of data is available through xlswrite statement.
Program syntax is something like that
a=1 ;
b= equation1 c=equation2 d=equatio3
then finally I use xls write to write all these variables in an excelsheet.
Now for a range of values for variable a, I want to do the work and store them in the same excel sheet row wise. How can one do it . I would appreciate if someone comes up with a solution.
Regards

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

采纳的回答

Mark Whirdy
Mark Whirdy 2012-11-13
编辑：Mark Whirdy 2012-11-13
I'm not sure I udnerstand the problem either as it seems that xlswrite should provide for this functionality explicity? ([SUCCESS,MESSAGE]=XLSWRITE(FILE,ARRAY,RANGE))
Regardless, I'd avoid using xlsrwite at all (as it re-instantiates Excel with each call) and manipulate the properties (exposed by the actxserver object) of the spreadsheet directly. Code below should guide you.
try
xlApp = actxGetRunningServer('Excel.Application'); % capture existing excel application
catch ME %#ok
xlApp = actxserver('Excel.Application'); % instantiate new excel application
end
xlApp.Visible = 1;
xlWorkbook = xlApp.workbooks.Open(fullfile(xlFilePath,xlFileName),0,true);
xlSheets = xlWorkbook.Sheets;
xlSheetNamesArray = cell(xlSheets.Count,1);
for i = 1:xlSheets.Count
xlSheetNamesArray{i} = xlSheets.Item(i).Name; % sheet-order is not guaranteed so must build array
end
[~,idx] = ismember('Price',xlSheetNamesArray);
xlSheets.Item(idx).Activate
xlActiveSheet = xlWorkbook.ActiveSheet;
xlCurrRange = xlActiveSheet.Range('C4:C33');
priceVector = xlCurrRange.Value2;
priceVector = cell2mat(priceVector);
[~,idx] = ismember('Portfolios',xlSheetNamesArray);
xlSheets.Item(idx).Activate
xlActiveSheet = xlWorkbook.ActiveSheet;
xlCurrRange = xlActiveSheet.Range('B4:B22');
isinVector = xlCurrRange.Value2;
xlCurrRange = xlActiveSheet.Range('C3:G3');
ptfNumVector = cell2mat(xlCurrRange.Value2)';
xlCurrRange = xlActiveSheet.Range('C4:G22');
dataMatrix = xlCurrRange.Value2;
isnanMatrixMask = strcmp(dataMatrix,'ActiveX VT_ERROR: '); % handle missing data - assume as no position
dataMatrix(isnanMatrixMask) = {0};
dataMatrix = cell2mat(dataMatrix);

更多回答（5 个）

Ilham Hardy
Ilham Hardy 2012-11-13
doc xlswrite
Amit Christian
Amit Christian 2012-11-13
Hi All
I think the answers might be partly correct.
I have some thing like below:
a=5 b=4 c=10 d= a*b; e= b*c; f= d*e;
xlswrite('aaa.xls, [a,b,c,d,e]);
so when you run the above program , it will calculate the values of d, e and f from the values of a , b and c and equations and will write it to aaa.xls file starting with A1 cell.
Now I want to run this calculations for a= 5 to 10 and each time I want to store the resultant values of a,b,c,d, and e in a single sheet in separate rows. Problem is I write for loop for variable 'a' and when it comes to xlswrite , it always writes from the A1 cell thus the values will be overwritten and output will be only the last array containing value of a=10 and corresponding variables. Rather i want to keep the old values and want to write them in new raw each time. Hope you understand the problem very well now.
Regards
  1 个评论
José-Luis
José-Luis 2012-11-13
编辑：José-Luis 2012-11-13
Have you actually read the documentation?
xlswrite(filename,A,sheet,range)
That does what you ask for. Or am I missing something? Also Mark gave a good answer to your question that avoids the overhead of repeated calls to xlswrite() that have the potential to slow down your code considerably.

请先登录，再进行评论。

Amit Christian
Amit Christian 2012-11-14
Hi Pedro,
Is that possible to get your e mail address? I will send you the file. Your answer seems to me quite nearing my requirement but I dont know how to transfer it to my requirement.
mohammad tahmasebi
mohammad tahmasebi 2013-2-25
Hi Amit. i have I have a similar problem.If you find a answer Please tell me. Thank you very much
vijay
vijay 2013-6-17
Hi, this is what is needed
a=5 b=4 c=10 d= a*b; e= b*c; f= d*e;
offset =1;
for a_iter 1:5
a = a_iter; b= 4*a_iter;c = 10*a_iter; d= a*b; e= b*c; f= d*e;
xlswrite('filename.xls', [a b c d e], 1, sprintf('A%d',offset));
offset = offset + 1;
end
It will print in the A1 row for first time. and then go to next row by this sprintf('A%d',offset);
  4 个评论
显示 2更早的评论
KRUNAL
KRUNAL 2014-8-5
I have created a new post. I hope you can now answer me for it. The link for that questions is :
http://www.mathworks.com/matlabcentral/answers/146375-read-and-write-multiple-excel-files-using-activex
Looking forward for your reply and suggestions on it
Pratik Nikam
Pratik Nikam 2020-8-19
I wish to run the program 3 times, and each time I want to save data in a same excel sheet but to the new row. Will it be possible to do?
Thanks.
Pratik N.

请先登录，再进行评论。

类别

在 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