/// <summary>
///
/// </summary>
/// <param name="FilePath">Excel_Template</param>
/// <param name="datetime">em_month.Text</param>
public B30398(Workbook Workbook, string datetime)
{
_workbook = Workbook;
_emMonthText = datetime;
daoAI3 = new AI3();
daoAM2 = new AM2();
}
/// <summary>
/// wf_30396
/// </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 Wf30396(string IsKindID = "BRF", string SheetName = "30396", 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);
//切換Sheet
Worksheet worksheet = _workbook.Worksheets[SheetName];
//無前月資料
int addRowCount = 0;//總計寫入的行數
DataTable dtAI3 = new AI3().ListAI3(IsKindID, StartDate, EndDate);
//讀取資料
string firstDATE = dtAI3.AsEnumerable().FirstOrDefault()["AI3_DATE"].AsDateTime().ToString("yyyy/MM");
if (firstDATE == _emMonthText)
{
RowIndex = RowIndex + 2;
}
//寫入資料
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(); //漲跌
}
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, $"{SheetName}a");//ex:30396a
}
}
catch (Exception ex) {
#if DEBUG
throw new Exception($"Wf30396:" + ex.Message);
#else
throw ex;
#endif
}
return(MessageDisplay.MSG_OK);
}
/// <summary>
/// 證期局七組月報
/// </summary>
/// <param name="FilePath">Excel_Template</param>
/// <param name="datetime">em_month.Text</param>
public B30310(string FilePath, string datetime)
{
daoAI2 = new AI2();
daoAI3 = new AI3();
dao30310 = new D30310();
_lsFile = FilePath;
_emMonthText = datetime;
}
/// <summary>
///
/// </summary>
/// <param name="FilePath">Excel_Template</param>
/// <param name="datetime">em_month.Text</param>
public B30393(Workbook workbook, string datetime)
{
_workbook = workbook;
_emMonthText = datetime;
daoAI2 = new AI2();
daoAI3 = new AI3();
b30398 = new B30398(workbook, datetime);
}
public W30710(string programID, string programName) : base(programID, programName)
{
daoAI3 = new AI3();
InitializeComponent();
this.Text = _ProgramID + "─" + _ProgramName;
txtDate.DateTimeValue = GlobalInfo.OCF_DATE;
ExportShow.Hide();
}
/// <summary>
/// wf_30391()
/// </summary>
/// <param name="RowIndex">Excel的Row位置</param>
/// <param name="RowTotal">Excel的Column預留數</param>
/// <param name="IsKindID">商品代號</param>
/// <param name="SheetName">工作表</param>
/// <param name="RptName">作業名稱</param>
/// <returns></returns>
public string Wf30391(int RowIndex = 1, int RowTotal = 32, string IsKindID = "T5F", string SheetName = "30391", string RptName = "「台灣五十」期貨契約價量資料")
{
Workbook workbook = new Workbook();
try {
//前月倒數2天交易日
DateTime StartDate = PbFunc.f_get_last_day("AI3", IsKindID, _emMonthText, 2);
//抓當月最後交易日
DateTime EndDate = PbFunc.f_get_end_day("AI3", IsKindID, _emMonthText);
//切換Sheet
workbook.LoadDocument(_lsFile);
Worksheet worksheet = workbook.Worksheets[SheetName];
//讀取資料
DataTable dt = new AI3().ListAI3(IsKindID, StartDate, EndDate);
if (dt.Rows.Count <= 0)
{
return($"{StartDate.ToShortDateString()}～{EndDate.ToShortDateString()},30391－{RptName},{IsKindID}無任何資料!");
}
DateTime ldtYMD = new DateTime(1900, 1, 1);
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.Rows[RowIndex][1 - 1].Value = ldtYMD.ToString("MM/dd"); //日期
}
worksheet.Rows[RowIndex][2 - 1].Value = row["AI3_CLOSE_PRICE"].AsDecimal(); //臺灣50期貨指數
worksheet.Rows[RowIndex][4 - 1].Value = row["AI3_M_QNTY"].AsDecimal(); //臺灣50期貨總成交量
worksheet.Rows[RowIndex][5 - 1].Value = row["AI3_OI"].AsDecimal(); //臺灣50期貨總未平倉量
worksheet.Rows[RowIndex][6 - 1].Value = row["AI3_INDEX"].AsDecimal(); //臺灣50現貨指數
}
//刪除空白列
if (RowTotal > addRowCount)
{
worksheet.Rows.Remove(RowIndex + 1, RowTotal - addRowCount);
}
}
catch (Exception ex) {
throw ex;
}
finally {
workbook.SaveDocument(_lsFile);
}
return(MessageDisplay.MSG_OK);
}
/// <summary>
///
/// </summary>
/// <param name="FilePath">Excel_Template</param>
/// <param name="datetime">em_month.Text</param>
public B30330(string FilePath, string datetime)
{
daoAI3 = new AI3();
_lsFile = FilePath;
_emMonthText = datetime;
}
