Read and write multiple excel files using ActiveX
7 Ansichten (letzte 30 Tage)
Ältere Kommentare anzeigen
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 Kommentare
Geoff Hayes
am 6 Aug. 2014
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).
Antworten (1)
Geoff Hayes
am 7 Aug. 2014
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 Kommentare
Image Analyst
am 8 Aug. 2014
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.
Siehe auch
Kategorien
Mehr zu Spreadsheets finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!