/// <summary> /// wf_30396 /// </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 Wf30396(string IsKindID = "BRF", string SheetName = "30396", int RowIndex = 1, int RowTotal = 33) { try { //前月倒數2天交易日 DateTime StartDate = PbFunc.f_get_last_day("AI3", IsKindID, _emMonthText, 2); //抓當月最後交易日 DateTime EndDate = PbFunc.f_get_end_day("AI3", IsKindID, _emMonthText); //切換Sheet Worksheet worksheet = _workbook.Worksheets[SheetName]; //無前月資料 int addRowCount = 0;//總計寫入的行數 DataTable dtAI3 = new AI3().ListAI3(IsKindID, StartDate, EndDate); //讀取資料 string firstDATE = dtAI3.AsEnumerable().FirstOrDefault()["AI3_DATE"].AsDateTime().ToString("yyyy/MM"); if (firstDATE == _emMonthText) { RowIndex = RowIndex + 2; } //寫入資料 DateTime ldtYMD = new DateTime(1900, 1, 1); foreach (DataRow row in dtAI3.Rows) { if (ldtYMD != row["AI3_DATE"].AsDateTime()) { ldtYMD = row["AI3_DATE"].AsDateTime(); RowIndex = RowIndex + 1; addRowCount++; worksheet.Rows[RowIndex][1 - 1].Value = ldtYMD.ToString("MM/dd");//日期 } //if not isnull(ld_val) then iole_1.application.activecell(ii_ole_row, 3).value = ids_1.getitemdecimal(i, "ai3_close_price") - ids_1.getitemdecimal(i, "ai3_last_close_price") //pb這段只會在Excel隱藏的欄位執行成功 if (RowIndex == 2 && row["AI3_LAST_CLOSE_PRICE"] != DBNull.Value) { worksheet.Rows[RowIndex][3 - 1].Value = row["AI3_CLOSE_PRICE"].AsDecimal() - row["AI3_LAST_CLOSE_PRICE"].AsDecimal(); //漲跌 } worksheet.Rows[RowIndex][2 - 1].Value = row["AI3_CLOSE_PRICE"].AsDecimal(); //布蘭特原油期貨價格 worksheet.Rows[RowIndex][4 - 1].Value = row["AI3_M_QNTY"].AsDecimal(); //布蘭特原油期貨總成交量 worksheet.Rows[RowIndex][5 - 1].Value = row["AI3_OI"].AsDecimal(); //布蘭特原油期貨總未平倉量 } //刪除空白列 if (RowTotal > addRowCount) { worksheet.Rows.Remove(RowIndex + 1, RowTotal - addRowCount); //重新選取圖表範圍 ResetChartData(RowIndex + 1, _workbook, worksheet, $"{SheetName}a");//ex:30396a } } catch (Exception ex) { #if DEBUG throw new Exception($"Wf30396:" + ex.Message); #else throw ex; #endif } return(MessageDisplay.MSG_OK); }
protected override ResultStatus Export() { if (!StartExport()) { return(ResultStatus.Fail); } Workbook workbook = new Workbook(); string lsFile = PbFunc.wf_copy_file(_ProgramID, "30393"); //載入Excel workbook.LoadDocument(lsFile); try { b30393 = new B30393(workbook, emMonth.Text); //前月倒數2天交易日 DateTime StartDate = PbFunc.f_get_last_day("AI3", "RHF", emMonth.Text, 2); //抓當月最後交易日 DateTime EndDate = PbFunc.f_get_end_day("AI3", "RHF", emMonth.Text); //RHF ShowMsg("30393-「RHF」期貨契約價量資料 轉檔中..."); OutputShowMessage = b30393.Wf30393(StartDate, EndDate, "RHF", "30393_1(RHF)"); ShowMsg("30397-「黃金」期貨契約價量資料(買賣方比重) 轉檔中..."); OutputShowMessage = b30393.Wf30393abc("RHF", "data_30393_1abc"); //RTF b30393 = new B30393(workbook, emMonth.Text); ShowMsg("30393-「RTF」期貨契約價量資料 轉檔中..."); OutputShowMessage = b30393.Wf30393(StartDate, EndDate, "RTF", "30393_2(RTF)"); ShowMsg("30397-「黃金」期貨契約價量資料(買賣方比重) 轉檔中..."); OutputShowMessage = b30393.Wf30393abc("RTF", "data_30393_2abc"); //XEF b30393 = new B30393(workbook, emMonth.Text); ShowMsg("30393-「XEF」期貨契約價量資料 轉檔中..."); OutputShowMessage = b30393.Wf30393(StartDate, EndDate, "XEF", "30393_3(XEF)"); ShowMsg("30397-「黃金」期貨契約價量資料(買賣方比重) 轉檔中..."); OutputShowMessage = b30393.Wf30393abc("XEF", "data_30393_3abc"); //XJF b30393 = new B30393(workbook, emMonth.Text); ShowMsg("30393-「XJF」期貨契約價量資料 轉檔中..."); OutputShowMessage = b30393.Wf30393(StartDate, EndDate, "XJF", "30393_4(XJF)"); ShowMsg("30397-「黃金」期貨契約價量資料(買賣方比重) 轉檔中..."); OutputShowMessage = b30393.Wf30393abc("XJF", "data_30393_4abc"); } catch (Exception ex) { File.Delete(lsFile); WriteLog(ex); return(ResultStatus.Fail); } finally { //存檔 workbook.SaveDocument(lsFile); workbook.Dispose(); EndExport(); } return(ResultStatus.Success); }
/// <summary> /// wf_30381() /// </summary> /// <param name="RowIndex">Excel的Row位置</param> /// <param name="RowTotal">Excel的Column預留數</param> /// <param name="IsKindID">商品代號</param> /// <param name="SheetName">工作表</param> /// <param name="RptName">作業名稱</param> /// <returns></returns> public string Wf30381(int RowIndex = 1, int RowTotal = 32, string IsKindID = "STW", string SheetName = "30381", string RptName = "新加坡交易所(SGX)摩根臺股期貨市場概況表") { Workbook workbook = new Workbook(); try { //前月倒數2天交易日 DateTime StartDate = PbFunc.f_get_last_day("AI3", IsKindID, _emMonthText, 2); //抓當月最後交易日 DateTime EndDate = PbFunc.f_get_end_day("AI3", IsKindID, _emMonthText); //切換Sheet workbook.LoadDocument(_lsFile); Worksheet worksheet = workbook.Worksheets[SheetName]; //讀取資料 DataTable dt = new D30380().GetData(StartDate, EndDate); if (dt.Rows.Count <= 0) { return($"{StartDate.ToShortDateString()}~{EndDate.ToShortDateString()},30311-{RptName},{IsKindID}無任何資料!"); } DateTime ldtYMD = new DateTime(1900, 1, 1); RowTotal = 32 + 1; //Excel的Column預留數 預留顯示32行加上隱藏的1行 int addRowCount = 0; //總計寫入的行數 foreach (DataRow row in dt.Rows) { if (ldtYMD != row["AI3_DATE"].AsDateTime()) { ldtYMD = row["AI3_DATE"].AsDateTime(); RowIndex = RowIndex + 1; addRowCount++; worksheet.Rows[RowIndex][1 - 1].Value = ldtYMD.ToString("MM/dd"); //日期 worksheet.Rows[RowIndex][4 - 1].Value = row["AI3_M_QNTY"].AsDecimal(); //總成交量 worksheet.Rows[RowIndex][5 - 1].Value = row["AI3_OI"].AsDecimal(); //總未平倉量 } worksheet.Rows[RowIndex][2 - 1].Value = row["AI3_CLOSE_PRICE"].AsDecimal(); //期貨指數 worksheet.Rows[RowIndex][6 - 1].Value = row["AI3_INDEX"].AsDecimal(); //現貨指數 worksheet.Rows[RowIndex][10 - 1].Value = row["AI3_M_QNTY_FITX"].AsDecimal(); //臺指量 } //刪除空白列 if (RowTotal > addRowCount) { worksheet.Range[$"{RowIndex + 2}:{RowTotal + 2}"].Clear(); //清空沒有必要的數值 worksheet.Rows.Hide(RowIndex + 1, RowIndex + (RowTotal - addRowCount)); //隱藏代替刪除 } } catch (Exception ex) { throw ex; } finally { workbook.SaveDocument(_lsFile); } return(MessageDisplay.MSG_OK); }
/// <summary> /// wf_30331 /// 30398,30399相同 /// </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 Wf30331(string IsKindID = "GTF", string SheetName = "30398", int RowIndex = 1, int RowTotal = 33) { try { //前月倒數2天交易日 DateTime StartDate = PbFunc.f_get_last_day("AI3", IsKindID, _emMonthText, 2); //抓當月最後交易日 DateTime EndDate = PbFunc.f_get_end_day("AI3", IsKindID, _emMonthText); Worksheet worksheet = _workbook.Worksheets[SheetName]; /*add some infor 原本template標題就已經設定 這段看不出意義在哪 所以不翻 * iole_1.application.activecell(1, 1).value = "櫃買期貨" * iole_1.application.activecell(2, 2).value = "櫃買價格" * iole_1.application.activecell(2, 4).value = "櫃買期貨總成交量" * iole_1.application.activecell(2, 5).value = "櫃買期貨總未平倉量" * end add*/ worksheet.Range["A1"].Select(); int addRowCount = 0;//總計寫入的行數 //讀取資料 DataTable dtAI3 = daoAI3.ListAI3(IsKindID, StartDate, EndDate); //寫入資料 DateTime ldtYMD = new DateTime(1900, 1, 1); foreach (DataRow row in dtAI3.Rows) { if (ldtYMD != row["AI3_DATE"].AsDateTime()) { ldtYMD = row["AI3_DATE"].AsDateTime(); RowIndex = RowIndex + 1; addRowCount++; worksheet.Rows[RowIndex][1 - 1].Value = ldtYMD.ToString("MM/dd");//日期 } worksheet.Rows[RowIndex][2 - 1].Value = row["AI3_CLOSE_PRICE"].AsDecimal(); //櫃買價格 worksheet.Rows[RowIndex][4 - 1].Value = row["AI3_M_QNTY"].AsDecimal(); //櫃買期貨總成交量 worksheet.Rows[RowIndex][5 - 1].Value = row["AI3_OI"].AsDecimal(); //櫃買期貨總未平倉量 worksheet.Rows[RowIndex][6 - 1].Value = row["AI3_INDEX"].AsDecimal(); //現貨價格 } //刪除空白列 if (RowTotal > addRowCount) { worksheet.Rows.Remove(RowIndex + 1, RowTotal - addRowCount); //重新選取圖表範圍 ResetChartData(RowIndex + 1, _workbook, worksheet, $"{SheetName}a"); //ex:30398a worksheet.ScrollTo(0, 0); //直接滾動到最上面,不然看起來很像少行數 } } catch (Exception ex) { #if DEBUG throw new Exception($"Wf30331:" + ex.Message); #else throw ex; #endif } return(MessageDisplay.MSG_OK); }
/// <summary> /// 寫入 30331 sheet /// </summary> /// <returns></returns> public string Wf30331() { Workbook workbook = new Workbook(); try { string lsKindID = "GBF"; //前月倒數2天交易日 DateTime StartDate = PbFunc.f_get_last_day("AI3", lsKindID, _emMonthText, 2); //抓當月最後交易日 DateTime EndDate = PbFunc.f_get_end_day("AI3", lsKindID, _emMonthText); //切換Sheet workbook.LoadDocument(_lsFile); Worksheet worksheet = workbook.Worksheets[0]; DateTime ldtYMD = new DateTime(1900, 1, 1); int rowIndex = 1; int RowTotal = 32 + 1; //Excel的Column預留數 預留顯示32行加上隱藏的1行 int addRowCount = 0; //總計寫入的行數 //讀取資料 DataTable dt = daoAI3.ListAI3(lsKindID, StartDate, EndDate); foreach (DataRow row in dt.Rows) { if (ldtYMD != row["AI3_DATE"].AsDateTime()) { ldtYMD = row["AI3_DATE"].AsDateTime(); rowIndex = rowIndex + 1; addRowCount++; worksheet.Rows[rowIndex][1 - 1].Value = ldtYMD.ToString("MM/dd"); //日期 } worksheet.Rows[rowIndex][2 - 1].Value = row["AI3_CLOSE_PRICE"].AsDecimal(); //公債期貨價格 worksheet.Rows[rowIndex][4 - 1].Value = row["AI3_M_QNTY"].AsDecimal(); //公債期貨總成交量 worksheet.Rows[rowIndex][5 - 1].Value = row["AI3_OI"].AsDecimal(); //公債期貨總未平倉量 } //刪除空白列 if (RowTotal > addRowCount) { worksheet.Rows.Remove(rowIndex + 1, RowTotal - addRowCount); //重新選取圖表範圍 ResetChartData(rowIndex + 1, workbook, worksheet, "30332"); } worksheet.ScrollTo(0, 0); } catch (Exception ex) { throw ex; } finally { workbook.SaveDocument(_lsFile); } return(MessageDisplay.MSG_OK); }
/// <summary> /// wf_30391() /// </summary> /// <param name="RowIndex">Excel的Row位置</param> /// <param name="RowTotal">Excel的Column預留數</param> /// <param name="IsKindID">商品代號</param> /// <param name="SheetName">工作表</param> /// <param name="RptName">作業名稱</param> /// <returns></returns> public string Wf30391(int RowIndex = 1, int RowTotal = 32, string IsKindID = "T5F", string SheetName = "30391", string RptName = "「台灣五十」期貨契約價量資料") { Workbook workbook = new Workbook(); try { //前月倒數2天交易日 DateTime StartDate = PbFunc.f_get_last_day("AI3", IsKindID, _emMonthText, 2); //抓當月最後交易日 DateTime EndDate = PbFunc.f_get_end_day("AI3", IsKindID, _emMonthText); //切換Sheet workbook.LoadDocument(_lsFile); Worksheet worksheet = workbook.Worksheets[SheetName]; //讀取資料 DataTable dt = new AI3().ListAI3(IsKindID, StartDate, EndDate); if (dt.Rows.Count <= 0) { return($"{StartDate.ToShortDateString()}~{EndDate.ToShortDateString()},30391-{RptName},{IsKindID}無任何資料!"); } DateTime ldtYMD = new DateTime(1900, 1, 1); RowTotal = 32 + 1; //Excel的Column預留數 預留顯示32行加上隱藏的1行 int addRowCount = 0; //總計寫入的行數 foreach (DataRow row in dt.Rows) { if (ldtYMD != row["AI3_DATE"].AsDateTime()) { ldtYMD = row["AI3_DATE"].AsDateTime(); RowIndex = RowIndex + 1; addRowCount++; worksheet.Rows[RowIndex][1 - 1].Value = ldtYMD.ToString("MM/dd"); //日期 } worksheet.Rows[RowIndex][2 - 1].Value = row["AI3_CLOSE_PRICE"].AsDecimal(); //臺灣50期貨指數 worksheet.Rows[RowIndex][4 - 1].Value = row["AI3_M_QNTY"].AsDecimal(); //臺灣50期貨總成交量 worksheet.Rows[RowIndex][5 - 1].Value = row["AI3_OI"].AsDecimal(); //臺灣50期貨總未平倉量 worksheet.Rows[RowIndex][6 - 1].Value = row["AI3_INDEX"].AsDecimal(); //臺灣50現貨指數 } //刪除空白列 if (RowTotal > addRowCount) { worksheet.Rows.Remove(RowIndex + 1, RowTotal - addRowCount); } } catch (Exception ex) { throw ex; } finally { workbook.SaveDocument(_lsFile); } return(MessageDisplay.MSG_OK); }
protected override ResultStatus Export() { try { #region 輸入&日期檢核 if (string.Compare(txtStartMonth.Text, txtEndMonth.Text) > 0) { MessageDisplay.Error("月份起始年月不可小於迄止年月!", GlobalInfo.ErrorText); return(ResultStatus.Fail); } #endregion //0. ready panFilter.Enabled = false; labMsg.Visible = true; labMsg.Text = "開始轉檔..."; this.Cursor = Cursors.WaitCursor; this.Refresh(); Thread.Sleep(5); DateTime as_symd = DateTime.ParseExact((txtStartMonth.Text + "/01"), "yyyy/MM/dd", null); DateTime as_eymd = PbFunc.f_get_end_day("AI2", "TXF", txtEndMonth.Text); //抓當月最後交易日 //1.複製檔案 & 開啟檔案 (因為三張報表都輸出到同一份excel,所以提出來) string excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID); Workbook workbook = new Workbook(); workbook.LoadDocument(excelDestinationPath); Worksheet worksheet = workbook.Worksheets[0]; //2.填資料 ii_ole_row = 3; bool sheet1 = wf_Export_30621(workbook, worksheet, as_symd, as_eymd); ii_ole_row += 5; bool sheet2 = wf_Export_30622(workbook, worksheet, as_symd, as_eymd); //存檔 workbook.SaveDocument(excelDestinationPath); if (!sheet1 && !sheet2) { File.Delete(excelDestinationPath); return(ResultStatus.Fail); } return(ResultStatus.Success); } catch (Exception ex) { WriteLog(ex); } finally { panFilter.Enabled = true; labMsg.Text = ""; labMsg.Visible = false; this.Cursor = Cursors.Arrow; } return(ResultStatus.Fail); }
/// <summary> /// wf_30392_aprf /// </summary> /// <param name="workbook"></param> /// <param name="kindId"></param> /// <param name="sheetName"></param> /// <param name="row"></param> protected void wf_30392_1_aprf(Workbook workbook, string sheetName, int row) { string rptName = "「東證期貨」放寬漲跌幅統計表"; ShowMsg(string.Format("{0}-{1} 轉檔中...", _ProgramID, rptName)); try { //1. 處理日期 DateTime ldt_sdate = PbFunc.f_get_last_day("AI3", "SPF", txtMonth.Text, 2); //前月倒數2天交易日 DateTime ldt_edate = PbFunc.f_get_end_day("AI3", "SPF", txtMonth.Text); //抓當月最後交易日 ldt_sdate = DateTime.ParseExact(ldt_edate.ToString("yyyy/MM/01"), "yyyy/MM/dd", null); DataTable dtAprf = dao30392.d_30392_aprf(ldt_sdate, ldt_edate); if (dtAprf.Rows.Count <= 0) { MessageDisplay.Info(string.Format("{0}~{1},{2}-{3},無任何資料!", ldt_sdate.ToString("yyyy/MM/dd"), ldt_edate.ToString("yyyy/MM/dd"), _ProgramID, rptName), GlobalInfo.ResultText); return; } //2. 切換sheet Worksheet ws2 = workbook.Worksheets[sheetName]; //3. 內容 ws2.Import(dtAprf, false, row, 0); //4. 刪除空白列 int rowTotal = 35; if (rowTotal > dtAprf.Rows.Count + 4) { Range ra = ws2.Range[(dtAprf.Rows.Count + 5).AsString() + ":" + rowTotal.AsString()]; ra.Delete(DeleteMode.EntireRow); } ws2.Range["A1"].Select(); ws2.ScrollToRow(0); flag++; } catch (Exception ex) { WriteLog(ex); } }
public static void MyClassInitialize(TestContext testContext) { ConnectionInfo connectionInfo = SettingDragons.Instance.GetConnectionInfo(SettingDragons.Instance.Setting.Database.CiUserAp); GlobalDaoSetting.Set(connectionInfo); reportDirectoryPath = Path.Combine(Environment.CurrentDirectory.Replace("PhoenixCITests", "PhoenixCI"), "Report", DateTime.Now.ToString("yyyyMMdd")); Directory.CreateDirectory(reportDirectoryPath); string excelTemplateDirectoryPath = Path.Combine(Environment.CurrentDirectory.Replace("PhoenixCITests", "PhoenixCI"), "Excel_Template", "30393.xlsx"); destinationFilePath = Path.Combine(reportDirectoryPath, "30393_" + DateTime.Now.ToString("yyyy.MM.dd") + "-" + DateTime.Now.ToString("hh.mm.ss") + "Test.xlsx"); File.Copy(excelTemplateDirectoryPath, destinationFilePath, true); //前月倒數2天交易日 _StartDate = PbFunc.f_get_last_day("AI3", "RHF", "2018/10", 2); //抓當月最後交易日 _EndDate = PbFunc.f_get_end_day("AI3", "RHF", "2018/10"); _workbook = new Workbook(); //載入Excel _workbook.LoadDocument(destinationFilePath); }
protected override ResultStatus Export() { try { //1. ready panFilter.Enabled = false; labMsg.Visible = true; labMsg.Text = "開始轉檔..."; this.Cursor = Cursors.WaitCursor; this.Refresh(); Thread.Sleep(5); //2. 設定日期 DateTime ldt_sdate, ldt_edate; flag = 0; //2.1 copy template xls to target path string excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID); Workbook workbook = new Workbook(); workbook.LoadDocument(excelDestinationPath); //3. 填資料 //3.1 I5F //前月倒數2天交易日 ldt_sdate = PbFunc.f_get_last_day("AI3", "I5F", txtMonth.Text, 2); //抓當月最後交易日 ldt_edate = PbFunc.f_get_end_day("AI3", "I5F", txtMonth.Text); int row = 1; wf_30392_1(workbook, "I5F", "30392_2(I5F)", ldt_sdate, ldt_edate, row); row = 3; wf_30392_1abc(workbook, "I5F", "data_30392_2abc", row); //3.2 TJF ldt_sdate = PbFunc.f_get_last_day("AI3", "TJF", txtMonth.Text, 2); ldt_edate = PbFunc.f_get_end_day("AI3", "TJF", txtMonth.Text); row = 1; wf_30392_1(workbook, "TJF", "30392_1(TJF)", ldt_sdate, ldt_edate, row); row = 3; wf_30392_1abc(workbook, "TJF", "data_30392_1abc", row); //3.3 UDF ldt_sdate = PbFunc.f_get_last_day("AI3", "UDF", txtMonth.Text, 2); ldt_edate = PbFunc.f_get_end_day("AI3", "UDF", txtMonth.Text); string sdate = ldt_sdate.ToString("yyyyMM"); string edate = ldt_edate.ToString("yyyyMM"); if (sdate == edate) { ldt_sdate = DateTime.ParseExact(ldt_sdate.ToString("yyyy/MM/01"), "yyyy/MM/dd", null).AddDays(-1); } row = 1; wf_30392_1(workbook, "UDF", "30392_3(UDF)", ldt_sdate, ldt_edate, row); row = 3; wf_30392_1abc(workbook, "UDF", "data_30392_3abc", row); //3.4 SPF ldt_sdate = PbFunc.f_get_last_day("AI3", "SPF", txtMonth.Text, 2); ldt_edate = PbFunc.f_get_end_day("AI3", "SPF", txtMonth.Text); sdate = ldt_sdate.ToString("yyyyMM"); edate = ldt_edate.ToString("yyyyMM"); if (sdate == edate) { ldt_sdate = DateTime.ParseExact(ldt_sdate.ToString("yyyy/MM/01"), "yyyy/MM/dd", null).AddDays(-1); } row = 1; wf_30392_1(workbook, "SPF", "30392_4(SPF)", ldt_sdate, ldt_edate, row); row = 3; wf_30392_1abc(workbook, "SPF", "data_30392_4abc", row); //3.5 一定要放到最後,因為ldt_sdate會變成當月1日 row = 4; wf_30392_1_aprf(workbook, "30392_1d", row); if (flag == 0) { File.Delete(excelDestinationPath); MessageDisplay.Info(MessageDisplay.MSG_NO_DATA, GlobalInfo.ResultText); return(ResultStatus.Fail); } //4. save workbook.SaveDocument(excelDestinationPath); if (FlagAdmin) { System.Diagnostics.Process.Start(excelDestinationPath); } return(ResultStatus.Success); } catch (Exception ex) { MessageDisplay.Info(MessageDisplay.MSG_NO_DATA, GlobalInfo.ResultText); WriteLog(ex); } finally { panFilter.Enabled = true; labMsg.Text = ""; labMsg.Visible = false; this.Cursor = Cursors.Arrow; } return(ResultStatus.Fail); }
/// <summary> /// 寫入 30311_1 sheet /// </summary> /// <param name="lsKindID"></param> /// <param name="SheetName"></param> /// <returns></returns> public string Wf30310one(string lsKindID, string SheetName) { Workbook workbook = new Workbook(); try { //切換Sheet workbook.LoadDocument(_lsFile); Worksheet worksheet = workbook.Worksheets[SheetName]; //前月倒數2天交易日 DateTime StartDate = PbFunc.f_get_last_day("AI3", lsKindID, _emMonthText, 2); //抓當月最後交易日 DateTime EndDate = PbFunc.f_get_end_day("AI3", lsKindID, _emMonthText); //讀取資料 DataTable dt = dao30310.GetData(lsKindID, StartDate, EndDate); if (dt.Rows.Count <= 0) { return($"{StartDate.ToShortDateString()}~{EndDate.ToShortDateString()},30310-我國臺股期貨契約價量資料,{lsKindID}無任何資料!"); } DateTime ldtYMD = new DateTime(1900, 1, 1); int rowIndex = 1; int RowTotal = 32 + 1; //Excel的Column預留數 預留顯示32行加上隱藏的1行 int addRowCount = 0; //總計寫入的行數 foreach (DataRow row in dt.Rows) { //不同的日期就寫入新的一行 if (ldtYMD != row["AI3_DATE"].AsDateTime()) { ldtYMD = row["AI3_DATE"].AsDateTime(); rowIndex = rowIndex + 1; addRowCount++; //日期 worksheet.Cells[$"A{rowIndex + 1}"].SetValue(ldtYMD.ToString("MM/dd")); } //臺股期貨市場 worksheet.Cells[$"B{rowIndex + 1}"].SetValue(row["AI3_CLOSE_PRICE"]); //臺股期貨指數(TX) worksheet.Cells[$"D{rowIndex + 1}"].SetValue(row["AI3_M_QNTY"]); //股價指數類期貨成交量(註①) worksheet.Cells[$"E{rowIndex + 1}"].SetValue(row["AI3_OI"]); //股價指數類未平倉量(註①) //臺股現貨市場 worksheet.Cells[$"F{rowIndex + 1}"].SetValue(row["AI3_INDEX"]); //臺股現貨指數(TAIEX) if (lsKindID == "TXF") { worksheet.Cells[$"H{rowIndex + 1}"].SetValue(row["AI3_AMOUNT"]);//成交值(億元) } } //刪除空白列 if (RowTotal > addRowCount) { worksheet.Rows.Remove(rowIndex + 1, RowTotal - addRowCount); //重新選取圖表範圍 ResetChartData(rowIndex + 1, workbook, worksheet, $"{SheetName}a"); //ex:30393_1a worksheet.ScrollTo(0, 0); //直接滾動到最上面,不然看起來很像少行數 } //表尾 dt = daoAI2.ListAI2ym(lsKindID, EndDate.ToString("yyyyMM"), StartDate.ToString("yyyyMM")); if (dt.Rows.Count <= 0) { return(""); } int liDayCnt; //上月 rowIndex = rowIndex + 5; liDayCnt = dt.Rows[0]["LAST_M_DAY_CNT"].AsInt(); if (liDayCnt > 0) { worksheet.Cells[$"E{rowIndex + 1}"].Value = Math.Round(dt.Rows[0]["LAST_M_QNTY"].AsDecimal() / liDayCnt, 0); worksheet.Cells[$"G{rowIndex + 1}"].Value = Math.Round(dt.Rows[0]["LAST_M_OI"].AsDecimal() / liDayCnt, 0); } //本月 rowIndex = rowIndex - 1; liDayCnt = dt.Rows[0]["CUR_M_DAY_CNT"].AsInt(); if (liDayCnt > 0) { worksheet.Cells[$"E{rowIndex + 1}"].Value = Math.Round(dt.Rows[0]["CUR_M_QNTY"].AsDecimal() / liDayCnt, 0); worksheet.Cells[$"G{rowIndex + 1}"].Value = Math.Round(dt.Rows[0]["CUR_M_OI"].AsDecimal() / liDayCnt, 0); } rowIndex = rowIndex + 1; //今年迄今 rowIndex = rowIndex + 2; liDayCnt = dt.Rows[0]["Y_DAY_CNT"].AsInt(); if (liDayCnt > 0) { worksheet.Cells[$"E{rowIndex + 1}"].Value = Math.Round(dt.Rows[0]["Y_QNTY"].AsDecimal() / liDayCnt, 0); worksheet.Cells[$"G{rowIndex + 1}"].Value = Math.Round(dt.Rows[0]["Y_OI"].AsDecimal() / liDayCnt, 0); } } catch (Exception ex) { throw ex; } finally { workbook.SaveDocument(_lsFile);//存檔 } return(MessageDisplay.MSG_OK); }
/// <summary> /// 寫入 30311_4 sheet /// </summary> /// <returns></returns> public string Wf30310four() { Workbook workbook = new Workbook(); try { string lsKindID = "MSF"; string SheetName = "30311_4"; //前月倒數2天交易日 DateTime StartDate = PbFunc.f_get_last_day("AI3", lsKindID, _emMonthText, 2); //抓當月最後交易日 DateTime EndDate = PbFunc.f_get_end_day("AI3", lsKindID, _emMonthText); //切換Sheet workbook.LoadDocument(_lsFile); Worksheet worksheet = workbook.Worksheets[SheetName]; DateTime ldtYMD = new DateTime(1900, 1, 1); //讀取資料 DataTable dt = daoAI3.ListAI3(lsKindID, StartDate, EndDate); if (dt.Rows.Count <= 0) { return(MessageDisplay.MSG_OK); } int rowIndex = 1; int RowTotal = 32 + 1; //Excel的Column預留數 預留顯示32行加上隱藏的1行 int addRowCount = 0; //總計寫入的行數 foreach (DataRow row in dt.Rows) { //不同的日期就寫入新的一行 if (ldtYMD != row["AI3_DATE"].AsDateTime()) { ldtYMD = row["AI3_DATE"].AsDateTime(); rowIndex = rowIndex + 1; addRowCount++; worksheet.Cells[$"A{rowIndex + 1}"].SetValue(ldtYMD.ToString("MM/dd")); } //金融期貨市場 worksheet.Cells[$"B{rowIndex + 1}"].SetValue(row["AI3_CLOSE_PRICE"]); //金融期貨指數(TX) worksheet.Cells[$"D{rowIndex + 1}"].SetValue(row["AI3_M_QNTY"]); //金融期貨成交量(註①) worksheet.Cells[$"E{rowIndex + 1}"].SetValue(row["AI3_OI"]); //金融期貨未平倉量(註①) //金融現貨市場 worksheet.Cells[$"F{rowIndex + 1}"].SetValue(row["AI3_INDEX"]); //金融現貨指數(TAIEX) } //刪除空白列 if (RowTotal > addRowCount) { worksheet.Rows.Remove(rowIndex + 1, RowTotal - addRowCount); string[] data = new string[] { "D4:D", "E4:E", $@"B4:B", $@"F4:F" }; int count = 0; //重新抓取圖表範圍 foreach (var item in data) { workbook.ChartSheets[$"{SheetName}a"].Chart.Series[count++].Values = new ChartData { RangeValue = worksheet.Range[item + rowIndex.ToString()] }; } } worksheet.ScrollTo(0, 0); } catch (Exception ex) { throw ex; } finally { workbook.SaveDocument(_lsFile);//存檔 } return(MessageDisplay.MSG_OK); }
private bool wf_30611(Workbook workbook) { try { string rptName = "每月現、期貨市場振幅、波動度、成交量彙集"; string rptId = "30611"; ShowMsg(string.Format("{0}-{1} 轉檔中...", rptId, rptName)); Worksheet worksheet = workbook.Worksheets[(int)SheetNo.mon]; //切換sheet int ii_ole_row = 4; int li_ole_row_tol = 184; //每月 DateTime endDay = PbFunc.f_get_end_day("AI2", "TXF", txtEndMonth.Text); DataTable dtContent = dao30610.GetMonData(txtStartMonth.DateTimeValue, endDay);; //月明細表 if (dtContent.Rows.Count <= 0) { MessageDisplay.Info(string.Format("{0}~{1},{2}-{3},無任何資料!", txtStartMonth.Text, txtEndMonth.Text, rptId, rptName), GlobalInfo.ResultText); return(false); } int li_ole_end_row = 0; for (int w = 1; w <= dtContent.Rows.Count; w++) { if (w == dtContent.Rows.Count) { li_ole_end_row = ii_ole_row; ii_ole_row = li_ole_row_tol; } else { ii_ole_row++; } worksheet.Cells[ii_ole_row - 1, 0].Value = dtContent.Rows[w - 1]["AMIF_YM"].AsString().SubStr(0, 4).AsInt() - 1911 + dtContent.Rows[w - 1]["AMIF_YM"].AsString().SubStr(4, 2); worksheet.Cells[ii_ole_row - 1, 1].Value = dtContent.Rows[w - 1]["AMIF_TOT_CNT"].AsInt(); worksheet.Cells[ii_ole_row - 1, 2].Value = dtContent.Rows[w - 1]["TFXM_AVG_UP_DOWN"].AsDecimal(); worksheet.Cells[ii_ole_row - 1, 4].Value = dtContent.Rows[w - 1]["TFXM_CNT"].AsInt(); worksheet.Cells[ii_ole_row - 1, 5].Value = dtContent.Rows[w - 1]["RETURN_P2"].AsDecimal() * 100; worksheet.Cells[ii_ole_row - 1, 6].Value = dtContent.Rows[w - 1]["TFXM_AVG_CLOSE_PRICE"].AsDecimal(); worksheet.Cells[ii_ole_row - 1, 7].Value = dtContent.Rows[w - 1]["TFXM_M_QNTY_TAL"].AsDecimal(); worksheet.Cells[ii_ole_row - 1, 8].Value = dtContent.Rows[w - 1]["AMIF_AVG_UP_DOWN"].AsDecimal(); worksheet.Cells[ii_ole_row - 1, 10].Value = dtContent.Rows[w - 1]["AMIF_CNT"].AsInt(); worksheet.Cells[ii_ole_row - 1, 11].Value = dtContent.Rows[w - 1]["RETURN_P1"].AsDecimal() * 100; worksheet.Cells[ii_ole_row - 1, 12].Value = dtContent.Rows[w - 1]["AMIF_AVG_CLOSE_PRICE"].AsDecimal(); worksheet.Cells[ii_ole_row - 1, 13].Value = dtContent.Rows[w - 1]["AI2_AVG_QTY_TXF"].AsDecimal(); worksheet.Cells[ii_ole_row - 1, 14].Value = dtContent.Rows[w - 1]["AI2_AVG_QTY_TXO"].AsDecimal(); worksheet.Cells[ii_ole_row - 1, 15].Value = dtContent.Rows[w - 1]["AI2_AVG_TOT_QTY"].AsDecimal(); } //刪除空白列 //if (li_ole_end_row < li_ole_row_tol) { // worksheet.Rows.Remove(ii_ole_row , li_ole_row_tol - ii_ole_row); //} if (li_ole_row_tol > dtContent.Rows.Count + 4) { Range ra = worksheet.Range[(dtContent.Rows.Count + 4).AsString() + ":183"]; ra.Delete(DeleteMode.EntireRow); } worksheet.Range["A1"].Select(); worksheet.ScrollToRow(0); return(true); } catch (Exception ex) { WriteLog(ex); } return(false); }
/// <summary> /// wf_30404 (sheet4 data) /// </summary> /// <param name="workbook"></param> /// <param name="sheetNo">SheetNo.tradeSum</param> /// <param name="row"> 1 </param> /// <returns></returns> protected int wf_30404(Workbook workbook, SheetNo sheetNo, int rowNum, string strKindId) { string rptName = "個別股票期貨成交量及未平倉量變化表"; ShowMsg("30404-" + rptName + " 轉檔中..."); try { //1. 取日期 string sDate = PbFunc.f_get_last_day("AI3", strKindId, txtMon.Text, 2, "yyyyMMdd").ToString("yyyy/MM/dd"); //前月倒數2天交易日 string eDate = PbFunc.f_get_end_day("AI3", strKindId, txtMon.Text).ToString("yyyy/MM/dd"); //抓當月最後交易日 //2. 讀取資料 DataTable dt30404 = dao30400.Get30404Data(strKindId, sDate, eDate); if (dt30404.Rows.Count <= 0) { MessageDisplay.Info(String.Format("{0}~{1},30404 - {2},無任何資料!", sDate, eDate, rptName), GlobalInfo.ResultText); return(0); } //if (dt30404.Rows.Count <= 0) //3. 切換Sheet Worksheet ws4 = workbook.Worksheets[(int)sheetNo]; //4. 處理資料 int rowTotal = 35; DataTable dtAPDK = dao30400.GetAdpkData(strKindId); //好像只會有一筆資料 if (dtAPDK.Rows.Count <= 0) { return(0); } else if (dtAPDK.Rows.Count > 0) { ws4.Cells[0, 0].Value = dtAPDK.Rows[0]["apdk_name"].AsString(); ws4.Cells[0, 1].Value = txtKindId.Text; ws4.Cells[0, 8].Value = dtAPDK.Rows[0]["apdk_stock_id"].AsString(); } DateTime ymd = DateTime.ParseExact("1900/01/01", "yyyy/MM/dd", null); foreach (DataRow dr in dt30404.Rows) { DateTime ai3Date = dr["ai3_date"].AsDateTime(); decimal closePrice = dr["ai3_close_price"].AsDecimal(); decimal mQnty = dr["ai3_m_qnty"].AsDecimal(); decimal ai3Oi = dr["ai3_oi"].AsDecimal(); decimal ai3Index = dr["ai3_index"].AsDecimal(); decimal ai3Amount = dr["ai3_amount"].AsDecimal(); if (ymd != ai3Date) { ymd = ai3Date; rowNum++; ws4.Cells[rowNum, 0].Value = ymd.ToString("MM/dd"); } ws4.Cells[rowNum, 1].Value = closePrice; ws4.Cells[rowNum, 3].Value = mQnty; ws4.Cells[rowNum, 4].Value = ai3Oi; ws4.Cells[rowNum, 5].Value = ai3Index; ws4.Cells[rowNum, 7].Value = ai3Amount; }//foreach (DataRow dr in dt30404.Rows) //5. 刪除空白列 if (rowTotal > dt30404.Rows.Count) { Range ra = ws4.Range[(dt30404.Rows.Count + 3).ToString() + ":" + rowTotal.ToString()]; ra.Delete(DeleteMode.EntireRow); } ws4.Range["A1"].Select(); ws4.ScrollToRow(0); return(1); } catch (Exception ex) { WriteLog(ex); return(0); } }
protected override ResultStatus Export() { ExportShow.Text = "轉檔中..."; ExportShow.Show(); try { Workbook workbook = new Workbook(); DataTable dt = new DataTable(); string kindId = "TXF"; DateTime sdate = PbFunc.f_get_last_day("AI3", kindId, txtDate.DateTimeValue.ToString("yyyy/MM"), 1); DateTime edate = PbFunc.f_get_end_day("AI3", kindId, txtDate.DateTimeValue.ToString("yyyy/MM")); dt = daoAI3.ListAI3(kindId, sdate, edate); //查無資料時不產檔 if (dt.Rows.Count <= 0) { ExportShow.Hide(); MessageDisplay.Info(sdate + "~" + edate + "," + _ProgramID + '-' + _ProgramName + ",無任何資料!"); return(ResultStatus.Fail); } //複製Template string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID); workbook.LoadDocument(destinationFilePath); //切換sheet Worksheet worksheet = workbook.Worksheets["30711"]; DateTime ldt_ymd = new DateTime(1900, 1, 1); int row_tol = 33; //寫入資料 if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { if (ldt_ymd != Convert.ToDateTime(dt.Rows[i]["ai3_date"])) { ldt_ymd = Convert.ToDateTime(dt.Rows[i]["ai3_date"]); worksheet.Cells[i + 1, 0].Value = ldt_ymd; } worksheet.Cells[i + 1, 1].Value = float.Parse(dt.Rows[i]["ai3_index"].ToString()); worksheet.Cells[i + 1, 2].Value = float.Parse(dt.Rows[i]["ai3_close_price"].ToString()); } } //刪除空白列 if (row_tol > dt.Rows.Count) { Range ra = worksheet.Range[(dt.Rows.Count + 2).ToString() + ":" + row_tol.ToString()]; ra.Delete(DeleteMode.EntireRow); } //圖表重選範圍 ChartObject chartObjs = workbook.ChartSheets[0].Chart; chartObjs.Series[0].SeriesName.SetValue("近月份期貨契約指數"); ChartData closePrice = new ChartData(); closePrice.RangeValue = worksheet.Range["C2:C" + (dt.Rows.Count + 1).ToString()]; chartObjs.Series[0].Values = closePrice; ChartData index = new ChartData(); index.RangeValue = worksheet.Range["B2:B" + (dt.Rows.Count + 1).ToString()]; chartObjs.Series[1].Values = index; workbook.SaveDocument(destinationFilePath); } catch (Exception ex) { ExportShow.Text = "轉檔失敗"; throw ex; } ExportShow.Text = "轉檔成功!"; return(ResultStatus.Success); }
protected override ResultStatus Export() { string showMsg = ""; try { this.Cursor = Cursors.WaitCursor; this.Refresh(); Thread.Sleep(5); lblProcessing.Visible = true; ShowMsg("開始轉檔..."); dao30202 = new D30202(); //判斷是否有檔案,決定是否要寫入DB. showMsg = "讀取既有計算資料錯誤"; string cpYmd = txtDate.DateTimeValue.ToString("yyyyMMdd"); DataTable dtPL1 = dao30202.d_30202_pl1(cpYmd); if (dtPL1.Rows.Count > 0) { DialogResult result = MessageDisplay.Choose("已有計算資料,是否要更新資料庫資料?"); if (result == DialogResult.No) { cbxDB.Checked = false; } } txtPrevEymd.DateTimeValue = PbFunc.f_get_end_day("DATE", "", txtEMonth.Text); txtCurEymd.DateTimeValue = PbFunc.f_get_end_day("DATE", "", txtCurEMonth.Text); string rptId = "30202", rptName = "股價指數暨黃金類商品部位限制數檢視表", file, curSMonth = txtCurSMonth.Text.Replace("/", ""), curEMonth = txtCurEMonth.Text.Replace("/", ""), sMonth = txtSMonth.Text.Replace("/", ""), eMonth = txtEMonth.Text.Replace("/", ""); decimal natureSdt = txtMultiNature.Text.AsDecimal() / 100; decimal legalSdt = txtMultiLegal.Text.AsDecimal() / 100; //讀取資料 showMsg = "讀取資料錯誤"; DataTable dt30202 = dao30202.d_30202(cpYmd, sMonth, eMonth, curSMonth, curEMonth, natureSdt, legalSdt); if (dt30202.Rows.Count == 0) { MessageDisplay.Info(eMonth + "," + rptId + '-' + rptName + ",無任何資料!"); lblProcessing.Visible = false; return(ResultStatus.Fail); } ShowMsg(rptId + '-' + rptName + " 轉檔中..."); //複製檔案 showMsg = "複製檔案錯誤"; file = PbFunc.wf_copy_file(rptId, rptId); if (file == "") { return(ResultStatus.Fail); } //開啟檔案 showMsg = "開啟檔案錯誤"; Workbook workbook = new Workbook(); workbook.LoadDocument(file); //切換Sheet showMsg = "切換Sheet錯誤"; Worksheet ws30202 = workbook.Worksheets[0]; //寫入資料 showMsg = "寫入資料錯誤"; int rowIndex = 2; lblProcessing.Text = rptId + "-" + rptName + " 轉檔中..."; #region wf_30202 string str, natureType, legalType, col; decimal value1, changeRange, curNature, curLegal, cpNature, cpLegal, nature, legal; if (!cbxDB.Checked) { ws30202.Cells[0, 0].Value = ws30202.Cells[0, 0].Value.AsString() + "(試算)"; } //(一) ws30202.Cells[3, 1].Value = "(" + txtSMonth.Text + "/01" + "~" + txtPrevEymd.Text + ")"; //(二) ws30202.Cells[3, 3].Value = "(" + txtCurSMonth.Text + "/01" + "~" + txtCurEymd.Text + ")"; //(六) str = txtCurEMonth.Text; DateTime date = (str + "/01").AsDateTime("yyyy/MM/dd"); do { date = PbFunc.relativedate(date, date.Day * -1); str = str + "、" + date.ToString("yyyy/MM~") + txtCurEMonth.Text; } while ((date.ToString("yyyy/MM") + "/01").AsDateTime("yyyy/MM/dd") > txtSMonth.DateTimeValue);//只比年月 ws30202.Cells[3, 12].Value = str; foreach (DataRow dr in dt30202.Rows) { rowIndex = dr["RPT_SEQ_NO"].AsInt() - 1; decimal pAvgQnty = dr["P_AVG_QNTY"].AsDecimal(); decimal pAvgOi = dr["P_AVG_OI"].AsDecimal(); //前次檢視之數值 ws30202.Cells[rowIndex, 1].Value = pAvgQnty; ws30202.Cells[rowIndex, 2].Value = pAvgOi; //本次檢視之數值 ws30202.Cells[rowIndex, 3].SetValue(dr["C_AVG_QNTY"]); ws30202.Cells[rowIndex, 4].SetValue(dr["C_AVG_OI"]); //相較前次數值增減幅度 if (pAvgQnty > pAvgOi) { value1 = pAvgQnty; } else { value1 = pAvgOi; } if (value1 == 0) { changeRange = -1; } else { changeRange = Math.Round(dr["C_MAX_VALUE"].AsDecimal() / value1 - 1, 4, MidpointRounding.AwayFromZero); } ws30202.Cells[rowIndex, 5].Value = changeRange; dr["CHANGE_RANGE"] = changeRange; //現行部位限制數 curNature = dr["PL2_NATURE"] == DBNull.Value ? -1 : dr["PL2_NATURE"].AsDecimal(); //當該欄位的值為DBNull時等於-1 (權宜做法) curLegal = dr["PL2_LEGAL"] == DBNull.Value ? -1 : dr["PL2_LEGAL"].AsDecimal(); //當該欄位的值為DBNull時等於-1 (權宜做法) if (dr["PL2_NATURE"] != DBNull.Value) { ws30202.Cells[rowIndex, 6].Value = curNature; } if (dr["PL2_LEGAL"] != DBNull.Value) { ws30202.Cells[rowIndex, 7].Value = curLegal; } //按交易規則檢視後之部位限制數 //自然人 value1 = dr["PLT1_T1_MULTIPLE"] == DBNull.Value ? -1 : dr["PLT1_T1_MULTIPLE"].AsDecimal();//當該欄位的值為DBNull時等於-1 (權宜做法) if (dr["PLT1_T1_MIN_NATURE"] == DBNull.Value) { cpNature = dr["C_MAX_VALUE"].AsDecimal() * natureSdt; if (value1 > 0) { cpNature = Math.Truncate(cpNature / value1) * value1; } } else { cpNature = dr["PLT1_T1_MIN_NATURE"].AsDecimal(); } ws30202.Cells[rowIndex, 9].Value = cpNature; dr["CP_NATURE"] = cpNature; //法人 value1 = dr["PLT1_T2_MULTIPLE"] == DBNull.Value ? -1 : dr["PLT1_T2_MULTIPLE"].AsDecimal();//當該欄位的值為DBNull時等於-1 (權宜做法) if (dr["PLT1_T2_MIN_LEGAL"] == DBNull.Value) { cpLegal = dr["C_MAX_VALUE"].AsDecimal() * legalSdt; if (value1 > 0) { cpLegal = Math.Truncate(cpLegal / value1) * value1; } } else { cpLegal = dr["PLT1_T2_MIN_LEGAL"].AsDecimal(); } ws30202.Cells[rowIndex, 10].Value = cpLegal; dr["CP_LEGAL"] = cpLegal; dr["CP_999"] = cpLegal * 3; //近1~6月日均交易量與未沖銷量 /**************************** * 1. 檢視部位限制級距時,若該期間之每日平均交易量或未沖銷量與前次調整時相較,其增減未逾百分之二‧五時,雖達調整級距標準,仍不調整。 * 2.針對須降低部位限制數之商品再增加以最近1、2、4、5、6個月區間資料檢視,並取其數額大者為基準數,惟計算後不得超過前次之部位限制數。 ****************************/ //(isnull(ld_cur_nature) and isnull(ld_cur_legal))這個條件拿掉,因為不會成立 if (Math.Abs(changeRange) <= 0.025m || (curNature == -1 && curLegal == -1) || (curNature == cpNature && curLegal == cpLegal)) { str = "不適用"; if (curNature != -1 && curLegal != -1) { nature = curNature; legal = curLegal; } else { nature = cpNature; legal = cpLegal; } natureType = "不變"; legalType = "不變"; } else { if (cpNature < curNature || cpLegal < curLegal) { col = "max"; //最大者 } else { col = "min"; //最小者 } str = "近" + dr[col + "_MONTH_SEQ_NO"].AsString() + "個月"; if (dr[col + "_TYPE"].AsString() == "OI") { str = str + "未沖銷量"; } else { str = str + "交易量"; } if (col == "max") { str = str + "最大者"; } else { str = str + "最小者"; } str = str + "(" + dr[col + "_VALUE"].AsDecimal().ToString("#,##0") + ")"; //自然人 value1 = dr["PLT1_R1_MULTIPLE"] == DBNull.Value ? -1 : dr["PLT1_R1_MULTIPLE"].AsDecimal();//當該欄位的值為DBNull時等於-1 (權宜做法) if (dr["PLT1_R1_MIN_NATURE"] == DBNull.Value) { nature = dr[col + "_VALUE"].AsDecimal() * natureSdt; if (value1 > 0) { nature = Math.Truncate(nature / value1) * value1; } } else { nature = dr["PLT1_R1_MIN_NATURE"].AsDecimal(); } natureType = "不變"; if (nature < curNature) { natureType = "降低"; } else if (nature > curNature) { natureType = "調高"; } //法人 value1 = dr["PLT1_R2_MULTIPLE"] == DBNull.Value ? -1 : dr["PLT1_R2_MULTIPLE"].AsDecimal();//當該欄位的值為DBNull時等於-1 (權宜做法) if (dr["PLT1_R2_MIN_LEGAL"] == DBNull.Value) { legal = dr[col + "_VALUE"].AsDecimal() * legalSdt; if (value1 > 0) { legal = Math.Truncate(legal / value1) * value1; } } else { legal = dr["PLT1_R2_MIN_LEGAL"].AsDecimal(); } legalType = "不變"; if (legal < curLegal) { legalType = "降低"; } else if (legal > curLegal) { legalType = "調高"; } } ws30202.Cells[rowIndex, 12].Value = str; ws30202.Cells[rowIndex, 14].Value = nature; ws30202.Cells[rowIndex, 15].Value = legal; ws30202.Cells[rowIndex, 16].Value = natureType; ws30202.Cells[rowIndex, 17].Value = legalType; dr["NATURE"] = nature; dr["LEGAL"] = legal; dr["P999"] = legal * 3; switch (natureType) { case "降低": str = "-"; break; case "調高": str = "+"; break; default: str = " "; break; } dr["NATURE_ADJ"] = str; switch (legalType) { case "降低": str = "-"; break; case "調高": str = "+"; break; default: str = " "; break; } dr["LEGAL_ADJ"] = str; dr["P999_ADJ"] = str; dr["YMD"] = cpYmd; //針對須調降之商品再增加檢視標準後之部位限制數 }//foreach (DataRow dr in dt30202.Rows) //表尾 rowIndex = dao30202.row_index(); if (rowIndex > 0) { ws30202.Cells[rowIndex - 1, 0].Value = ws30202.Cells[rowIndex - 1, 0].Value.ToString() + "自然人乘以" + txtMultiNature.Text + "%,法人乘以" + txtMultiLegal.Text + "%)"; } //存檔 ws30202.ScrollToRow(0); workbook.SaveDocument(file); ShowMsg("轉檔成功"); #endregion if (!cbxDB.Checked) { return(ResultStatus.Success); } #region wf_30202_write bool dbCommit = false; //刪除PL0的資料 showMsg = "PL0刪除失敗"; dbCommit = dao30202.DeletePL0ByDate(cpYmd); if (!dbCommit) { MessageDisplay.Error(showMsg); return(ResultStatus.Fail); } //新增PL0的資料 showMsg = "PL0新增失敗"; dbCommit = dao30202.InsertPL0(cpYmd, sMonth, eMonth, curSMonth, curEMonth, GlobalInfo.USER_ID); if (!dbCommit) { MessageDisplay.Error(showMsg); return(ResultStatus.Fail); } //刪除PL2的資料 showMsg = "PL2刪除失敗"; dbCommit = dao30202.DeletePL2ByDate(cpYmd); if (!dbCommit) { MessageDisplay.Error(showMsg); return(ResultStatus.Fail); } //刪除PL1的資料 showMsg = "PL1刪除失敗"; dbCommit = dao30202.DeletePL1ByDate(cpYmd); if (!dbCommit) { MessageDisplay.Error(showMsg); return(ResultStatus.Fail); } //寫入 dtPL1.Clear(); dtPL1.AcceptChanges(); for (int f = 0; f < dt30202.Rows.Count; f++) { dtPL1.Rows.Add(); for (int g = 0; g < 24; g++) { dtPL1.Rows[dtPL1.Rows.Count - 1][g] = dt30202.Rows[f][g]; //現行自然人,現行法人PL if (g == 9 || g == 11) { if (dt30202.Rows[f][g] == DBNull.Value) { dtPL1.Rows[dtPL1.Rows.Count - 1][g] = 0; } } } dtPL1.Rows[dtPL1.Rows.Count - 1][24] = DateTime.Now; dtPL1.Rows[dtPL1.Rows.Count - 1][25] = GlobalInfo.USER_ID; } try { ResultData myResultData = dao30202.updatePL1(dtPL1); } catch (Exception ex) { MessageDisplay.Error("計算結果新增至資料庫錯誤! "); WriteLog(ex, "", false); return(ResultStatus.Fail); } #endregion } catch (Exception ex) { MessageDisplay.Error(showMsg); throw ex; } finally { this.Cursor = Cursors.Arrow; this.Refresh(); Thread.Sleep(5); } return(ResultStatus.Success); }
protected virtual int ExportSummary(Workbook workbook, int sheetIndex, int rowBegin) { //1.1 前月倒數2天交易日 DateTime ldt_sdate = PbFunc.f_get_last_day("AI3", KindId, StartMonth, 2); //1.2 抓當月最後交易日 DateTime ldt_edate = PbFunc.f_get_end_day("AI3", KindId, StartMonth); Worksheet ws = workbook.Worksheets[sheetIndex]; int rowIndex = rowBegin; int emptyRowCount = rowIndex + 1 + 32; //1.3 get ai3 data DataTable dtTemp = Dao.d_ai3(KindId, ldt_sdate, ldt_edate); if (dtTemp.Rows.Count <= 0) { //刪除空白列 if (rowIndex < emptyRowCount) { string selectBegin = (rowIndex + 2).ToString(); string selectEnd = (emptyRowCount).ToString(); string cellRange = string.Format("A{0}:G{1}", selectBegin, selectEnd); ws.DeleteCells(ws.Range[cellRange], DeleteMode.EntireRow); } return(0); } //1.4 export to sheet DateTime ldt_ymd = DateTime.MinValue; foreach (DataRow dr in dtTemp.Rows) { DateTime ai3_date = dr["ai3_date"].AsDateTime(); if (ldt_ymd != ai3_date) { ldt_ymd = ai3_date; rowIndex++; ws.Cells[rowIndex, 0].Value = ldt_ymd.ToString("MM/dd"); } ws.Cells[rowIndex, 1].Value = dr["ai3_close_price"].AsDecimal(); ws.Cells[rowIndex, 3].Value = dr["ai3_m_qnty"].AsDecimal(); ws.Cells[rowIndex, 4].Value = dr["ai3_oi"].AsDecimal(); ws.Cells[rowIndex, 5].Value = dr["ai3_index"].AsDecimal(); }//foreach (DataRow dr in dtTemp.Rows) { //1.5 刪除空白列(注意,沒刪除好會影響到後面sheet的圖表) if (rowIndex < emptyRowCount) { string selectBegin = (rowIndex + 2).ToString(); string selectEnd = (emptyRowCount).ToString(); string cellRange = string.Format("A{0}:G{1}", selectBegin, selectEnd); ws.DeleteCells(ws.Range[cellRange], DeleteMode.EntireRow); //ken,用DeleteCells還是不行,測試結果似乎xlsx的圖表公式一直固定,不會更新 } //1.6 圖表範圍重選 ChartObject chartObjs = workbook.ChartSheets[$"{ws.Name}a"].Chart; ChartData newChartData = new ChartData(); //黃金 or 台幣黃金期貨總成交量 newChartData.RangeValue = ws.Range["D4:D" + (rowIndex + 1).AsString()]; chartObjs.Series[0].Values = newChartData; //黃金 or 台幣黃金期貨總未平倉量 newChartData.RangeValue = ws.Range["E4:E" + (rowIndex + 1).AsString()]; chartObjs.Series[1].Values = newChartData; //黃金 or 台幣黃金期貨價格 newChartData.RangeValue = ws.Range["B4:B" + (rowIndex + 1).AsString()]; chartObjs.Series[2].Values = newChartData; //現貨價格 newChartData.RangeValue = ws.Range["F4:F" + (rowIndex + 1).AsString()]; chartObjs.Series[3].Values = newChartData; return(1); }
protected override ResultStatus Export() { if (!StartExport()) { return(ResultStatus.Fail); } string lsFile = PbFunc.wf_copy_file(_ProgramID, "30350"); //message.OutputShowMessage只會儲存ok的狀態,如沒有任何一個ok代表全部function都沒有資料 MessageDisplay message = new MessageDisplay(); Workbook workbook = new Workbook(); //載入Excel workbook.LoadDocument(lsFile); try { string Txt = string.Empty; //輸入交易日期 string emMonthTxt = emMonth.Text; //前月倒數1天交易日 DateTime StartDate = PbFunc.f_get_last_day("AI2", "TXO", emMonthTxt, 1); //抓當月最後交易日 DateTime EndDate = PbFunc.f_get_end_day("AI2", "TXO", emMonthTxt); b30350 = new B30350(workbook, emMonthTxt, StartDate, EndDate); //30350_01 Txt = "臺指選擇權成交量及未平倉量變化表"; ShowMsg($"30350-{Txt} 轉檔中..."); message.OutputShowMessage = b30350.DataFrom30351(1, 33, "TXO", "30350_01", Txt); //30350_02 Txt = "金融選擇權成交量及未平倉量變化表"; ShowMsg($"30350-{Txt} 轉檔中..."); message.OutputShowMessage = b30350.DataFrom30351(1, 33, "TFO", "30350_02", Txt); //30350_03 Txt = "電子選擇權成交量及未平倉量變化表"; ShowMsg($"30350-{Txt} 轉檔中..."); message.OutputShowMessage = b30350.DataFrom30351(1, 33, "TEO", "30350_03", Txt); //30350_04 Txt = "摩臺選擇權成交量及未平倉量變化表"; ShowMsg($"30350-{Txt} 轉檔中..."); message.OutputShowMessage = b30350.DataFrom30351(1, 32, "MSO", "30350_04", Txt, B30350.Condition30350.sheet30350four); //30350_05 Txt = "非金電選擇權成交量及未平倉量變化表"; ShowMsg($"30350-{Txt} 轉檔中..."); message.OutputShowMessage = b30350.DataFrom30351(1, 32, "XIO", "30350_05", Txt, B30350.Condition30350.RowIndexAddOne); //30350_06 Txt = "櫃買選擇權成交量及未平倉量變化表"; ShowMsg($"30350-{Txt} 轉檔中..."); message.OutputShowMessage = b30350.DataFrom30351(1, 32, "GTO", "30350_06", Txt, B30350.Condition30350.RowIndexAddOne); //30350_07 Txt = "黃金選擇權成交量及未平倉量變化表"; ShowMsg($"30350-{Txt} 轉檔中..."); message.OutputShowMessage = b30350.DataFrom30351(1, 32, "TGO", "30350_07", Txt, B30350.Condition30350.RowIndexAddOne); //30350_08 Txt = "週臺指選擇權成交量及未平倉量變化表"; ShowMsg($"30350-{Txt} 轉檔中..."); message.OutputShowMessage = b30350.DataFrom30358(1, 33, "TXW", "30350_08", Txt, B30350.Condition30350.NoLastDay); //30350_09 Txt = "月臺指選擇權成交量及未平倉量變化表"; ShowMsg($"30350-{Txt} 轉檔中..."); message.OutputShowMessage = b30350.DataFrom30358(1, 33, "TXO", "30350_09", Txt, B30350.Condition30350.NoLastDay); //30350_10 Txt = "美元兌人民幣選擇權 成交量及未平倉量變化表"; ShowMsg($"30350-{Txt} 轉檔中..."); message.OutputShowMessage = b30350.DataFrom30351(1, 33, "RHO", "30350_10", Txt, B30350.Condition30350.NoLastMonth); //30350_11 Txt = "小型美元兌人民幣選擇權成交量及未平倉量變化表"; ShowMsg($"30350-{Txt} 轉檔中..."); message.OutputShowMessage = b30350.DataFrom30351(1, 33, "RTO", "30350_11", Txt, B30350.Condition30350.NoLastMonth); //存檔 workbook.SaveDocument(lsFile); //連續跳11次無資料刪除檔案 if (string.IsNullOrEmpty(message.OutputShowMessage)) { if (File.Exists(lsFile)) { File.Delete(lsFile); } return(ResultStatus.Fail); } } catch (Exception ex) { if (File.Exists(lsFile)) { File.Delete(lsFile); } WriteLog(ex); return(ResultStatus.Fail); } finally { EndExport(); } return(ResultStatus.Success); }