/// <summary>
/// 获取表导入Excel
/// </summary>
/// <param name="rfctable"></param>
/// <param name="ExcelPath"></param>
/// <param name="Sheet"></param>
public static void GetTableToExcel(string rfctable, string ExcelPath, string Sheet)
{
RPADataExport.ImportDispose(
new IRfcTableRPADataReader(func.GetTable(rfctable)),
new ExcelDataExport(ExcelPath, Sheet)
);
}
/// <summary>
/// 获取表导入TXT
/// </summary>
/// <param name="rfctable">表名称</param>
/// <param name="path">文件路径</param>
/// <param name="delimiter">分隔符,如果为空则判断文件后缀,csv为",",其余默认"\t"</param>
public static void GetTableToTXT(string rfctable, string path, string delimiter = "")
{
RPADataExport.ImportDispose(
new IRfcTableRPADataReader(func.GetTable(rfctable)),
new TXTDataExport(path, delimiter)
);
}
static void SqlServerToTXT(string[] p)
{
if (p.Length < 3)
{
Console.WriteLine("DataSource\tip,prot"); //2
Console.WriteLine("DataBase\t数据库名称"); //3
Console.WriteLine("user\t\t用户"); //4
Console.WriteLine("pwd\t\t密码"); //5
Console.WriteLine("SQL"); //6
Console.WriteLine("Path"); //7
}
else
{
string connStr = $@"Data Source={p[2]};Initial Catalog={p[3]};User ID={p[4]};Pwd={p[5]};";
using (SqlConnection connection = new SqlConnection(connStr))
{
SqlCommand command = new SqlCommand(p[6], connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
RPADataExport.ImportDispose(
reader,
new TXTDataExport(p[7])
);
}
}
}
protected override void Action()
{
AccessDataReader accessDataReader = new AccessDataReader(accessPath, SQL);
count = accessDataReader.Count;
RPADataExport.ImportDispose(accessDataReader, new DataTableDataExport(result));
}
/// <summary>
/// 获取表导入SqlServer
/// </summary>
/// <param name="rfctable"></param>
/// <param name="DataSource"></param>
/// <param name="DataBase"></param>
/// <param name="user"></param>
/// <param name="pwd"></param>
/// <param name="table"></param>
/// <param name="appand">是否附加数据,默认true,否则清空表</param>
/// <param name="timeout">超时时间(秒),默认1800(半小时),小于0时使用默认值</param>
public static void GetTableToSqlServer(string rfctable, string DataSource, string DataBase, string user, string pwd, string table, bool appand = true, decimal timeout = 0)
{
if (timeout < 0)
{
timeout = 1800;
}
RPADataExport.ImportDispose(
new IRfcTableRPADataReader(func.GetTable(rfctable)),
new SQLServerDataExport(DataSource, DataBase, user, pwd, table, appand, (int)timeout)//超时时间半小时
);
}
static void ExcelToSqlServer(string[] p)
{
if (p.Length < 3)
{
Console.WriteLine("DataSource\tip,prot"); //2
Console.WriteLine("DataBase\t数据库名称"); //3
Console.WriteLine("user\t\t用户"); //4
Console.WriteLine("pwd\t\t密码"); //5
Console.WriteLine("Table"); //6
Console.WriteLine("ExcelPath"); //7
Console.WriteLine("sheet"); //8
Console.WriteLine("TRUNCATE TABLE\t如果不是0则清空数据表"); //9
}
else
{
RPADataExport.ImportDispose(
new ExcelDataReader(p[7], p[8]),
new SQLServerDataExport(p[2], p[3], p[4], p[5], p[6], p[9].Equals("0"))//超时时间半小时
);
}
}
public static void Access_CSO_Test()
{
RPADataExport.ImportDispose(
new ExcelDataReader(
@"C:\Users\zhang\Desktop\a.xlsx", "d"
),
//new AccessDataImport(
// @"C:\Users\zhang\Desktop\a.accdb",
// "d"
//)
//请考虑使用 OLEDB 而不是 ODBC
//new DbDataImport(
// new System.Data.Odbc.OdbcConnection(@"Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\Users\zhang\Desktop\a.accdb;"),
// "d"
//)
new DbDataExport(
new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\zhang\Desktop\a.accdb;"),
"d"
)
);
//AccessAction.ClearUp();
new Process_Close();
}
protected override ResponseData RunAction(RequestData requestData)
{
if (requestData.ObjectName.Equals("Excel CSO"))
{
//高阶-运行Excel函数 HighLevel_RunFunction
if (requestData.Action.Equals("HighLevel_RunFunction"))
{
HighLevel_RunFunction highLevel_RunFunction = new HighLevel_RunFunction(
(string)requestData.InputParams["wbPath"].Value,
(string)requestData.InputParams["wsName"].Value,
(string)requestData.InputParams["VBACode"].Value,
(string)requestData.InputParams["FunctionName"].Value,
(string)requestData.InputParams["param1"].Value,
(string)requestData.InputParams["param2"].Value,
(string)requestData.InputParams["param3"].Value,
(string)requestData.InputParams["param4"].Value,
(string)requestData.InputParams["param5"].Value,
(string)requestData.InputParams["param6"].Value,
(string)requestData.InputParams["param7"].Value,
(string)requestData.InputParams["param8"].Value,
(string)requestData.InputParams["param9"].Value,
(string)requestData.InputParams["param10"].Value
);
return(new ResponseData(new Dictionary <string, Param>()
{
{ "Ret", new Param(highLevel_RunFunction.Ret) }
}));
}
//单元格-写入集合 Range_WriteToDataTable
else if (requestData.Action.Equals("Range_WriteToDataTable"))
{
RPADataExport.ImportDispose(
new DataTableDataReader((System.Data.DataTable)requestData.InputParams["Table"].Value),
new ExcelDataExport(
requestData.InputParams["ExcelPath"].Value as string,
requestData.InputParams["Sheet"].Value as string,
requestData.InputParams["range"].Value as string,
(bool)requestData.InputParams["withTitle"].Value
)
);
return(new ResponseData(new Dictionary <string, Param>()));
}
//单元格-读取集合 Range_WriteFromDataTable
else if (requestData.Action.Equals("Range_WriteToDataTable"))
{
var table = new System.Data.DataTable();
RPADataExport.ImportDispose(
new ExcelDataReader(
(string)requestData.InputParams["ExcelPath"].Value,
(string)requestData.InputParams["Sheet"].Value,
(string)requestData.InputParams["range"].Value
),
new DataTableDataExport(table)
);
return(new ResponseData(new Dictionary <string, Param>()
{
{ "Table", new Param(table) }
}));
}
}
else if (requestData.ObjectName.Equals("CSRFC"))
{
//配置RFC SetRFCConf
if (requestData.Action.Equals("SetRFCConf"))
{
CSRFC.CSRFC.SetRFCConf(
(string)requestData.InputParams["host"].Value,
(string)requestData.InputParams["user"].Value,
(string)requestData.InputParams["pwd"].Value,
(string)requestData.InputParams["client"].Value,
(string)requestData.InputParams["language"].Value,
(string)requestData.InputParams["timeout"].Value,
(string)requestData.InputParams["name"].Value,
(string)requestData.InputParams["funcName"].Value
);
return(new ResponseData(new Dictionary <string, Param>()));
}
//设置RFC参数
else if (requestData.Action.Equals("SetValue"))
{
CSRFC.CSRFC.SetValue(
(string)requestData.InputParams["name"].Value,
(string)requestData.InputParams["value"].Value
);
return(new ResponseData(new Dictionary <string, Param>()));
}
//执行RFC
else if (requestData.Action.Equals("Invoke"))
{
CSRFC.CSRFC.Invoke();
return(new ResponseData(new Dictionary <string, Param>()));
}
//获取返回值
else if (requestData.Action.Equals("GetValue"))
{
string value = CSRFC.CSRFC.GetValue(
(string)requestData.InputParams["name"].Value
);
return(new ResponseData(new Dictionary <string, Param>()
{
{ "value", new Param(value) }
}));
}
//获取表导入TXT
else if (requestData.Action.Equals("GetTableToTXT"))
{
CSRFC.CSRFC.GetTableToTXT(
(string)requestData.InputParams["rfctable"].Value,
(string)requestData.InputParams["path"].Value,
(string)requestData.InputParams["delimiter"].Value
);
return(new ResponseData(new Dictionary <string, Param>()));
}
//获取表导入Excel
else if (requestData.Action.Equals("GetTableToExcel"))
{
CSRFC.CSRFC.GetTableToExcel(
(string)requestData.InputParams["rfctable"].Value,
(string)requestData.InputParams["ExcelPath"].Value,
(string)requestData.InputParams["Sheet"].Value
);
return(new ResponseData(new Dictionary <string, Param>()));
}
//获取表导入SqlServer
else if (requestData.Action.Equals("GetTableToSqlServer"))
{
CSRFC.CSRFC.GetTableToSqlServer(
(string)requestData.InputParams["rfctable"].Value,
(string)requestData.InputParams["DataSource"].Value,
(string)requestData.InputParams["DataBase"].Value,
(string)requestData.InputParams["user"].Value,
(string)requestData.InputParams["pwd"].Value,
(string)requestData.InputParams["table"].Value,
(bool)requestData.InputParams["appand"].Value,
(decimal)requestData.InputParams["timeout"].Value
);
return(new ResponseData(new Dictionary <string, Param>()));
}
}
throw new Exception($"没有找到对象({requestData.ObjectName})或者操作({requestData.Action})");
}