input Matlab table columns to excel formula and perform iteration calculation

조회 수: 2 (최근 30일)
Kaiyuan
Kaiyuan 2025년 1월 30일
댓글: Kaiyuan 2025년 2월 3일
I have two matlab tables called meandiretcion (image1) and meanspeed (image2). I also have a excel file with formula (image3), the excel formula file has 3 sheet (sheet1, sheet2, sheet3).
The aim is to perform iterative calculation on excel formula file using the value from two matlab tables.
For example, column1 of meandirection will be input to sheet1.columnF, column1 of meanspeed will be input to sheet2.columnM, results from sheet 3.columnP will be saved.
And then column2 of meandirection will be input to sheet1.columnF, column2 of meanspeed will be input to sheet2.columnM, results from sheet 3.columnP will be saved.
And then column3 of meandirection will be input to sheet1.columnF, column3 of meanspeed will be input to sheet2.columnM, results from sheet 3.columnP will be saved...
How do I perform this automatic task with Matlab?
  댓글 수: 2
dpb
dpb 2025년 1월 31일
This is a very inefficient approach; much better would be to read the data and do the calculation in MATLAB and then write all the results. The approach above would have to open/close all three files for every column instead of just once for reading/computing and once to output results.
My experience has been that trying to use readtable/writetable or related cell/matrix routines in such a tight loop can fail with the system eventually hanging as the Excel COM processes are created and closed and timing issues arise. If one were to really attempt the above in a loop, my recommendation would be to open a COM session and then do it all there before closing the output file. One could still read the two input files in one call to each file and have the data in memory for the calculations. There's really no point in updating the Excel workbook for every call; as described it will just end up with the last set in place; bring the calculation over to MATLAB and one could use vectorized operations and most likely avoid loops altogether.
Kaiyuan
Kaiyuan 2025년 2월 3일
Thanks for the detailed explanation, I will try to explore the approach you have mentioned.

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

답변 (1개)

Kaiyuan
Kaiyuan 2025년 2월 3일
As the comment above, it is inefficient to use matlab combining with excel.
Expect the calculation in Matlab directly, the other community also propose the approch which is based on VBA of excel: https://stackoverflow.com/questions/79402592/loop-through-excel-raw-data-file-and-input-them-to-other-excel-formula-sheet-and/79403925#79403925
The approach also works fine.

카테고리

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