如何使用Matlab在excel中找到最后一列索引

本文介绍了如何使用Matlab在excel中找到最后一列索引的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我正在使用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