C#窗体内嵌EXCEL应用程序，构造二重JSON通过RFC获取SAP系统数据
程序员文章站
2022-05-26 11:13:20
...
0.1.1. 目的
为什么要做？
在做接口的时候，多个系统的数据交互，要求对各种字段和表关联比较熟悉，比如这个字段来自哪张表，它使用的数据元素是什么，它是否有固定值，是否有搜索帮助，对应的表有哪些主键。如果对表和字段不熟悉，就要频繁的使用SE11去查询和验证每一个字段是否在对应的表中存在，对于初学者而言，如果不了解SAP系统，则会花费很多时间在找表、验证表等毫无意义和重复性工作之中。
做了什么？
为了解决这个问题，我用C#写了一个内嵌EXCEL的应用程序，通过微软提供的OFFICE类库创建了内嵌的EXCEL，可以直接像操作EXCEL一样操作内嵌的EXCEL，操作EXCEL的每一个事件几乎都能在程序中捕捉到，该程序具有很大的可扩展性。
怎么做的？
将需要查询的表名、字段名、数据元素名、域名填在程序自动生成的模板EXCEL中，点击“获取”，程序会获取EXCEL输入的数据并转换为JSON，将构建好的动态程序JSON结构和参数JSON组成二重JSON传到SAP系统；SAP对二重JSON进行解析，生成对应的类，方法和表结构，并动态调用类方法，将执行的结果重装为二重JSON传回C#；C#对回传的二重JSON进行解析，获取输出参数，并写入到内嵌EXCEL，然后可以对EXCEL数据进行二次操作并保存备份。
具体实现
0.1.1. ABAP创建一个类，并正常调用
REPORT z14143_14.
CLASS da DEFINITION.
PUBLIC SECTION.
CLASS-METHODS: getrelationship IMPORTING i_json TYPE string
EXPORTING o_json TYPE string.
ENDCLASS.
CLASS da IMPLEMENTATION.
METHOD getrelationship.
*定义类型ty_field创建时间06.05.2018 01:28:21
TYPES: BEGIN OF ty_field,
tablename TYPE dd03l-tabname, "表名
istable TYPE dd03l-tabletype, "是表？
fieldname TYPE dd03l-fieldname, "字段名
iskey TYPE dd03l-keyflag, "主键？
elementname TYPE dd03l-rollname, "数据元素
checktable TYPE dd03l-checktable, "检查表
datatype TYPE dd03l-datatype, "数据类型
length TYPE dd03l-leng, "长度
decimals TYPE dd03l-decimals, "精度
domainname TYPE dd03l-domname, "域名
shlporigin TYPE dd07t-ddtext,
comptype TYPE dd07t-ddtext,
END OF ty_field.
DATA:lt_field TYPE TABLE OF ty_field,
ls_field TYPE ty_field.
DATA:rt_tab TYPE RANGE OF dd03l-tabname,
rt_fid TYPE RANGE OF dd03l-fieldname,
rt_ele TYPE RANGE OF dd03l-rollname,
rt_dom TYPE RANGE OF dd03l-domname.
DATA:rs_tab LIKE LINE OF rt_tab,
rs_fid LIKE LINE OF rt_fid,
rs_ele LIKE LINE OF rt_ele,
rs_dom LIKE LINE OF rt_dom.
/ui2/cl_json=>deserialize( EXPORTING json = i_json pretty_name = /ui2/cl_json=>pretty_mode-none CHANGING data = lt_field ).
LOOP AT lt_field INTO ls_field.
IF ls_field-tablename IS NOT INITIAL.
rs_tab-low = ls_field-tablename.
TRANSLATE rs_tab-low TO UPPER CASE.
rs_tab-option = 'EQ'.
rs_tab-sign = 'I'.
APPEND rs_tab TO rt_tab.
ENDIF.
IF ls_field-fieldname IS NOT INITIAL.
rs_fid-low = ls_field-fieldname.
TRANSLATE rs_fid-low TO UPPER CASE.
rs_fid-option = 'EQ'.
rs_fid-sign = 'I'.
APPEND rs_fid TO rt_fid.
ENDIF.
IF ls_field-elementname IS NOT INITIAL.
rs_ele-low = ls_field-elementname..
TRANSLATE rs_ele-low TO UPPER CASE.
rs_ele-option = 'EQ'.
rs_ele-sign = 'I'.
APPEND rs_ele TO rt_ele.
ENDIF.
IF ls_field-domainname IS NOT INITIAL.
rs_dom-low = ls_field-domainname..
TRANSLATE rs_dom-low TO UPPER CASE.
rs_dom-option = 'EQ'.
rs_dom-sign = 'I'.
APPEND rs_dom TO rt_dom.
ENDIF.
ENDLOOP.
IF rt_tab is INITIAL and rt_fid is INITIAL and rt_ele is INITIAL and rt_dom is INITIAL.
o_json = '请输入数据'.
RETURN.
ENDIF.
SELECT dd03l~tabname AS tablename
tabletype AS istable
fieldname
keyflag AS iskey
rollname AS elementname
checktable
datatype
leng AS length
decimals
dd03l~domname AS domainname
dd07t_1~ddtext AS shlporigin
dd07t_2~ddtext AS comptype
FROM dd03l
LEFT JOIN dd07t AS dd07t_1 ON dd07t_1~domname = 'SHLPORIGIN' AND dd07t_1~ddlanguage = '1' AND dd07t_1~domvalue_l = dd03l~shlporigin
LEFT JOIN dd07t AS dd07t_2 ON dd07t_2~domname = 'COMPTYPE' AND dd07t_2~ddlanguage = '1' AND dd07t_2~domvalue_l = dd03l~comptype
INTO TABLE lt_field
WHERE dd03l~tabname IN rt_tab
AND dd03l~fieldname IN rt_fid
AND dd03l~rollname IN rt_ele
AND dd03l~domname IN rt_dom
AND dd03l~as4local = 'A'.
IF lt_field IS NOT INITIAL.
o_json = /ui2/cl_json=>serialize( data = lt_field compress = '' pretty_name = /ui2/cl_json=>pretty_mode-none ).
ENDIF.
ENDMETHOD.
ENDCLASS.
START-OF-SELECTION.
DATA i_json TYPE string.
DATA o_json TYPE string.
i_json = '[{"tablename":"makt","istable":"","fieldname":"","iskey":"","elementname":"","checktable":"",' &&
'"datatype":"","length":"","decimals":"","domainname":"","shlporigin":"","comptype":""}]'.
CALL METHOD da=>getrelationship
EXPORTING
i_json = i_json
IMPORTING
o_json = o_json.
cl_demo_output=>display_json( o_json ).实现的功能就是表名，字段名，数据元素名，域名的联合查询，
0.1.2. 改写成JSON
0.1.3. C#主要程序
0.1.3.1. 创建一个连接SAP系统的类
usingSAP.Middleware.Connector;
using System;
usingSystem.Collections.Generic;
using System.Linq;
using System.Text;
namespace SAPconnector
{
classConnectSAP
{
RfcDestination rfcDest;
RfcConfigParameters Parms;
RfcRepository rfcrep;
IRfcFunction func;
string FFMName;
string InputName;
string OutputName;
publicclassConnectStruct
{
public ConnectStruct(bool p_isconnect, bool p_isconnectsuccessful,string p_connectinfo)
{
isconnect = p_isconnect;
isconnectsuccessful =p_isconnectsuccessful;
connectinfo = p_connectinfo;
}
publicbool isconnect =false;//连接状态：true正在连接，false连接结束
publicbool isconnectsuccessful =false;//是否成功连接,ture成功，false失败
publicstring connectinfo ="Without Connection";//连接信息
};
ConnectStruct connstru;
delegatevoidSetTextCallback(ConnectStruct info);//具有一个传入参数的委托
Func<ConnectStruct> func_sys;
Func<ConnectStruct> func_rfm;
IAsyncResult cookie;
Form1 form1;
public ConnectSAP(Form1 form1)
{
this.form1 = form1;
}
publicvoid XXXXXSystem()
{
form1.label1.Text ="Wait...";
connstru =newConnectStruct(false,false,"Without Connection");
RfcConfigParameters parms =newRfcConfigParameters();
parms.Add(RfcConfigParameters.Name,"XXX");
parms.Add(RfcConfigParameters.AppServerHost,"XXXXXXXXXXXXXX");
parms.Add(RfcConfigParameters.SystemNumber,"XX");
parms.Add(RfcConfigParameters.Client,"XXX");
parms.Add(RfcConfigParameters.User,"XXXXXXXX");
parms.Add(RfcConfigParameters.Password,"XXXXXXXXXXXX");
parms.Add(RfcConfigParameters.Language,"XX");
parms.Add(RfcConfigParameters.PoolSize,"X");
parms.Add(RfcConfigParameters.PeakConnectionsLimit,"XX");
parms.Add(RfcConfigParameters.IdleTimeout,"X");
parms.Add(RfcConfigParameters.MaxPoolWaitTime,"X");
parms.Add(RfcConfigParameters.SAPRouter,"XXXXXXXXXXXXXX");
FFMName ="ZZTEST_WEBSERVICE_02";
InputName ="ZZIMSEG";
OutputName ="ZZEMSEG";
this.SetConnectandRFM(parms);
}
publicstring ExecuteRFM(string i_param)
{
//先检查服务器是否连接成功（需要检查服务器是否真正连接），不成功不允许调用参数
if (connstru.isconnect ==false)
{
return"正在连接RFM中，请勿重复点击，当然我也不会处理！";//正在连接中，直接返回，不让调用RFC函数
}
else
{
if (connstru.isconnectsuccessful ==false)
{
return"连接RFM失败，请勿重复点击，当然我也不会处理！";//连接失败，直接返回，不让调用RFC函数
}
}
func.SetValue(InputName, i_param);//SAP里面的传入参数
func.Invoke(rfcDest);
//return func.GetValue(1).ToString();
return func.GetString(OutputName);
}
publicvoid SetRFM(string name,string inputname,string outputname)
{
if (rfcrep ==null)//如果没有创建数据对象仓库
{
return;
}
if (rfcrep.CachedFunctionMetadata.Count == 0)//如果数据仓库中没有添加函数对象
{
return;
}
if (rfcrep.CachedFunctionMetadata.Find(s =>s.Name.Equals(name)) !=null)//如果数据仓库中找到函数名为name的对象，则不设置函数
{
return;
}
FFMName = name;
InputName = inputname;
OutputName = outputname;
func_rfm = SetRFM;//泛型func<out ConnectStruct>设置了一个返回参数，不能用Acion
cookie = func_rfm.BeginInvoke((IAsyncResult res) => {this.SetLableText(func_rfm.EndInvoke(cookie));},null);//回调函数，当异步调用完成之后触发，第二个参数是传入参数
}
privateConnectStruct SetRFM()
{
try
{
func =rfcrep.CreateFunction(FFMName);//RFM名称
}
catch (Exception ex)
{
returnnewConnectStruct(true,false, ex.ToString());
}
returnnewConnectStruct(true,true,"Connect RFM successful!");
}
publicvoid SetConnectandRFM(RfcConfigParameters param)
{
Parms = param;
func_sys = SetConnectandRFM;//泛型func<out string>设置了一个返回参数，不能用Acion
cookie = func_sys.BeginInvoke((IAsyncResult res) => {this.SetLableText(func_sys.EndInvoke(cookie));},null);//回调函数，当异步调用完成之后触发，第二个参数是传入参数
}
privateConnectStruct SetConnectandRFM()
{
try
{
rfcDest =RfcDestinationManager.GetDestination(Parms);
rfcrep = rfcDest.Repository;
func =rfcrep.CreateFunction(FFMName);//RFM名称
}
catch (Exception ex)
{
returnnewConnectStruct(true,false, ex.ToString());
}
returnnewConnectStruct(true,true,"Connect successful!");
}
privatevoid SetLableText(ConnectStruct info)
{
// InvokeRequired required compares the thread IDof the
// calling thread to the thread ID of the creatingthread.
// If these threads are different, it returns true.
if (this.form1.label1.InvokeRequired)
{
SetTextCallback d =newSetTextCallback(SetLableText);
this.form1.Invoke(d,newobject[] { info });
}
else
{
this.form1.label1.Text +="\r\n"+ info.connectinfo;
connstru = info;
}
}
}
}0.1.3.2. 在主类的载入事件中连接系统和获取数据仓库指定RFM
privatevoid Form1_Load(object sender, EventArgs e)
{
connectSAP =newConnectSAP(this);
connectSAP.XXXXXSystem();
System.Threading.Thread.Sleep(100);
connectSAP.SetRFM("ZZTEST_WEBSERVICE_03","I_JSON","O_JSON");
System.Threading.Thread.Sleep(100);
}0.1.3.3. 载入内嵌EXCEL的方法
private void载入表字段关联模板ToolStripMenuItem_Click(object sender, EventArgs e)
{
app =new Microsoft.Office.Interop.Excel.Application();
if (app ==null)
{
return;
}
Workbooks workbooks = app.Workbooks;
Workbook workbook = workbooks.Add(Type.Missing);
worksheet = (Worksheet)workbook.Sheets[1];
worksheet.Select();
Range range1 =worksheet.get_Range("A1","L1");
if (range1 ==null)
{
return;
}
object[,] tabdata =newobject[1, 12];
tabdata[0, 0] ="表名";
tabdata[0, 1] ="是表？";
tabdata[0, 2] ="字段名";
tabdata[0, 3] ="主键？";
tabdata[0, 4] ="数据元素";
tabdata[0, 5] ="检查表";
tabdata[0, 6] ="数据类型";
tabdata[0, 7] ="长度";
tabdata[0, 8] ="精度";
tabdata[0, 9] ="域名";
tabdata[0, 10] ="搜索帮助类型";
tabdata[0, 11] ="数据元素类型";
range1.Value2 = (dynamic)tabdata;
range1.Activate();
IntPtr hwnd = (IntPtr)app.Hwnd;
SetParent(hwnd,this.Filltable.Handle);
MoveWindow((IntPtr)app.Hwnd,this.Filltable.ClientRectangle.Left,this.Filltable.ClientRectangle.Top+ 25, this.Filltable.ClientRectangle.Width,this.Filltable.ClientRectangle.Height- 25, false);
app.EditDirectlyInCell =true;//设置单元格直接可编辑，无意中发现，否者只能双击单元格，十分麻烦
app.DisplayAlerts =false;
app.Visible =true;
}0.1.3.4. 内嵌EXCEL大小随着父容器大小变化而变化
private void Filltable_Resize(object sender, EventArgs e)
{
if (app !=null && (IntPtr)app.Hwnd !=IntPtr.Zero)
{
MoveWindow((IntPtr)app.Hwnd,this.Filltable.ClientRectangle.Left,this.Filltable.ClientRectangle.Top+ 25, this.Filltable.ClientRectangle.Width,this.Filltable.ClientRectangle.Height- 25, false);
}
}0.1.3.5. 构建动态类方法的类结构
namespace SAPconnector
{
classProgramStru
{
[JsonProperty("METHODS")]
publicList<METHODSTRU> METHODS { get;set; }
publicstructMETHODSTRU
{
publicstring METHODNAME {get;set; }
publicList<PARAM> INPUTDATA { get;set; }
publicList<PARAM> OUTPUTDATA { get;set; }
publicstring BODY {get;set; }
}
publicstructPARAM
{
public string DATATYPE {get;set; }
public string DATANAME {get;set; }
publicstring DATAVALUE {get;set; }
}
}
}
0.1.3.6. 获取和处理内嵌EXCEL数据并调用RFM回写EXCEL
privatevoid获取ToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
connectSAP.SetRFM("ZZTEST_WEBSERVICE_03","I_JSON","O_JSON");
System.Threading.Thread.Sleep(100);
Range range1 = ((_Worksheet)app.Workbooks.get_Item(1).Worksheets.get_Item(1)).UsedRange;
range1 = ((_Worksheet)app.Workbooks.get_Item(1).Worksheets.get_Item(1)).UsedRange;
object[,] data = range1.get_Value();
TabTableStru.Clear();
for (int i = 2; i <= data.GetLength(0); i++)
{
indexTableStru = newTableFieldStru();
indexTableStru.tablename =convertStr(data[i, 1]);
indexTableStru.istable =convertStr(data[i, 2]);
indexTableStru.fieldname =convertStr(data[i, 3]);
indexTableStru.iskey =convertStr(data[i, 4]);
indexTableStru.elementname= convertStr(data[i, 5]);
indexTableStru.checktable =convertStr(data[i, 6]);
indexTableStru.datatype =convertStr(data[i, 7]);
indexTableStru.length =convertStr(data[i, 8]);
indexTableStru.decimals =convertStr(data[i, 9]);
indexTableStru.domainname =convertStr(data[i, 10]);
indexTableStru.shlporigin =convertStr(data[i, 11]);
indexTableStru.comptype =convertStr(data[i, 12]);
TabTableStru.Add(indexTableStru);
}
if (TabTableStru.Count == 0)
{
this.label1.Text +="\r\n" +"获取Excel数据失败";
return;
}
string AAA =JsonConvert.SerializeObject(TabTableStru);
this.label1.Text = AAA;
ProgramStru programStru =newProgramStru();
ProgramStru.PARAM param;
List<ProgramStru.METHODSTRU> TABMETHODSTRU = newList<ProgramStru.METHODSTRU>();
ProgramStru.METHODSTRU methods =newProgramStru.METHODSTRU();
methods.METHODNAME = "METHOD1";
param =newProgramStru.PARAM();
param.DATANAME = "I_JSON";
param.DATATYPE = "STRING";
param.DATAVALUE = AAA;
List<ProgramStru.PARAM> TABPARAM = newList<ProgramStru.PARAM>();
TABPARAM.Clear();
TABPARAM.Add(param);
methods.INPUTDATA = TABPARAM;
param = newProgramStru.PARAM();
param.DATANAME = "O_JSON";
param.DATATYPE = "STRING";
param.DATAVALUE ="";
TABPARAM = newList<ProgramStru.PARAM>();
TABPARAM.Clear();
TABPARAM.Add(param);
methods.OUTPUTDATA = TABPARAM;
methods.BODY = "TYPES: BEGIN OFty_field, tablename TYPE dd03l-tabname, istable TYPE dd03l-tabletype, fieldnameTYPE dd03l-fieldname, iskey TYPE dd03l-keyflag, elementname TYPEdd03l-rollname, checktable TYPEdd03l-checktable, datatype TYPE dd03l-datatype, length TYPE dd03l-leng, decimalsTYPE dd03l-decimals, domainname TYPEdd03l-domname, shlporigin TYPEdd07t-ddtext, comptype TYPE dd07t-ddtext, END OF ty_field. DATA:lt_field TYPETABLE OF ty_field, ls_field TYPE ty_field. DATA:rt_tab TYPE RANGE OFdd03l-tabname, rt_fid TYPE RANGE OF dd03l-fieldname, rt_ele TYPE RANGE OFdd03l-rollname, rt_dom TYPE RANGE OF dd03l-domname. DATA:rs_tab LIKE LINE OFrt_tab, rs_fid LIKE LINE OF rt_fid, rs_ele LIKE LINE OF rt_ele, rs_dom LIKE LINE OF rt_dom. /ui2/cl_json=>deserialize(EXPORTING json = i_json pretty_name = /ui2/cl_json=>pretty_mode-noneCHANGING data = lt_field ). LOOP AT lt_field INTO ls_field. IFls_field-tablename IS NOT INITIAL. rs_tab-low = ls_field-tablename. TRANSLATErs_tab-low TO UPPER CASE. rs_tab-option = 'EQ'. rs_tab-sign = 'I'. APPENDrs_tab TO rt_tab. ENDIF. IF ls_field-fieldname IS NOT INITIAL. rs_fid-low =ls_field-fieldname. TRANSLATE rs_fid-low TO UPPER CASE. rs_fid-option = 'EQ'.rs_fid-sign = 'I'. APPEND rs_fid TO rt_fid. ENDIF. IF ls_field-elementname ISNOT INITIAL. rs_ele-low = ls_field-elementname. TRANSLATE rs_ele-low TO UPPERCASE. rs_ele-option = 'EQ'. rs_ele-sign = 'I'. APPEND rs_ele TO rt_ele. ENDIF.IF ls_field-domainname IS NOT INITIAL. rs_dom-low = ls_field-domainname.TRANSLATE rs_dom-low TO UPPER CASE. rs_dom-option = 'EQ'. rs_dom-sign = 'I'.APPEND rs_dom TO rt_dom. ENDIF. ENDLOOP. IF rt_tab IS INITIAL AND rt_fid ISINITIAL AND rt_ele IS INITIAL AND rt_dom IS INITIAL. o_json = '请输入数据'. RETURN. ENDIF. SELECTdd03l~tabname AS tablename tabletype AS istable fieldname keyflag AS iskeyrollname AS elementname checktable datatype leng AS length decimalsdd03l~domname AS domainname dd07t_1~ddtext AS shlporigin dd07t_2~ddtext AScomptype FROM dd03l LEFT JOIN dd07t AS dd07t_1 ON dd07t_1~domname = 'SHLPORIGIN' AND dd07t_1~ddlanguage = '1' AND dd07t_1~domvalue_l = dd03l~shlporiginLEFT JOIN dd07t AS dd07t_2 ON dd07t_2~domname = 'COMPTYPE' ANDdd07t_2~ddlanguage = '1' AND dd07t_2~domvalue_l = dd03l~comptype INTO TABLElt_field WHERE dd03l~tabname IN rt_tab AND dd03l~fieldname IN rt_fid ANDdd03l~rollname IN rt_ele AND dd03l~domname IN rt_dom AND dd03l~as4local = 'A'.IF lt_field IS NOT INITIAL. o_json = /ui2/cl_json=>serialize( data =lt_field compress = ' ' pretty_name = /ui2/cl_json=>pretty_mode-none ).ENDIF.";
TABMETHODSTRU.Add(methods);
programStru.METHODS =TABMETHODSTRU;
string sss =JsonConvert.SerializeObject(programStru);
string str_SAPOutput = connectSAP.ExecuteRFM(sss);
this.label1.Text = str_SAPOutput;
dynamic model =JsonConvert.DeserializeObject(str_SAPOutput);
string outstring = model[0].OUTPUTDATA[0].DATAVALUE.ToString();
model = JsonConvert.DeserializeObject(outstring);
int row = model.Count;
Range range2 = worksheet.get_Range("A2","L" + (row+1));
if (range2 ==null)
{
return;
}
object[,] tabdata =newobject[model.Count, 12];
for (int i = 0; i < model.Count; i++)
{
tabdata[i, 0] =model[i].TABLENAME.ToString();
tabdata[i, 1] =model[i].ISTABLE.ToString();
tabdata[i, 2] =model[i].FIELDNAME.ToString();
tabdata[i, 3] =model[i].ISKEY.ToString();
tabdata[i, 4] =model[i].ELEMENTNAME.ToString();
tabdata[i, 5] =model[i].ELEMENTNAME.ToString();
tabdata[i, 6] =model[i].DATATYPE.ToString();
tabdata[i, 7] =model[i].DATATYPE.ToString();
tabdata[i, 8] =model[i].DECIMALS.ToString();
tabdata[i, 9] =model[i].DOMAINNAME.ToString();
tabdata[i, 10] =model[i].SHLPORIGIN.ToString();
tabdata[i, 11] =model[i].COMPTYPE.ToString();
}
range2.Value2 = (dynamic)tabdata;
}
catch (Exception)
{
}
}0.1.3.7. 关闭应用程序同时注销EXCEL进程
//关闭窗体触发
protectedoverridevoid OnHandleDestroyed(EventArgs e)
{
// Stop the application
if (app !=null )
{
// Post a colse message
//PostMessage((IntPtr)app.Hwnd,WM_CLOSE, 0, 0);
app.DisplayAlerts = false;
app.Quit();
// Delay for it to get the message
System.Threading.Thread.Sleep(1000);
// Clear internal handle
}
base.OnHandleDestroyed(e);
}