I have an excel file with 50 sheets. Each excel sheet has 4 coloumns. I want to plot graph in each excel sheet using 4 coloumns as 4 series of data. How is it possible.
MATLAB: Plots in excel sheets using matlab
plots
Related Solutions
I have found the solution with
Excel.ActiveChart.ChartType = 65; can not make 2 X-axis . I did that on ChartType = 73 and worked very wellHere the list of chart type
hier ist mein end Code
Excel = actxserver('excel.application'); % Get Workbook object
WB = Excel.Workbooks.Open(outname); % Get neu Worksheets object
WS = WB.Worksheets; WS.Add([], WS.Item(WS.Count)); Sheet=WB.Worksheets.Item('Ergebnis'); Charts = WB.Charts; Chart = invoke(Charts,'Add'); invoke(Excel.ActiveChart.SeriesCollection,'NewSeries'); Excel.ActiveChart.SeriesCollection(1).Name = 'Temperatur der Erdr'; Excel.ActiveChart.SeriesCollection(1).XValues=Sheet.Range(strcat('B3:B',num2str(C)));Excel.ActiveChart.SeriesCollection(1).Values =Sheet.Range(strcat('A3:A',num2str(C))); invoke(Excel.ActiveChart.SeriesCollection,'NewSeries'); Excel.ActiveChart.SeriesCollection(2).Name = 'Temperatur der CO2'; Excel.ActiveChart.SeriesCollection(2).XValues=Sheet.Range(strcat('C3:C',num2str(C)));Excel.ActiveChart.SeriesCollection(2).Values=Sheet.Range(strcat('A3:A',num2str(C))); invoke(Excel.ActiveChart.SeriesCollection,'NewSeries'); Excel.ActiveChart.SeriesCollection(3).Name = 'Temperatur der Wand'; Excel.ActiveChart.SeriesCollection(3).XValues=Sheet.Range(strcat('I3:I',num2str(C)));Excel.ActiveChart.SeriesCollection(3).Values=Sheet.Range(strcat('A3:A',num2str(C))); % Setting the Chart Sheet Title & Chart Title.
Excel.ActiveChart.HasTitle = 1; Excel.ActiveChart.ChartTitle.Characters.Text = 'Temperatur'; Excel.ActiveChart.Name = 'Temperatur'; % Setting the (X-Axis) and (Y-Axis) titles.
ChartAxes = invoke(Chart,'Axes',1); set(ChartAxes,'HasTitle',1); set(ChartAxes.AxisTitle,'Caption','Temperatur /°C'); ChartAxes = invoke(Chart,'Axes',2); set(ChartAxes,'HasTitle',2); set(ChartAxes.AxisTitle,'Caption','Tiefe /m'); Excel.ActiveChart.ChartType = 73; temp=[handles.T,handles.T_E,handles.Twz]; % Setting the (X-Axis) Scale
Excel.ActiveChart.Axes(1).Select; Excel.ActiveChart.Axes(1).MinimumScale = min(temp(:)); Excel.ActiveChart.Axes(1).MaximumScale = max(temp(:)); % Setting the (Y-Axis) Scale
Excel.ActiveChart.Axes(2).Select; Excel.ActiveChart.Axes(2).MinimumScale = 0; Excel.ActiveChart.Axes(2).MaximumScale = max(handles.tiefe(:,C)); Excel.ActiveChart.Axes(2).ReversePlotOrder = true; WB.Save(); WB.Close;
There are two ways of doing this. You can either use xlsread and load each set of data into matlab one sheet at a time, or you can use actX and do much the same thing. Personally, because you have so many sheets and files, I would suggest using actX because it doesn't require you to open an close excel 500 times.
Examples of both methods:
files = dir('*.xlsx'); % Get list of files; this does assume you are working directly in the directory which contains them%% xlsread methodfor i = 3:length(files) for j = 1:50 data(:,:,j,i) = xlsread(files(i).name,j); endend%% actx methodxl = actxserver('Excel.Application'); % Start Excel% set(xl,'Visible',1); % Make excel visiblexlsdir = pwd; % Work in current directoryfor i = 3:length(files) xls = xl.Workbooks.Open(files(i).name); % Open specific document; may need to use full file path xlss = xls.Worksheets; for j = 1:50 xlssl = xlss.get('Item',j); colEnd = xlssl.Range('A1').End('xlToRight').Column; rowEnd = xlssl.Range('A1').End('xlDown').Row; range = ['A1:',char('A'-1+rem),char('A'-1+rem2),num2str(rowEnd)]; Range = get(eSheet1,'Range',range); data(:,:,j,i) = Range.Value; endend%% Reorganizing things for one sheetfor j = 1:50 out = [data(:,:,j,:)]; % Preferred print to file method, xlswrite, or actx xlswrite('myoutfile.xlsx',out,j); % or xls = xl.WorkBooks.Open('myoutfile.xlsx'); % Should move this outside the loop, no need to repeat xlssl = xls.Worksheets.get('Item',j); range = 'A1:XX100'; % You will need to find your own range Range = get(xlssl,'Range',range); Range.Value = out;end
That should give you a sterting point from whichever method you prefer. Note that I assumed all of your data was the same size. If not, you will need to adjust your matlab data storage variable (I called it data) to be able to accomodate the different sizes.
Related Question
- How to rename a sheet in Excel using the COM interface in MATLAB
- How to create chart objects in Excel from MATLAB 7.8 (R2008a)
- How to set the ‘HasAxis’ property of the Excel Chart object using ActiveX in MATLAB 7.11 (R2010b)
- Color the particular row of the excel sheet
- How to add a Header or a Footer to an EXCEL worksheet using MATLAB 7.7 (R2008b)
Best Answer