Writing data to excel within a variable range
10 次查看(过去 30 天)
显示 更早的评论
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 个评论
Walter Roberson
2012-6-21
http://www.mathworks.com/matlabcentral/answers/13205-tutorial-how-to-format-your-question-with-markup
采纳的回答
Adam Filion
2012-6-21
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 个评论
Adam Filion
2012-6-21
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
2014-7-16
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
更多回答(0 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 ActiveX 的更多信息
产品
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!