Read and write multiple excel files using ActiveX

6 次查看（过去 30 天）
KRUNAL
KRUNAL 2014-8-5
评论： Image Analyst 2014-8-8
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 个评论
显示 1更早的评论
Geoff Hayes
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).
KRUNAL
KRUNAL 2014-8-6
The above code is opening and closing excel each time functions like xlswrite and xlsfont runs in the loop (if you will see their code in matlab file exchange). This makes the code slow when used for files upto 20-30. So I want to use activex

请先登录，再进行评论。

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

回答（1 个）

Geoff Hayes
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 个评论
KRUNAL
KRUNAL 2014-8-8
ok..and where should I add the calculation part and how?
Image Analyst
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!

Translated by