Excel file customization via matlab
52 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
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 comentarios
Respuestas (3)
Image Analyst
el 15 de Nov. de 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 comentarios
The Matlab Spot
el 15 de Nov. de 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
el 15 de Nov. de 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 comentarios
Ver también
Categorías
Más información sobre Spreadsheets en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!