/// <summary> /// 顯示選取條件 /// </summary> /// <returns></returns> private string ConditionText() { string lsText; /******************* * 顯示條件 *******************/ if (gbMarket.EditValue.Equals("rb_market_1")) { lsText = "盤後交易時段"; } else { lsText = "一般交易時段"; } if (!string.IsNullOrEmpty(_D500Xx.Sbrkno) || !string.IsNullOrEmpty(_D500Xx.Ebrkno)) { lsText = lsText + ",造市者:"; if (_D500Xx.Sbrkno == _D500Xx.Ebrkno) { lsText = lsText + _D500Xx.Sbrkno + " "; } else { lsText = lsText + _D500Xx.Sbrkno + "~" + _D500Xx.Ebrkno + " "; } } if (!string.IsNullOrEmpty(_D500Xx.ProdCategory)) { lsText = lsText + ",商品群組:" + _D500Xx.ProdCategory; } if (!string.IsNullOrEmpty(_D500Xx.ProdKindIdSto)) { lsText = lsText + ",2碼商品(個股):" + _D500Xx.ProdKindIdSto; } if (!string.IsNullOrEmpty(_D500Xx.ProdKindId)) { lsText = lsText + ",造市商品:" + _D500Xx.ProdKindId; } if (PbFunc.Left(lsText, 1) == ",") { lsText = PbFunc.Mid(lsText, 1, 50); } if (!string.IsNullOrEmpty(lsText)) { lsText = "報表條件:" + lsText; } return(lsText); }
/// <summary> /// 顯示選取條件 /// </summary> /// <returns></returns> private string ConditionText() { string lsText; /******************* * 顯示條件 *******************/ lsText = "一般交易時段"; if (!string.IsNullOrEmpty(_D500Xx.Sbrkno) || !string.IsNullOrEmpty(_D500Xx.Ebrkno)) { lsText = lsText + ",造市者:"; if (_D500Xx.Sbrkno == _D500Xx.Ebrkno) { lsText = lsText + _D500Xx.Sbrkno + " "; } else { lsText = lsText + _D500Xx.Sbrkno + "~" + _D500Xx.Ebrkno + " "; } } if (!string.IsNullOrEmpty(_D500Xx.ProdCategory)) { lsText = lsText + ",商品群組:" + _D500Xx.ProdCategory; } if (!string.IsNullOrEmpty(_D500Xx.ProdKindIdSto)) { lsText = lsText + ",2碼商品(個股):" + _D500Xx.ProdKindIdSto; } if (!string.IsNullOrEmpty(_D500Xx.ProdKindId)) { lsText = lsText + ",造市商品:" + _D500Xx.ProdKindId; } if (PbFunc.Left(lsText, 1) == ",") { lsText = PbFunc.Mid(lsText, 1, 50); } if (!string.IsNullOrEmpty(lsText)) { lsText = "報表條件:" + lsText; } lsText = lsText == "" ? $"報表條件:連續{SleCMth.Text}個月不符造市規定" : lsText + $",連續{SleCMth.Text}個月不符造市規定"; return(lsText); }
/// <summary> /// wf_30333() /// 30398,30399,30393相同 /// </summary> /// <param name="IsKindID">商品代號</param> /// <param name="SheetName">工作表</param> /// <param name="RowIndex">Excel的Row位置</param> /// <param name="RowTotal">Excel的Column預留數</param> /// <returns></returns> public string Wf30333(string IsKindID = "GTF", string SheetName = "data_30398abc", int RowIndex = 3, int RowTotal = 12) { try { //切換Sheet Worksheet worksheet = _workbook.Worksheets[SheetName]; //總列數 int sumRowIndex = RowTotal + RowIndex + 1; //小計行數 int addRowCount = 0; //總計寫入的行數 worksheet.Rows[sumRowIndex][1 - 1].Value = $"{PbFunc.Left(_emMonthText, 4).AsInt() - 1911}小計"; string lsYMD = ""; //讀取資料 DataTable dt = daoAM2.ListAM2(IsKindID, $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", "")); //寫入資料 foreach (DataRow row in dt.Rows) { if (lsYMD != row["AM2_YMD"].AsString()) { RowIndex = RowIndex + 1; lsYMD = row["AM2_YMD"].AsString(); //li_month_cnt = li_month_cnt + 1; addRowCount++; worksheet.Rows[RowIndex][1 - 1].Value = $"{PbFunc.Left(lsYMD, 4).AsInt() - 1911}/{PbFunc.Right(lsYMD, 2)}"; } //判斷欄位 int columnIndex = IDFGtype(row); worksheet.Rows[RowIndex][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal();//買 or 賣 } //刪除空白列 if (RowTotal > addRowCount) { worksheet.Rows.Hide(RowIndex + 1, RowIndex + (RowTotal - addRowCount)); //隱藏代替刪除,這樣就不用再重新抓取圖表範圍 worksheet.ScrollTo(0, 0); //直接滾動到最上面,不然看起來很像少行數 } } catch (Exception ex) { #if DEBUG throw new Exception($"Wf30333:" + ex.Message); #else throw ex; #endif } return(MessageDisplay.MSG_OK); }
/// <summary> /// wf_30541 /// </summary> /// <param name="RowIndex">Excel的Row位置</param> /// <param name="RowTotal">Excel的Column預留數</param> /// <returns></returns> public string Wf30541(int RowIndex = 6, string RptName = "國內股價指數選擇權交易概況表") { Workbook workbook = new Workbook(); try { //切換Sheet workbook.LoadDocument(_lsFile); Worksheet worksheet = workbook.Worksheets[0]; worksheet.Range["A1"].Select(); //總列數,隱藏於A5 int rowTol = RowIndex + worksheet.Cells["A5"].Value.AsInt(); //起始年份,隱藏於B5 string firstYear = worksheet.Cells["B5"].Value.AsString(); /****************** * 讀取資料 * 分三段: * 1.年 * 2.當年1月至當月合計 * 3.當年1月至當月明細 ******************/ DataTable dt = new D30540().List30541(firstYear, PbFunc.Left(_emMonthText, 4), $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", "")); if (dt.Rows.Count <= 0) { return($"{firstYear}~{PbFunc.Left(_emMonthText, 4)},{PbFunc.Left(_emMonthText, 4)}01~{_emMonthText.Replace("/", "")},30541-{RptName},無任何資料!"); } /* 成交量 & OI */ DataTable dtAI2 = new D30540().List30541AI2(firstYear, PbFunc.Left(_emMonthText, 4), $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", "")); if (dtAI2.Rows.Count <= 0) { return($"{firstYear}~{PbFunc.Left(_emMonthText, 4)},{PbFunc.Left(_emMonthText, 4)}01~{_emMonthText.Replace("/", "")},30541-{RptName},無任何資料!"); } //寫入內容 string lsYMD = ""; int rowEndIndex = 0; string endYMD = dt.AsEnumerable().LastOrDefault()["AM2_YMD"].AsString(); foreach (DataRow row in dt.Rows) { if (lsYMD != row["AM2_YMD"].AsString()) { lsYMD = row["AM2_YMD"].AsString(); RowIndex = RowIndex + 1; /* 最後一列時 */ if (lsYMD == endYMD) { rowEndIndex = RowIndex; RowIndex = rowTol; } /* 年度資料 */ if (lsYMD.Length == 4) { worksheet.Rows[RowIndex][1 - 1].Value = PbFunc.Left(lsYMD, 4).AsInt(); } else { worksheet.Rows[RowIndex][1 - 1].Value = PbFunc.f_get_month_eng_name(PbFunc.Right(lsYMD, 2).AsInt(), "1"); }//if (lsYMD.Length==4) /* 成交量 & OI */ //ai2_pc_code=C OImethod(RowIndex, worksheet, dtAI2, lsYMD, "C"); //ai2_pc_code=P OImethod(RowIndex, worksheet, dtAI2, lsYMD, "P"); }//if (lsYMD != row["AM2_YMD"].AsString()) //判斷欄位 int columnIndex = IDFGtype(row); if (row["AM2_YMD"].AsString() != endYMD) { worksheet.Rows[RowIndex][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal(); } else { worksheet.Rows[rowTol][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal(); } }//foreach (DataRow row in dt.Rows) //刪除空白列 if (rowTol > rowEndIndex) { worksheet.Range[$"{rowEndIndex + 1}:{rowTol + 1 - 1}"].Delete(DeleteMode.EntireRow); worksheet.ScrollTo(0, 0);//直接滾動到最上面,不然看起來很像少行數 } } catch (Exception ex) { #if DEBUG throw new Exception($"wf30541:" + ex.Message); #else throw ex; #endif } finally { workbook.SaveDocument(_lsFile); } return(MessageDisplay.MSG_OK); }
protected override ResultStatus Export() { base.Export(); string ls_rpt_name = "造市者報表"; string ls_rpt_id = _ProgramID; StartExport(ls_rpt_id, ls_rpt_name); StartRetrieve("0000000", "Z999999"); _D500Xx.Filename = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, _ProgramID + "_" + DateTime.Now.ToString("yyyy.MM.dd") + "-" + DateTime.Now.ToString("HH.mm.ss") + ".csv"); try { /* 商品群組 */ string isKey = dwProdCt.SelectedText.Trim() + "%"; if (string.IsNullOrEmpty(isKey) || dwProdCt.Enabled == false) { isKey = "%"; } /* 商品 */ string kindID2 = dwProdKdSto.SelectedText.Trim() + "%"; if (string.IsNullOrEmpty(kindID2) || dwProdKdSto.Enabled == false) { kindID2 = "%"; } /******************* * 統計子類別 *******************/ string prodSubtype = "%"; string sumSubtype = ""; if (gbGroup.EditValue.Equals("rb_gparam")) { sumSubtype = "3"; } else if (gbGroup.EditValue.Equals("rb_s")) { sumSubtype = "S"; prodSubtype = "S"; } else if (gbGroup.EditValue.Equals("rb_gkind2")) { sumSubtype = "4"; } /******************* * 交易時段 *******************/ int marketCode = 0; if (gbMarket.EditValue.Equals("rb_market_1")) { marketCode = 1; } if (gbMarket.EditValue.Equals("rb_market_0")) { marketCode = 0; } /******************* * 報表內容 *******************/ string detailType = ""; if (gbDetial.EditValue.Equals("rb_gdate")) { detailType = "D"; } if (gbDetial.EditValue.Equals("rb_gnodate")) { detailType = "N"; } /******************* * 顯示條件 *******************/ string lsText = ""; if (gbMarket.EditValue.Equals("rb_market_1")) { lsText = "盤後交易時段"; } else { lsText = "一般交易時段"; } if (!string.IsNullOrEmpty(_D500Xx.Sdate)) { lsText = lsText + "(" + _D500Xx.Sdate; } if (!string.IsNullOrEmpty(_D500Xx.Edate)) { lsText = lsText + "~" + _D500Xx.Edate + ")"; } if (_D500Xx.Sbrkno != "%" || _D500Xx.Ebrkno != "%") { lsText = lsText + ",造市者:"; if (_D500Xx.Sbrkno == _D500Xx.Ebrkno) { lsText = lsText + _D500Xx.Sbrkno + " "; } else { lsText = lsText + _D500Xx.Sbrkno + "~" + _D500Xx.Ebrkno + " "; } } if (isKey != "%") { lsText = lsText + ",商品群組:" + isKey; } if (kindID2 != "%") { lsText = lsText + ",2碼商品(個股):" + kindID2; } if (PbFunc.Left(lsText, 1) == ",") { lsText = PbFunc.Mid(lsText, 1, 50); } if (lsText != "") { lsText = "報表條件:" + lsText; } /****************** * 讀取資料 ******************/ /* 報表內容 */ _Data = dao50031.ListD50031 (marketCode, sumSubtype, detailType, _D500Xx.Sdate, _D500Xx.Edate, _D500Xx.Sbrkno, _D500Xx.Ebrkno, isKey, prodSubtype, kindID2, lsText); if (_Data.Rows.Count <= 0) { MessageDisplay.Info(MessageDisplay.MSG_NO_DATA); return(ResultStatus.Fail); } /****************** * 開啟檔案 ******************/ Workbook workbook = new Workbook(); //判斷檔案是否存在,不存在就開一個新檔案 if (!File.Exists(_D500Xx.Filename)) { File.Create(_D500Xx.Filename).Close(); } workbook.LoadDocument(_D500Xx.Filename); /****************** * 切換Sheet ******************/ Worksheet worksheet = workbook.Worksheets[0]; workbook.Options.Export.Csv.WritePreamble = true; //不加這段中文會是亂碼 //將DataTable中的數據導入Excel中 worksheet.Import(_Data, false, 0, 0); workbook.SaveDocument(_D500Xx.Filename, DocumentFormat.Csv); } catch (Exception ex) { WfRunError(); WriteLog(ex); } finally { EndExport(); } return(ResultStatus.Success); }
//protected override ResultStatus Run(PokeBall args) //{ // this.BeginInvoke(new MethodInvoker(() => { // args.GridControlMain = gcMain; // args.GridControlSecond = gcLogsp; // args.OcfDate = txtOcfDate.DateTimeValue; // args.OcfType = OCF_TYPE; // })); // ResultStatus result = base.RunAsync(args); // return result; //} /// <summary> /// 1系列功能使用 /// </summary> /// <param name="args"></param> /// <returns></returns> protected override ResultStatus Run(PokeBall args) { DateTime OcfDate = txtOcfDate.DateTimeValue; this.Invoke(new MethodInvoker(() => { FormWait formWait = new FormWait(); SplashScreenManager.ShowForm(this, typeof(FormWait), true, true); //SplashScreenManager.ShowForm(this , typeof(FormWait) , true , true , SplashFormStartPosition.Manual , pointWait , ParentFormState.Locked); })); GridView gv = gvMain; DataTable dtLOGSPForRuned = servicePrefix1.ListLogspForRunned(OcfDate, _ProgramID, OCF_TYPE); DataView dvLOGSPForRuned = new DataView(dtLOGSPForRuned); servicePrefix1.SetTXF1(" ", _ProgramID); for (int i = 0; i < gv.RowCount; i++) { string TXF_SERVER = gv.GetRowCellValue(i, "TXF_SERVER").AsString(); string TXF_DB = gv.GetRowCellValue(i, "TXF_DB").AsString(); string TXF_TXN_ID = gv.GetRowCellValue(i, "TXF_TXN_ID").AsString(); int TXF_SEQ_NO = gv.GetRowCellValue(i, "TXF_SEQ_NO").AsInt(); string TXF_TYPE = gv.GetRowCellValue(i, "TXF_TYPE").AsString(); string TXF_TID = gv.GetRowCellValue(i, "TXF_TID").AsString(); string TXF_TID_NAME = gv.GetRowCellValue(i, "TXF_TID_NAME").AsString(); string TXF_DESC = gv.GetRowCellValue(i, "TXF_DESC").AsString(); string TXF_DEFAULT = gv.GetRowCellValue(i, "TXF_DEFAULT").AsString(); string TXF_REDO = gv.GetRowCellValue(i, "TXF_REDO").AsString(); string TXF_ARG = gv.GetRowCellValue(i, "TXF_ARG").AsString(); string TXF_PERIOD = gv.GetRowCellValue(i, "TXF_PERIOD").AsString(); string TXF_SERVICE = gv.GetRowCellValue(i, "TXF_SERVICE").AsString(); string TXF_FOLDER = gv.GetRowCellValue(i, "TXF_FOLDER").AsString(); string TXF_AP_NAME = gv.GetRowCellValue(i, "TXF_AP_NAME").AsString(); args.TXF_TID = TXF_TID; args.TXF_TID_NAME = TXF_TID_NAME; if (TXF_DEFAULT == "1") { DateTime LOGSP_DATE = OcfDate; string LOGSP_TXN_ID = _ProgramID; int LOGSP_SEQ_NO = TXF_SEQ_NO; string LOGSP_TID = TXF_DESC; string LOGSP_TID_NAME = TXF_TID_NAME; DateTime LOGSP_BEGIN_TIME = new DateTime(); DateTime LOGSP_END_TIME = new DateTime(); string LOGSP_MSG = ""; //判斷是否可重覆執行 if (TXF_REDO == "N") { dvLOGSPForRuned.RowFilter = "LOGSP_TID='" + LOGSP_TID + "' AND NOT ISNULL(LOGSP_BEGIN_TIME)"; if (dvLOGSPForRuned.Count != 0) { if (MessageDisplay.Choose(TXF_TID + " ★★★曾經執行過且不可重覆執行,是否強迫繼續執行 ?") == DialogResult.No) { return(ResultStatus.Fail); } } } #region 開始執行 LOGSP_BEGIN_TIME = DateTime.Now; string nextYmd = PbFunc.f_ocf_date(2, _DB_TYPE); if (!string.IsNullOrEmpty(TXF_PERIOD)) { switch (TXF_PERIOD) { case "M": //月底執行 if (OcfDate.ToString("yyyyMM") == PbFunc.Left(nextYmd, 6)) { LOGSP_MSG = "完成! (今日非月底,不需執行)"; this.Invoke(new MethodInvoker(() => { gv.SetRowCellValue(i, "TXF_DEFAULT", 0); })); } break; case "W": //週最後一天執行 if (Convert.ToInt32(OcfDate.DayOfWeek) < Convert.ToInt32(nextYmd.AsDateTime("yyyyMMdd").DayOfWeek)) { LOGSP_MSG = "完成! (今日非本週最後1天,不需執行)"; this.Invoke(new MethodInvoker(() => { gv.SetRowCellValue(i, "TXF_DEFAULT", 0); })); } break; case "Y": //年底執行 if (OcfDate.ToString("yyyy") == PbFunc.Left(nextYmd, 4)) { LOGSP_MSG = "完成! (今日非本年度最後1日,不需執行)"; this.Invoke(new MethodInvoker(() => { gv.SetRowCellValue(i, "TXF_DEFAULT", 0); })); } break; } LOGSP_END_TIME = DateTime.Now; servicePrefix1.SaveLogsp(LOGSP_DATE, LOGSP_TXN_ID, LOGSP_SEQ_NO, LOGSP_TID, LOGSP_TID_NAME, LOGSP_BEGIN_TIME, LOGSP_END_TIME, LOGSP_MSG, OCF_TYPE); continue; } //開始前執行特別的Function string rtnText = RunBeforeEveryItem(args); if (!string.IsNullOrEmpty(rtnText)) { if (PbFunc.Left(rtnText, 4) == "不需執行") { LOGSP_MSG = "完成! (" + rtnText + ")"; gv.SetRowCellValue(i, "ERR_MSG", LOGSP_MSG); gv.SetRowCellValue(i, "TXF_DEFAULT", 0); LOGSP_END_TIME = DateTime.Now; servicePrefix1.SaveLogsp(LOGSP_DATE, LOGSP_TXN_ID, LOGSP_SEQ_NO, LOGSP_TID, LOGSP_TID_NAME, LOGSP_BEGIN_TIME, LOGSP_END_TIME, LOGSP_MSG, OCF_TYPE); } else { if (MessageDisplay.Choose($"{rtnText}是否強迫繼續執行?", MessageBoxDefaultButton.Button2).AsInt() == 2) { gv.SetRowCellValue(i, "ERR_MSG", rtnText); continue; } } } //記錄正在執行 servicePrefix1.SetTXF1(TXF_TID, _ProgramID); servicePrefix1.SaveLogs(LOGSP_DATE, TXF_TID, DateTime.Now, GlobalInfo.USER_ID, "開始執行"); ResultData resultData = new ResultData(); string fileName = ""; switch (TXF_TYPE) { //Informatica case "I": fileName = $@"{GlobalInfo.DEFAULT_BATCH_ErrSP_DIRECTORY_PATH}\{TXF_SERVER}_{TXF_TXN_ID}_{TXF_SEQ_NO}_infor"; resultData = serviceCommon.ExecuteInfoWorkFlow(TXF_TID, UserProgInfo, TXF_FOLDER, TXF_SERVICE, TXF_AP_NAME, fileName); break; //SP case "S": List <DbParameterEx> listParams = null; // 如果這個SP有參數的話 if (TXF_ARG == "Y") { DataTable dtTXFPARM = serviceCommon.ListTXFPARM(TXF_SERVER, TXF_DB, TXF_TXN_ID, TXF_TID); if (dtTXFPARM.Rows.Count > 0) { listParams = new List <DbParameterEx>(); } foreach (DataRow row in dtTXFPARM.Rows) { string TXFPARM_ARG = row["TXFPARM_ARG"].AsString(); string TXFPARM_ARG_TYPE = row["TXFPARM_ARG_TYPE"].AsString(); string TXFPARM_DEFAULT = row["TXFPARM_DEFAULT"].AsString(); DbParameterEx paramEx; switch (TXFPARM_ARG) { case "": paramEx = new DbParameterEx("", TXFPARM_DEFAULT); listParams.Add(paramEx); break; case "em_ymd": paramEx = new DbParameterEx("", OcfDate.ToString("yyyyMMdd")); listParams.Add(paramEx); break; case "em_ym": paramEx = new DbParameterEx("", OcfDate.ToString("yyyyMM")); listParams.Add(paramEx); break; case "em_date": paramEx = new DbParameterEx(); paramEx.DbType = DbTypeEx.Date; paramEx.Name = ""; paramEx.Value = OcfDate; listParams.Add(paramEx); break; default: break; } } } ConnectionInfo connectionInfo = SettingDragons.Instance.GetConnectionInfo(TXF_DB); try { resultData = serviceCommon.ExecuteStoredProcedure(connectionInfo, string.Format("{0}.{1}", TXF_DB, TXF_TID), listParams, true); } catch (Exception ex) { resultData.Status = ResultStatus.Fail; string msg = fileName = $@"{GlobalInfo.DEFAULT_BATCH_ErrSP_DIRECTORY_PATH}\{TXF_SERVER}_{TXF_TXN_ID}_{TXF_SEQ_NO}.err"; System.IO.File.WriteAllText(fileName, ex.Message); resultData.returnString = $"請通知「{TXF_AP_NAME}」 作業執行失敗!\n{ex.Message}"; } break; //視窗功能 case "W": this.Invoke(new MethodInvoker(() => { resultData = ExecuteForm(args); })); break; default: break; } LOGSP_END_TIME = DateTime.Now; if (resultData.Status == ResultStatus.Success) { LOGSP_MSG = "執行正常完成!"; } else { LOGSP_MSG = "作業執行失敗!"; servicePrefix1.SaveLogsp(LOGSP_DATE, LOGSP_TXN_ID, LOGSP_SEQ_NO, LOGSP_TID, LOGSP_TID_NAME, LOGSP_BEGIN_TIME, LOGSP_END_TIME, LOGSP_MSG, OCF_TYPE); //MessageDisplay.Error("序號" + LOGSP_SEQ_NO + "的" + LOGSP_TID + "," + LOGSP_MSG); MessageDisplay.Error(resultData.returnString); this.Invoke(new MethodInvoker(() => { SplashScreenManager.CloseForm(); gv.SetRowCellValue(i, "ERR_MSG", LOGSP_MSG); })); return(ResultStatus.Fail); } this.Invoke(new MethodInvoker(() => { gv.SetRowCellValue(i, "ERR_MSG", LOGSP_MSG); })); servicePrefix1.SaveLogsp(LOGSP_DATE, LOGSP_TXN_ID, LOGSP_SEQ_NO, LOGSP_TID, LOGSP_TID_NAME, LOGSP_BEGIN_TIME, LOGSP_END_TIME, LOGSP_MSG, OCF_TYPE); servicePrefix1.SaveLogs(LOGSP_DATE, TXF_TID, DateTime.Now, GlobalInfo.USER_ID, "執行完畢"); #endregion 開始執行 #region 執行特別的程式 this.Invoke(new MethodInvoker(() => { gv.SetRowCellValue(i, "TXF_DEFAULT", 0); RunAfterEveryItem(args); })); #endregion 執行特別的程式 //流程時間控制 DataTable dtJRF = servicePrefix1.ListJrf(_ProgramID, TXF_TID); if (dtJRF.Rows.Count > 0) { string JRF_DO_TXN_ID = dtJRF.Rows[0]["JRF_DO_TXN_ID"].AsString(); string JRF_DO_JOB_TYPE = dtJRF.Rows[0]["JRF_DO_TXN_ID"].AsString(); string JRF_DO_SEQ_NO = dtJRF.Rows[0]["JRF_DO_TXN_ID"].AsString(); string JRF_SW_CODE = dtJRF.Rows[0]["JRF_DO_TXN_ID"].AsString(); servicePrefix1.UpdateJsw(JRF_DO_TXN_ID, JRF_DO_JOB_TYPE, JRF_DO_SEQ_NO, JRF_SW_CODE, OcfDate, DateTime.Now, GlobalInfo.USER_ID); } } else { // 沒勾選項目的話清空狀態 this.Invoke(new MethodInvoker(() => { gv.SetRowCellValue(i, "ERR_MSG", ""); })); } if (i == gv.RowCount - 1 && OCF_TYPE == "D") { servicePrefix1.setCIOCF(); } } //全部結束 servicePrefix1.SetTXF1(" ", _ProgramID); this.Invoke(new MethodInvoker(() => { SplashScreenManager.CloseForm(); })); return(ResultStatus.Success); }
/// <summary> /// 寫入 Data_30333.30334 sheet /// </summary> /// <returns></returns> public string Wf30333() { Workbook workbook = new Workbook(); try { string SheetName = "Data_30333.30334"; string lsKindID = "GBF"; //切換Sheet workbook.LoadDocument(_lsFile); Worksheet worksheet = workbook.Worksheets[SheetName]; DateTime ldtYMD = new DateTime(1900, 1, 1); //總列數 int rowIndex = 3; //int li_month_cnt=0; int RowTotal = 12; //Excel的Column預留空白行數12 int sumRowIndex = RowTotal + rowIndex + 1; //小計行數 int addRowCount = 0; //總計寫入的行數 worksheet.Rows[sumRowIndex][1 - 1].Value = $"{PbFunc.Left(_emMonthText, 4).AsInt() - 1911}小計"; string lsYMD = ""; //讀取資料 DataTable dt = new AM2().ListAM2(lsKindID, $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", ""));//月份 foreach (DataRow row in dt.Rows) { if (lsYMD != row["AM2_YMD"].AsString()) { rowIndex = rowIndex + 1; lsYMD = row["AM2_YMD"].AsString(); //li_month_cnt = li_month_cnt + 1; addRowCount++; //月份 worksheet.Rows[rowIndex][1 - 1].Value = $"{PbFunc.Left(lsYMD, 4).AsInt() - 1911}/{PbFunc.Right(lsYMD, 2)}"; } //判斷買賣欄位位置 int columnIndex = 0; switch (row["AM2_IDFG_TYPE"].AsString()) { case "1": columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 2 : 3) - 1; break; case "2": columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 4 : 5) - 1; break; case "3": columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 6 : 7) - 1; break; case "5": columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 8 : 9) - 1; break; case "6": columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 10 : 11) - 1; break; case "8": columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 12 : 13) - 1; break; case "7": columnIndex = (row["AM2_BS_CODE"].AsString() == "B" ? 14 : 15) - 1; break; } //columnIndex指定要寫入的買賣欄位 worksheet.Rows[rowIndex][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal(); } //刪除空白列 if (RowTotal > addRowCount) { worksheet.Rows.Remove(rowIndex + 1, RowTotal - addRowCount); } worksheet.ScrollTo(0, 0); } catch (Exception ex) { throw ex; } finally { workbook.SaveDocument(_lsFile); } return(MessageDisplay.MSG_OK); }
/// <summary> /// wf_30371() /// </summary> /// <param name="RowIndex">Excel的Row位置</param> /// <param name="SheetName">工作表</param> /// <param name="RptName">作業名稱</param> /// <returns></returns> public string Wf30371(int RowIndex = 3, string SheetName = "30371", string RptName = "年度期間法人機構期貨交易量統計表") { Workbook workbook = new Workbook(); try { //切換Sheet workbook.LoadDocument(_lsFile); Worksheet worksheet = workbook.Worksheets[SheetName]; //總列數,隱藏於A1 int rowTol = RowIndex + worksheet.Cells["A1"].Value.AsInt(); int rowYtolIndex = rowTol; //起始年份,隱藏於B1 string firstYear = worksheet.Cells["B1"].Value.AsString(); /****************** * 讀取資料 * 分三段: * 1.年 * 2.當年1月至當月明細 * 3.當年1月至當月合計 ******************/ DataTable dt = dao30370.Get30371Data(firstYear, PbFunc.Left(_emMonthText, 4), $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", "")); if (dt.Rows.Count <= 0) { return($"{firstYear}~{PbFunc.Left(_emMonthText, 4)},{PbFunc.Left(_emMonthText, 4)}01~{_emMonthText.Replace("/", "")},{SheetName}-{RptName},無任何資料!"); } string endYMD = dt.AsEnumerable().LastOrDefault()["AM2_YMD"].AsString(); /* 不等於起始年,則從第2個年開始 */ if (dt.Rows[0]["AM2_YMD"].AsString() != firstYear) { //刪除兩列 worksheet.Range[$"{RowIndex + 1}:{RowIndex + 2}"].Delete(); rowYtolIndex = rowYtolIndex - 2; } int monthCnt = 0; string lsYMD = ""; foreach (DataRow row in dt.Rows) { if (lsYMD != row["AM2_YMD"].AsString()) { if (lsYMD.Length == 4) { /* 年與年中間跳2 row */ RowIndex = RowIndex + 1; /* 年部份結束,則把"年"空白列刪除 */ if (row["AM2_YMD"].AsString().Length != 4) { try { //worksheet.Rows.Hide(RowIndex + 1, rowYtolIndex); worksheet.Range[$"{RowIndex + 2}:{rowYtolIndex + 1}"].Delete(); } catch (Exception ex) { return(ex.Message + Environment.NewLine + "30370template[B1]每新增一個年份[A1]要加3"); } rowTol = RowIndex + 1 + 12;//12個月份 } } RowIndex = RowIndex + 1; lsYMD = row["AM2_YMD"].AsString(); if (lsYMD.Length == 4) { if (lsYMD != endYMD) { worksheet.Rows[RowIndex][1 - 1].Value = $"{PbFunc.Left(lsYMD, 4).AsInt() - 1911}小計"; } else { worksheet.Rows[rowTol][1 - 1].Value = $"{PbFunc.Left(lsYMD, 4).AsInt() - 1911}小計";//這在倒數第二行 } } else { monthCnt++;//li_month_cnt = li_month_cnt + 1 worksheet.Rows[RowIndex][1 - 1].Value = $"{PbFunc.Left(lsYMD, 4).AsInt() - 1911}/{PbFunc.Right(lsYMD, 2)}"; } }//if (lsYMD != row["AM2_YMD"].AsString()) //判斷欄位 int columnIndex = IDFGtype(row); if (row["AM2_YMD"].AsString() != endYMD) { worksheet.Rows[RowIndex][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal(); } else { worksheet.Rows[rowTol][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal();//這在倒數第二行 } }//foreach (DataRow row in dt.Rows) //刪除剩餘空白列 try { if (rowTol > monthCnt) { worksheet.Range[$"{RowIndex + 1}:{RowIndex + 12 - monthCnt}"].Delete(); } } catch (Exception ex) { throw ex; } } catch (Exception ex) { throw ex; } finally { workbook.SaveDocument(_lsFile); } return(MessageDisplay.MSG_OK); }
/// <summary> /// wf_30521 /// </summary> /// <param name="RowIndex">Excel的Row位置</param> /// <param name="RowTotal">Excel的Column預留數</param> /// <returns></returns> public string Wf30521(int RowIndex = 4, string RptName = "國內期貨交易概況表") { Workbook workbook = new Workbook(); try { //切換Sheet workbook.LoadDocument(_lsFile); Worksheet worksheet = workbook.Worksheets[0];//30520 //起始年份,隱藏於B3 string firstYear = worksheet.Cells["B3"].Value.AsString(); /****************** * 讀取資料 * 分三段: * 1.年 * 2.當年1月至當月合計 * 3.當年1月至當月明細 ******************/ DataTable dt = dao30520.List30521(firstYear, PbFunc.Left(_emMonthText, 4), $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", "")); if (dt.Rows.Count <= 0) { return($"{firstYear}~{PbFunc.Left(_emMonthText, 4)},{PbFunc.Left(_emMonthText, 4)}01~{_emMonthText.Replace("/", "")},30521-{RptName},無任何資料!"); } /* 成交量 & OI */ DataTable dtAI2 = dao30520.List30521AI2(firstYear, PbFunc.Left(_emMonthText, 4), $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", "")); if (dtAI2.Rows.Count <= 0) { return($"{firstYear}~{PbFunc.Left(_emMonthText, 4)},{PbFunc.Left(_emMonthText, 4)}01~{_emMonthText.Replace("/", "")},30521-{RptName},無任何資料!"); } //寫入內容 string lsYMD = ""; int addCount = 0;//計算新增row的數量 foreach (DataRow row in dt.Rows) { if (lsYMD != row["AM2_YMD"].AsString()) { lsYMD = row["AM2_YMD"].AsString(); RowIndex = RowIndex + 1; addCount++; /* 年度資料 */ if (lsYMD.Length == 4) { worksheet.Cells[$"A{RowIndex + 1}"].Value = PbFunc.Left(lsYMD, 4).AsInt(); } else { worksheet.Cells[$"A{RowIndex + 1}"].Value = PbFunc.f_get_month_eng_name(PbFunc.Right(lsYMD, 2).AsInt(), "1"); }//if (lsYMD.Length==4) /* 成交量 & OI */ int foundIndex = dtAI2.Rows.IndexOf(dtAI2.Select($@"ai2_ymd ='{lsYMD}'").FirstOrDefault()); if (foundIndex > -1) { worksheet.Cells[$"F{RowIndex + 1}"].SetValue(dtAI2.Rows[foundIndex]["AI2_M_QNTY"]); //成交契約總數 Total Trading Volume worksheet.Cells[$"G{RowIndex + 1}"].SetValue(dtAI2.Rows[foundIndex]["AI2_OI"]); //未沖銷契約數 Open Interest } }//if (lsYMD != row["AM2_YMD"].AsString()) //判斷欄位 int columnIndex = IDFGtype(row); worksheet.Rows[RowIndex][columnIndex].SetValue(row["AM2_M_QNTY"]);//買進 Long or 賣出 Short }//foreach (DataRow row in dt.Rows) //刪除空白列 //總列數,隱藏於A3 int rowTotal = worksheet.Cells["A3"].Value.AsInt(); if (rowTotal > addCount) { worksheet.Rows.Remove(RowIndex + 1, rowTotal - addCount); //表格最後一行設回原本的框線 int lineIndex = RowIndex + 1; worksheet.Range[$"A{lineIndex}:G{lineIndex}"].Borders.BottomBorder.LineStyle = BorderLineStyle.Thin; worksheet.Range[$"A{lineIndex}:G{lineIndex}"].Borders.BottomBorder.Color = Color.Black; worksheet.ScrollTo(0, 0);//直接滾動到最上面,不然看起來很像少行數 } } catch (Exception ex) { #if DEBUG throw new Exception($"wf30520:" + ex.Message); #else throw ex; #endif } finally { workbook.SaveDocument(_lsFile); } return(MessageDisplay.MSG_OK); }
/// <summary> /// wf_30550 /// </summary> /// <param name="RowIndex">Excel的Row位置</param> /// <param name="RowTotal">Excel的Column預留數</param> /// <returns></returns> public string Wf30550(int RowIndex = 4, string SheetName = "30550", string RptName = "國內股價指數選擇權交易概況明細表") { Workbook workbook = new Workbook(); try { //切換Sheet workbook.LoadDocument(_lsFile); Worksheet worksheet = workbook.Worksheets[SheetName]; //總列數,隱藏於A3 int rowTotal = RowIndex + worksheet.Cells["A3"].Value.AsInt(); //起始年份,隱藏於B3 string firstYear = worksheet.Cells["B3"].Value.AsString(); /****************** * 讀取資料 * 分三段: * 1.年 * 2.當年1月至當月合計 * 3.當年1月至當月明細 ******************/ DataTable dt = new D30550().GetData(firstYear, PbFunc.Left(_emMonthText, 4), $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", "")); if (dt.Rows.Count <= 0) { return($"{firstYear}~{PbFunc.Left(_emMonthText, 4)},{PbFunc.Left(_emMonthText, 4)}01~{_emMonthText.Replace("/", "")},{SheetName}-{RptName},無任何資料!"); } string lsYMD = ""; string endYMD = dt.AsEnumerable().LastOrDefault()["AM2_YMD"].AsString(); foreach (DataRow row in dt.Rows) { if (lsYMD != row["AM2_YMD"].AsString()) { lsYMD = row["AM2_YMD"].AsString(); RowIndex = RowIndex + 1; if (lsYMD.Length == 4) { worksheet.Rows[RowIndex][1 - 1].Value = PbFunc.Left(lsYMD, 4).AsInt(); } else { string getMonthEngName = PbFunc.f_get_month_eng_name(PbFunc.Right(lsYMD, 2).AsInt(), "1"); if (lsYMD != endYMD) { worksheet.Rows[RowIndex][1 - 1].Value = getMonthEngName; } else { worksheet.Rows[rowTotal][1 - 1].Value = getMonthEngName; } } //if (lsYMD.Length==4) } //if (lsYMD != row["AM2_YMD"].AsString()) //判斷欄位 int columnIndex = IDFGtype(row); if (row["AM2_YMD"].AsString() != endYMD) { worksheet.Rows[RowIndex][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal(); } else { worksheet.Rows[rowTotal][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal(); } } //刪除空白列 if (rowTotal > RowIndex) { worksheet.Range[$"{RowIndex+1}:{rowTotal+1-1}"].Delete(DeleteMode.EntireRow); worksheet.ScrollTo(0, 0);//直接滾動到最上面,不然看起來很像少行數 } } catch (Exception ex) { throw ex; } finally { workbook.SaveDocument(_lsFile); } return(MessageDisplay.MSG_OK); }
/// <summary> /// wf_30581 /// </summary> /// <param name="RowIndex">Excel的Row位置</param> /// <param name="RowTotal">Excel的Column預留數</param> /// <returns></returns> public string Wf30581(int RowIndex = 6, string RptName = "國內股票期貨及黃金期貨交易概況表") { Workbook workbook = new Workbook(); try { //切換Sheet workbook.LoadDocument(_lsFile); Worksheet worksheet = workbook.Worksheets[0]; worksheet.Range["A1"].Select(); //總列數,隱藏於A5 int rowTotal = RowIndex + worksheet.Cells["A5"].Value.AsInt(); //起始年份,隱藏於B5 string firstYear = worksheet.Cells["B5"].Value.AsString(); /****************** * 讀取資料 * 分三段: * 1.年 * 2.當年1月至當月合計 * 3.當年1月至當月明細 ******************/ DataTable dt = new D30580().List30581(firstYear, PbFunc.Left(_emMonthText, 4), $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", "")); if (dt.Rows.Count <= 0) { return($"{firstYear}~{PbFunc.Left(_emMonthText, 4)},{PbFunc.Left(_emMonthText, 4)}01~{_emMonthText.Replace("/", "")},30581-{RptName},無任何資料!"); } /* 成交量 & OI */ DataTable dtAI2 = new D30580().List30581AI2(firstYear, PbFunc.Left(_emMonthText, 4), $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", "")); if (dtAI2.Rows.Count <= 0) { return($"{firstYear}~{PbFunc.Left(_emMonthText, 4)},{PbFunc.Left(_emMonthText, 4)}01~{_emMonthText.Replace("/", "")},30581-{RptName},無任何資料!"); } //寫入內容 string lsYMD = ""; int rowCurr = 0; string endYMD = dt.AsEnumerable().LastOrDefault()["AM2_YMD"].AsString(); foreach (DataRow row in dt.Rows) { if (lsYMD != row["AM2_YMD"].AsString()) { lsYMD = row["AM2_YMD"].AsString(); RowIndex = RowIndex + 1; if (lsYMD.Length == 4) { worksheet.Rows[RowIndex][1 - 1].Value = PbFunc.Left(lsYMD, 4).AsInt(); rowCurr = RowIndex; } else { rowCurr = lsYMD != endYMD ? RowIndex : rowTotal; worksheet.Rows[rowCurr][1 - 1].Value = PbFunc.f_get_month_eng_name(PbFunc.Right(lsYMD, 2).AsInt(), "1"); }//if (lsYMD.Length==4) /* 成交量 & OI */ int foundIndex = dtAI2.Rows.IndexOf(dtAI2.Select($@"ai2_ymd ='{lsYMD}'").FirstOrDefault()); if (foundIndex > -1) { //iole_1.application.activecell(li_ole_row_curr,6).value = ids_tmp.getitemdecimal(ll_found,"cp_m_qnty") worksheet.Rows[rowCurr][6 - 1].Value = dtAI2.Compute("sum(AI2_M_QNTY)", $@"AI2_SUM_TYPE='{dtAI2.Rows[foundIndex]["AI2_SUM_TYPE"]}' and AI2_YMD='{dtAI2.Rows[foundIndex]["AI2_YMD"]}'").AsDecimal(); worksheet.Rows[rowCurr][7 - 1].Value = dtAI2.Rows[foundIndex]["AI2_OI"].AsDecimal(); } }//if (lsYMD != row["AM2_YMD"].AsString()) //判斷欄位 int columnIndex = IDFGtype(row); worksheet.Rows[rowCurr][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal(); }//foreach (DataRow row in dt.Rows) //刪除空白列 if (rowTotal > RowIndex) { worksheet.Range[$"{RowIndex+1}:{rowTotal + 1 - 1}"].Delete(DeleteMode.EntireRow); worksheet.ScrollTo(0, 0);//直接滾動到最上面,不然看起來很像少行數 } } catch (Exception ex) { #if DEBUG throw new Exception($"wf30581:" + ex.Message); #else throw ex; #endif } finally { workbook.SaveDocument(_lsFile); } return(MessageDisplay.MSG_OK); }