Writing data to excel within a variable range

16 次查看（过去 30 天）
Matthew
Matthew 2012-6-21
评论： Nam Hoang Le 2014-7-16
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
Walter Roberson 2012-6-21
http://www.mathworks.com/matlabcentral/answers/13205-tutorial-how-to-format-your-question-with-markup

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Adam Filion
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 个评论
显示 1更早的评论
Adam Filion
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
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 中查找有关 Use COM Objects in MATLAB 的更多信息

标签

产品

Community Treasure Hunt

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

Start Hunting!

Translated by