/// <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); }
/// <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 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() { 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); }
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); }
/// <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); }