如何使用Matlab在excel中找到最后一列索引 [英] How to find the last column index in excel with Matlab

问题描述

我正在使用Matlab将数据作为矩阵写入excel文件。我的矩阵维度有所不同，我不知道矩阵的维数。假设我有不同维度的矩阵A，B和C。我想做的是将矩阵A写入excel，并在A之后写入矩阵B，并在它们之间留出1列间隙。我不知道是否有一种方法，Matlab可以在excel中找到最后一列索引，并创建这两个矩阵之间的差距。

我的代码现在是这样的：

  xlswrite（'我的文件xls'，A，'我的工作表'，'B2'）; 
 
 %% 
这是我不知道填写以找到我的下一个矩阵的起始范围索引。 
 %% 
 
 xlswrite（'My file.xls'，B，'My Sheet'，'newindex'）; 
  

解决方案

根据索引号计算Excel列ID我做了以下功能：

  function col = excel_column（n）
 
％//查找LSB 
 xlsb = mod（n-1,26）+1; 
 xmsb = fix（（n-1）/ 26）; 
 
％//查找MSB（必要时递归）
如果xmsb> = 1 
 col = [excel_column（xmsb）char（xlsb + 64）]; 
 else 
 col = char（xlsb + 64）; 
 end 
  

这将适用于任何数字，但请注意Excel的最大数量的列（ 2 ^ 14 = 16384 我的版本中的列最大值）。例如，为了表明它处理信函增加，您可以运行简短测试：

 >> x = [25:28 233：236 700：705 16383：16385]; 
 for n = x 
 fprintf（'Index：％5d =>列：％s\\\
'，n，excel_column（n））
 end 
 
指数：25 =>列：Y 
索引：26 =>列：Z 
索引：27 =>栏：AA 
索引：28 =>列：AB 
索引：233 =>列：HY 
索引：234 =>列：HZ 
索引：235 =>列：IA 
索引：236 =>列：IB 
索引：700 =>列：ZX 
索引：701 =>列：ZY 
索引：702 =>列：ZZ 
索引：703 =>列：AAA 
索引：704 =>列：AAB 
索引：705 =>列：AAC 
索引：16383 =>列：XFC 
索引：16384 =>列：XFD％//最后一列
索引：16385 =>专栏：XFE％//小心此列不存在于Excel 
  

---

所以在你的情况下，您开始在列$ 'B ...'中编写矩阵 A ，这是列索引 2 。
要知道从哪里开始您的矩阵 B ，只需计算 A 的大小，并添加必要的差距。
假设您的矩阵 A 有573列。

  startIdx_A = 2; ％//矩阵A从列索引2开始
 ncA = size（A，2）; ％// A中的列数，应返回573 
 columnGap = 1; ％//A和B之间有多少差距
 
 startColumnMatrixB_index = startIdx + ncA + columnGap; ％// MatrixB第一列的索引
 startColumnMatrixB_excel = excel_column（startColumnMatrixB_index）; ％// return'VD'（假设A有573列）
  

---

如果您的矩阵非常大（列数），那么在调用 xlswrite

I am writing data as matrices into excel file using Matlab. My matrices' dimension varies and I don't know the matrices' dimension in advance. Let's say I have matrix A, B, and C of various dimensions. What I want to do is to write matrix A into excel first and write matrix B after A with 1 column of gap between them. I don't know if there is a way Matlab could find the last column index in excel and create a gap between these two matrices.

My code is something like this now:

xlswrite('My file.xls',A,'My Sheet','B2');

%%
Here is what I don't know to fill in to find the starting range index for my next matrix. 
%%

xlswrite('My file.xls',B,'My Sheet','newindex');

解决方案

To calculate an Excel column ID based on an index number I made the following function:

function col = excel_column(n)

    %// find LSB
    xlsb = mod(n-1,26)+1 ;
    xmsb = fix((n-1)/26) ;

    %// find MSB (recursively if necessary)
    if xmsb >= 1
        col = [excel_column(xmsb) char(xlsb+64)] ;
    else
        col = char(xlsb+64) ;
    end

This will work for any number, but be careful that Excel has a maximum number of column (2^14=16384 columns max in my version). As an example, to shows that it handles the letter incrementation, you can run the short test:

>> x = [25:28 233:236 700:705 16383:16385] ;
for n=x
    fprintf('Index: %5d => Column: %s\n', n , excel_column(n) )
end

Index:    25 => Column: Y
Index:    26 => Column: Z
Index:    27 => Column: AA
Index:    28 => Column: AB
Index:   233 => Column: HY
Index:   234 => Column: HZ
Index:   235 => Column: IA
Index:   236 => Column: IB
Index:   700 => Column: ZX
Index:   701 => Column: ZY
Index:   702 => Column: ZZ
Index:   703 => Column: AAA
Index:   704 => Column: AAB
Index:   705 => Column: AAC
Index: 16383 => Column: XFC
Index: 16384 => Column: XFD %// Last real column
Index: 16385 => Column: XFE %// Careful. This column DOES NOT exist in Excel

---

So in your case, You start to write your matrix A at column 'B...', which is column index 2. To know where to start your matrix B, simply calculate the size of A and add the necessary gap. Let's say your matrix A has 573 columns.

startIdx_A   = 2 ;         %// Matrix "A" started at column index 2
ncA          = size(A,2) ; %// Number of column in A, should return 573
columnGap    = 1 ;         %// how much gap you want between "A" and "B"

startColumnMatrixB_index = startIdx + ncA + columnGap ; %// index of the first column for Matrix "B"
startColumnMatrixB_excel = excel_column(startColumnMatrixB_index) ; %// return 'VD' (assuming A had 573 columns)

---

If your matrices are very large (in number of columns), it would be prudent to include a check to make sure you won't run out of column before you call the xlswrite

这篇关于如何使用Matlab在excel中找到最后一列索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！