/// <summary> /// 7/2上線前的版本 可供新需求的邏輯參考 /// </summary> /// <returns></returns> public string Wf40040ETF() { Workbook workbook = new Workbook(); try { workbook.LoadDocument(_lsFile); Worksheet worksheet = workbook.Worksheets[(int)SheetName.SheetTwo]; DateTime emdate = _emDateText.AsDateTime("yyyy/MM/dd"); worksheet.Cells["P1"].Value = "資料日期:" + emdate.ToLongDateString(); //前一交易日 DateTime dateLast = dao40040.GetDateLast(emdate, (int)SheetName.SheetTwo); //讀取資料 DataTable dtETF = dao40040.ListEtfData(emdate, dateLast, $"{_oswGrpVal}%"); if (dtETF.Rows.Count <= 0) { return(MessageDisplay.MSG_NO_DATA); } //讀取資料(現貨資料) DataTable dtEtfMg6 = dao40040.ListEtfMg6Data(emdate); //保證金變動幅度達10%,分別為第n天 DataTable dtDay = dao40040.ListDayData(emdate); foreach (DataRow row in dtETF.Rows) { int rowIndex = row["SN"].AsInt() + 10; #region 第B、C、D、E、F欄 worksheet.Cells[$"B{rowIndex}"].Value = row["SN"].AsInt(); worksheet.Cells[$"C{rowIndex}"].Value = row["MGT2_KIND_ID_OUT"].AsString(); worksheet.Cells[$"D{rowIndex}"].Value = row["APDK_NAME"].AsString(); worksheet.Cells[$"E{rowIndex}"].Value = row["APDK_STOCK_ID"].AsString(); worksheet.Cells[$"F{rowIndex}"].Value = row["PID_NAME"].AsString(); #endregion if (row["MG1_CHANGE_FLAG"].AsString() != "Y") { continue; } //1.保證金變動幅度之趨勢/ //15%≧X≧10% decimal mg1ChangeRange = row["MG1_CHANGE_RANGE"].AsDecimal(); #region 第G欄 if (Math.Abs(mg1ChangeRange) >= 0.1m && Math.Abs(mg1ChangeRange) <= 0.15m) { worksheet.Cells[$"G{rowIndex}"].SetValue(mg1ChangeRange); } #endregion //X>15% #region 第H欄 if (Math.Abs(mg1ChangeRange) > 0.15m) { worksheet.Cells[$"H{rowIndex}"].SetValue(mg1ChangeRange); } #endregion //前一交易日保證金變動幅度 var ldValue = row["MG1_CHANGE_RANGE_LAST"]; #region 第I欄 worksheet.Cells[$"I{rowIndex}"].SetValue(ldValue == DBNull.Value ? " " : ldValue); #endregion #region 第J欄 worksheet.Cells[$"J{rowIndex}"].SetValue(ldValue == DBNull.Value ? " " : OX(row)); #endregion //達得調整標準天數 string kindID = row["MG1_KIND_ID"].AsString(); #region 第K欄 int dtDayIndex = dtDay.Rows.IndexOf(dtDay.Select($@"mg1_kind_id ='{kindID}'").FirstOrDefault()); worksheet.Cells[$"K{rowIndex}"].SetValue(dtDayIndex > -1 ? (long)dtDay.Rows[dtDayIndex]["DAY_CNT"].AsDecimal() : 1); #endregion //2.未沖銷部位數/ var ai2OI = row["AI2_OI"]; if (ai2OI != DBNull.Value) { #region 第L欄 worksheet.Cells[$"L{rowIndex}"].SetValue(ai2OI); #endregion #region 第M欄 var oiRate = row["OI_RATE"]; worksheet.Cells[$"M{rowIndex}"].SetValue(oiRate.AsDecimal() < 0.0001m && ai2OI.AsDecimal() > 0 ? "小於0.01%" : oiRate); #endregion #region 第N欄 decimal TotOIiRound = Math.Round(dtETF.Rows[0]["TOT_OI"].AsDecimal() * 0.005m, 0, MidpointRounding.AwayFromZero); worksheet.Cells[$"N{rowIndex}"].SetValue(ai2OI.AsDecimal() >= TotOIiRound ? "O" : "X"); #endregion //屆到期日前7個交易日 #region 第O欄 worksheet.Cells[$"O{rowIndex}"].SetValue( row["APROD_7DATE"].AsDateTime() <= emdate && row["APROD_DELIVERY_DATE"].AsDateTime() > emdate ? "O" : "X" ); #endregion } //3.現貨、期貨漲跌/ int dtMg6EtfIndex = dtEtfMg6.Rows.IndexOf(dtEtfMg6.Select($@"trim(mg6_kind_id) ='{kindID}'").FirstOrDefault()); if (dtMg6EtfIndex > -1) { string prodSubtype = ""; //現貨漲跌幅度 #region 第P欄 var updown = dtEtfMg6.Rows[dtMg6EtfIndex]["MG6_UP_DOWN"]; decimal rateMUL100 = dtEtfMg6.Rows[dtMg6EtfIndex]["MG6_RETURN_RATE"].AsDecimal() * 100; string flag = FlagStr(updown.AsDecimal()); worksheet.Cells[$"P{rowIndex}"].SetValue(updown == DBNull.Value ? updown : WriteUpDownPercent(updown.AsDecimal(), rateMUL100, prodSubtype, flag)); #endregion //現貨漲跌與保證金調整方向相同 var cmlast = row["MG1_CM_LAST"]; #region 第R欄 worksheet.Cells[$"R{rowIndex}"].SetValue(cmlast == DBNull.Value ? "" : WriteFlag(flag, row)); #endregion //期貨漲跌幅度 if (row["APDK_PROD_TYPE"].AsString() == "O") { dtMg6EtfIndex = dtEtfMg6.Rows.IndexOf(dtEtfMg6.Select($@"apdk_stock_id = '{row["APDK_STOCK_ID"].AsString()}' and mg6_prod_type ='F' and kind_seq_no = 1").FirstOrDefault()); if (dtMg6EtfIndex <= -1) { continue; } } #region 第Q欄 var fupdown = dtEtfMg6.Rows[dtMg6EtfIndex]["AI5_UP_DOWN"]; string flag2 = FlagStr(fupdown.AsDecimal()); decimal frateMUL100 = dtEtfMg6.Rows[dtMg6EtfIndex]["AI5_RETURN_RATE"].AsDecimal() * 100; worksheet.Cells[$"Q{rowIndex}"].SetValue(WriteUpDownPercent(fupdown.AsDecimal(), frateMUL100, prodSubtype, flag2)); #endregion //期貨漲跌與保證金調整方向相同 #region 第S欄 worksheet.Cells[$"S{rowIndex}"].SetValue(cmlast == DBNull.Value ? "" : WriteFlag(flag2, row)); #endregion } // if (dtMg6EtfIndex > -1) } //foreach (DataRow row in dt.Rows) //刪除空白列 int rowCount = dtETF.Rows.Count; if (50 > rowCount) { worksheet.Rows.Hide(rowCount + 10, 60 - 1); } //save worksheet.ScrollTo(0, 0); } catch (Exception ex) { #if DEBUG throw new Exception($"Wf40040ETF:" + ex.Message); #else throw ex; #endif } finally { workbook.SaveDocument(_lsFile); } return(MessageDisplay.MSG_OK); }