Версия 2010 года.
Изменения.
1.) Работа метода WRITE_TABLE значительно (примерно в 10 раз) ускорена. Теперь работает в 20-30 раз быстрее конкурирующих разработок для вывода в Excel, что по скорости сравнимо с выводом посредством программного формирования XML-файла (а иногда и быстрее). Выводит со скоростью порядка 1000 страниц в минуту.
2.) Убраны завязки с инклудом ZALV_UTILS.
3.) Исправлена одна старая ошибка в методе READ_TABLE.
4.) Добавлена возможность загрузки шаблонов из Web-репозитария.
5.) Добавлена возможность оставлять документ открытым и после выхода из сформировавшей его транзакции.
6.) Еще несколько мелких дополнений.
7.) Добавлен класс lcl_word_document, для работы с MS Word. Пока бета-версия. Корректно работает, к сожалению, не на всех системах (причины пока не найдены). Некорректность связана с тем, что иногда заменяет только первую переменную, при этом выдает системную ошибку и дальше не идет. Если такая ошибка в вашей системе не появляется, то значит для вас это вполне рабочая версия. Выводит значения в переменные (по &ИМЕНИ&) и в таблицы (по номеру таблицы в документе, данные берет из внутренней АВАР-таблицы). Читать не умеет. Исходники см. в следующем сообщении (здесь разместить не дало из-за ограничений в размерах).
Code:
*&---------------------------------------------------------------------*
*& Include ZDOI_UTILS
*&---------------------------------------------------------------------*
*& Классы-утилиты для работы с Excel: чтение и запись.
*& Позволяют как создавать Excel-документы по шаблону, так и читать
*& данные из имеющихся Excel-файлов.
*& В качестве исходных документов/шаблонов могут использоваться как
*& локальные файлы с компьютера, так и хранящиеся в BDS документы.
*& Хранящиеся в BDS документы должны быть присвоены к классу
*& SOFFICEINTEGRATION .
*&---------------------------------------------------------------------*
*& Автор: Амосов В.А.
*&---------------------------------------------------------------------*
*& Использование (пример):
*&---------------------------------------------------------------------*
*& Загружаем шаблон в BDS: запускаем транзакцию OAOR,
*& класс = SOFFICEINTEGRATION,
*& тип класса = OT
*& ключ объекта = MY_FOLDER.
*& F8 (запускаем).
*& Загружаем Excel или Word документ, называем его DOC1 (поле "Описание"
*& в окне атрибутов, появляющемся при загрузке) .
*&---------------------------------------------------------------------*
*& Далее в программе:
*&---------------------------------------------------------------------*
*& DATA: lr_excel TYPE REF TO lcl_excel_document ,
*& lp_var1 TYPE i VALUE '999',
*& lp_var2(30) VALUE 'Это тестовая переменная для вывода'.
*&
*& CREATE OBJECT lr_excel .
*& CALL METHOD lr_excel->load_file
*& EXPORTING
*& p_source = 'MY_FOLDER\DOC1'
*& p_source_type = lcl_excel_document=>c_st_bds
*& p_mode = lcl_excel_document=>c_am_write
*& EXCEPTIONS
*& not_excel = 1.
*&
*& CALL METHOD lr_excel->write_value
*& EXPORTING
*& p_rownr = 4
*& p_colnr = 4
*& p_value = lp_var1.
*&
*& CALL METHOD lr_excel->write_value
*& EXPORTING
*& p_rownr = 5
*& p_colnr = 4
*& p_value = lp_var2.
*&
*& CALL METHOD lr_excel->write_table
*& EXPORTING
*& p_start_rownr = 6
*& p_start_colnr = 1
*& p_end_colnr = 18
*& CHANGING
*& pt_data = gte_excel[].
*&
*& CALL METHOD lr_excel->end_update .
*&---------------------------------------------------------------------*
*& где gte_excel - ваша внутренняя таблица, которую нужно вывести в
*& Excel. Обычная внутренняя таблица, коррелирующая со структурой
*& Excel-документа.
*&---------------------------------------------------------------------*
TYPE-POOLS: sbdst,
soi.
INCLUDE officeintegrationinclude .
DATA: go_factory TYPE REF TO i_oi_document_factory .
TYPES: abadr_tab_id_integer TYPE TABLE OF i .
*----------------------------------------------------------------------*
* CLASS lcl_excel_document DEFINITION
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
CLASS lcl_excel_document DEFINITION.
PUBLIC SECTION.
CONSTANTS:
* Типы источников
c_st_file TYPE string VALUE 'FILE',
c_st_bds TYPE string VALUE 'BDS',
c_st_web TYPE string VALUE 'WEBREP',
c_st_select TYPE string VALUE 'SELECT',
* Режимы доступа
c_am_read TYPE string VALUE 'READ',
c_am_write TYPE string VALUE 'WRITE',
c_am_exchange TYPE string VALUE 'EXCHANGE' .
DATA: table_offset TYPE i,
mode(1) TYPE c,
source_type TYPE string,
source TYPE string ,
persist_ TYPE c ,
retcode TYPE t_oi_ret_string ,
s_appl TYPE ole2_object ,
s_handle TYPE cntl_handle .
METHODS: constructor .
METHODS: end_update .
METHODS: load_file
IMPORTING
p_source TYPE string
p_source_type TYPE string
p_mode TYPE string
p_persist_ TYPE c DEFAULT ' '
EXCEPTIONS not_excel .
METHODS: save_file
IMPORTING
p_destination TYPE string
p_destination_type TYPE string.
METHODS: read_value
IMPORTING
p_rownr TYPE i
p_colnr TYPE i
CHANGING p_value TYPE any.
METHODS: write_value
IMPORTING
p_rownr TYPE i
p_colnr TYPE i
p_value TYPE any.
METHODS: copy_row
IMPORTING
p_template_rownr TYPE i
p_dest_rownr TYPE i
p_start_colnr TYPE i
p_end_colnr TYPE i
ps_data TYPE any.
METHODS: insert_rows
IMPORTING
range TYPE c
count TYPE i.
METHODS: read_table
IMPORTING
p_start_rownr TYPE i
p_start_colnr TYPE i
p_end_colnr TYPE i
CHANGING
pt_data TYPE STANDARD TABLE .
METHODS: set_sheet
IMPORTING
p_sheetname TYPE string .
METHODS: write_table
IMPORTING
p_start_rownr TYPE i
p_start_colnr TYPE i
p_end_colnr TYPE i
CHANGING pt_data TYPE STANDARD TABLE .
METHODS: write_static_ex
IMPORTING
p_start_rownr TYPE i
p_start_colnr TYPE i
p_end_colnr TYPE i
pt_excel_offset TYPE abadr_tab_id_integer
CHANGING pt_data TYPE STANDARD TABLE .
METHODS: write_table_ex
IMPORTING
p_start_rownr TYPE i
p_start_colnr TYPE i
p_end_colnr TYPE i
pt_excel_offset TYPE abadr_tab_id_integer
CHANGING pt_data TYPE STANDARD TABLE .
METHODS: write_static_table
IMPORTING
p_start_rownr TYPE i
p_start_colnr TYPE i
p_end_colnr TYPE i
CHANGING pt_data TYPE STANDARD TABLE .
METHODS: document_data
CHANGING
p_size TYPE i
pt_data TYPE STANDARD TABLE .
METHODS: close_file .
PRIVATE SECTION .
CONSTANTS: c_document_type(13) VALUE soi_doctype_excel97_sheet,
* c_doc_classname TYPE sbdst_classname VALUE 'ZOFFICE_TEMPLATES',
c_doc_classname TYPE sbdst_classname VALUE 'SOFFICEINTEGRATION',
c_doc_classtype TYPE sbdst_classtype VALUE 'OT' .
DATA:
lo_document TYPE REF TO i_oi_document_proxy,
lo_bds TYPE REF TO cl_bds_document_set,
lo_xinterface TYPE REF TO i_oi_spreadsheet,
lt_docdata TYPE sbdst_content,
l_data_size TYPE i,
lt_excel_cells TYPE soi_generic_table,
lwa_excel_cells TYPE soi_generic_item.
METHODS: read_cells
IMPORTING
p_start_row TYPE i
p_row_count TYPE i
p_start_column TYPE i
p_column_count TYPE i .
ENDCLASS. "lCl_excel_document DEFINITION
*---------------------------------------------------------------------*
* CLASS lcl_excel_document IMPLEMENTION
*---------------------------------------------------------------------*
*
*---------------------------------------------------------------------*
CLASS lcl_excel_document IMPLEMENTATION.
METHOD constructor .
retcode = c_oi_errors=>ret_ok.
IF go_factory IS INITIAL.
CALL METHOD c_oi_factory_creator=>get_document_factory
IMPORTING
factory = go_factory
retcode = retcode.
IF retcode NE c_oi_errors=>ret_ok. EXIT. ENDIF.
CALL METHOD go_factory->start_factory
EXPORTING
r3_application_name = 'Документ сгенерирован SAP-системой' "#EC NOTEXT
register_on_close_event = ' '
register_on_custom_event = ' '
IMPORTING
retcode = retcode.
CALL METHOD c_oi_errors=>show_message
EXPORTING
type = 'E'.
ENDIF.
ENDMETHOD. "lcl_excel_document
*===========================================================
METHOD end_update .
IF persist_ EQ ' ' .
CALL FUNCTION 'FLUSH'.
SET PROPERTY OF s_appl 'DisplayAlerts' = 1.
SET PROPERTY OF s_appl 'ScreenUpdating' = 1.
SET PROPERTY OF s_appl 'Visible' = 1.
CALL FUNCTION 'FLUSH'.
ELSE.
DATA: lp_workdir TYPE string,
lp_filename TYPE string,
p_size TYPE i .
DATA: ltp_data TYPE swxmlcont.
cl_gui_frontend_services=>get_sapgui_workdir( CHANGING sapworkdir = lp_workdir ).
CONCATENATE lp_workdir '\' sy-tcode sy-datum sy-uzeit '.xls'
INTO lp_filename.
CALL METHOD me->document_data
CHANGING
p_size = p_size
pt_data = ltp_data[].
CALL METHOD cl_gui_frontend_services=>gui_download
EXPORTING
filename = lp_filename
filetype = 'BIN'
CHANGING
data_tab = ltp_data[]
EXCEPTIONS
file_write_error = 1
OTHERS = 99.
CONCATENATE '"' lp_filename '"' INTO lp_filename.
CALL METHOD cl_gui_frontend_services=>execute
EXPORTING
application = 'Excel'
parameter = lp_filename
operation = 'OPEN'.
CALL FUNCTION 'FLUSH'.
SET PROPERTY OF s_appl 'DisplayAlerts' = 1.
SET PROPERTY OF s_appl 'ScreenUpdating' = 1.
SET PROPERTY OF s_appl 'Visible' = 1.
CALL FUNCTION 'FLUSH'.
CALL METHOD me->close_file( ) .
ENDIF.
MESSAGE s006(zcommon) .
ENDMETHOD . "end_update
METHOD load_file .
* IMPORTING
* p_source TYPE string
* p_source_type TYPE string
* p_mode TYPE string
* p_persist_ type c default ' '
* EXCEPTIONS not_excel .
source = p_source.
mode = p_mode .
source_type = p_source_type .
persist_ = p_persist_ .
IF lo_document IS INITIAL.
CALL METHOD go_factory->get_document_proxy
EXPORTING
document_type = c_document_type
IMPORTING
document_proxy = lo_document
retcode = retcode.
CALL METHOD c_oi_errors=>show_message
EXPORTING
type = 'E'.
ENDIF.
IF source_type = c_st_file .
CALL FUNCTION 'GUI_UPLOAD'
EXPORTING
filename = source
filetype = 'BIN'
* HAS_FIELD_SEPARATOR = ' '
* HEADER_LENGTH = 0
* READ_BY_LINE = 'X'
* DAT_MODE = ' '
* CODEPAGE = ' '
* IGNORE_CERR = ABAP_TRUE
* REPLACEMENT = '#'
* CHECK_BOM = ' '
* IMPORTING
* FILELENGTH =
* HEADER =
TABLES
data_tab = lt_docdata
EXCEPTIONS
file_open_error = 1
file_read_error = 2
no_batch = 3
gui_refuse_filetransfer = 4
invalid_type = 5
no_authority = 6
unknown_error = 7
bad_data_format = 8
header_not_allowed = 9
separator_not_allowed = 10
header_too_long = 11
unknown_dp_error = 12
access_denied = 13
dp_out_of_memory = 14
disk_full = 15
dp_timeout = 16
OTHERS = 17
.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
CALL METHOD lo_document->open_document_from_table
EXPORTING
document_size = l_data_size
document_table = lt_docdata
* DOCUMENT_TITLE = ' '
* NO_FLUSH = ' '
* OPEN_INPLACE = ' '
open_readonly = 'X'
* PROTECT_DOCUMENT = ' '
* ONSAVE_MACRO = ' '
* STARTUP_MACRO = ''
* IMPORTING
* ERROR =
* RETCODE =
.
CALL METHOD c_oi_errors=>show_message
EXPORTING
type = 'E'.
ELSEIF source_type = c_st_bds .
IF lo_bds IS INITIAL.
CREATE OBJECT lo_bds .
ENDIF.
DATA: l_object_key TYPE sbdst_object_key,
l_docname TYPE bapisignat-prop_value,
lt_doc_signature TYPE sbdst_signature,
lwa_doc_signature LIKE LINE OF lt_doc_signature,
lt_doc_uris TYPE sbdst_uri,
lwa_doc_uris LIKE LINE OF lt_doc_uris,
l_doc_url TYPE bapiuri-uri .
SPLIT source AT '\' INTO l_object_key l_docname.
CLEAR: lwa_doc_signature.
REFRESH lt_doc_signature.
lwa_doc_signature-prop_name = 'DESCRIPTION'.
lwa_doc_signature-prop_value = l_docname .
APPEND lwa_doc_signature TO lt_doc_signature.
CALL METHOD lo_bds->get_info
EXPORTING
classname = c_doc_classname
classtype = c_doc_classtype
object_key = l_object_key
CHANGING
* components = doc_components
signature = lt_doc_signature.
CALL METHOD lo_bds->get_with_url
EXPORTING
classname = c_doc_classname
classtype = c_doc_classtype
object_key = l_object_key
CHANGING
uris = lt_doc_uris
signature = lt_doc_signature.
READ TABLE lt_doc_uris INTO lwa_doc_uris INDEX 1.
l_doc_url = lwa_doc_uris-uri.
CALL METHOD lo_document->open_document
EXPORTING
open_inplace = ' '
document_url = l_doc_url.
ELSEIF source_type = c_st_web .
DATA: ltp_html TYPE STANDARD TABLE OF w3html,
ltp_mime TYPE STANDARD TABLE OF w3mime,
lp_val(255) TYPE c,
lsp_wkey TYPE wwwdatatab,
lp_docsize TYPE i,
lp_dockey TYPE wwwparams-objid .
lp_dockey = p_source .
CALL FUNCTION 'WWWPARAMS_READ'
EXPORTING
relid = 'MI'
objid = lp_dockey
name = 'filesize'
IMPORTING
value = lp_val
EXCEPTIONS
entry_not_exists = 1
OTHERS = 2.
lp_docsize = lp_val .
lsp_wkey-relid = 'MI'.
lsp_wkey-objid = lp_dockey .
CALL FUNCTION 'WWWDATA_IMPORT'
EXPORTING
key = lsp_wkey
TABLES
html = ltp_html
mime = ltp_mime
EXCEPTIONS
wrong_object_type = 1
import_error = 2
OTHERS = 3.
lo_document->open_document_from_table(
EXPORTING document_size = lp_docsize
document_table = ltp_mime ) .
* IMPORTING error = error ).
ENDIF.
DATA: lp_error TYPE REF TO i_oi_error,
lp_retcode TYPE soi_ret_string.
CALL METHOD lo_document->get_document_handle
IMPORTING
error = lp_error
handle = s_handle
retcode = lp_retcode.
GET PROPERTY OF s_handle-obj 'Application' = s_appl.
CALL FUNCTION 'FLUSH'.
SET PROPERTY OF s_appl 'DisplayAlerts' = 0.
SET PROPERTY OF s_appl 'ScreenUpdating' = 0.
SET PROPERTY OF s_appl 'Visible' = 0.
CALL FUNCTION 'FLUSH'.
IF p_mode = c_am_write .
CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
EXPORTING
percentage = 0
text = 'Подождите, производится формирование документа в Excel...'.
DATA: l_ftest TYPE dmbtr,
l_ctest(7) .
l_ftest = '1000.99' .
WRITE l_ftest TO l_ctest LEFT-JUSTIFIED . "#EC UOM_IN_MES
SET PROPERTY OF s_appl
'DecimalSeparator' = l_ctest+4(1).
SET PROPERTY OF s_appl
'ThousandsSeparator' = ' '.
ENDIF.
DATA: l_is_available TYPE i .
CALL METHOD lo_document->has_spreadsheet_interface
* EXPORTING
* NO_FLUSH = ' '
IMPORTING
* ERROR =
is_available = l_is_available .
* RETCODE =
.
IF NOT l_is_available IS INITIAL .
CALL METHOD lo_document->get_spreadsheet_interface
EXPORTING
no_flush = ' '
IMPORTING
* ERROR =
sheet_interface = lo_xinterface
* RETCODE =
.
CALL METHOD c_oi_errors=>show_message
EXPORTING
type = 'E'.
ELSE.
MESSAGE e005(zcommon) RAISING not_excel .
ENDIF.
ENDMETHOD. "load_file
METHOD save_file .
* IMPORTING
* p_destination TYPE string
* p_destination_type TYPE string.
DATA: lp_destination(255) .
lp_destination = p_destination .
CALL METHOD lo_document->save_as
EXPORTING
file_name = lp_destination
* NO_FLUSH = ' '
* PROMPT_USER = ' '
* IMPORTING
* ERROR =
* RETCODE =
.
ENDMETHOD. "save_file
METHOD close_file .
DATA: l_has_changed TYPE i,
l_retcode TYPE i.
FREE OBJECT: s_appl .
IF NOT lo_document IS INITIAL.
CALL METHOD lo_document->is_destroyed
IMPORTING
ret_value = l_retcode.
IF l_retcode IS INITIAL.
CALL METHOD lo_document->close_document
EXPORTING
do_save = ' '
IMPORTING
has_changed = l_has_changed
retcode = retcode.
IF retcode NE c_oi_errors=>ret_ok.
EXIT.
ENDIF.
ENDIF.
CALL METHOD lo_document->release_document
IMPORTING
retcode = retcode.
ELSE.
retcode = c_oi_errors=>ret_document_not_open.
ENDIF.
ENDMETHOD. "write_table
METHOD read_value.
CALL METHOD me->read_cells
EXPORTING
p_start_row = p_rownr
p_row_count = 1
p_start_column = p_colnr
p_column_count = 1.
DATA: ln_rownr(4) TYPE n,
ln_colnr(4) TYPE n .
ln_rownr = p_rownr .
ln_colnr = p_colnr .
READ TABLE lt_excel_cells INTO lwa_excel_cells INDEX 1.
* WITH KEY row = ln_rownr
* column = ln_colnr .
CHECK sy-subrc = 0.
p_value = lwa_excel_cells-value .
ENDMETHOD. "read_value
*========================================================================
METHOD write_value .
* IMPORTING
* p_rownr TYPE i
* p_colnr TYPE i
* p_value TYPE any.
DATA: lt_rangedefs TYPE soi_dimension_table ,
lwa_rangedefs TYPE soi_dimension_item,
lt_ranges TYPE soi_range_list,
l_nbuf(4) TYPE n.
lwa_rangedefs-row = p_rownr .
lwa_rangedefs-column = p_colnr .
lwa_rangedefs-rows = 1 .
lwa_rangedefs-columns = 1 .
APPEND lwa_rangedefs TO lt_rangedefs .
REFRESH lt_excel_cells .
CLEAR lwa_excel_cells .
lwa_excel_cells-row = l_nbuf = 1 . " p_rownr .
lwa_excel_cells-column = l_nbuf = 1 . " p_colnr .
lwa_excel_cells-value = p_value .
APPEND lwa_excel_cells TO lt_excel_cells .
CALL METHOD lo_xinterface->set_ranges_data
EXPORTING
no_flush = 'X'
ranges = lt_ranges
contents = lt_excel_cells
updating = 1
rangesdef = lt_rangedefs
IMPORTING
* ERROR =
retcode = retcode
.
CALL METHOD c_oi_errors=>show_message
EXPORTING
type = 'E'.
ENDMETHOD. "write_value
METHOD insert_rows.
ENDMETHOD. "insert_rows
*========================================================================
**********************************************************************
METHOD read_table .
**********************************************************************
* IMPORTING
* p_start_rownr TYPE i
* p_start_colnr TYPE i
* p_end_colnr TYPE i
* CHANGING
* pt_data TYPE STANDARD TABLE .
FIELD-SYMBOLS: <l_field> TYPE ANY,
<l_line> TYPE ANY .
DATA: lc_ecolnr(4) TYPE c,
l_old_rownr(4),
l_colnr TYPE i,
l_startrow TYPE i,
l_colcount TYPE i,
l_ftype,
l_is_alles,
lr_line TYPE REF TO data,
l_is_initial(1) TYPE c.
l_startrow = p_start_rownr .
l_colcount = p_end_colnr - p_start_colnr + 1 .
WRITE l_colcount TO lc_ecolnr LEFT-JUSTIFIED .
CREATE DATA lr_line LIKE LINE OF pt_data .
ASSIGN lr_line->* TO <l_line> .
DO .
CALL METHOD me->read_cells
EXPORTING
p_start_row = l_startrow
p_row_count = 50
p_start_column = p_start_colnr
p_column_count = l_colcount.
l_colnr = 1.
l_is_initial = 'X'.
LOOP AT lt_excel_cells INTO lwa_excel_cells .
IF l_old_rownr NE lwa_excel_cells-row .
IF l_old_rownr NE ' '.
APPEND <l_line> TO pt_data .
CLEAR <l_line> .
l_is_initial = 'X'.
l_colnr = 1.
ENDIF.
l_old_rownr = lwa_excel_cells-row .
ENDIF .
ASSIGN COMPONENT l_colnr OF STRUCTURE <l_line> TO <l_field>.
CHECK sy-subrc IS INITIAL .
<l_field> = lwa_excel_cells-value .
IF NOT lwa_excel_cells-value IS INITIAL .
CLEAR l_is_initial .
ENDIF.
IF lwa_excel_cells-column = lc_ecolnr AND l_is_initial = 'X' .
l_is_alles = 'X'.
EXIT .
ENDIF.
ADD 1 TO l_colnr .
ENDLOOP.
IF l_is_initial = ' ' .
APPEND <l_line> TO pt_data .
ENDIF.
IF l_is_alles = 'X' OR sy-subrc = 4.
EXIT.
ENDIF.
ADD 51 TO l_startrow .
ENDDO.
DESCRIBE TABLE pt_data LINES table_offset .
table_offset = table_offset + p_start_rownr .
ENDMETHOD. "read_table
**********************************************************************
METHOD write_table .
**********************************************************************
* IMPORTING
* p_start_rownr TYPE i
* p_start_colnr TYPE i
* p_end_colnr TYPE i
* CHANGING pt_data TYPE STANDARD TABLE .
DATA: lt_rangedefs TYPE soi_dimension_table ,
lwa_rangedefs TYPE soi_dimension_item,
lt_ranges TYPE soi_range_list,
l_nbuf(4) TYPE n,
l_colnr TYPE i,
l_rownr TYPE i,
* l_nrownr(2) TYPE n,
l_macroname(30) TYPE c,
l_error_string(100) TYPE c,
l_macrocount TYPE i,
l_end_colnr TYPE i,
l_insert_cnt TYPE i,
l_table_cursor TYPE i,
l_excel_cursor TYPE i,
l_range_cursor TYPE i,
l_tobe_count TYPE i,
l_delta TYPE i,
l_nextdelta TYPE i,
l_deltan(6) TYPE n,
l_already TYPE i,
l_curr_templ_name(40).
DATA: ls_range TYPE ole2_object,
lp_error TYPE REF TO i_oi_error,
lp_retcode TYPE soi_ret_string.
IF p_end_colnr > 0.
l_end_colnr = p_end_colnr .
ELSE.
DATA: l_type(1) TYPE c.
DESCRIBE FIELD pt_data TYPE l_type COMPONENTS l_end_colnr .
l_end_colnr = l_end_colnr + p_start_colnr - 1 .
ENDIF.
lwa_rangedefs-columns = l_end_colnr - p_start_colnr + 1 .
*=============== вставка новых строк =====================*
CALL METHOD lo_xinterface->insert_range_dim
EXPORTING
* NO_FLUSH = ' '
name = 'BLOCK_TEMPLATE000001'
left = p_start_colnr
top = p_start_rownr
rows = 1
columns = lwa_rangedefs-columns
* UPDATING = -1
* SHEETNAME = ''
* IMPORTING
* ERROR =
* RETCODE =
.
CALL METHOD lo_xinterface->select_range
EXPORTING
rangename = 'BLOCK_TEMPLATE000001'
no_flush = 'X'.
GET PROPERTY OF s_appl 'Selection' = ls_range queue-only.
CALL METHOD OF ls_range 'Copy' NO FLUSH QUEUE-ONLY.
DATA: lc_data TYPE i .
lc_data = LINES( pt_data ) .
l_tobe_count = l_insert_cnt = lc_data - 1 .
l_delta = 1 .
l_nextdelta = 1 .
l_already = 0 .
DO l_insert_cnt TIMES.
IF l_already = l_nextdelta AND
l_tobe_count GE l_nextdelta AND
l_nextdelta LT '500000' .
** Если уже напечатали 10, 100, 1000, 10000 строк, и осталось еще
** не меньше, идем на увеличение в 10 раз: копируем напечатанный блок
** (берем его в качестве шаблона) и вставляем его.
l_curr_templ_name = l_deltan = l_nextdelta .
CONCATENATE 'BLOCK_TEMPLATE' l_curr_templ_name
INTO l_curr_templ_name .
CALL METHOD lo_xinterface->insert_range_dim
EXPORTING
* NO_FLUSH = ' '
name = l_curr_templ_name
left = p_start_colnr
top = p_start_rownr
rows = l_nextdelta
columns = lwa_rangedefs-columns
* UPDATING = -1
* SHEETNAME = ''
* IMPORTING
* ERROR =
* RETCODE =
.
CALL METHOD lo_xinterface->select_range
EXPORTING
rangename = l_curr_templ_name
no_flush = 'X'.
GET PROPERTY OF s_appl 'Selection' = ls_range queue-only.
CALL METHOD OF ls_range 'Copy' NO FLUSH QUEUE-ONLY.
l_delta = l_nextdelta .
l_nextdelta = l_nextdelta * 10 .
ELSEIF l_tobe_count LT l_delta .
** Если осталось вставить строк меньше, чем строк в текущем шаблоне,
** идем на уменьшение: берем ранее созданный RANGE, который в 10 раз
** меньше текущего, и начинаем вставлять уже его.
WHILE l_tobe_count LT l_delta .
l_delta = l_delta / 10 .
ENDWHILE .
l_curr_templ_name = l_deltan = l_delta .
CONCATENATE 'BLOCK_TEMPLATE' l_curr_templ_name
INTO l_curr_templ_name .
CALL METHOD lo_xinterface->select_range
EXPORTING
rangename = l_curr_templ_name
no_flush = 'X'.
GET PROPERTY OF s_appl 'Selection' = ls_range queue-only.
CALL METHOD OF ls_range 'Copy' NO FLUSH QUEUE-ONLY.
ENDIF.
CALL METHOD OF ls_range 'Insert'
NO FLUSH QUEUE-ONLY
EXPORTING #1 = -4121.
l_tobe_count = l_tobe_count - l_delta .
l_already = l_already + l_delta .
IF l_tobe_count = 0 .
EXIT .
ENDIF .
ENDDO.
SET PROPERTY OF s_appl 'CutCopyMode' = 0 queue-only.
FREE OBJECT: ls_range.
FIELD-SYMBOLS: <l_field> TYPE ANY ,
<l_line> TYPE ANY .
l_excel_cursor = p_start_rownr .
LOOP AT pt_data ASSIGNING <l_line> .
l_table_cursor = sy-tabix .
IF l_range_cursor = 0 .
* Данные рейнджа устанавливаем пачками не более 9999 позиций, т.к.
* номер строки для установки данных рейнджа - не более 9999 позиций (потом 0000).
CLEAR lwa_rangedefs .
REFRESH lt_rangedefs .
lwa_rangedefs-column = p_start_colnr .
lwa_rangedefs-columns = l_end_colnr - p_start_colnr + 1 .
lwa_rangedefs-row = l_excel_cursor .
lwa_rangedefs-rows = lc_data - l_table_cursor + 1.
IF lwa_rangedefs-rows > 9999 .
lwa_rangedefs-rows = 9999 .
ENDIF.
APPEND lwa_rangedefs TO lt_rangedefs .
* l_nrownr = p_start_rownr .
*=============== Конец вставки новых строк =====================*
CALL METHOD lo_xinterface->insert_range_dim
EXPORTING
* NO_FLUSH = ' '
name = 'TABLE'
left = p_start_colnr
top = lwa_rangedefs-row
rows = lwa_rangedefs-rows
columns = lwa_rangedefs-columns
* UPDATING = -1
* SHEETNAME = ''
* IMPORTING
* ERROR =
* RETCODE =
.
REFRESH lt_excel_cells .
ENDIF.
ADD 1 TO l_range_cursor .
DO lwa_rangedefs-columns TIMES .
l_colnr = sy-index .
ASSIGN COMPONENT l_colnr OF STRUCTURE <l_line> TO <l_field>.
CHECK sy-subrc = 0.
CLEAR lwa_excel_cells .
lwa_excel_cells-row = l_nbuf = l_range_cursor .
lwa_excel_cells-column = l_nbuf = l_colnr .
PERFORM write_cell_value USING <l_field>
CHANGING lwa_excel_cells-value .
* IMG 20.12.07
* WRITE <l_field> TO lwa_excel_cells-value LEFT-JUSTIFIED .
APPEND lwa_excel_cells TO lt_excel_cells .
ENDDO .
ADD 1 TO: l_excel_cursor .
IF l_range_cursor = 9999 OR l_table_cursor = lc_data .
CLEAR l_range_cursor .
CALL METHOD lo_xinterface->set_ranges_data
EXPORTING
* NO_FLUSH = ' '
ranges = lt_ranges
contents = lt_excel_cells
updating = 1
rangesdef = lt_rangedefs
IMPORTING
* ERROR =
retcode = retcode
.
CALL METHOD c_oi_errors=>show_message
EXPORTING
type = 'E'.
ENDIF.
ENDLOOP.
IF l_insert_cnt > 0 .
table_offset = p_start_rownr + l_insert_cnt .
ELSE.
table_offset = p_start_rownr .
ENDIF.
ENDMETHOD. "write_table
**********************************************************************
METHOD write_table_ex .
**********************************************************************
* IMPORTING
* p_start_rownr TYPE i
* p_start_colnr TYPE i
* p_end_colnr TYPE i
* pt_excel_offset TYPE abadr_tab_id_integer
* CHANGING pt_data TYPE STANDARD TABLE .
DATA: lt_rangedefs TYPE soi_dimension_table ,
lwa_rangedefs TYPE soi_dimension_item,
lt_ranges TYPE soi_range_list,
l_nbuf(4) TYPE n,
l_colnr TYPE i,
l_rownr TYPE i,
* l_nrownr(2) TYPE n,
l_macroname(30) TYPE c,
l_error_string(100) TYPE c,
l_macrocount TYPE i,
l_end_colnr TYPE i,
l_insert_cnt TYPE i,
l_table_cursor TYPE i,
l_excel_cursor TYPE i,
l_range_cursor TYPE i.
DATA: ls_range TYPE ole2_object,
lp_error TYPE REF TO i_oi_error,
lp_retcode TYPE soi_ret_string.
DATA:
l_excel_offset LIKE LINE OF pt_excel_offset
, l_component TYPE i
.
IF p_end_colnr > 0.
l_end_colnr = p_end_colnr .
ELSE.
DATA: l_type(1) TYPE c.
DESCRIBE FIELD pt_data TYPE l_type COMPONENTS l_end_colnr .
l_end_colnr = l_end_colnr + p_start_colnr - 1 .
ENDIF.
lwa_rangedefs-columns = l_end_colnr - p_start_colnr + 1 .
CALL METHOD lo_xinterface->insert_range_dim
EXPORTING
* NO_FLUSH = ' '
name = 'LINE_TEMPLATE'
left = p_start_colnr
top = p_start_rownr
rows = 1
columns = lwa_rangedefs-columns
* UPDATING = -1
* SHEETNAME = ''
* IMPORTING
* ERROR =
* RETCODE =
.
DATA: lc_data TYPE i .
lc_data = LINES( pt_data ) .
*=============== вставка новых строк =====================*
CALL METHOD lo_xinterface->select_range
EXPORTING
rangename = 'LINE_TEMPLATE'
no_flush = 'X'.
GET PROPERTY OF s_appl 'Selection' = ls_range queue-only.
CALL METHOD OF ls_range 'Copy' NO FLUSH QUEUE-ONLY.
l_insert_cnt = lc_data - 1 .
DO l_insert_cnt TIMES.
CALL METHOD OF ls_range 'Copy' NO FLUSH QUEUE-ONLY.
CALL METHOD OF ls_range 'Insert'
NO FLUSH QUEUE-ONLY
EXPORTING #1 = -4121.
SET PROPERTY OF s_appl 'CutCopyMode' = 0 queue-only.
ENDDO.
SET PROPERTY OF s_appl 'CutCopyMode' = 0 queue-only.
FREE OBJECT: ls_range.
*=============== Конец вставки новых строк =====================*
FIELD-SYMBOLS: <l_field> TYPE ANY ,
<l_line> TYPE ANY .
l_excel_cursor = p_start_rownr .
LOOP AT pt_data ASSIGNING <l_line> .
l_table_cursor = sy-tabix .
IF l_range_cursor = 0 .
* Данные рейнджа устанавливаем пачками не более 9999 позиций, т.к.
* номер строки для установки данных рейнджа - не более 9999 позиций (потом 0000).
CLEAR lwa_rangedefs .
REFRESH lt_rangedefs .
lwa_rangedefs-column = p_start_colnr .
lwa_rangedefs-columns = l_end_colnr - p_start_colnr + 1 .
lwa_rangedefs-row = l_excel_cursor .
lwa_rangedefs-rows = lc_data - l_table_cursor + 1.
IF lwa_rangedefs-rows > 9999 .
lwa_rangedefs-rows = 9999 .
ENDIF.
APPEND lwa_rangedefs TO lt_rangedefs .
CALL METHOD lo_xinterface->insert_range_dim
EXPORTING
* NO_FLUSH = ' '
name = 'TABLE'
left = p_start_colnr
top = lwa_rangedefs-row
rows = lwa_rangedefs-rows
columns = lwa_rangedefs-columns
* UPDATING = -1
* SHEETNAME = ''
* IMPORTING
* ERROR =
* RETCODE =
.
REFRESH lt_excel_cells .
ENDIF.
ADD 1 TO l_range_cursor .
l_component = 1 .
DO lwa_rangedefs-columns TIMES .
l_colnr = sy-index .
CLEAR l_excel_offset.
READ TABLE pt_excel_offset[] INTO l_excel_offset INDEX l_component.
CLEAR lwa_excel_cells .
lwa_excel_cells-row = l_nbuf = l_range_cursor .
lwa_excel_cells-column = l_nbuf = l_colnr .
IF l_colnr = l_excel_offset.
ASSIGN COMPONENT l_component OF STRUCTURE <l_line> TO <l_field>.
IF sy-subrc = 0.
PERFORM write_cell_value USING <l_field>
CHANGING lwa_excel_cells-value .
ADD 1 TO l_component.
ENDIF.
ELSE.
lwa_excel_cells-value = ' ' .
ENDIF.
APPEND lwa_excel_cells TO lt_excel_cells .
ENDDO .
ADD 1 TO: l_excel_cursor .
IF l_range_cursor = 9999 OR l_table_cursor = lc_data .
CLEAR l_range_cursor .
CALL METHOD lo_xinterface->set_ranges_data
EXPORTING
* NO_FLUSH = ' '
ranges = lt_ranges
contents = lt_excel_cells
updating = 1
rangesdef = lt_rangedefs
IMPORTING
* ERROR =
retcode = retcode
.
CALL METHOD c_oi_errors=>show_message
EXPORTING
type = 'E'.
ENDIF.
ENDLOOP.
IF l_insert_cnt > 0 .
table_offset = p_start_rownr + l_insert_cnt .
ELSE.
table_offset = p_start_rownr .
ENDIF.
ENDMETHOD. "write_table_ex
METHOD write_static_table .
* IMPORTING
* p_start_rownr TYPE i
* p_start_colnr TYPE i
* p_end_colnr TYPE i
* CHANGING pt_data TYPE STANDARD TABLE .
DATA: lt_rangedefs TYPE soi_dimension_table ,
lwa_rangedefs TYPE soi_dimension_item,
lt_ranges TYPE soi_range_list,
l_nbuf(4) TYPE n,
l_colnr TYPE i,
l_rownr TYPE i,
l_nrownr(2) TYPE n,
l_error_string(100) TYPE c,
l_end_colnr TYPE i .
DATA: l_type(1) TYPE c.
IF p_end_colnr > 0.
l_end_colnr = p_end_colnr .
ELSE.
DESCRIBE FIELD pt_data TYPE l_type COMPONENTS l_end_colnr .
l_end_colnr = l_end_colnr + p_start_colnr - 1 .
ENDIF.
lwa_rangedefs-row = p_start_rownr .
lwa_rangedefs-column = p_start_colnr .
DESCRIBE TABLE pt_data LINES lwa_rangedefs-rows .
lwa_rangedefs-columns = l_end_colnr - p_start_colnr + 1 .
APPEND lwa_rangedefs TO lt_rangedefs .
FIELD-SYMBOLS: <l_field> TYPE ANY ,
<l_line> TYPE ANY .
l_nrownr = p_start_rownr .
CALL METHOD lo_xinterface->insert_range_dim
EXPORTING
* NO_FLUSH = ' '
name = 'TABLE'
left = p_start_colnr
top = p_start_rownr
rows = lwa_rangedefs-rows
columns = lwa_rangedefs-columns
* UPDATING = -1
* SHEETNAME = ''
* IMPORTING
* ERROR =
* RETCODE =
.
REFRESH lt_excel_cells .
LOOP AT pt_data ASSIGNING <l_line> .
l_rownr = sy-tabix .
DO lwa_rangedefs-columns TIMES .
l_colnr = sy-index .
ASSIGN COMPONENT l_colnr OF STRUCTURE <l_line> TO <l_field>.
CHECK sy-subrc = 0.
CLEAR lwa_excel_cells .
lwa_excel_cells-row = l_nbuf = l_rownr .
lwa_excel_cells-column = l_nbuf = l_colnr .
* DESCRIBE FIELD <l_field> TYPE l_type .
* if l_type eq 'd' or l_type eq 'd'
* WRITE <l_field> TO lwa_excel_cells-value LEFT-JUSTIFIED .
* lwa_excel_cells-value = <l_field> .
PERFORM write_cell_value USING <l_field>
CHANGING lwa_excel_cells-value .
APPEND lwa_excel_cells TO lt_excel_cells .
ENDDO .
ENDLOOP.
CALL METHOD lo_xinterface->set_ranges_data
EXPORTING
* NO_FLUSH = ' '
ranges = lt_ranges
contents = lt_excel_cells
updating = 1
rangesdef = lt_rangedefs
IMPORTING
* ERROR =
retcode = retcode
.
CALL METHOD c_oi_errors=>show_message
EXPORTING
type = 'E'.
table_offset = lwa_rangedefs-rows + p_start_rownr - 1 .
ENDMETHOD. "write_static_table
**********************************************************************
METHOD write_static_ex .
**********************************************************************
* IMPORTING
* p_start_rownr TYPE i
* p_start_colnr TYPE i
* p_end_colnr TYPE i
* pt_excel_offset TYPE abadr_tab_id_integer
* CHANGING pt_data TYPE STANDARD TABLE .
DATA: lt_rangedefs TYPE soi_dimension_table ,
lwa_rangedefs TYPE soi_dimension_item,
lt_ranges TYPE soi_range_list,
l_nbuf(4) TYPE n,
l_colnr TYPE i,
l_rownr TYPE i,
* l_nrownr(2) TYPE n,
l_macroname(30) TYPE c,
l_error_string(100) TYPE c,
l_macrocount TYPE i,
l_end_colnr TYPE i,
l_insert_cnt TYPE i,
l_table_cursor TYPE i,
l_excel_cursor TYPE i,
l_range_cursor TYPE i.
DATA: ls_range TYPE ole2_object,
lp_error TYPE REF TO i_oi_error,
lp_retcode TYPE soi_ret_string.
DATA:
l_excel_offset LIKE LINE OF pt_excel_offset
, l_component TYPE i
.
IF p_end_colnr > 0.
l_end_colnr = p_end_colnr .
ELSE.
DATA: l_type(1) TYPE c.
DESCRIBE FIELD pt_data TYPE l_type COMPONENTS l_end_colnr .
l_end_colnr = l_end_colnr + p_start_colnr - 1 .
ENDIF.
lwa_rangedefs-columns = l_end_colnr - p_start_colnr + 1 .
DATA: lc_data TYPE i .
lc_data = LINES( pt_data ) .
FIELD-SYMBOLS: <l_field> TYPE ANY ,
<l_line> TYPE ANY .
l_excel_cursor = p_start_rownr .
LOOP AT pt_data ASSIGNING <l_line> .
l_table_cursor = sy-tabix .
IF l_range_cursor = 0 .
* Данные рейнджа устанавливаем пачками не более 9999 позиций, т.к.
* номер строки для установки данных рейнджа - не более 9999 позиций (потом 0000).
CLEAR lwa_rangedefs .
REFRESH lt_rangedefs .
lwa_rangedefs-column = p_start_colnr .
lwa_rangedefs-columns = l_end_colnr - p_start_colnr + 1 .
lwa_rangedefs-row = l_excel_cursor .
lwa_rangedefs-rows = lc_data - l_table_cursor + 1.
IF lwa_rangedefs-rows > 9999 .
lwa_rangedefs-rows = 9999 .
ENDIF.
APPEND lwa_rangedefs TO lt_rangedefs .
CALL METHOD lo_xinterface->insert_range_dim
EXPORTING
* NO_FLUSH = ' '
name = 'TABLE'
left = p_start_colnr
top = lwa_rangedefs-row
rows = lwa_rangedefs-rows
columns = lwa_rangedefs-columns
* UPDATING = -1
* SHEETNAME = ''
* IMPORTING
* ERROR =
* RETCODE =
.
REFRESH lt_excel_cells .
ENDIF.
ADD 1 TO l_range_cursor .
l_component = 1 .
DO lwa_rangedefs-columns TIMES .
l_colnr = sy-index .
CLEAR l_excel_offset.
READ TABLE pt_excel_offset[] INTO l_excel_offset INDEX l_component.
CLEAR lwa_excel_cells .
lwa_excel_cells-row = l_nbuf = l_range_cursor .
lwa_excel_cells-column = l_nbuf = l_colnr .
IF l_colnr = l_excel_offset.
ASSIGN COMPONENT l_component OF STRUCTURE <l_line> TO <l_field>.
IF sy-subrc = 0.
PERFORM write_cell_value USING <l_field>
CHANGING lwa_excel_cells-value .
ADD 1 TO l_component.
ENDIF.
ELSE.
lwa_excel_cells-value = ' ' .
ENDIF.
APPEND lwa_excel_cells TO lt_excel_cells .
ENDDO .
ADD 1 TO: l_excel_cursor .
IF l_range_cursor = 9999 OR l_table_cursor = lc_data .
CLEAR l_range_cursor .
CALL METHOD lo_xinterface->set_ranges_data
EXPORTING
* NO_FLUSH = ' '
ranges = lt_ranges
contents = lt_excel_cells
updating = 1
rangesdef = lt_rangedefs
IMPORTING
* ERROR =
retcode = retcode
.
CALL METHOD c_oi_errors=>show_message
EXPORTING
type = 'E'.
ENDIF.
ENDLOOP.
IF l_insert_cnt > 0 .
table_offset = p_start_rownr + l_insert_cnt .
ELSE.
table_offset = p_start_rownr .
ENDIF.
ENDMETHOD. "write_static_ex
METHOD read_cells .
DATA: lt_rangedefs TYPE soi_dimension_table ,
lwa_rangedefs TYPE soi_dimension_item,
lt_ranges TYPE soi_range_list .
lwa_rangedefs-row = p_start_row .
lwa_rangedefs-column = p_start_column .
lwa_rangedefs-rows = p_row_count .
lwa_rangedefs-columns = p_column_count .
APPEND lwa_rangedefs TO lt_rangedefs .
CALL METHOD lo_xinterface->get_ranges_data
EXPORTING
* NO_FLUSH = ' '
* ALL = ''
* UPDATING = -1
rangesdef = lt_rangedefs
IMPORTING
contents = lt_excel_cells
* ERROR =
retcode = retcode
CHANGING
ranges = lt_ranges
.
CALL METHOD c_oi_errors=>show_message
EXPORTING
type = 'E'.
ENDMETHOD . "read_cells
**********************************************************************
METHOD copy_row .
**********************************************************************
* IMPORTING
* p_template_rownr TYPE i
* p_dest_rownr TYPE i
* p_start_colnr TYPE i
* p_end_colnr TYPE i
* ps_data TYPE any.
DATA: lt_rangedefs TYPE soi_dimension_table ,
lwa_rangedefs TYPE soi_dimension_item,
lt_ranges TYPE soi_range_list,
l_nbuf(4) TYPE n,
l_colnr TYPE i,
l_rownr TYPE i,
l_nrownr(2) TYPE n,
l_macroname(30) TYPE c,
l_error_string(100) TYPE c,
l_macrocount TYPE i,
l_end_colnr TYPE i .
IF p_end_colnr > 0.
l_end_colnr = p_end_colnr .
ELSE.
DATA: l_type(1) TYPE c.
DESCRIBE FIELD ps_data TYPE l_type COMPONENTS l_end_colnr .
l_end_colnr = l_end_colnr + p_start_colnr - 1 .
ENDIF.
lwa_rangedefs-columns = l_end_colnr - p_start_colnr + 1 .
FIELD-SYMBOLS: <l_field> TYPE ANY .
l_nrownr = 1 .
*=============== Вставка новых строк =====================*
DATA: s_handle TYPE cntl_handle,
ls_range TYPE ole2_object,
ls_destrange TYPE ole2_object,
s_appl TYPE ole2_object,
lp_error TYPE REF TO i_oi_error,
lp_retcode TYPE soi_ret_string.
CALL METHOD lo_xinterface->insert_range_dim
EXPORTING
no_flush = 'X'
name = 'LINE_TEMPLATE'
left = p_start_colnr
top = p_template_rownr
rows = 1
columns = lwa_rangedefs-columns
* UPDATING = -1
* SHEETNAME = ''
* IMPORTING
* ERROR =
* RETCODE =
.
*
* CALL METHOD lo_document->get_document_handle
* IMPORTING
* error = lp_error
* handle = s_handle
* retcode = lp_retcode.
*
* GET PROPERTY OF s_handle-obj 'Application' = s_appl.
*
IF sy-subrc = 0.
CALL METHOD lo_xinterface->select_range
EXPORTING
rangename = 'LINE_TEMPLATE'
no_flush = 'X'.
GET PROPERTY OF s_appl 'Selection' = ls_range queue-only.
CALL METHOD OF ls_range 'Copy' NO FLUSH QUEUE-ONLY.
CALL METHOD lo_xinterface->insert_range_dim
EXPORTING
no_flush = 'X'
name = 'NEW_LINE'
left = p_start_colnr
top = p_dest_rownr
rows = 1
columns = lwa_rangedefs-columns
* UPDATING = -1
* SHEETNAME = ''
* IMPORTING
* ERROR =
* RETCODE =
.
CALL METHOD lo_xinterface->select_range
EXPORTING
rangename = 'NEW_LINE'
no_flush = 'X'.
GET PROPERTY OF s_appl 'Selection' = ls_destrange queue-only.
CALL METHOD OF ls_destrange 'Insert'
NO FLUSH QUEUE-ONLY
EXPORTING #1 = -4121.
SET PROPERTY OF s_appl 'CutCopyMode' = 0 queue-only.
FREE OBJECT: ls_range.
ENDIF.
*=============== Конец вставки новый строк =====================*
REFRESH lt_excel_cells .
DO lwa_rangedefs-columns TIMES .
l_colnr = sy-index .
ASSIGN COMPONENT l_colnr OF STRUCTURE ps_data TO <l_field>.
CHECK sy-subrc = 0.
CLEAR lwa_excel_cells .
lwa_excel_cells-row = l_nbuf = 1 .
lwa_excel_cells-column = l_nbuf = l_colnr .
lwa_excel_cells-value = <l_field> .
APPEND lwa_excel_cells TO lt_excel_cells .
ENDDO .
lwa_rangedefs-row = p_dest_rownr .
lwa_rangedefs-column = p_start_colnr .
lwa_rangedefs-rows = 1 .
lwa_rangedefs-columns = l_end_colnr - p_start_colnr + 1 .
APPEND lwa_rangedefs TO lt_rangedefs .
CALL METHOD lo_xinterface->set_ranges_data
EXPORTING
no_flush = 'X'
ranges = lt_ranges
contents = lt_excel_cells
updating = 1
rangesdef = lt_rangedefs
IMPORTING
* ERROR =
retcode = retcode
.
CALL METHOD c_oi_errors=>show_message
EXPORTING
type = 'E'.
table_offset = p_dest_rownr .
ENDMETHOD. " copy_row
METHOD set_sheet .
* using
* p_sheetname TYPE string .
DATA: lp_sheetname(30) TYPE c ,
lp_retcode TYPE soi_ret_string .
lp_sheetname = p_sheetname .
CALL METHOD lo_xinterface->select_sheet
EXPORTING
name = lp_sheetname
* NO_FLUSH = ' '
IMPORTING
* ERROR =
retcode = lp_retcode
.
CLEAR table_offset .
ENDMETHOD. " set_sheet
METHOD document_data .
* CHANGING
* p_size TYPE i
* pt_data TYPE STANDARD TABLE
CALL METHOD lo_document->save_document_to_table
* EXPORTING
* NO_FLUSH = ' '
* IMPORTING
* ERROR =
* RETCODE =
CHANGING
document_size = p_size
document_table = pt_data
.
ENDMETHOD . "document_data
ENDCLASS. "lCl_excel_document DEFINITION
*&---------------------------------------------------------------------*
*& Form write_cell_value
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_<L_FIELD> text
* <--P_LWA_EXCEL_CELLS_VALUE text
*----------------------------------------------------------------------*
FORM write_cell_value USING p_source
CHANGING p_cells_value.
DATA: l_type(1) TYPE c.
DESCRIBE FIELD p_source TYPE l_type .
CASE l_type .
WHEN 'D' .
IF NOT ( p_source CO '0' OR p_source IS INITIAL ) .
IF p_source CO '0123456789' .
WRITE p_source TO p_cells_value .
ELSE.
p_cells_value = p_source .
ENDIF.
ENDIF.
WHEN 'F' OR 'P' OR 'i' .
CLEAR p_cells_value .
p_cells_value = ABS( p_source ) .
SHIFT p_cells_value LEFT DELETING LEADING space .
* IMG 20.12.07 Еще необходимо заменить . на ,
REPLACE '.' IN p_cells_value WITH ','.
IF p_source < 0 .
CONCATENATE '-' p_cells_value INTO p_cells_value .
ENDIF.
WHEN OTHERS .
WRITE p_source TO p_cells_value LEFT-JUSTIFIED .
ENDCASE .
ENDFORM. " write_cell_value
*&---------------------------------------------------------------------*
*& Form read_cell_value
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_<L_FIELD> text
* <--P_LWA_EXCEL_CELLS_VALUE text
*----------------------------------------------------------------------*
FORM read_cell_value USING p_source
p_convexit TYPE dd01l-convexit
CHANGING p_value.
DATA: l_type(1) TYPE c,
l_funcname(32) .
DESCRIBE FIELD p_value TYPE l_type .
CASE l_type .
WHEN 'D' .
IF NOT ( p_source CO '0' OR p_source IS INITIAL ) .
IF p_source CO '0123456789' .
p_value = p_source .
ELSE.
CALL FUNCTION 'CONVERSION_EXIT_DATEX_INPUT'
EXPORTING
input = p_source
IMPORTING
output = p_value.
ENDIF.
ENDIF.
WHEN OTHERS .
IF NOT p_convexit IS INITIAL .
CONCATENATE 'CONVERSION_EXIT_' p_convexit '_INPUT' INTO l_funcname .
CALL FUNCTION l_funcname
EXPORTING
input = p_source
IMPORTING
output = p_value.
.
ELSE.
p_value = p_source .
* WRITE p_source TO p_value .
ENDIF.
ENDCASE .
ENDFORM. " write_cell_value