将Excel列字母转换为相应的列号 [英] Converting Excel column letters to corresponding column numbers
问题描述
我正在创建一个类来协助创建excel文件（第二个问题）。我有一个成员函数在特定的行和列位置创建一个单元格，并将其保存到数组中。
I'm creating a class to assist in creating excel files (second question). I have a member function that creates a cell at a specific row and column location and saves it to an array.
ExcelSheet::SetValue(int row, int column, std::string szValue) {
myWorksheet[row][column] = szValue;
}
但是，我正在重载该功能，并将列指定为字母/数字组合，如Excel。我想要将输入A3转换为行3列1或AB19到第19列第28列。IE，
But I'm overloading that function with the option of specifying the column as a letter/number combination, as in Excel. I want to be able to convert inputs like "A3" to Row 3 Column 1, or AB19 to Row 19 Column 28. I.E.,
ExcelSheet::SetValue(std::string szCell, std::string value) {
// search for the alphabetical part of szCell using regex and
// convert it to a column number
myWorksheet[row][column] = szValue;
}
Excel列模式如下：
The Excel column pattern goes like this:
A, B, C, D, E... Z (1-26)
AA, AB, AC, AD, AE... AZ (27-52)
BA, BB, BC, BD, BE... BZ (53-78)
...
ZA, ZB, ZC... ZZ
AAA, AAB, AAC... AAZ
ABA, ABB, ABC... ABZ
...
AZA, AZB, AZC... AZZ
BAA, BAB, BAC... BAZ
...
所以我在想一些基于26的循环会做的伎俩，但是我甚至不知道在实现它的时候从哪里开始。
So I'm thinking some sort of 26-based loop would do the trick, but I'm not even sure where to start when it comes to implementing it.
我的几乎工作的代码：它编译，但是由于一些不清楚的原因，没有正确的排列。
My almost-working code: It compiles, but does not get the row correctly for some unclear reason.
#include <vector>
#include <string>
#include <iostream>
#include <regex>
class VectorClass {
std::vector<std::string> vectorString;
public:
void PrintVector(std::string szValue) {
std::string str = "space";
vectorString.push_back(szValue);
vectorString.push_back(str);
std::cout << "Loop:" << std::endl;
for(int i=0; i < vectorString.size(); i++) {
std::cout << vectorString[i] << std::endl;
}
}
int GetColumn(std::string szValue) {
std::regex rgx("^([a-zA-Z]+)");
std::smatch result;
if( regex_search(szValue, result, rgx) ) {
std::string szResult = result[0];
int numletters = szResult.length();
const char * colstr = szResult.c_str();
//char colstr[5];
//int numofletters = 0;
//for(int n=0;n<szResult.length();n++) {
// colstr[n] = szResult.substr(n,1);
// numofletters++;
//}
//char colstr[]="AEF";
int col=0;
for(int i=0; i<numletters; i++) {
col = 26*col + colstr[i] - 'A' + 1;
}
return col;
//return atoi(szResult.c_str());
}
else
return -1;
}
int GetRow(std::string szValue) {
std::regex rgx("^[a-zA-Z]+(\d+)$");
std::smatch result;
if( regex_search(szValue, result, rgx) ) {
std::cout << "test: " << result.size() << std::endl;
for(size_t i=0; i<result.size(); ++i) {
std::cout << result[i] << std::endl;
}
std::string szResult = result[0];
return atoi(szResult.c_str());
}
else
return -1;
}
};
int main () {
VectorClass myclass;
//myclass.PrintVector("ship");
std::cout << "A1 = Column " << myclass.GetColumn("A1") << ", Row " << myclass.GetRow("A1") << std::endl;
std::cout << "B32 = Column " << myclass.GetColumn("B32") << ", Row " << myclass.GetRow("B32") << std::endl;
std::cout << "Z65 = Column " << myclass.GetColumn("Z65") << ", Row " << myclass.GetRow("Z65") << std::endl;
std::cout << "AA12 = Column " << myclass.GetColumn("AA12") << ", Row " << myclass.GetRow("AA12") << std::endl;
std::cout << "AB366 = Column " << myclass.GetColumn("AB366") << ", Row " << myclass.GetRow("AB366") << std::endl;
std::cout << "FAB43 = Column " << myclass.GetColumn("ZAB43") << ", Row " << myclass.GetRow("ZAB43") << std::endl;
std::cout << "ZZZ43456 = Column " << myclass.GetColumn("XDE43456") << ", Row " << myclass.GetRow("XDE43456") << std::endl;
std::cin.get();
return 0;
}
推荐答案
请注意， ASCII值，您可以从以下列中获取列：
Note that a character has an ASCII value and you can get the column from the following:
char colstr[]="AEF";
int ii, col=0;
for(ii=0; ii<3; ii++) {
col = 26*col + colstr[ii] - 'A' + 1;
}
需要注意的几件事：
- 我使用char [] - 一个字符数组 - 用于存储字符串。这使得访问ASCII值变得微不足道。
- 我将循环连接到3 - 如果您的列标签长度不同，您可能需要解决
- 单引号中的值A是字符A，因此ASCII值
A few things to note: - I use char[] - a character array - for storing the string. This makes accessing the ASCII value trivial. - I hard wired the loop to 3 - if your column labels are different lengths you might want to address that - the value 'A' in single quotes is the character "A" and thus the ASCII value 65.
编辑：由于您似乎无法获取行值，所以这里是一个非常简单的（C）函数，可以让你们一起显示，同时显示一些代码几个例子。我无法得到您的代码编译 - 显然我没有在我的机器上的库....我假设标签传递给函数是很好的形式：只有字母后面只有数字。它使用 toupper（）函数处理小写字母（注意 - 这是你的代码没有做的...）。我弄清楚最后一封信是在哪里，然后在两部分上进行操作。我在行和 col 参数指向的位置中返回行和列的值。
since you seemed to have trouble getting the row value, here is a very simple (C) function that will get you both - together with some code showing a few examples. I couldn't get your code to compile - apparently I don't have the library on my machine.... I am assuming that the label passed to the function is well formed: only letters followed by only numbers. It does handle lowercase letters with the toupper() function (note - this is something your code didn't do...). I figure out where the last letter is, then operate on the two parts. I return the values for row and column in the locations pointed to by row and col parameters.
#include <stdio.h>
#include <stdlib.h>
void rc(char * cellAddr, int *row, int *col) {
int ii=0, jj, colVal=0;
while(cellAddr[ii++] >= 'A') {};
ii--;
// ii now points to first character of row address
for(jj=0;jj<ii;jj++) colVal = 26*colVal + toupper(cellAddr[jj]) -'A' + 1;
*col = colVal;
*row = atoi(cellAddr+ii);
}
int main() {
int row, col;
char cellAddr1[] = "A123";
char cellAddr2[] = "aB321";
char cellAddr3[] = "ABCA6543";
rc(cellAddr1, &row, &col);
printf("for %s the row is %d and the column is %d\n", cellAddr1, row, col);
rc(cellAddr2, &row, &col);
printf("for %s the row is %d and the column is %d\n", cellAddr2, row, col);
rc(cellAddr3, &row, &col);
printf("for %s the row is %d and the column is %d\n", cellAddr3, row, col);
}
这给我的输出是：
for A123 the row is 123 and the column is 1
for aB321 the row is 321 and the column is 28
for ABCA6543 the row is 6543 and the column is 19007
这篇关于将Excel列字母转换为相应的列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
