Excel file customization via matlab
82 Ansichten (letzte 30 Tage)
Ältere Kommentare anzeigen
Hi everybody,
I need to know how to realize via matlab these following excel customizations:
1) How to add and set (lines width, ..) cell border;
2) How to centre (in height and length) the content inside the cell.
3) How to set the number of decimal places;
Thanks!
0 Kommentare
Antworten (3)
Image Analyst
am 15 Nov. 2013
I have an Excel class to do some common things that I need to do all the time. It's attached below in blue. For example the function to format the left borders is:
% borders is a collections of all. if you want, you can set one
% particular border as,
%
% my_border = get(borders, 'Item', <item>);
% set(my_border, 'ColorIndex', 3);
% set(my_border, 'LineStyle', 9);
%
% where, <item> can be,
% 1 - all vertical but not rightmost
% 2 - all vertical but not leftmost
% 3 - all horizontal but not bottommost
% 4 - all horizontal but not topmost
% 5 - all diagonal down
% 6 - all diagonal up
% 7 - leftmost only
% 8 - topmost only
% 9 - bottommost only
% 10 - rightmost only
% 11 - all inner vertical
% 12 - all inner horizontal
%
% so, you can choose your own side.
function FormatLeftBorder(sheetReference, columnNumbers, startingRow, endingRow)
try
numberOfColumns = length(columnNumbers);
for col = 1 : numberOfColumns
% Put a thick black line along the left edge of column columnNumber
columnLetterCode = cell2mat(ExcelCol(columnNumbers(col)));
cellReference = sprintf('%s%d:%s%d', columnLetterCode, startingRow, columnLetterCode, endingRow);
theCell = sheetReference.Range(cellReference);
borders = get(theCell, 'Borders');
% Get just the left most border.
leftBorder = get(borders, 'Item', 7);
% Set it's style.
set(leftBorder, 'LineStyle', 1);
% Set it's weight.
set(leftBorder, 'Weight', 4);
end
catch ME
errorMessage = sprintf('Error in function FormatLeftBorder.\n\nError Message:\n%s', ME.message);
WarnUser(errorMessage);
end
return; % from FormatLeftBorder
end % of FormatLeftBorder
Here's an example of how I've called the methods in the class to fancy up some cells in the workbook:
% Bold A18 - H19
Excel_utils.FormatCellFont(Excel, 'A18:H19', 'Calibri', 11, true, 0);
% Bold row 24
Excel_utils.FormatCellFont(Excel, 'A24:M24', 'Calibri', 11, true, 0);
% Left align A20
Excel_utils.AlignCells(Excel, 'A20', 4, false);
Excel_utils.FormatCellFont(Excel, 'A20', 'Calibri', 11, false, 0);
% Left align M25 and 26.
Excel_utils.AlignCells(Excel, 'M25:M26', 4, false);
% Center align B18 - L25
Excel_utils.AlignCells(Excel, 'B18:L25', 3, false);
0 Kommentare
The Matlab Spot
am 15 Nov. 2013
Use this to get the cell object...
exl = actxserver('excel.application');
exlWkbk = exl.Workbooks;
exlFile = exlWkbk.Open('C:\someExcelFile.xlsx');
exlSheet1 = exlFile.Sheets.Item('Sheet1');
dat_range = 'A1:A1'; % Example range
rngObj = exlSheet1.Range(dat_range);
cells = rngObj.Cells;
displayFormat = cells.DisplayFormat;
cellstyle = styledisplayFormat.Style;
then on the command prompt...
>>get(cellstyle)
and play around with the properties to set the cell border, allignment and other properties of the cell object
The Matlab Spot
am 15 Nov. 2013
%Examples:
set(cellstyle,'HorizontalAlignment','xlHAlignRight');
set(cellstyle,'VerticalAlignment','xlVAlignCenter');
set(rngObj.Borders,'LineStyle',12);
set(rngObj,'NumberFormat','0.000%');
For more details on the Excel COM object model from where you can take references of properties and object types
3 Kommentare
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!