Excel files: how to edit formatting of a specific sheet within a workbook?

4 次查看（过去 30 天）
Keilan
Keilan 2014-12-9
评论： Keilan 2014-12-18
Hi all,
I am having some trouble editing Excel formatting using MATLAB. I would ultimately like to edit the borders of certain cells. I have a workbook with multiple sheets, and also want to be able to select a specific sheet of interest to edit. With the code below I am able to edit the cell borders, but I can only do it for whichever sheet is active. How can I specify which sheet to edit?
Thanks for any help or suggestions,
K
% Link to Excel
Excel = actxserver('Excel.Application');
Excel.Workbooks.Open('C:\Users\Keilan\Documents\MATLAB Expirements\2014\MATLAB to Excel test.xlsm');
%%Here is where I am trying to specify which sheet to edit - for example I would like to edit the
% 3rd sheet in the workbook
double = get(Excel.ActiveWorkBook.Sheets,'Item',3); % Doesn't work for me
% Number associated with each border: left, right, top, bottom
lt = 1;
rt = 2;
% tp = 3;
bt = 4;
% Add in the periodic borders required
for kk = 1:ct
% Row numbers of interest
rn1 = 1 + 3*kk; % R1, A(rn1):E(rn2). R2, BG(rn1):BK(rn2)
rn2 = 3 + 3*kk; % R3, A(rn2):BK(rn6)
% Ranges of interest
R1 = sprintf('A%d:E%d',rn1,rn2); %'A4:E6';
R2 = sprintf('BG%d:BK%d',rn1,rn2); % 'BG4:BK6';
R3 = sprintf('A%d:BK%d',rn2,rn2); % 'A6:BK6';
Range1 = Excel.Range(R1);
Range2 = Excel.Range(R2);
Range3 = Excel.Range(R3);
% Create solid borders in desired locations
set(get((Range1.borders),'item',lt),'linestyle',1);
set(get((Range1.borders),'item',rt),'linestyle',1);
set(get((Range2.borders),'item',rt),'linestyle',1);
set(get((Range3.borders),'item',bt),'linestyle',1);
end
Excel.Visible = 1; % Open the Excel spreadsheet
delete(Excel); % Close the activex server

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

采纳的回答

Guillaume
Guillaume 2014-12-9
Don't use ActiveWorkbook and ActiveSheet, instead use the objects returned when you open the workbook / access the sheet:
workbook = Excel.Workbooks.Open(...); %Use workbook instead of ActiveWorkbook from now on
sheet = workbook.Sheets.Item(3);
%...
range1 = sheet.Range(R1); %and so on
And never ever use double as a variable name. This is the name of a very common function in matlab, so will break a lot of code (even some built-in functions).

更多回答（0 个）

类别

在 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