/// <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_30393_1 /// </summary> /// <param name="StartDate">前月倒數2天交易日</param> /// <param name="EndDate">抓當月最後交易日</param> /// <param name="IsKindID">商品代號</param> /// <param name="SheetName">工作表</param> /// <param name="RowIndex">Excel的Row位置</param> /// <param name="RowTotal">Excel的Column預留數</param> /// <returns></returns> public string Wf30393(DateTime StartDate, DateTime EndDate, string IsKindID, string SheetName, int RowIndex = 1, int RowTotal = 33) { try { Worksheet worksheet = _workbook.Worksheets[SheetName]; 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");//日期 } //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(); //C } worksheet.Rows[RowIndex][2 - 1].Value = row["AI3_CLOSE_PRICE"].AsDecimal(); //B worksheet.Rows[RowIndex][4 - 1].Value = row["AI3_M_QNTY"].AsDecimal(); //D worksheet.Rows[RowIndex][5 - 1].Value = row["AI3_OI"].AsDecimal(); //E worksheet.Rows[RowIndex][6 - 1].Value = row["AI3_INDEX"].AsDecimal(); //F } //刪除空白列 if (RowTotal > addRowCount) { worksheet.Rows.Remove(RowIndex + 1, RowTotal - addRowCount); //重新選取圖表範圍 ResetChartData(RowIndex + 1, _workbook, worksheet, SheetName.Replace($"({IsKindID})", "a"));//ex:30393_1a } //表尾 DataTable dtAI2 = daoAI2.ListAI2ym(IsKindID, EndDate.ToString("yyyyMM"), StartDate.ToString("yyyyMM")); if (dtAI2.Rows.Count <= 0) { return(MessageDisplay.MSG_OK); } int liDayCnt; //上月 RowIndex = RowIndex + 5; liDayCnt = dtAI2.Rows[0]["LAST_M_DAY_CNT"].AsInt(); if (liDayCnt > 0) { worksheet.Rows[RowIndex][5 - 1].Value = Math.Round(dtAI2.Rows[0]["LAST_M_QNTY"].AsDecimal() / liDayCnt, 0); worksheet.Rows[RowIndex][7 - 1].Value = Math.Round(dtAI2.Rows[0]["LAST_M_OI"].AsDecimal() / liDayCnt, 0); } //今年迄今 RowIndex = RowIndex + 2; liDayCnt = dtAI2.Rows[0]["Y_DAY_CNT"].AsInt(); if (liDayCnt > 0) { worksheet.Rows[RowIndex][5 - 1].Value = Math.Round(dtAI2.Rows[0]["Y_QNTY"].AsDecimal() / liDayCnt, 0); worksheet.Rows[RowIndex][7 - 1].Value = Math.Round(dtAI2.Rows[0]["Y_OI"].AsDecimal() / liDayCnt, 0); } } catch (Exception ex) { #if DEBUG throw new Exception($"Wf30393:" + ex.Message); #else throw ex; #endif } return(MessageDisplay.MSG_OK); }
/// <summary> /// 寫入30311_2(EXF)&30311_3(FXF) sheet /// </summary> /// <param name="lsKindID"></param> /// <param name="SheetName"></param> /// <returns></returns> public string Wf30310two(string lsKindID, string SheetName) { Workbook workbook = new Workbook(); try { //前月倒數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($"{StartDate.ToShortDateString()}~{EndDate.ToShortDateString()},30310-我國臺股期貨契約價量資料,{lsKindID}無任何資料!"); } 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); //重新選取圖表範圍 ResetChartData(rowIndex + 1, workbook, worksheet, SheetName.Replace($"({lsKindID})", "a")); //ex:30393_1a worksheet.ScrollTo(0, 0); //直接滾動到最上面,不然看起來很像少行數 } //表尾 dt = daoAI2.ListAI2ym(lsKindID, EndDate.ToString("yyyyMM"), StartDate.ToString("yyyyMM")); if (dt.Rows.Count <= 0) { return(MessageDisplay.MSG_OK); } 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 + 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); } worksheet.ScrollTo(0, 0); } catch (Exception ex) { throw ex; } finally { workbook.SaveDocument(_lsFile);//存檔 } return(MessageDisplay.MSG_OK); }
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); }