/// <summary> /// wf_30561 /// </summary> /// <param name="RowIndex">Excel的Row位置</param> /// <param name="RowTotal">Excel的Column預留數</param> /// <returns></returns> public string Wf30561(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 D30560().List30561(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("/", "")},30561-{RptName},無任何資料!"); } /* 成交量 & OI */ DataTable dtAI2 = new D30560().List30561AI2(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("/", "")},30561-{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 */ //ai2_pc_code=C OImethod(rowCurr, worksheet, dtAI2, lsYMD, "C"); //ai2_pc_code=P OImethod(rowCurr, worksheet, dtAI2, lsYMD, "P"); }//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($"wf30561:" + ex.Message); #else throw ex; #endif } finally { workbook.SaveDocument(_lsFile); } return(MessageDisplay.MSG_OK); }
public void List30561Test() { DataTable data = new D30560().List30561("2003", "2018", "201801", "201810"); Assert.IsNotNull(data); }