SAP 成本中心计划 KP06如何实现用EXCEL上传
SAPCO成本要素会计CEL与成本中心会计CCA配置与实务操作Guide讲义
可编辑修改精选全文完整版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配置一致。
SAP成本中心计划KP06如何实现用EXCEL上传
一直对这部分内容有点困惑,在网上找了好久没有找到相关资料。
自己琢磨了几天弄出来了。
整理下思路,分享下先看看操作中都有什么信息需要输入在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的数据交互与处理
利用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成本中心计划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事务码: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文件。
SAP 内表数据作为无格式附件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。
SAP 将EXCEL作为附件发送Email
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.。
Excel模板的上传和下载
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。
SAP导入EXCEL
* text
*----------------------------------------------------------------------*
FORM FRM_SET_LAYOUT .
V_REPID = SY-REPID. "当前程序
I_LAYOUT-ZEBRA = 'X'. "颜色交替
I_LAYOUT-DETAIL_POPUP = 'X'."弹出子窗体
DATA: V_POS TYPE I,
V_REPID TYPE SY-REPID,
I_LAYOUT TYPE SLIS_LAYOUT_ALV,
I_FIELDCAT TYPE SLIS_FIELDCAT_ALV,
APPEND I_FIELDCAT TO I_FIELDCAT_ALV.
CLEAR I_FIELDCAT.
I_FIELDCAT-FIELDNAME = 'POST1'.
I_FIELDCAT-SELTEXT_S = '对象号'.
APPEND I_FIELDCAT TO I_FIELDCAT_ALV.
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM FRM_BAND .
CLEAR I_FIELDCAT.
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
一直对这部分内容有点困惑,在网上找了好久没有找到相关资料.自己琢磨了几天弄出来了.整理下思路,分享下
先看看操作中都有什么信息需要输入
在KP06的主界面,
输入版本,会计年度
选择附加-EXCEL计划-上载
路径或文件: 选择上传文件,需要格式为TXT或者CSV
文件描述: 这里和上传的格式是对应的,相同的格式在配置的时候会对应到一个文件描述十进制记数法:这里一般选择下面的选项,我们中国的习惯是, 做百分位. 做小数点CSV文件中的分隔符:这里一般是,
执行的时候就点击小闹钟
通过操作界面,我们看到需要我们配置实现的是2个东西文件和文件描述
创建文件,就是创建计划编制格式路径如下,一般是选择第一个创建成本要素计划编制的计划编制格式
可以通过复制创建,当然你要想在KP06看看你要参考哪个格式
我创建的就是按照季显示和上传成本要素的格式
定义好格式后,还要创建计划者参数文件,把格式分配给计划者参数文件
路径如下图
定义已定义用户的计划员参数文件
SAP的惯例,还是用复制的方式,我创建了ZTEST1.目前我们有了计划者参数文件,和文件(格式). 还差文件描述.这个东西我找了好久.最后在一个SAP的help文档中找到了答案.
Defining an Excel Template
1. CO-PA: Execute the planning method Enter planning data, using the related
parameter set. Doing so 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. You
must do this so that you can open the spreadsheet in the next step. If desired, you can delete these entries again after you have customized your Excel template.
这里讲是在默认参数那里继续执行,会自动生成.
下图就到了设置默认参数,然后F5概观屏幕.系统会提示自动创建文件描述.跳到EXCEL界面. 现在所有的配置就都有了.到KP06按照步骤操作即可.。