Re: excel output format

Former Member — Mon, 16 Jun 2008 08:13:11 GMT

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.

topic Re: excel output format in Application Development and Automation Discussions

excel output format

Re: excel output format

Re: excel output format

Re: excel output format

Re: excel output format