代码之家  ›  专栏  ›  技术社区  ›  mendokusai

利用ActiveX从Excel导入Matlab

  •  1
  • mendokusai  · 技术社区  · 7 年前

    scriptName = mfilename('fullpath');
    [currentpath, filename, fileextension]= fileparts(scriptName);    
    xlsnames = dir(fullfile(currentpath,'*.xls'));
    xlscount = length(xlsnames);
    xlsimportdata = zeros(7,6,xlscount);
    
    for k = 1:xlscount
    xlsimport = xlsread(xlsnames(k).name,'D31:I37');
    xlsimportdata(:,1:size(xlsimport,2),k) = xlsimport;
    end
    

    我每周有近10k个文件需要处理,在我当前的工作站上处理每个文件大约2秒,大约需要5个小时。

    我已经读到ActiveX可以用于此目的,但这远远超出了我目前的编程技能,并且无法在其他地方找到解决方案。如果您能提供帮助,我们将不胜感激。

    如果使用ActiveX(或其他建议的方法)执行起来很简单,我也会对单元格D5和G3上的数据感兴趣,我目前正在从“xlsname”(k,1)中获取这些数据。名称和xlsname(k,1)。日期'

    编辑:更新以反映解决方案

    % Get path to .m script
    scriptName = mfilename('fullpath');
    [currentpath, filename, fileextension]= fileparts(scriptName);
    
    % Generate list of .xls file data
    xlsnames = dir(fullfile(currentpath,'*.xls'));
    xlscount = length(xlsnames);
    SampleInfo = cell(xlscount,2);
    xlsimportdata = cell(7,6,xlscount);
    
    % Define xls data ranges to import
    SampleID = 'G3';
    SampleRuntime = 'D5';
    data_range = 'D31:I37';
    
    % Initiate progression bar
    h = waitbar(0,'Initiating import...');
    
    % Start actxserver
    exl = actxserver('excel.application');
    exlWkbk = exl.Workbooks;
    
    for k = 1:xlscount
    
        % Restart actxserver every 100 loops due limited system memory
        if mod (k,100) == 0
            exl.Quit
            exl = actxserver('excel.application');
            exlWkbk = exl.Workbooks;
        end
    
        exlFile   = exlWkbk.Open([dname filesep xlsnames(k).name]);
        exlSheet1 = exlFile.Sheets.Item('Page 0');
    
        rngObj1    = exlSheet1.Range(SampleID);
        xlsimport_ID = rngObj1.Value;
    
        rngObj2    = exlSheet1.Range(SampleRuntime);
        xlsimport_Runtime = rngObj2.Value;
    
        rngObj3    = exlSheet1.Range(data_range);
        xlsimport_data = rngObj3.Value;
    
        SampleInfo(k,1) = {xlsimport_ID};
        SampleInfo(k,2) = {xlsimport_Runtime};
        xlsimportdata(:,:,k) = xlsimport_data;
    
        % Progression bar updater
        progress = round((k / xlscount) * 100);
    
        importtext = sprintf('Importing %d of %d', k, xlscount);
        waitbar(progress/100,h,sprintf(importtext));
        disp(['Import progress: ' num2str(k) '/' num2str(xlscount)]);
    
    end
    %close actxserver
    exl.Quit
    
    % Close progression bar
    close(h)
    
    1 回复  |  直到 7 年前
        1
  •  1
  •   Aero Engy    7 年前

    试试这个。我无论如何都不是ActiveX Excel大师。然而,这对我的少量测试XLS文件(3)有效。我从来没有 close 这个 exlWkbk

    >> timeit(@getSomeXLS)
    ans =
        1.8641
    
    >> timeit(@getSomeXLS_old)
    ans =
        4.6192
    

    请留下一些反馈,如果这对大量的Excel工作表的工作,因为我很好奇它如何进行。

    function xlsimportdata = getSomeXLS()
    
    scriptName = mfilename('fullpath');
    [currentpath, filename, fileextension]= fileparts(scriptName);
    xlsnames = dir(fullfile(currentpath,'*.xls'));
    xlscount = length(xlsnames);
    xlsimportdata = zeros(7,6,xlscount);
    exl = actxserver('excel.application');
    
    exlWkbk = exl.Workbooks;
    dat_range = 'D31:I37';
    
    for k = 1:xlscount
        exlFile   = exlWkbk.Open([currentpath filesep xlsnames(k).name]);    
        exlSheet1 = exlFile.Sheets.Item('Sheet1'); %Whatever your sheet is called.
        rngObj    = exlSheet1.Range(dat_range);
        xlsimport = cell2mat(rngObj.Value);
        xlsimportdata(:,:,k) = xlsimport;
    end
    exl.Quit