Community
Application Development and Automation Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

excel output format

Former Member

‎2008 Jun 16 9:02 AM

0 Likes
659

hello,

would it be possible that our excel output would have merged cells using gui_download function module?

4 REPLIES 4
Read only

Former Member

‎2008 Jun 16 9:08 AM

0 Likes
574

It does not provide any functionality to merge cells

Read only

Former Member

‎2008 Jun 16 9:11 AM

0 Likes
574

hi there is no option in the GUI_DOWNLOAD for the merging cells..

go to the GUI_DOWNLOAD....function module documentation

se37--> GUI_DOWNLOAD--->function module documentation

Read only

Former Member

‎2008 Jun 16 9:13 AM

0 Likes
574

IN THIS INCLUDE YOU WILL ALL SUBROUTINES TO WORK WITH

EXCEL FILE.

MERGING OF CELLS ALSO EXISTS.

***INCLUDE ZANB_LIBRARY_excel.

INCLUDE OLE2INCL.

DATA:

excel_APPLICATION TYPE OLE2_OBJECT,

excel_BOOK TYPE OLE2_OBJECT,

excel_SHEET TYPE OLE2_OBJECT,

excel_COLUMN TYPE OLE2_OBJECT,

excel_ROW TYPE OLE2_OBJECT,

excel_CELL TYPE OLE2_OBJECT,

excel_VARIABLE1 TYPE OLE2_OBJECT,

excel_VARIABLE2 TYPE OLE2_OBJECT,

excel_VARIABLE3 TYPE OLE2_OBJECT,

excel_VARIABLE4 TYPE OLE2_OBJECT.

*&----

&*

*& Form Create_Application_excel

*& Ñîçäàòü âèäèìî - 1 / 0 (ôîðìàò -Number between 0 or 1)

*&----

&*

Form Create_Application_excel using Visible.

CREATE OBJECT excel_APPLICATION 'excel.Application'.

IF SY-SUBRC <> 0.

WRITE: / 'Error! excel application did not open!!!'.

else.

SET PROPERTY OF excel_APPLICATION 'Visible' = Visible NO FLUSH.

CALL METHOD OF excel_APPLICATION 'WorkBooks' = excel_BOOK NO FLUSH.

CALL METHOD OF excel_BOOK 'ADD' NO FLUSH.

CALL METHOD OF excel_APPLICATION 'WorkSheets' = excel_SHEET

NO FLUSH EXPORTING #1 = 1.

DATA: EXCEL_PAGE_SETUP TYPE OLE2_OBJECT.

CALL METHOD OF EXCEL_SHEET 'PageSetup' = EXCEL_PAGE_SETUP.

SET PROPERTY OF EXCEL_PAGE_SETUP 'Orientation' = 2.

endif.

endform. " Form Create_Application_excel

*----

*

  • FORM CURRENT_SHEET_EXCEL *

*----

*

  • ........ *

*----

*

  • --> NUMBER *

*----

*

FORM CURRENT_SHEET_EXCEL USING NUMBER.

CALL METHOD OF excel_APPLICATION 'WorkSheets' = excel_SHEET

EXPORTING #1 = NUMBER.

ENDFORM.

*----

*

  • FORM Create_List_excel *

*----

*

  • ........ *

*----

*

Form Create_List_excel.

*Sheets.Add

CALL METHOD OF excel_APPLICATION 'Sheets' = excel_SHEET.

CALL METHOD OF excel_SHEET 'Add'.

endform. " Form Create_Application_excel

*&----

&*

*& Form Quit_Application_excel

*&----

&*

Form Quit_Application_excel.

CALL METHOD OF excel_APPLICATION 'quit' NO FLUSH.

perform Clear_All_Variable_excel.

endform. " Form Quit_Application_excel

*&----

&*

*& Form Save_to_File_excel

*& Çàïèñàòü book â ôàéë (ôîðìàò - TXT)

*& Ïðèìåð: perform Save_to_File_excel using 'd:Tempexcel1.xls'

*&----

&*

Form Save_to_File_excel using Filename.

CALL METHOD OF excel_APPLICATION 'ActiveWorkbook' = excel_VARIABLE1

NO FLUSH.

CALL METHOD OF excel_VARIABLE1 'SaveAs' NO FLUSH

EXPORTING #1 = Filename.

IF SY-SUBRC <> 0.

perform Quit_Application_excel.

MESSAGE E145.

ENDIF.

endform. " Form Save_to_File_excel

*&----

&*

*& Form Open_File_excel

*& Çàãðóçèòü excel-ôàéë â excel (ôîðìàò - TXT)

*& Îòêðûòü âèäèìî - 1 / 0 (ôîðìàò -Number between 0 or 1)

*Ïðèìåð: perform Save_to_excel_File using 'd:Tempexcel1.xls' 1

*&----

&*

Form Open_File_excel using Filename Visible.

CREATE OBJECT excel_APPLICATION 'excel.APPLICATION'.

IF SY-SUBRC <> 0.

WRITE: / 'Error! excel application did not open!!!'.

else.

CALL METHOD OF excel_APPLICATION 'WORKBOOKS' = excel_BOOK NO FLUSH.

CALL METHOD OF excel_BOOK 'OPEN' EXPORTING #1 = Filename.

IF SY-SUBRC <> 0.

MESSAGE E035.

ENDIF.

set property of excel_APPLICATION 'VISIBLE' = Visible NO FLUSH.

CALL METHOD OF excel_APPLICATION 'WorkSheets' = excel_SHEET NO FLUSH

EXPORTING #1 = 1.

ENDIF.

endform. " Form Open_File_excel

*&----

&*

*& Form Clear_All_Variable_excel " Î÷èùàåò âñå çíà÷åíèÿ OLE ïåðåìåííûõ

*&----

&*

Form Clear_All_Variable_excel.

FREE OBJECT excel_BOOK.

FREE OBJECT excel_SHEET.

FREE OBJECT excel_COLUMN.

FREE OBJECT excel_ROW.

FREE OBJECT excel_CELL.

FREE OBJECT excel_VARIABLE1.

FREE OBJECT excel_VARIABLE2.

FREE OBJECT excel_VARIABLE3.

FREE OBJECT excel_VARIABLE4.

FREE OBJECT excel_APPLICATION.

endform. " Form Clear_All_Variable_excel

*&----

&*

*& Form Write_Cell_Text_excel " Ïèøåò â ÿ÷åéêó Òåêñò

*&----

&*

Form Write_Cell_Text_excel USING row " Ñòðîêà

column " Ñòîëáåö

Text. " Òåêñò ÿ÷åéêè

CALL METHOD OF excel_SHEET 'cells' = excel_CELL NO FLUSH

EXPORTING #1 = row #2 = column.

SET PROPERTY OF excel_CELL 'Value' = Text NO FLUSH.

set property of excel_cell 'WrapText' = 'True' NO FLUSH.

endform. " Form Write_Cell_Text_excel

*&----

&*

Form Write_Cell_Text_Excel_Flush USING row " No?iea

column " Noieaao

Text. " Oaeno y?aeee

call method of excel_sheet 'cells' = excel_cell

EXPORTING #1 = row #2 = column.

set property of excel_cell 'Value' = text.

endform. " Form Write_Cell_Text_Excel_Flush

*&----

&*

*& Form Write_Cell_SizeText_excel "Ïèøåò â ÿ÷åéêó Òåêñò è ðàçìåð øðèôò

*&----

&*

Form Write_Cell_SizeText_excel USING row " Ñòðîêà

column " Ñòîëáåö

Size_Font "Ðàçìåð Øðèôòà 1 - 72

Text. " Òåêñò ÿ÷åéêè

CALL METHOD OF excel_SHEET 'cells' = excel_CELL NO FLUSH

EXPORTING #1 = row #2 = column.

call method of excel_cell 'Font' = excel_variable1 NO FLUSH.

SET PROPERTY OF excel_CELL 'Value' = Text NO FLUSH.

set property of excel_cell 'WrapText' = 'True' NO FLUSH.

set property of excel_variable1 'Size' = Size_Font NO FLUSH.

*Âûðàâíèâàåò

set property of excel_cell 'VerticalAlignment' = 2 NO FLUSH.

set property of excel_cell 'HorizontalAlignment' = 3 NO FLUSH.

endform. " Form Write_Cell_Text_excel

*&----

&*

*& Form Write_Cell_SizeTextDown_excel "Ïèøåò â ÿ÷åéêó Òåêñò è ðàçìåð

*& øðèôò ïî âåðòèêàëè âûðàâíèâàå âíèç

*&----

&*

Form Write_Cell_SizeTextDown_excel USING row " Ñòðîêà

column " Ñòîëáåö

Size_Font "Ðàçìåð Øðèôòà 1 - 72

Text. " Òåêñò ÿ÷åéêè

CALL METHOD OF excel_SHEET 'cells' = excel_CELL NO FLUSH

EXPORTING #1 = row #2 = column.

call method of excel_cell 'Font' = excel_variable1 NO FLUSH.

SET PROPERTY OF excel_CELL 'Value' = Text NO FLUSH.

set property of excel_cell 'WrapText' = 'True' NO FLUSH.

set property of excel_variable1 'Size' = Size_Font NO FLUSH.

*Âûðàâíèâàåò

set property of excel_cell 'VerticalAlignment' = 4 NO FLUSH.

  • set property of excel_cell 'HorizontalAlignment' = 3 NO FLUSH.

endform. " Form Write_Cell_Text_excel

*&----

&*

*& Form Write_Cell_SizeTextLeft_excel "Ïèøåò â ÿ÷åéêó Òåêñò

*& è ðàçìåð øðèôòà, âûðàâíèâàíèå â ÿ÷åéêå ïî ëåâîìó êðàþ

*&----

&*

Form Write_Cell_SizeTextLeft_excel USING row " Ñòðîêà

column " Ñòîëáåö

Size_Font "Ðàçìåð Øðèôòà 1 - 72

Text. " Òåêñò ÿ÷åéêè

CALL METHOD OF excel_SHEET 'cells' = excel_CELL NO FLUSH

EXPORTING #1 = row #2 = column.

call method of excel_cell 'Font' = excel_variable1 NO FLUSH.

SET PROPERTY OF excel_CELL 'Value' = Text NO FLUSH.

set property of excel_cell 'WrapText' = 'True' NO FLUSH.

set property of excel_variable1 'Size' = Size_Font NO FLUSH.

*Âûðàâíèâàåò

set property of excel_cell 'VerticalAlignment' = 2 NO FLUSH.

set property of excel_cell 'HorizontalAlignment' = 2 NO FLUSH.

endform. " Form Write_Cell_SizeTextLeft_excel

*&----

&*

*& Form Write_Cell_SizeTextLeftB_excel "Ïèøåò â ÿ÷åéêó Æèðíûé Òåêñò

*& è ðàçìåð øðèôòà, âûðàâíèâàíèå â ÿ÷åéêå ïî ëåâîìó êðàþ

*&----

&*

Form Write_Cell_SizeTextLeftB_excel USING row " Ñòðîêà

column " Ñòîëáåö

Size_Font "Ðàçìåð Øðèôòà 1 - 72

Text. " Òåêñò ÿ÷åéêè

CALL METHOD OF excel_SHEET 'cells' = excel_CELL NO FLUSH

EXPORTING #1 = row #2 = column.

call method of excel_cell 'Font' = excel_variable1 NO FLUSH.

SET PROPERTY OF excel_CELL 'Value' = Text NO FLUSH.

set property of excel_cell 'WrapText' = 'True' NO FLUSH.

set property of excel_variable1 'Size' = Size_Font NO FLUSH.

*Âûðàâíèâàåò

set property of excel_cell 'VerticalAlignment' = 2 NO FLUSH.

set property of excel_cell 'HorizontalAlignment' = 2 NO FLUSH.

*Æèðíûé òåêñò

set property of excel_variable1 'Bold' = 'True' NO FLUSH.

endform. " Form Write_Cell_SizeTextLeftB_excel

*&----

&*

*& Form Write_Cell_SizeTextRight_excel "Ïèøåò â ÿ÷åéêó Òåêñò

*& è ðàçìåð øðèôòà, âûðàâíèâàíèå â ÿ÷åéêå ïî ïðàâîìó êðàþ

*&----

&*

Form Write_Cell_SizeTextRight_excel USING row " Ñòðîêà

column " Ñòîëáåö

Size_Font "Ðàçìåð Øðèôòà 1 - 72

Text. " Òåêñò ÿ÷åéêè

CALL METHOD OF excel_SHEET 'cells' = excel_CELL NO FLUSH

EXPORTING #1 = row #2 = column.

call method of excel_cell 'Font' = excel_variable1 NO FLUSH.

SET PROPERTY OF excel_CELL 'Value' = Text NO FLUSH.

set property of excel_cell 'WrapText' = 'True' NO FLUSH.

set property of excel_variable1 'Size' = Size_Font NO FLUSH.

*Âûðàâíèâàåò

set property of excel_cell 'VerticalAlignment' = 2 NO FLUSH.

set property of excel_cell 'HorizontalAlignment' = 4 NO FLUSH.

endform. " Form Write_Cell_SizeTextRight_excel

*&----

&*

*& Form Write_Cell_SizTextRightB_excel "Ïèøåò â ÿ÷åéêó Æèðíûé Òåêñò

*& è ðàçìåð øðèôòà, âûðàâíèâàíèå â ÿ÷åéêå ïî ïðàâîìó êðàþ

*&----

&*

Form Write_Cell_SizTextRightB_excel USING row " Ñòðîêà

column " Ñòîëáåö

Size_Font "Ðàçìåð Øðèôòà 1 - 72

Text. " Òåêñò ÿ÷åéêè

CALL METHOD OF excel_SHEET 'cells' = excel_CELL NO FLUSH

EXPORTING #1 = row #2 = column.

call method of excel_cell 'Font' = excel_variable1 NO FLUSH.

SET PROPERTY OF excel_CELL 'Value' = Text NO FLUSH.

set property of excel_cell 'WrapText' = 'True' NO FLUSH.

set property of excel_variable1 'Size' = Size_Font NO FLUSH.

*Âûðàâíèâàåò

set property of excel_cell 'VerticalAlignment' = 2 NO FLUSH.

set property of excel_cell 'HorizontalAlignment' = 4 NO FLUSH.

*Æèðíûé òåêñò

set property of excel_variable1 'Bold' = 'True' NO FLUSH.

endform. " Form Write_Cell_SizTextRightB_excel

*&----

&*

*& Form Write_Cell_SizeTextLeftU_excel "Ïèøåò â ÿ÷åéêó Òåêñò

*& è ðàçìåð øðèôòà, âûðàâíèâàíèå â ÿ÷åéêå ïî ëåâîìó êðàþ

*&----

&*

Form Write_Cell_SizeTextLeftU_excel USING row " Ñòðîêà

column " Ñòîëáåö

Size_Font "Ðàçìåð Øðèôòà 1 - 72

Text. " Òåêñò ÿ÷åéêè

CALL METHOD OF excel_SHEET 'cells' = excel_CELL NO FLUSH

EXPORTING #1 = row #2 = column.

call method of excel_cell 'Font' = excel_variable1 NO FLUSH.

SET PROPERTY OF excel_CELL 'Value' = Text NO FLUSH.

set property of excel_cell 'WrapText' = 'True' NO FLUSH.

set property of excel_variable1 'Size' = Size_Font NO FLUSH.

*Âûðàâíèâàåò

set property of excel_cell 'VerticalAlignment' = 2 NO FLUSH.

set property of excel_cell 'HorizontalAlignment' = 2 NO FLUSH.

*Ïîä÷åðêèâàíèå

set property of excel_variable1 'Underline' = 2 NO FLUSH.

endform. " Form Write_Cell_SizeTextLeftU_excel

*----

*

  • FORM Write_Cell_Text_excel_BOLD " Ïèøåò â ÿ÷åéêó Æèðíûé Òåêñò *

*----

*

Form Write_Cell_Text_excel_BOLD USING row " Ñòðîêà

column " Ñòîëáåö

Text. " Òåêñò ÿ÷åéêè

CALL METHOD OF excel_SHEET 'cells' = excel_CELL NO FLUSH

EXPORTING #1 = row #2 = column.

SET PROPERTY OF excel_CELL 'Value' = Text NO FLUSH.

call method of excel_cell 'Font' = excel_variable1 NO FLUSH.

set property of excel_variable1 'Bold' = 'True' NO FLUSH.

set property of excel_cell 'VerticalAlignment' = 2 NO FLUSH.

set property of excel_cell 'HorizontalAlignment' = 3 NO FLUSH.

set property of excel_cell 'WrapText' = 'True' NO FLUSH.

endform. " Form Write_Cell_Text_excel

*----

*

  • FORM Write_Cell_Text_excel_BOLD_H " Ïèøåò â ÿ÷åéêó Æèðíûé Òåêñò

  • áåç ïåðåíîñà ñëîâ, äëÿ çàãîëîâêîâ

*----

*

Form Write_Cell_Text_excel_BOLD_H USING row " Ñòðîêà

column " Ñòîëáåö

Text. " Òåêñò ÿ÷åéêè

CALL METHOD OF excel_SHEET 'cells' = excel_CELL NO FLUSH

EXPORTING #1 = row #2 = column.

SET PROPERTY OF excel_CELL 'Value' = Text NO FLUSH.

call method of excel_cell 'Font' = excel_variable1 NO FLUSH.

set property of excel_variable1 'Bold' = 'True' NO FLUSH.

set property of excel_cell 'VerticalAlignment' = 2 NO FLUSH.

set property of excel_cell 'HorizontalAlignment' = 3 NO FLUSH.

endform. " Form Write_Cell_Text_excel_BOLD_H

*----

*

  • FORM FORMAT_CELL *

*----

*

  • ........ *

*----

*

  • --> ROW *

  • --> COLUMN *

  • --> FORMAT_CELL *

*----

*

FORM FORMAT_CELL USING Row Column Format_Cell.

*Ôîðìàò ÿ÷åéêè ('C' - Òåêñò,'N'-÷èñëî, 'F' - ôèíàíñîâûé, 'T' -

*âðåìåííîé, 'D' - Äàòû

CALL METHOD OF excel_SHEET 'cells' = excel_CELL NO FLUSH

EXPORTING #1 = Row #2 = Column.

Case Format_Cell.

When 'N' or 'n'. " ×èñëîâîé ôîðìàò

set property of excel_Cell 'NumberFormat' = '0' NO FLUSH.

When 'F' or 'f'. " ôèíàíñîâûé ôîðìàò

set property of excel_Cell 'NumberFormat'

= '#,##0.00' NO FLUSH.

When 'C' or 'c'. " Òåêñòîâûé ôîðìàò

set property of excel_Cell 'NumberFormat' = '@' NO FLUSH.

When 'D' or 'd'. " Äàòà ôîðìàò

set property of excel_Cell 'NumberFormat' = 'm/d/yy' NO FLUSH.

When 'T' or 't'. " Âðåìÿ ôîðìàò

set property of excel_Cell 'NumberFormat' = 'h:mm:ss' NO FLUSH.

endcase.

ENDFORM.

*&----

&*

*& Form Cell_Atributes_excel

*&----

&*

Form Cell_Atributes_excel USING

Row " Ñòðîêà (number > 1)

Column " Ñòîëáåö (number > 1)

Name_Font " Èìÿ øðèôòà

Size_Font " Ðàçìåð øðèôòà (number between 1

" or 72)

Color_Font " Öâåò øðèôòà (number between 1 or

" 56(0-îñòàâèòü áåç èçìåíåíèÿ))

BackGround " Ôîí ÿ÷åéêè (number between 1 or

" 56(0-îñòàâèòü áåç èçìåíåíèÿ))

Index " Âåðõíèé/Íèæíèé èíäåêñ ( txt 'U'

" or 'D' '0'-îñòàâèòü áåç èçìåí)

Style_Text " Ñòèëü òåêñòà

"(Bold,Italic,Underline,Çà÷åðêíóò)

" 'X','X',between 1 or 5,'X'

Alignment " Ðàñïîëîæåíèå (ãîðèç/âåðòèê)

"between 1 or 7/between 1 or 4

WrapText " Ïåðåíîñ òåêñòà ïî ñëîâàì ('X')

Orientation" Ïîâîðîò (ãðàäóñîâ) (betwwen -90

" or 90)

ShrinkToFit" Àâòîïîäáîð øèðèíû ('X')

Format_Cell." Ôîðìàò ÿ÷åéêè ('C' - Òåêñò,'N'-

" ÷èñëî, 'F' - ôèíàíñîâûé, 'T' -

" âðåìåííîé, 'D' - Äàòû

************************************************************************

*|----

|*

| Ôîðìàò äëÿ Underline | Ôîðìàò äëÿ BackGround or Color_Font |

*|----

|----

|*

*| 1 - áåç | 1 - Ïðîçðà÷íûé

*| 2 - îäíî ïîä÷åðêèâ ïî çíà÷ | 2 - Áåëûé

*| 3 - äâà ïîä÷åðêèâ ïî çíà÷ | 3 - Êðàñíûé

*| 4 - îäíî ïîä÷åðêèâ ïî ÿ÷åéêå | 4 - Çåëåíûé

*| 5 - äâà ïîä÷åðêèâ ïî ÿ÷åéêå | 5 - Ñèíèé

*|----

-

| 6 - Æåëòûé

*| Alignment | 7 - Ïóðïóðíûé

*|----

-

| 8 - Ñâ. ñèíèé

*| ãîðèç | âåðòèê | 9 - Ò. êðàñíûé

*|----

|----

-

10 - Ò. çåëåíûé

*

1 - ïî çîíå

1 - ïî âåðõó

11 - Ò. ñèíèé

*

2 - ïî ëåâîìó

2 - ïî öåíòðó

15 - Ñåðûé

*

3 - ïî öåíòðe

3 - íèæí. êðàé

16 - Ò. Ñåðûé

*

4 - ïðàâûé

4 - ïî âûñîòå

-

*

5 - ñ çàïîëíå

-

*

6 - ïî øèðèíå

*| 7 - ïî öåíòðó|

*| âûäåëåíèÿ|

*----

|*

Data:

Atributes(4),

Alignm(2).

*

Atributes = Style_Text.

Alignm = Alignment.

CALL METHOD OF excel_SHEET 'cells' = excel_CELL NO FLUSH

EXPORTING #1 = Row #2 = Column.

call method of excel_cell 'Font' = excel_variable1 NO FLUSH.

if Name_Font ne ''.

set property of excel_variable1 'Name' = Name_Font NO FLUSH.

endif.

If Size_Font between 1 and 72. " Ðàçìåð øðèôòà

set property of excel_variable1 'Size' = Size_Font NO FLUSH.

endif.

If Color_Font between 1 and 56. " Öâåò øðèôòà

set property of excel_variable1 'ColorIndex' = Color_Font NO FLUSH.

endif.

If BackGround between 1 and 56. " Ôîí ÿ÷åéêè

call method of excel_cell 'Interior' = excel_variable2.

SET PROPERTY OF excel_variable2 'ColorIndex' = BackGround NO FLUSH.

endif.

If Index eq 'U' or Index eq 'u'. " Âåðõíèé èíäåêñ

set property of excel_VARIABLE1 'Superscript' = 'True' NO FLUSH.

elseIf Index eq 'D' or Index eq 'd'. " Íèæíèé èíäåêñ

set property of excel_VARIABLE1 'Subscript' = 'True' NO FLUSH.

endif.

If ATRIBUTES0(1) eq 'X' or ATRIBUTES0(1) eq 'x'. " Æèðíûé

set property of excel_variable1 'Bold' = 'True' NO FLUSH.

endif.

If ATRIBUTES1(1) eq 'X' or ATRIBUTES1(1) eq 'x'. " Êóðñèâ

set property of excel_variable1 'Italic' = 'True' NO FLUSH.

endif.

If ATRIBUTES+2(1) between '2' and '5'. " Ïîä÷åðêèâàíèå

set property of excel_variable1 'Underline'

= ATRIBUTES+2(1) NO FLUSH.

endif.

If ATRIBUTES3(1) eq 'X' or ATRIBUTES3(1) eq 'x'."Çà÷åðêíóòûé òåêñò

set property of excel_variable1 'Strikethrough'

= 'True' NO FLUSH.

endif.

If Alignm+0(1) between 1 and 7.

set property of excel_cell 'HorizontalAlignment'

= Alignm+0(1) NO FLUSH.

endif.

If Alignm+1(1) between 1 and 4.

set property of excel_cell 'VerticalAlignment'

= Alignm+1(1) NO FLUSH.

endif.

If WrapText eq 'X' or WrapText eq 'x'. " Ïåðåíîñ òåêñòà ïî ñëîâàì

set property of excel_cell 'WrapText' = 'True' NO FLUSH.

endif.

If Orientation between -90 and 90. " Orientation" Ïîâîðîò (ãðàäóñîâ)

set property of excel_cell 'Orientation'

= Orientation NO FLUSH.

endif.

If ShrinkToFit eq 'X' or ShrinkToFit eq 'x'. " Àâòîïîäáîð øèðèíû

set property of excel_cell 'ShrinkToFit' = 'True' NO FLUSH.

endif.

Case Format_Cell.

When 'N' or 'n'. " ×èñëîâîé ôîðìàò

set property of excel_Cell 'NumberFormat' = '0.00' NO FLUSH.

When 'F' or 'f'. " ôèíàíñîâûé ôîðìàò

set property of excel_Cell 'NumberFormat'

= '#,##0.00' NO FLUSH.

When 'C' or 'c'. " Òåêñòîâûé ôîðìàò

set property of excel_Cell 'NumberFormat' = '@' NO FLUSH.

When 'D' or 'd'. " Äàòà ôîðìàò

set property of excel_Cell 'NumberFormat' = 'm/d/yy' NO FLUSH.

When 'T' or 't'. " Âðåìÿ ôîðìàò

set property of excel_Cell 'NumberFormat' = 'h:mm:ss' NO FLUSH.

endcase.

endform. " Form Cell_Atributes_excel

*&----

&*

*& Height_Row_excel

*&----

&*

Form Height_Row_excel USING

Row

Height_Row

Name_Font

Color_Font

Size_Font

Style_Text

BackGround

Alignment

Format_Row.

Data:

Atributes(3),

Alignm(2).

*

Atributes = Style_Text.

Alignm = Alignment.

CALL METHOD OF excel_SHEET 'rows' = excel_ROW NO FLUSH

EXPORTING #1 = Row.

if Height_Row > 0.

SET PROPERTY OF excel_ROW 'RowHeight' = Height_Row NO FLUSH.

endif.

call method of excel_ROW 'Font' = excel_variable1 NO FLUSH.

if Name_Font ne ''.

set property of excel_variable1 'Name' = Name_Font NO FLUSH.

endif.

If Size_Font between 1 and 72. " Ðàçìåð øðèôòà

set property of excel_variable1 'Size' = Size_Font NO FLUSH.

endif.

If Color_Font between 1 and 56. " Öâåò øðèôòà

set property of excel_variable1 'ColorIndex' = Color_Font NO FLUSH.

endif.

If BackGround between 1 and 56. " Ôîí ÿ÷åéêè

call method of excel_ROW 'Interior' = excel_variable2.

SET PROPERTY OF excel_variable2 'ColorIndex' = BackGround NO FLUSH.

endif.

If ATRIBUTES0(1) eq 'X' or ATRIBUTES0(1) eq 'x'. " Æèðíûé

set property of excel_variable1 'Bold' = 'True' NO FLUSH.

endif.

If ATRIBUTES1(1) eq 'X' or ATRIBUTES1(1) eq 'x'. " Êóðñèâ

set property of excel_variable1 'Italic' = 'True' NO FLUSH.

endif.

If ATRIBUTES+2(1) between '2' and '5'. " Ïîä÷åðêèâàíèå

set property of excel_variable1 'Underline'

= ATRIBUTES+2(1) NO FLUSH.

endif.

If Alignm+0(1) between 1 and 7.

set property of excel_row 'HorizontalAlignment'

= Alignm+0(1) NO FLUSH.

endif.

If Alignm+1(1) between 1 and 4.

set property of excel_row 'VerticalAlignment'

= Alignm+1(1) NO FLUSH.

endif.

Case Format_Row.

When 'N' or 'n'. " ×èñëîâîé ôîðìàò

set property of excel_Row 'NumberFormat' = '0' NO FLUSH.

When 'F' or 'f'. " ôèíàíñîâûé ôîðìàò

set property of excel_Row 'NumberFormat' = '#,##0.00' NO FLUSH.

When 'C' or 'c'. " Òåêñòîâûé ôîðìàò

set property of excel_Row 'NumberFormat' = '@' NO FLUSH.

When 'D' or 'd'. " Äàòà ôîðìàò

set property of excel_Row 'NumberFormat' = 'm/d/yy' NO FLUSH.

When 'T' or 't'. " Âðåìÿ ôîðìàò

set property of excel_Row 'NumberFormat' = 'h:mm:ss' NO FLUSH.

endcase.

endform. " Form Height_Row_excel.

*&----

&*

*& Width_Column_excel

*&----

&*

Form Width_Column_excel using

Column

Width_Column

Name_Font

Color_Font

Size_Font

Style_Text

BackGround

Alignment

Format_Column.

Data:

Atributes(3),

Alignm(2).

*

Atributes = Style_Text.

Alignm = Alignment.

CALL METHOD OF excel_SHEET 'Columns' = excel_Column NO FLUSH

EXPORTING #1 = Column.

if Width_Column > 0.

SET PROPERTY OF excel_Column 'ColumnWidth' = Width_Column NO FLUSH.

endif.

call method of excel_Column 'Font' = excel_variable1 NO FLUSH.

if Name_Font ne ''.

set property of excel_variable1 'Name' = Name_Font NO FLUSH.

endif.

If Size_Font between 1 and 72. " Ðàçìåð øðèôòà

set property of excel_variable1 'Size' = Size_Font NO FLUSH.

endif.

If Color_Font between 1 and 56. " Öâåò øðèôòà

set property of excel_variable1 'ColorIndex' = Color_Font NO FLUSH.

endif.

If BackGround between 1 and 56. " Ôîí ÿ÷åéêè

call method of excel_Column 'Interior' = excel_variable2 NO FLUSH.

SET PROPERTY OF excel_variable2 'ColorIndex' = BackGround NO FLUSH.

endif.

If ATRIBUTES0(1) eq 'X' or ATRIBUTES0(1) eq 'x'. " Æèðíûé

set property of excel_variable1 'Bold' = 'True' NO FLUSH.

endif.

If ATRIBUTES1(1) eq 'X' or ATRIBUTES1(1) eq 'x'. " Êóðñèâ

set property of excel_variable1 'Italic' = 'True' NO FLUSH.

endif.

If ATRIBUTES+2(1) between '2' and '5'. " Ïîä÷åðêèâàíèå

set property of excel_variable1 'Underline'

= ATRIBUTES+2(1) NO FLUSH.

endif.

If Alignm+0(1) between 1 and 7.

set property of excel_Column 'HorizontalAlignment'

= Alignm+0(1) NO FLUSH.

endif.

If Alignm+1(1) between 1 and 4.

set property of excel_Column 'VerticalAlignment'

= Alignm+1(1) NO FLUSH.

endif.

Case Format_Column.

When 'N' or 'n'. " ×èñëîâîé ôîðìàò

set property of excel_Column 'NumberFormat' = '0' NO FLUSH.

When 'F' or 'f'. " ôèíàíñîâûé ôîðìàò

set property of excel_Column 'NumberFormat'

= '#,##0.00' NO FLUSH.

When 'C' or 'c'. " Òåêñòîâûé ôîðìàò

set property of excel_Column 'NumberFormat' = '@' NO FLUSH.

When 'D' or 'd'. " Äàòà ôîðìàò

set property of excel_Column 'NumberFormat' = 'm/d/yy' NO FLUSH.

When 'T' or 't'. " Âðåìÿ ôîðìàò

set property of excel_Column 'NumberFormat' = 'h:mm:ss' NO FLUSH.

endcase.

endform. " Width_Column_excel

*&----

&*

*& Form Merge_Cells_excel

*& Ñëèòü ÿ÷åéêè

*&----

&*

Form Merge_Cells_excel using row1 " ñòðîêà 1 ÿ÷åéêè

column1 " ñòîëáåö 1 ÿ÷åéêè

row2 " ñòðîêà 2 ÿ÷åéêè

column2." ñòîëáåö 2 ÿ÷åéêè

  • Âûïîëíåíèå Visual Basic êîìàíäû

  • Range(Cells(row1, column1), Cells(row2, column2)).Clear

  • Range(Cells(row1, column1), Cells(row2, column2)).Merge

CALL METHOD OF excel_SHEET 'cells' = excel_Variable2 NO FLUSH

EXPORTING #1 = row1 #2 = column1.

CALL METHOD OF excel_SHEET 'cells' = excel_Variable3 NO FLUSH

EXPORTING #1 = row2 #2 = column2.

CALL METHOD OF excel_SHEET 'range' = excel_Variable1 NO FLUSH

EXPORTING #1 = excel_Variable2

#2 = excel_Variable3.

  • CALL METHOD OF EXcEL_Variable1 'Clear'.

  • CALL METHOD OF EXcEL_Variable1 'Activate'.

CALL METHOD OF excel_Variable1 'Merge' NO FLUSH.

endform. " Form Merge_excel_Cells

*&----

&*

*& Form Merge_Cells_excel_BOLD

*& Ñëèòü ÿ÷åéêè, æèðíàÿ ãðàíèöà

*&----

&*

Form Merge_Cells_excel_BOLD using row1 " ñòðîêà 1 ÿ÷åéêè

column1 " ñòîëáåö 1 ÿ÷åéêè

row2 " ñòðîêà 2 ÿ÷åéêè

column2." ñòîëáåö 2 ÿ÷åéêè

  • Âûïîëíåíèå Visual Basic êîìàíäû

  • Range(Cells(row1, column1), Cells(row2, column2)).Clear

  • Range(Cells(row1, column1), Cells(row2, column2)).Merge

CALL METHOD OF excel_SHEET 'cells' = excel_Variable2 NO FLUSH

EXPORTING #1 = row1 #2 = column1.

CALL METHOD OF excel_SHEET 'cells' = excel_Variable3 NO FLUSH

EXPORTING #1 = row2 #2 = column2.

CALL METHOD OF excel_SHEET 'range' = excel_Variable1 NO FLUSH

EXPORTING #1 = excel_Variable2

#2 = excel_Variable3.

  • CALL METHOD OF EXcEL_Variable1 'Clear'.

  • CALL METHOD OF EXcEL_Variable1 'Activate'.

CALL METHOD OF excel_Variable1 'Merge' NO FLUSH.

CALL METHOD OF excel_Variable1 'Borders' = excel_Variable4 NO FLUSH.

*set property of excel_Variable4 'LineStyle' = 1.

set property of excel_Variable4 'Weight' = 3.

endform. " Form Merge_excel_Cells_BOLD

*&----

&*

*& Form Merge_Cells_excel_THIN

*& Ñëèòü ÿ÷åéêè, òîíêàÿ ãðàíèöà

*&----

&*

Form Merge_Cells_excel_THIN using row1 " ñòðîêà 1 ÿ÷åéêè

column1 " ñòîëáåö 1 ÿ÷åéêè

row2 " ñòðîêà 2 ÿ÷åéêè

column2." ñòîëáåö 2 ÿ÷åéêè

  • Âûïîëíåíèå Visual Basic êîìàíäû

  • Range(Cells(row1, column1), Cells(row2, column2)).Clear

  • Range(Cells(row1, column1), Cells(row2, column2)).Merge

CALL METHOD OF excel_SHEET 'cells' = excel_Variable2 NO FLUSH

EXPORTING #1 = row1 #2 = column1.

CALL METHOD OF excel_SHEET 'cells' = excel_Variable3 NO FLUSH

EXPORTING #1 = row2 #2 = column2.

CALL METHOD OF excel_SHEET 'range' = excel_Variable1 NO FLUSH

EXPORTING #1 = excel_Variable2

#2 = excel_Variable3.

  • CALL METHOD OF EXcEL_Variable1 'Clear'.

  • CALL METHOD OF EXcEL_Variable1 'Activate'.

CALL METHOD OF excel_Variable1 'Merge' NO FLUSH.

CALL METHOD OF excel_Variable1 'Borders' = excel_Variable4 NO FLUSH.

*set property of excel_Variable4 'LineStyle' = 1.

set property of excel_Variable4 'Weight' = 2.

endform. " Form Merge_excel_Cells_THIN

*&----

&*

*& Form Hidden_Column_excel

*& ñêðûòü/âèäåòü ñòîëáöû

*&----

&*

Form Hidden_Column_excel using column1 " ñòîëáåö 1

column2 " ñòîëáåö 2

Status.

  • Âûïîëíåíèå Visual Basic êîìàíäû

  • Range(Columns(column1), Columns(column2)).Hidden = True

CALL METHOD OF excel_SHEET 'columns' = excel_Variable2 NO FLUSH

EXPORTING #1 = column1.

CALL METHOD OF excel_SHEET 'columns' = excel_Variable3 NO FLUSH

EXPORTING #1 = column2.

CALL METHOD OF excel_SHEET 'range' = excel_Variable1 NO FLUSH

EXPORTING #1 = excel_Variable2

#2 = excel_Variable3.

if status = 0.

set property of excel_Variable1 'Hidden' = 'True' NO FLUSH.

else.

set property of excel_Variable1 'Hidden' = 'False' NO FLUSH.

endif.

endform. " Form Hidden_Column_excel

*&----

&*

*& Form Border_Cells_excel "Áîðäþð äëÿ ÿ÷ååê

*&----

&*

*& Style_line (num between 0 or 9 ) Boundary (num betwwen 0 or 6)

*& 0 - áåç ëèíèé 1 - ëåâî

*& 1 - îäèíàðíàÿ òîíêàÿ 2 - ïðàâî

*& 2,3 - ïóíêòèð (ðàçí.ïëîòíîñòü) 3 - âåðõ

*& 4,5 - øòðèõïóíêòèð (ðàçí.êîë.ò) 4 - íèç

*& 6 - òîëñòûé øòðèõ ïóíêòèð 5 - ãëàâíàÿ äèàãîíàëü ()

*& 7 - ìåëêèå òî÷êè 6 - ïîáî÷íàÿ äèàãîíàëü (/)

*& 8 - òîëñòûé ïóíêòèð 0 - Âñå ãðàíèöû, êðîìå äèàãîíàëåé

*& 9 - äâîéíàÿ òîíêàÿ

*& Border_Color (num between 0 or 56)

*& Òîëüêî îñíîâíûå öâåòà:

*& 0 - Îñòàâ áåç èçì 4 - Çåëåíûé 8 - Ëèëîâûé

*& 1 - Ïðîçðà÷íûé 5 - Ñèíèé 9 - Áèðþçîâûé

*& 2 - Áåëûé 6 - Æåëòûé

*& 3 - Êðàñíûé 7 - Ïóðïóðíûé

*&----

-

*& Ïðèìåð: perform Border_Cells_excel using 2 2 6 6 3 9 0.

*&----

&*

Form Border_Cells_excel using row1 " ñòðîêà 1 ÿ÷åéêè

column1 " ñòîëáåö 1 ÿ÷åéêè

row2 " ñòðîêà 2 ÿ÷åéêè

column2 " ñòîëáåö 2 ÿ÷åéêè

Border_Color " öâåò áîðäþðà

Style_line " ñòèëü ëèíèè

Boundary. " Ãðàíèöà

  • Âûïîëíåíèå Visual Basic êîìàíä

  • Range(Cells(row1, column1), Cells(row2, column2)).Borders(Boundary).

  • LineStyle = Style_line

  • Range(Cells(row1, column1), Cells(row2, column2)).Borders(Boundary).

  • ColorIndex = Border_Color

CALL METHOD OF excel_SHEET 'cells' = excel_Variable2 NO FLUSH

EXPORTING #1 = row1 #2 = column1.

CALL METHOD OF excel_SHEET 'cells' = excel_Variable3 NO FLUSH

EXPORTING #1 = row2 #2 = column2.

CALL METHOD OF excel_SHEET 'range' = excel_Variable1 NO FLUSH

EXPORTING #1 = excel_Variable2

#2 = excel_Variable3.

if Boundary between 1 and 6.

CALL METHOD OF excel_Variable1 'Borders' = excel_Variable4 NO FLUSH

EXPORTING #1 = Boundary.

else.

CALL METHOD OF excel_Variable1 'Borders' = excel_Variable4 NO FLUSH.

endif.

if Style_line between 1 and 9.

set property of excel_Variable4 'LineStyle' = Style_line NO FLUSH.

endif.

if Style_line between 1 and 56.

set property of excel_Variable4 'ColorIndex'

= Border_Color NO FLUSH.

endif.

endform. " Form Border_Cells_excel

*&----

&*

*& Form Read_Cell_excel "C÷èòàòü çíà÷åíèå ÿ÷åéêè

*&

*&----

&*

Form Read_Cell_excel using row1 " ñòðîêà 1 ÿ÷åéêè

column1 " ñòîëáåö 1 ÿ÷åéêè

Value. " ñ÷èòàííîå çíà÷åíèå ÿ÷åéêè

CALL METHOD OF excel_APPLICATION 'cells' = excel_CELL NO FLUSH

EXPORTING #1 = row1 #2 = column1.

get property of excel_CELL 'FormulaR1C1' = Value.

endform. " Form Read_Cell_excel

*&----

&*

*& Form Read_Cell_excel_num "C÷èòàòü çíà÷åíèå ÿ÷åéêè òîëüêî ÷èñëî

*&

*&----

&*

Form Read_Cell_excel_num using row1 " ñòðîêà 1 ÿ÷åéêè

column1 " ñòîëáåö 1 ÿ÷åéêè

Value. " ñ÷èòàííîå çíà÷åíèå ÿ÷åéêè

CALL METHOD OF excel_APPLICATION 'cells' = excel_CELL NO FLUSH

EXPORTING #1 = row1 #2 = column1.

get property of excel_CELL 'Value' = Value.

endform. " Form Read_Cell_excel

*&----

&*

*& Form Name_Sheet_excel "Çàäàòü èìÿ ðàáî÷åìó ëèñòó

*&

*& ýêâèâàëåíòíàÿ îïåðàöèÿ : ActiveSheet.Name = "Êàðòî÷êà ïîëüçîâàòåëÿ"

*&

*&----

&*

Form Name_Sheet_excel using Name. " Èìÿ ëèñòà

CALL METHOD OF excel_APPLICATION 'ActiveSheet' = excel_Variable1

NO FLUSH.

set property of excel_Variable1 'Name' = Name NO FLUSH.

endform. " Form Name_Sheet_excel

*&----

&*

*& Form Get_Name_Sheet_excel "Çàäàòü èìÿ ðàáî÷åìó ëèñòó

*&

*& ýêâèâàëåíòíàÿ îïåðàöèÿ : ActiveSheet.Name = "Êàðòî÷êà ïîëüçîâàòåëÿ"

*&

*&----

&*

Form Get_Name_Sheet_excel using Name. " Èìÿ ëèñòà

CALL METHOD OF excel_APPLICATION 'ActiveSheet' = excel_Variable1

NO FLUSH.

get property of excel_Variable1 'Name' = Name.

endform. " Form Get_Name_Sheet_excel

*&----

&*

*& Form Grig_Window_excel " Ïîêàçàòü èëè ñêðûòü ñåòêó

*&

*& ýêâèâàëåíòíàÿ îïåðàöèÿ : ActiveWindow.DisplayGridlines = Visible

*&----

&*

Form Grig_Window_excel using Visible. " Âèäíî(True)/Ñêðûòî(False)

CALL METHOD OF excel_APPLICATION 'ActiveWindow' = excel_Variable1

NO FLUSH.

set property of excel_Variable1 'DisplayGridlines' = Visible NO FLUSH.

endform. " Form Grig_Window_excel

*&----

&*

*& Form Screen_Updating_excel " Ïîêàçûâàòü Update ýêðàíà

*&

*& ýêâèâàëåíòíàÿ îïåðàöèÿ : ActiveWindow.DisplayGridlines = Visible

*&----

&*

Form Screen_Updating_excel using Update. " Âèäíî(True)/Ñêðûòî(False)

set property of excel_APPLICATION 'ScreenUpdating' = Update no flush.

endform. " Form Screen_Updating_excel

*&----

&*

*& Form select_all_cells " Âûäåëèòü âñå ÿ÷åéêè

*& è ïðèñâîèòü èì òåêñòîâûé ôîìàò

*& ýêâèâàëåíòíàÿ îïåðàöèÿ : Cells.NumberFormat = "@"

*&----

&*

Form select_all_cells.

CALL METHOD OF excel_APPLICATION 'cells' = excel_CELL NO FLUSH.

  • CALL METHOD OF excel_CELL 'Select' NO FLUSH.

  • CALL METHOD OF excel_APPLICATION 'Selection' = excel_CELL NO FLUSH.

set property of excel_CELL 'NumberFormat' = '@' NO FLUSH.

endform. " select_all_cells

*&----

&*

*& Form paint_range " Çàêðàñèòü äèàïàçîí öâåòîì

*& ýêâèâàëåíòíàÿ îïåðàöèÿ :

*& Range(Cells(row1, column1), Cells(row2, column2)).Interior.

*& ColorIndex = 6

*&----

&*

Form paint_range using row1

column1

row2

column2

color.

CALL METHOD OF excel_SHEET 'cells' = excel_Variable2 NO FLUSH

EXPORTING #1 = row1 #2 = column1.

CALL METHOD OF excel_SHEET 'cells' = excel_Variable3 NO FLUSH

EXPORTING #1 = row2 #2 = column2.

CALL METHOD OF excel_SHEET 'range' = excel_Variable1 NO FLUSH

EXPORTING #1 = excel_Variable2

#2 = excel_Variable3.

CALL METHOD OF excel_Variable1 'Interior' = excel_Variable4 NO FLUSH.

set property of excel_Variable4 'ColorIndex' = color NO FLUSH.

endform. " paint_range

*&----

&*

*& Form SetColumnWidth " Óñòàíîâèòü øèðèíó äëÿ ñòîëáöà

*& ýêâèâàëåíòíàÿ îïåðàöèÿ :

*& Columns(3).ColumnWidth = 30

*&----

&*

Form SetColumnWidth using column Width.

CALL METHOD OF excel_SHEET 'Columns' = excel_CELL NO FLUSH

EXPORTING #1 = column.

SET PROPERTY OF excel_CELL 'ColumnWidth' = Width NO FLUSH.

endform. " SetColumnWidth

*----

*

  • FORM SET_ALL_SIZE *

*----

*

*----

*

  • --> FONT_SIZE *

*----

*

FORM SET_ALL_SIZE USING FONT_SIZE.

CALL METHOD OF excel_APPLICATION 'cells' = excel_CELL NO FLUSH.

CALL METHOD of excel_CELL 'Font' = excel_variable1.

set property of excel_variable1 'Size' = 8.

FREE OBJECT excel_APPLICATION.

ENDFORM.

Read only

vinod_vemuru2
Active Contributor

‎2008 Jun 16 9:17 AM

0 Likes
574

Hi,

I don't think it is possible. Because in ur internal table u can't differentiate on what criteria we have merge the cells. Other option could be modifying ur itab before downloading. If u have same value for some field for the given key criteria then Modify the itab by placing space in that field.So that user can understand this value is repeating.

eg: If ur itab has 3 col.

col1 col2 col3.

A A 10

A B 10

C D 30

C F 30

C G 20.

Then modify ur itab as below.

col1 col2 col3.

A A 10

A B

C D 30

C F

C G 20.

Hope this might be helpfull,

Thanks,

Vinod.