/// <summary>
/// Excel形式的列变换
/// </summary>
public class ExcelCRHelper
{
private const string ALPHABET_UPPER = "ABCDEFGHIGKLMNOPQRSTUVWXYZ";
/// <summary>
/// 形如(B4)的单元格的列号提取
/// </summary>
/// <returns></returns>
public static int ExtractColIndex(string strColRow)
{
int iCol = -1;
Regex reg = new Regex("[A-Z]+");
Match m = reg.Match(strColRow);
if (m.Success && m.Value !=null)
{
iCol = 0;
if (m.Value.Length == 1)
{
iCol = ALPHABET_UPPER.IndexOf(m.Value);
}
else if (m.Value.Length > 1)
{
for (int i = 0; i < m.Value.Length; i++)
{
if (i == m.Value.Length - 1)
{
iCol += ALPHABET_UPPER.IndexOf(m.Value[i]);
}
else
{
int iBase = Convert.ToInt32( Math.Pow(26, m.Value.Length - i - 1));
iCol += iBase * (ALPHABET_UPPER.IndexOf(m.Value[i]) + 1);
}
}
}
}
return iCol;
}
/// <summary>
/// 形如(B4)的单元格的行号提取 /// </summary>
/// <returns></returns>
public static int ExtractRowIndex(string strColRow)
{
int iRow = -1;
Regex reg = new Regex("[\\d]+");
Match m = reg.Match(strColRow);
if (m.Success && m.Value != null)
{
try
{
iRow = Convert.ToInt32(m.Value) - 1;
}
catch
{ }
}
return iRow;
}
/// <summary>
/// 根据列号转换成列名(如:3-->D)
/// </summary>
/// <returns></returns>
public static string GetColumnName(int colIndex)
{
if (colIndex < 0)
return "";
string colName = "";
if (colIndex < 26)
{
colName += ALPHABET_UPPER[colIndex];
}
else
{
List<int> lstIndex = new List<int>();
int remainder = colIndex % 26;
colIndex = Convert.ToInt32(Math.Floor(colIndex / 26.0));
lstIndex.Add(remainder);
while (colIndex > 26)
{
remainder = colIndex % 26;
colIndex = Convert.ToInt32(Math.Floor(colIndex / 26.0));
lstIndex.Add(remainder);
}
if (colIndex > 0)
{
colName += ALPHABET_UPPER[colIndex - 1];
}
for (int i = lstIndex.Count - 1; i >= 0; i--)
{
if (i == 0)
{
colName += ALPHABET_UPPER[lstIndex[i]];
}
else
{
colName += ALPHABET_UPPER[lstIndex[i] -1];
}
}
}
return colName;
}
}
Excel单元格列名转化成函数
最新推荐文章于 2020-12-19 20:11:13 发布