Use matlab write data to excel
24 次查看(过去 30 天)
显示 更早的评论
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?
0 个评论
采纳的回答
Image Analyst
2013-7-10
编辑:Image Analyst
2013-7-10
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
3 个评论
Jan
2013-7-10
@chengxuan yu: Getting such error means, that you have to adjust the code to your needs. Did you try this already? You can use the debugger to find the source of the problems. Although Image Analyst has been so kind to offer this piece of code, he is not responsible to maintain it.
Image Analyst
2013-7-11
Below is another demo where I launch Excel and get the "Excel" object that you need to pass in to the FormatCellFont() function. Like Jan says, you probably need to adapt it to you situation. I don't know what you mean by "new Excel" and "the excel that have already exist." This code assumes Excel is not running and launches it. If you want to connect in to an instance of Excel that is already running, you can use
Excel = actxGetRunningServer('Excel.application')
I probably should have done this first and then only call actxserver('Excel.Application') if actxGetRunningServer() fails, so you might do that.
% 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;
更多回答(0 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!