根据数值获取Excel列字母的FORM

*&---------------------------------------------------------------------*
*& Report  Z_BARRY_EXCEL_COL_NAME
*&
*&---------------------------------------------------------------------*
*& 根据数值获取Excel列字母的FORM
*& 如：1 -> A
*&     2 -> B
*&     27 -> AA
*&---------------------------------------------------------------------*

REPORT  z_barry_excel_co_name NO STANDARD PAGE HEADING.

PARAMETERS: input TYPE i.
DATA: col_name(2) TYPE c .

START-OF-SELECTION.
  DO 705 TIMES .
    CLEAR col_name.
    PERFORM get_col USING sy-index
                          col_name .
    WRITE / col_name.
  ENDDO.
*&---------------------------------------------------------------------*
*&      Form  get_col
*&---------------------------------------------------------------------*
FORM get_col USING col TYPE i
                   col_name TYPE c .
  DATA: str TYPE string ,
        xstr TYPE xstring,
        l_con TYPE REF TO cl_rpe_convert .
  DATA: mod TYPE i ,
        div TYPE i .

  IF col > 702  OR col < 1 .
    MESSAGE '超过范围，FORM退出！' TYPE 'I'.
    EXIT.
  ENDIF.

  CREATE OBJECT l_con.

  mod = col MOD 26 .
  div = col DIV 26 .

  IF div > 0 AND mod <> 0.
    xstr = div + 64 .
  ELSEIF div > 1 AND mod = 0  .
    xstr = div + 63 .
  ENDIF.

  CALL METHOD l_con->xstring_to_string
    EXPORTING
      input  = xstr
    IMPORTING
      output = str.
  col_name = str .

  IF mod = 0 .
    str = 'Z'.
  ELSE.
    xstr = mod + 64 .
    CALL METHOD l_con->xstring_to_string
      EXPORTING
        input  = xstr
      IMPORTING
        output = str.
  ENDIF.

  CONCATENATE col_name str INTO col_name.
ENDFORM.                    "get_col

或者：

*
DATA: col_name(2) TYPE c .
FIELD-SYMBOLS : TYPE c.

START-OF-SELECTION.
  DO 705 TIMES .
    CLEAR col_name.
    PERFORM get_col USING sy-index
                          col_name .
    WRITE / col_name.
  ENDDO.
*&---------------------------------------------------------------------*
*&      Form  get_col
*&---------------------------------------------------------------------*
FORM get_col USING col TYPE i
                   col_name TYPE c .
  DATA: xc(4) TYPE x .
  DATA: mod TYPE i ,
        div TYPE i .
  ASSIGN xc TO CASTING  .

  IF col > 702  OR col < 1 .
    EXIT.
  ENDIF.

  mod = col MOD 26 .
  div = col DIV 26 .

  IF div > 0 AND mod <> 0.
    xc = div + 64 .
    col_name = +1(1).
  ELSEIF div > 1 AND mod = 0  .
    xc = div + 63 .
    col_name = +1(1).
  ENDIF.

  IF mod = 0 .
    CONCATENATE col_name 'Z' INTO col_name.
  ELSE.
    xc = mod + 64 .
    CONCATENATE col_name +1(1) INTO col_name.
  ENDIF.
ENDFORM.                    "get_col

 

 

 

 

46C：

**********************************************************
DATA: col_name(2) TYPE c .
FIELD-SYMBOLS : TYPE c.

START-OF-SELECTION.
  DO 705 TIMES .
    CLEAR col_name.
    PERFORM get_col USING sy-index
                          col_name .
    WRITE / col_name.
  ENDDO.
*&---------------------------------------------------------------------*
*&      Form  get_col
*&---------------------------------------------------------------------*
FORM get_col USING col TYPE i
                   col_name TYPE c .
  DATA: xc TYPE x .
  DATA: mod TYPE i ,
        div TYPE i .
  ASSIGN xc TO CASTING TYPE c.

  IF col > 702  OR col < 1 .
    EXIT.
  ENDIF.

  mod = col mod 26 .
  div = col DIV 26 .

  IF div > 0 AND mod <> 0.
    xc = div + 64 .
    col_name = .
  ELSEIF div > 1 AND mod = 0  .
    xc = div + 63 .
    col_name = .
  ENDIF.

  IF mod = 0 .
    CONCATENATE col_name 'Z' INTO col_name.
  ELSE.
    xc = mod + 64 .
    CONCATENATE col_name INTO col_name.
  ENDIF.
ENDFORM.

 

 

 

 

---------------NEW Program----------------

Excel 2007，总列数为XFD，16384列，以前Excel版本的64倍。

行数为1048576，前版本Excel的16倍

 

REPORT  z_barry_test3.

 

DATA: charstr TYPE string ,
      number TYPE i.

 

DO 1000 TIMES.
  PERFORM numtochar USING sy-index CHANGING charstr.
  WRITE : / sy-index,charstr.

 

  PERFORM chartonum USING charstr CHANGING number.
  WRITE number.
ENDDO.

 

*&---------------------------------------------------------------------*
*&      Form  chartonum
*&---------------------------------------------------------------------*
FORM chartonum USING value(char) TYPE string CHANGING value(num) TYPE i.
  DATA: xstr TYPE xstring,
        index TYPE i,
        len TYPE i,
        l_con TYPE REF TO cl_rpe_convert .

 

  CREATE OBJECT l_con.
  CLEAR num .

 

  CALL METHOD l_con->string_to_xstring
    EXPORTING
      input  = char
    IMPORTING
      output = xstr.

  len = XSTRLEN( xstr ).

  DO len TIMES.
    index = len - sy-index  .
    num = num + ( xstr+index(1) - 64 ) * ( 26 ** ( sy-index - 1 ) ).
  ENDDO.
ENDFORM.                    "chartonum

*&---------------------------------------------------------------------*
*&      Form  numtochar
*&---------------------------------------------------------------------*
FORM numtochar USING value(num) TYPE i CHANGING value(char) TYPE string.
  DATA: mod TYPE i ,
        xc TYPE x,
        xstr TYPE xstring,
        l_con TYPE REF TO cl_rpe_convert .

  CREATE OBJECT l_con..
  WHILE num > 0 .
    mod = num MOD 26 .
    num = num DIV 26 .
    IF mod = 0.
      mod = 26.
      num = num - 1.
    ENDIF.

    xc = mod + 64 .
    CONCATENATE xc xstr INTO xstr IN BYTE MODE.

    IF num < 26 AND num > 0 .
      xc = num + 64 .
      CONCATENATE xc xstr INTO xstr IN BYTE MODE.
      EXIT.
    ENDIF.
  ENDWHILE.

  CLEAR char.
  CALL METHOD l_con->xstring_to_string
    EXPORTING
      input  = xstr
    IMPORTING
      output = char.
ENDFORM.                    "numtochar