/// <summary> /// wf_30412 (sheet3 data) /// </summary> /// <param name="workbook"></param> /// <param name="sheetNo">SheetNo.tradeDetail</param> /// <param name="row"> 4 </param> /// <returns></returns> protected bool wf_30412(Workbook workbook, SheetNo sheetNo, int row) { string rptName = "股票期貨各標的未平倉量分佈明細統計表"; //報表標題名稱 string rptId = "30412"; ShowMsg(string.Format("{0}-{1} 轉檔中...", rptId, rptName)); try { //1. 取得資料最大日期, 抓取OI用 (在wf_30410取得) string maxDate = new D30410().GetMaxDate(StartDate, EndDate); if (string.IsNullOrEmpty(maxDate)) { MessageDisplay.Info(string.Format("{0}~{1},{2} - {3},無任何資料!", StartDate, EndDate, rptId, rptName), GlobalInfo.ResultText); return(false); }//if (string.IsNullOrEmpty(maxDate)) DateTime eDate = DateTime.ParseExact(maxDate, "yyyyMMdd", null); //yyyy/MM/dd //2. 讀取資料 DataTable dt30412 = new D30410().ListData2(txtStartDate.DateTimeValue, eDate); if (dt30412.Rows.Count <= 0) { MessageDisplay.Info(string.Format("{0}~{1},{2} - {3},無任何資料!", txtStartDate.Text, txtEndDate.Text, rptId, rptName), GlobalInfo.ResultText); return(false); } //if (dt.Rows.Count <= 0 ) //3. 切換Sheet Worksheet ws30412 = workbook.Worksheets[(int)sheetNo]; ws30412.Range["A1"].Select(); ws30412.Cells[1, 0].Value = txtStartDate.Text; ws30412.Cells[1, 1].Value = txtEndDate.Text; //3.1 撈資料列總數 //PB這邊帶入參數為txnId = 30410 , txdId = 30410,兩者撈出皆為500 int rowCnt = new RPT().DataByRptId("30410", "30411").AsInt(); int rowTotal = row + rowCnt; //4.填資料(交割年月) int found = 0; int maxSeqNo = dt30412.Compute("Max(seq_no)", "").AsInt(); //取得seq_no欄位的最大值 for (int w = 1; w <= maxSeqNo; w++) { if (dt30412.Select("seq_no =" + w).Length != 0) { found = dt30412.Rows.IndexOf(dt30412.Select(string.Format("seq_no ={0}", w))[0]); } if (found >= 0) { string text = dt30412.Rows[found]["amif_settle_date"].AsString(); ws30412.Cells[3, w + 2].Value = text.SubStr(0, 4) + "/" + text.SubStr(4, 2); } } string kindId = ""; foreach (DataRow dr in dt30412.Rows) { string amifKindId = dr["amif_kind_id"].AsString(); string apdkName = dr["apdk_name"].AsString(); decimal amifOpenInt = dr["amif_open_interest"].AsDecimal(); found = dr["seq_no"].AsInt(); if (kindId != amifKindId) { kindId = amifKindId; row++; ws30412.Cells[row - 1, 0].Value = kindId; ws30412.Cells[row - 1, 1].Value = apdkName; } ws30412.Cells[row - 1, found + 2].Value = amifOpenInt; } //4. 刪除空白列 Range ra = ws30412.Range[string.Format("{0}:504", row + 1)]; ra.Delete(DeleteMode.EntireRow); ws30412.Range["A1"].Select(); ws30412.ScrollToRow(0); return(true); } catch (Exception ex) { WriteLog(ex); return(false); } }
/// <summary> /// wf_30410 (sheet1 data) /// </summary> /// <param name="workbook"></param> /// <param name="sheetNo">SheetNo.tradeSum</param> /// <param name="row"> 3 </param> /// <returns></returns> protected bool wf_30410(Workbook workbook, SheetNo sheetNo, int row) { string rptName = "股票期貨各標的交易概況統計表"; //報表標題名稱 labMsg.Text = _ProgramID + "-" + rptName + " 轉檔中..."; try { //1. 取得資料最大日期, 抓取OI用 string maxDate = new D30410().GetMaxDate(StartDate, EndDate); if (string.IsNullOrEmpty(maxDate)) { MessageDisplay.Info(string.Format("{0}~{1},{2} - {3},無任何資料!", StartDate, EndDate, _ProgramID, rptName), GlobalInfo.ResultText); return(false); }//if (string.IsNullOrEmpty(maxDate)) //2. 讀取資料 string eDate = maxDate; DataTable dt30410 = new D30410().ListData(StartDate, eDate); if (dt30410.Rows.Count <= 0) { MessageDisplay.Info(string.Format("{0}~{1},{2} - {3},無任何資料!", StartDate, eDate, _ProgramID, rptName), GlobalInfo.ResultText); } //if (dt.Rows.Count <= 0 ) //3. 切換Sheet Worksheet ws = workbook.Worksheets[(int)sheetNo]; ws.Cells[1, 0].Value = txtStartDate.Text; ws.Cells[1, 1].Value = txtEndDate.Text; int rowTotal = new RPT().DataByRptId("30410", "30410").AsInt(); //將rowTotal轉為int使用 rowTotal += row; string KindId = "19000101"; foreach (DataRow dr in dt30410.Rows) { row++; string ai2KindId = dr["ai2_kind_id"].AsString(); string apdkName = dr["apdk_name"].AsString(); int exRow = row - 1; if (KindId != ai2KindId) { KindId = ai2KindId; ws.Cells[exRow, 0].Value = KindId; ws.Cells[exRow, 1].Value = apdkName; } ws.Cells[exRow, 2].Value = dr["ai2_m_qnty"].AsDecimal(); ws.Cells[exRow, 3].Value = dr["ai2_oi"].AsDecimal(); ws.Cells[exRow, 4].Value = dr["am10_cnt"].AsDecimal(); ws.Cells[exRow, 6].Value = dr["am9_acc_cnt"].AsDecimal(); ws.Cells[exRow, 8].Value = dr["ab4_id_cnt"].AsDecimal(); } //4. 刪除空白列 if (dt30410.Rows.Count < rowTotal) { ws.Rows.Remove(row, rowTotal - row); } ws.Range["A1"].Select(); ws.ScrollToRow(0); return(true); } catch (Exception ex) { WriteLog(ex); return(false); } }