EXCEL中如何求得单元格所在列的标题字母
用上OFFICE2007后,从第703列开始,EXCEL的列标题有3个字母组成(从 AAA 到 XFD 止,相应的单元格表示为:AAA1,AAA2,……)。在VFP对EXCEL的操作中,如:测得最后一个数据所在列为 1378,对应的列标号是 AZZ。 对应单元格就为:AZZ1,AZZ,……,AZZn,……
请问 VF 如何用 1378 转换出 AZZ 来?
Function GetColumName(lie0 As Integer) As String '根据列序号,返回列字母名,本模块是数学方式计算。 'WDB [2014.10.11] Dim str1 As String, lie1, lie2, liew As Integer, iMsg As Integer Const STR0 = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" If lie0 > 0 And lie0 <= 16384 And lie0 - Int(lie0) = 0 Then lie0 = Int(lie0) '最大支持:XFD=16384 Select Case lie0 Case Is <= 26 '1位字母 str1 = Mid(STR0, lie0, 1) Case 27 To 702 '2位字母 lie2 = (lie0 - 1) \ 26 liew = IIf(lie0 Mod 26 = 0, 26, lie0 Mod 26) str1 = Mid(STR0, lie2, 1) & Mid(STR0, liew, 1) Case Is > 702 '3位字母 lie1 = Int((Int((lie0 - 1) / 26) - 1) / 26) Mod 26 lie2 = IIf(Int((lie0 - 1) / 26) Mod 26 = 0, 26, Int((lie0 - 1) / 26) Mod 26) liew = IIf(lie0 Mod 26 = 0, 26, lie0 Mod 26) str1 = Mid(STR0, lie1, 1) & Mid(STR0, lie2, 1) & Mid(STR0, liew, 1) End Select Else str1 = "数值不是合理值(负数、小数或>16384)!" End If GetColumName = str1 End Function
CREATE CURSOR T1 (xh n(5), zm c(3)) FOR lnj = 1 TO 16384 INSERT INTO t1 VALUES (lnj, SZTOZM(lnj)) ENDFOR BROWSE FUNCTION SZTOZM PARAMETERS ln IF NOT BETWEEN(ln, 1, 16384) RETURN "" ENDIF LOCAL ln0, ln1, ln2, ln3 STORE 0 TO ln0, ln1, ln2, ln3 ln0 = CEILING(ln / 26) - 1 ln1 = IIF(ln0 > 26, CEILING(ln0 / 26) - 1, 0) ln2 = IIF(ln0 > 26, ICASE(MOD(ln0, 26) = 0, 26, MOD(ln0, 26)), ln0) ln3 = IIF(MOD(ln, 26) = 0, 26, MOD(ln, 26)) RETURN IIF(ln1 = 0, "", CHR(ln1 + 64)) + IIF(ln2 = 0, "", CHR(ln2 + 64)) + CHR(ln3 + 64) ENDFUNC RETURN