Writing data to excel within a variable range

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.

[Walter Roberson]

http://www.mathworks.com/matlabcentral/answers/13205-tutorial-how-to-format-your-question-with-markup

Answer 1

[Adam Filion]

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.

[Matthew]

Thanks, I tried something similar that came close but I was missing the num2string part. Is thee a good place to get more info on actxserver then what is in the product documentation. I know it can do a lot more than what I am seeing in there.

[Adam Filion]

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]

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