/// <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);
}
protected override ResultStatus Export()
{
if (!StartExport())
{
return(ResultStatus.Fail);
}
Workbook workbook = new Workbook();
string lsFile = PbFunc.wf_copy_file(_ProgramID, "30393");
//載入Excel
workbook.LoadDocument(lsFile);
try {
b30393 = new B30393(workbook, emMonth.Text);
//前月倒數2天交易日
DateTime StartDate = PbFunc.f_get_last_day("AI3", "RHF", emMonth.Text, 2);
//抓當月最後交易日
DateTime EndDate = PbFunc.f_get_end_day("AI3", "RHF", emMonth.Text);
//RHF
ShowMsg("30393－「RHF」期貨契約價量資料 轉檔中...");
OutputShowMessage = b30393.Wf30393(StartDate, EndDate, "RHF", "30393_1(RHF)");
ShowMsg("30397－「黃金」期貨契約價量資料(買賣方比重) 轉檔中...");
OutputShowMessage = b30393.Wf30393abc("RHF", "data_30393_1abc");
//RTF
b30393 = new B30393(workbook, emMonth.Text);
ShowMsg("30393－「RTF」期貨契約價量資料 轉檔中...");
OutputShowMessage = b30393.Wf30393(StartDate, EndDate, "RTF", "30393_2(RTF)");
ShowMsg("30397－「黃金」期貨契約價量資料(買賣方比重) 轉檔中...");
OutputShowMessage = b30393.Wf30393abc("RTF", "data_30393_2abc");
//XEF
b30393 = new B30393(workbook, emMonth.Text);
ShowMsg("30393－「XEF」期貨契約價量資料 轉檔中...");
OutputShowMessage = b30393.Wf30393(StartDate, EndDate, "XEF", "30393_3(XEF)");
ShowMsg("30397－「黃金」期貨契約價量資料(買賣方比重) 轉檔中...");
OutputShowMessage = b30393.Wf30393abc("XEF", "data_30393_3abc");
//XJF
b30393 = new B30393(workbook, emMonth.Text);
ShowMsg("30393－「XJF」期貨契約價量資料 轉檔中...");
OutputShowMessage = b30393.Wf30393(StartDate, EndDate, "XJF", "30393_4(XJF)");
ShowMsg("30397－「黃金」期貨契約價量資料(買賣方比重) 轉檔中...");
OutputShowMessage = b30393.Wf30393abc("XJF", "data_30393_4abc");
}
catch (Exception ex) {
File.Delete(lsFile);
WriteLog(ex);
return(ResultStatus.Fail);
}
finally {
//存檔
workbook.SaveDocument(lsFile);
workbook.Dispose();
EndExport();
}
return(ResultStatus.Success);
}
/// <summary>
/// wf_30381()
/// </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 Wf30381(int RowIndex = 1, int RowTotal = 32, string IsKindID = "STW", string SheetName = "30381", string RptName = "新加坡交易所(SGX)摩根臺股期貨市場概況表")
{
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 D30380().GetData(StartDate, EndDate);
if (dt.Rows.Count <= 0)
{
return($"{StartDate.ToShortDateString()}～{EndDate.ToShortDateString()},30311－{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][4 - 1].Value = row["AI3_M_QNTY"].AsDecimal(); //總成交量
worksheet.Rows[RowIndex][5 - 1].Value = row["AI3_OI"].AsDecimal(); //總未平倉量
}
worksheet.Rows[RowIndex][2 - 1].Value = row["AI3_CLOSE_PRICE"].AsDecimal(); //期貨指數
worksheet.Rows[RowIndex][6 - 1].Value = row["AI3_INDEX"].AsDecimal(); //現貨指數
worksheet.Rows[RowIndex][10 - 1].Value = row["AI3_M_QNTY_FITX"].AsDecimal(); //臺指量
}
//刪除空白列
if (RowTotal > addRowCount)
{
worksheet.Range[$"{RowIndex + 2}:{RowTotal + 2}"].Clear(); //清空沒有必要的數值
worksheet.Rows.Hide(RowIndex + 1, RowIndex + (RowTotal - addRowCount)); //隱藏代替刪除
}
}
catch (Exception ex) {
throw ex;
}
finally {
workbook.SaveDocument(_lsFile);
}
return(MessageDisplay.MSG_OK);
}
/// <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_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);
}
protected override ResultStatus Export()
{
try {
#region 輸入&日期檢核
if (string.Compare(txtStartMonth.Text, txtEndMonth.Text) > 0)
{
MessageDisplay.Error("月份起始年月不可小於迄止年月!", GlobalInfo.ErrorText);
return(ResultStatus.Fail);
}
#endregion
//0. ready
panFilter.Enabled = false;
labMsg.Visible = true;
labMsg.Text = "開始轉檔...";
this.Cursor = Cursors.WaitCursor;
this.Refresh();
Thread.Sleep(5);
DateTime as_symd = DateTime.ParseExact((txtStartMonth.Text + "/01"), "yyyy/MM/dd", null);
DateTime as_eymd = PbFunc.f_get_end_day("AI2", "TXF", txtEndMonth.Text); //抓當月最後交易日
//1.複製檔案 & 開啟檔案 (因為三張報表都輸出到同一份excel,所以提出來)
string excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
Workbook workbook = new Workbook();
workbook.LoadDocument(excelDestinationPath);
Worksheet worksheet = workbook.Worksheets[0];
//2.填資料
ii_ole_row = 3;
bool sheet1 = wf_Export_30621(workbook, worksheet, as_symd, as_eymd);
ii_ole_row += 5;
bool sheet2 = wf_Export_30622(workbook, worksheet, as_symd, as_eymd);
//存檔
workbook.SaveDocument(excelDestinationPath);
if (!sheet1 && !sheet2)
{
File.Delete(excelDestinationPath);
return(ResultStatus.Fail);
}
return(ResultStatus.Success);
} catch (Exception ex) {
WriteLog(ex);
} finally {
panFilter.Enabled = true;
labMsg.Text = "";
labMsg.Visible = false;
this.Cursor = Cursors.Arrow;
}
return(ResultStatus.Fail);
}
/// <summary>
/// wf_30392_aprf
/// </summary>
/// <param name="workbook"></param>
/// <param name="kindId"></param>
/// <param name="sheetName"></param>
/// <param name="row"></param>
protected void wf_30392_1_aprf(Workbook workbook, string sheetName, int row)
{
string rptName = "「東證期貨」放寬漲跌幅統計表";
ShowMsg(string.Format("{0}－{1} 轉檔中...", _ProgramID, rptName));
try {
//1. 處理日期
DateTime ldt_sdate = PbFunc.f_get_last_day("AI3", "SPF", txtMonth.Text, 2); //前月倒數2天交易日
DateTime ldt_edate = PbFunc.f_get_end_day("AI3", "SPF", txtMonth.Text); //抓當月最後交易日
ldt_sdate = DateTime.ParseExact(ldt_edate.ToString("yyyy/MM/01"), "yyyy/MM/dd", null);
DataTable dtAprf = dao30392.d_30392_aprf(ldt_sdate, ldt_edate);
if (dtAprf.Rows.Count <= 0)
{
MessageDisplay.Info(string.Format("{0}~{1},{2}-{3},無任何資料!", ldt_sdate.ToString("yyyy/MM/dd"), ldt_edate.ToString("yyyy/MM/dd"), _ProgramID, rptName), GlobalInfo.ResultText);
return;
}
//2. 切換sheet
Worksheet ws2 = workbook.Worksheets[sheetName];
//3. 內容
ws2.Import(dtAprf, false, row, 0);
//4. 刪除空白列
int rowTotal = 35;
if (rowTotal > dtAprf.Rows.Count + 4)
{
Range ra = ws2.Range[(dtAprf.Rows.Count + 5).AsString() + ":" + rowTotal.AsString()];
ra.Delete(DeleteMode.EntireRow);
}
ws2.Range["A1"].Select();
ws2.ScrollToRow(0);
flag++;
} catch (Exception ex) {
WriteLog(ex);
}
}
public static void MyClassInitialize(TestContext testContext)
{
ConnectionInfo connectionInfo = SettingDragons.Instance.GetConnectionInfo(SettingDragons.Instance.Setting.Database.CiUserAp);
GlobalDaoSetting.Set(connectionInfo);
reportDirectoryPath = Path.Combine(Environment.CurrentDirectory.Replace("PhoenixCITests", "PhoenixCI"), "Report", DateTime.Now.ToString("yyyyMMdd"));
Directory.CreateDirectory(reportDirectoryPath);
string excelTemplateDirectoryPath = Path.Combine(Environment.CurrentDirectory.Replace("PhoenixCITests", "PhoenixCI"), "Excel_Template", "30393.xlsx");
destinationFilePath = Path.Combine(reportDirectoryPath, "30393_" + DateTime.Now.ToString("yyyy.MM.dd") + "-" + DateTime.Now.ToString("hh.mm.ss") + "Test.xlsx");
File.Copy(excelTemplateDirectoryPath, destinationFilePath, true);
//前月倒數2天交易日
_StartDate = PbFunc.f_get_last_day("AI3", "RHF", "2018/10", 2);
//抓當月最後交易日
_EndDate = PbFunc.f_get_end_day("AI3", "RHF", "2018/10");
_workbook = new Workbook();
//載入Excel
_workbook.LoadDocument(destinationFilePath);
}
protected override ResultStatus Export()
{
try {
//1. ready
panFilter.Enabled = false;
labMsg.Visible = true;
labMsg.Text = "開始轉檔...";
this.Cursor = Cursors.WaitCursor;
this.Refresh();
Thread.Sleep(5);
//2. 設定日期
DateTime ldt_sdate, ldt_edate;
flag = 0;
//2.1 copy template xls to target path
string excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
Workbook workbook = new Workbook();
workbook.LoadDocument(excelDestinationPath);
//3. 填資料
//3.1 I5F
//前月倒數2天交易日
ldt_sdate = PbFunc.f_get_last_day("AI3", "I5F", txtMonth.Text, 2);
//抓當月最後交易日
ldt_edate = PbFunc.f_get_end_day("AI3", "I5F", txtMonth.Text);
int row = 1;
wf_30392_1(workbook, "I5F", "30392_2(I5F)", ldt_sdate, ldt_edate, row);
row = 3;
wf_30392_1abc(workbook, "I5F", "data_30392_2abc", row);
//3.2 TJF
ldt_sdate = PbFunc.f_get_last_day("AI3", "TJF", txtMonth.Text, 2);
ldt_edate = PbFunc.f_get_end_day("AI3", "TJF", txtMonth.Text);
row = 1;
wf_30392_1(workbook, "TJF", "30392_1(TJF)", ldt_sdate, ldt_edate, row);
row = 3;
wf_30392_1abc(workbook, "TJF", "data_30392_1abc", row);
//3.3 UDF
ldt_sdate = PbFunc.f_get_last_day("AI3", "UDF", txtMonth.Text, 2);
ldt_edate = PbFunc.f_get_end_day("AI3", "UDF", txtMonth.Text);
string sdate = ldt_sdate.ToString("yyyyMM");
string edate = ldt_edate.ToString("yyyyMM");
if (sdate == edate)
{
ldt_sdate = DateTime.ParseExact(ldt_sdate.ToString("yyyy/MM/01"), "yyyy/MM/dd", null).AddDays(-1);
}
row = 1;
wf_30392_1(workbook, "UDF", "30392_3(UDF)", ldt_sdate, ldt_edate, row);
row = 3;
wf_30392_1abc(workbook, "UDF", "data_30392_3abc", row);
//3.4 SPF
ldt_sdate = PbFunc.f_get_last_day("AI3", "SPF", txtMonth.Text, 2);
ldt_edate = PbFunc.f_get_end_day("AI3", "SPF", txtMonth.Text);
sdate = ldt_sdate.ToString("yyyyMM");
edate = ldt_edate.ToString("yyyyMM");
if (sdate == edate)
{
ldt_sdate = DateTime.ParseExact(ldt_sdate.ToString("yyyy/MM/01"), "yyyy/MM/dd", null).AddDays(-1);
}
row = 1;
wf_30392_1(workbook, "SPF", "30392_4(SPF)", ldt_sdate, ldt_edate, row);
row = 3;
wf_30392_1abc(workbook, "SPF", "data_30392_4abc", row);
//3.5 一定要放到最後，因為ldt_sdate會變成當月1日
row = 4;
wf_30392_1_aprf(workbook, "30392_1d", row);
if (flag == 0)
{
File.Delete(excelDestinationPath);
MessageDisplay.Info(MessageDisplay.MSG_NO_DATA, GlobalInfo.ResultText);
return(ResultStatus.Fail);
}
//4. save
workbook.SaveDocument(excelDestinationPath);
if (FlagAdmin)
{
System.Diagnostics.Process.Start(excelDestinationPath);
}
return(ResultStatus.Success);
} catch (Exception ex) {
MessageDisplay.Info(MessageDisplay.MSG_NO_DATA, GlobalInfo.ResultText);
WriteLog(ex);
} finally {
panFilter.Enabled = true;
labMsg.Text = "";
labMsg.Visible = false;
this.Cursor = Cursors.Arrow;
}
return(ResultStatus.Fail);
}
/// <summary>
/// 寫入 30311_1 sheet
/// </summary>
/// <param name="lsKindID"></param>
/// <param name="SheetName"></param>
/// <returns></returns>
public string Wf30310one(string lsKindID, string SheetName)
{
Workbook workbook = new Workbook();
try {
//切換Sheet
workbook.LoadDocument(_lsFile);
Worksheet worksheet = workbook.Worksheets[SheetName];
//前月倒數2天交易日
DateTime StartDate = PbFunc.f_get_last_day("AI3", lsKindID, _emMonthText, 2);
//抓當月最後交易日
DateTime EndDate = PbFunc.f_get_end_day("AI3", lsKindID, _emMonthText);
//讀取資料
DataTable dt = dao30310.GetData(lsKindID, StartDate, EndDate);
if (dt.Rows.Count <= 0)
{
return($"{StartDate.ToShortDateString()}～{EndDate.ToShortDateString()},30310－我國臺股期貨契約價量資料,{lsKindID}無任何資料!");
}
DateTime ldtYMD = new DateTime(1900, 1, 1);
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 (lsKindID == "TXF")
{
worksheet.Cells[$"H{rowIndex + 1}"].SetValue(row["AI3_AMOUNT"]);//成交值(億元)
}
}
//刪除空白列
if (RowTotal > addRowCount)
{
worksheet.Rows.Remove(rowIndex + 1, RowTotal - addRowCount);
//重新選取圖表範圍
ResetChartData(rowIndex + 1, workbook, worksheet, $"{SheetName}a"); //ex:30393_1a
worksheet.ScrollTo(0, 0); //直接滾動到最上面，不然看起來很像少行數
}
//表尾
dt = daoAI2.ListAI2ym(lsKindID, EndDate.ToString("yyyyMM"), StartDate.ToString("yyyyMM"));
if (dt.Rows.Count <= 0)
{
return("");
}
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 - 1;
liDayCnt = dt.Rows[0]["CUR_M_DAY_CNT"].AsInt();
if (liDayCnt > 0)
{
worksheet.Cells[$"E{rowIndex + 1}"].Value = Math.Round(dt.Rows[0]["CUR_M_QNTY"].AsDecimal() / liDayCnt, 0);
worksheet.Cells[$"G{rowIndex + 1}"].Value = Math.Round(dt.Rows[0]["CUR_M_OI"].AsDecimal() / liDayCnt, 0);
}
rowIndex = rowIndex + 1;
//今年迄今
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);
}
}
catch (Exception ex) {
throw ex;
}
finally {
workbook.SaveDocument(_lsFile);//存檔
}
return(MessageDisplay.MSG_OK);
}
/// <summary>
/// 寫入 30311_4 sheet
/// </summary>
/// <returns></returns>
public string Wf30310four()
{
Workbook workbook = new Workbook();
try {
string lsKindID = "MSF";
string SheetName = "30311_4";
//前月倒數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(MessageDisplay.MSG_OK);
}
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);
string[] data = new string[] { "D4:D", "E4:E", $@"B4:B", $@"F4:F" };
int count = 0;
//重新抓取圖表範圍
foreach (var item in data)
{
workbook.ChartSheets[$"{SheetName}a"].Chart.Series[count++].Values = new ChartData {
RangeValue = worksheet.Range[item + rowIndex.ToString()]
};
}
}
worksheet.ScrollTo(0, 0);
}
catch (Exception ex) {
throw ex;
}
finally {
workbook.SaveDocument(_lsFile);//存檔
}
return(MessageDisplay.MSG_OK);
}
private bool wf_30611(Workbook workbook)
{
try {
string rptName = "每月現、期貨市場振幅、波動度、成交量彙集";
string rptId = "30611";
ShowMsg(string.Format("{0}－{1} 轉檔中...", rptId, rptName));
Worksheet worksheet = workbook.Worksheets[(int)SheetNo.mon]; //切換sheet
int ii_ole_row = 4;
int li_ole_row_tol = 184;
//每月
DateTime endDay = PbFunc.f_get_end_day("AI2", "TXF", txtEndMonth.Text);
DataTable dtContent = dao30610.GetMonData(txtStartMonth.DateTimeValue, endDay);; //月明細表
if (dtContent.Rows.Count <= 0)
{
MessageDisplay.Info(string.Format("{0}~{1},{2}-{3},無任何資料!", txtStartMonth.Text, txtEndMonth.Text, rptId, rptName), GlobalInfo.ResultText);
return(false);
}
int li_ole_end_row = 0;
for (int w = 1; w <= dtContent.Rows.Count; w++)
{
if (w == dtContent.Rows.Count)
{
li_ole_end_row = ii_ole_row;
ii_ole_row = li_ole_row_tol;
}
else
{
ii_ole_row++;
}
worksheet.Cells[ii_ole_row - 1, 0].Value = dtContent.Rows[w - 1]["AMIF_YM"].AsString().SubStr(0, 4).AsInt() - 1911 +
dtContent.Rows[w - 1]["AMIF_YM"].AsString().SubStr(4, 2);
worksheet.Cells[ii_ole_row - 1, 1].Value = dtContent.Rows[w - 1]["AMIF_TOT_CNT"].AsInt();
worksheet.Cells[ii_ole_row - 1, 2].Value = dtContent.Rows[w - 1]["TFXM_AVG_UP_DOWN"].AsDecimal();
worksheet.Cells[ii_ole_row - 1, 4].Value = dtContent.Rows[w - 1]["TFXM_CNT"].AsInt();
worksheet.Cells[ii_ole_row - 1, 5].Value = dtContent.Rows[w - 1]["RETURN_P2"].AsDecimal() * 100;
worksheet.Cells[ii_ole_row - 1, 6].Value = dtContent.Rows[w - 1]["TFXM_AVG_CLOSE_PRICE"].AsDecimal();
worksheet.Cells[ii_ole_row - 1, 7].Value = dtContent.Rows[w - 1]["TFXM_M_QNTY_TAL"].AsDecimal();
worksheet.Cells[ii_ole_row - 1, 8].Value = dtContent.Rows[w - 1]["AMIF_AVG_UP_DOWN"].AsDecimal();
worksheet.Cells[ii_ole_row - 1, 10].Value = dtContent.Rows[w - 1]["AMIF_CNT"].AsInt();
worksheet.Cells[ii_ole_row - 1, 11].Value = dtContent.Rows[w - 1]["RETURN_P1"].AsDecimal() * 100;
worksheet.Cells[ii_ole_row - 1, 12].Value = dtContent.Rows[w - 1]["AMIF_AVG_CLOSE_PRICE"].AsDecimal();
worksheet.Cells[ii_ole_row - 1, 13].Value = dtContent.Rows[w - 1]["AI2_AVG_QTY_TXF"].AsDecimal();
worksheet.Cells[ii_ole_row - 1, 14].Value = dtContent.Rows[w - 1]["AI2_AVG_QTY_TXO"].AsDecimal();
worksheet.Cells[ii_ole_row - 1, 15].Value = dtContent.Rows[w - 1]["AI2_AVG_TOT_QTY"].AsDecimal();
}
//刪除空白列
//if (li_ole_end_row < li_ole_row_tol) {
// worksheet.Rows.Remove(ii_ole_row , li_ole_row_tol - ii_ole_row);
//}
if (li_ole_row_tol > dtContent.Rows.Count + 4)
{
Range ra = worksheet.Range[(dtContent.Rows.Count + 4).AsString() + ":183"];
ra.Delete(DeleteMode.EntireRow);
}
worksheet.Range["A1"].Select();
worksheet.ScrollToRow(0);
return(true);
} catch (Exception ex) {
WriteLog(ex);
}
return(false);
}
/// <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);
}
}
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);
}
protected override ResultStatus Export()
{
string showMsg = "";
try {
this.Cursor = Cursors.WaitCursor;
this.Refresh();
Thread.Sleep(5);
lblProcessing.Visible = true;
ShowMsg("開始轉檔...");
dao30202 = new D30202();
//判斷是否有檔案,決定是否要寫入DB.
showMsg = "讀取既有計算資料錯誤";
string cpYmd = txtDate.DateTimeValue.ToString("yyyyMMdd");
DataTable dtPL1 = dao30202.d_30202_pl1(cpYmd);
if (dtPL1.Rows.Count > 0)
{
DialogResult result = MessageDisplay.Choose("已有計算資料,是否要更新資料庫資料?");
if (result == DialogResult.No)
{
cbxDB.Checked = false;
}
}
txtPrevEymd.DateTimeValue = PbFunc.f_get_end_day("DATE", "", txtEMonth.Text);
txtCurEymd.DateTimeValue = PbFunc.f_get_end_day("DATE", "", txtCurEMonth.Text);
string rptId = "30202", rptName = "股價指數暨黃金類商品部位限制數檢視表", file,
curSMonth = txtCurSMonth.Text.Replace("/", ""),
curEMonth = txtCurEMonth.Text.Replace("/", ""),
sMonth = txtSMonth.Text.Replace("/", ""),
eMonth = txtEMonth.Text.Replace("/", "");
decimal natureSdt = txtMultiNature.Text.AsDecimal() / 100;
decimal legalSdt = txtMultiLegal.Text.AsDecimal() / 100;
//讀取資料
showMsg = "讀取資料錯誤";
DataTable dt30202 = dao30202.d_30202(cpYmd, sMonth, eMonth, curSMonth, curEMonth, natureSdt, legalSdt);
if (dt30202.Rows.Count == 0)
{
MessageDisplay.Info(eMonth + "," + rptId + '－' + rptName + ",無任何資料!");
lblProcessing.Visible = false;
return(ResultStatus.Fail);
}
ShowMsg(rptId + '－' + rptName + " 轉檔中...");
//複製檔案
showMsg = "複製檔案錯誤";
file = PbFunc.wf_copy_file(rptId, rptId);
if (file == "")
{
return(ResultStatus.Fail);
}
//開啟檔案
showMsg = "開啟檔案錯誤";
Workbook workbook = new Workbook();
workbook.LoadDocument(file);
//切換Sheet
showMsg = "切換Sheet錯誤";
Worksheet ws30202 = workbook.Worksheets[0];
//寫入資料
showMsg = "寫入資料錯誤";
int rowIndex = 2;
lblProcessing.Text = rptId + "－" + rptName + " 轉檔中...";
#region wf_30202
string str, natureType, legalType, col;
decimal value1, changeRange, curNature, curLegal, cpNature, cpLegal, nature, legal;
if (!cbxDB.Checked)
{
ws30202.Cells[0, 0].Value = ws30202.Cells[0, 0].Value.AsString() + "(試算)";
}
//(一)
ws30202.Cells[3, 1].Value = "(" + txtSMonth.Text + "/01" + "～" + txtPrevEymd.Text + ")";
//(二)
ws30202.Cells[3, 3].Value = "(" + txtCurSMonth.Text + "/01" + "～" + txtCurEymd.Text + ")";
//(六)
str = txtCurEMonth.Text;
DateTime date = (str + "/01").AsDateTime("yyyy/MM/dd");
do
{
date = PbFunc.relativedate(date, date.Day * -1);
str = str + "、" + date.ToString("yyyy/MM～") + txtCurEMonth.Text;
} while ((date.ToString("yyyy/MM") + "/01").AsDateTime("yyyy/MM/dd") > txtSMonth.DateTimeValue);//只比年月
ws30202.Cells[3, 12].Value = str;
foreach (DataRow dr in dt30202.Rows)
{
rowIndex = dr["RPT_SEQ_NO"].AsInt() - 1;
decimal pAvgQnty = dr["P_AVG_QNTY"].AsDecimal();
decimal pAvgOi = dr["P_AVG_OI"].AsDecimal();
//前次檢視之數值
ws30202.Cells[rowIndex, 1].Value = pAvgQnty;
ws30202.Cells[rowIndex, 2].Value = pAvgOi;
//本次檢視之數值
ws30202.Cells[rowIndex, 3].SetValue(dr["C_AVG_QNTY"]);
ws30202.Cells[rowIndex, 4].SetValue(dr["C_AVG_OI"]);
//相較前次數值增減幅度
if (pAvgQnty > pAvgOi)
{
value1 = pAvgQnty;
}
else
{
value1 = pAvgOi;
}
if (value1 == 0)
{
changeRange = -1;
}
else
{
changeRange = Math.Round(dr["C_MAX_VALUE"].AsDecimal() / value1 - 1, 4, MidpointRounding.AwayFromZero);
}
ws30202.Cells[rowIndex, 5].Value = changeRange;
dr["CHANGE_RANGE"] = changeRange;
//現行部位限制數
curNature = dr["PL2_NATURE"] == DBNull.Value ? -1 : dr["PL2_NATURE"].AsDecimal(); //當該欄位的值為DBNull時等於-1 (權宜做法)
curLegal = dr["PL2_LEGAL"] == DBNull.Value ? -1 : dr["PL2_LEGAL"].AsDecimal(); //當該欄位的值為DBNull時等於-1 (權宜做法)
if (dr["PL2_NATURE"] != DBNull.Value)
{
ws30202.Cells[rowIndex, 6].Value = curNature;
}
if (dr["PL2_LEGAL"] != DBNull.Value)
{
ws30202.Cells[rowIndex, 7].Value = curLegal;
}
//按交易規則檢視後之部位限制數
//自然人
value1 = dr["PLT1_T1_MULTIPLE"] == DBNull.Value ? -1 : dr["PLT1_T1_MULTIPLE"].AsDecimal();//當該欄位的值為DBNull時等於-1 (權宜做法)
if (dr["PLT1_T1_MIN_NATURE"] == DBNull.Value)
{
cpNature = dr["C_MAX_VALUE"].AsDecimal() * natureSdt;
if (value1 > 0)
{
cpNature = Math.Truncate(cpNature / value1) * value1;
}
}
else
{
cpNature = dr["PLT1_T1_MIN_NATURE"].AsDecimal();
}
ws30202.Cells[rowIndex, 9].Value = cpNature;
dr["CP_NATURE"] = cpNature;
//法人
value1 = dr["PLT1_T2_MULTIPLE"] == DBNull.Value ? -1 : dr["PLT1_T2_MULTIPLE"].AsDecimal();//當該欄位的值為DBNull時等於-1 (權宜做法)
if (dr["PLT1_T2_MIN_LEGAL"] == DBNull.Value)
{
cpLegal = dr["C_MAX_VALUE"].AsDecimal() * legalSdt;
if (value1 > 0)
{
cpLegal = Math.Truncate(cpLegal / value1) * value1;
}
}
else
{
cpLegal = dr["PLT1_T2_MIN_LEGAL"].AsDecimal();
}
ws30202.Cells[rowIndex, 10].Value = cpLegal;
dr["CP_LEGAL"] = cpLegal;
dr["CP_999"] = cpLegal * 3;
//近1~6月日均交易量與未沖銷量
/****************************
* 1. 檢視部位限制級距時，若該期間之每日平均交易量或未沖銷量與前次調整時相較，其增減未逾百分之二‧五時，雖達調整級距標準，仍不調整。
* 2.針對須降低部位限制數之商品再增加以最近1、2、4、5、6個月區間資料檢視，並取其數額大者為基準數，惟計算後不得超過前次之部位限制數。
****************************/
//(isnull(ld_cur_nature) and isnull(ld_cur_legal))這個條件拿掉，因為不會成立
if (Math.Abs(changeRange) <= 0.025m ||
(curNature == -1 && curLegal == -1) ||
(curNature == cpNature && curLegal == cpLegal))
{
str = "不適用";
if (curNature != -1 && curLegal != -1)
{
nature = curNature;
legal = curLegal;
}
else
{
nature = cpNature;
legal = cpLegal;
}
natureType = "不變";
legalType = "不變";
}
else
{
if (cpNature < curNature || cpLegal < curLegal)
{
col = "max"; //最大者
}
else
{
col = "min"; //最小者
}
str = "近" + dr[col + "_MONTH_SEQ_NO"].AsString() + "個月";
if (dr[col + "_TYPE"].AsString() == "OI")
{
str = str + "未沖銷量";
}
else
{
str = str + "交易量";
}
if (col == "max")
{
str = str + "最大者";
}
else
{
str = str + "最小者";
}
str = str + "(" + dr[col + "_VALUE"].AsDecimal().ToString("#,##0") + ")";
//自然人
value1 = dr["PLT1_R1_MULTIPLE"] == DBNull.Value ? -1 : dr["PLT1_R1_MULTIPLE"].AsDecimal();//當該欄位的值為DBNull時等於-1 (權宜做法)
if (dr["PLT1_R1_MIN_NATURE"] == DBNull.Value)
{
nature = dr[col + "_VALUE"].AsDecimal() * natureSdt;
if (value1 > 0)
{
nature = Math.Truncate(nature / value1) * value1;
}
}
else
{
nature = dr["PLT1_R1_MIN_NATURE"].AsDecimal();
}
natureType = "不變";
if (nature < curNature)
{
natureType = "降低";
}
else if (nature > curNature)
{
natureType = "調高";
}
//法人
value1 = dr["PLT1_R2_MULTIPLE"] == DBNull.Value ? -1 : dr["PLT1_R2_MULTIPLE"].AsDecimal();//當該欄位的值為DBNull時等於-1 (權宜做法)
if (dr["PLT1_R2_MIN_LEGAL"] == DBNull.Value)
{
legal = dr[col + "_VALUE"].AsDecimal() * legalSdt;
if (value1 > 0)
{
legal = Math.Truncate(legal / value1) * value1;
}
}
else
{
legal = dr["PLT1_R2_MIN_LEGAL"].AsDecimal();
}
legalType = "不變";
if (legal < curLegal)
{
legalType = "降低";
}
else if (legal > curLegal)
{
legalType = "調高";
}
}
ws30202.Cells[rowIndex, 12].Value = str;
ws30202.Cells[rowIndex, 14].Value = nature;
ws30202.Cells[rowIndex, 15].Value = legal;
ws30202.Cells[rowIndex, 16].Value = natureType;
ws30202.Cells[rowIndex, 17].Value = legalType;
dr["NATURE"] = nature;
dr["LEGAL"] = legal;
dr["P999"] = legal * 3;
switch (natureType)
{
case "降低":
str = "-";
break;
case "調高":
str = "+";
break;
default:
str = " ";
break;
}
dr["NATURE_ADJ"] = str;
switch (legalType)
{
case "降低":
str = "-";
break;
case "調高":
str = "+";
break;
default:
str = " ";
break;
}
dr["LEGAL_ADJ"] = str;
dr["P999_ADJ"] = str;
dr["YMD"] = cpYmd;
//針對須調降之商品再增加檢視標準後之部位限制數
}//foreach (DataRow dr in dt30202.Rows)
//表尾
rowIndex = dao30202.row_index();
if (rowIndex > 0)
{
ws30202.Cells[rowIndex - 1, 0].Value = ws30202.Cells[rowIndex - 1, 0].Value.ToString() +
"自然人乘以" + txtMultiNature.Text +
"%，法人乘以" + txtMultiLegal.Text + "%)";
}
//存檔
ws30202.ScrollToRow(0);
workbook.SaveDocument(file);
ShowMsg("轉檔成功");
#endregion
if (!cbxDB.Checked)
{
return(ResultStatus.Success);
}
#region wf_30202_write
bool dbCommit = false;
//刪除PL0的資料
showMsg = "PL0刪除失敗";
dbCommit = dao30202.DeletePL0ByDate(cpYmd);
if (!dbCommit)
{
MessageDisplay.Error(showMsg);
return(ResultStatus.Fail);
}
//新增PL0的資料
showMsg = "PL0新增失敗";
dbCommit = dao30202.InsertPL0(cpYmd, sMonth, eMonth, curSMonth, curEMonth, GlobalInfo.USER_ID);
if (!dbCommit)
{
MessageDisplay.Error(showMsg);
return(ResultStatus.Fail);
}
//刪除PL2的資料
showMsg = "PL2刪除失敗";
dbCommit = dao30202.DeletePL2ByDate(cpYmd);
if (!dbCommit)
{
MessageDisplay.Error(showMsg);
return(ResultStatus.Fail);
}
//刪除PL1的資料
showMsg = "PL1刪除失敗";
dbCommit = dao30202.DeletePL1ByDate(cpYmd);
if (!dbCommit)
{
MessageDisplay.Error(showMsg);
return(ResultStatus.Fail);
}
//寫入
dtPL1.Clear();
dtPL1.AcceptChanges();
for (int f = 0; f < dt30202.Rows.Count; f++)
{
dtPL1.Rows.Add();
for (int g = 0; g < 24; g++)
{
dtPL1.Rows[dtPL1.Rows.Count - 1][g] = dt30202.Rows[f][g];
//現行自然人,現行法人PL
if (g == 9 || g == 11)
{
if (dt30202.Rows[f][g] == DBNull.Value)
{
dtPL1.Rows[dtPL1.Rows.Count - 1][g] = 0;
}
}
}
dtPL1.Rows[dtPL1.Rows.Count - 1][24] = DateTime.Now;
dtPL1.Rows[dtPL1.Rows.Count - 1][25] = GlobalInfo.USER_ID;
}
try {
ResultData myResultData = dao30202.updatePL1(dtPL1);
}
catch (Exception ex) {
MessageDisplay.Error("計算結果新增至資料庫錯誤! ");
WriteLog(ex, "", false);
return(ResultStatus.Fail);
}
#endregion
}
catch (Exception ex) {
MessageDisplay.Error(showMsg);
throw ex;
}
finally {
this.Cursor = Cursors.Arrow;
this.Refresh();
Thread.Sleep(5);
}
return(ResultStatus.Success);
}
protected virtual int ExportSummary(Workbook workbook, int sheetIndex, int rowBegin)
{
//1.1 前月倒數2天交易日
DateTime ldt_sdate = PbFunc.f_get_last_day("AI3", KindId, StartMonth, 2);
//1.2 抓當月最後交易日
DateTime ldt_edate = PbFunc.f_get_end_day("AI3", KindId, StartMonth);
Worksheet ws = workbook.Worksheets[sheetIndex];
int rowIndex = rowBegin;
int emptyRowCount = rowIndex + 1 + 32;
//1.3 get ai3 data
DataTable dtTemp = Dao.d_ai3(KindId, ldt_sdate, ldt_edate);
if (dtTemp.Rows.Count <= 0)
{
//刪除空白列
if (rowIndex < emptyRowCount)
{
string selectBegin = (rowIndex + 2).ToString();
string selectEnd = (emptyRowCount).ToString();
string cellRange = string.Format("A{0}:G{1}", selectBegin, selectEnd);
ws.DeleteCells(ws.Range[cellRange], DeleteMode.EntireRow);
}
return(0);
}
//1.4 export to sheet
DateTime ldt_ymd = DateTime.MinValue;
foreach (DataRow dr in dtTemp.Rows)
{
DateTime ai3_date = dr["ai3_date"].AsDateTime();
if (ldt_ymd != ai3_date)
{
ldt_ymd = ai3_date;
rowIndex++;
ws.Cells[rowIndex, 0].Value = ldt_ymd.ToString("MM/dd");
}
ws.Cells[rowIndex, 1].Value = dr["ai3_close_price"].AsDecimal();
ws.Cells[rowIndex, 3].Value = dr["ai3_m_qnty"].AsDecimal();
ws.Cells[rowIndex, 4].Value = dr["ai3_oi"].AsDecimal();
ws.Cells[rowIndex, 5].Value = dr["ai3_index"].AsDecimal();
}//foreach (DataRow dr in dtTemp.Rows) {
//1.5 刪除空白列(注意,沒刪除好會影響到後面sheet的圖表)
if (rowIndex < emptyRowCount)
{
string selectBegin = (rowIndex + 2).ToString();
string selectEnd = (emptyRowCount).ToString();
string cellRange = string.Format("A{0}:G{1}", selectBegin, selectEnd);
ws.DeleteCells(ws.Range[cellRange], DeleteMode.EntireRow);
//ken,用DeleteCells還是不行,測試結果似乎xlsx的圖表公式一直固定,不會更新
}
//1.6 圖表範圍重選
ChartObject chartObjs = workbook.ChartSheets[$"{ws.Name}a"].Chart;
ChartData newChartData = new ChartData();
//黃金 or 台幣黃金期貨總成交量
newChartData.RangeValue = ws.Range["D4:D" + (rowIndex + 1).AsString()];
chartObjs.Series[0].Values = newChartData;
//黃金 or 台幣黃金期貨總未平倉量
newChartData.RangeValue = ws.Range["E4:E" + (rowIndex + 1).AsString()];
chartObjs.Series[1].Values = newChartData;
//黃金 or 台幣黃金期貨價格
newChartData.RangeValue = ws.Range["B4:B" + (rowIndex + 1).AsString()];
chartObjs.Series[2].Values = newChartData;
//現貨價格
newChartData.RangeValue = ws.Range["F4:F" + (rowIndex + 1).AsString()];
chartObjs.Series[3].Values = newChartData;
return(1);
}
protected override ResultStatus Export()
{
if (!StartExport())
{
return(ResultStatus.Fail);
}
string lsFile = PbFunc.wf_copy_file(_ProgramID, "30350");
//message.OutputShowMessage只會儲存ok的狀態,如沒有任何一個ok代表全部function都沒有資料
MessageDisplay message = new MessageDisplay();
Workbook workbook = new Workbook();
//載入Excel
workbook.LoadDocument(lsFile);
try {
string Txt = string.Empty;
//輸入交易日期
string emMonthTxt = emMonth.Text;
//前月倒數1天交易日
DateTime StartDate = PbFunc.f_get_last_day("AI2", "TXO", emMonthTxt, 1);
//抓當月最後交易日
DateTime EndDate = PbFunc.f_get_end_day("AI2", "TXO", emMonthTxt);
b30350 = new B30350(workbook, emMonthTxt, StartDate, EndDate);
//30350_01
Txt = "臺指選擇權成交量及未平倉量變化表";
ShowMsg($"30350－{Txt} 轉檔中...");
message.OutputShowMessage = b30350.DataFrom30351(1, 33, "TXO", "30350_01", Txt);
//30350_02
Txt = "金融選擇權成交量及未平倉量變化表";
ShowMsg($"30350－{Txt} 轉檔中...");
message.OutputShowMessage = b30350.DataFrom30351(1, 33, "TFO", "30350_02", Txt);
//30350_03
Txt = "電子選擇權成交量及未平倉量變化表";
ShowMsg($"30350－{Txt} 轉檔中...");
message.OutputShowMessage = b30350.DataFrom30351(1, 33, "TEO", "30350_03", Txt);
//30350_04
Txt = "摩臺選擇權成交量及未平倉量變化表";
ShowMsg($"30350－{Txt} 轉檔中...");
message.OutputShowMessage = b30350.DataFrom30351(1, 32, "MSO", "30350_04", Txt, B30350.Condition30350.sheet30350four);
//30350_05
Txt = "非金電選擇權成交量及未平倉量變化表";
ShowMsg($"30350－{Txt} 轉檔中...");
message.OutputShowMessage = b30350.DataFrom30351(1, 32, "XIO", "30350_05", Txt, B30350.Condition30350.RowIndexAddOne);
//30350_06
Txt = "櫃買選擇權成交量及未平倉量變化表";
ShowMsg($"30350－{Txt} 轉檔中...");
message.OutputShowMessage = b30350.DataFrom30351(1, 32, "GTO", "30350_06", Txt, B30350.Condition30350.RowIndexAddOne);
//30350_07
Txt = "黃金選擇權成交量及未平倉量變化表";
ShowMsg($"30350－{Txt} 轉檔中...");
message.OutputShowMessage = b30350.DataFrom30351(1, 32, "TGO", "30350_07", Txt, B30350.Condition30350.RowIndexAddOne);
//30350_08
Txt = "週臺指選擇權成交量及未平倉量變化表";
ShowMsg($"30350－{Txt} 轉檔中...");
message.OutputShowMessage = b30350.DataFrom30358(1, 33, "TXW", "30350_08", Txt, B30350.Condition30350.NoLastDay);
//30350_09
Txt = "月臺指選擇權成交量及未平倉量變化表";
ShowMsg($"30350－{Txt} 轉檔中...");
message.OutputShowMessage = b30350.DataFrom30358(1, 33, "TXO", "30350_09", Txt, B30350.Condition30350.NoLastDay);
//30350_10
Txt = "美元兌人民幣選擇權 成交量及未平倉量變化表";
ShowMsg($"30350－{Txt} 轉檔中...");
message.OutputShowMessage = b30350.DataFrom30351(1, 33, "RHO", "30350_10", Txt, B30350.Condition30350.NoLastMonth);
//30350_11
Txt = "小型美元兌人民幣選擇權成交量及未平倉量變化表";
ShowMsg($"30350－{Txt} 轉檔中...");
message.OutputShowMessage = b30350.DataFrom30351(1, 33, "RTO", "30350_11", Txt, B30350.Condition30350.NoLastMonth);
//存檔
workbook.SaveDocument(lsFile);
//連續跳11次無資料刪除檔案
if (string.IsNullOrEmpty(message.OutputShowMessage))
{
if (File.Exists(lsFile))
{
File.Delete(lsFile);
}
return(ResultStatus.Fail);
}
}
catch (Exception ex) {
if (File.Exists(lsFile))
{
File.Delete(lsFile);
}
WriteLog(ex);
return(ResultStatus.Fail);
}
finally {
EndExport();
}
return(ResultStatus.Success);
}
