/// <summary>
/// wf_30331
/// 30398,30399相同
/// </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 Wf30331(string IsKindID = "GTF", string SheetName = "30398", int RowIndex = 1, int RowTotal = 33)
{
try {
//前月倒數2天交易日
DateTime StartDate = PbFunc.f_get_last_day("AI3", IsKindID, _emMonthText, 2);
//抓當月最後交易日
DateTime EndDate = PbFunc.f_get_end_day("AI3", IsKindID, _emMonthText);
Worksheet worksheet = _workbook.Worksheets[SheetName];
/*add some infor 原本template標題就已經設定 這段看不出意義在哪 所以不翻
* iole_1.application.activecell(1, 1).value = "櫃買期貨"
* iole_1.application.activecell(2, 2).value = "櫃買價格"
* iole_1.application.activecell(2, 4).value = "櫃買期貨總成交量"
* iole_1.application.activecell(2, 5).value = "櫃買期貨總未平倉量"
* end add*/
worksheet.Range["A1"].Select();
int addRowCount = 0;//總計寫入的行數
//讀取資料
DataTable dtAI3 = daoAI3.ListAI3(IsKindID, StartDate, EndDate);
//寫入資料
DateTime ldtYMD = new DateTime(1900, 1, 1);
foreach (DataRow row in dtAI3.Rows)
{
if (ldtYMD != row["AI3_DATE"].AsDateTime())
{
ldtYMD = row["AI3_DATE"].AsDateTime();
RowIndex = RowIndex + 1;
addRowCount++;
worksheet.Rows[RowIndex][1 - 1].Value = ldtYMD.ToString("MM/dd");//日期
}
worksheet.Rows[RowIndex][2 - 1].Value = row["AI3_CLOSE_PRICE"].AsDecimal(); //櫃買價格
worksheet.Rows[RowIndex][4 - 1].Value = row["AI3_M_QNTY"].AsDecimal(); //櫃買期貨總成交量
worksheet.Rows[RowIndex][5 - 1].Value = row["AI3_OI"].AsDecimal(); //櫃買期貨總未平倉量
worksheet.Rows[RowIndex][6 - 1].Value = row["AI3_INDEX"].AsDecimal(); //現貨價格
}
//刪除空白列
if (RowTotal > addRowCount)
{
worksheet.Rows.Remove(RowIndex + 1, RowTotal - addRowCount);
//重新選取圖表範圍
ResetChartData(RowIndex + 1, _workbook, worksheet, $"{SheetName}a"); //ex:30398a
worksheet.ScrollTo(0, 0); //直接滾動到最上面，不然看起來很像少行數
}
}
catch (Exception ex) {
#if DEBUG
throw new Exception($"Wf30331:" + ex.Message);
#else
throw ex;
#endif
}
return(MessageDisplay.MSG_OK);
}
/// <summary>
/// 寫入 30331 sheet
/// </summary>
/// <returns></returns>
public string Wf30331()
{
Workbook workbook = new Workbook();
try {
string lsKindID = "GBF";
//前月倒數2天交易日
DateTime StartDate = PbFunc.f_get_last_day("AI3", lsKindID, _emMonthText, 2);
//抓當月最後交易日
DateTime EndDate = PbFunc.f_get_end_day("AI3", lsKindID, _emMonthText);
//切換Sheet
workbook.LoadDocument(_lsFile);
Worksheet worksheet = workbook.Worksheets[0];
DateTime ldtYMD = new DateTime(1900, 1, 1);
int rowIndex = 1;
int RowTotal = 32 + 1; //Excel的Column預留數 預留顯示32行加上隱藏的1行
int addRowCount = 0; //總計寫入的行數
//讀取資料
DataTable dt = daoAI3.ListAI3(lsKindID, StartDate, EndDate);
foreach (DataRow row in dt.Rows)
{
if (ldtYMD != row["AI3_DATE"].AsDateTime())
{
ldtYMD = row["AI3_DATE"].AsDateTime();
rowIndex = rowIndex + 1;
addRowCount++;
worksheet.Rows[rowIndex][1 - 1].Value = ldtYMD.ToString("MM/dd"); //日期
}
worksheet.Rows[rowIndex][2 - 1].Value = row["AI3_CLOSE_PRICE"].AsDecimal(); //公債期貨價格
worksheet.Rows[rowIndex][4 - 1].Value = row["AI3_M_QNTY"].AsDecimal(); //公債期貨總成交量
worksheet.Rows[rowIndex][5 - 1].Value = row["AI3_OI"].AsDecimal(); //公債期貨總未平倉量
}
//刪除空白列
if (RowTotal > addRowCount)
{
worksheet.Rows.Remove(rowIndex + 1, RowTotal - addRowCount);
//重新選取圖表範圍
ResetChartData(rowIndex + 1, workbook, worksheet, "30332");
}
worksheet.ScrollTo(0, 0);
}
catch (Exception ex) {
throw ex;
}
finally {
workbook.SaveDocument(_lsFile);
}
return(MessageDisplay.MSG_OK);
}
/// <summary>
/// wf_30393_1
/// </summary>
/// <param name="StartDate">前月倒數2天交易日</param>
/// <param name="EndDate">抓當月最後交易日</param>
/// <param name="IsKindID">商品代號</param>
/// <param name="SheetName">工作表</param>
/// <param name="RowIndex">Excel的Row位置</param>
/// <param name="RowTotal">Excel的Column預留數</param>
/// <returns></returns>
public string Wf30393(DateTime StartDate, DateTime EndDate, string IsKindID, string SheetName, int RowIndex = 1, int RowTotal = 33)
{
try {
Worksheet worksheet = _workbook.Worksheets[SheetName];
int addRowCount = 0;//總計寫入的行數
//讀取資料
DataTable dtAI3 = daoAI3.ListAI3(IsKindID, StartDate, EndDate);
//寫入資料
DateTime ldtYMD = new DateTime(1900, 1, 1);
foreach (DataRow row in dtAI3.Rows)
{
if (ldtYMD != row["AI3_DATE"].AsDateTime())
{
ldtYMD = row["AI3_DATE"].AsDateTime();
RowIndex = RowIndex + 1;
addRowCount++;
worksheet.Rows[RowIndex][1 - 1].Value = ldtYMD.ToString("MM/dd");//日期
}
//if not isnull(ld_val) then iole_1.application.activecell(ii_ole_row, 3).value = ids_1.getitemdecimal(i, "ai3_close_price") - ids_1.getitemdecimal(i, "ai3_last_close_price")
//pb這段只會在Excel隱藏的欄位執行成功
if (RowIndex == 2 && row["AI3_LAST_CLOSE_PRICE"] != DBNull.Value)
{
worksheet.Rows[RowIndex][3 - 1].Value = row["AI3_CLOSE_PRICE"].AsDecimal() - row["AI3_LAST_CLOSE_PRICE"].AsDecimal(); //C
}
worksheet.Rows[RowIndex][2 - 1].Value = row["AI3_CLOSE_PRICE"].AsDecimal(); //B
worksheet.Rows[RowIndex][4 - 1].Value = row["AI3_M_QNTY"].AsDecimal(); //D
worksheet.Rows[RowIndex][5 - 1].Value = row["AI3_OI"].AsDecimal(); //E
worksheet.Rows[RowIndex][6 - 1].Value = row["AI3_INDEX"].AsDecimal(); //F
}
//刪除空白列
if (RowTotal > addRowCount)
{
worksheet.Rows.Remove(RowIndex + 1, RowTotal - addRowCount);
//重新選取圖表範圍
ResetChartData(RowIndex + 1, _workbook, worksheet, SheetName.Replace($"({IsKindID})", "a"));//ex:30393_1a
}
//表尾
DataTable dtAI2 = daoAI2.ListAI2ym(IsKindID, EndDate.ToString("yyyyMM"), StartDate.ToString("yyyyMM"));
if (dtAI2.Rows.Count <= 0)
{
return(MessageDisplay.MSG_OK);
}
int liDayCnt;
//上月
RowIndex = RowIndex + 5;
liDayCnt = dtAI2.Rows[0]["LAST_M_DAY_CNT"].AsInt();
if (liDayCnt > 0)
{
worksheet.Rows[RowIndex][5 - 1].Value = Math.Round(dtAI2.Rows[0]["LAST_M_QNTY"].AsDecimal() / liDayCnt, 0);
worksheet.Rows[RowIndex][7 - 1].Value = Math.Round(dtAI2.Rows[0]["LAST_M_OI"].AsDecimal() / liDayCnt, 0);
}
//今年迄今
RowIndex = RowIndex + 2;
liDayCnt = dtAI2.Rows[0]["Y_DAY_CNT"].AsInt();
if (liDayCnt > 0)
{
worksheet.Rows[RowIndex][5 - 1].Value = Math.Round(dtAI2.Rows[0]["Y_QNTY"].AsDecimal() / liDayCnt, 0);
worksheet.Rows[RowIndex][7 - 1].Value = Math.Round(dtAI2.Rows[0]["Y_OI"].AsDecimal() / liDayCnt, 0);
}
}
catch (Exception ex) {
#if DEBUG
throw new Exception($"Wf30393:" + ex.Message);
#else
throw ex;
#endif
}
return(MessageDisplay.MSG_OK);
}
/// <summary>
/// 寫入30311_2(EXF)&30311_3(FXF) sheet
/// </summary>
/// <param name="lsKindID"></param>
/// <param name="SheetName"></param>
/// <returns></returns>
public string Wf30310two(string lsKindID, string SheetName)
{
Workbook workbook = new Workbook();
try {
//前月倒數2天交易日
DateTime StartDate = PbFunc.f_get_last_day("AI3", lsKindID, _emMonthText, 2);
//抓當月最後交易日
DateTime EndDate = PbFunc.f_get_end_day("AI3", lsKindID, _emMonthText);
//切換Sheet
workbook.LoadDocument(_lsFile);
Worksheet worksheet = workbook.Worksheets[SheetName];
DateTime ldtYMD = new DateTime(1900, 1, 1);
//讀取資料
DataTable dt = daoAI3.ListAI3(lsKindID, StartDate, EndDate);
if (dt.Rows.Count <= 0)
{
return($"{StartDate.ToShortDateString()}～{EndDate.ToShortDateString()},30310－我國臺股期貨契約價量資料,{lsKindID}無任何資料!");
}
int rowIndex = 1;
int RowTotal = 32 + 1; //Excel的Column預留數 預留顯示32行加上隱藏的1行
int addRowCount = 0; //總計寫入的行數
foreach (DataRow row in dt.Rows)
{
//不同的日期就寫入新的一行
if (ldtYMD != row["AI3_DATE"].AsDateTime())
{
ldtYMD = row["AI3_DATE"].AsDateTime();
rowIndex = rowIndex + 1;
addRowCount++;
worksheet.Cells[$"A{rowIndex + 1}"].SetValue(ldtYMD.ToString("MM/dd"));//日期
}
//電子期貨市場
worksheet.Cells[$"B{rowIndex + 1}"].SetValue(row["AI3_CLOSE_PRICE"]); //電子期貨指數(TX)
worksheet.Cells[$"D{rowIndex + 1}"].SetValue(row["AI3_M_QNTY"]); //電子期貨成交量(註①)
worksheet.Cells[$"E{rowIndex + 1}"].SetValue(row["AI3_OI"]); //電子期貨未平倉量(註①)
//電子現貨市場
worksheet.Cells[$"F{rowIndex + 1}"].SetValue(row["AI3_INDEX"]); //電子現貨指數(TAIEX)
}
//刪除空白列
if (RowTotal > addRowCount)
{
worksheet.Rows.Remove(rowIndex + 1, RowTotal - addRowCount);
//重新選取圖表範圍
ResetChartData(rowIndex + 1, workbook, worksheet, SheetName.Replace($"({lsKindID})", "a")); //ex:30393_1a
worksheet.ScrollTo(0, 0); //直接滾動到最上面，不然看起來很像少行數
}
//表尾
dt = daoAI2.ListAI2ym(lsKindID, EndDate.ToString("yyyyMM"), StartDate.ToString("yyyyMM"));
if (dt.Rows.Count <= 0)
{
return(MessageDisplay.MSG_OK);
}
int liDayCnt;
//上月
rowIndex = rowIndex + 5;
liDayCnt = dt.Rows[0]["LAST_M_DAY_CNT"].AsInt();
if (liDayCnt > 0)
{
worksheet.Cells[$"E{rowIndex + 1}"].Value = Math.Round(dt.Rows[0]["LAST_M_QNTY"].AsDecimal() / liDayCnt, 0);
worksheet.Cells[$"G{rowIndex + 1}"].Value = Math.Round(dt.Rows[0]["LAST_M_OI"].AsDecimal() / liDayCnt, 0);
}
//今年迄今
rowIndex = rowIndex + 2;
liDayCnt = dt.Rows[0]["Y_DAY_CNT"].AsInt();
if (liDayCnt > 0)
{
worksheet.Cells[$"E{rowIndex + 1}"].Value = Math.Round(dt.Rows[0]["Y_QNTY"].AsDecimal() / liDayCnt, 0);
worksheet.Cells[$"G{rowIndex + 1}"].Value = Math.Round(dt.Rows[0]["Y_OI"].AsDecimal() / liDayCnt, 0);
}
worksheet.ScrollTo(0, 0);
}
catch (Exception ex) {
throw ex;
}
finally {
workbook.SaveDocument(_lsFile);//存檔
}
return(MessageDisplay.MSG_OK);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
try {
Workbook workbook = new Workbook();
DataTable dt = new DataTable();
string kindId = "TXF";
DateTime sdate = PbFunc.f_get_last_day("AI3", kindId, txtDate.DateTimeValue.ToString("yyyy/MM"), 1);
DateTime edate = PbFunc.f_get_end_day("AI3", kindId, txtDate.DateTimeValue.ToString("yyyy/MM"));
dt = daoAI3.ListAI3(kindId, sdate, edate);
//查無資料時不產檔
if (dt.Rows.Count <= 0)
{
ExportShow.Hide();
MessageDisplay.Info(sdate + "~" + edate + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!");
return(ResultStatus.Fail);
}
//複製Template
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
workbook.LoadDocument(destinationFilePath);
//切換sheet
Worksheet worksheet = workbook.Worksheets["30711"];
DateTime ldt_ymd = new DateTime(1900, 1, 1);
int row_tol = 33;
//寫入資料
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (ldt_ymd != Convert.ToDateTime(dt.Rows[i]["ai3_date"]))
{
ldt_ymd = Convert.ToDateTime(dt.Rows[i]["ai3_date"]);
worksheet.Cells[i + 1, 0].Value = ldt_ymd;
}
worksheet.Cells[i + 1, 1].Value = float.Parse(dt.Rows[i]["ai3_index"].ToString());
worksheet.Cells[i + 1, 2].Value = float.Parse(dt.Rows[i]["ai3_close_price"].ToString());
}
}
//刪除空白列
if (row_tol > dt.Rows.Count)
{
Range ra = worksheet.Range[(dt.Rows.Count + 2).ToString() + ":" + row_tol.ToString()];
ra.Delete(DeleteMode.EntireRow);
}
//圖表重選範圍
ChartObject chartObjs = workbook.ChartSheets[0].Chart;
chartObjs.Series[0].SeriesName.SetValue("近月份期貨契約指數");
ChartData closePrice = new ChartData();
closePrice.RangeValue = worksheet.Range["C2:C" + (dt.Rows.Count + 1).ToString()];
chartObjs.Series[0].Values = closePrice;
ChartData index = new ChartData();
index.RangeValue = worksheet.Range["B2:B" + (dt.Rows.Count + 1).ToString()];
chartObjs.Series[1].Values = index;
workbook.SaveDocument(destinationFilePath);
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
