*&---------------------------------------------------------------------* *& Include Z_HR_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. 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:\Temp\excel1.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:\Temp\excel1.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 ATRIBUTES+0(1) eq 'X' or ATRIBUTES+0(1) eq 'x'. " Жирный set property of excel_variable1 'Bold' = 'True' NO FLUSH. endif. If ATRIBUTES+1(1) eq 'X' or ATRIBUTES+1(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 ATRIBUTES+3(1) eq 'X' or ATRIBUTES+3(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 ATRIBUTES+0(1) eq 'X' or ATRIBUTES+0(1) eq 'x'. " Жирный set property of excel_variable1 'Bold' = 'True' NO FLUSH. endif. If ATRIBUTES+1(1) eq 'X' or ATRIBUTES+1(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 ATRIBUTES+0(1) eq 'X' or ATRIBUTES+0(1) eq 'x'. " Жирный set property of excel_variable1 'Bold' = 'True' NO FLUSH. endif. If ATRIBUTES+1(1) eq 'X' or ATRIBUTES+1(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.
|
|