/// <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);
}
/// <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 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()
{
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);
}
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);
}
/// <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);
}
