I have data in 10 excel files and each excel file contains 50 sheets. I want to couple data of each corresponding sheet of excel file means first sheet data of all 10 files, 2nd sheet data of all 10 excel files to 50th sheet of all 10 files. Hence finally there should be 50 sheets in one file. How is it possible. Please suggest.
MATLAB: Coupling of coorresponding sheets of excel files
excel coupling
Related Solutions
You can rename an Excel sheet using the "Name" property of a sheet object. For example the following renames the first sheet of an Excel file:
filename = 'C:\SomeExcelFile.xls';% Open Excel Automation server
Excel = actxserver('Excel.Application');% Make Excel visible
Excel.Visible=1;% Open Excel file
Workbook = Excel.Workbooks.Open(filename);% Get the list of sheets in the workbook
Sheets = Excel.ActiveWorkbook.Sheets;% Rename the first sheet
Sheets.Item(1).Name = 'This is sheet 1';
The following code then saves the Workbook, quits Excel and removes the COM server:
% Save the file
Workbook.Save();% Quit Excel, remove the COM server and delete the related objects
Excel.Quit();Excel.delete();clear Excel;clear Workbook;clear Sheets;
If you want to create a plot in the excel sheet directly you need to use the ActXServer capability.
Here is a brief layout of what you're looking for. It's not going to be perfect, but it should get you started. Variable names are optional, but keep track of what they are calling.
rows = 100; % Set number of rows, if variable this gets much tougherxl = actxserver('Excel.Application'); % Open excel% set(xl,'Visible',1); % Make excel visiblexlsdir = pwd; % Set excel to work in current directoryxls = xl.Workbooks.Open('myexcelfile.xlsx'); % Open specific documentxlss = xls.Worksheets; % Define worksheet variable, for easier callingfor i = 1:50; xlssl = xlss.get('Item',i); % Select next worksheet xlssl.Activate % Activate selected worksheet xlchart = xlssl.ChartObjects.Add(xloc,yloc,xsize,ysize); % Create a chart in a defined location and size xlchart.Chart.Charttype = 'xlxyscattersmoothNoMarkers'; % Set chart type, can adjust as needed for j = 1:rows xlchart.Chart.SeriesCollection.NewSeries; % Create an new series xlchart.Chart.SeriesCollection(j).Name = seriesname; % Name the new series. Must be as string! xlchart.Chart.SeriesCollection(j).XValues = [1 2 3 4]; % Define xvalues of series xlchart.Chart.SeriesCollection(j).Values = xlssl.Range(sprintf('A%i:D%i',j,j)); % Define yvalues of series xlchart.Chart.SeriesCollection.Item(j).Format.Line.ForeColor.SchemeColor = 4; end % Useful formatting stuff xlchart.Chart.HasTitle = 1; xlchart.Chart.Axes(1).HasTitle = 1; xlchart.Chart.Axes(2).HasTitle = 1; xlchart.Chart.Axes(1).AxisTitle.Text = 'Number'; xlchart.Chart.Axes(2).AxisTitle.Text = 'Other Number'; % Set Xaxis range xlchart.Chart.Axes(1).Minimumscale = 0; xlchart.Chart.Axes(2).Minimumscale = 0; xlchart.Chart.Axes(1).MaximumScale = 3.0; % Activate grid lines xlchart.Chart.Axes(1).HasMajorGridlines = 1;endxls.Save; % Save the documentxl.Quit; % Close the documentxl.delete; % Remove Excel reference to allow the document to be opened independantly
Best Answer