C# 服务调用RFC函数获取物料信息，并输出生成Excel文件

这个例子是C#服务调用RFC函数，获取物料的信息，并生成Excel文件

上接文章：C#服务

文章目录

创建函数
创建结构
编写源代码
创建批处理文件
运行结果-成功
部署服务器
C#代码
配置文件
注意！！

创建函数

在这里插入图片描述

创建结构

在这里插入图片描述

编写源代码

在这里插入图片描述

创建批处理文件

在这里插入图片描述

echo %~dp0
%SystemRoot%\Microsoft.NET\Framework64\v4.0.30319\installUtil.exe %~dp0WindowsService1_SAP_RFC.exe
Net Start C#ServerTest
sc config C#ServerTest=auto
pause

在这里插入图片描述

%SystemRoot%\Microsoft.NET\Framework64\v4.0.30319\InstallUtil.exe /u %~dp0WindowsService1_SAP_RFC.exe
pause

运行结果-成功

在这里插入图片描述

部署服务器

在这里插入图片描述

C#代码

using SAP.Middleware.Connector;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Threading.Tasks;
using System.Timers;namespace WindowsService1_SAP_RFC
{//创建人：LiuHongyu//创建日期：2024年12月20日//描述：测试C#服务的创建以及和SAP进行通讯，并生成Excel文档public partial class Service1 : ServiceBase{public Service1(){InitializeComponent();}//文件路径private string filePath = ConfigurationManager.AppSettings["FilePath"];//Excel文件路径private string excelFilePath = ConfigurationManager.AppSettings["ExcelFilePath"];//Excel模板文件路径private string excelFileTemplatePath = ConfigurationManager.AppSettings["ExcelFileTemplatePath"];private int n = 1;//计时器Timer timer;//rfc的地址private RfcDestination _rfcDestination;//每天定时器开关private bool dayScheduleSwitch;//每天定时执行的时间private string dayScheduleTime;//每月定时器开关private bool monthScheduleSwitch;//服务启动时调用protected override void OnStart(string[] args){WriteLog(filePath, "启动服务------");InitTimer();}private void InitTimer(){try{WriteLog(filePath, "开始计数------");timer = new Timer(60000);//1分钟//时间间隔到了之后，执行的操作为Executetimer.Elapsed += Execute;//是否重复执行timer.AutoReset = true;  //如果不设置就是只会执行一次//是否执行Elapsed中的事件timer.Enabled = true;}catch (Exception ex){WriteLog(filePath, "计时器错误: " + ex.Message);throw;}}//执行的操作private void Execute(object sender, ElapsedEventArgs e){//注册SAP客户端this.RegisterDestination();//获取当前的每日定时开关状态dayScheduleSwitch = Convert.ToBoolean(ConfigurationManager.AppSettings["DayScheduleSwitch"]);//获取每日定时执行的时间dayScheduleTime = ConfigurationManager.AppSettings["DayScheduleTime"];//获取当时的每月定时开关状态monthScheduleSwitch = Convert.ToBoolean(ConfigurationManager.AppSettings["MonthScheduleSwitch"]);//如果每日定时开关打开，并且当时是设置的时间if (dayScheduleSwitch && DateTime.Now.ToString("t") == dayScheduleTime){timer.Stop();//获取SAP数据this.InvokeRFCFunctionGetJobList();WriteLog(filePath, "第 " + n + " 次数据写入完毕------");n++;timer.Start();}else if (monthScheduleSwitch) //每月定时开关是打开状态{DateTime dt = DateTime.Now;int year = dt.Date.Year;    //获取当前的年int month = dt.Date.Month;  //获取当前的月int dayCount = DateTime.DaysInMonth(year, month); //返回这个当前年当前月的天数var endDayOfMonth = new DateTime(year, month, dayCount, 0, 0, 0);  //最后一天if (DateTime.Now.ToString("g") == endDayOfMonth.ToString("g"))//g：常规（短日期和短时间）{timer.Stop();//获取SAP数据this.InvokeRFCFunctionGetJobList();WriteLog(filePath, "第 " + n + " 次数据写入完毕------");n++;timer.Start();}}//DateTime dt = DateTime.Now;//int year = dt.Date.Year;    //获取当前的年//int month = dt.Date.Month;  //获取当前的月//int dayCount = DateTime.DaysInMonth(year, month); //返回这个当前年当前月的天数//var endDayOfMonth = new DateTime(year, month, dayCount, 0, 0, 0);  //最后一天if (DateTime.Now.ToString("t") == "7:00" || DateTime.Now.ToString("t") == "11:30" || DateTime.Now.ToString("t") == "18:00")if (DateTime.Now.ToString("g") == endDayOfMonth.ToString("g"))//g：常规（短日期和短时间）//if (DateTime.Now.ToString("t") == "16:30")//{//    timer.Stop();//    //获取SAP数据//    this.InvokeRFCFunctionGetJobList();//    WriteLog(filePath, "第 " + n + " 次数据写入完毕------");//    n++;//    timer.Start();//}}//获取函数private void InvokeRFCFunctionGetJobList(){//数据表DataTable dataTable = new DataTable();//根据Destination获取函数IRfcFunction rfcFunction = null;try{WriteLog(filePath, "获取数据，第 "+ n +" 次------");RfcRepository rfcRepository = _rfcDestination.Repository;rfcFunction = rfcRepository.CreateFunction("Z_TEST_CSHAP_GET_MARA");rfcFunction.SetValue("I_MATKL", "10010101");  //设置参数rfcFunction.SetParameterActive(0, false); //设置参数有效性,false无效rfcFunction.Invoke(_rfcDestination);     //执行函数IRfcTable rfcTable = rfcFunction.GetTable("OT_TABLE"); //获取返回的表//给数据表添加列结构for (int i = 0; i < rfcTable.ElementCount; i++){RfcElementMetadata rfcElementMetadata = rfcTable.GetElementMetadata(i);dataTable.Columns.Add(rfcElementMetadata.Name);}//填充数据表DataRow drH = dataTable.NewRow();drH["MATNR"] = "物料编号";drH["MAKTX"] = "物料描述";drH["ERSDA"] = "创建日期";drH["MATKL"] = "物料组";drH["MEINS"] = "基本计量单位";drH["ZGUIG"] = "规格";dataTable.Rows.Add(drH);//填充数据foreach (IRfcStructure rs in rfcTable){DataRow dr = dataTable.NewRow();//循环每一列for (int i = 0; i < rfcTable.ElementCount; i++){RfcElementMetadata rfcElement = rfcTable.GetElementMetadata(i);dr[rfcElement.Name] = rs.GetString(rfcElement.Name);}dataTable.Rows.Add(dr);}dataTable.TableName = "Data";//复制模板Excel，覆盖原来的旧的Excel文件File.Copy(excelFileTemplatePath, excelFilePath, true);//选择解析文档格式相匹配的字符串string connectString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFilePath + ";" + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=0'";using(OleDbConnection con = new OleDbConnection(connectString)){con.Open();string insertCmd = "INSERT INTO [Sheet1$] (物料编号,物料描述,创建日期,物料组,基本计量单位,规格) VALUES (?, ?, ?, ?, ?, ?)";//拼接插入语句；//创建执行命令OleDbCommand command = new OleDbCommand(insertCmd, con);//定义执行命令对应值的参数类型；command.Parameters.Add("@物料编号", OleDbType.VarChar);command.Parameters.Add("@物料描述", OleDbType.VarChar);command.Parameters.Add("@创建日期", OleDbType.DBDate);command.Parameters.Add("@物料组", OleDbType.VarChar);command.Parameters.Add("@基本计量单位", OleDbType.VarChar);command.Parameters.Add("@规格", OleDbType.VarChar);//执行插入语句for (int i = 1; i < dataTable.Rows.Count; i++){command.Parameters["@物料编号"].Value = dataTable.Rows[i]["MATNR"].ToString();command.Parameters["@物料描述"].Value = dataTable.Rows[i]["MAKTX"].ToString();command.Parameters["@创建日期"].Value = dataTable.Rows[i]["ERSDA"].ToString();command.Parameters["@物料组"].Value = dataTable.Rows[i]["MATKL"].ToString();command.Parameters["@基本计量单位"].Value = dataTable.Rows[i]["MEINS"].ToString();command.Parameters["@规格"].Value = dataTable.Rows[i]["ZGUIG"].ToString();command.ExecuteNonQuery();}con.Close();}#region 写入txt文件,已经注释写入txt文件//string dataFilePath = "D:\\Projects\\Visual_Studio_2022\\WindowsService1_SAP_RFC\\Data.txt";//using (StreamWriter sw = new StreamWriter(dataFilePath,true))//{//    foreach (IRfcStructure rs in rfcTable)//    {//        sw.Write(rs.GetString(0).Trim().ToString() + " ");//        sw.Write(rs.GetString(1).Trim().ToString() + " ");//        sw.Write(rs.GetString(2).Trim().ToString() + " ");//        sw.Write(rs.GetString(3).Trim().ToString() + " ");//        sw.Write(rs.GetString(4).Trim().ToString() + " ");//        sw.Write(rs.GetString(5).Trim().ToString() + " ");//        sw.WriteLine();//    }//}#endregion}catch (Exception ex){WriteLog(filePath, "发生错误: " + ex.Message);throw;}}private void RegisterDestination(){try{if (_rfcDestination == null){//_rfcDestination = RfcDestinationManager.GetDestination("S4P");  //正式库_rfcDestination = RfcDestinationManager.GetDestination("S4D");  //测试库}}catch (Exception ex){WriteLog(filePath, "SAP注册客户端失败: " + ex.Message);}}//写日志private void WriteLog(string filePath,string message){StreamWriter sw = null;try{//创建一个log文件sw = new StreamWriter(filePath, true);sw.WriteLine(message + DateTime.Now.ToString());}catch(Exception ex){sw.Write(ex.Message + DateTime.Now.ToString());}finally{if (sw != null){sw.Close();  //关闭流对象sw.Dispose(); //释放资源}}}//服务停止时调用protected override void OnStop(){WriteLog(filePath, "停止服务------");}}
}

配置文件

在这里插入图片描述

<?xml version="1.0" encoding="utf-8" ?>
<configuration><!--关于连接SAP的配置--><configSections><sectionGroup name="SAP.Middleware.Connector"><sectionGroup name="ClientSettings"><section name="DestinationConfiguration" type="SAP.Middleware.Connector.RfcDestinationConfiguration,sapnco"/></sectionGroup></sectionGroup></configSections><SAP.Middleware.Connector><ClientSettings><DestinationConfiguration><destinations><!--正式系统800--><!--<add NAME="S4P" USER="999999" PASSWD="Hs123456789" CLIENT="800" SYSNR="00" ASHOST="192.168.4.23" LANG="ZH" GROUP="PUBLIC" MAX_POOL_SIZE="10" IDLE_TIMEOUT="600"/>--><!--测试系统310--><add NAME="S4D" USER="024083" PASSWD="Hs87654321" CLIENT="310" SYSNR="00" ASHOST="192.168.4.20" LANG="ZH" GROUP="PUBLIC" MAX_POOL_SIZE="10" IDLE_TIMEOUT="600"/></destinations></DestinationConfiguration></ClientSettings></SAP.Middleware.Connector><startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" /></startup><startup useLegacyV2RuntimeActivationPolicy="true"><supportedRuntime version="v4.0"/></startup><!--配置日志的位置--><appSettings><!--本机测试目录--><add key="FilePath" value="D:\Projects\Visual_Studio_2022\WindowsService1_SAP_RFC\log.txt" /><add key="ExcelFilePath" value="D:\Projects\Visual_Studio_2022\WindowsService1_SAP_RFC\SAP返回数据.xlsx"/><add key="ExcelFileTemplatePath" value="D:\Projects\Visual_Studio_2022\WindowsService1_SAP_RFC\SAP返回数据模板.xlsx"/><!--服务器测试目录--><!--<add key="FilePath" value="D:\HS_C#Service_RFC_SAP_TEST\WindowsService1_SAP_RFC\log.txt" /><add key="ExcelFileTemplatePath" value="D:\HS_C#Service_RFC_SAP_TEST\WindowsService1_SAP_RFC\SAP返回数据模板.xlsx"/><add key="ExcelFilePath" value="D:\HS_C#Service_RFC_SAP_TEST\WindowsService1_SAP_RFC\SAP返回数据.xlsx"/>--><!--每月循环开关MonthScheduleSwitch，如果打开（设置为true）就是每月月末执行生成Excel，关闭（设置为false）就不执行--><add key="MonthScheduleSwitch" value="false"/><!--定时开关DayScheduleSwitch，如果打开（设置为true）就是每天定时执行生成Excel，关闭（设置为false）就不执行--><add key="DayScheduleSwitch" value="true"/><!--time:指定每天定时生成Excel的时间 9:00,10:00的格式--><add key="DayScheduleTime" value="14:30"/><add key="ClientSettingsProvider.ServiceUri" value="" /></appSettings>
</configuration>