Excel file customization via matlab
52 views (last 30 days)
Show older comments
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 Comments
Answers (3)
Image Analyst
on 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 Comments
The Matlab Spot
on 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
on 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 Comments
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!