How do I select several rows in Excel?

조회 수: 7(최근 30일)
Bobby
Bobby 2022년 1월 4일
댓글: Bobby 2022년 1월 10일
I am trying to find the quickest way to color thousands of rows in an Excel spreadsheet. My current method involves creating a cell array with strings, limited by the number of characters, of all the Excel cells that need to be colored, and then applying the appropriate colors at the end with several loops. If there is a way to use Union within Matlab, or to select thousands of non-adjacent rows within Excel, so that I can apply colors in one go without a loop, I'd greatly appreciate any suggestions!
Summary of current code:
xls=actxserver('Excel.Application');
xlsWB=xls.Workbooks.Open(FULL_XLS_FILE);
xlsWS=xlsWB.Sheets.Item(1);
rangeidx=cell(1000,10);
rangeidx(:)={''};
j=ones(1,10);
strlimit=240; %max for Range input is apparently 255 char, assume cells reach 6 digits
% This is one section of the loop for one coloring scheme, which is one
% string segmented into 255 char limited chunks.
for i=2:size(FINAL,1) %cycling through every row for color index
if coloridx{i} == 1 %for one coloring scheme
if coloridx(i-1) == 1 %for adjacent rows
rangeidx{j(1),1}= rangeidx{j(1),1}(1:end-(numel(num2str(i))+1)); % removing last row number and comma
rangeidx{j(1),1}=[rangeidx{j(1),1} sprintf('%g,',i)]; % adding second row number for previous column I
else %regular input
rangeidx{j(1),1}=[rangeidx{j(1),1} sprintf('A%g:I%g,',i,i)]; %storing syntax for rows to be colored
end
if size(rangeidx{j(1),1},2) >= strlimit %for max Range string input
j(1)=j(1)+1; %cycling index for next rows
end
end
end
% One out of many loops for coloring all the rows. The Range() part is what
% limits the chars for the strings listing the rows. This line is what
% takes the most time, and I'm trying to find a method that doesn't require
% it to be looped.
for i=1:j(1) %for one color
if size(rangeidx{i,1},2) >= 6 %minimum length for one input
rangeidx{i,1}=rangeidx{i,1}(1:end-1); %getting rid of comma
xlsWS.Range(rangeidx{i,1}).Interior.Color=rgbGreen;
end
end

채택된 답변

dpb
dpb 2022년 1월 4일
편집: dpb 2022년 1월 4일
You can use any valid range expression to address any range you can code the logic to compute addresses of.
You must then use either a string variable or a char() string in the call with COM, however, a cellstr() will fail.
An example of some code I just finished the other day that writes formatting info in a range of cells including a non-contiguous selection looks like...
function prettifySheet(FQFN,cats,yrs,heightOutMax)
% a helper function to build a range address in Excel
fnXLRange=@(r,c,w,h)strrep(compose('%s:%s',xlsAddr(r,c),xlsAddr(r+h-1,char(c+w-1))),'$','');
...
% now set text alignment, etc....
r=r-4; h=3; c=char(c-3); w=1; % row, height, column, width of range to build
rnge=fnXLRange(r,c,w,h);
rnge=string(compose('%s,%s',rnge{:},strrep(xlsAddr(r+3,char(c+2)),'$','')));
%disp(rnge), disp(' ')
Excel_utils.hAlignRange(excel,rnge,'xlRight')
Excel_utils.FormatCellFont(excel,rnge,[],[],[],true)
...
An example of what the above builds for the range expression is--
K>> r=3;c='B';w=3,h=3; % row,column,width,height
K>> rnge=fnXLRange(r,c,w,h) % build the contiguous range expression
rnge =
1×1 cell array
{'B3:D5'}
% add another non-contiguous location; this is just a cell but can be
% another range as well...
K>> rnge=string(compose('%s,%s',rnge{:},strrep(xlsAddr(r+3,char(c+2)),'$','')));
K>> disp(rnge)
B3:D5,D6
K>>
NB: the non-contiguous ranges are separated by a comma. The Excel_utils class is from the one @Image Analyst built and posted; I've added to it locally for a few specific things was interested in/needed somewhat differently than the original, but the base COM code remains. I don't have as extensive an amount of stuff to write so the speed issue hasn't been a problem and so haven't embedded the COM code but continued to use the class methods. However, there's no reason that code can't be brought in line -- it looks like
function hAlignRange(Excel, range, horizAlign)
if ~isstring(range), range=string(range); end % a cellstr array doesn't work for range
merge=false;
if ~isnumeric(horizAlign)
if contains(horizAlign,'left','ignorecase',1)
horizAlign=-4131;
elseif contains(horizAlign,'right','ignorecase',1)
horizAlign=-4152;
elseif contains(horizAlign,'center','ignorecase',1)&~contains(horizAlign,'across','ignorecase',1)
horizAlign=-4108;
elseif contains(horizAlign,'general','ignorecase',1)
horizAlign=1;
elseif contains(horizAlign,'across','ignorecase',1)
horizAlign=-4108;
merge=true;
else
return
end
end
try
Excel.Range(range).Select;
Excel.Selection.HorizontalAlignment = horizAlign;
if merge, Excel.Selection.Merge, end
catch ME
errorMessage = sprintf('Error in function hAlignRange.\n\nError Message:\n%s', ME.message);
fprintf('%s\n', errorMessage);
end % from hAlignRange
return;
end % of the hAlignRange() method.
You'll note that while the top-level code uses cellstr() operations in order to not rewrite my xlsAddr utility that was built before the introduction of the string() class, the Excel_utils functions/methods cast the range to a string() if they're not. The COM interface cannot handle a cellstr() range address -- only a string or a char() variable or literal char() string.
Also note that the above strips the "$" absolute addresses -- I've not tested extensively at the low-level COM interface level, but the MATLAB xlswrite fails with them and so I've kept them out for compatibility with old code. xlsAddr builds them in (I really should add a flag control input variable) so they can be used to build formulas that may need them to keep absolute references.
Also NB: that xlsAddr accepts either the letter or a numeric value for the column so one can do arithmetic on computing new columns. I sometimes use all numeric values, often will start with the initial letter as in the above example and then use the ability of MATLAB to silently coerce a char() variable to double to do the arithmetic and then back again...just whatever turns out to be more convenient.
Hope that helps...
  댓글 수: 12
Bobby
Bobby 2022년 1월 10일
Haha, good question. But yes, coloring all of these rows is necessary for my task.
So I haven't used conditional formatting before, and looking at the Microsoft help page makes it seem difficult for my application (or just because of my inexperience). The coloring scheme takes in to account two columns; the first being one of two options, the second being one of four. These determine the coloring of the background and the font vs just the background. The coloring index I use to sort out all the coloring formats is not in the Excel sheet, so I would probably have to rewrite the entire coloring section of my code, which is fine if I saw a clear path at my task. But like I said, my inexperience would require a lot of time researching the method itself.

댓글을 달려면 로그인하십시오.

추가 답변(1개)

dpb
dpb 2022년 1월 5일
I'll throw the last example code out as an Answer as well as Comment just for visibility -- and it may turn out useful for others as well.
Excel = actxserver('Excel.Application');
Workbook = Excel.Workbooks.Open(fullfile(FOLDER,'testunion.xlsx'))
Excel.Worksheets.Item("Sheet1").Activate
uRnge=Excel.Application.Union(Excel.Range("A1"),Excel.Range("A10"))
uRnge.Formula="=RAND()"
Excel.ActiveWorkbook.Save
Excel.ActiveWorkbook.Close(false)
delete(Excel),clear Excel
The example in the MS documentation worked as advertised -- the above begins to illustrate what you have to do to simulate the VBA compiler translation to low-level commands that the COM engine can understand (really, instantiating references to the COM object you created instead of the MATLAB interpreter trying to read all as MATLAB variables/functions).
The reference to the 'Item' array in the Worksheets collection instead of trying to just write
Excel.Worksheets("Sheet1").Activate
is a hard-won lesson of trial and error and finding other code smarter folks than I had figured out...
I think it is illustrated the concept works--now whether it will be any faster or not, only experience and trying it out will determine.
The key is to activate the sheet you're working on, build a range object as the output from the Union method and then do your thing on that compound range.
The Q? will be whether it's any faster to make a call to Union with a whole bunch of arguments with individual references to the Range method over just calling each iteratively. The MATLAB code to build a set of string references will be fast, of course, the possible bottleneck will be in turning those into Excel range references/objects that the Union method expects...it doesn't know anything about just a cell address, unfortunately.
I presume but didn't test that you can build range expressions to pass to .Range() above that are also compound references, whether the overhead then is reduced or not again only testing will tell.

제품


릴리스

R2019b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by