How can I write data to excel sheet in the next column automatically when my program runs for the 2nd time, 3rd time and so on while still having the previous runtime computations saved in previous columns of excel file
조회 수: 29 (최근 30일)
이전 댓글 표시
function []=calculate(a,b,c,d)
% code
calculation(1)=a*b;
calculation(2)=a+b+c+d;
calculation(3)=a*b*c*d;
calculation(4)=(a+b)-(c+d);
calculation = calculation'; %transpose of calculation array
excelfile= 'test.xlsx';
xlswrite(excelfile,calculation,'C4');
end
My actual code consists of a function which accepts 5 arguments and returns 14 values in an array similar to the code posted here. My problem is that each time I call the calculate() function it writes the data into test.xlsx file at column C starting from row4, but it overwrites the previously calculated data stored from C4. I want that when the 2nd time function calculate() is called the data should be written from column D and row4, for the third time to E4 and so on. Please help me with a solution.
댓글 수: 1
madhan ravi
2018년 7월 22일
Yes because you have specified C4 column in the function? Maybe it’s better you change it each time while calling the function would be a better idea in my opinion.
채택된 답변
Shreyansh Dubey
2018년 7월 25일
댓글 수: 1
Image Analyst
2018년 7월 25일
Keep in mind that this is not general, like my solution, and only works if you keep track of which columns you're writing. If you open an existing spreadsheet, your code would overwrite existing cells unless you call xlsread() like I did to figure out where the existing data ends. But if you start with a new workbook, and keep track of everywhere you write, then this should be fine, and probably a little faster than using xlsread() every time.
추가 답변 (2개)
dpb
2018년 7월 22일
Amplifying on madhan ravi's comment...
function []=storecalculate(a,b,c,d,excelfile,location)
...
xlswrite(excelfile,calculation,location);
remove the specifics from the routine--above shows one way (probably not the best factorization) is to pass the filename and the start location to the function and do the bookkeeping at the calling level to know which location is the desired one.
Perhaps(probably?) the better way would be to refactor on a more general level and vectorize to do all the computations and then write only once; xlswrite is a very high-overhead operation so calling it repetitively in a loop will result in noticeable latency in the application. If this only a couple columns or so, that probably won't be a big problem but if it's hundreds or thousands, you could have a long(ish) wait!
댓글 수: 2
madhan ravi
2018년 7월 22일
편집: madhan ravi
2018년 7월 22일
Thank you @dpb, a lot learnt from your answer.
Image Analyst
2018년 7월 23일
Right before you write an additional column, I'd read the existing workbook and get it's size
[numbers, strings, raw] = xlsread(filename);
Then compute how many columns are already in it
numColumns = size(raw, 2)
Then make a cell reference to one column past that, numColumns+1. You might need to use something like this routine https://www.mathworks.com/matlabcentral/fileexchange/15748-excel-column-number-to-column-name or excel2col() or similar, to turn column numbers into column letters, especially when you go past column Z. So do this:
excelColumnLetters = excel2col(excelColumnLetters);
cellReference = sprintf('%s1', excelColumnLetters); % Write to next column, row 1.
Then call xlswrite with that cell reference:
xlswrite(xlFileName, yourNewColumnData, sheetName, cellReference);
댓글 수: 4
참고 항목
카테고리
Help Center 및 File Exchange에서 Spreadsheets에 대해 자세히 알아보기
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!