How to increment the column in a for loop (When i = 1 column is Z1, when i =2 column is AC1)? Is there any commands that support R1C1 format of indexing excel cells?

Question

Sameer 2023-9-15

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2021381-how-to-increment-the-column-in-a-for-loop-when-i-1-column-is-z1-when-i-2-column-is-ac1-is-the

编辑： Stephen23 2023-10-19

Writting data into an excel file columns in side a for loop

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Stephen23 2023-9-15

编辑：Stephen23 2023-9-15

"Is there any commands that support R1C1 format of indexing excel cells?"

Excel's R1C1 indexing is really just the same as normal MATLAB subscript indexing.

For your example, just add an offset.

请先登录，再进行评论。

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

Answer 1

prabhat kumar sharma 2023-10-19

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2021381-how-to-increment-the-column-in-a-for-loop-when-i-1-column-is-z1-when-i-2-column-is-ac1-is-the#answer_1336359

在 MATLAB Online 中打开

Hi Sameer,

I understand that you are facing an issue with writing Excel data to a specific row and column in the sheet. Additionally, the (Row, Column) values should be different for each iteration of the 'for' loop.

Here's an example that addresses your requirement:

filename = 'prabhat_data.xlsx' 
sheet = 1 
data = [1 2 3 4 5]; 
for i = 1:numel(data) 
    column = char('A' + i - 1);  
    cell = [column '1'];  
    T = table(data(i));  
    writetable(T, filename, 'Sheet', sheet, 'Range', cell);  
    
end 

In this code, we generate the column letter by adding the loop index i to the ASCII value of the letter 'A' and subtracting 1. This gives us the corresponding column letter in Excel.

The 'R1C1' format cell reference is created by concatenating the column letter with the row number (in this case, '1'). If you want to change the row, you can modify the code accordingly.

We create a table T with the data value (data(i)) using the table function.

Then, we use the ‘writetable’ function to write the table to the specified cell (cell) in the Excel file, specifying the filename, sheet, and range.

For more information on the ‘writetable’ function, you can refer to the following documentation

https://mathworks.com/help/matlab/ref/writetable.html

I hope it helps!

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Stephen23 2023-10-19

编辑：Stephen23 2023-10-19

"In this code, we generate the column letter by adding the loop index i to the ASCII value of the letter 'A' and subtracting 1. This gives us the corresponding column letter in Excel. The 'R1C1' format cell reference is created by concatenating the column letter with the row number (in this case, '1')."

No, that incorrect statement mixes up R1C1 and A1 style references that Excel uses:

https://learn.microsoft.com/en-us/office/troubleshoot/excel/numeric-columns-and-rows

With R1C1 references the column "index" is numeric (as the Microsoft documentation explains) and the column letter does not change, it is always simply "C". Nothing in your answer has anything to do with R1C1 style referencing.

@prabhat kumar sharma: did you use an AI engine to generate this answer?

请先登录，再进行评论。