/// <summary>
/// wf_30403 (sheet3 data)
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheetNo">SheetNo.tradeSum</param>
/// <param name="row"> 0 </param>
/// <returns></returns>
protected int wf_30403(Workbook workbook, SheetNo sheetNo, int rowNum)
{
string rptName = "股票期貨交易概況表";
ShowMsg("30403－" + rptName + " 轉檔中...");
try {
//1. 抓當月最後交易日
string strMon = txtMon.Text.Replace("/", "");
string lastTradeDate = dao30400.GetThisMonLastTradeData(strMon); //當月最後交易日(yyyyMMdd)
string sDate = strMon + "01"; //當月第一天
//2. 讀取資料
DataTable dt30403 = dao30400.Get30403Data(sDate, lastTradeDate);
if (dt30403.Rows.Count <= 0)
{
DateTime startDate = DateTime.ParseExact(sDate, "yyyyMMdd", null);
MessageDisplay.Info(String.Format("{0}~,30403 - {1},無任何資料!", startDate.ToString("yyyy/MM/dd"), rptName), GlobalInfo.ResultText);
return(0);
} //if (dt30401.Rows.Count <= 0)
//3. 切換Sheet
Worksheet ws3 = workbook.Worksheets[(int)sheetNo];
//4. 處理資料
string kindId = "";
foreach (DataRow dr in dt30403.Rows)
{
string kindId2 = dr["ai2_kind_id_2"].AsString();
string pdkName = dr["pdk_name"].AsString();
if (kindId != kindId2)
{
kindId = kindId2;
rowNum++;
ws3.Cells[rowNum, 0].Value = kindId;
ws3.Cells[rowNum, 1].Value = pdkName;
}
int colNum = dr["seq_no"].AsInt();
if (colNum > 0)
{
string ymd = dr["ai2_ymd"].AsString();
string tmpYmd = DateTime.ParseExact(ymd, "yyyyMMdd", null).ToString("yyyy/MM/dd");
decimal mQnty = dr["ai2_m_qnty"].AsDecimal();
ws3.Cells[0, colNum + 1].Value = tmpYmd;
ws3.Cells[rowNum, colNum + 1].Value = mQnty;
}
}//foreach (DataRow dr in dt30403.Rows)
ws3.Range["A1"].Select();
ws3.ScrollToRow(0);
return(1);
} catch (Exception ex) {
WriteLog(ex);
return(0);
}
}
/// <summary>
/// wf_30417 (sheet1 data)
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheetNo">SheetNo.tradeSum</param>
/// <param name="rowNum"> 2 </param>
/// <returns></returns>
protected bool wf_30417(Workbook workbook, SheetNo sheetNo, int rowNum, DataTable dtYmd)
{
string rptName = "股票期貨每週交概況統計表"; //報表標題名稱
labMsg.Text = _ProgramID + "－" + rptName + " 轉檔中...";
try {
//1. 切換Sheet
Worksheet ws30417 = workbook.Worksheets[(int)sheetNo];
//2. 讀取資料
DataTable dt = new D30417().ListData(StartDate, EndDate);
if (dt.Rows.Count <= 0)
{
MessageDisplay.Info(String.Format("{0}~{1},{2} - {3},無任何資料!", StartDate, EndDate, _ProgramID, rptName), GlobalInfo.ResultText);
return(false);
} //if (dt.Rows.Count <= 0 )
//3. 日期
int pos = 0;
foreach (DataRow dr in dtYmd.Rows)
{
pos++;
//將週日期區間數量相加
string ai2Ymd = dr["startDate"].AsString().Replace("/", "");
string ymdEnd = dr["endDate"].AsString().Replace("/", "");
DataTable dtTmp = dt.Filter(string.Format("ai2_ymd>='{0}' and ai2_ymd<='{1}'", ai2Ymd, ymdEnd));
if (dtTmp.Rows.Count > 0)
{
int tmpRow = pos + 1;
decimal sumDayCount = dtTmp.Compute("Sum(ai2_day_count)", "").AsDecimal();
decimal sumMQnty = dtTmp.Compute("Sum(ai2_m_qnty)", "").AsDecimal();
decimal sumOi = dtTmp.Compute("Sum(ai2_oi)", "").AsDecimal();
decimal sumCnt = dtTmp.Compute("Sum(am10_cnt)", "").AsDecimal();
decimal sumAccCnt = dtTmp.Compute("Sum(am9_acc_cnt)", "").AsDecimal();
string sDate = dr["startDate"].AsString().SubStr(0, 10);
string eDate = dr["endDate"].AsString().SubStr(0, 10);
ws30417.Cells[tmpRow, 0].Value = sumDayCount;
ws30417.Cells[tmpRow, 1].Value = string.Format("{0}～{1}", sDate, eDate);
ws30417.Cells[tmpRow, 2].Value = sumMQnty;
ws30417.Cells[tmpRow, 4].Value = sumOi;
ws30417.Cells[tmpRow, 6].Value = sumCnt;
ws30417.Cells[tmpRow, 9].Value = sumAccCnt;
} //if (dtTmp.Rows.Count > 0)
} //foreach (DataRow dr in dtYm.Rows)
ws30417.Range["A1"].Select();
ws30417.ScrollToRow(0);
return(true);
} catch (Exception ex) {
WriteLog(ex);
return(false);
}
}
/// <summary>
/// wf_30402 (sheet2 & sheet8 data)
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheetNo">SheetNo.tradeSum</param>
/// <param name="row"> 1 </param>
/// <returns></returns>
protected int wf_30402(Workbook workbook, SheetNo sheetNo, int rowNum)
{
string rptName = "股票期貨交易概況表";
ShowMsg("30402－" + rptName + " 轉檔中...");
try {
string strMon = txtMon.Text.Replace("/", "");
//1. 讀取資料
DataTable dt30402 = dao30400.Get30402Data(strMon, "F");
if (dt30402.Rows.Count <= 0)
{
MessageDisplay.Info(String.Format("{0},30402 - {1},無任何資料!", strMon, rptName), GlobalInfo.ResultText);
return(0);
} //if (dt30401.Rows.Count <= 0)
//2. 切換Sheet
Worksheet ws2 = workbook.Worksheets[(int)sheetNo];
//3. 處理資料
int rowTotal = 1001;
foreach (DataRow dr in dt30402.Rows)
{
string pdkName = dr["pdk_name"].AsString();
string kindId2 = dr["kind_id_2"].AsString();
decimal mQnty = dr["m_qnty"].AsDecimal();
ws2.Cells[rowNum, 0].Value = string.Format("{0}({1})", pdkName, kindId2);
ws2.Cells[rowNum, 1].Value = mQnty;
ws2.Cells[rowNum, 3].Value = pdkName;
rowNum++;
}//foreach (DataRow dr in dt30402.Rows)
rowNum--;
//4. 刪除空白列
if (rowTotal > dt30402.Rows.Count)
{
Range ra = ws2.Range[(dt30402.Rows.Count + 2).ToString() + ":" + rowTotal.ToString()];
ra.Delete(DeleteMode.EntireRow);
}
ws2.Range["A1"].Select();
ws2.ScrollToRow(0);
return(1);
} catch (Exception ex) {
WriteLog(ex);
return(0);
}
}
/// <summary>
/// wf_30408 (sheet8 data)
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheetNo">SheetNo.tradeSum</param>
/// <param name="row"> 1 </param>
/// <returns></returns>
protected int wf_30408(Workbook workbook, SheetNo sheetNo, int rowNum)
{
string rptName = "Top 30 股票期貨成交量";
try {
string strMon = txtMon.Text.Replace("/", "");
//1. 讀取資料
DataTable dt30402 = dao30400.Get30402Data(strMon, "F");
if (dt30402.Rows.Count <= 0)
{
MessageDisplay.Info(String.Format("{0},30402 - {1},無任何資料!", strMon, rptName), GlobalInfo.ResultText);
return(0);
} //if (dt30401.Rows.Count <= 0)
//1.1 sort dt
dt30402.DefaultView.Sort = "m_qnty desc , kind_id_2 asc";
dt30402 = dt30402.DefaultView.ToTable();
//2. 切換Sheet
Worksheet ws8 = workbook.Worksheets[(int)sheetNo];
//3. 處理資料
int cnt = Math.Min(30, dt30402.Rows.Count);
for (int w = 0; w < cnt; w++)
{
rowNum++;
DataRow dr = dt30402.Rows[w];
decimal totalQnty = dt30402.Compute("SUM(m_qnty)", "").AsDecimal();
string kindId2 = dr["kind_id_2"].AsString();
string pdkName = dr["pdk_name"].AsString();
decimal mQnty = dr["m_qnty"].AsDecimal();
ws8.Cells[rowNum, 1].Value = kindId2;
ws8.Cells[rowNum, 2].Value = pdkName;
ws8.Cells[rowNum, 3].Value = mQnty;
ws8.Cells[rowNum, 4].Value = mQnty / totalQnty;
}//for (int w = 0 ; w < cnt ; w++)
ws8.Range["A1"].Select();
ws8.ScrollToRow(0);
return(1);
} catch (Exception ex) {
WriteLog(ex);
return(0);
}
}
/// <summary>
/// wf_40152
/// </summary>
/// <returns></returns>
protected bool wf_40152(Workbook workbook, SheetNo sheetNo)
{
try {
labMsg.Text = "訊息：40152-" + _ProgramName + "轉檔中...";
//1. 切換Sheet
Worksheet worksheet = workbook.Worksheets[(int)sheetNo];
worksheet.Range["A1"].Select();
//2. 填資料
DataTable dt2 = new D40150().ListByDate(txtStartDate.DateTimeValue);
if (dt2.Rows.Count <= 0)
{
MessageDisplay.Info(string.Format("{0},讀取「SPAN參數一覽表」無任何資料!", txtStartDate.Text), GlobalInfo.ResultText);
return(false);
}//if (dt.Rows.Count <= 0 )
DataTable dtSp2 = new D40150().ListSp2ByDate(txtStartDate.DateTimeValue);
//int row = 1;
foreach (DataRow dr in dt2.Rows)
{
int row = dr["rpt_seq_no"].AsInt();
string sp1KindId1 = dr["sp1_kind_id1"].AsString();
string txt = "";
//SS
txt = "sp2_type='SV' and sp2_kind_id1='" + sp1KindId1 + "'";
DataRow[] drtmpSp2 = dtSp2.Select(txt);
if (drtmpSp2.Length != 0) //不是空陣列
{
worksheet.Cells[row - 1, 1].Value = dr["SD_sp1_rate"].AsDecimal();
worksheet.Cells[row - 1, 1].Font.Bold = true;
}
else
{
worksheet.Cells[row - 1, 1].Value = dr["SD_sp1_cur_rate"].AsDecimal();
} //if (found > 0)
} //foreach (DataRow dr in dt.Rows)
return(true);
} catch (Exception ex) {
WriteLog(ex);
return(false);
}
}
/// <summary>
/// wf_40150
/// </summary>
/// <returns></returns>
protected bool wf_40150(Workbook workbook, SheetNo sheetNo)
{
try {
//1. 切換Sheet
Worksheet worksheet = workbook.Worksheets[(int)sheetNo];
worksheet.Range["A1"].Select();
//2. 填資料
DataTable dt = new D40150().GetDataList(txtStartDate.DateTimeValue);
if (dt.Rows.Count <= 0)
{
MessageDisplay.Info(string.Format("{0},讀取「SPAN參數一覽表」無任何資料!", txtStartDate.Text), GlobalInfo.ResultText);
return(false);
}//if (dt.Rows.Count <= 0 )
DataTable dtSp2 = new D40150().ListSp2ByDate(txtStartDate.DateTimeValue);
int row = 1;
foreach (DataRow dr in dt.Rows)
{
row++;
worksheet.Cells[row - 1, 0].Value = dr["sp1_seq_no"].AsInt();
worksheet.Cells[row - 1, 1].Value = dr["spt1_com_id"].AsString();
worksheet.Cells[row - 1, 4].Value = dr["spt1_kind_id1_out"].AsString();
worksheet.Cells[row - 1, 7].Value = dr["spt1_kind_id2_out"].AsString();
string sp1KindId1 = dr["sp1_kind_id1"].AsString();
string sp1KindId2 = dr["sp1_kind_id2"].AsString();
string txt = "";
//SS
txt = "sp2_type='SS' and sp2_kind_id1='" + sp1KindId1 + "' and sp2_kind_id2='" + sp1KindId2 + "' ";
DataRow[] drtmp = dtSp2.Select(txt);
if (drtmp.Length != 0) //不是空陣列
{
worksheet.Cells[row - 1, 2].Value = dr["SS_sp1_rate"].AsDecimal();
worksheet.Cells[row - 1, 2].Font.Bold = true;
}
else
{
worksheet.Cells[row - 1, 2].Value = dr["SS_sp1_cur_rate"].AsDecimal();
}//if (drtmp.Length != 0)
//SD
txt = "sp2_type='SD' and sp2_kind_id1='" + sp1KindId1 + "' and sp2_kind_id2='" + sp1KindId2 + "' ";
DataRow[] drtmp2 = dtSp2.Select(txt);
if (drtmp2.Length != 0) //不是空陣列
{
worksheet.Cells[row - 1, 6].Value = dr["SD_sp1_rate"].AsDecimal();
worksheet.Cells[row - 1, 6].Font.Bold = true;
}
else
{
worksheet.Cells[row - 1, 6].Value = dr["SD_sp1_cur_rate"].AsDecimal();
} //if (drtmp2.Length != 0)
} //foreach (DataRow dr in dt.Rows)
return(true);
} catch (Exception ex) {
WriteLog(ex);
return(false);
}
}
protected bool wfExport(Workbook workbook, SheetNo sheetNo)
{
try {
string rptName = "Eurex FTX vs TX 振幅、波動度及交易量統計(總表)";
ShowMsg(string.Format("{0}－{1} 轉檔中...", _ProgramID, rptName));
//1.讀取資料
DataTable dt = new DataTable();
if ((int)sheetNo == 0)
{
dt = dao30660.GetData(PrevStart, PrevEnd, AftStart, AftEnd, AllStart, AllEnd); // Eurex vs TX
}
else
{
dt = dao30660.GetDetailData(AllStart, AllEnd); //每日明細
}
if (dt.Rows.Count <= 0)
{
MessageDisplay.Info(string.Format("{0},{1},無任何資料!", AllStart + "-" + AllEnd, this.Text), GlobalInfo.ResultText);
return(false);
}
//2.切換Sheet填資料
Worksheet worksheet = workbook.Worksheets[(int)sheetNo];
if ((int)sheetNo == 0)
{
li_ole_row = 3;
for (int i = 0; i < dt.Rows.Count; i++)
{
li_ole_row += 1;
for (int j = 0; j < 14; j++)
{
if (j == 0)
{
worksheet.Cells[li_ole_row - 1, j].Value = dt.Rows[i][j].AsString();
}
else
{
if (dt.Rows[i][j] != DBNull.Value)
{
worksheet.Cells[li_ole_row - 1, j].Value = dt.Rows[i][j].AsDecimal();
}
}
}
}
}
else
{
li_ole_row = 3;
for (int i = 0; i < dt.Rows.Count; i++)
{
li_ole_row += 1;
for (int j = 0; j < 19; j++)
{
if (j == 0)
{
worksheet.Cells[li_ole_row - 1, j].Value = dt.Rows[i][j].AsDateTime();
}
else
{
if (dt.Rows[i][j] != DBNull.Value)
{
worksheet.Cells[li_ole_row - 1, j].Value = dt.Rows[i][j].AsDecimal();
}
}
}
}
}
return(true);
} catch (Exception ex) {
WriteLog(ex);
return(false);
} finally {
panFilter.Enabled = true;
labMsg.Text = "";
labMsg.Visible = false;
this.Cursor = Cursors.Arrow;
}
}
/// <summary>
/// wf_30416 (sheet3 data)
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheetNo">SheetNo.tradeSum</param>
/// <param name="rowNum"> 2 </param>
/// <returns></returns>
protected bool wf_30416(Workbook workbook, SheetNo sheetNo, int rowNum, DataTable dtTmp)
{
string rptName = "股票期貨各標的每月日均未平倉量統計表"; //報表標題名稱
labMsg.Text = "30416－" + rptName + " 轉檔中...";
try {
#region 30416
//1. 表頭
DataTable dtProd = new D30414().ListProdByMon(StartMon, EndMon);
if (dtProd.Rows.Count <= 0)
{
MessageDisplay.Info(string.Format("{0}~{1},30416 - 商品檔,無任何資料!", StartMon, EndMon), GlobalInfo.ResultText);
return(false);
}//if (dtProd.Rows.Count <= 0)
//切換Sheet
Worksheet ws30416 = workbook.Worksheets[(int)sheetNo];
for (int w = 0; w < dtProd.Rows.Count; w++)
{
rowNum++;
string apdkName = dtProd.Rows[w]["apdk_name"].AsString();
string kindId = dtProd.Rows[w]["ai2_kind_id"].AsString();
int rowTmp = rowNum - 1;
ws30416.Cells[rowTmp, 0].Value = w + 1;
ws30416.Cells[rowTmp, 1].Value = string.Format("{0}({1})", apdkName, kindId);
}//for (int w = 0 ; w < dtProd.Rows.Count ; w++)
int colNum = 2;
foreach (DataRow dr in dtTmp.Rows)
{
colNum++;
int tmpCol = colNum - 1;
string tmpYm = dr["ai2_ymd"].AsString();
if (tmpYm.Length > 4)
{
tmpYm = string.Format("{0}年{1}月", tmpYm.SubStr(0, 4), tmpYm.SubStr(4, 2));
}
else
{
tmpYm = string.Format("{0}年全年", tmpYm.SubStr(0, 4));
}
ws30416.Cells[1, tmpCol].Value = tmpYm;
}//foreach (DataRow dr in dtTmp.Rows)
DataTable dt30416 = new D30414().ListDataByMon(StartMon, EndMon);
if (dt30416.Rows.Count <= 0)
{
MessageDisplay.Info(string.Format("{0}~{1},30416 - 股票期貨各標的每月日均未平倉量統計表,無任何資料!", StartMon, EndMon), GlobalInfo.ResultText);
return(false);
}//if (dt30415.Rows.Count <= 0)
DataRow insertRow2 = dt30416.NewRow();
insertRow2["ai2_ymd"] = "999999";
insertRow2["ai2_kind_id"] = "end";
dt30416.Rows.Add(insertRow2);
int pot = 0;
foreach (DataRow dr in dt30416.Rows)
{
pot++;
string kindId = dr["ai2_kind_id"].AsString();
string ai2Ymd = dr["ai2_ymd"].AsString();
decimal avgOi = dr["avg_oi"].AsDecimal();
if (pot == dt30416.Rows.Count)
{
continue;
}
dtProd.PrimaryKey = new DataColumn[] { dtProd.Columns["ai2_kind_id"] };
dtTmp.PrimaryKey = new DataColumn[] { dtTmp.Columns["ai2_ymd"] };
int found = dtProd.Rows.IndexOf(dtProd.Rows.Find(dr["ai2_kind_id"])).AsInt();
int foundCol = dtTmp.Rows.IndexOf(dtTmp.Rows.Find(dr["ai2_ymd"])).AsInt();
if (found >= 0 && foundCol >= 0)
{
ws30416.Cells[found + 2, foundCol + 2].Value = avgOi;
}//if (found >= 0 && foundCol >= 0)
//年度
string year = dr["ai2_ymd"].AsString().SubStr(0, 4);
string nextYear = dt30416.Rows[pot]["ai2_ymd"].AsString().SubStr(0, 4);
string nextKindId = dt30416.Rows[pot]["ai2_kind_id"].AsString();
if (year != nextYear || kindId != nextKindId)
{
foundCol = dtTmp.Rows.IndexOf(dtTmp.Rows.Find(dr["ai2_ymd"])).AsInt();
decimal sumDayCount = dt30416.Compute("Sum(ai2_day_count)", "ai2_kind_id = '" + kindId + "'").AsDecimal(); //取得ai2_day_count欄位的總和
decimal sumOi = dt30416.Compute("Sum(ai2_oi)", "ai2_kind_id = '" + kindId + "'").AsDecimal(); //ai2_oi
if (found >= 0 && foundCol >= 0)
{
decimal yAvgOi;
if (sumDayCount == 0)
{
yAvgOi = 0;
}
else
{
yAvgOi = Math.Round(sumOi / sumDayCount, 0, MidpointRounding.AwayFromZero);
}
int tmp = 12 - ai2Ymd.SubStr(4, 2).AsInt() + 1;
ws30416.Cells[found + 2, foundCol + 2 + tmp].Value = yAvgOi;
} //if (found >= 0 && foundCol >= 0)
}
} //foreach (DataRow dr in dt30415.Rows)
#endregion
ws30416.Range["A1"].Select();
ws30416.ScrollToRow(0);
return(true);
} catch (Exception ex) {
WriteLog(ex);
return(false);
}
}
/// <summary>
/// wf_30414 (sheet1 data)
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheetNo">SheetNo.tradeSum</param>
/// <param name="rowNum"> 2 </param>
/// <returns></returns>
protected bool wf_30414(Workbook workbook, SheetNo sheetNo, int rowNum, DataTable dtTmp)
{
string rptName = "股票期貨每月交易概況統計表"; //報表標題名稱
labMsg.Text = _ProgramID + "－" + rptName + " 轉檔中...";
try {
//1. 切換Sheet
Worksheet ws = workbook.Worksheets[(int)sheetNo];
//2. 讀取資料
DataTable dt = new D30414().ListData(StartMon, EndMon);
if (dt.Rows.Count <= 0)
{
MessageDisplay.Info(String.Format("{0}~{1},{2} - {3},無任何資料!", StartMon, EndMon, _ProgramID, rptName), GlobalInfo.ResultText);
return(false);
} //if (dt.Rows.Count <= 0 )
DataRow insertRow = dt.NewRow();
insertRow["ai2_ymd"] = "999999";
dt.Rows.Add(insertRow);
int pos = 0;
foreach (DataRow row in dt.Rows)
{
pos++;
if (pos == dt.Rows.Count)
{
continue; //執行到最後一列執行continue
}
string ai2Ymd = row["ai2_ymd"].AsString();
decimal ai2DayCount = row["ai2_day_count"].AsDecimal();
decimal ai2MQnty = row["ai2_m_qnty"].AsDecimal();
decimal ai2Oi = row["ai2_oi"].AsDecimal();
decimal am10Cnt = row["am10_cnt"].AsDecimal();
decimal am9AccCnt = row["am9_acc_cnt"].AsDecimal();
dtTmp.PrimaryKey = new DataColumn[] { dtTmp.Columns["ai2_ymd"] };
int found = dtTmp.Rows.IndexOf(dtTmp.Rows.Find(row["ai2_ymd"].AsString().Trim())).AsInt();
if (found >= 0)
{
rowNum = found + 2;
}//if(found >= 0)
ws.Cells[rowNum, 0].Value = ai2DayCount;
ws.Cells[rowNum, 1].Value = string.Format("{0}年{1}月", ai2Ymd.SubStr(0, 4), ai2Ymd.SubStr(4, 2));
ws.Cells[rowNum, 2].Value = ai2MQnty;
ws.Cells[rowNum, 4].Value = ai2Oi;
if (row["am10_cnt"] != DBNull.Value)
{
ws.Cells[rowNum, 6].Value = am10Cnt;
}
ws.Cells[rowNum, 9].Value = am9AccCnt;
//年度總計
string year = ai2Ymd.SubStr(0, 4);
decimal sumDayCount = dt.Compute("Sum(ai2_day_count)", "").AsDecimal(); //取得ai2_day_count欄位的總和
decimal sumMQnty = dt.Compute("Sum(ai2_m_qnty)", "").AsDecimal(); //取得ai2_m_qnty欄位的總和
decimal sumOi = dt.Compute("Sum(ai2_oi)", "").AsDecimal(); //取得ai2_m_qnty欄位的總和
decimal sumCnt = dt.Compute("Sum(am10_cnt)", "").AsDecimal(); //取得am10_cnt欄位的總和
decimal sumAccCnt = dt.Compute("Sum(am9_acc_cnt)", "").AsDecimal(); //取得am9_acc_cnt欄位的總和
string nextYear = dt.Rows[pos]["ai2_ymd"].AsString().SubStr(0, 4);
if (year != nextYear)
{
found = dtTmp.Rows.IndexOf(dtTmp.Rows.Find(row["ai2_ymd"])).AsInt(); //found因C#索引從0開始所以少1
if (found >= 0)
{
int addRow = 12 - (row["ai2_ymd"].AsString().SubStr(4, 2).AsInt()); //補滿12月的列數
rowNum = found + 3 + addRow;
}
ws.Cells[rowNum, 0].Value = sumDayCount;
ws.Cells[rowNum, 1].Value = string.Format("{0}年(至執行當月底)", year);
ws.Cells[rowNum, 2].Value = sumMQnty;
ws.Cells[rowNum, 4].Value = sumOi;
ws.Cells[rowNum, 6].Value = sumCnt;
ws.Cells[rowNum, 9].Value = sumAccCnt;
} //if (year != nextYear)
} //foreach (DataRow row in dt.Rows)
ws.Range["A1"].Select();
ws.ScrollToRow(0);
return(true);
} catch (Exception ex) {
WriteLog(ex);
return(false);
}
}
/// <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_30419 (sheet3 data)
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheetNo">SheetNo.tradeSum</param>
/// <param name="rowNum"> 2 </param>
/// <returns></returns>
protected bool wf_30419(Workbook workbook, SheetNo sheetNo, int rowNum, DataTable dtYmd)
{
string rptName = "股票期貨各標的每週日均未平倉量統計表"; //報表標題名稱
labMsg.Text = "30419－" + rptName + " 轉檔中...";
try {
#region 30419
//1. 表頭
DataTable dtProd = new D30414().ListProdByMon(StartMon, EndMon);
if (dtProd.Rows.Count <= 0)
{
MessageDisplay.Info(string.Format("{0}~{1},30419 - 商品檔,無任何資料!", StartMon, EndMon), GlobalInfo.ResultText);
return(false);
}//if (dtProd.Rows.Count <= 0)
//2. 切換Sheet
Worksheet ws30419 = workbook.Worksheets[(int)sheetNo];
//3. 讀取資料
for (int w = 0; w < dtProd.Rows.Count; w++)
{
rowNum++;
string apdkName = dtProd.Rows[w]["apdk_name"].AsString();
string kindId = dtProd.Rows[w]["ai2_kind_id"].AsString();
int rowTmp = rowNum - 1;
ws30419.Cells[rowTmp, 0].Value = w + 1;
ws30419.Cells[rowTmp, 1].Value = string.Format("{0}({1})", apdkName, kindId);
}//for (int w = 0 ; w < dtProd.Rows.Count ; w++)
int colNum = 2;
foreach (DataRow dr in dtYmd.Rows)
{
colNum++;
int tmpCol = colNum - 1;
string sDate = dr["startDate"].AsString().SubStr(0, 10);
string eDate = dr["endDate"].AsString().SubStr(0, 10);
ws30419.Cells[1, tmpCol].Value = string.Format("{0}～{1}", sDate + Environment.NewLine, Environment.NewLine + eDate);
}//foreach (DataRow dr in dtTmp.Rows)
DataTable dt = new D30417().ListData2(StartDate, EndDate);
if (dt.Rows.Count <= 0)
{
MessageDisplay.Info(String.Format("{0}~{1},30419 - {2},無任何資料!", StartDate, EndDate, rptName), GlobalInfo.ResultText);
return(false);
} //if (dt.Rows.Count <= 0 )
int pos1 = 0;
int pos2 = 0;
foreach (DataRow drYmd in dtYmd.Rows)
{
pos1++;
pos2 = 0;
foreach (DataRow drProd in dtProd.Rows)
{
pos2++;
//將週日期區間數量相加
string kindId = drProd["ai2_kind_id"].AsString();
string ai2Symd = drYmd["startDate"].AsString().Replace("/", "");
string ai2Eymd = drYmd["endDate"].AsString().Replace("/", "");
DataTable dtTmp = dt.Filter(string.Format("ai2_kind_id='{0}' and ai2_ymd>='{1}' and ai2_ymd<='{2}'", kindId, ai2Symd, ai2Eymd));
if (dtTmp.Rows.Count > 0)
{
decimal sumDayCount = dtTmp.Compute("Sum(ai2_day_count)", "").AsDecimal();
decimal sumOi = dtTmp.Compute("Sum(ai2_oi)", "").AsDecimal();
decimal yAvgOi;
rowNum = pos2 + 1;
if (sumDayCount == 0)
{
yAvgOi = 0;
}
else
{
yAvgOi = Math.Round(sumOi / sumDayCount, 0, MidpointRounding.AwayFromZero);
}
ws30419.Cells[rowNum, pos1 + 1].Value = yAvgOi;
} //if (dtTmp.Rows.Count < 0)
} //foreach (DataRow drProd in dtProd.Rows)
} //foreach (DataRow drYmd in dtYmd.Rows)
#endregion
ws30419.Range["A1"].Select();
ws30419.ScrollToRow(0);
return(true);
} catch (Exception ex) {
WriteLog(ex);
return(false);
}
}
/// <summary>
/// 根據報表類型,轉出excel (其實三張報表都輸出到同一份excel)
/// </summary>
/// <param name="sheetNo"></param>
/// <param name="rowTotalCount">最後一列空白行列</param>
/// /// <param name="ls_rpt_name">sheet標題中文</param>
/// <param name="startRowIndex">起始插入資料的row index</param>
/// <param name="searchDate">根據reportType傳入[起始年月]或[起始年月日]</param>
/// <returns></returns>
protected bool wf_export(Workbook workbook, SheetNo sheetNo, string ls_rpt_name, int rowTotalCount,
int startRowIndex, string searchDate)
{
try {
IFormatProvider culture = new System.Globalization.CultureInfo("zh-TW", true);
DateTime xxxDate = DateTime.ParseExact(searchDate, "yyyy/MM/dd", culture);
//1.讀取資料
DataTable dtContent = new DataTable();
if ((int)sheetNo == 0)
{
dtContent = daoS0010.d_s0010_cm(xxxDate); //結算會員
}
else
{
dtContent = daoS0010.d_s0010_fcm(xxxDate); //期貨商
}
//1.1 check row count
if (dtContent.Rows.Count <= 0)
{
MessageDisplay.Info(string.Format("{0},{1},無任何資料!", searchDate, _ProgramID + "－" + ls_rpt_name), GlobalInfo.ResultText);
return(false);
}
//2.切換Sheet
Worksheet sheet1 = workbook.Worksheets[(int)sheetNo];
//2.1 設定資料日期
sheet1.Cells[0, 4].Value += searchDate;
//3.匯出資料(單純輸出dataTable,直接用函數sheet.Import(DataTable,isNeedTitle,rowIndex,colIndex)
sheet1.Import(dtContent, false, startRowIndex, 1);
#region (結算會員)sheet1 處理內容的資料(old)
//if ((int)sheetNo == 0) {
// ////4.處理sheet1(結算會員)下方處理內容的資料
// DataTable dtTemp = new DataTable();
//dtTemp = daoS0010.d_s0010_sp2_old(xxxDate);
//string ls_str, ls_str2, ls_str3;
////4.1.PSR (40070-MG2)
//DataView dv = dtTemp.AsDataView();
//dv.RowFilter = "SP2_TYPE = 'PSR'";
//DataTable dt = dv.ToTable();
//ls_str = "";
//for (int i = 0 ; i < dt.Rows.Count ; i++) {
// if (!String.IsNullOrEmpty(ls_str)) {
// ls_str = ls_str + "、";
// }
// ls_str += dt.Rows[i]["SP2_KIND_ID1"].AsString().Trim() + ":" + string.Format("{0:#.0#%}" , dt.Rows[i]["SP1_CHANGE_RANGE"].AsDecimal());
//}
//sheet1.Cells[218 , 1].Value = ls_str;
////4.2.VSR (SV)
//DataView dv2 = dtTemp.AsDataView();
//dv2.RowFilter = "SP2_TYPE = 'SV'";
//DataTable dt2 = dv.ToTable();
//ls_str2 = "";
//for (int i = 0 ; i < dt.Rows.Count ; i++) {
// if (!String.IsNullOrEmpty(ls_str2)) {
// ls_str2 = ls_str2 + "、";
// }
// ls_str2 += dt.Rows[i]["SP2_KIND_ID1"].AsString().Trim() + ":" + string.Format("{0:#.0#%}" , dt.Rows[i]["SP1_CHANGE_RANGE"].AsDecimal());
//}
//sheet1.Cells[220 , 1].Value = ls_str2;
////4.3.契約價值耗用比率 (SD)
//DataView dv3 = dtTemp.AsDataView();
//dv2.RowFilter = "SP2_TYPE = 'SD'";
//DataTable dt3 = dv.ToTable();
//ls_str3 = "";
//for (int i = 0 ; i < dt.Rows.Count ; i++) {
// if (!String.IsNullOrEmpty(ls_str3)) {
// ls_str3 = ls_str3 + "、";
// }
// ls_str3 += dt.Rows[i]["SP2_KIND_ID1"].AsString().Trim() + "/" + dt.Rows[i]["SP2_KIND_ID2"].AsString().Trim() +
// ":" + string.Format("{0:#.0#%}" , dt.Rows[i]["SP1_CHANGE_RANGE"].AsDecimal());
//}
//sheet1.Cells[222 , 1].Value = ls_str3;
//}
#endregion
//用ken改寫的SQL去兜可跑這段
//4.如果是結算會員sheet(0)要另外填上調整內容：PSR/VSR/契約價值耗用比率
if ((int)sheetNo == 0)
{
DataTable dtTemp = daoS0010.d_s0010_sp2(searchDate);
if (dtTemp.Rows.Count > 0)
{
sheet1.Cells[218, 1].Value = dtTemp.Rows[0]["TEST"].AsString();
sheet1.Cells[220, 1].Value = dtTemp.Rows[1]["TEST"].AsString();
sheet1.Cells[222, 1].Value = dtTemp.Rows[2]["TEST"].AsString();
}
}
//5.刪除空白列
Range ra = sheet1.Range[string.Format("{0}:{1}", dtContent.Rows.Count + 7, rowTotalCount)];
ra.Delete(DeleteMode.EntireRow);
//5.1 把指標移到[A1]
sheet1.Range["A1"].Select();
sheet1.ScrollToRow(0);
return(true);
} catch (Exception ex) {
WriteLog(ex);
return(false);
}
}
/// <summary>
/// wf_30407 (sheet7 data)
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheetNo">SheetNo.tradeSum</param>
/// <param name="row"> 3 </param>
/// <returns></returns>
protected int wf_30407(Workbook workbook, SheetNo sheetNo, int rowNum)
{
string rptName = "股票期貨交易量分佈明細統計表";
try {
//1. 切換Sheet
Worksheet ws7 = workbook.Worksheets[(int)sheetNo];
ws7.Cells[1, 0].Value = txtMon.Text;
int rowTotal = 35;
//2. 取日期 (取查詢年月月份的第1天跟最後1天(string))
string sDate = txtMon.Text + "/01";
string eDate = txtMon.DateTimeValue.AddMonths(1).AddDays(-txtMon.DateTimeValue.AddMonths(1).Day).ToString("yyyy/MM/dd");
//3. 讀取資料
DataTable dt30407 = dao30400.Get30406Data(sDate, eDate);
if (dt30407.Rows.Count <= 0)
{
MessageDisplay.Info(String.Format("{0}~{1},30406 - {2},無任何資料!", sDate, eDate, rptName), GlobalInfo.ResultText);
return(0);
}
//4. 處理資料
//交割年月
int maxSeqNo = dt30407.Compute("MAX(seq_no)", "").AsInt();
for (int w = 1; w <= maxSeqNo; w++)
{
//dt30407.PrimaryKey = new DataColumn[] { dt30407.Columns["AMIF_DATE"] , dt30407.Columns["SEQ_NO"] };
int found = dt30407.Rows.IndexOf(dt30407.Select(string.Format("seq_no ={0}", w.AsString())).FirstOrDefault());
if (found < 0)
{
continue;
}
else
{
string settleDate = dt30407.Rows[found]["amif_settle_date"].AsString();
ws7.Cells[3, w + 1].Value = DateTime.ParseExact(settleDate, "yyyyMM", null).ToString("yyyy/MM");
}
}//for (int w = 0 ; w < maxSeqNo ; w++)
DateTime baseTime = DateTime.ParseExact("1900/01/01", "yyyy/MM/dd", null);
foreach (DataRow dr in dt30407.Rows)
{
DateTime amifDate = dr["amif_date"].AsDateTime();
decimal openInterest = dr["amif_open_interest"].AsDecimal();
if (baseTime != amifDate)
{
baseTime = amifDate;
rowNum++;
ws7.Cells[rowNum, 0].Value = baseTime.ToString("yyyy/MM/dd");
}
int found = dr["seq_no"].AsInt();
ws7.Cells[rowNum, found + 1].Value = openInterest;
}//foreach (DataRow dr in dt30407.Rows)
//5. 刪除空白列
if (rowTotal > rowNum - 3)
{
Range ra = ws7.Range[(rowNum + 2).ToString() + ":" + rowTotal.ToString()];
ra.Delete(DeleteMode.EntireRow);
}
ws7.Range["A1"].Select();
ws7.ScrollToRow(0);
return(1);
} catch (Exception ex) {
WriteLog(ex);
return(0);
}
}
/// <summary>
/// wf_30405 (sheet5 data)
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheetNo">SheetNo.tradeSum</param>
/// <param name="row"> 1 </param>
/// <returns></returns>
protected int wf_30405(Workbook workbook, SheetNo sheetNo, int rowNum)
{
string rptName = "股票期貨交易概況統計表";
ShowMsg("30405－" + rptName + " 轉檔中...");
try {
//1. 取日期 (取查詢年月月份的第1天跟最後1天(string))
string sDate = txtMon.Text.Replace("/", "") + "01";
string eDate = txtMon.DateTimeValue.AddMonths(1).AddDays(-txtMon.DateTimeValue.AddMonths(1).Day).ToString("yyyyMMdd");
//2. 讀取資料
DataTable dt30405 = dao30400.Get30405Data(sDate, eDate);
if (dt30405.Rows.Count <= 0)
{
MessageDisplay.Info(String.Format("{0}~{1},30405 - {2},無任何資料!", sDate, eDate, rptName), GlobalInfo.ResultText);
return(0);
}
//3. 切換Sheet
Worksheet ws5 = workbook.Worksheets[(int)sheetNo];
//4. 處理資料
ws5.Cells[1, 0].Value = txtMon.Text;
int rowTotal = 34;
string ymd = "19000101";
foreach (DataRow dr in dt30405.Rows)
{
string ai2Ymd = dr["ai2_ymd"].AsString();
decimal mQnty = dr["ai2_m_qnty"].AsDecimal();
decimal ai2Oi = dr["ai2_oi"].AsDecimal();
decimal am10Cnt = dr["am10_cnt"].AsDecimal();
decimal cnt = dr["am9_acc_cnt"].AsDecimal();
decimal idCnt = dr["ab4_id_cnt"].AsDecimal();
if (ymd != ai2Ymd)
{
ymd = ai2Ymd;
rowNum++;
string dTimeYmd = DateTime.ParseExact(ymd, "yyyyMMdd", null).ToString("yyyy/MM/dd");
ws5.Cells[rowNum, 0].Value = dTimeYmd;
}
ws5.Cells[rowNum, 1].Value = mQnty;
ws5.Cells[rowNum, 2].Value = ai2Oi;
ws5.Cells[rowNum, 3].Value = am10Cnt;
ws5.Cells[rowNum, 5].Value = cnt;
ws5.Cells[rowNum, 7].Value = idCnt;
}//foreach (DataRow dr in dt30405.Rows)
//5. 刪除空白列
if (rowTotal > dt30405.Rows.Count)
{
Range ra = ws5.Range[(dt30405.Rows.Count + 4).ToString() + ":" + rowTotal.ToString()];
ra.Delete(DeleteMode.EntireRow);
}
ws5.Range["A1"].Select();
ws5.ScrollToRow(0);
return(1);
} catch (Exception ex) {
WriteLog(ex);
return(0);
}
}
/// <summary>
/// wf_30404 (sheet4 data)
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheetNo">SheetNo.tradeSum</param>
/// <param name="row"> 1 </param>
/// <returns></returns>
protected int wf_30404(Workbook workbook, SheetNo sheetNo, int rowNum, string strKindId)
{
string rptName = "個別股票期貨成交量及未平倉量變化表";
ShowMsg("30404－" + rptName + " 轉檔中...");
try {
//1. 取日期
string sDate = PbFunc.f_get_last_day("AI3", strKindId, txtMon.Text, 2, "yyyyMMdd").ToString("yyyy/MM/dd"); //前月倒數2天交易日
string eDate = PbFunc.f_get_end_day("AI3", strKindId, txtMon.Text).ToString("yyyy/MM/dd"); //抓當月最後交易日
//2. 讀取資料
DataTable dt30404 = dao30400.Get30404Data(strKindId, sDate, eDate);
if (dt30404.Rows.Count <= 0)
{
MessageDisplay.Info(String.Format("{0}~{1},30404 - {2},無任何資料!", sDate, eDate, rptName), GlobalInfo.ResultText);
return(0);
} //if (dt30404.Rows.Count <= 0)
//3. 切換Sheet
Worksheet ws4 = workbook.Worksheets[(int)sheetNo];
//4. 處理資料
int rowTotal = 35;
DataTable dtAPDK = dao30400.GetAdpkData(strKindId); //好像只會有一筆資料
if (dtAPDK.Rows.Count <= 0)
{
return(0);
}
else if (dtAPDK.Rows.Count > 0)
{
ws4.Cells[0, 0].Value = dtAPDK.Rows[0]["apdk_name"].AsString();
ws4.Cells[0, 1].Value = txtKindId.Text;
ws4.Cells[0, 8].Value = dtAPDK.Rows[0]["apdk_stock_id"].AsString();
}
DateTime ymd = DateTime.ParseExact("1900/01/01", "yyyy/MM/dd", null);
foreach (DataRow dr in dt30404.Rows)
{
DateTime ai3Date = dr["ai3_date"].AsDateTime();
decimal closePrice = dr["ai3_close_price"].AsDecimal();
decimal mQnty = dr["ai3_m_qnty"].AsDecimal();
decimal ai3Oi = dr["ai3_oi"].AsDecimal();
decimal ai3Index = dr["ai3_index"].AsDecimal();
decimal ai3Amount = dr["ai3_amount"].AsDecimal();
if (ymd != ai3Date)
{
ymd = ai3Date;
rowNum++;
ws4.Cells[rowNum, 0].Value = ymd.ToString("MM/dd");
}
ws4.Cells[rowNum, 1].Value = closePrice;
ws4.Cells[rowNum, 3].Value = mQnty;
ws4.Cells[rowNum, 4].Value = ai3Oi;
ws4.Cells[rowNum, 5].Value = ai3Index;
ws4.Cells[rowNum, 7].Value = ai3Amount;
}//foreach (DataRow dr in dt30404.Rows)
//5. 刪除空白列
if (rowTotal > dt30404.Rows.Count)
{
Range ra = ws4.Range[(dt30404.Rows.Count + 3).ToString() + ":" + rowTotal.ToString()];
ra.Delete(DeleteMode.EntireRow);
}
ws4.Range["A1"].Select();
ws4.ScrollToRow(0);
return(1);
} catch (Exception ex) {
WriteLog(ex);
return(0);
}
}
/// <summary>
/// 根據報表類型,轉出excel (其實三張報表都輸出到同一份excel)
/// </summary>
/// <param name="sheetNo"></param>
/// <param name="reportType"></param>
/// <param name="ls_rpt_name">sheet標題中文</param>
/// <param name="rowTotalCount">空白行的筆數</param>
/// <param name="startRowIndex">起始插入資料的row index</param>
/// <param name="startDate">根據reportType傳入[起始年月]或[起始年月日]</param>
/// <param name="endDate">根據reportType傳入[結束年月]或[結束年月日]</param>
/// <returns></returns>
protected bool wf_Export(Workbook workbook, SheetNo sheetNo, ReportType reportType,
string ls_rpt_name, int rowTotalCount, int startRowIndex,
DateTime startDate, DateTime endDate)
{
try {
string tmpStart = (reportType == ReportType.mon ? startDate.ToString("yyyy/MM") : startDate.ToString("yyyy/MM/dd"));
string tmpEnd = (reportType == ReportType.mon ? endDate.ToString("yyyy/MM") : endDate.ToString("yyyy/MM/dd"));
//1.讀取資料
DataTable dtTemp = new DataTable();
switch (reportType)
{
case ReportType.mon:
dtTemp = dao95140.ListMonth(startDate.ToString("yyyyMM"), endDate.ToString("yyyyMM"));
break;
case ReportType.date:
dtTemp = dao95140.ListDateFcm(startDate.ToString("yyyyMMdd"), endDate.ToString("yyyyMMdd"));
break;
case ReportType.detail:
dtTemp = dao95140.ListDateAcc(startDate.ToString("yyyyMMdd"), endDate.ToString("yyyyMMdd"));
break;
}
//1.1 check row count
if (dtTemp.Rows.Count == 0)
{
MessageDisplay.Info(string.Format("{0},{1},無任何資料!", tmpStart + "~" + tmpEnd, _ProgramID + "－" + ls_rpt_name));
return(false);
}
//2.切換Sheet
Worksheet sheet1 = workbook.Worksheets[(int)sheetNo];
//2.1 設定標題
string titleDateDesc = "";
if (startDate == endDate)
{
titleDateDesc = tmpStart;
}
else
{
titleDateDesc = tmpStart + "~" + tmpEnd;
}
sheet1.Cells[0, 0].Value = titleDateDesc + sheet1.Cells[0, 0].Value;
//3.匯出資料(因為單純輸出dataTable,可直接用函數sheet.Import(DataTable,isNeedTitle,rowIndex,colIndex)
sheet1.Import(dtTemp, false, startRowIndex, 0);
//4.刪除空白列
if (dtTemp.Rows.Count <= rowTotalCount)
{
sheet1.Rows.Remove(startRowIndex + dtTemp.Rows.Count, rowTotalCount - dtTemp.Rows.Count);
}
//4.1 把指標移到[A1]
sheet1.Range["A1"].Select();
sheet1.ScrollToRow(0);
return(true);
} catch (Exception ex) {
PbFunc.f_write_logf(_ProgramID, "error", ex.Message);
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);
}
}
private void wf_Export(Workbook workbook, SheetNo sheetNo, DataTable dt, string rptId, string rptName)
{
try {
//切換Sheet
Worksheet worksheet = workbook.Worksheets[(int)sheetNo];
if (dwParamKey.EditValue.AsString() != "%")
{
worksheet.Cells[1, 0].Value = "商品：" + dwParamKey.Text;
}
if (txtAftStartYM.Text == txtAftEndYM.Text)
{
worksheet.Cells[2, 2].Value = PbFunc.f_conv_date(txtAftStartYM.DateTimeValue, 6);
}
else
{
worksheet.Cells[2, 2].Value = PbFunc.f_conv_date(txtAftStartYM.DateTimeValue, 6) + " ～ " + PbFunc.f_conv_date(txtAftEndYM.DateTimeValue, 6);
}
int tmp = ((int)sheetNo == 0 ? 5 : 4);
if (txtPrevStartYM.Text == txtPrevEndYM.Text)
{
worksheet.Cells[2, tmp].Value = PbFunc.f_conv_date(txtPrevStartYM.DateTimeValue, 6);
}
else
{
worksheet.Cells[2, tmp].Value = PbFunc.f_conv_date(txtPrevStartYM.DateTimeValue, 6) + " ～ " + PbFunc.f_conv_date(txtPrevEndYM.DateTimeValue, 6);
}
//只有成交量(Sheet1)需要執行這段
if (sheetNo == 0)
{
if (gbMarket.EditValue.ToString() == "rb_market_0")
{
worksheet.Cells[1, 0].Value = "一般交易時段";
}
else if (gbMarket.EditValue.ToString() == "rb_market_1")
{
worksheet.Cells[1, 0].Value = "盤後交易時段";
}
for (int i = 0; i < dt.Rows.Count; i++)
{
int ii_ole_row = dt.Rows[i]["RPT_SEQ_NO"].AsInt();
worksheet.Cells[ii_ole_row - 1, 2].Value = dt.Rows[i]["AM21_M_QNTY_AFT"].AsDecimal();
if (dt.Rows[i]["TRADE_DAYS_AFT"].AsDecimal() > 0)
{
worksheet.Cells[ii_ole_row - 1, 4].Value = dt.Rows[i]["AM21_M_QNTY_AFT"].AsDecimal() / dt.Rows[i]["TRADE_DAYS_AFT"].AsDecimal();
}
worksheet.Cells[ii_ole_row - 1, 5].Value = dt.Rows[i]["AM21_M_QNTY_PREV"].AsDecimal();
if (dt.Rows[i]["TRADE_DAYS_PREV"].AsDecimal() > 0)
{
worksheet.Cells[ii_ole_row - 1, 7].Value = dt.Rows[i]["AM21_M_QNTY_PREV"].AsDecimal() / dt.Rows[i]["TRADE_DAYS_PREV"].AsDecimal();
}
}
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
int ii_ole_row = dt.Rows[i]["RPT_SEQ_NO"].AsInt();
if (dt.Rows[i]["TRADE_DAYS_AFT"].AsDecimal() > 0)
{
worksheet.Cells[ii_ole_row - 1, 2].Value = dt.Rows[i]["AM21_OI_QNTY_AFT"].AsDecimal() / dt.Rows[i]["TRADE_DAYS_AFT"].AsDecimal();
}
if (dt.Rows[i]["TRADE_DAYS_PREV"].AsDecimal() > 0)
{
worksheet.Cells[ii_ole_row - 1, 4].Value = dt.Rows[i]["AM21_OI_QNTY_PREV"].AsDecimal() / dt.Rows[i]["TRADE_DAYS_PREV"].AsDecimal();
}
}
}
worksheet.Range["A1"].Select();
worksheet.ScrollToRow(0);
} catch (Exception ex) {
WriteLog(ex);
}
return;
}
/// <summary>
/// wf_30401 (sheet1 data)
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheetNo">SheetNo.tradeSum</param>
/// <param name="row"> 1 </param>
/// <returns></returns>
protected int wf_30401(Workbook workbook, SheetNo sheetNo, int rowNum)
{
string rptName = "股票期貨成交量及未平倉量變化表"; //報表標題名稱
ShowMsg("30401－" + rptName + " 轉檔中...");
try {
//1. 前月倒數1天交易日(?)
//DateTime sDate = GlobalInfo.OCF_DATE.AddDays(-GlobalInfo.OCF_DATE.Day + 1); //月份第1天
//DateTime eDate = GlobalInfo.OCF_DATE.AddMonths(1).AddDays(-GlobalInfo.OCF_DATE.AddMonths(1).Day); //月份最後1天
DateTime sDate = txtMon.DateTimeValue.AddDays(-txtMon.DateTimeValue.Day + 1); //月份第1天
DateTime eDate = txtMon.DateTimeValue.AddMonths(1).AddDays(-txtMon.DateTimeValue.AddMonths(1).Day); //月份最後1天
string strSDate = sDate.ToString("yyyyMMdd");
string strEDate = eDate.ToString("yyyyMMdd");
string lastTradeDate = daoAI2.GetLastTradeDate("D", "O", "S", sDate, eDate);//抓當月最後交易日
//2. 讀取資料
DataTable dt30401 = dao30400.Get30401Data(strSDate, strEDate, "F");
if (dt30401.Rows.Count <= 0)
{
MessageDisplay.Info(String.Format("{0}~{1},30401 - {2},無任何資料!", sDate.ToString("yyyy/MM/dd"), eDate.ToString("yyyy/MM/dd"), rptName), GlobalInfo.ResultText);
return(0);
} //if (dt30401.Rows.Count <= 0)
//3. 切換Sheet
Worksheet ws1 = workbook.Worksheets[(int)sheetNo];
//4. 處理資料
int rowTotal = rowNum + 34;
string ymd = "";
foreach (DataRow dr in dt30401.Rows)
{
string ai2Ymd = dr["ai2_ymd"].AsString();
decimal mQnty = dr["ai2_m_qnty"].AsDecimal();
decimal sumMmkQnty = dr["cp_sum_ai2_mmk_qnty"].AsDecimal();
decimal sumOi = dr["cp_sum_ai2_oi"].AsDecimal();
if (ymd != ai2Ymd)
{
ymd = ai2Ymd;
rowNum++;
ws1.Cells[rowNum, 0].Value = string.Format("{0}/{1}", ymd.SubStr(4, 2), ymd.SubStr(6, 2));
}
ws1.Cells[rowNum, 1].Value = mQnty;
ws1.Cells[rowNum, 3].Value = sumMmkQnty;
ws1.Cells[rowNum, 5].Value = sumOi;
}//foreach (DataRow dr in dt30401.Rows)
//5. 刪除空白列
if (rowTotal > dt30401.Rows.Count)
{
Range ra = ws1.Range[(dt30401.Rows.Count + 3).ToString() + ":" + rowTotal.ToString()];
ra.Delete(DeleteMode.EntireRow);
}
ws1.Range["A1"].Select();
ws1.ScrollToRow(0);
return(1);
} catch (Exception ex) {
WriteLog(ex);
return(0);
}
}