MATLAB: Use matlab write data to excel

xlswrite

Thanks in advance, any comments will be appreciate! I have data that I have write to a cell in an Excel Spreadsheet, but I also want to assign a background color and font color to this cell. I would like to know the way to do this through MATLAB. How can I do?

Best Answer

You need to use ActiveX programming. Search for ActiveX because there are lots of examples in this forum. For example:

function FormatCellFont(Excel, sheetNumber, cellsToFormat)
try
  numberOfCells = size(cellsToFormat, 1);
  worksheets = Excel.sheets;
  %     thisSheet = get(worksheets, 'Item', sheetNumber);
  thisSheet = Excel.ActiveSheet;
  thisSheetsName = Excel.ActiveSheet.Name;  % For info only.
  for k = 1 : numberOfCells
    row = cellsToFormat(k, 1);  % Get the row number.
    column = cellsToFormat(k, 2);  % Get the column number.
    columnLetterCode = cell2mat(ExcelCol(column));
    % Get a reference to the cell at this row in column A.
    cellReference = sprintf('%s%d', columnLetterCode, row);
    theCell = thisSheet.Range(cellReference);
    % Set the horizontal alignment to left justified.
    theCell.HorizontalAlignment = 2;
    % Set the font.
    theCell.Font.Name = 'Calibri';
    % Set the font to bold.
    theCell.Font.Bold = true;
    % Set the font size to 12 points.
    theCell.Font.Size = 12;
    % Set the font color to blue.
    theCell.Font.Color = -65536;
  end
  
catch ME
  errorMessage = sprintf('Error in function FormatCellFont.\n\nError Message:\n%s', ME.message);
  fprintf(errorMessage);
  WarnUser(errorMessage);
end
return; % from FormatCellFont

Related Solutions

MATLAB: How Can I Write a Matrix to excel using ActXserver (or another way if needed!)

See my demo:

% Demo macro to write numerical arrays and cell arrays
% to two different worksheets in an Excel workbook file.
% Uses xlswrite1, available from the File Exchange
% http://www.mathworks.com/matlabcentral/fileexchange/10465-xlswrite1
function ExcelDemo
clc;
close all;
clear all;
fullFileName = GetXLFileName();
if isempty(fullFileName)
  % User clicked Cancel.

  return;
end
Excel = actxserver('Excel.Application');   
% Prepare proper filename extension.
% Get the Excel version because if it's version 11 (Excel 2003) the file extension should be .xls,
% but if it's 12.0 (Excel 2007) then we'll need to use an extension of .xlsx to avoid nag messages.
excelVersion = str2double(Excel.Version);
if excelVersion < 12
  excelExtension = '.xls';
else
  excelExtension = '.xlsx';
end
% Determine the proper format to save the files in.  It depends on the extension (Excel version).
switch excelExtension
  case '.xls' %xlExcel8 or xlWorkbookNormal
     xlFormat = -4143;
  case '.xlsb' %xlExcel12
     xlFormat = 50;
  case '.xlsx' %xlOpenXMLWorkbook
     xlFormat = 51;
  case '.xlsm' %xlOpenXMLWorkbookMacroEnabled 
     xlFormat = 52;
  otherwise
     xlFormat = -4143;
end
if ~exist(fullFileName, 'file')
  message = sprintf('I am going to create Excel workbook:\n\n%s\n\nClick OK to continue.\nClick Exit to exit this function', fullFileName);
  button = questdlg(message, 'Creating new workbook', 'OK', 'Exit', 'OK');
  drawnow;  % Refresh screen to get rid of dialog box remnants.
  if strcmpi(button, 'Exit')
    return;
  end
  % Add a new workbook.
  ExcelWorkbook = Excel.workbooks.Add;
  % Save this workbook we just created.
  ExcelWorkbook.SaveAs(fullFileName, xlFormat);
  ExcelWorkbook.Close(false);
else
  % Delete the existing file.
  delete(fullFileName);
end
% Open up the workbook named in the variable fullFileName.
invoke(Excel.Workbooks,'Open', fullFileName);
Excel.visible = true;
% Create some sample data.
myData = magic(20);
myOtherData = rand(10);
% Then run the new xlswrite1 function as many times as needed or in a loop 
% (for example xlswrite1(fullFileName, yourArrayName, XL_CellLocation). 
% IMPORTANT NOTE: the Excel object variable MUST exist in the routine that calls xlswrite1()
% and it MUST be named "Excel" EXACTLY because xlswrite1() has this line it it:
%   Excel = evalin('caller', 'Excel');
xlswrite1(fullFileName, myData, 'mySheetName', 'B2');
ca = {'Column Header 1', 'Column Header 2'};
xlswrite1(fullFileName, ca, 'mySheetName', 'B1');
ca = {'Row Header 1'; 'Row Header 2'};
xlswrite1(fullFileName, ca, 'mySheetName', 'A2');
xlswrite1(fullFileName, myOtherData, 'myOtherSheetName', 'B2');
% Delete all empty sheets in the active workbook.
DeleteEmptyExcelSheets(Excel);
% For fun, add comments to cells A1:A12 on sheet #1.
worksheets = Excel.sheets;
thisSheet = get(worksheets, 'Item', 1); 
for k = 1 : 12
  myComment = sprintf('Comment for cell A%d', k);
  cellReference = sprintf('A%d', k);
  theCell = thisSheet.Range(cellReference);
  theCell.AddComment(myComment);
end
% Then run the following code to close the activex server:
invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit;
Excel.delete;
clear Excel;
message = sprintf('Done!\nThis Excel workbook has been created:\n%s', fullFileName);
msgbox(message);
% End of main function: ExcelDemo.m -----------------------------
%--------------------------------------------------------------------
% Gets the name of the workbook from the user.
function fullExcelFileName = GetXLFileName()
  fullExcelFileName = [];  % Default.
  % Ask user for a filename.
  FilterSpec = {'*.xls', 'Excel workbooks (*.xls)'; '*.*', 'All Files (*.*)'};
  DialogTitle = 'Save workbook file name';
  % Get the default filename.  Make sure it's in the folder where this m-file lives.
  % (If they run this file but the cd is another folder then pwd will show that folder, not this one.
  thisFile = mfilename('fullpath');
  [thisFolder, baseFileName, ext] = fileparts(thisFile);
  DefaultName = sprintf('%s/%s.xls', thisFolder, baseFileName);
  [fileName, specifiedFolder] = uiputfile(FilterSpec, DialogTitle, DefaultName);
  if fileName == 0
    % User clicked Cancel.
    return;
  end
  % Parse what they actually specified.
  [folder, baseFileName, ext] = fileparts(fileName);
  % Create the full filename, making sure it has a xls filename.
  fullExcelFileName = fullfile(specifiedFolder, [baseFileName '.xls']);
% --------------------------------------------------------------------
% DeleteEmptyExcelSheets: deletes all empty sheets in the active workbook.
% This function looped 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)
%   excelObject = actxserver('Excel.Application');
%   excelWorkbook = excelObject.workbooks.Open(fileName);
  worksheets = excelObject.sheets;
  sheetIdx = 1;
  sheetIdx2 = 1;
  numSheets = worksheets.Count;
  % Prevent beeps from sounding if we try to delete a non-empty worksheet.
  excelObject.EnableSound = false;
  % Loop over all sheets
  while sheetIdx2 <= numSheets
    % Saves the current number of sheets in the workbook
    temp = 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(sheetIdx>1,numSheets-sheetIdx2>0)
      % worksheets.Item(sheetIdx).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(sheetIdx).UsedRange.Count == 1
        worksheets.Item(sheetIdx).Delete;
      end
    end
    % Check whether the number of sheets has changed. If this is not the
    % case the counter "sheetIdx" is increased by one.
    if temp == worksheets.count;
      sheetIdx = sheetIdx + 1;
    end
    sheetIdx2 = sheetIdx2 + 1; % prevent endless loop...
  end
  excelObject.EnableSound = true;
  return;

MATLAB: Opening excel only once at the beginning of the optimization proces in Matlab and not each time Matlab sends a new set of decision values to excel

Yes but you need to use ActiveX programming. See this demo:

% Demo macro to write numerical arrays and cell arrays
% to two different worksheets in an Excel workbook file.
% Uses xlswrite1, available from the File Exchange
% http://www.mathworks.com/matlabcentral/fileexchange/10465-xlswrite1
function ExcelDemo
clc;
close all;
clear all;
fullFileName = GetXLFileName();
if isempty(fullFileName)
  % User clicked Cancel.

  return;
end
Excel = actxserver('Excel.Application');   
% Prepare proper filename extension.
% Get the Excel version because if it's version 11 (Excel 2003) the file extension should be .xls,
% but if it's 12.0 (Excel 2007) then we'll need to use an extension of .xlsx to avoid nag messages.
excelVersion = str2double(Excel.Version);
if excelVersion < 12
  excelExtension = '.xls';
else
  excelExtension = '.xlsx';
end
% Determine the proper format to save the files in.  It depends on the extension (Excel version).
switch excelExtension
  case '.xls' %xlExcel8 or xlWorkbookNormal
     xlFormat = -4143;
  case '.xlsb' %xlExcel12
     xlFormat = 50;
  case '.xlsx' %xlOpenXMLWorkbook
     xlFormat = 51;
  case '.xlsm' %xlOpenXMLWorkbookMacroEnabled 
     xlFormat = 52;
  otherwise
     xlFormat = -4143;
end
if ~exist(fullFileName, 'file')
  message = sprintf('I am going to create Excel workbook:\n\n%s\n\nClick OK to continue.\nClick Exit to exit this function', fullFileName);
  button = questdlg(message, 'Creating new workbook', 'OK', 'Exit', 'OK');
  drawnow;  % Refresh screen to get rid of dialog box remnants.
  if strcmpi(button, 'Exit')
    return;
  end
  % Add a new workbook.
  ExcelWorkbook = Excel.workbooks.Add;
  % Save this workbook we just created.
  ExcelWorkbook.SaveAs(fullFileName, xlFormat);
  ExcelWorkbook.Close(false);
else
  % Delete the existing file.
  delete(fullFileName);
end
% Open up the workbook named in the variable fullFileName.
invoke(Excel.Workbooks,'Open', fullFileName);
Excel.visible = true;
% Create some sample data.
myData = magic(20);
myOtherData = rand(10);
% Then run the new xlswrite1 function as many times as needed or in a loop 
% (for example xlswrite1(fullFileName, yourArrayName, XL_CellLocation). 
% IMPORTANT NOTE: the Excel object variable MUST exist in the routine that calls xlswrite1()
% and it MUST be named "Excel" EXACTLY because xlswrite1() has this line it it:
%   Excel = evalin('caller', 'Excel');
xlswrite1(fullFileName, myData, 'mySheetName', 'B2');
ca = {'Column Header 1', 'Column Header 2'};
xlswrite1(fullFileName, ca, 'mySheetName', 'B1');
ca = {'Row Header 1'; 'Row Header 2'};
xlswrite1(fullFileName, ca, 'mySheetName', 'A2');
xlswrite1(fullFileName, myOtherData, 'myOtherSheetName', 'B2');
% Delete all empty sheets in the active workbook.
DeleteEmptyExcelSheets(Excel);
% For fun, add comments to cells A1:A12 on sheet #1.
worksheets = Excel.sheets;
thisSheet = get(worksheets, 'Item', 1); 
for k = 1 : 12
  myComment = sprintf('Comment for cell A%d', k);
  cellReference = sprintf('A%d', k);
  theCell = thisSheet.Range(cellReference);
  theCell.AddComment(myComment);
end
% Then run the following code to close the activex server:
invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit;
Excel.delete;
clear Excel;
message = sprintf('Done!\nThis Excel workbook has been created:\n%s', fullFileName);
msgbox(message);
% End of main function: ExcelDemo.m -----------------------------
%--------------------------------------------------------------------
% Gets the name of the workbook from the user.
function fullExcelFileName = GetXLFileName()
  fullExcelFileName = [];  % Default.
  % Ask user for a filename.
  FilterSpec = {'*.xls', 'Excel workbooks (*.xls)'; '*.*', 'All Files (*.*)'};
  DialogTitle = 'Save workbook file name';
  % Get the default filename.  Make sure it's in the folder where this m-file lives.
  % (If they run this file but the cd is another folder then pwd will show that folder, not this one.
  thisFile = mfilename('fullpath');
  [thisFolder, baseFileName, ext] = fileparts(thisFile);
  DefaultName = sprintf('%s/%s.xls', thisFolder, baseFileName);
  [fileName, specifiedFolder] = uiputfile(FilterSpec, DialogTitle, DefaultName);
  if fileName == 0
    % User clicked Cancel.
    return;
  end
  % Parse what they actually specified.
  [folder, baseFileName, ext] = fileparts(fileName);
  % Create the full filename, making sure it has a xls filename.
  fullExcelFileName = fullfile(specifiedFolder, [baseFileName '.xls']);
% --------------------------------------------------------------------
% DeleteEmptyExcelSheets: deletes all empty sheets in the active workbook.
% This function looped 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)
%   excelObject = actxserver('Excel.Application');
%   excelWorkbook = excelObject.workbooks.Open(fileName);
  worksheets = excelObject.sheets;
  sheetIdx = 1;
  sheetIdx2 = 1;
  numSheets = worksheets.Count;
  % Prevent beeps from sounding if we try to delete a non-empty worksheet.
  excelObject.EnableSound = false;
  % Loop over all sheets
  while sheetIdx2 <= numSheets
    % Saves the current number of sheets in the workbook
    temp = 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(sheetIdx>1,numSheets-sheetIdx2>0)
      % worksheets.Item(sheetIdx).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(sheetIdx).UsedRange.Count == 1
        worksheets.Item(sheetIdx).Delete;
      end
    end
    % Check whether the number of sheets has changed. If this is not the
    % case the counter "sheetIdx" is increased by one.
    if temp == worksheets.count;
      sheetIdx = sheetIdx + 1;
    end
    sheetIdx2 = sheetIdx2 + 1; % prevent endless loop...
  end
  excelObject.EnableSound = true;
  return;

Best Answer

Related Solutions

MATLAB: How Can I Write a Matrix to excel using ActXserver (or another way if needed!)

MATLAB: Opening excel only once at the beginning of the optimization proces in Matlab and not each time Matlab sends a new set of decision values to excel

Related Question