Creating an array of characters
2 views (last 30 days)
Show older comments
I have a lot of excel files that I need to process into one single file. The problem I am having is that I want to create an array of titles to put as headers over each set of data. I can read in the file names just fine. I'd like to have an array such as the following -
title 1 0 title 2 0 title 3 0...
The zeros are just there because I want one title for every set of two columns of xy data.
0 Comments
Answers (2)
Image Analyst
on 24 Nov 2011
You should use ActiveX for speed - it will be a lot faster than using xlsread a lot of times. Here's a little demo for you. I think it should be obvious and straightforward to see how to adapt the column headers to be what you need. ImageAnalyst
% 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');
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,1);
ExcelWorkbook.Close(false);
end
% Open up the workbook named in the variable fullFileName.
invoke(Excel.Workbooks,'Open',fullFileName);
% 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).
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);
% 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;
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!