topic excel format in Application Development and Automation Discussions

excel format

Former Member — Thu, 05 Jul 2007 10:43:45 GMT

I have a program that downloads a list of employee numbers that starts with 0. As I debug the program, I do get the first '000123'. After the intenal table passed to WS_DOWNLOAD and check the excel, there's no zero in it. I tried to open the file in Notepad and there are zero's on the first digit.

I can't use other call function like gui_download since it's not available in SAP 4.0. I also tried OLE2 and it is still the same.

Is it really SAP4.0 can't set it to excel? Should I ask the user to manually set the format of the employee numbers?

Re: excel format

Former Member — Thu, 05 Jul 2007 10:47:47 GMT

Check with below program :

It downloads leading zero's into excel file :

REPORT ZTEST3 line-size 400.

DATA : V_CHAR(1) TYPE C VALUE ''''.

data : v_field(12) type c.

data : begin of itab occurs 0,

fld1(12) type c,

end of itab.

start-of-selection.

v_field = '0000012345'.

CONCATENATE V_CHAR V_FIELD INTO V_FIELD.

itab-fld1 = v_field.

append itab.

CALL FUNCTION 'WS_DOWNLOAD'

EXPORTING

BIN_FILESIZE = ' '
CODEPAGE = ' '

FILENAME =

'C:\Documents and Settings\smaramreddy\Desktop\fff.xls'

FILETYPE = 'ASC'

MODE = ' '
WK1_N_FORMAT = ' '
WK1_N_SIZE = ' '
WK1_T_FORMAT = ' '
WK1_T_SIZE = ' '
COL_SELECT = ' '
COL_SELECTMASK = ' '
NO_AUTH_CHECK = ' '
IMPORTING
FILELENGTH =

TABLES

DATA_TAB = itab

FIELDNAMES =

EXCEPTIONS

FILE_OPEN_ERROR = 1

FILE_WRITE_ERROR = 2

INVALID_FILESIZE = 3

INVALID_TYPE = 4

NO_BATCH = 5

UNKNOWN_ERROR = 6

INVALID_TABLE_WIDTH = 7

GUI_REFUSE_FILETRANSFER = 8

CUSTOMER_ERROR = 9

OTHERS = 10

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

Thanks

Seshu

Re: excel format

Former Member — Thu, 05 Jul 2007 10:49:33 GMT

Hi,

before downloading check itab for values with leading zeroes if they are not there, then use conversion input exit to convert the same and move it a separate field and display

regards

Shiva

Re: excel format

Former Member — Thu, 05 Jul 2007 10:51:26 GMT

Hi,

Basically it has nothing to do with additional zeros but it is mainly due to tabs that are set for sap, notepa and excel formats so i think its not a matter of worry.

Pls reward points.

Regards,

Ameet

Re: excel format

Former Member — Thu, 05 Jul 2007 10:57:11 GMT

give the <b>FILETYPE as DBF</b> and checkout if it works

Re: excel format

Former Member — Thu, 05 Jul 2007 11:13:44 GMT

This program is working with leading zeros .

[code]report zaseras.

TYPE-POOLS abap.

INCLUDE ole2incl.

PARAMETERS:

filename LIKE rlgrap-filename OBLIGATORY,

pa_tab TYPE dd02l-tabname,

p_maxrow(4) TYPE n,

p_sample AS CHECKBOX.

DATA: subrc LIKE sy-subrc,

generic_table_wa TYPE REF TO data.

FIELD-SYMBOLS <p_tab_wa> TYPE ANY.

DATA: t_fields TYPE TABLE OF fieldname WITH HEADER LINE,

t_details TYPE abap_compdescr_tab,

wa_comp TYPE abap_compdescr.

handles for OLE objects

DATA: h_excel TYPE ole2_object, " Excel object

h_mapl TYPE ole2_object, " list of workbooks

h_map TYPE ole2_object, " workbook

h_zl TYPE ole2_object, " cell

h_f TYPE ole2_object. " font

At Selection Screen Event...........................

AT SELECTION-SCREEN ON VALUE-REQUEST FOR filename.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

mask = '*.xls'

static = 'X'

CHANGING

file_name = filename

EXCEPTIONS

mask_too_long = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

START-OF-SELECTION.

CREATE DATA generic_table_wa TYPE (pa_tab).

ASSIGN generic_table_wa->* TO <p_tab_wa>.

IF NOT p_sample IS INITIAL.

PERFORM f_download_sample USING filename.

ELSE.

PERFORM f_excel_upload USING filename

subrc.

ENDIF.

======================================================================

Subroutine to upload data from excel file from local PC *

----

---> P_TAB Internal table for data to be uploaded *
---> P_FILENAME File to be uploaded *
<--- P_RC Return Code *

======================================================================

FORM f_excel_upload USING p_filename LIKE rlgrap-filename

p_rc LIKE sy-subrc

DATA : $i_intern TYPE kcde_cells OCCURS 0 WITH HEADER LINE.

DATA : $v_index TYPE i.

DATA : $v_start_col TYPE i VALUE '1',

$v_start_row TYPE i VALUE '1',

$v_end_col TYPE i VALUE '256',

$v_end_row TYPE i VALUE '65536'.

FIELD-SYMBOLS : <$fs>.

CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'

EXPORTING

filename = p_filename

i_begin_col = $v_start_col

i_begin_row = $v_start_row

i_end_col = $v_end_col

i_end_row = $v_end_row

TABLES

intern = $i_intern

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2.

MOVE : sy-subrc TO p_rc.

CHECK NOT $i_intern[] IS INITIAL.

SORT $i_intern BY row col.

LOOP AT $i_intern.

MOVE : $i_intern-col TO $v_index.

ASSIGN COMPONENT $v_index OF STRUCTURE <p_tab_wa> TO <$fs>.

MOVE : $i_intern-value TO <$fs>.

AT END OF row.

MODIFY (pa_tab) FROM <p_tab_wa>.

IF sy-subrc EQ 0.

COMMIT WORK.

WRITE:/ 'ok', <p_tab_wa>.

ELSE.

WRITE:/ 'ER', <p_tab_wa>.

ENDIF.

CLEAR <p_tab_wa>.

IF $i_intern-row = p_maxrow AND NOT p_maxrow IS INITIAL.

WRITE: / p_maxrow, 'lineas leidas'.

EXIT.

ENDIF.

ENDAT.

ENDLOOP.

ENDFORM.

&----

*& Form f_download_sample

&----

text

----

-->P_FILENAME text

----

FORM f_download_sample USING p_filename.

Dynamic field description of a structure

DATA: ref_descr TYPE REF TO cl_abap_structdescr.

ref_descr ?= cl_abap_typedescr=>describe_by_data( <p_tab_wa> ).

Export to excel

t_details[] = ref_descr->components[].

LOOP AT ref_descr->components INTO wa_comp.

AT FIRST.

APPEND 'Attributes' TO t_fields.

ENDAT.

APPEND wa_comp-name TO t_fields.

ENDLOOP.

PERFORM table2excel.

CALL FUNCTION 'EXCEL_OLE_STANDARD_DAT'

EXPORTING

file_name = p_filename

data_sheet_name = 'Hoja1'

TABLES

data_tab = t_details

fieldnames = t_fields

EXCEPTIONS

file_not_exist = 1

filename_expected = 2

communication_error = 3

ole_object_method_error = 4

ole_object_property_error = 5

invalid_filename = 6

invalid_pivot_fields = 7

download_problem = 8

OTHERS = 9.

IF sy-subrc eq 1.

MESSAGE i368(00) WITH 'You should save the file'.

elseif sy-subrc <> 0.

MESSAGE i368(00) WITH sy-subrc.

ENDIF.

ENDFORM. " f_download_sample

&----

*& Form table2excel

&----

text

----

--> p1 text
<-- p2 text

----

FORM table2excel.

Linea

DATA: h TYPE i, line TYPE i.

FIELD-SYMBOLS <comp> TYPE ANY.

start Excel

CREATE OBJECT h_excel 'EXCEL.APPLICATION'.

PERFORM err_hdl.

SET PROPERTY OF h_excel 'Visible' = 1.

PERFORM err_hdl.

tell user what is going on

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

PERCENTAGE = 0

text = 'Opening workbook...'

EXCEPTIONS

OTHERS = 1.

get list of workbooks, initially empty

CALL METHOD OF h_excel 'Workbooks' = h_mapl.

PERFORM err_hdl.

add a new workbook

CALL METHOD OF h_mapl 'Add' = h_map.

PERFORM err_hdl.

tell user what is going on

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

PERCENTAGE = 0

text = 'Complete the internal table ...'

EXCEPTIONS

OTHERS = 1.

output column headings to active Excel sheet

LOOP AT t_fields.

h = sy-tabix.

PERFORM fill_cell USING 1 h 1 t_fields.

ENDLOOP.

PERFORM fill_cell USING 2 1 0 'Length'.

PERFORM fill_cell USING 3 1 0 'Decimals'.

PERFORM fill_cell USING 4 1 0 'Type'.

PERFORM fill_cell USING 5 1 0 'Sample entry'.

LOOP AT t_details INTO wa_comp.

h = sy-tabix + 1.

PERFORM fill_cell USING 2 h 0 wa_comp-length.

PERFORM fill_cell USING 3 h 0 wa_comp-decimals.

PERFORM fill_cell USING 4 h 0 wa_comp-type_kind.

ENDLOOP.

line = 5.

SELECT single * INTO <p_tab_wa>

FROM (pa_tab).

DO.

ASSIGN COMPONENT sy-index OF STRUCTURE <p_tab_wa> TO <comp>.

IF sy-subrc NE 0. EXIT. ENDIF.

h = sy-index + 1.

PERFORM fill_cell USING line h 0 <comp>.

ENDDO.

disconnect from Excel

FREE OBJECT h_excel.

PERFORM err_hdl.

ENDFORM. " table2excel

----

FORM FILL_CELL *

----

sets cell at coordinates i,j to value val boldtype bold *

----

FORM fill_cell USING i j bold val.

CALL METHOD OF h_excel 'Cells' = h_zl EXPORTING #1 = i #2 = j.

PERFORM err_hdl.

SET PROPERTY OF h_zl 'Value' = val .

PERFORM err_hdl.

GET PROPERTY OF h_zl 'Font' = h_f.

PERFORM err_hdl.

SET PROPERTY OF h_f 'Bold' = bold .

PERFORM err_hdl.

ENDFORM.

&----

*& Form ERR_HDL

&----

outputs OLE error if any *

----

--> p1 text
<-- p2 text

----

FORM err_hdl.

IF sy-subrc <> 0.

WRITE: / 'OLE-Automation failed:'(010), sy-subrc.

STOP.

ENDIF.

ENDFORM. " ERR_HDL[/code]

reward points if it is usefull ....

Girish