Effacer les filtres
Effacer les filtres

Writing data to excel within a variable range

8 vues (au cours des 30 derniers jours)
Matthew
Matthew le 21 Juin 2012
Commenté : Nam Hoang Le le 16 Juil 2014
I'm working on a program that filters the tidal signal out of river flow data. I bring the flow data in from excel and filter it using a butterworth filter. Then I need to write the filtered data back to excel. My problem is that i don't necessarily know how many data points there are so I need the range that I write to in excel to be a variable. I can use xlsread to bring in the entire file but i want to use a com server to write it back to excel. I also want to be able to make the titles in bold and I haven't found a function for that. Here is what I have done so far:
%-------------------------------------------------------------------------% %Import data from excel spread sheet datafile1 = uigetfile; [nums,date,data] = xlsread(datafile1); count1 = numel(nums); count1 = count1 + 1; %-------------------------------------------------------------------------% % Butterworth Lowpass filter designed using FDESIGN.LOWPASS.
% All frequency values are in Hz. Fs = 1; % Sampling Frequency this is just to simplify filtering and has %nothing to do with the actual data sampling frequency of the file
Fpass = 0.00625; % Passband Frequency Fstop = 0.00833; % Stopband Frequency Apass = 1; % Passband Ripple (dB) Astop = 9; % Stopband Attenuation (dB) match = 'passband'; % Band to match exactly
% Construct an FDESIGN object and call its BUTTER method. h = fdesign.lowpass(Fpass, Fstop, Apass, Astop, Fs); Hd = design(h, 'butter', 'MatchExactly', match); y = filtfilt(Hd.sosMatrix,Hd.ScaleValues,nums); %-------------------------------------------------------------------------% %Open a server connection to excel then write the data to %a new excel file
% First, open an Excel Server. e = actxserver('Excel.Application');
% Insert a new workbook. eWorkbook = e.Workbooks.Add; e.Visible = 1;
% Make the first sheet active. eSheets = e.ActiveWorkbook.Sheets;
eSheet1 = eSheets.get('Item', 1); eSheet1.Activate; % Put titles into Excel. A = 'Date and Time'; eActivesheetRange = e.Activesheet.get('Range', 'A1'); eActivesheetRange.Value = A; B = 'Flow'; eActivesheetRange = e.Activesheet.get('Range', 'B1'); eActivesheetRange.Value = B; C = 'Filtered Flow'; eActivesheetRange = e.Activesheet.get('Range', 'C1'); eActivesheetRange.Value = C;
% Put MATLAB data back into Excel. eActivesheetRange = e.Activesheet.get('Range', 'A2:A65535' ); eActivesheetRange.Value = date; eActivesheetRange = e.Activesheet.get('Range', 'B2:B65535' ); eActivesheetRange.Value = nums; eActivesheetRange = e.Activesheet.get('Range', 'C2:C65535' ); eActivesheetRange.Value = y;
% Now, save the workbook. eWorkbook.SaveAs('FilteredData.xls');
% Avoid saving the workbook and being prompted to do so eWorkbook.Saved = 1; %eWorkbook.Close;
% Quit Excel and delete the server. %e.Quit; %e.delete;
In the part where I put the data back into excel I don't want to have to specify the range I want the range to be from for example A2 through count1. it works as it is but it puts N/A for all of the empty cells after the data is filled in and I would like to avoid that.
  1 commentaire
Walter Roberson
Walter Roberson le 21 Juin 2012
http://www.mathworks.com/matlabcentral/answers/13205-tutorial-how-to-format-your-question-with-markup

Connectez-vous pour commenter.

Réponse acceptée

Adam Filion
Adam Filion le 21 Juin 2012
If all you're looking for is to not manually specify the range of the data you are writing, then you can do this fairly easily directly in MATLAB as below.
data = [12.7, 5.02, -98, 63.9, 0, -.2, 56]';
xlswrite('testdata.xls', data, ['D1:D' num2str(length(data))] )
Using string concatenation will let you dynamically specify the range based on the size of the data.
However if you want to automatically make it bold then I think the only way is to go through actxserver, I don't know of a way to do it otherwise.
  3 commentaires
Adam Filion
Adam Filion le 21 Juin 2012
Unfortunately I know very little about dealing with the COM server directly and the best I can do is point you to the doc.
Nam Hoang Le
Nam Hoang Le le 16 Juil 2014
Hi there, any way to write SUM function of excel from matlab,
Ex : I want to SUM from G2:G10, of course in excel is =SUM(G2:G10). but how to write it from MATLAB.
Thank you so much

Connectez-vous pour commenter.

Plus de réponses (0)

Catégories

En savoir plus sur Use COM Objects in MATLAB dans Help Center et File Exchange

Produits

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by