private bool StartExport() { /* 條件值檢核*/ DateTime ldStart, ldEnd; string lsType = ""; switch (rgDate.EditValue) { case "rb_day": //週 if (!emStartDate.IsDate(emStartDate.Text, CheckDate.Start) || !emEndDate.IsDate(emEndDate.Text, CheckDate.End)) { return(false); } if (string.Compare(emStartDate.Text, emEndDate.Text) > 0) { MessageDisplay.Error(GlobalInfo.ErrorText, CheckDate.Datedif); return(false); } ldStart = Convert.ToDateTime(emStartDate.Text); ldEnd = Convert.ToDateTime(emEndDate.Text); startYMD = emStartDate.Text.Replace("/", "").SubStr(0, 8); endYMD = emEndDate.Text.Replace("/", "").SubStr(0, 8); lsType = "Day"; sumType = "D"; logText = ldStart.ToString("yyyy.MM.dd") + "至" + ldEnd.ToString("yyyy.MM.dd") + " 交易量"; break; case "rb_week": //週 if (!emStartDate1.IsDate(emStartDate1.Text, CheckDate.Start) || !emEndDate1.IsDate(emEndDate1.Text, CheckDate.End)) { return(false); } if (string.Compare(emStartDate1.Text, emEndDate1.Text) > 0) { MessageDisplay.Error(GlobalInfo.ErrorText, CheckDate.Datedif); return(false); } ldStart = Convert.ToDateTime(emStartDate1.Text); ldEnd = Convert.ToDateTime(emEndDate1.Text); startYMD = emStartDate1.Text.Replace("/", "").SubStr(0, 8); endYMD = emEndDate1.Text.Replace("/", "").SubStr(0, 8); lsType = "Week"; sumType = "D"; logText = ldStart.ToString("yyyy.MM.dd") + "至" + ldEnd.ToString("yyyy.MM.dd") + " 交易量"; break; case "rb_month": //月 string emSmth = emStartMth.Text + "/01"; string emEmth = emEndMth.Text + "/01"; if (!emStartMth.IsDate(emSmth, CheckDate.Start) || !emEndMth.IsDate(emEmth, CheckDate.End)) { return(false); } ldStart = Convert.ToDateTime(emSmth); ldEnd = PbFunc.relativedate(Convert.ToDateTime(emEmth), 31); if (ldEnd.Month != PbFunc.Right(emStartMth.Text, 2).AsInt()) { ldEnd = PbFunc.relativedate(ldEnd, -ldEnd.Day); } startYMD = emStartMth.Text.Replace("/", "").SubStr(0, 6); endYMD = emEndMth.Text.Replace("/", "").SubStr(0, 6); lsType = "Month"; sumType = "M"; logText = startYMD + "至" + endYMD + " 交易量"; break; case "rb_year": //年 startYMD = emStartYear.Text; endYMD = emEndYear.Text; lsType = "Year"; sumType = "Y"; logText = startYMD + "至" + endYMD + " 交易量"; break; default: break; } saveFilePath = _ProgramID + "_" + lsType + "(" + startYMD + "-" + endYMD + ")"; //期別 switch (rgPeriod.EditValue) { case "rb_txw"://一週到期契約 lsKindId2 = "TXW%"; saveFilePath = saveFilePath + "W"; break; case "rb_txo"://一般天期契約 lsKindId2 = "TXO%"; saveFilePath = saveFilePath + "S"; break; default://所有天期契約 lsKindId2 = "%"; break; } /*點選儲存檔案之目錄*/ saveFilePath = PbFunc.wf_GetFileSaveName(saveFilePath + ".csv"); if (string.IsNullOrEmpty(saveFilePath)) { return(false); } /******************* * Messagebox *******************/ st_msg_txt.Visible = true; st_msg_txt.Text = "開始轉檔..."; st_msg_txt.Text = logText + " 轉檔中..."; this.Cursor = Cursors.WaitCursor; this.Refresh(); Thread.Sleep(5); return(true); }
/// <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); }
/// <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); }
private bool StartExport() { /* 條件值檢核*/ DateTime ldStart, ldEnd; string lsType = ""; switch (rgDate.EditValue) { case "rb_day": //週 if (!emStartDate.IsDate(emStartDate.Text, CheckDate.Start) || !emEndDate.IsDate(emEndDate.Text, CheckDate.End)) { return(false); } if (string.Compare(emStartDate.Text, emEndDate.Text) > 0) { MessageDisplay.Error(GlobalInfo.ErrorText, CheckDate.Datedif); return(false); } ldStart = Convert.ToDateTime(emStartDate.Text); ldEnd = Convert.ToDateTime(emEndDate.Text); lsSymd = emStartDate.Text.Replace("/", "").SubStr(0, 8); lsEymd = emEndDate.Text.Replace("/", "").SubStr(0, 8); lsType = "Daily"; sumType = "D"; logText = ldStart.ToString("yyyy.MM.dd") + "至" + ldEnd.ToString("yyyy.MM.dd") + " 交易量"; break; case "rb_week": //週 if (!emStartDate1.IsDate(emStartDate1.Text, CheckDate.Start) || !emEndDate1.IsDate(emEndDate1.Text, CheckDate.End)) { return(false); } if (string.Compare(emStartDate1.Text, emEndDate1.Text) > 0) { MessageDisplay.Error(GlobalInfo.ErrorText, CheckDate.Datedif); return(false); } ldStart = Convert.ToDateTime(emStartDate1.Text); ldEnd = Convert.ToDateTime(emEndDate1.Text); lsSymd = emStartDate1.Text.Replace("/", "").SubStr(0, 8); lsEymd = emEndDate1.Text.Replace("/", "").SubStr(0, 8); lsType = "Weekly"; sumType = "D"; logText = ldStart.ToString("yyyy.MM.dd") + "至" + ldEnd.ToString("yyyy.MM.dd") + " 交易量"; break; case "rb_month": //月 string Smth = this.emStartMth.Text + "/01"; string Emth = this.emEndMth.Text + "/01"; if (!this.emStartMth.IsDate(Smth, CheckDate.Start) || !this.emEndMth.IsDate(Emth, CheckDate.End)) { return(false); } ldStart = Convert.ToDateTime(Smth); ldEnd = PbFunc.relativedate(Convert.ToDateTime(Emth), 31); if (ldEnd.Month != PbFunc.Right(emStartMth.Text, 2).AsInt()) { ldEnd = PbFunc.relativedate(ldEnd, -ldEnd.Day); } lsSymd = emStartMth.Text.Replace("/", "").SubStr(0, 6); lsEymd = emEndMth.Text.Replace("/", "").SubStr(0, 6); lsType = "Monthly"; sumType = "M"; logText = lsSymd + "至" + lsEymd + " 交易量"; break; case "rb_year": //年 lsSymd = emStartYear.Text; lsEymd = emEndYear.Text; lsType = "Yearly"; sumType = "Y"; logText = lsSymd + "至" + lsEymd + " 交易量"; break; default: break; } //商品別 if (rbTMU.EditValue.Equals("rb_options")) { lsType = lsType + "_OPT"; lsProdType = "O"; } else { lsType = lsType + "_FUT"; lsProdType = "F"; } //ids_1.dataobject = "d_"+gs_txn_id if (cbxEng.Checked) { lsType = lsType + "_eng"; } //交易時段 switch (rgTime.EditValue) { case "rb_market0": lsMarketCode = "0"; lsType = lsType + "_day"; break; case "rb_market1": lsMarketCode = "1"; lsType = lsType + "_night"; break; default: lsMarketCode = "%"; break; } /*點選儲存檔案之目錄*/ saveFilePath = PbFunc.wf_GetFileSaveName(lsType + "(" + lsSymd + "-" + lsEymd + ").csv"); if (string.IsNullOrEmpty(saveFilePath)) { return(false); } /******************* * Messagebox *******************/ stMsgTxt.Visible = true; stMsgTxt.Text = "開始轉檔..."; stMsgTxt.Text = logText + " 轉檔中..."; this.Cursor = Cursors.WaitCursor; this.Refresh(); Thread.Sleep(5); return(true); }
/// <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_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_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); }