Здравствуйте, коллеги! Нужно сделать программу для загрузки информации из excel файла.
Много раз делала это для текстового файла: в SM35 проходила путь ручного ввода (в нужной транзакции) и затем по записанной системой программе создавала свою BDC программу. Но теперь у меня Excel. Причем в этом excel не только таблица, но и шапка с информацией над таблицей.
Т.е. в программе как-то надо будет указывать конкретные ячейки, из которых брать данные...
Может кто-то может поделиться примером программы? Буду благодарна за любую помощь.

Excel всегда таблица, сохраняйте как текстовый файл и дальше по накатанной.

ФМ: ALSM_EXCEL_TO_INTERNAL_TABLE
При необходимости - расковырять внутренности ФМ и написать свою программу на их основе

INCLUDE Z_HR_LIBRARY_EXCEL.
DATA:
user_action_c TYPE I,
file_count_c TYPE I,
file_tab_c LIKE STANDARD TABLE OF FILE_TABLE,
file_table_line_c TYPE FILE_TABLE,
wa_fc TYPE file_table,
stmp TYPE string.

selection-screen begin of block sw with frame title TEXT-001.
PARAMETERS: fc(255) DEFAULT 'C:\' OBLIGATORY.
selection-screen end of block sw.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR fc.
PERFORM ass_ovr_fc.
START-OF-SELECTION.
perform excel_data.


FORM ASS_OVR_FC .
Data:
excel_c type string.
CONCATENATE 'Файл EXCEL(*.xlsx)|*.xlsx|' 'Файл EXCEL(*.xls)|*.xls' INTO excel_c.

CALL METHOD CL_GUI_FRONTEND_SERVICES=>FILE_OPEN_DIALOG

EXPORTING
WINDOW_TITLE = 'Диалог выбора ресурса для загрузки из системы 1С...'
* DEFAULT_EXTENSION = 'xls'
FILE_FILTER = excel_c
INITIAL_DIRECTORY = 'C:\'
CHANGING
FILE_TABLE = file_tab_c
RC = file_count_c
USER_ACTION = user_action_c
EXCEPTIONS
FILE_OPEN_DIALOG_FAILED = 1
CNTL_ERROR = 2
ERROR_NO_GUI = 3
NOT_SUPPORTED_BY_GUI = 4
OTHERS = 5.
IF SY-SUBRC <> 0.
EXIT.
ELSE.
READ TABLE file_tab_c INTO wa_fc INDEX 1.
fc = wa_fc-filename.
ENDIF.
ENDFORM. " ASS_OVR_FC

FORM EXCEL_DATA .
Data:
n type string,
stmp type string.
IF m1 = 'X'.
* 1c
PERFORM Open_File_excel USING fc 0.
row = 1.
DO.
ADD 1 TO row.

n = row.
CONCATENATE 'Считывание данных 1C!--' n 'строка' into stmp SEPARATED BY space.
perform display_process_status using stmp.

PERFORM Read_Cell_excel USING row 1 val1.
PERFORM Read_Cell_excel USING row 2 val2.
PERFORM Read_Cell_excel USING row 3 val3.
PERFORM Read_Cell_excel USING row 4 val4.
PERFORM Read_Cell_excel USING row 5 val5.
PERFORM Read_Cell_excel USING row 6 val6.
PERFORM Read_Cell_excel USING row 7 val7.

IF val3 = ''. EXIT.
ELSE.
SHIFT val1 LEFT DELETING LEADING SPACE.
SHIFT val2 LEFT DELETING LEADING SPACE.
SHIFT val3 LEFT DELETING LEADING SPACE.
SHIFT val4 LEFT DELETING LEADING SPACE.
SHIFT val5 LEFT DELETING LEADING SPACE.
SHIFT val6 LEFT DELETING LEADING SPACE.
SHIFT val7 LEFT DELETING LEADING SPACE.

wa_exl1c-filial = val1.

call function 'CONVERSION_EXIT_ALPHA_INPUT'
exporting
input = val2
importing
output = wa_exl1c-vbelv.

call function 'CONVERSION_EXIT_ALPHA_INPUT'
exporting
input = val3
importing
output = wa_exl1c-kunnr.


* wa_exl1c-vbelv = .
* wa_exl1c-kunnr = .

TRANSLATE val4 to UPPER CASE.
if val4 = 'ТЕНГЕ'.
select single matnr from zcard_purse into wa_exl1c-matnr
where
purse_name = val4.
call function 'CONVERSION_EXIT_ALPHA_OUTPUT'
exporting
input = wa_exl1c-matnr
importing
output = wa_exl1c-matnr.
else.
wa_exl1c-matnr = val4.
endif.

wa_exl1c-litr = val5.
wa_exl1c-price = val6.
wa_exl1c-ostat = val7.
if val4 = ''.
append wa_exl1c to tb_exl1c_t.
else.
append wa_exl1c to tb_exl1c.
endif.
clear: wa_exl1c.

ENDIF.
ENDDO.
* закрываем фаил
CALL METHOD OF excel_book 'CLOSE'.
CALL METHOD OF excel_application 'QUIT'.
COMMIT WORK.
PERFORM Quit_Application_excel.
MESSAGE s001(zfimessages).

*&---------------------------------------------------------------------*
*& 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.