Correct format for field

[Former Member]

Hello All,

Im downloading EAN11 (it is a character of length 18 ) field form MARA table to an excel. In excel i got the value like 3.16514E+12 where as actual value to be displayed is 3165140039925.

But i know that, to get the value displayed without exponential format, i need to put single quatation in front of the number. I mean, we need to concatenate single quatation with the value.

But is there any other way to do this. If im not wrong, we have to do some thing with OLE format.

Good answers will be appriciated.

Thanks in advance.

Best Regards,

Sasidhar Reddy Matli.

[Former Member]

Change the format of the cells in Excel to Number ..

[Former Member]

Please check below code may help to create excel usign OLE.

REPORT ZTEST_BYW.
*
* OLE specific include file
*
INCLUDE OLE2INCL.
INCLUDE EXCEL__C.
*
* data definitions of OLE objects with type ole2_object
*
PARAMETERS: P_FILENM(80) LOWER CASE OBLIGATORY
DEFAULT 'C:\Documents and Settings\gunasekharb\Desktop\report_tsl.xls'.
DATA: G_TMP_WORKBOOK TYPE OLE2_OBJECT.
DATA: G_TMP_WORKSHEETS TYPE OLE2_OBJECT.
DATA: G_TMP_WORKSHEET1 TYPE OLE2_OBJECT.
DATA: G_TMP_WORKSHEET2 TYPE OLE2_OBJECT.

DATA: G_PC_TEMPLATE LIKE RCGFILETR-FTAPPL
VALUE 'C:\Documents and Settings\gunasekharb\Desktop\mmr_rpt_tsl_tmp.xls'.
DATA: G_EXCEL TYPE OLE2_OBJECT.
DATA: G_WORKBOOKS TYPE OLE2_OBJECT.
DATA: G_WORKBOOK TYPE OLE2_OBJECT.
DATA: G_WORKSHEETS TYPE OLE2_OBJECT.
DATA: G_WORKSHEET TYPE OLE2_OBJECT.
DATA: G_CELL TYPE OLE2_OBJECT.
DATA: G_CELL1 TYPE OLE2_OBJECT.
DATA: G_CELL2 TYPE OLE2_OBJECT.
DATA: G_CELLRANGE TYPE OLE2_OBJECT.
DATA: G_FONT TYPE OLE2_OBJECT.
DATA: G_INTERIOR TYPE OLE2_OBJECT.
DATA: G_BORDERS TYPE OLE2_OBJECT.
DATA: G_FIRST_WS VALUE 'Y'.
DATA: G_ROW TYPE I.
DATA: G_COL TYPE I.
*
* definition of loop counter
*
DATA: I TYPE I VALUE '5'.

DATA: BEGIN OF T_SHEET1 OCCURS 1,
COL1(10),
COL2(20),
END OF T_SHEET1.
DATA: BEGIN OF T_SHEET2 OCCURS 1,
COL1(10),
COL2(20),
END OF T_SHEET2.

START-OF-SELECTION.
PERFORM PREPARE_DATA.
PERFORM DOWNLOAD_TO_EXCEL.

END-OF-SELECTION.
* call method of excel 'QUIT'.
*
* it is now possible to leave the EXCEL application
* remark: you have to go to the EXCEL application and answer the
* popup dialog screen
*
*&---------------------------------------------------------------------*
*& Form PREPARE_DATA
*&---------------------------------------------------------------------*
FORM PREPARE_DATA.
T_SHEET1-COL1 = 'A1'.
T_SHEET1-COL2 = 'A2'.
APPEND T_SHEET1.
T_SHEET1-COL1 = 'B1'.
T_SHEET1-COL2 = 'B2'.
APPEND T_SHEET1.

T_SHEET2-COL1 = 'Y1'.
T_SHEET2-COL2 = 'Y2'.
APPEND T_SHEET2.
T_SHEET2-COL1 = 'Z1'.
T_SHEET2-COL2 = 'Z2'.
APPEND T_SHEET2.

ENDFORM. " PREPARE_DATA

*&---------------------------------------------------------------------*
*& Form DOWNLOAD_TO_EXCEL
*&---------------------------------------------------------------------*
FORM DOWNLOAD_TO_EXCEL.
PERFORM OPEN_EXCEL.
PERFORM ADD_WORKSHEET1.
PERFORM ADD_WORKSHEET2.
PERFORM OPEN_TEMPLATE_FILE.
PERFORM CLOSE_EXCEL.
WRITE: / 'End of program'.
ENDFORM. " DOWNLOAD_TO_EXCEL

*&---------------------------------------------------------------------*
*& Form FILL_CELL
*& FORM routine, which fills the specified cell in the EXCEL sheet
*& with the given value
*&---------------------------------------------------------------------*
FORM FILL_CELL USING I_ROW TYPE I
I_COL TYPE I
I_VALUE
I_FONTBOLD
I_DIGIT
I_WRAPTEXT
I_HORIZON_ALIGN
I_VERTICAL_ALIGN.

DATA: L_STR TYPE STRING.

CALL METHOD OF G_EXCEL 'CELLS' = G_CELL
EXPORTING #1 = I_ROW
#2 = I_COL.

SET PROPERTY OF G_CELL 'VALUE' = I_VALUE.

IF I_FONTBOLD = 'X'.
GET PROPERTY OF G_CELL 'Font' = G_FONT.
SET PROPERTY OF G_FONT 'Bold' = 1.
ENDIF.

IF NOT I_WRAPTEXT IS INITIAL.
SET PROPERTY OF G_CELL 'WrapText' = 1.
ENDIF.

IF NOT I_HORIZON_ALIGN IS INITIAL.
IF I_HORIZON_ALIGN = 'L'.
SET PROPERTY OF G_CELL 'HorizontalAlignment' = XLLEFT.
ELSEIF I_HORIZON_ALIGN = 'R'.
SET PROPERTY OF G_CELL 'HorizontalAlignment' = XLRIGHT.
ELSEIF I_HORIZON_ALIGN = 'C'.
SET PROPERTY OF G_CELL 'HorizontalAlignment' = XLCENTER.
ENDIF.
ENDIF.

IF NOT I_VERTICAL_ALIGN IS INITIAL.
IF I_VERTICAL_ALIGN = 'T'.
SET PROPERTY OF G_CELL 'VerticalAlignment' = XLTOP.
ELSEIF I_VERTICAL_ALIGN = 'B'.
SET PROPERTY OF G_CELL 'VerticalAlignment' = XLBOTTOM.
ELSEIF I_VERTICAL_ALIGN = 'C'.
SET PROPERTY OF G_CELL 'VerticalAlignment' = XLCENTER.
ENDIF.
ENDIF.

* To set number format for cell
IF I_DIGIT <> ''.
IF I_VALUE IS INITIAL AND I_DIGIT <> '%'.
SET PROPERTY OF G_CELL 'VALUE' = ''.
ELSE.
* Set number format for cell with number
IF I_DIGIT = '1'.
SET PROPERTY OF G_CELL 'NumberFormat' = '#,###.0 '.
ELSEIF I_DIGIT = '2'.
SET PROPERTY OF G_CELL 'NumberFormat' = '#,##0.00 '.
ELSEIF I_DIGIT = '%'.
SET PROPERTY OF G_CELL 'NumberFormat' = '#,##0.00% '.
ELSE.
SET PROPERTY OF G_CELL 'NumberFormat' = '#,### '.
ENDIF.
ENDIF.
ENDIF.
ADD 1 TO I_COL.
ENDFORM.

*&---------------------------------------------------------------------*
*& Form OPEN_EXCEL
*&---------------------------------------------------------------------*
FORM OPEN_EXCEL.
DATA: L_CNT TYPE I.
*
* create object excel of OLE class 'EXCEL.APPLICATION'
*
CREATE OBJECT G_EXCEL 'EXCEL.APPLICATION'.
CALL METHOD OF G_EXCEL 'WORKBOOKS' = G_WORKBOOKS.
CALL METHOD OF G_WORKBOOKS 'ADD' = G_WORKBOOK.
GET PROPERTY OF G_WORKBOOK 'Worksheets' = G_WORKSHEETS.
GET PROPERTY OF G_EXCEL 'ACTIVESHEET' = G_WORKSHEET.
SET PROPERTY OF G_EXCEL 'DISPLAYALERTS' = 0.

GET PROPERTY OF G_WORKSHEETS 'Count' = l_cnt.

L_CNT = L_CNT - 1.
* Delete unwanted worksheets
DO L_CNT TIMES.
GET PROPERTY OF G_EXCEL 'ACTIVESHEET' = G_WORKSHEET.
CALL METHOD OF G_WORKSHEET 'DELETE'.
ENDDO.

ENDFORM. " OPEN_EXCEL

*&---------------------------------------------------------------------*
*& Form CLOSE_EXCEL
*&---------------------------------------------------------------------*
FORM CLOSE_EXCEL.
SET PROPERTY OF G_EXCEL 'VISIBLE' = 1.

CALL METHOD OF G_WORKBOOK 'SAVEAS'
EXPORTING #1 = P_FILENM.
*
* quit Excel and free all OLE objects
*
* call method of g_excel 'QUIT'.
FREE OBJECT G_INTERIOR.
FREE OBJECT G_BORDERS.
FREE OBJECT G_FONT.
FREE OBJECT G_CELL.
FREE OBJECT G_CELL1.
FREE OBJECT G_CELL2.
FREE OBJECT G_CELLRANGE.
FREE OBJECT G_EXCEL.
FREE OBJECT G_WORKBOOKS.
FREE OBJECT G_WORKBOOK.
FREE OBJECT G_WORKSHEETS.
FREE OBJECT G_WORKSHEET.

ENDFORM. " CLOSE_EXCEL

*&---------------------------------------------------------------------*
*& Form ADD_WORKSHEET
*&---------------------------------------------------------------------*
FORM ADD_WORKSHEET USING I_NAME.
* Add new worksheet
IF G_FIRST_WS <> 'N'.
G_FIRST_WS = 'N'.
GET PROPERTY OF G_EXCEL 'ACTIVESHEET' = G_WORKSHEET.
ELSE.
CALL METHOD OF G_WORKSHEETS 'Add' = G_WORKSHEET.
ENDIF.
SET PROPERTY OF G_WORKSHEET 'NAME' = I_NAME.
G_ROW = 1.
G_COL = 1.
ENDFORM. " ADD_WORKSHEET

*&---------------------------------------------------------------------*
*& Form ADD_WORKSHEET1
*&---------------------------------------------------------------------*
FORM ADD_WORKSHEET1.
PERFORM ADD_WORKSHEET USING 'Test 1'.
*
* fill line in EXCEL sheet with headerlines of table columns
*
G_COL = 1.
PERFORM FILL_CELL USING G_ROW G_COL 'My First Worksheet' 'X' ''
'' '' ''.
PERFORM MERGE_CELL USING G_ROW 1 G_ROW 4.
PERFORM SET_BORDER USING G_ROW 1 G_ROW 4 4 ''.

ADD 1 TO G_ROW.
G_COL = 1.
PERFORM FILL_CELL USING G_ROW G_COL 'ID'(001) 'X' '' '' '' ''.
PERFORM FILL_CELL USING G_ROW G_COL 'Name'(002) 'X' '' '' '' ''.
PERFORM FILL_CELL USING G_ROW G_COL 'Telephon'(003) 'X' '' '' '' ''.
PERFORM FILL_CELL USING G_ROW G_COL 'Rabatt'(004) 'X' '' '' '' ''.
*
* loop at customr table and print values into EXCEL sheet
*
ADD 1 TO G_ROW.
LOOP AT T_SHEET1.
G_COL = 1.
PERFORM FILL_CELL USING G_ROW G_COL T_SHEET1-COL1 '' '' '' '' ''.
PERFORM FILL_CELL USING G_ROW G_COL T_SHEET1-COL2 '' '' '' '' ''.
ADD 1 TO G_ROW.
ENDLOOP.

G_COL = 4.
PERFORM SET_BORDER USING 1 1 G_ROW G_COL '' 4.
ENDFORM. " ADD_WORKSHEET1

*&---------------------------------------------------------------------*
*& Form ADD_WORKSHEET2
*&---------------------------------------------------------------------*
FORM ADD_WORKSHEET2.
PERFORM ADD_WORKSHEET USING 'Test 2'.
*
* fill line in EXCEL sheet with headerlines of table columns
*
G_COL = 1.
PERFORM FILL_CELL USING G_ROW G_COL 'My Second Worksheet' 'X' ''
'' '' ''.
PERFORM MERGE_CELL USING G_ROW 1 G_ROW 4.
PERFORM SET_BORDER USING G_ROW 1 G_ROW 4 1 'X'.
PERFORM SET_COLOR USING G_ROW 1 G_ROW 4 'LT'.

ADD 1 TO G_ROW.
G_COL = 1.
PERFORM FILL_CELL USING G_ROW G_COL 'ID'(001) 'X' '' '' '' ''.
PERFORM FILL_CELL USING G_ROW G_COL 'Name'(002) 'X' '' '' '' ''.
PERFORM FILL_CELL USING G_ROW G_COL 'Telephon'(003) 'X' '' '' '' ''.
PERFORM FILL_CELL USING G_ROW G_COL 'Rabatt'(004) 'X' '' '' '' ''.
*
* loop at customr table and print values into EXCEL sheet
*
ADD 1 TO G_ROW.
LOOP AT T_SHEET2.
G_COL = 1.
PERFORM FILL_CELL USING G_ROW G_COL T_SHEET2-COL1 '' '' '' '' ''.
PERFORM FILL_CELL USING G_ROW G_COL T_SHEET2-COL2 '' '' '' '' ''.
ADD 1 TO G_ROW.
ENDLOOP.
G_COL = 4.
PERFORM SET_BORDER USING 1 1 G_ROW G_COL 2 'X'.

ADD 4 TO G_ROW.
G_COL = 1.
PERFORM FILL_CELL USING G_ROW G_COL 'This is a wrap text' 'X' ''
'X' 'C' 'T'.
PERFORM FILL_CELL USING G_ROW G_COL 'Vertical - Bottom' 'X' ''
'' 'L' 'T'.

ADD 4 TO G_ROW.
G_COL = 1.
PERFORM FILL_CELL USING G_ROW G_COL 'Amount 1' 'X' '' '' '' ''.
PERFORM FILL_CELL USING G_ROW G_COL '100.04' '' 'X' '' '' ''.
ADD 1 TO G_ROW.
G_COL = 1.
PERFORM FILL_CELL USING G_ROW G_COL 'Amount 2' 'X' '' '' '' ''.
PERFORM FILL_CELL USING G_ROW G_COL '200.01' '' 'X' '' '' ''.
ADD 1 TO G_ROW.
G_COL = 1.
PERFORM FILL_CELL USING G_ROW G_COL 'Total' 'X' '' '' '' ''.
PERFORM FILL_CELL USING G_ROW G_COL '=SUM(B13:B14)' 'X' 'X' '' '' ''.
PERFORM FILL_CELL USING G_ROW G_COL '0.00' 'X' 'X' '' '' ''.

PERFORM COPY_CELL USING G_ROW 2 17 5 25 8.
ENDFORM. " ADD_WORKSHEET2

*&---------------------------------------------------------------------*
*& Form MERGE_CELL
*&---------------------------------------------------------------------*
FORM MERGE_CELL USING I_ROW1 I_COL1
I_ROW2 I_COL2.
CALL METHOD OF G_EXCEL 'Cells' = G_CELL1
EXPORTING #1 = I_ROW1
#2 = I_COL1.

CALL METHOD OF G_EXCEL 'Cells' = G_CELL2
EXPORTING #1 = I_ROW2
#2 = I_COL2.

CALL METHOD OF G_EXCEL 'Range' = G_CELLRANGE
EXPORTING #1 = G_CELL1
#2 = G_CELL2.

CALL METHOD OF G_CELLRANGE 'Merge'.
ENDFORM. " MERGE_CELL

*&---------------------------------------------------------------------*
*& Form SET_BORDER
*&---------------------------------------------------------------------*
FORM SET_BORDER USING I_ROW1 I_COL1
I_ROW2 I_COL2
I_THICKNESS
I_ALLBORDERS.

CALL METHOD OF G_EXCEL 'Cells' = G_CELL1
EXPORTING #1 = I_ROW1
#2 = I_COL1.

CALL METHOD OF G_EXCEL 'Cells' = G_CELL2
EXPORTING #1 = I_ROW2
#2 = I_COL2.

CALL METHOD OF G_EXCEL 'Range' = G_CELLRANGE
EXPORTING #1 = G_CELL1
#2 = G_CELL2.

IF I_ALLBORDERS IS INITIAL.
CALL METHOD OF G_CELLRANGE 'BorderAround'
EXPORTING #1 = 1 "Continuous line
#2 = I_THICKNESS. "Thickness: 1 - Normal, 4 - Thick
ELSE.
GET PROPERTY OF G_CELLRANGE 'Borders' = G_BORDERS.
SET PROPERTY OF G_BORDERS 'LineStyle' = '1'.
SET PROPERTY OF G_BORDERS 'Weight' = I_THICKNESS.
ENDIF.
ENDFORM. " SET_BORDER

*&---------------------------------------------------------------------*
*& Form SET_COLOR
*&---------------------------------------------------------------------*
FORM SET_COLOR USING I_ROW1 I_COL1
I_ROW2 I_COL2
I_COLOR.

DATA: L_COLORINDEX TYPE I.

CASE I_COLOR.
when 'BK'.
L_COLORINDEX = 1. "Black
when 'BR'.
l_colorindex = 53. "Brown
when 'OG'.
l_colorindex = 52. "Olive Green
when 'DG'.
l_colorindex = 51. "Dark Green
when 'DT'.
l_colorindex = 49. "Dark Teal
when 'DB'.
L_COLORINDEX = 11. "Dark Blue
WHEN 'ID'.
L_COLORINDEX = 55. "Indigo
when 'G4'.
l_colorindex = 56. "Gray 80%
when 'DR'.
L_COLORINDEX = 9. "Dark Red
when 'OR'.
l_colorindex = 46. "Orange
when 'DY'.
l_colorindex = 12. "Dark Yellow
when 'GR'.
l_colorindex = 10. "Green
when 'TL'.
L_COLORINDEX = 14. "Teal
WHEN 'BL'.
L_COLORINDEX = 5. "Blue
WHEN 'BY'.
L_COLORINDEX = 47. "Blue Gray
when 'G3'.
l_colorindex = 16. "Gray 50%
when 'RD'.
L_COLORINDEX = 3. "Red
when 'LO'.
l_colorindex = 45. "Light Orange
when 'LI'.
l_colorindex = 43. "Lime
when 'SG'.
l_colorindex = 50. "Sea Green
when 'AQ'.
L_COLORINDEX = 42. "Aqua
WHEN 'LB'.
L_COLORINDEX = 41. "Light Blue
WHEN 'VL'.
L_COLORINDEX = 13. "Violet
when 'G2'.
l_colorindex = 48. "Gray 40%
when 'PK'.
L_COLORINDEX = 7. "Pink
when 'GD'.
l_colorindex = 44. "Gold
when 'YL'.
L_COLORINDEX = 6. "Yellow
when 'BG'.
L_COLORINDEX = 4. "Bright Green
WHEN 'TQ'.
L_COLORINDEX = 8. "Turquoise
WHEN 'SB'.
L_COLORINDEX = 33. "Sky Blue
WHEN 'PL'.
L_COLORINDEX = 54. "Plum
when 'G1'.
l_colorindex = 15. "Gray 25%
when 'RS'.
l_colorindex = 38. "Rose
when 'TN'.
l_colorindex = 40. "Tan
when 'LY'.
l_colorindex = 36. "Light Yellow
when 'LG'.
l_colorindex = 35. "Light Green
when 'LT'.
L_COLORINDEX = 34. "Light Turquoise
WHEN 'PB'.
L_COLORINDEX = 37. "Pale Blue
WHEN 'LV'.
L_COLORINDEX = 39. "Lavender
when 'WH'.
L_COLORINDEX = 2. "White
WHEN OTHERS.
L_COLORINDEX = 2. "White
ENDCASE.

CALL METHOD OF G_EXCEL 'Cells' = G_CELL1
EXPORTING #1 = I_ROW1
#2 = I_COL1.

CALL METHOD OF G_EXCEL 'Cells' = G_CELL2
EXPORTING #1 = I_ROW2
#2 = I_COL2.

CALL METHOD OF G_EXCEL 'Range' = G_CELLRANGE
EXPORTING #1 = G_CELL1
#2 = G_CELL2.

GET PROPERTY OF G_CELLRANGE 'Interior' = G_INTERIOR.

SET PROPERTY OF G_INTERIOR 'ColorIndex' = L_COLORINDEX.
ENDFORM. " SET_COLOR

*&---------------------------------------------------------------------*
*& Form COPY_CELL
*&---------------------------------------------------------------------*
FORM COPY_CELL USING I_CROW I_CCOL
I_PROW1 I_PCOL1
I_PROW2 I_PCOL2.
CALL METHOD OF G_EXCEL 'Cells' = G_CELL
EXPORTING #1 = I_CROW
#2 = I_CCOL.

CALL METHOD OF G_CELL 'Copy'.

CALL METHOD OF G_EXCEL 'Cells' = G_CELL1
EXPORTING #1 = I_PROW1
#2 = I_PCOL1.

CALL METHOD OF G_EXCEL 'Cells' = G_CELL2
EXPORTING #1 = I_PROW2
#2 = I_PCOL2.

CALL METHOD OF G_EXCEL 'Range' = G_CELLRANGE
EXPORTING #1 = G_CELL1
#2 = G_CELL2.

CALL METHOD OF G_WORKSHEET 'Paste'
EXPORTING #1 = G_CELLRANGE.

ENDFORM. " COPY_CELL

*&---------------------------------------------------------------------*
*& Form OPEN_TEMPLATE_FILE
*&---------------------------------------------------------------------*
FORM OPEN_TEMPLATE_FILE.
CALL METHOD OF G_WORKBOOKS 'Open' = G_TMP_WORKBOOK
EXPORTING #1 = G_PC_TEMPLATE
#2 = 2
#3 = 0
#4 = 1
#5 = 0
#6 = 0
#7 = 1.

CALL FUNCTION 'FLUSH'
EXCEPTIONS
OTHERS = 0.

GET PROPERTY OF G_TMP_WORKBOOK 'Worksheets' = G_TMP_WORKSHEETS.
GET PROPERTY OF G_TMP_WORKSHEETS 'Item' = G_TMP_WORKSHEET1
EXPORTING #1 = 1.
GET PROPERTY OF G_TMP_WORKSHEETS 'Item' = G_TMP_WORKSHEET2
EXPORTING #1 = 2.

CALL METHOD OF G_TMP_WORKSHEET1 'Copy'
EXPORTING #1 = G_WORKSHEET.

CALL METHOD OF G_TMP_WORKSHEET2 'Copy'
EXPORTING #1 = G_WORKSHEET.

* call method of g_tmp_workbook 'Close'.
CALL FUNCTION 'FLUSH'
EXCEPTIONS
OTHERS = 0.

ENDFORM. " OPEN_TEMPLATE_FILE

Edited by: Gunasekhar B on Apr 16, 2009 3:19 PM

Edited by: Gunasekhar B on Apr 16, 2009 3:19 PM

[Former Member]

http://www.scribd.com/doc/7202623/MSWord-Excel-With-ABAP

https://www.sdn.sap.com/irj/scn/wiki?path=/display/abap/sampleprogramtoopenexcelsheetusing+OLE

Refer these links !

Regards,

Lalit Mohan Gupta.