Excel file customization via matlab

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!

Answer 1

[Image Analyst]

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);

Answer 2

[The Matlab Spot]

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

[Tommaso]

Ok, now it works with: "cellstyle = displayFormat.Style;".

May you write an example to how to set something?

I took a generic excel file and I saved the properties in a .txt. Than I modified

the excel (adding border, centring values), but i see no differences with the

properties in .txt..

Answer 3

[The Matlab Spot]

%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

Excel COM Object Model Reference

[Tommaso]

I made this .m file to customize excel:

% file_sintesi = fullfile(pwd, 'Sintesi.xlsx');

% h = actxserver('excel.application');

% wb = h.WorkBooks.Add(); % % h.Visible = 1;

% h.Columns.Item('A').ColumnWidth = 4;

% h.Columns.Item('B').ColumnWidth = 15;

% h.Columns.Item('C').ColumnWidth = 7.86;

% h.Columns.Item('D').ColumnWidth = 5;

% % h.Rows.Item('1:1').RowHeight = 30;

% % h.Range('A1:D4').Select;

% h.Selection.WrapText = true; % % set(h.Selection.Borders,'LineStyle',1); % % cells = h.Selection.Cells;

% displayFormat = cells.DisplayFormat;

% cellstyle = displayFormat.Style;

% set(cellstyle,'HorizontalAlignment','xlHAlignCenter');

% set(cellstyle,'VerticalAlignment','xlVAlignCenter'); % % % A1:B1 % ran = h.Activesheet.get('Range','A1:B1');

% ran.Font.Bold = true; % % % C1:D1 % ran = h.Activesheet.get('Range','C1:D1');

% ran.interior.Color = hex2dec('B4D5FC');

% ran.Font.Bold = true; % % wb.SaveAs(file_sintesi);

% wb.Close;

% h.Quit;

% h.delete; % % nome_file_excel = 'Sintesi.xlsx';

% a = [1 2 3];

% b = [10 20 30];

% c = [100 200 300];

% d = [1000 2000 3000];

% VectNome = {'Test' 'ACQ' 'Distance [km]' 'Time [s]'};

% xlswrite(nome_file_excel,VectNome,'A1:D1') % % for count=1:3

% Vect = {a(count) b(count) c(count) d(count)};

% eval(['xlswrite(nome_file_excel,Vect,''A',num2str(count+1),':D',num2str(count+1),''')'])

% end

It works, except for the lines:

% cells = h.Selection.Cells;

% displayFormat = cells.DisplayFormat;

% cellstyle = displayFormat.Style;

% set(cellstyle,'HorizontalAlignment','xlHAlignCenter');

% set(cellstyle,'VerticalAlignment','xlVAlignCenter')

The .xlsx file that is created does not have the cells content centred.

May you please tell me how to fix this code?

[Image Analyst]

Maybe remove the % symbols so the code will actually execute???

[Tommaso]

What a funny answer :D

I meant if you could please add the code that let me obtain the content inside the cell centred (in height and length).