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