Read and write multiple excel files using ActiveX
7 ビュー (過去 30 日間)
古いコメントを表示
I have 2 folders each containing 4 excel files. I need to read data from 1st excel file from each folder and write those data into the third excel file.Then I need to do arithmetic calculation between those two copied data and write result in 3rd excel file stored in folder 3. This process I want to do for all the files in the two folders. Is it possible using ActiveX server?
3 件のコメント
Geoff Hayes
2014 年 8 月 6 日
The link I provided in my comment should get you started. I have to ask - why do you wish to use ActiveX server over the above code?
I could be mistaken, but (for example) lines 168-171 of xlswrite (R2014a) are
%---------------------------------------------------------------
% Attempt to start Excel as ActiveX server.
try
Excel = actxserver('Excel.Application');
% etc.
So it is attempting to start Excel as an ActiveX server. Why do you want duplicate what this function is doing for you already? You will see a similar line of code in xlsread and in xlsfont (if this latter is from the MATLAB File Exchange).
回答 (1 件)
Geoff Hayes
2014 年 8 月 7 日
Krunal - this was taken from the link I already provided and so will get you started
try
numSheets = 5;
% first open an excelActXSrvr Server
excelActXSrvr = actxserver('Excel.Application');
% set as visible
set(excelActXSrvr, 'Visible', 1);
% insert a new workbook
excelWorkbooks = excelActXSrvr.Workbooks;
excelWorkbook = invoke(excelWorkbooks, 'Add');
% get the sheets for this workbook
excelSheets = excelActXSrvr.ActiveWorkBook.Sheets;
% ensure that we have enough sheets
if excelSheets.Count < numSheets
sheetLast = get(excelSheets, 'Item', excelSheets.Count);
invoke(sheetLast, 'Activate');
invoke(excelSheets,'Add',[],sheetLast,numSheets-excelSheets.Count);
end
% for each sheet do
for k=1:numSheets
% make the kth sheet active
sheetk = get(excelSheets, 'Item', k);
invoke(sheetk, 'Activate');
% write the header data to the A1:D1 fields
data = {'BHS_P_SE' 'BHS_S_SE' 'BHS_P_LB' 'BHS_S_LB'};
activeSheetRange = get(sheetk, 'Range', 'A1:D1');
set(activeSheetRange, 'Value', data);
end
% now save the workbook
invoke(excelWorkbook, 'SaveAs', 'myfile.xlsx');
% quit excelActXSrvr
invoke(excelActXSrvr, 'Quit');
% end process
delete(excelActXSrvr);
catch exception
fprintf('Error: %s:%s\n',exception.identifier,exception.message);
end
It updates five worksheets with the specified column headers for columns A1 through D1. Using the above as a template, you should be able to complete the updates to your code and minimize the number of xlswrites. Once that has been completed, then I suggest you do something similar for the xlsread and xlsfont.
2 件のコメント
Image Analyst
2014 年 8 月 8 日
I usually do all my calculations early in the code. I save everything into arrays, and then once all the functions have been called, computations made, and arrays have their values, then I do all my Excel writing stuff after all that.
参考
カテゴリ
Help Center および File Exchange で Spreadsheets についてさらに検索
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!