input Matlab table columns to excel formula and perform iteration calculation

29 ビュー (過去 30 日間)
Kaiyuan
Kaiyuan 2025 年 1 月 30 日 17:53
コメント済み: Kaiyuan 2025 年 2 月 3 日 16:54
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 日 18:55
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 日 16:54
Thanks for the detailed explanation, I will try to explore the approach you have mentioned.

サインインしてコメントする。

回答 (1 件)

Kaiyuan
Kaiyuan 2025 年 2 月 3 日 16:45
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 Center および File ExchangeSpreadsheets についてさらに検索

Community Treasure Hunt

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

Start Hunting!

Translated by