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