public static object Run(object parameters, ExcelFunc excelFunc, [System.Runtime.CompilerServices.CallerMemberName] string callerFunctionName = null, bool async = true)
{
var asyncResult = async ? ExcelAsyncUtil.Run(callerFunctionName, parameters, excelFunc) : excelFunc();
return asyncResult == null || asyncResult.Equals(ExcelError.ExcelErrorNA)
? (async ? ExcelError.ExcelErrorGettingData : ExcelError.ExcelErrorNA)
: asyncResult;
}
public static object GetBookPositions(
[ExcelArgument(AllowReference = true, Name = "Book")] string bookId = "",
[ExcelArgument(AllowReference = true, Name = "Position Date")] string businessDate = "")
{
var caller = AddinContext.Excel.Call(XlCall.xlfCaller);
ExcelFunc getData = () =>
{
if (!IsLoggedIn)
{
throw new ApplicationException($"Please login from the Argomi tab.");
}
if (AddinContext.AssumedAmid == 0)
{
throw new ApplicationException($"User {AddinContext.Username} does not have a valid asset manager relationship");
}
var bookIds = bookId.MatchAll() ? null : new List <string> {
bookId
};
var positionDate = !businessDate.MatchAll() &&
DateTime.TryParse(businessDate, CultureInfo.CurrentCulture, DateTimeStyles.AssumeLocal, out DateTime businessDateParsed)
? (DateTime?)businessDateParsed
: null;
var api = AddinContext.Container.Resolve <ITransactionsInterface>();
var positions = api.SearchPositions(
assetManagerId: AddinContext.AssumedAmid,
bookIds: bookIds,
positionDate: positionDate,
pageNo: 1,
pageSize: QueryConstants.DefaultPageSize).Result;
var assetsApi = AddinContext.Container.Resolve <IAssetsInterface>();
var assets = assetsApi.SearchAssets(
assetManagerId: AddinContext.AssumedAmid,
assetIds: positions.Select(p => p.AssetId).ToList(),
pageNo: 1,
pageSize: QueryConstants.DefaultPageSize).Result;
var models = positions.Select(p =>
new EnrichedModel <Position, Asset>(p, assets.FirstOrDefault(a => a.AssetId == p.AssetId)));
return(ExcelTable.Format(models, AddinContext.Container.Resolve <IFormatter <EnrichedModel <Position, Asset> > >(), caller));
};
var output = AddinContext.Excel.Run(
UdfNames.BookPositionSearch,
string.Join(",", bookId, businessDate),
getData);
return(output?.Equals(ExcelError.ExcelErrorNA) ?? true ? ExcelError.ExcelErrorGettingData : output);
}
/// <summary>
/// Trato de forma assincrona todas as chamadas para não travar o Excel e enquanto o metodo não retornar p
/// </summary>
/// <param name="func">Delegate que será executado</param>
/// <param name="parametro">CorrelationID que utilizo para saber para qual delegate retorno o resultado da função</param>
/// <returns></returns>
public static dynamic Run(ExcelFunc func, string parametro)
{
var stackTrace = new StackTrace();
string methodBase = stackTrace.GetFrame(1).GetMethod().Name;
try
{
dynamic rt = ExcelAsyncUtil.Run(methodBase, parametro, func);
if (rt is ExcelError)
{
//Mensagem que será exibida ao usuário enquanto o delegate está sendo executado.
if (rt == ExcelError.ExcelErrorNA)
{
return("#Aguarde...");
}
}
return(rt);
}
catch (Exception ex)
{
return("Ops, " + ex.Message);
}
}
// Make a one-shot 'Observable' from the func
public static object ProcessFunc(string functionName, object parameters, ExcelFunc func)
{
return(ProcessObservable(functionName, parameters,
delegate { return new ThreadPoolDelegateObservable(func); }));
}
// Async function support
public static object Run(string callerFunctionName, object callerParameters, ExcelFunc asyncFunc)
{
return AsyncObservableImpl.ProcessFunc(callerFunctionName, callerParameters, asyncFunc);
}
public override object Run(string callerFunctionName, object callerParameters, ExcelFunc asyncFunc)
{
return(ExcelAsyncUtil.Run(callerFunctionName, callerParameters, asyncFunc));
}
// Async function support
public static object Run(string callerFunctionName, object callerParameters, ExcelFunc asyncFunc)
{
return(AsyncObservableImpl.ProcessFunc(callerFunctionName, callerParameters, asyncFunc));
}
/// <summary>
/// 通用函数,读文本文件
/// </summary>
/// <param name="fileName">读入的文本文件名称</param>
public string ImportFileData(string fileName, string A308_ID, string log_key_, ref DataTable dt_data)
{
log_key = log_key_;
string strRecord = "";
int errorcount = 0;
DataTable dt_a308 = new DataTable();
dt_a308 = Fun.getDtBySql("Select t.* from A308 t where a308_id='" + A308_ID + "'");
DataTable mydt = new DataTable();
string FILE_TYPE = dt_a308.Rows[0]["FILE_TYPE"].ToString().ToUpper();
string CHECK_NAME = dt_a308.Rows[0]["CHECK_NAME"].ToString().ToUpper();
string index__ = "";
if (FILE_TYPE == "*")
{
FILE_TYPE = System.IO.Path.GetExtension(fileName).ToUpper();
FILE_TYPE = FILE_TYPE.Substring(1, 3);
if (FILE_TYPE != "XLS")
{
FILE_TYPE = "TXT";
}
}
else
{
FILE_TYPE = FILE_TYPE.Substring(0, 3);
}
DataTable dt_a30801 = new DataTable();
dt_a30801 = Fun.getDtBySql("Select t.* , -1 as col_num from A30801 t where a308_id='" + A308_ID + "' order by line_no ");
if (dt_a30801.Rows.Count == 0)
{
return("1");
}
DataTable dt_a30802 = new DataTable();
dt_a30802 = Fun.getDtBySql("Select t.* from A30802 t where a308_id='" + A308_ID + "' order by line_no ");
if (dt_a30802.Rows.Count == 0)
{
return("1");
}
totleline = 0;
//读入文本文件时,一定要指定文件的编码格式.其中:default为文本文件本来的编码格式
//如果是简体中文的文本文件,也可以这样设置编码格式: System.Text.Encoding.GetEncode("gb2312")
//Encoding.GetEncode("gb2312")为简体中文编码格式,Encoding.GetEncode("big5")为繁体中文编码格式.
int r = 0;
totleline = 0;
if (FILE_TYPE == "TXT")
{
string BETWEEN_FLAG = dt_a308.Rows[0]["BETWEEN_FLAG"].ToString().ToUpper();
if (BETWEEN_FLAG == "" || BETWEEN_FLAG == null)
{
BETWEEN_FLAG = "0";
}
index__ = Chr(int.Parse(BETWEEN_FLAG));
StreamReader reader = new StreamReader(fileName, System.Text.Encoding.Default);
int i, j;
try
{
while (reader.Peek() >= 0)
{
strRecord = reader.ReadLine();
if (r == 0)
{
string check_head_ = check_head(strRecord, index__.ToCharArray(), CHECK_NAME, ref dt_a30801);
}
else
{
check_data(strRecord, index__.ToCharArray(), dt_a30801, dt_a30802);
}
r = r + 1;
}
}
catch (Exception ex)
{
return("文件:[" + fileName + "]导入失败,错误行是第" + (r + 1).ToString() + "行,原因是: " + ex.Message);
}
//相关资源的消除
finally
{
reader.Close();
}
}
else
{
int colcount = 250;
ExcelFunc xls = new ExcelFunc();
// xls.OpenReportTempalte(fileName);
index__ = "|";
try
{
string connectionStringFormat = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '{0}';Extended Properties=Excel 8.0";
connectionStringFormat = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source='{0}';" + "Extended ProPerties= \"Excel 12.0;HDR=Yes;\"";
DataSet myDs = new DataSet();
string connectString = string.Format(connectionStringFormat, fileName);
try
{
myDs.Tables.Clear();
myDs.Clear();
OleDbConnection cnnxls = new OleDbConnection(connectString);
cnnxls.Open();
OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
myDa.Fill(myDs, "c");
mydt = myDs.Tables[0];
cnnxls.Close();
}
catch (Exception ex)
{
return("文件:[" + fileName + "]导入失败,错误行是第" + r.ToString() + "行,原因是: " + ex.Message);
}
DataColumn dc = new DataColumn();
dc.ColumnName = "ERROR_MSG_";
mydt.Columns.Add(dc);
for (int i = 0; i < mydt.Columns.Count - 1; i++)
{
strRecord += mydt.Columns[i].ColumnName + index__;
}
string check_head_ = check_head(strRecord, index__.ToCharArray(), CHECK_NAME, ref dt_a30801);
r = 2;
for (int i = 0; i < mydt.Rows.Count; i++)
{
strRecord = "";
for (int j = 0; j < mydt.Columns.Count - 1; j++)
{
strRecord += mydt.Rows[i][j].ToString() + index__;
}
if (strRecord.Replace(index__, "") == "")
{
continue;
}
r = r + 1;
try
{
check_data(strRecord, index__.ToCharArray(), dt_a30801, dt_a30802);
}
catch (Exception ex)
{
mydt.Rows[i]["ERROR_MSG_"] = ex.Message;
errorcount = errorcount + 1;
}
}
dt_data = mydt;
}
catch (Exception ex)
{
return("文件:" + fileName + "导入失败,错误行是第" + r.ToString() + "行,原因是: " + ex.Message);
}
finally
{
// xls.CloseReportTemplate(fileName);
}
}
try
{
exec_update_sql();
}
catch (Exception ex)
{
return("文件:[" + fileName + "]导入失败,原因是: " + ex.Message);
}
if (errorcount == 0)
{
return("1");
}
else
{
return("导入文件出错！");
}
}
// Async function support
public static object Run(string callerFunctionName, object callerParameters, ExcelFunc asyncFunc)
{
Debug.Print("ExcelAsyncUtil.Run - {0} : {1}", callerFunctionName, callerParameters);
return AsyncObservableImpl.ProcessFunc(callerFunctionName, callerParameters, asyncFunc);
}
public string savefile(string a00201_key_)
{
DataTable dt_main = new DataTable();
dt_main = Fun.getDtBySql("Select t.* from A002_v01 t where a002_key='" + a00201_key_ + "'");
string getdatasql_ = HttpContext.Current.Session["QUERY_" + a00201_key_].ToString();
DataTable dt_data = new DataTable();
DataTable dt_show = new DataTable();
string sql = " ";
dt_show = Fun.getA013010101(a00201_key_);
dt_data = Fun.getDtBySql(getdatasql_);
string ls_xls = "";
string uploadfilepath = HttpContext.Current.Request.MapPath("..\\data\\Blank.xls");
string model_file = uploadfilepath;
string table_id__ = dt_main.Rows[0]["table_id"].ToString();
if (System.IO.File.Exists(uploadfilepath) == true)
{
string filename = Guid.NewGuid().ToString() + System.IO.Path.GetExtension(uploadfilepath); // Guid.NewGuid().ToString() + System.IO.Path.GetExtension(uploadfilepath);
uploadfilepath = "..\\temp";
string vpsh = uploadfilepath + "\\" + table_id__ + "\\" + filename;
string psh = HttpContext.Current.Request.MapPath(vpsh);
if (!System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(psh)))
{
System.IO.Directory.CreateDirectory(System.IO.Path.GetDirectoryName(psh));
}
ls_xls = Fun.GetIndexUrl() + "/temp/" + table_id__ + "/" + filename;
string outfilename = HttpContext.Current.Request.MapPath("..\\temp\\" + table_id__ + "\\" + filename);
string[] column_id_ = new string[250];
string[] col_text_ = new string[250];
string[] remove_col = new string[250];
int col_num = 0;
int remove_num = 0;
string[] Removecol = new string[250];
for (int j = 0; j < dt_show.Rows.Count; j++)
{
string col_text = dt_show.Rows[j]["col_text"].ToString();
string column_id = dt_show.Rows[j]["column_id"].ToString().ToUpper();
string col_visible = dt_show.Rows[j]["col_visible"].ToString();
if (col_visible != "1")
{
continue;
}
for (int c = 0; c < dt_data.Columns.Count; c++)
{
if (dt_data.Columns[c].ColumnName == column_id)
{
dt_data.Columns[c].Caption = col_text;
break;
}
}
column_id_[col_num] = column_id;
col_text_[col_num] = col_text;
col_num = col_num + 1;
}
for (int c = 0; c < dt_data.Columns.Count; c++)
{
string column_id = dt_data.Columns[c].ColumnName.ToUpper();
string col_visible = "0";
for (int j = 0; j < col_num; j++)
{
if (column_id == column_id_[j])
{
col_visible = "1";
break;
}
}
if (col_visible != "1")
{
remove_col[remove_num] = column_id;
remove_num = remove_num + 1;
continue;
}
}
for (int i = 0; i < remove_num; i++)
{
dt_data.Columns.Remove(remove_col[i]);
}
ExcelFunc exc = new ExcelFunc();
//exc.SaveExcel(dt_data, model_file, outfilename);
exc.WriteExeclByColumns(dt_data, column_id_, col_text_, model_file, outfilename);
}
return(ls_xls);
}
public ThreadPoolDelegateObservable(ExcelFunc func)
{
_func = func;
}
// Make a one-shot 'Observable' from the func
public static object ProcessFunc(string functionName, object parameters, ExcelFunc func)
{
return ProcessObservable(functionName, parameters,
delegate { return new ThreadPoolDelegateObservable(func); });
}
public static object GetBookTransactions(
[ExcelArgument(AllowReference = true, Name = "Book")] string bookId = "",
[ExcelArgument(AllowReference = true, Name = "Start date", Description = "Start date of the transaction date range filter.")] string beginDate = "",
[ExcelArgument(AllowReference = true, Name = "End date", Description = "End date of the transaction date range filter.")] string endDate = "")
{
var caller = AddinContext.Excel.Call(XlCall.xlfCaller);
ExcelFunc getData = () =>
{
if (!IsLoggedIn)
{
throw new ApplicationException($"Please login from the Argomi tab.");
}
if (AddinContext.AssumedAmid == 0)
{
throw new ApplicationException($"User {AddinContext.Username} does not have a valid asset manager relationship");
}
var bookIds = bookId.MatchAll() ? null : new List <string> {
bookId
};
var transactionStartDate = !beginDate.MatchAll() &&
DateTime.TryParse(beginDate, CultureInfo.CurrentCulture, DateTimeStyles.AssumeLocal, out DateTime beginDateParsed)
? (DateTime?)beginDateParsed
: null;
var transactionEndDate = !endDate.MatchAll() &&
DateTime.TryParse(endDate, CultureInfo.CurrentCulture, DateTimeStyles.AssumeLocal, out DateTime endDateParsed)
? (DateTime?)endDateParsed
: null;
var models = new List <EnrichedModel <Transaction, Asset> >();
var transactions = new List <Transaction>();
var pageNo = 1;
while (true)
{
var results = SearchTransactions(bookIds, transactionStartDate, transactionEndDate, pageNo++);
if (results == null)
{
break;
}
transactions.AddRange(results);
if (results.Count() < QueryConstants.DefaultPageSize)
{
break;
}
}
if (transactions != null && transactions.Count != 0)
{
var assetsApi = AddinContext.Container.Resolve <IAssetsInterface>();
var assetIds = transactions.Select(t => t.AssetId).Distinct().ToList();
var assets = assetsApi.SearchAssets(
assetManagerId: AddinContext.AssumedAmid,
assetIds: assetIds,
pageNo: 1,
pageSize: assetIds.Count).Result;
models = transactions.Select(t =>
new EnrichedModel <Transaction, Asset>(t, assets.FirstOrDefault(a => a.AssetId == t.AssetId))).ToList();
}
return(ExcelTable.Format(models, AddinContext.Container.Resolve <IFormatter <EnrichedModel <Transaction, Asset> > >(), caller));
};
var output = AddinContext.Excel.Run(UdfNames.BookTransactionSearch,
string.Join(",", bookId, beginDate, endDate),
getData);
return(output?.Equals(ExcelError.ExcelErrorNA) ?? true ? ExcelError.ExcelErrorGettingData : output);
}
public ThreadPoolDelegateObservable(ExcelFunc func)
{
_func = func;
}
public virtual object Run(string callerFunctionName, object callerParameters, ExcelFunc asyncFunc)
{
throw new NotImplementedException();
}
// Async function support
public static object Run(string callerFunctionName, object callerParameters, ExcelFunc asyncFunc)
{
Debug.Print("ExcelAsyncUtil.Run - {0} : {1}", callerFunctionName, callerParameters);
return(AsyncObservableImpl.ProcessFunc(callerFunctionName, callerParameters, asyncFunc));
}
public object Run(string udfName, object parameters, ExcelFunc func)
{
return(ExcelAsyncUtil.Run(udfName, parameters, func));
}
