/// <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>
/// wf_30720()
/// </summary>
/// <returns></returns>
public string WF30720()
{
string newFilePath = _lsFile;
Workbook workbook = new Workbook();
try {
//交易時段
string lsMarketCode = string.Empty;
string marketTitle = string.Empty;
switch (_rgMarketVal)
{
case "rb_market0":
lsMarketCode = "0%";
marketTitle = "一般交易時段";
newFilePath = Rename(_lsFile, "_一般");
break;
case "rb_market1":
lsMarketCode = "1%";
marketTitle = "盤後交易時段";
newFilePath = Rename(_lsFile, "_盤後");
break;
default:
lsMarketCode = "%";
newFilePath = Rename(_lsFile, "_全部");
break;
}
//切換Sheet
workbook.LoadDocument(newFilePath);
Worksheet worksheet = workbook.Worksheets["30720"];
worksheet.Cells["A2"].Value = marketTitle;
//資料來源
D30720 dao30720 = new D30720();
DataTable dt = null;
string lsYMD = string.Empty;
DateTime dateMonth = _emMonthText.AsDateTime("yyyy/MM");
if (_rgDateVal.Equals("rb_month"))
{
//月
lsYMD = _emMonthText.Replace("/", "");
dt = dao30720.GetData("M", lsYMD, lsYMD, lsYMD, lsMarketCode);
worksheet.Cells["E1"].Value = $"本國期貨市場{dateMonth.Year - 1911}年{dateMonth.Month}月份" + worksheet.Cells["E1"].Value;
worksheet.Cells["E2"].Value = PbFunc.f_get_month_eng_name(dateMonth.Month, "1") + dateMonth.Year + worksheet.Cells["E2"].Value;
}
else
{
//年
lsYMD = _sleYearText;
dt = dao30720.GetData("Y", lsYMD, lsYMD + "01", lsYMD + "12", lsMarketCode);
worksheet.Cells["E1"].Value = $"本國期貨市場{lsYMD.AsInt() - 1911}年" + worksheet.Cells["E1"].Value;
worksheet.Cells["E2"].Value = lsYMD + worksheet.Cells["E2"].Value;
}
if (dt.Rows.Count <= 0)
{
File.Delete(newFilePath);
return($"{lsYMD},30720－月份交易量彙總表,無任何資料!");
}
foreach (DataRow dr in dt.Rows)
{
int rowIndex = dr["RPT_SEQ_NO"].AsInt();
for (int k = 2; k < 22; k++)
{
worksheet.Rows[rowIndex - 1][k].SetValue(dr[k]);
}
}
worksheet.ScrollTo(0, 0); //直接滾動到最上面，不然看起來很像少行數
workbook.SaveDocument(newFilePath); //存檔
}
catch (Exception ex) {
File.Delete(newFilePath);
#if DEBUG
throw new Exception("WF30720:" + ex.Message);
#else
throw ex;
#endif
}
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_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_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);
}
