SAP 成本中心计划 KP06如何实现用EXCEL上传

可编辑修改精选全文完整版SAP CO成本要素会计CEL与成本中心会计CCA配置与实务操作Guide目录SAP CO成本要素会计CEL与成本中心会计CCA配置与实务操作Guide (1)1.SPRO IMG 配置 (2)1.1.维护成本控制范围 (2)1.2.分配公司代码至成本控制范围 (2)1.3.激活CO组件和会计年度 (3)1.4.维护成本控制凭证的编号范围 (4)1.5.为成本业务事务组分配编号范围 (5)1.6.维护版本 (6)1.7.创建成本要素组 (6)1.8.创建初级成本要素 (7)1.9.创建成本中心组 (9)1.10.创建成本中心 (9)1.11.定义作业分配成本要素KA06 (11)1.12.创建作业类型组KLH1 (12)1.13.创建作业类型KL01 (13)2.业务过账 (15)1.14.创建费用过账凭证，完成CO凭证记账。

F-02 (15)1.15.成本要素重过账与成本中心重过帐KB11N (16)1.16.成本要素重过账与成本中心重过帐显示KB13N (17)1.17.成本要素重过账与成本中心重过帐冲销KB14N (18)1.18.查询成本中心实际报表S_ALR_87013611 (19)1.19.成本中心作业类型计划—输入作业类型计划数量 (20)1.20.成本中心作业类型计划—输入作业类型计划价值 (21)1.21.成本中心作业类型计划—计划价格计算 (23)1.22.查询成本中心计划报表S_ALR_87013611 (24)1.23.创建次级成本要素-分摊成本要素KA06 (25)1.24.创建成本分摊循环KSU1 (26)1.25.执行分摊循环KSU5 (29)1.26.查询成本分摊报表S_ALR_87013611 (30)1.SPRO IMG 配置1.1. 维护成本控制范围1.1.1.IMG路径：控制-一般控制-组织结构-维护成本控制范围输入“控制范围编码”、“控制范围名称”、“跨公司成本核算”、“货币类型”、“科目表”、“会计年度变式”等信息，其中，“科目表”和“会计年度变式”必须与公司代码FI配置中的主帐套0L配置一致。

一直对这部分内容有点困惑,在网上找了好久没有找到相关资料。

自己琢磨了几天弄出来了。

整理下思路,分享下先看看操作中都有什么信息需要输入在KP06的主界面,输入版本,会计年度选择附加—EXCEL计划-上载路径或文件: 选择上传文件，需要格式为TXT或者CSV文件描述: 这里和上传的格式是对应的，相同的格式在配置的时候会对应到一个文件描述十进制记数法:这里一般选择下面的选项,我们中国的习惯是，做百分位 . 做小数点CSV文件中的分隔符:这里一般是,执行的时候就点击小闹钟通过操作界面，我们看到需要我们配置实现的是2个东西文件和文件描述创建文件,就是创建计划编制格式路径如下，一般是选择第一个创建成本要素计划编制的计划编制格式可以通过复制创建，当然你要想在KP06看看你要参考哪个格式我创建的就是按照季显示和上传成本要素的格式定义好格式后，还要创建计划者参数文件,把格式分配给计划者参数文件路径如下图定义已定义用户的计划员参数文件SAP的惯例，还是用复制的方式，我创建了ZTEST1。

目前我们有了计划者参数文件,和文件（格式）。

还差文件描述。

这个东西我找了好久.最后在一个SAP的help文档中找到了答案.Defining an Excel Template1. CO—PA： Execute the planning method Enter planning data， using the related parameter set. Doingso takes you to an unformatted Excel sheet. There you choose Settings Formatting.Other applications： In the IMG for defining the planner profile， choose Default Parameters and enter values for the variables defined in the planning layout。

利用VBA实现Excel与SAP的数据交互与处理在企业管理中，Excel和SAP是最常用的两个工具。

Excel作为数据处理和分析的基础工具，而SAP则是企业资源规划（ERP）系统的代表。

为了更高效地管理和处理数据，许多企业都需要将Excel与SAP进行数据交互和处理。

在这篇文章中，我们将探讨如何利用VBA（Visual Basic for Applications）实现Excel与SAP的数据交互与处理。

首先，我们需要了解VBA的基本原理。

VBA是一种面向对象的编程语言，可以用于自动化和定制Microsoft Office应用程序。

在Excel中使用VBA可以增强其功能，包括与其他程序（如SAP）进行数据交互。

为了能够与SAP进行数据交互，我们需要安装和配置SAPGUI（Graphical User Interface）。

SAP GUI是与SAP系统进行交互的标准接口，一般情况下，企业都会提供给员工安装。

安装完成后，我们可以通过VBA代码来连接SAP系统并获取数据。

连接SAP系统的第一步是创建一个SAP连接对象。

我们可以使用"CreateObject"函数来创建一个SAP连接对象，并设置相应的属性，如SAP服务器地址、用户名和密码。

接下来，我们可以使用"Connection"方法来连接到SAP系统，并打开一个SAP会话。

一旦与SAP系统建立了连接，我们就可以使用VBA来执行各种操作，如读取和写入数据，运行特定的SAP事务码等。

对于数据的读取，我们可以使用"Execute"方法来执行一个SAP查询，并将结果存储在一个Excel工作表中。

例如，我们可以使用"Range"对象来指定要写入数据的单元格，然后使用"CopyFromRecordset"方法将查询结果复制到指定的单元格范围。

除了读取数据外，我们还可以使用VBA来写入数据到SAP系统中。

对于成本中心成本要素计划，在SAP系统中通过KP06进行录入。

若成本中心个数很多(100个)、且成本要素也很多(50个)，这时就需要录入成本中心计划 5000个(=100*50)，这时手工录入工作量就很大，工作量一大就容易出错。

这时就需要用批导入方式来上载计划了。

对于批导入的实现，可以采用BDC方式(程序或手工都行)，但BDC方式也比较麻烦(此方法这里不作叙述)。

SAP系统也提供了EXCEL上载方式，这种方式相对简单容易操作。

相应的处理方法如下：1.定义“Planning Layout”路径： IMG -> Controlling -> Cost Center Accounting -> Planning -> Manual Planning -> User-Defined Planning Layouts -> Create Planning Layouts for Cost Element Planning事务码:KP651.1 定义 lead column1.2 定义Key figure定义 1月到12月。

1.3 定义“Gen. data selection”2.定义“User-Defined Planner Profiles”路径： IMG -> Controlling -> Cost Center Accounting -> Planning -> Manual Planning -> Define User-Defined Planner Profiles2.1定义名称2.2定义“General Controlling”2.3定义“Layouts for Controlling”2.4定义“Default parameters”(1.保存 file description 2. 保存 Save Excel layout 3.保存退出)3. 模板下载事务码: KP063.1 选择 planner profile选择刚才定义的 planner profile选择planner profile 后，重新回到KP06的画面：如上图，点击EXCEL中的菜单: 文件，然后选择另存为本地的EXCEL文件。

form frm_send_mail .data: objpack like sopcklsti1 occurs 2 with header line.data: objhead like solisti1 occurs 1 with header line.* DATA: OBJBIN LIKE SOLIX OCCURS 10 WITH HEADER LINE.data objbin like solisti1 occurs 0 with header line.data: objtxt like solisti1 occurs 10 with header line.data: reclist like somlreci1 occurs 5 with header line.data: objhex like solix occurs 10 with header line.data: doc_chng like sodocchgi1.data: tab_lines like sy-tabix.data:sender_address like soextreci1-receiver value 'account@teamwi '.data: tmpstr type string .data:i_record like solix occurs 0 with header line, "solisti1filelen type i.data: v_lines_bin_all type i ,v_lines_bin type i .data:lv_str type string.data:lv_recname type ad_smtpadr .data:lt_mail like standard table of zcfg_mail with header line .data :begin of lt_address occurs 0 ,smtp_addr type ad_smtpadr ,end of lt_address .data :begin of lt_ccaddress occurs 0 ,smtp_addr type ad_smtpadr ,end of lt_ccaddress .*** Mail Objectconcatenate lv_str '贸易料检验批监控' into doc_chng-obj_descr separated by space.*** 邮件正文clear objtxt.objtxt = '你好，：'.append objtxt.clear objtxt.append objtxt.clear objtxt.objtxt = '附件是贸易料检验批清单，请查收，谢谢 '.append objtxt.describe table objtxt lines tab_lines.read table objtxt index tab_lines.doc_chng-doc_size = ( tab_lines - 1 ) * 255 + strlen( objtxt ).* Packing List For the E-mail Bodyobjpack-head_start = 1.objpack-head_num = 0.objpack-body_start = 1.objpack-body_num = tab_lines.objpack-doc_type = 'RAW'.append objpack.**内表作为邮件附件clear: tmpstr,i_record,i_record[].perform itabtostr tables gt_xml using tmpstr.perform strtorecord tables i_record using tmpstr filelen.append lines of i_record to objhex."objbin.describe table i_record lines v_lines_bin.describe table objhex lines v_lines_bin_all."objbinobjhead = '贸易料检验批清单 '.append objhead.* Packing List For the E-mail Attachmentobjpack-transf_bin = 'X'.objpack-head_start = 1.objpack-head_num = 0.objpack-body_start = 1.objpack-body_num = v_lines_bin.objpack-obj_descr = '贸易料检验批清单'.objpack-obj_name = '贸易料检验批清单.xls' .objpack-doc_type = 'XLS'.objpack-doc_size = v_lines_bin_all * 255.append objpack.**** 取发件人和收件人以及抄送人，select *into corresponding fields of table lt_mailfrom zcfg_mailwhere progname = 'ZMMR074'.if sy-subrc = 0 .read table lt_mail index 1 .split lt_mail-recname at '/' into table lt_address .split lt_mail-ccname at '/' into table lt_ccaddress .endif.loop at lt_address.clear reclist .reclist-receiver = lt_address-smtp_addr.* RECLIST-EXPRESS = 'X'. "发送快件reclist-rec_type = 'U'.translate reclist-receiver to upper case.append reclist.endloop.loop at lt_ccaddress.clear reclist .reclist-receiver = lt_ccaddress-smtp_addr.reclist-copy = 'X'. "抄送reclist-rec_type = 'U'.translate reclist-receiver to upper case.append reclist.endloop.call function 'SO_DOCUMENT_SEND_API1'exportingdocument_data = doc_chngput_in_outbox = 'X'sender_address = sender_address "'qyyu@teamwisep 'sender_address_type = 'SMTP'"Bcommit_work = 'X'tablespacking_list = objpackobject_header = objhead* contents_bin = objbincontents_txt = objtxtcontents_hex = objhex* OBJECT_PARA =* OBJECT_PARB =receivers = reclistexceptionstoo_many_receivers = 1document_not_sent = 2document_type_not_exist = 3operation_no_authorization = 4parameter_error = 5x_error = 6enqueue_error = 7others = 8.if sy-subrc <> 0.message id sy-msgid type sy-msgty number sy-msgnowith sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.else.wait up to 2 seconds.submit rsconn01 with mode = 'INT' and return.message '发送成功' type 'S'.endif.endform. " FRM_SEND_MAIL*&---------------------------------------------------------------------**& Form itabtostr*&---------------------------------------------------------------------** text*----------------------------------------------------------------------** -->INTAB text* -->OUTSTR text*----------------------------------------------------------------------*form itabtostr tables intabusing outstr type string.data: tab type c value cl_abap_char_utilities=>horizontal_tab,enter(2) type c value cl_abap_char_utilities=>cr_lf,n type i.data: begin of headtab occurs 0 ,length type i ,decimals type i,type_kind type c,name(30) type c,end of headtab.data descr_ref type ref to cl_abap_structdescr.field-symbols: <comp_wa> type abap_compdescr ,<f_field> ,<f_intab> type any .data:str type string,str2 type string ,text1 type c.descr_ref ?= cl_abap_typedescr=>describe_by_data( intab ).loop at descr_ref->components assigning <comp_wa>.move-corresponding <comp_wa> to headtab.append headtab.endloop.describe table headtab lines n.loop at intab assigning <f_intab>.do n times.assign component sy-index of structure <f_intab> to <f_field>. str = <f_field>.read table headtab index sy-index.if headtab-type_kind = 'I' or headtab-type_kind = 'P'or headtab-type_kind = 'F'.search str for '-'.if sy-subrc = 0 and sy-fdpos <> 0.split str at '-' into str text1.condense str.concatenate '-' str into str.else.condense str.endif.else.* SHIFT str LEFT DELETING LEADING '0' .endif.concatenate str2 tab str into str2.enddo.shift str2.concatenate outstr str2 enter into outstr.clear str2.endloop.endform. " ITABTOSTR*&---------------------------------------------------------------------**& Form STRTORECORD*&---------------------------------------------------------------------** text*----------------------------------------------------------------------** -->P_I_RECORD text* -->P_TMPSTR text* -->P_FILELEN text*----------------------------------------------------------------------*form strtorecord tables record using str len.data:tmpbuffer type xstring.call function 'SCMS_STRING_TO_XSTRING'exportingtext = strmimetype = '"text/html; charset=gb2312"'* encoding = '8400'importingbuffer = tmpbufferexceptionsfailed = 1others = 2.call function 'SCMS_XSTRING_TO_BINARY'exportingbuffer = tmpbufferappend_to_table = ''importingoutput_length = lentablesbinary_tab = record.endform. "strtorecord。

types: begin of xml_line,data(255) type x,end of xml_line.data:l_xml_table type table of xml_line.data:ls_itab type typ_itab.types: begin of xml_line,data(256) type x,end of xml_line.data: l_ixml type ref to if_ixml,l_streamfactory type ref to if_ixml_stream_factory, l_ostream type ref to if_ixml_ostream,l_renderer type ref to if_ixml_renderer,l_document type ref to if_ixml_document.data:l_element_root type ref to if_ixml_element,ns_attribute type ref to if_ixml_attribute,r_element_properties type ref to if_ixml_element,r_element type ref to if_ixml_element,r_worksheet type ref to if_ixml_element,r_table type ref to if_ixml_element,r_column type ref to if_ixml_element,r_row type ref to if_ixml_element,r_styles type ref to if_ixml_element,r_style type ref to if_ixml_element,r_borders type ref to if_ixml_element,r_border type ref to if_ixml_element,r_format type ref to if_ixml_element,r_cell type ref to if_ixml_element,r_data type ref to if_ixml_element,* l_xml_table TYPE TABLE OF xml_line,l_xml_size type i,l_rc type i,l_text(255) type c,l_value type string,l_type type string,p_type .field-symbols:<data_line> type any,<field> type any,<fwa> type any,<fs_flag> type any .data:p_tab like dd02l-tabname .data:data_tab type ref to data,gotstate type ddgotstate,dd03p_tab type table of dd03p,dd03p type dd03p.field-symbols:<data_tab> type standard table.field-symbols:<fs_field> like line of dd03p_tab.clear l_xml_table.define add_fieldcol.unassign: <fs_field>.clear dd03p.dd03p-fieldname = &1.dd03p-ddtext = &2.dd03p-outputlen = &3.dd03p-inttype = &4.dd03p-decimals = &5.dd03p-reptext = &6.dd03p-position = &7.append dd03p to dd03p_tab assigning <fs_field>.end-of-definition.add_fieldcol 'PRUEFLOS' '检验批' '8' 'C' '0' '' ''.add_fieldcol 'WERK' '工厂' '4' 'C' '0' '' ''.add_fieldcol 'CHARG' '批次' '6' 'C' '0' '' ''.add_fieldcol 'MATNR' '物料编码' '10' 'C' '0' '' ''.add_fieldcol 'MAKTX' '物料描述' '35' 'C' '0' '' ''.add_fieldcol 'LOSMENGE' '交检数量' '8' 'P' '3' '' ''.add_fieldcol 'GESSTICHPR' '抽检数量' '8' 'P' '3' '' ''.add_fieldcol 'ERSTELDAT' '收货日期' '8' 'C' '0' '' ''.add_fieldcol 'BUDAT' '过账日期' '8' 'C' '0' '' ''.add_fieldcol 'LAGORTCHRG' '库位' '4' 'C' '0' '' ''.add_fieldcol 'OBJNR' '工作指示编号' '8' 'C' '0' '' ''.add_fieldcol 'ERSTELLER' '创建者' '8' 'C' '0' '' ''.add_fieldcol 'TXT04' '系统状态' '35' 'C' '0' '' ''.* SORT dd03p_tab BY position .create data data_tab type standard table of typ_itab."STANDARD TABLE OF (P_TAB).* CREATE DATA DATA_TAB TYPE STANDARD TABLE OF (P_TAB).assign data_tab->* to <data_tab>.loop at gt_itab into ls_itab .append ls_itab to <data_tab>.endloop.* Creating a ixml factoryl_ixml = cl_ixml=>create( ).* Creating the dom object modell_document = l_ixml->create_document( ).* Create root node 'Workbook'l_element_root = l_document->create_simple_element(name = 'Workbook'* uri = 'urn:schemas-microsoft-com:office:spreadsheet' parent = l_document ).l_element_root->set_attribute(name = 'xmlns'value = 'urn:schemas-microsoft-com:office:spreadsheet' ).ns_attribute = l_document->create_namespace_decl(name = 'ss'prefix = 'xmlns'uri = 'urn:schemas-microsoft-com:office:spreadsheet' ). l_element_root->set_attribute_node( ns_attribute ). ns_attribute = l_document->create_namespace_decl(name = 'x'prefix = 'xmlns'uri = 'urn:schemas-microsoft-com:office:excel' ).l_element_root->set_attribute_node( ns_attribute ).* Create node for document properties.r_element_properties = l_document->create_simple_element(name = 'DocumentProperties'parent = l_element_root ).l_value = sy-uname.l_document->create_simple_element(name = 'Author'value = l_valueparent = r_element_properties ).* Stylesr_styles = l_document->create_simple_element(name = 'Styles'parent = l_element_root ).***** 抬头* Style for Header - Ice Blue, BOLDr_style = l_document->create_simple_element(name = 'Style'parent = r_styles ).r_style->set_attribute_ns(name = 'ID'prefix = 'ss'value = 'Header' ).r_format = l_document->create_simple_element(name = 'Font'parent = r_style ).r_format->set_attribute_ns(name = 'Bold'prefix = 'ss'value = '1' ).r_format = l_document->create_simple_element(name = 'Interior'parent = r_style ).r_format->set_attribute_ns(name = 'Color'prefix = 'ss'value = '#F2F2F2' ).r_format->set_attribute_ns(name = 'Pattern'prefix = 'ss'value = 'Solid' ).r_format = l_document->create_simple_element(name = 'Alignment'parent = r_style ).r_format->set_attribute_ns(name = 'Horizontal'prefix = 'ss'value = 'Center' ).r_format->set_attribute_ns(name = 'Vertical'prefix = 'ss'value = 'Center' ).r_format->set_attribute_ns(name = 'WrapText'prefix = 'ss'value = '1' ).**** 设置颜色格式***** 设置合计行格式r_style = l_document->create_simple_element(name = 'Style'parent = r_styles ).r_style->set_attribute_ns(name = 'ID'prefix = 'ss'value = 'RLine' ).r_format = l_document->create_simple_element( name = 'Interior'parent = r_style ).r_format->set_attribute_ns(name = 'Color'prefix = 'ss'value = '#FF0000' ).r_format->set_attribute_ns(name = 'Pattern'prefix = 'ss'value = 'Solid' ).* r_borders = l_document->create_simple_element( * name = 'Borders'* parent = r_style ).* r_format = l_document->create_simple_element( * name = 'Border'* parent = r_borders ).* r_format->set_attribute_ns(* name = 'Position'* prefix = 'ss'* value = 'Bottom' ).* r_format->set_attribute_ns(* name = 'LineStyle'* prefix = 'ss'* value = 'Dash' ).** r_format = l_document->create_simple_element( * name = 'Border'* parent = r_borders ).* r_format->set_attribute_ns(* name = 'Position'* prefix = 'ss'* value = 'Right' ).* r_format->set_attribute_ns(* name = 'LineStyle'* prefix = 'ss'* value = 'Dash' ).** r_format = l_document->create_simple_element( * name = 'Border'* parent = r_borders ).* r_format->set_attribute_ns(* name = 'Position'* prefix = 'ss'* value = 'Top' ).* r_format->set_attribute_ns(* name = 'LineStyle'* prefix = 'ss'* value = 'Dash' ).* r_format = l_document->create_simple_element( * name = 'Font'* parent = r_style ).* r_format->set_attribute_ns(* name = 'Bold'* prefix = 'ss'* value = '1' ).****** 设置颜色行是黄色r_style = l_document->create_simple_element(name = 'Style'parent = r_styles ).r_style->set_attribute_ns(name = 'ID'prefix = 'ss'value = 'YLine' ).r_format = l_document->create_simple_element( name = 'Interior'parent = r_style ).r_format->set_attribute_ns(name = 'Color'prefix = 'ss'value = '#FFFF00' ).r_format->set_attribute_ns(name = 'Pattern'prefix = 'ss'value = 'Solid' ).* Style for Dataloop at dd03p_tab into dd03p where fieldname <> 'MANDT'.case dd03p-inttype.when 'I' or 'N'.* General formatwhen 'P' or 'F'.* Numeric with specific number of decimalsr_style = l_document->create_simple_element(name = 'Style'parent = r_styles ).l_value = dd03p-fieldname.r_style->set_attribute_ns(name = 'ID'prefix = 'ss'value = l_value ).r_format = l_document->create_simple_element(name = 'NumberFormat'parent = r_style ).if dd03p-decimals > 0.l_value = '0.'.do dd03p-decimals times.concatenate l_value '0' into l_value.enddo.else.l_value = ''.endif.* l_value = '#,##0' .r_format->set_attribute_ns(name = 'Format'prefix = 'ss'value = l_value ).* WHEN 'D'.** General formatwhen others.* Fixed textr_style = l_document->create_simple_element(name = 'Style'parent = r_styles ).l_value = dd03p-fieldname.r_style->set_attribute_ns(name = 'ID'prefix = 'ss'value = l_value ).r_format = l_document->create_simple_element(name = 'NumberFormat'parent = r_style ).l_value = '@'.r_format->set_attribute_ns(name = 'Format'prefix = 'ss'value = l_value ).endcase.endloop .* Worksheetr_worksheet = l_document->create_simple_element(name = 'Worksheet 'parent = l_element_root ). r_worksheet->set_attribute_ns(name = 'Name'prefix = 'ss'value = 'Sheet1' ).* TABLE* <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="1" x:FullCol umns="1" x:FullRows="1">r_table = l_document->create_simple_element(name = 'Table'parent = r_worksheet ).r_table->set_attribute_ns(name = 'FullColumns'prefix = 'x'value = '1' ).r_table->set_attribute_ns(name = 'FullRows'prefix = 'x'value = '1' ).* Column formatting ==>给对应列赋STYLE和WIDEloop at dd03p_tab into dd03p where fieldname <> 'MANDT'.* COLUMNNr_column = l_document->create_simple_element(name = 'Column'parent = r_table ).case dd03p-inttype.when 'I' or 'N'.* General formatwhen 'P' or 'F'.l_value = dd03p-fieldname.r_column->set_attribute_ns(name = 'StyleID'prefix = 'ss'value = l_value ).* WHEN 'D'.** General formatwhen others.l_value = dd03p-fieldname.r_column->set_attribute_ns(name = 'StyleID'prefix = 'ss'value = l_value ).* ENDIF.endcase.l_value = ( dd03p-outputlen + 5 ) * 5.condense l_value no-gaps.r_column->set_attribute_ns(name = 'Width'prefix = 'ss'value = l_value ).endloop."抬头* Column Headers Rowr_row = l_document->create_simple_element(name = 'Row'parent = r_table ).r_row->set_attribute_ns(name = 'StyleID'prefix = 'ss'value = 'Header' ).r_row->set_attribute_ns(name = 'AutoFitHeight'prefix = 'ss'value = '1' ).** Column nameloop at dd03p_tab into dd03p where fieldname <> 'MANDT'. * <Data>r_cell = l_document->create_simple_element(name = 'Cell'parent = r_row ).* ENDIF.l_value = dd03p-ddtext. "fieldname, scrtext_m etc.r_data = l_document->create_simple_element(name = 'Data'value = l_valueparent = r_cell ).r_data->set_attribute_ns(name = 'Type'prefix = 'ss'value = 'String' ).endloop.loop at <data_tab> assigning <data_line> .assign component 'LIGHT' of structure <data_line> to <fs_flag>.* ROWr_row = l_document->create_simple_element(name = 'Row'parent = r_table ).loop at dd03p_tab into dd03p where fieldname <> 'MANDT'.assign component dd03p-fieldname of structure <data_line> to <f ield>.check sy-subrc is initial.* <Cell>r_cell = l_document->create_simple_element(name = 'Cell'parent = r_row ).case dd03p-inttype.when 'I' or 'P' or 'F' or 'N'.l_type = 'Number'.* L_VALUE = <FIELD>.* CONDENSE L_VALUE NO-GAPS."负数时，将负号提前if <field> < 0.l_text = abs( <field> ).condense l_text.concatenate '-' l_text into l_value.* L_VALUE = L_TEXT.condense l_value no-gaps.else.l_value = <field>.if l_value is initial or l_value = 0.l_value = '-' .endif.condense l_value no-gaps.endif.when 'D' or 'T'.l_type = 'String'.if <field> = '00000000'.l_value = ''.else.write <field> to l_text.l_value = l_text.endif.when others.write <field> to l_text.shift l_text left deleting leading space.l_value = l_text.l_type = 'String'.endcase.** <DATA>if <fs_flag> = icon_led_red. " 异常通知单红色显示r_cell->set_attribute_ns(name = 'StyleID'prefix = 'ss'value = 'RLine' ).else.r_cell->set_attribute_ns(name = 'StyleID'prefix = 'ss'value = 'YLine' ).endif.r_data = l_document->create_simple_element(name = 'Data'value = l_valueparent = r_cell ). * Cell formatr_data->set_attribute_ns(name = 'Type'prefix = 'ss'value = l_type ).endloop.endloop.* CREATING A STREAM FACTORYl_streamfactory = l_ixml->create_stream_factory( ).* Connect internal XML table to stream factoryl_ostream = l_streamfactory->create_ostream_itable( table = l_xml_t able ).* RENDERING THE DOCUMENTl_renderer = l_ixml->create_renderer( ostream = l_ostreamdocument = l_document ).l_rc = l_renderer->render( ).* SAVING THE XML DOCUMENTl_xml_size = l_ostream->get_num_written_raw( ).***下载本地文件call method cl_gui_frontend_services=>gui_downloadexportingbin_filesize = l_xml_sizefilename = 'D:\xml\ZQMR022.xls'filetype = 'BIN'changingdata_tab = l_xml_tableexceptionsothers = 24.if sy-subrc <> 0.message id sy-msgid type sy-msgty number sy-msgnowith sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.endif.*****发送邮件data: objpack like sopcklsti1 occurs 2 with header line.data: objhead like solisti1 occurs 1 with header line.* DATA: OBJBIN LIKE SOLIX OCCURS 10 WITH HEADER LINE.data: objtxt like solisti1 occurs 10 with header line.data: reclist like somlreci1 occurs 5 with header line.data: objhex like solix occurs 10 with header line.data: doc_chng like sodocchgi1.data: tab_lines like sy-tabix.data: l_num(3).data: subj_date(10) type c.data:lv_char type string .data:mailtitle(100) type c.data:g_vendername like lfa1-name1.data g_lifnr like ekko-lifnr.data: g_adrnr like lfa1-adrnr.data:sender_address like soextreci1-receiver.data:wa_xml type xml_line.data:lt_mail like standard table of zcfg_mail with header line .data :begin of lt_address occurs 0 ,smtp_addr type ad_smtpadr ,end of lt_address .data :begin of lt_ccaddress occurs 0 ,smtp_addr type ad_smtpadr ,end of lt_ccaddress .*** Mail Object* CONCATENATE lv_str 'Weekly Sales Report' INTO doc_chng-obj_descr SEPARATED BY space.doc_chng-obj_descr = '检验批监控' .*** 邮件正文clear objtxt.objtxt = '你好：'.append objtxt.clear objtxt.append objtxt.clear objtxt.objtxt = '附件是存在异常检验批监控数据，请查收并及时处理，谢谢 '.append objtxt.clear objtxt.lv_char = '其中异常总数量： ' && gv_line && ';' && '未检验数量：' && gv_wj&& ';' && '异常数量：' && gv_yc .objtxt = lv_char .append objtxt.describe table objtxt lines tab_lines.read table objtxt index tab_lines.doc_chng-doc_size = ( tab_lines - 1 ) * 255 + strlen( objtxt ).* Packing List For the E-mail Bodyobjpack-head_start = 1.objpack-head_num = 0.objpack-body_start = 1.objpack-body_num = tab_lines.objpack-doc_type = 'RAW'.append objpack.**内表作为邮件附件** Creation of the Document Attachmentloop at l_xml_table into wa_xml.clear objhex.objhex-line = wa_xml-data.append objhex.endloop.describe table objhex lines tab_lines."objbinobjhead = '检验批监控'.append objhead.* Packing List For the E-mail Attachmentobjpack-transf_bin = 'X'.objpack-head_start = 1.objpack-head_num = 0.objpack-body_start = 1.objpack-body_num = tab_lines.objpack-obj_descr = '检验批监控异常数据'.objpack-obj_name = '检验批监控.xls' .objpack-doc_type = 'XLS'.objpack-doc_size = tab_lines * 255.append objpack.**** 取发件人和收件人以及抄送人，sender_address = '*************************.cn'.select *into corresponding fields of table lt_mailfrom zcfg_mailwhere progname = 'ZQMR022'.if sy-subrc = 0 .read table lt_mail index 1 .split lt_mail-recname at '/' into table lt_address .split lt_mail-ccname at '/' into table lt_ccaddress .endif.loop at lt_address.clear reclist .reclist-receiver = lt_address-smtp_addr.* RECLIST-EXPRESS = 'X'. "发送快件reclist-rec_type = 'U'.translate reclist-receiver to upper case.append reclist.endloop.loop at lt_ccaddress.clear reclist .reclist-receiver = lt_ccaddress-smtp_addr.reclist-copy = 'X'. "抄送reclist-rec_type = 'U'.translate reclist-receiver to upper case.append reclist.endloop.call function 'SO_DOCUMENT_SEND_API1'exportingdocument_data = doc_chngput_in_outbox = 'X'sender_address = sender_address "'qyyu@teamwisep 'sender_address_type = 'SMTP'"Bcommit_work = 'X'tablespacking_list = objpackobject_header = objhead* contents_bin = objbincontents_txt = objtxtcontents_hex = objhex* OBJECT_PARA =* OBJECT_PARB =receivers = reclistexceptionstoo_many_receivers = 1document_not_sent = 2document_type_not_exist = 3operation_no_authorization = 4parameter_error = 5x_error = 6enqueue_error = 7others = 8.if sy-subrc <> 0.message id sy-msgid type sy-msgty number sy-msgnowith sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. else.wait up to 2 seconds.submit rsconn01 with mode = 'INT' and return.message '发送成功' type 'S'.endif.。

sap中Excel的模版上传和下载一：所用到的事物码：smw0二：上传步骤图1图2图3注：“包”为你所在项目的包的名称。

图4图5点击“7” 选择Excel模版，就可以将模版上传到sap的服务器上了，另需要记住对象名称，在后面的程序中有需要。

三：下载代码TABLES:sscrfields.*定义OLE变量DATA:g_excel TYPE ole2_object,g_applica TYPE ole2_object,g_sheet TYPE ole2_object,g_cell TYPE ole2_object,g_workbook TYPE ole2_object.DATA:p_filepath TYPE string.CONSTANTS:c_yes TYPE c VALUE 'X',c_path TYPE char128 VALUE 'C:\TMP\价格信息批量导入.xls'.*模版下载按钮SELECTION-SCREEN PUSHBUTTON /1(10) text-002USER-COMMAND attch.AT SELECTION-SCREEN.PERFORM frm_check_input.*&---------------------------------------------------------------------**& Form frm_check_input*&---------------------------------------------------------------------** text*----------------------------------------------------------------------* FORM frm_check_input .DATA l_ret TYPE abap_bool.IF sscrfields-ucomm = 'ATTCH'.* 下载模版PERFORM frm_download_template.ELSE.IF p_filepath IS INITIAL.MESSAGE s004(zmess) DISPLAY LIKE 'E'.STOP.ENDIF.* 将路径全部转换为大写TRANSLATE p_filepath TO UPPER CASE.* 检查文件是否存在CALL METHOD cl_gui_frontend_services=>file_existEXPORTINGfile = p_filepathRECEIVINGresult = l_retEXCEPTIONScntl_error = 1error_no_gui = 2wrong_parameter = 3not_supported_by_gui = 4OTHERS = 5.IF sy-subrc <> 0.MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.ENDIF.IF l_ret NE c_yes.MESSAGE e004(zmess) WITH '文件不存在'.ENDIF.ENDIF.ENDFORM. "frm_check_input*&---------------------------------------------------------------------* *& Form frm_download_template*&---------------------------------------------------------------------* * text*----------------------------------------------------------------------* FORM frm_download_template .DATA: lv_objdata LIKE wwwdatatab,lv_obj_name LIKE wwwdatatab-objid,lv_destination LIKE rlgrap-filename,lv_objid LIKE sy-repid,lv_subrc LIKE sy-subrc.DATA:l_ret TYPE abap_bool,lv_answer.DATA:lv_file TYPE string.MOVE c_path TO lv_file.CALL METHOD cl_gui_frontend_services=>file_existEXPORTINGfile = lv_fileRECEIVINGresult = l_retEXCEPTIONScntl_error = 1error_no_gui = 2wrong_parameter = 3not_supported_by_gui = 4OTHERS = 5.IF sy-subrc <> 0.MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.EXIT.ENDIF.IF l_ret EQ c_yes.* 模版已存在，是否覆盖CALL FUNCTION 'POPUP_TO_CONFIRM'EXPORTINGtext_question = '模版已存在，是否覆盖？'text_button_1 = '是'(001)text_button_2 = '否'(002)IMPORTINGanswer = lv_answerEXCEPTIONStext_not_found = 1OTHERS = 2.IF sy-subrc <> 0.ENDIF.IF lv_answer EQ 'A'. "取消EXIT.ELSEIF lv_answer NE '1'. "否p_filepath = c_path.ENDIF.ELSE.lv_answer = '1'.ENDIF.CREATE OBJECT g_excel 'EXCEL.APPLICATION'.GET PROPERTY OF g_excel 'Workbooks' = g_workbook . CALL METHOD OFg_workbook'Close'.IF lv_answer EQ '1'.MOVE 'ZGN_MMB021' TO lv_obj_name.SELECT relid objidFROM wwwdataINTO CORRESPONDING FIELDS OF lv_objdataUP TO 1 ROWSWHERE srtf2 = 0 AND relid = 'MI'AND objid = lv_obj_name.ENDSELECT.CALL FUNCTION 'DOWNLOAD_WEB_OBJECT'EXPORTINGkey = lv_objdatadestination = c_pathIMPORTINGrc = lv_subrc.IF lv_subrc = 0.p_filepath = c_path.ENDIF.ENDIF.CALL METHOD OFg_workbook'open'EXPORTING#1 = c_path.CALL METHOD OFg_excel'worksheets' = g_sheetEXPORTING#1 = 1.CALL METHOD OFg_sheet'activate'.SET PROPERTY OF g_excel 'visible' = 1.FREE OBJECT g_sheet.FREE OBJECT g_applica.FREE OBJECT g_workbook.FREE OBJECT g_excel.ENDFORM. "frm_download_template。

目录1流程步骤 (1)1.1成本中心主数据维护 (1)1.2创建成本中心组 (3)1.3设置计划参数文件 (3)1.4手工输入成本中心计划值 (4)1.5通过excel上载成本中心计划值 (6)1.6计划帮助–复制计划数值 (7)1.7复制上一年度的实际数据到计划 (8)1.8设置报表显示货币 (9)1.9通过报表检查计划数据 (10)1 流程步骤1.1 成本中心主数据维护1. 请选择以下导航选项之一访问该事务：选项一：SAP 图形用户界面 (SAP GUI)SAP ECC 菜单会计核算→控制→成本中心会计→主数据→标准层次→显示事务代码OKENN2. 查看成本中心的标准层次3. 查看单个成本中心信息：4. 如果要相应的修改或新增或者直接使用KS01创建，注意胜科集团成本中心的命名规则1.2 创建成本中心组1. 请选择以下导航选项之一访问该事务：选项一：SAP 图形用户界面 (SAP GUI)SAP ECC 菜单会计核算→控制→成本中心会计→主数据→成本中心组→修改事务代码KSH2建议一个公司代码下的成本中心组统一管理创建1.3 设置计划参数文件1. 请选择以下导航选项之一访问该事务：选项一：SAP 图形用户界面 (SAP GUI)SAP ECC 菜单会计核算→控制→成本中心会计→计划→设置计划着参数文件事务代码KP042. 设置计划参数文件“ZSCIG-CN”，点击“用户主记录”保存1.4 手工输入成本中心计划值操作步骤1. 请选择以下导航选项之一访问该事务：选项一：SAP 图形用户界面 (SAP GUI)SAP ECC 菜单会计核算→控制→成本中心会计→计划→成本和作业输入→更改事务代码KP062. 在更改成本要素/作业输入计划：初始屏幕屏幕上，进行以下输入：字段名称描述用户操作/值注释格式ZC1/ZC2版本0会计年度<预算年度> 2010成本中心组/成本中心<成本中心组>成本要素组/成本要素*3. 要显示所有相关成本要素，请选择基于表格的并选择概览屏幕。

1.定义计划编制格式1)IMG->控制->成本中心会计->计划->手动计划->用户定义的计划格式->创建成本要素计划编制的计划编制格式（共有三种格式，此处以成本要素的计划编制格式为例来讲解）2)选择“创建成本要素的计划编制格式”。

3)输入计划格式名称和描述，然后点击“创建”。

4)在“一般数据选择”中，加入相关的要素。

5)双击“引导栏位”，在跳出的窗口中选择“成本要素”，然后点击“第一列”，选择“特征值与名称”。

6)在插入列的时候，选择不同的元素会影响用户的输入。

比如“PWOG-对象货币中的总计划成本”，用户输入金额的时候，系统会认为是以成本对象（比如成本中心）的币别为单位来输入的；而“PWKG-在CO范围货币中的总计划价格”，则系统会认为是以Controlling Area的货币为单位来输入的。

7)在此之后可以增加一个“分配”栏位，在“总计划成本”之后双击插入之后，系统会跳出窗口，选择“属性”，再选择“分配代码”，在最后一个窗口中，选择与“总计划成本”一致的元素，保存即可。

8)至此，计划编制格式创建完毕2.定义计划员参数文件1)IMG->控制->成本中心会计->计划->手动计划->定义已定义用户的计划员参数文件。

2)选择对应的计划员参数文件，逐步进入控制布局。

3)点击“新条目”，输入新创建的计划编制格式，勾选“预览”和“综合的excel”，然后选中该条目，点击“缺省参数”。

在跳转的画面中，输入一些sample的参数，然后点击“Overview”，系统会提示“文件描述XXXXXX已生成”，然后会call excel 产生一个文件，该文件就是此计划编制格式对应的上载模板。

4)至此，计划员参数文件创建完毕，已经可以从KP06的画面中看到对应的格式，并可以用参数文件格式进行上载。

5)哈哈。

SAP软件批量导入成本中心费用计划
在“SAP软件输入成本中心费用计划”一文中，我们介绍了如何手工录入费用计划，但是这种方式效率不高，今天给大家介绍如何批量导入成本中心费用计划。

批量导入时，也使用事务码KP06，
双击，进入KP06初始界面，
然后选择菜单栏“设置->设置计划者参数文件”，
计划参数文件选择“Z00001”，参数文件Z00001是事先定义好的，如果没有定义，则不能使用批量导入功能。

选择后，返回到上一界面，KP06界面变为下图，
然后下载导入模板，
将如上模板导出，并在Excel中整理好数据，如下。

并且保存为TXT格式的文件。

接下来，选择菜单栏的“附加->Excel计划->上载”，
进入Excel上载界面，
这里的关键，就是导入文件了，文件为TXT格式，一般在Excel 中整理好，保存为TXT文件即可。

另外，要注意一下“十进制记数法”，是以什么作为小数位的分隔符。

执行结果如下，说明计划已经成功导入。

接下来就可以通过报表S_ALR_87013611查看导入的计划了，。

SAP实施中Excel函数公式在BOM批量导入中的应用作者：么艳丽来源：《中国管理信息化》2014年第04期[摘要] ERP系统实施过程中，企业数据如何迅速转换成实施顾问需要的形式，是一个目前尚无太多研究和讨论的领域，但对该领域的忽略造成了数据转换工作一直以低水平低效率的状态在各个项目中进行，给数据批量导入乃至系统上线带来很多隐患。

本文以SAP系统实施中，BOM数据的转换为例，采用逐步展示图表的方式，详细阐述了如何利用Excel函数公式，设计出高效、快捷、准确转换的自动化模板，迅速将多层BOM自动转换成顾问需要的单层BOM形式，从而满足了批量导入在格式、时间及准确度方面的要求。

[关键词] SAP；Excel函数；BOM；批量导入doi ： 10 . 3969 / j . issn . 1673 - 0194 . 2014 . 04. 022[中图分类号] TP391 [文献标识码] A [文章编号] 1673 - 0194（2014）04- 0038- 03BOM（Bill of Material）也叫物料清单，是企业生产运营中最重要的资料之一，它说明产品是如何“做出来”的，包涵了企业产品从原材料到最终产成品的各种状态下所有物料的信息，包括物料名称、数量、材质以及物料上下级关系。

BOM是运行ERP（Enterprise Resource Planning）内部集成系统的主导文件，企业各个业务部门都要依据统一的BOM进行工作。

1 BOM的内涵在缺乏ERP系统支持的情况下，企业通常采用办公软件作为BOM的载体，例如Microsoft的Excel或Word。

前者由于其简单易用，普及大众化，并具备基本的统计功能，可以进行初步的物料统计，从而得到了众多企业的青睐。

为企业在“前ERP时代”的生产运营起到了不可或缺的重要作用。

图1是一份简化的BOM范例，真正的BOM包含更多内容。

尤其是制造业，产品结构层次复杂的大型设备制造商将不得不面对动辄上千行的BOM清单。

sap数据传输的EXCEL-OLE*&---------------------------------------------------------------------**& Report ZKRIS_OLE4_SDN_DL_CLIPBOARD*&*&---------------------------------------------------------------------**&*&*&---------------------------------------------------------------------*report zkris_ole4_sdn_dl_clipboard.include ole2incl..data: w_cell1 type ole2_object,w_cell2 type ole2_object.*--- Ole data Declarationsdata: h_excel type ole2_object, " Excel objecth_mapl type ole2_object, " list of workbooksh_map type ole2_object, " workbookh_zl type ole2_object, " cellh_f type ole2_object, " fontgs_interior type ole2_object, " Patternworksheet type ole2_object,h_cell type ole2_object,h_cell1 type ole2_object,range type ole2_object,h_sheet2 type ole2_object,h_sheet3 type ole2_object,gs_font type ole2_object,flg_stop(1) type c.*************************************************************** ******** Internal table Declaration*************************************************************** ******data: begin of t_excel occurs 0,vkorg(20) type c, "Sales Orgvbtyp(20) type c, "Document Categoryauart(20) type c, "Document Typeernam(20) type c, "Created Byvbeln(20) type c, "Document Numberposnr(20) type c, "Item Numbererdat(20) type c, "Created Datevdatu(20) type c, "Header Requested Delivery Datereqdat(20) type c, "Request datecondat(20) type c, "Confirm datelifsk(20) type c, "Header Blocktxt30(30) type c, "Order User Status Description lifsp(20) type c, "Line Blockdispo(20) type c, "MRP Controllerdsnam(20) type c, "MRP Controller Description vmsta(20) type c, "Material Sales Status kunnr(20) type c, "Sold Tocname(35) type c, "Sold To Nameregio(20) type c, "Statecufd(10) type c, "CUDbstnk(20) type c, "PO#bsark(20) type c, "Ordering Methodmatnr(20) type c, "Materialmaktx(35) type c, "Material Descriptiont200(20) type c, "T200 vtext(20) type c, "T200 Descriptionmatkl(20) type c, "Material Groupzzbomind(7) type c, "BOM Indicatorostat(20) type c, "Order Statuscmgst(20) type c, "CRDinco1(20) type c, "Incotermsoqty(20) type c, "Order Quantitypqty(20) type c, "Open Quantityunit(20) type c, "UOMonet(20) type c, "Order Valuepnet(20) type c, "Open Valuecurr(20) type c, "Currency keyso_bezei like tvkbt-bezei,"Sales Officesg_bezei like tvgrt-bezei,"Sales Groupbname(20) type c, "Ordering Partycontact(20) type c, "Contact Nametelf1(20) type c, "Contact telf1reqqty(20) type c, "Item Request qtyreqval(20) type c, "Item Request valueconqty(20) type c, "Item Confirm qtyconval(20) type c, "Item Confirm valuezzrev(02) type c, "Revenue recognition acceptance bezei(20) type c, "Revenue recognition textvgbel(20) type c, "Reference Order for RETURNS0008text(255) type c, "Internal Order Comment Textend of t_excel.data: t_excel_bckord like t_excel occurs 0 with header line, t_excel_bcklog like t_excel occurs 0 with header line,t_excel_blkord like t_excel occurs 0 with header line.types: data1(1500) type c,ty type table of data1.data: it type ty with header line,it_2 type ty with header line,it_3 type ty with header line,rec type sy-tfill,deli(1) type c,l_amt(18) type c.data: begin of hex,tab type x,end of hex.field-symbols: <fs> .constants cns_09(2) type n value 09.assign deli to <fs> type 'X'.hex-tab = cns_09.<fs> = hex-tab.data gv_sheet_name(20) type c .*----------------------------------------------------------------------** M A C R O Declaration*----------------------------------------------------------------------*define ole_check_error.if &1 ne 0.message e001(zz) with &1. exit.endif.end-of-definition.t_excel_bckord-vkorg = 'ABC'.t_excel_bckord-vbtyp = 'DEF'.t_excel_bckord-auart = 'GHI'.t_excel_bckord-ernam = 'JKL'.t_excel_bckord-vbeln = 'MNO'.t_excel_bckord-0008text = 'XYZ'. append t_excel_bckord.t_excel_bckord-vkorg = 'ABC1'.t_excel_bckord-vbtyp = 'DEF1'.t_excel_bckord-auart = 'GHI1'.t_excel_bckord-ernam = 'JKL1'.t_excel_bckord-vbeln = 'MNO1'. t_excel_bckord-0008text = 'XYZ1'. append t_excel_bckord.t_excel_bckord-vkorg = 'ABC2'.t_excel_bckord-vbtyp = 'DEF2'.t_excel_bckord-auart = 'GHI2'.t_excel_bckord-ernam = 'JKL2'.t_excel_bckord-vbeln = 'MNO2'. t_excel_bckord-0008text = 'XYZ2'. append t_excel_bckord.t_excel_bckord-vkorg = 'ABC'.t_excel_bckord-vbtyp = 'DEF'.t_excel_bckord-auart = 'GHI'.t_excel_bckord-ernam = 'JKL'.t_excel_bckord-vbeln = 'MNO'.t_excel_bckord-0008text = 'XYZ'. append t_excel_bckord.t_excel_bckord-vkorg = 'ABC1'.t_excel_bckord-vbtyp = 'DEF1'.t_excel_bckord-auart = 'GHI1'.t_excel_bckord-ernam = 'JKL1'.t_excel_bckord-vbeln = 'MNO1'. t_excel_bckord-0008text = 'XYZ1'. append t_excel_bckord.t_excel_bckord-vkorg = 'ABC2'.t_excel_bckord-vbtyp = 'DEF2'.t_excel_bckord-auart = 'GHI2'.t_excel_bckord-ernam = 'JKL2'.t_excel_bckord-vbeln = 'MNO2'. t_excel_bckord-0008text = 'XYZ2'. append t_excel_bckord.t_excel_bckord-vkorg = 'ABC'.t_excel_bckord-vbtyp = 'DEF'.t_excel_bckord-auart = 'GHI'.t_excel_bckord-ernam = 'JKL'.t_excel_bckord-vbeln = 'MNO'.t_excel_bckord-0008text = 'XYZ'. append t_excel_bckord.t_excel_bckord-vkorg = 'ABC1'.t_excel_bckord-vbtyp = 'DEF1'.t_excel_bckord-auart = 'GHI1'.t_excel_bckord-ernam = 'JKL1'.t_excel_bckord-vbeln = 'MNO1'. t_excel_bckord-0008text = 'XYZ1'. append t_excel_bckord.t_excel_bckord-vkorg = 'ABC2'.t_excel_bckord-vbtyp = 'DEF2'.t_excel_bckord-auart = 'GHI2'.t_excel_bckord-ernam = 'JKL2'.t_excel_bckord-vbeln = 'MNO2'. t_excel_bckord-0008text = 'XYZ2'. append t_excel_bckord.t_excel_bckord-vkorg = 'ABC'.t_excel_bckord-vbtyp = 'DEF'.t_excel_bckord-auart = 'GHI'.t_excel_bckord-ernam = 'JKL'.t_excel_bckord-vbeln = 'MNO'.t_excel_bckord-0008text = 'XYZ'. append t_excel_bckord.t_excel_bckord-vkorg = 'ABC1'.t_excel_bckord-vbtyp = 'DEF1'.t_excel_bckord-auart = 'GHI1'.t_excel_bckord-ernam = 'JKL1'.t_excel_bckord-vbeln = 'MNO1'. t_excel_bckord-0008text = 'XYZ1'.append t_excel_bckord.t_excel_bckord-vkorg = 'ABC2'.t_excel_bckord-vbtyp = 'DEF2'.t_excel_bckord-auart = 'GHI2'.t_excel_bckord-ernam = 'JKL2'.t_excel_bckord-vbeln = 'MNO2'. t_excel_bckord-0008text = 'XYZ2'. append t_excel_bckord.t_excel_bckord-vkorg = 'ABC'.t_excel_bckord-vbtyp = 'DEF'.t_excel_bckord-auart = 'GHI'.t_excel_bckord-ernam = 'JKL'.t_excel_bckord-vbeln = 'MNO'.t_excel_bckord-0008text = 'XYZ'. append t_excel_bckord.t_excel_bckord-vkorg = 'ABC1'.t_excel_bckord-vbtyp = 'DEF1'.t_excel_bckord-auart = 'GHI1'.t_excel_bckord-ernam = 'JKL1'.t_excel_bckord-vbeln = 'MNO1'. t_excel_bckord-0008text = 'XYZ1'. append t_excel_bckord.t_excel_bckord-vkorg = 'ABC2'.t_excel_bckord-vbtyp = 'DEF2'.t_excel_bckord-auart = 'GHI2'.t_excel_bckord-ernam = 'JKL2'.t_excel_bckord-vbeln = 'MNO2'.append t_excel_bckord.t_excel_bckord-vkorg = 'ABC'.t_excel_bckord-vbtyp = 'DEF'.t_excel_bckord-auart = 'GHI'.t_excel_bckord-ernam = 'JKL'.t_excel_bckord-vbeln = 'MNO'.t_excel_bckord-0008text = 'XYZ'. append t_excel_bckord.t_excel_bckord-vkorg = 'ABC1'.t_excel_bckord-vbtyp = 'DEF1'.t_excel_bckord-auart = 'GHI1'.t_excel_bckord-ernam = 'JKL1'.t_excel_bckord-vbeln = 'MNO1'. t_excel_bckord-0008text = 'XYZ1'. append t_excel_bckord.t_excel_bckord-vkorg = 'ABC2'.t_excel_bckord-vbtyp = 'DEF2'.t_excel_bckord-auart = 'GHI2'.t_excel_bckord-ernam = 'JKL2'.t_excel_bckord-vbeln = 'MNO2'. t_excel_bckord-0008text = 'XYZ2'. append t_excel_bckord.t_excel_bckord-vkorg = 'ABC'.t_excel_bckord-vbtyp = 'DEF'.t_excel_bckord-auart = 'GHI'.t_excel_bckord-ernam = 'JKL'.t_excel_bckord-vbeln = 'MNO'.append t_excel_bckord.t_excel_bckord-vkorg = 'ABC1'.t_excel_bckord-vbtyp = 'DEF1'.t_excel_bckord-auart = 'GHI1'.t_excel_bckord-ernam = 'JKL1'.t_excel_bckord-vbeln = 'MNO1'. t_excel_bckord-0008text = 'XYZ1'. append t_excel_bckord.t_excel_bckord-vkorg = 'ABC2'.t_excel_bckord-vbtyp = 'DEF2'.t_excel_bckord-auart = 'GHI2'.t_excel_bckord-ernam = 'JKL2'.t_excel_bckord-vbeln = 'MNO2'. t_excel_bckord-0008text = 'XYZ2'. append t_excel_bckord.t_excel_bcklog-vkorg = 'ABC'.t_excel_bcklog-vbtyp = 'DEF'.t_excel_bcklog-auart = 'GHI'.t_excel_bcklog-ernam = 'JKL'.t_excel_bcklog-vbeln = 'MNO'.t_excel_bcklog-0008text = 'XYZ'. append t_excel_bcklog.t_excel_bcklog-vkorg = 'ABC1'.t_excel_bcklog-vbtyp = 'DEF1'.t_excel_bcklog-auart = 'GHI1'.t_excel_bcklog-ernam = 'JKL1'.t_excel_bcklog-vbeln = 'MNO1'. t_excel_bcklog-0008text = 'XYZ1'. append t_excel_bcklog.t_excel_bcklog-vkorg = 'ABC2'.t_excel_bcklog-vbtyp = 'DEF2'.t_excel_bcklog-auart = 'GHI2'.t_excel_bcklog-ernam = 'JKL2'.t_excel_bcklog-vbeln = 'MNO2'. t_excel_bcklog-0008text = 'XYZ2'. append t_excel_bcklog.t_excel_bcklog-vkorg = 'ABC3'.t_excel_bcklog-vbtyp = 'DEF3'.. t_excel_bcklog-auart = 'GHI3'.t_excel_bcklog-ernam = 'JKL3'.t_excel_bcklog-vbeln = 'MNO3'. t_excel_bcklog-0008text = 'XYZ3'. append t_excel_bcklog.t_excel_blkord-vkorg = 'ABC'.t_excel_blkord-vbtyp = 'DEF'.t_excel_blkord-auart = 'GHI'.t_excel_blkord-ernam = 'JKL'.t_excel_blkord-vbeln = 'MNO'.t_excel_blkord-0008text = 'XYZ'. append t_excel_blkord.t_excel_blkord-vkorg = 'ABC1'.t_excel_blkord-vbtyp = 'DEF1'.t_excel_blkord-auart = 'GHI1'.t_excel_blkord-ernam = 'JKL1'.t_excel_blkord-vbeln = 'MNO1'. t_excel_blkord-0008text = 'XYZ1'. append t_excel_blkord.t_excel_blkord-vkorg = 'ABC2'.t_excel_blkord-vbtyp = 'DEF2'.t_excel_blkord-auart = 'GHI2'.t_excel_blkord-ernam = 'JKL2'.t_excel_blkord-vbeln = 'MNO2'. t_excel_blkord-0008text = 'XYZ2'. append t_excel_blkord.t_excel_blkord-vkorg = 'ABC3'.t_excel_blkord-vbtyp = 'DEF3'.. t_excel_blkord-auart = 'GHI3'.t_excel_blkord-ernam = 'JKL3'.t_excel_blkord-vbeln = 'MNO3'. t_excel_blkord-0008text = 'XYZ3'. append t_excel_blkord.t_excel_blkord-vkorg = 'ABC4'.t_excel_blkord-vbtyp = 'DEF4'.. t_excel_blkord-auart = 'GHI4'.t_excel_blkord-ernam = 'JKL4'.t_excel_blkord-vbeln = 'MNO4'.t_excel_blkord-0008text = 'XYZ4'. append t_excel_blkord.loop at t_excel_bckord. concatenatet_excel_bckord-vkorgt_excel_bckord-vbtypt_excel_bckord-auartt_excel_bckord-ernamt_excel_bckord-vbelnt_excel_bckord-posnrt_excel_bckord-erdatt_excel_bckord-vdatut_excel_bckord-reqdatt_excel_bckord-condatt_excel_bckord-lifskt_excel_bckord-txt30t_excel_bckord-lifsp t_excel_bckord-dispo t_excel_bckord-dsnam t_excel_bckord-vmsta t_excel_bckord-kunnr t_excel_bckord-cname t_excel_bckord-regio t_excel_bckord-cufd t_excel_bckord-bstnk t_excel_bckord-bsark t_excel_bckord-matnr t_excel_bckord-maktx t_excel_bckord-t200 t_excel_bckord-vtext t_excel_bckord-matklt_excel_bckord-zzbomind t_excel_bckord-ostatt_excel_bckord-cmgstt_excel_bckord-inco1t_excel_bckord-oqtyt_excel_bckord-pqtyt_excel_bckord-unitt_excel_bckord-onett_excel_bckord-pnett_excel_bckord-currt_excel_bckord-so_bezei t_excel_bckord-sg_bezei t_excel_bckord-bnamet_excel_bckord-contact t_excel_bckord-telf1t_excel_bckord-reqvalt_excel_bckord-conqty t_excel_bckord-conval t_excel_bckord-zzrevt_excel_bckord-bezeit_excel_bckord-vgbelt_excel_bckord-0008textinto itseparated by deli.append it.clear it.endloop.loop at t_excel_bcklog. concatenatet_excel_bcklog-vkorgt_excel_bcklog-vbtypt_excel_bcklog-ernam t_excel_bcklog-vbeln t_excel_bcklog-posnr t_excel_bcklog-erdat t_excel_bcklog-vdatu t_excel_bcklog-reqdat t_excel_bcklog-condat t_excel_bcklog-lifskt_excel_bcklog-txt30 t_excel_bcklog-lifspt_excel_bcklog-dispo t_excel_bcklog-dsnam t_excel_bcklog-vmsta t_excel_bcklog-kunnrt_excel_bcklog-regiot_excel_bcklog-cufdt_excel_bcklog-bstnkt_excel_bcklog-bsarkt_excel_bcklog-matnrt_excel_bcklog-maktxt_excel_bcklog-t200t_excel_bcklog-vtextt_excel_bcklog-matklt_excel_bcklog-zzbomind t_excel_bcklog-ostatt_excel_bcklog-cmgstt_excel_bcklog-inco1t_excel_bcklog-oqtyt_excel_bcklog-unitt_excel_bcklog-onett_excel_bcklog-pnett_excel_bcklog-currt_excel_bcklog-so_bezei t_excel_bcklog-sg_bezei t_excel_bcklog-bname t_excel_bcklog-contact t_excel_bcklog-telf1t_excel_bcklog-reqqty t_excel_bcklog-reqval t_excel_bcklog-conqty t_excel_bcklog-conval t_excel_bcklog-zzrevt_excel_bcklog-vgbelt_excel_bcklog-0008textinto it_2separated by deli.append it_2.clear it_2.endloop.loop at t_excel_blkord. concatenatet_excel_blkord-vkorgt_excel_blkord-vbtypt_excel_blkord-auartt_excel_blkord-ernam t_excel_blkord-vbelnt_excel_blkord-posnrt_excel_blkord-erdatt_excel_blkord-reqdat t_excel_blkord-condat t_excel_blkord-lifskt_excel_blkord-txt30 t_excel_blkord-lifspt_excel_blkord-dispo t_excel_blkord-dsnam t_excel_blkord-vmsta t_excel_blkord-kunnr t_excel_blkord-cname t_excel_blkord-regio t_excel_blkord-cufd t_excel_blkord-bstnk t_excel_blkord-bsarkt_excel_blkord-maktxt_excel_blkord-t200t_excel_blkord-vtextt_excel_blkord-matklt_excel_blkord-zzbomind t_excel_blkord-ostatt_excel_blkord-cmgstt_excel_blkord-inco1t_excel_blkord-oqtyt_excel_blkord-pqtyt_excel_blkord-unitt_excel_blkord-onett_excel_blkord-pnett_excel_blkord-currt_excel_blkord-so_bezeit_excel_blkord-sg_bezei t_excel_blkord-bname t_excel_blkord-contact t_excel_blkord-telf1t_excel_blkord-reqqty t_excel_blkord-reqval t_excel_blkord-conqty t_excel_blkord-conval t_excel_blkord-zzrevt_excel_blkord-bezeit_excel_blkord-vgbelt_excel_blkord-0008textinto it_3separated by deli. append it_3.clear it_3.endloop.if h_excel-header = space or h_excel-handle = -1.* start Excelcreate object h_excel 'EXCEL.APPLICATION'. endif.* PERFORM err_hdl.*--- get list of workbooks, initially emptycall method of h_excel 'Workbooks' = h_mapl.* PERFORM err_hdl.set property of h_excel 'Visible' = 1.* add a new workbookcall method of h_mapl 'Add' = h_map.* PERFORM err_hdl.*GV_SHEET_NAME = '1st SHEET'.gv_sheet_name = 'Back Orders'.get property of h_excel 'ACTIVESHEET' = worksheet. set property of worksheet 'Name' = gv_sheet_name .Formatting the area of additional data 1 and doing the BOLD call method of h_excel 'Cells' = w_cell1exporting#1 = 1#2 = 1.call method of h_excel 'Cells' = w_cell2exporting#1 = 1#2 = 50.call method of h_excel 'Range' = h_cellexporting#1 = w_cell1#2 = w_cell2.*CALL METHOD OF gs_cells 'Select' .get property of h_cell 'Font' = gs_font .set property of gs_font 'Bold' = 1 .data l_rc type i.call method cl_gui_frontend_services=>clipboard_export importingdata = it[]changingrc = l_rcexceptionscntl_error = 1error_no_gui = 2not_supported_by_gui = 3others = 4.*CALL METHOD OF h_excel 'Cells' = w_cell1 * EXPORTING* #1 = 1* #2 = 1.**CALL METHOD OF h_excel 'Cells' = w_cell2 * EXPORTING* #1 = 1* #2 = 1.** PERFORM err_hdl.*CALL METHOD OF h_excel 'Range' = range * EXPORTING* #1 = w_cell1* #2 = w_cell2.*CALL METHOD OF range 'Select'.* PERFORM err_hdl.call method of worksheet 'Paste'.* PERFORM err_hdl.* CALL METHOD OF h_excel 'QUIT'.*GV_SHEET_NAME = '2ND SHEET'.gv_sheet_name = 'Backlog'.get property of h_excel 'Sheets' = h_sheet2 .call method of h_sheet2 'Add' = h_map.set property of h_map 'Name' = gv_sheet_name .get property of h_excel 'ACTIVESHEET' = worksheet.*--Formatting the area of additional data 1 and doing the BOLD call method of h_excel 'Cells' = w_cell1exporting#1 = 1#2 = 1.call method of h_excel 'Cells' = w_cell2exporting#1 = 1#2 = 50.call method of h_excel 'Range' = h_cellexporting#1 = w_cell1#2 = w_cell2.get property of h_cell 'Font' = gs_font .set property of gs_font 'Bold' = 1 .call method cl_gui_frontend_services=>clipboard_export importingdata = it_2[]changingrc = l_rcexceptionscntl_error = 1error_no_gui = 2not_supported_by_gui = 3others = 4.call method of h_excel 'Cells' = w_cell1 exporting#1 = 1#2 = 1.call method of h_excel 'Cells' = w_cell2 exporting#1 = 1#2 = 1.* PERFORM err_hdl.call method of h_excel 'Range' = range exporting#1 = w_cell1#2 = w_cell2.call method of range 'Select'.* PERFORM err_hdl.call method of worksheet 'Paste'.*GV_SHEET_NAME = '3rd SHEET'.gv_sheet_name = 'Blocked Orders'.get property of h_excel 'Sheets' = h_sheet3 .call method of h_sheet3 'Add' = h_map.set property of h_map 'Name' = gv_sheet_name .get property of h_excel 'ACTIVESHEET' = worksheet.*--Formatting the area of additional data 1 and doing the BOLD call method of h_excel 'Cells' = w_cell1exporting#1 = 1#2 = 1.call method of h_excel 'Cells' = w_cell2exporting#1 = 1#2 = 50.call method of h_excel 'Range' = h_cellexporting#1 = w_cell1#2 = w_cell2.get property of h_cell 'Font' = gs_font .set property of gs_font 'Bold' = 1 .call method cl_gui_frontend_services=>clipboard_export importingdata = it_3[]changingrc = l_rcexceptionscntl_error = 1error_no_gui = 2not_supported_by_gui = 3others = 4.call method of h_excel 'Cells' = w_cell1 exporting#1 = 1#2 = 1.call method of h_excel 'Cells' = w_cell2 exporting#1 = 1#2 = 1.* PERFORM err_hdl.call method of h_excel 'Range' = range exporting#1 = w_cell1#2 = w_cell2.call method of range 'Select'.* PERFORM err_hdl.call method of worksheet 'Paste'.*--- disconnect from Excelfree object h_zl.free object h_mapl.free object h_map.free object h_excel.。

1.使用KP65事务码创建计划格式
2.选择计划行的特征
3.选择计格格式的特征值
4.维护费用计划的一般数据选择（计划抬头数据）
5.保存定义的计划格式
6.使用S_ALR_87005852事务码定义计划员参数
7.选择一般控制维护计划范围
8.点击控制布局选择上述章节定义的格式，勾选预览和综合的EXCEL（目前方格描述为空）
9.点击缺省参数进入下一屏幕
10.输入计划版本，期间和会计年度，选择基于表格的输入项
11.成功生成文件描述的提示
12.点击保存文件描述
13.复制文件的格式，作为上传文件的格式
14.保存复制的格式存为EXCEL文件
15.点击返回
16.计划人参数文件所对应的上传文件描述
17.准备上传文件的数据，保存成文本文件
18.使用KP06事务码进入EXCEL计划上载界面
19.选择上传文件对应的路径，输入文件描述点执行
20.查询上传结果
21.输入S_ALR_87013611事务码的初始查询条件
22.查询成本中心上传的费用计划。

SAP成本中心计划excel上载对于成本中心成本要素计划，在SAP系统中通过KP06进行录入。

若成本中心个数很多(100个)、且成本要素也很多(50个)，这时就需要录入成本中心计划5000个(=100*50)，这时手工录入工作量就很大，工作量一大就容易出错。

这时就需要用批导入方式来上载计划了。

对于批导入的实现，可以采用BDC方式(程序或手工都行)，但BDC方式也比较麻烦(此方法这里不作叙述)。

SAP系统也提供了EXCEL上载方式，这种方式相对简单容易操作。

相应的处理方法如下：1.定义“Planning Layout”路径： IMG -> Controlling -> Cost Center Accounting -> Planning -> Manual Planning -> User-Defined Planning Layouts -> Create Planning Layouts for Cost Element Planning事务码:KP65定义 1月到12月。

1.3 定义“Gen. data selection”2.定义“User-Defined Planner Profiles”路径： IMG -> Controlling -> Cost Center Accounting -> Planning -> Manual Planning -> Define User-Defined Planner Profiles2.1定义名称2.2定义“General Controlling”2.3定义“Layouts for Controlling”2.4定义“Default parameters”(1.保存 file description 2. 保存 Save Excel layout 3.保存退出)3. 模板下载事务码: KP063.1 选择 planner profile选择刚才定义的 planner profile选择planner profile 后，重新回到KP06的画面：如上图，点击EXCEL中的菜单: 文件，然后选择另存为本地的EXCEL文件。

SAPABAP如何将内表与EXCEL导入、导出将内表的数据导出到EXCELREPORT ZTEST08.DATA: BEGIN OF MY_DATA,MANDT LIKE USR02-MANDT,BNAME LIKE USR02-BNAME,ERDAT LIKE USR02-ERDAT,LTIME LIKE USR02-LTIME,END OF MY_DATA.DATA: IT_DATA LIKE MY_DATA OCCURS 0.SELECT USR02~MANDT USR02~BNAME USR02~ERDAT USR02~LTIMEAPPENDING TABLE IT_DATAFROM USR02UP to 100 rows.*排序SORT IT_DATA BY BNAME.INCLUDE OLE2INCL. "定义OLE变量DATA: EXCEL TYPE OLE2_OBJECT,SHEET TYPE OLE2_OBJECT,CELL TYPE OLE2_OBJECT,WORKBOOK TYPE OLE2_OBJECT,XLSNAME TYPE STRING VALUE 'C:\sap_usr02.xls',LINE TYPE I VALUE 0. "行号CREATE OBJECT EXCEL 'EXCEL.APPLICATION'."启动ExcelIF SY-SUBRC NE 0.WRITE: / '启动Excel失败。

'.STOP.ENDIF.CALL METHOD OF EXCEL 'WORKBOOKS' = WORKBOOK.SET PROPERTY OF EXCEL 'VISIBLE' = 1. "使excel 可视SET PROPERTY OF EXCEL 'SHEETSINNEWWORKBOOK' = 1. "如果是读取excel文件中的内容则是直接打开工作簿第一页CALL METHOD OF WORKBOOK 'ADD'. "例如：CALL METHOD OF EXCEL 'WORKSHEETS' = SHEET EXPORTING #1 = 1.LOOP AT IT_DATA INTO MY_DATA.LINE = LINE + 1. "Excel 中行号从1开始CALL METHOD OF EXCEL 'CELLS' = CELL EXPORTING #1 = LINE #2 = 1. "指定单元格，SET PROPERTY OF CELL 'VALUE' = MY_DATA-MANDT. "写入值CALL METHOD OF EXCEL 'CELLS' = CELL EXPORTING #1 = LINE #2 = 2. "指定单元格，SET PROPERTY OF CELL 'VALUE' = MY_DATA-BNAME. "写入值CALL METHOD OF EXCEL 'CELLS' = CELL EXPORTING #1 =LINE #2 = 3. "指定单元格，SET PROPERTY OF CELL 'VALUE' = MY_DATA-ERDAT. "写入值CALL METHOD OF EXCEL 'CELLS' = CELL EXPORTING #1 = LINE #2 = 4. "指定单元格，SET PROPERTY OF CELL 'VALUE' = MY_DATA-LTIME. "写入值ENDLOOP.GET PROPERTY OF EXCEL 'ACTIVESHEET' = SHEET. "激活工作簿GET PROPERTY OF EXCEL 'ACTIVEWORKBOOK' = WORKBOOK. "激活工作区CALL METHOD OF WORKBOOK 'SAVEAS' EXPORTING #1 = XLSNAME #2 = 1. "将excel文件保存CALL METHOD OF WORKBOOK 'CLOSE'. "关闭工作区CALL METHOD OF EXCEL 'QUIT'. "退出excel*WRITE:/ XLSNAME,'DONE'. "退出成功，输出doneFREE OBJECT SHEET. "释放操作FREE OBJECT WORKBOOK.FREE OBJECT EXCEL.上传的EXCEL给内表赋值这个EXCEL上载直接读取行和列，并把行和列对应的值赋给内表。

sapdoi调用excel事务代码OAOR 导入到系统中备用。

然后写程序用DOI技术读取该模板，向模板中填写数据。

最后输出出来。

因为可以支持公式和VBA扩展功能，所以可以实现自动计算、统计以及EXCEL图标功能。

（1）用OAOR 导入EXCEL文件CLASS NAME: HRFPM_EXCEL_STANDARD(一般sap有的，可以自己定义T-CODE:SBDSV1)CLASS TYPE 为OTObject key 定义成跟你的报表的名字一致，这样不容易弄错点create的STANDARD DOC.TYPE双击STANDARD APPLICATION 为excel 图标进行上传最好把file name, description,language的值都写成程序名一样（2）然后就可以编程用DOI实现报表的输出：为了输出EXCEL，需要创建一个屏幕，并在屏幕上放置一个容器，详细代码如下： *&---------------------------------------------------------------------**& Report ZZKP052*&*&---------------------------------------------------------------------**&*&*&---------------------------------------------------------------------*REPORT ZLJC_TEST NO STANDARD PAGE HEADING .DATA: document_name(30) VALUE 'TESTDOI',active_sheet(50) VALUE 'Sheet1'.CONSTANTS inplace VALUE 'X'.DATA: container TYPE REF TO cl_gui_custom_container,control TYPE REF TO i_oi_container_control,document TYPE REF TO i_oi_document_proxy,spreadsheet TYPE REF TO i_oi_spreadsheet,error TYPE REF TO i_oi_error,errors TYPE REF TO i_oi_error OCCURS 0 WITH HEADER LINE.DATA: rangeitem TYPE soi_range_item.DATA: ranges TYPE soi_range_list.DATA: excel_input TYPE soi_generic_table.DATA: excel_input_wa TYPE soi_generic_item.DATA: initialized(1), retcode TYPE soi_ret_string.DATA: item_url(256), already_done, newname(40).DATA document_type(80).DATA: excel(80) VALUE 'Excel.Sheet'.DATA: line_count TYPE i,column_count TYPE i.DATA: ok_code LIKE sy-ucomm.PARAMETERS flag.START-OF-SELECTION.CALL SCREEN 100.*&——————————————————————-*& Module OUTPUT_TO_EXCEL OUTPUT*&——————————————————————-MODULE status_0100 OUTPUT.SET PF-STATUS '100'.* 创建EXCEL模板PERFORM create_basic_objects USING '' '' '' '' document_name. * 输出EXCELPERFORM output_to_excel.ENDMODULE. "' OUTPUT_TO_EXCEL OUTPUT*———————————————————————** FORM create_basic_objects * *———————————————————————*FORM create_basic_objects USING p_app_namep_classnamep_classtypep_obj_keyp_docname.DATA l_app_name(200).DATA: bds_instance TYPE REF TO cl_bds_document_set.DATA: doc_signature TYPE sbdst_signature,wa_doc_signature LIKE LINE OF doc_signature,doc_components TYPE sbdst_components,doc_uris TYPE sbdst_uri,wa_doc_uris LIKE LINE OF doc_uris.DATA: doc_classname TYPE sbdst_classname VALUE 'PICTURES',doc_classtype TYPE sbdst_classtype VALUE 'OT',doc_object_key TYPE sbdst_object_key VALUE 'EXCEL'.CHECK initialized IS INITIAL.* 处理参数IF p_app_name IS INITIAL.l_app_name = 'R/3 Reporter'.ELSE.l_app_name = p_app_name.ENDIF.IF NOT ( p_classname IS INITIAL OR p_classtype IS INITIALOR p_obj_key IS INITIAL ).doc_classname = p_classname.doc_classtype = p_classtype.doc_object_key = p_obj_key.ENDIF.* 创建容器控制器实例CALL METHOD c_oi_container_control_creator=>get_container_controlIMPORTINGcontrol = controlerror = error.CALL METHOD error->raise_messageEXPORTINGtype = 'E'.* 创建屏幕上的容器对象CREATE OBJECT containerEXPORTINGcontainer_name = 'CONTAINER'.* 初始化容器控制器CALL METHOD control->init_controlEXPORTINGr3_application_name = l_app_nameinplace_enabled = inplaceinplace_scroll_documents = 'X'parent = containerregister_on_close_event = 'X'register_on_custom_event = 'X'no_flush = 'X'IMPORTINGerror = errors.APPEND errors.CLEAR item_url.wa_doc_signature-prop_name = 'DESCRIPTION'.document_type = excel.wa_doc_signature-prop_value = p_docname.APPEND wa_doc_signature TO doc_signature.* BDS对象实例化CREATE OBJECT bds_instance.* 读取BDS内容（模板）CALL METHOD bds_instance->get_infoEXPORTINGclassname = doc_classnameclasstype = doc_classtypeobject_key = doc_object_keyCHANGINGcomponents = doc_componentssignature = doc_signature.* 读取BDS的URLCALL METHOD bds_instance->get_with_urlEXPORTINGclassname = doc_classnameclasstype = doc_classtypeobject_key = doc_object_keyCHANGINGuris = doc_urissignature = doc_signature.FREE bds_instance.READ TABLE doc_uris INTO wa_doc_uris INDEX 1.item_url = wa_doc_uris-uri.* ask the SAP DOI container for a i_oi_document_proxy for Excel * 容器控制器获得一个EXCEL文档代理CALL METHOD control->get_document_proxyEXPORTINGdocument_type = 'Excel.Sheet'no_flush = 'X'IMPORTINGdocument_proxy = documenterror = errors.APPEND errors.* open a document saved in business document service. * 容器控制器中打开指定BDS返回的文档CALL METHOD document->open_documentEXPORTINGopen_inplace = inplacedocument_url = item_url.DATA: has TYPE i.CALL METHOD document->has_spreadsheet_interface EXPORTINGno_flush = ''IMPORTINGis_available = haserror = errors.APPEND errors.CALL METHOD document->get_spreadsheet_interface EXPORTINGno_flush = ' 'IMPORTINGsheet_interface = spreadsheeterror = errors.APPEND errors.* Activate sheetCALL METHOD spreadsheet->select_sheetEXPORTINGname = active_sheetno_flush = ''IMPORTINGerror = errors.APPEND errors.*错误处理LOOP AT errors.CALL METHOD errors->raise_messageEXPORTINGtype = 'S'.ENDLOOP.FREE errors.initialized = 'X'.ENDFORM. "' CREATE_BASIC_OBJECTS*———————————————————————* * FORM output_to_excel * *———————————————————————* FORM output_to_excel.DATA: tmpdate(10) TYPE c.column_count = 2.line_count = 2.PERFORM fill_cell USING line_countcolumn_count'TEST1'.line_count = line_count + 1.PERFORM fill_cell USING line_countcolumn_count'TEST2'.column_count = column_count + 1.PERFORM fill_cell USING line_countcolumn_count'ljc'.* call METHOD spreadsheet->PROTECT* EXPORTING* protect = 'X'.ENDFORM. "output_to_excel*———————————————————————* * FORM fill_cell * *———————————————————————* FORM fill_cell USING i j val.DATA: columns_number TYPE i,rows_number TYPE i.columns_number = 1.rows_number = 1.CALL METHOD spreadsheet->insert_range_dim EXPORTING name = 'cell'no_flush = 'X'top = ileft = jrows = rows_numbercolumns = columns_numberIMPORTINGerror = errors.APPEND errors.REFRESH: ranges, excel_input.rangeitem-name = 'cell'.rangeitem-columns = 1.rangeitem-rows = 1.APPEND rangeitem TO ranges.excel_input_wa-column = 1.excel_input_wa-row = 1.excel_input_wa-value = val.APPEND excel_input_wa TO excel_input.* set dataCALL METHOD spreadsheet->set_ranges_data EXPORTING ranges = rangescontents = excel_inputno_flush = 'X'IMPORTINGerror = errors.APPEND errors.CALL METHOD spreadsheet->fit_widestEXPORTINGname = spaceno_flush = 'X'.REFRESH: ranges, excel_input.ENDFORM. "fill_cell*&———————————————————————* *& Module EXIT INPUT*&———————————————————————* MODULE user_command_0100 INPUT.IF NOT document IS INITIAL.CALL METHOD document->close_document.FREE document.ENDIF.IF NOT control IS INITIAL.CALL METHOD control->destroy_control.FREE control.ENDIF.SET SCREEN 0 .ENDMODULE. " EXIT INPUT。

Excel宏读取SAP数据方法一: 使用WSDL 实现不登陆SAP 输出Excel 报表.实现直接从EXCEL输出报表数据的关键是sap Web ServicesSAP创建Web ServicesWeb service 就是一个应用程序，它向外界暴露出一个能够通过Web 进行调用的API。

也就是说，可以利用编程的方法通过Web 来调用这个应用程序。

Web services 是建立可互操作的分布式应用程序的新平台。

Web service 平台是一套标准，它定义了应用程序如何在Web 上实现互操作性。

你可以用任何你喜欢的语言，在任何你喜欢的平台上写Web service ，只要我们可以通过Web service 标准对这些服务进行查询和访问。

SAP 的Web Service 分为ABAP and Java Web Services。

创建Web Service方法 1.T-Code:SE80 选择Package,然后右击在菜单中选择，Create Enterprise Services/Web Services Web Service.方法 2.T-CODE ：SE37 选择一个function module ，显示，菜单，Utilities More Utilities Create Web Service From the Function Module or From the Function Group,这也是我们较为常用的一种方法.方法 3.T-CODE：BAPI BAPI Explorer , BAPI 也是Function,所以此方法实际上与方法 2 一致.在创建Web service 时，首先要创建Virtual interfacesVirtual interfaces ( 实际使用中此步骤并非必须的)Using virtual interfaces, you can define several views of an implementation and publish these separately as a Web service. When you create virtual interfaces, you can rename or hide operations and parameters. You can also define standard values for parameters and convert parameter types.创建Virtual interfacesT-CODE：SE80 选择Package 右击：Create Enterprise Service/Web Service Virtual InterfaceRelease Web ServiceT-CODE:WSConfig选择web service Service Definition ,Variant ，创建release web service.保存后列表中会浮现已release 的web service.生成WSDL服务管理T-CODE:sicf 管理SAP 服务(包含web service)在这里可以定义此SAP 服务的登陆信息-用户名密码.T-CODE:WSADMIN执行(Ctrl+F8),生成WSDL(Web Services Description Language) (Ctrl+F9)点击生成WSDL文件路径点击按钮后会生成一个网页, 需要记住其网址:服务监控：T-Code:smicm 服务监控在Excel 中导入生成的WSDL 文件, 引入对应的WS 服务.(此功能要求Excel 版本较高,如果是2003 及其以下版本则需要安装补丁)此处填入WSDL 路径,完成导入WS 服务.在excel 中编写一个宏来调用此WS 服务读取数据Sub sap_con()Dim Z_EXCEL_WS_T2_ws As New clsws_ZEXCELWST2V2ServiceDim ZCLASS As StringDim name As StringDim i As IntegerDim sheetname As StringFor i = 0 To 10name = sheet1.Cells(i + 1, 1).ValueIf name = "" ThenElseCall Z_EXCEL_WS_T2_ws.wsm_ZExcelWsT2(name, ZCLASS)sheet1.Cells(i + 1, 2) = "所在的用户组是"sheet1.Cells(i + 1, 3) = ZCLASS' sheet1.Cells(i + 1, 5) = nameEnd IfNext iMsgBox "用户信息读取成功"''''''''''''End Sub方法二:直接使用VBA 代码写宏读取信息,不用做任何设置, 缺点是安全性较差, 因为登陆用户名密码需要在VBA 中指定. 代码如下:Sub sap_table()Dim iqtable As ObjectDim ifields As ObjectDim idata As ObjectDim itabname As StringDim ifldname As String' Macro1 Macro' Macro recorded 9/7/2005 by StoneFSet oFunction = CreateObject("SAP.LogonControl.1")Set oConnection = oFunction.NewConnectionoConnection.Client = "800"nguage = "EN"er = "AC_WANGFZ" oConnection.Password = "XXXXXX" oConnection.ApplicationServer = "10.88.127.131" oConnection.SystemNumber = "03"result = oConnection.Logon(0, True)Set ofun = CreateObject("SAP.FUNCTIONS") Set ofun.Connection = oConnection “用户名密码写入代码中,安全性较差“Set func = ofun.Add("RFC_READ_TABLE") 'call RFC_READ_TABLE? func.Exports("QUERY_TABLE") = "T001T"Set ioptions = func.tables.Item("OPTIONS")Set ifields = func.tables.Item("FIELDS")Set idata = func.tables.Item("DATA")'here only add 3 fieldsifields.Rows.Addifields.Value(1, "FIELDNAME") = "BUKRS"ifields.Rows.Addifields.Value(2, "FIELDNAME") = "TXTNA"ifields.Rows.Addifields.Value(3, "FIELDNAME") = "LTEXT"'icall = func.RFC_READ_TABLE(QUERY_TABLE:="MARA", FIELDS:= ifields, DATA:=idata,OPTIONS = ioptions)icall = func.CallIf icall = True Then 'icall=trueFor i = 1 To idata.Rows.CountILine = idata.Value(i, "WA")imatno = Mid(ILine, 1, 4)imtype = Mid(ILine, 5, 16)imgrp = Mid(ILine, 17, 50)ThisWorkbook.Sheets(1).Cells(i, 1) = imatnoThisWorkbook.Sheets(1).Cells(i, 2) = imtypeThisWorkbook.Sheets(1).Cells(i, 3) = imgrpNext iMsgBox "表T001T 读取成功"ElseMsgBox "OPEN FAILed,Fuckying your damning SAP!"End IfEnd Sub。