Adding data into existing excel sheet from matlab

조회 수: 17 (최근 30일)
Rekha
Rekha 2013년 5월 3일
댓글: vickey vardwaj 2021년 8월 19일
I have an excel sheet 'myvalues.xlsx' cosisting of 200 vectors of 127bits each. Now I want to add another vector to my excel sheet which will be placed as the 201th row without changing the existing data. How can I acheive it. Have tried xlswrite but its not working.
  댓글 수: 2
syahdan edy murad
syahdan edy murad 2018년 7월 6일
i have the same problem here, could you include your code ? like saving the excel file, and adding it after you execute the file again.
vickey vardwaj
vickey vardwaj 2021년 8월 19일
how can we add a row at 100th row and the old 100th row shifts to 101th row?

댓글을 달려면 로그인하십시오.

채택된 답변

Mark Whirdy
Mark Whirdy 2013년 5월 6일
편집: Mark Whirdy 2013년 5월 6일
It seems that variable "mydata" is of size [1x3] but the size of the range you elected is [1x1] (i.e. a single excel cell rather than a range of cells) in
xlCurrRange = xlsheet.Range(['A' num2str(newRange)]);
% ['A' num2str(newRange)] = 'A301' since newRange appears to be the scalar 1 which is added to 300
I think that instead of 'A301' you want to have 'A301:C301' which is
xlsheet.Range(['A', num2str(newRange),':C', num2str(newRange)]);
which should capture the full [1x3] size of "mydata"
Is this it or have I misunderstood?
  댓글 수: 1
Rekha
Rekha 2013년 5월 6일
Works perfectly !
This is exactly what is needed. Guess I wasn't clear in my problem statements earlier..
Thank you Mark...

댓글을 달려면 로그인하십시오.

추가 답변 (1개)

Mark Whirdy
Mark Whirdy 2013년 5월 3일
편집: Mark Whirdy 2013년 5월 3일
Hi Rekha
Lots of similar questions on the Matlab Answers here if you search.
Here's one of mine on using the activex server to manipulate excel range values from matlab. In general there's no reason to use xlsread or xlswrite
Use commands like:
mydata = rand(100,3);
xlCurrRange = xlActiveSheet.Range('A201:C300');
xlCurrRange.Value2 = mydata;
google "actxserver" - no shortage of other examples on the web
Alternatively, have a look at Alec de Zegher's approach
All the best
Mark
  댓글 수: 4
Rekha
Rekha 2013년 5월 5일
Hi Mark,
I tried the code given by you. It works perfectly if am writing the mydata generated into the Range ('A201:C300').
Without considering how i'm arriving at the sparseVector or its contents, speaking only with reference to the above code, the problem am encoutering is:
Upon executing your code once my active excel-sheet(which is myvalues.xlsx in this case) will have matrix in the range 'A201:C300'. Before running the code for the second time I change,
mydata=randn(100,3);
to
mydata=randn(1,3);
I want my excel-sheet to get updated with mydata, generated during the second run written into the 301th row and positioned in the range 'A301:C301'. For the third run mydata must be entered into 302th row ('A302:C302') and so on. I have tried to do so, with small modifications in the code provided by you.
xlApp = actxserver('Excel.Application');
xlApp.visible = 1;
%Open the the spreadsheet
xlworkbook = xlApp.Workbooks.Open('myvalues.xlsx');
xlsheet = xlworkbook.ActiveSheet;
mydata=randn(1,3);
data=xlsread('myvalues.xlsx');
%Determine last row
last=size(data,1);
newRange=last+1;
xlCurrRange = xlsheet.Range(['A' num2str(newRange)]);
xlCurrRange.Value2 = mydata;
%Save and Close the Excel File
invoke(xlworkbook,'Save');
invoke(excelApp,'Quit');
delete(excelApp);
The problem is only the first element in mydata(i.e.mydata(1,1)) is being written into excel-sheet during each run but not the entire value.
Not able to figure out what's missing.
How can this be corrected.
Adithya Pillai
Adithya Pillai 2020년 6월 25일

Hi Rekha Try using a while loop to update the range after writing each row/column into the excel file

댓글을 달려면 로그인하십시오.

카테고리

Help CenterFile Exchange에서 Spreadsheets에 대해 자세히 알아보기

태그

Community Treasure Hunt

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

Start Hunting!

Translated by