Importing data to an excel file

I am having trouble importing data to an excel file. I was wondering if there is a code I can use, or if there's a command which closes the open excel file?

[Image Analyst]

See how I close Excel via ActiveX below.

Answer 1

[the cyclist]

Have you tried using xlsread() and/or xlswrite()?

[michal]

Yes, but it saves the file as .csv instead of .xls

[Image Analyst]

Your response makes no sense. Importing data into Excel is not related to saving it in any format. Regardless once it's in Excel YOU did the saving (interactively I presume), unless you used ActiveX like the demo I provided.

Just a wild guess - did you ask the wrong question? Did you really mean to ask how to import data FROM Excel (into MATLAB) rather than (from MATLAB) TO Excel? If so, realize that "from" and "to" are exact opposites so it's important to say what you mean?

[michal]

No, I asked the right question, I do mean how to import data to excel. What I meant to say was that it doesn't change the excel file, instead it puts a new file in the directory with the same name but with .cvs in the end.

[the cyclist]

Are you on a Mac? If so, read the notes at the end of "help xlswrite". It is crippled due to lack of COM server.

[Image Analyst]

cyclist, I don't think he wants to create an Excel workbook file from MATLAB. He's said twice that he wants to import data from MATLAB into Excel, and then have Excel save the data, presumably with an .xlsx file extension. Calling xlswrite() is not importing. Though the net result (a workbook file) may be the same, it's a somewhat different process. Not only is ActiveX much much faster if you're going to do multiple writes to the same workbook, but it also gives you the opportunity to do other things like formatting cells (borders, font, etc.) or create charts, etc. Michal, my code does exactly that. Did you try it? Don't be afraid of it. You can just copy and paste and run (after getting xlswrite1() from the Mathworks web site).

[the cyclist]

OK. I guess continue to be confused by what michal means by "import to".

Regardless, if he is on a Mac, I am not sure if your solution works. (But I would love to be proved wrong!) If there is a way to run ActiveX on a Mac, I would be excited to learn how.

[Image Analyst]

Might not. I run on Windows so I don't know how inter process communication happens on the Mac.

Answer 2

[Image Analyst]

To import your MATLAB variables into Excel you're either going to have to use xlswrite() to save them out to an Excel file, or use ActiveX to launch Excel, throw your MATLAB variables into Excel, then save your Excel workbook and quit Excel. To do the latter, adapt this demo code where I do all that with sample data:

% 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
% By ImageAnalyst 
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;