Code resources for excel-matlab activeX work??

I am working on a project for a consulting firm where I am setting up a report for banks that takes information from matlab and formats it in excel. It creates a report which is formatted and has a specific layout. I am having some trouble finding the appropriate source to find the right code to use to perform the simple formatting and layout operations.

I am using ActiveX on a running excel program:

xl = actxGetRunningServer('Excel.Application');

to then create a workbook, and edit the active sheet.

wrbk = invoke(xl.Workbooks, 'Add'); Sheet1 = xl.ActiveSheet

My issue is I do not know enough matlab or visual basic to write proper code. I need to perform simple functions like set a border and format the cells, and autofit them, etc...

The length of the data is arbitrary so I needed to devise a method of underlining or bordering the data for varying lengths. This is the method I am using for exporting the data to excel:

dimensions = size(simAnnDS); % simAnnDS = annual debt service report(cell aray)

row = dimensions(1);

column = dimensions(2);

r1 = 'a10'; %start location of matrix

r2 = get(Sheet1, 'Cells', row+9, column); % end location of matrix

myrange = get(Sheet1, 'Range', r2,r1);

set(myrange, 'Value', simAnnDS);

this allows me to find a range that I need depending on the size of the data and then write to an excel workbook according to that. HOW CAN I USE THIS OR A SIMILAR METHOD WHEN DEALING WITH ARBITRARY LENGTHS OF CELLS AND TRYING TO FORMAT THOSE CELLS???

For example, I need to underline a set of headers that I created for the data which starts at cell 'b9' BUT depending on the result it goes across more columns. How can I set up an underlining method which takes into account the varying length of the data?

My intuition tells me that I should be able to use a similar method for example:

dimensions = size(simAnnDS);

row = dimensions(1);

column = dimensions(2);

r1 = 'a10'; %start location of headers which need underlining

r2 = get(Sheet1, 'Cells', 1, column); % end location of text which needs underlining. range is set to be 1 x columns so that the entire row gets underlined

myrange = get(Sheet1, 'Range', r2,r1); % create a range that combines start to end of the row with text that needs to be underlined

set(myrange, 'PropertyName', 'PropertyValue');

The set function says that you should insert the property name and the property value that you want to change but i cant find anywhere where the acceptable parameters are listed. Obviously I am looking for something like format and underline but that doesnt work. Please help.

Thank you!

Answer 1

[Image Analyst]

Frank: I made a class with several routines that I want to use (I'm not saying they are all the absolute best way to do these things, but they work for me). To use, you'd do something like this:

% Apply some borders.
thisWorksheet=Excel.ActiveSheet;
rowsToApplyBottomBorderTo = [1 2 26];
lastColumnWithData = 15;  % Bottom border goes from column 1 to this column.
% % Put a thick black line (weight of 4) under the first and last row.
Excel_utils.FormatBottomBorder(thisWorksheet, rowsToApplyBottomBorderTo, 1, lastColumnWithData, 4);
% Put a thick black line along the left edge of certain columns.
firstRow = 2;
lastRow = 26;  % Left border goes from row 1 to this row.
columnsToApplyLeftBorderTo = [2, 5, 8, 9, 12, 13, 16];
Excel_utils.FormatLeftBorder(thisWorksheet, columnsToApplyLeftBorderTo, firstRow, lastRow);

Maybe some of them can help you:

classdef Excel_utils
      
  methods(Static)
    
    % --------------------------------------------------------------------
    % DeleteEmptyExcelSheets: deletes all empty sheets in the active workbook.
    % This function loops through all sheets and deletes those sheets that are empty.
    % Can be used to clean a newly created xls-file after all results have been saved in it.
    function DeleteEmptyExcelSheets(excelObject)
      try
        %   excelObject = actxserver('Excel.Application');
        %   excelWorkbook = excelObject.workbooks.Open(fileName);
        
        % Run Yair's program http://www.mathworks.com/matlabcentral/fileexchange/17935-uiinspect-display-methods-properties-callbacks-of-an-object
        % to see what methods and properties the Excel object has.
        %   uiinspect(excelObject);
        
        worksheets = excelObject.sheets;
        sheetIndex = 1;
        sheetIndex2 = 1;
        initialNumberOfSheets = worksheets.Count;
        % Prevent beeps from sounding if we try to delete a non-empty worksheet.
        excelObject.EnableSound = false;
        % Tell it to not ask you for confirmation to delete the sheet(s).
        excelObject.DisplayAlerts = false;
        
        % Loop over all sheets
        while sheetIndex2 <= initialNumberOfSheets
          % Saves the current number of sheets in the workbook.
          preDeleteSheetCount = worksheets.count;
          % Check whether the current worksheet is the last one. As there always
          % need to be at least one worksheet in an xls-file the last sheet must
          % not be deleted.
          if or(sheetIndex>1,initialNumberOfSheets-sheetIndex2>0)
            % worksheets.Item(sheetIndex).UsedRange.Count is the number of used cells.
            % This will be 1 for an empty sheet.  It may also be one for certain other
            % cases but in those cases, it will beep and not actually delete the sheet.
            if worksheets.Item(sheetIndex).UsedRange.Count == 1
              worksheets.Item(sheetIndex).Delete;
            end
          end
          % Check whether the number of sheets has changed. If this is not the
          % case the counter "sheetIndex" is increased by one.
          postDeleteSheetCount = worksheets.count;
          if preDeleteSheetCount == postDeleteSheetCount;
            % If this sheet was not empty, and wasn't deleted, move on to the next sheet.
            sheetIndex = sheetIndex + 1;
          else
            % sheetIndex stays the same.  It's not incremented because the current sheet got deleted,
            % and all the other sheets shift down in their sheet number.  So now sheetIndex will
            % point to the same number which is the next sheet in line for checking.
          end
          sheetIndex2 = sheetIndex2 + 1; % prevent endless loop...
        end
        excelObject.EnableSound = true;
      catch ME
        errorMessage = sprintf('Error in function DeleteEmptyExcelSheets.\n\nError Message:\n%s', ME.message);
        fprintf('%s\n', errorMessage);
        WarnUser(errorMessage);
      end
      return; % from DeleteEmptyExcelSheets
    end % of DeleteEmptyExcelSheets
    
    %---------------------------------------------------------------------------------------------------------------------
    % Add comments to cells on sheet.
    % Sometimes this throws exception #0x800A03EC on the second and subsequent images.  It looks like this:
    % "Error: Object returned error code: 0x800A03EC"
    % It is because of trying to insert a comment for a worksheet cell when a comment already exists for that worksheet cell.
    % So in that case, rather than deleting the comment and then inserting it, I'll just let it throw the exception
    % but I won't pop up any warning message for the user.
    function InsertComments(Excel, caComments, sheetNumber, startingRow, startingColumn)
      try
        worksheets = Excel.sheets;
        %     thisSheet = get(worksheets, 'Item', sheetNumber);
        thisSheet = Excel.ActiveSheet;
        thisSheetsName = Excel.ActiveSheet.Name;  % For info only.
        numberOfComments = size(caComments, 1);  % # rows
        for columnNumber = 1 : numberOfComments
          columnLetterCode = cell2mat(ExcelCol(startingColumn + columnNumber - 1));
          % Get the comment for this row.
          myComment = sprintf('%s', caComments{columnNumber});
          % Get a reference to the cell at this row in column A.
          cellReference = sprintf('%s%d', columnLetterCode, startingRow);
          theCell = thisSheet.Range(cellReference);
          % You need to clear any existing comment or else the AddComment method will throw an exception.
          theCell.ClearComments();
          % Add the comment to the cell.
          theCell.AddComment(myComment);
        end
        
      catch ME
        errorMessage = sprintf('Error in function InsertComments.\n\nError Message:\n%s', ME.message);
        fprintf(errorMessage);
        WarnUser(errorMessage);
      end
      return; % from InsertComments
    end % of InsertComments
    
    %----------------------------------------------------------------------------------------------------------
    % places is the number of decimal places to the right of the decimal point, like 0.000 for 3.
    % excelRange is the range over which you want to apply the formatting, like 'A1..C5'
    % Example call:  Excel_utils.FormatDecimalPlaces(Excel, 3, 'A1..C5');
    function FormatDecimalPlaces(Excel, places, excelRange)
      try
        if places == 0
          formatString = '0';
        else
          formatString = '0.';
          for p = 1 : places
            % Append additional zeros.
            formatString = sprintf('%s0', formatString);
          end
        end
        
        % Select the range
        Excel.Range(excelRange).Select;
        
        % Format cells to the specified number of decimal places.
        Excel.Selection.NumberFormat = formatString;
      catch ME
        errorMessage = sprintf('Error in function FormatDecimalPlaces.\nThe Error Message:\n%s', ME.message);
        fprintf(errorMessage);
        WarnUser(errorMessage);
      end
      return; % from FormatDecimalPlaces
    end % of FormatDecimalPlaces
    
    
    %----------------------------------------------------------------------------------------------------------
    % Turns on text wrap for cells in excelRange.
    % Example call:  Excel_utils.WrapText(Excel, 'A1..A12', true);
    function WrapText(Excel, excelRange, trueOrFalse)
      try
        % Select the range
        Excel.Range(excelRange).Select;
        
        % Turn wrapping on or off
        Excel.Selection.WrapText = trueOrFalse;
      catch ME
        errorMessage = sprintf('Error in function WrapText.\nThe Error Message:\n%s', ME.message);
        fprintf(errorMessage);
        WarnUser(errorMessage);
      end
      return; % from WrapText
    end % of WrapText

    % 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
    
    function FormatBottomBorder(sheetReference, rowNumbers, startingCol, endingCol, weight)
      try
        numberOfRows = length(rowNumbers);
        for row = 1 : numberOfRows
          % Put a thick black line along the bottom edge of row rowNumbers(row)
          column1Letter = cell2mat(ExcelCol(startingCol));
          column2Letter = cell2mat(ExcelCol(endingCol));
          cellReference = sprintf('%s%d:%s%d', column1Letter, rowNumbers(row), column2Letter, rowNumbers(row));
          theCell = sheetReference.Range(cellReference);
          borders = get(theCell, 'Borders');
          % Get just the bottom most border.
          leftBorder = get(borders, 'Item', 9);
          % Set it's style.
          set(leftBorder, 'LineStyle', 1);
          % Set it's weight.
          set(leftBorder, 'Weight', weight);
        end
        
      catch ME
        errorMessage = sprintf('Error in function FormatBottomBorder.\n\nError Message:\n%s', ME.message);
        WarnUser(errorMessage);
      end
      return; % from FormatBottomBorder
    end % of FormatBottomBorder
    
    %================================================================================================
    % Selects all cells in the current worksheet and auto-sizes all the columns
    % and vertically and horizontally aligns all the cell contents.
    % Leaves with cell A1 selected.
    function CenterCellsAndAutoSizeColumns(excelObject)
      try
        % Select the entire spreadsheet.
        excelObject.Cells.Select;
        % Auto fit all the columns.
        excelObject.Cells.EntireColumn.AutoFit;
        % Center align the cell contents.
        excelObject.Selection.HorizontalAlignment = 3;
        excelObject.Selection.VerticalAlignment = 2;
        % Put "cursor" or active cell at A1, the upper left cell.
        excelObject.Range('A1').Select;
      catch ME
        errorMessage = sprintf('Error in function CenterCellsAndAutoSizeColumns.\n\nError Message:\n%s', ME.message);
        fprintf('%s\n', errorMessage);
        WarnUser(errorMessage);
      end
      return; % from CenterCellsAndAutoSizeColumns
    end % of CenterCellsAndAutoSizeColumns
    %-------------------------------------------------------------------------------------------------------
    % Loops over all sheets in a workbook, auto-sizing columns and center-aligning all cells.
    function AutoSizeAllSheets(excelObject)
      try
        %   excelObject = actxserver('Excel.Application');
        %   excelWorkbook = excelObject.workbooks.Open(fileName);
        worksheets = excelObject.sheets;
        numSheets = worksheets.Count;
        
        % Loop over all sheets
        for currentSheet = 1 : numSheets
          thisSheet = get(worksheets, 'Item', currentSheet);
          invoke(thisSheet, 'Activate');
          % Center data in cells, and auto-size all columns.
          CenterCellsAndAutoSizeColumns(excelObject)
        end
      catch ME
        errorMessage = sprintf('Error in function AutoSizeAllSheets.\n\nError Message:\n%s', ME.message);
        fprintf('%s\n', errorMessage);
        WarnUser(errorMessage);
      end
      return; % from AutoSizeAllSheets
    end % of AutoSizeAllSheets
    
    %-------------------------------------------------------------------------------------------------------
    % Left-align the specified sheet only.
    function LeftAlignSheet(Excel, sheetNumber)
      try
        sheetNumber3 = get(Excel.sheets, 'Item', sheetNumber);
        sheetNumber3.Activate;
        % Select the entire spreadsheet.
        Excel.Cells.Select;
        % Auto fit all the columns.
        %     Excel.Cells.EntireColumn.AutoFit;
        % Left align the cell contents.
        Excel.Selection.HorizontalAlignment = 1;
        Excel.Selection.VerticalAlignment = 2;
        % Put "cursor" or active cell at A1, the upper left cell.
        Excel.Range('A1').Select;
      catch ME
        errorMessage = sprintf('Error in function LeftAlignSheet.\n\nError Message:\n%s', ME.message);
        fprintf('%s\n', errorMessage);
        WarnUser(errorMessage);
      end
      return; % from LeftAlignSheet
    end % of LeftAlignSheet
    
  end % methods definitions.
  
end

You can get more help from Microsoft:

http://msdn.microsoft.com/en-us/library/bb259457%28v=office.12%29.aspx

http://msdn.microsoft.com/en-us/library/bb242656%28office.12%29.aspx

http://support.microsoft.com/?scid=kb;EN-US;302094

or you can record a macro in Excel then edit it to see what methods you need to call - that's how I made the class above.