protected override ResultStatus Export()
{
gvMain.CloseEditor();
gvMain.UpdateCurrentRow();
ExportShow.Text = "轉檔中...";
ExportShow.Show();
ResultStatus result = ResultStatus.Fail;
try {
DataTable exportDt = (DataTable)gcMain.DataSource;
exportDt = exportDt.Filter("RUN_FLAG ='Y'");
Workbook workbook = new Workbook();
//user 所選之要匯出商品
foreach (DataRow exportDr in exportDt.Rows)
{
int noData = 0;
//user 所選要匯出商品的 modelType
foreach (CheckedListBoxItem ch in ModelTypies.CheckedItems)
{
string kindId = exportDr["MG1_KIND_ID"].AsString();
string subType = exportDr["MG1_PROD_SUBTYPE"].AsString();
string prodType = exportDr["MG1_PROD_TYPE"].AsString();
string newFileName = _ProgramID + "(" + kindId + ")_" + ch.Description + "_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss") + ".xlsx";
int sheetIndex = prodType == "O" ? 2 : 0;
DataTable importData = dao40041.GetExportData(kindId, exportDr["DATA_SDATE"].AsDateTime(), txtDate.DateTimeValue, ch.Value.ToString());
DataTable accountingData = dao40041.GetExportData(kindId, exportDr["MG1_SDATE"].AsDateTime(), exportDr["MG1_SDATE"].AsDateTime(), ch.Value.ToString());
if (importData == null)
{
MessageDisplay.Info($"({kindId }_{ch.Description})資料不足2筆，無法產出報表!");
result = ResultStatus.FailButNext;
noData++;
continue;
}
if (importData.Rows.Count < 2)
{
MessageDisplay.Info($"({kindId }_{ch.Description})資料不足2筆，無法產出報表!");
result = ResultStatus.FailButNext;
noData++;
continue;
}
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID, newFileName);
destinationFilePath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, destinationFilePath);
workbook.LoadDocument(destinationFilePath);
#region Write Data
Worksheet worksheet = workbook.Worksheets[sheetIndex];
worksheet.Cells[2, 2].Value = prodType == "O" ? kindId : kindId + "結算價";
worksheet.Cells[0, 1].Value = accountingData.Rows[0]["MG1_DATE"].AsDateTime();
for (int i = 2; i <= accountingData.Columns.Count - 1; i++)
{
worksheet.Cells[0, i].Value = accountingData.Rows[0][i].AsDecimal();
}
worksheet.Import(importData, false, 3, 0);
//delete empty Rows
Range emptyRa = worksheet.Range[(importData.Rows.Count + 4).ToString() + ":1003"];
emptyRa.Delete(DeleteMode.EntireRow);
#endregion
#region Gen Figure 重組圖表資料
sheetIndex = prodType == "O" ? 3 : 1;
worksheet = workbook.Worksheets[sheetIndex];
if (subType != "S")
{
Range ra = worksheet.Range["4:5"];
ra.Delete(DeleteMode.EntireRow);
}
else
{
worksheet.Cells[4, 1].Value = exportDr["APDK_NAME"].AsString();
worksheet.Cells[4, 2].Value = exportDr["APDK_STOCK_ID"].AsString();
worksheet.Cells[4, 3].Value = exportDr["PID_NAME"].AsString();
Range ra = worksheet.Range["2:3"];
ra.Delete(DeleteMode.EntireRow);
}
//表頭日期
worksheet.Cells[3, 8].Value = importData.Rows[importData.Rows.Count - 1]["MG1_DATE"].AsDateTime();
//填寫圖表資料來源
int count = 2;
for (int f = 7; f >= 6; f--)
{
DataRow dataRow = importData.Rows[importData.Rows.Count - count];
count--;
worksheet.Cells[f, 1].Value = dataRow[3].AsDecimal();
worksheet.Cells[f, 3].Value = dataRow[2].AsDecimal();
worksheet.Cells[f, 5].Value = dataRow[4].AsDecimal();
worksheet.Cells[f, 6].Value = dataRow[5].AsDecimal();
worksheet.Cells[f, 7].Value = dataRow[6].AsDecimal();
worksheet.Cells[f, 8].Value = dataRow[7].AsDecimal();
worksheet.Cells[f, 9].Value = dataRow[8].AsDecimal();
}
#endregion
workbook.SaveDocument(destinationFilePath);
result = ResultStatus.Success;
}
//如果不相等, 表示並非所有參數都無資料
if (noData != ModelTypies.CheckedItems.Count)
{
result = ResultStatus.Success;
}
}
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
WriteLog(ex);
}
ExportShow.Text = "轉檔成功!";
return(result);
}
private bool ManipulateExcel()
{
try {
#region wf_55030 造市者各商品交易經手費折減比率月計表
txtMonth.Enabled = false;
this.Cursor = Cursors.WaitCursor;
this.Refresh();
Thread.Sleep(5);
lblProcessing.Visible = true;
ShowMsg("開始轉檔...");
string brkNo, accNo, session = "0", rptName, rptId, file;
int f, colNum, datacount, rowTol, rowNum;
rptName = "造市者各商品交易經手費折減比率月計表";
rptId = "55030";
ShowMsg(rptId + "－" + rptName + " 轉檔中...");
//讀取資料
DataTable dtContent = dao55030.ListByDate(txtMonth.Text.Replace("/", ""));
if (dtContent.Rows.Count == 0)
{
MessageDisplay.Info(string.Format("{0},{1},無任何資料!", txtMonth.Text, this.Text));
return(false);
}
//複製檔案
file = PbFunc.wf_copy_file(rptId, rptId);
if (file == "")
{
return(false);
}
//切換Sheet
Workbook workbook = new Workbook();
workbook.LoadDocument(file);
Worksheet worksheet = workbook.Worksheets[0];
//填資料
rowNum = 5;
datacount = int.Parse(worksheet.Cells[0, 0].Value.ToString());
if (datacount == null || datacount == 0)
{
datacount = dtContent.Rows.Count;
}
rowTol = rowNum + datacount;
worksheet.Cells[3, 0].Value = worksheet.Cells[3, 0].Value + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
worksheet.Cells[3, 7].Value = worksheet.Cells[3, 7].Value + txtMonth.Text.Replace("/", "");
brkNo = "";
accNo = "";
for (f = 0; f < dtContent.Rows.Count; f++)
{
if (brkNo != dtContent.Rows[f]["feetrd_fcm_no"].ToString() || accNo != dtContent.Rows[f]["feetrd_acc_no"].ToString())
{
rowNum = rowNum + 1;
brkNo = dtContent.Rows[f]["feetrd_fcm_no"].ToString();
accNo = dtContent.Rows[f]["feetrd_acc_no"].ToString();
worksheet.Cells[rowNum, 0].Value = brkNo;
worksheet.Cells[rowNum, 1].Value = dtContent.Rows[f]["brk_abbr_name"].ToString();
worksheet.Cells[rowNum, 2].Value = accNo;
}
colNum = int.Parse(dtContent.Rows[f]["rpt_seq_no"].ToString());
if (rowNum > 0 && colNum > 0)
{
worksheet.Cells[rowNum, colNum].Value = decimal.Parse(dtContent.Rows[f]["feetrd_rate"].ToString());
}
}
/*******************
* 刪除空白列
*******************/
if (rowTol > rowNum)
{
worksheet.Rows.Remove(rowNum + 1, rowTol - rowNum);
}
#endregion
#region wf_55031
string kindId;
session = "0";
/******************
* 讀取資料
******************/
DataTable dtContent2 = dao55031.ListByDate(txtMonth.Text.Replace("/", ""));
if (dtContent.Rows.Count == 0)
{
MessageDisplay.Info(string.Format("{0},{1},無任何資料!", txtMonth.Text, this.Text));
return(false);
}
//契約檔
DataTable dtAPDK = daoAPDK.ListAll_55031();
/******************
* 切換Sheet
******************/
Worksheet worksheet2 = workbook.Worksheets[1];
rowNum = 5;
datacount = int.Parse(worksheet2.Cells[0, 0].Value.ToString());
if (datacount == null || datacount == 0)
{
datacount = dtContent2.Rows.Count;
}
rowTol = rowNum + datacount;
worksheet2.Cells[3, 0].Value = worksheet2.Cells[3, 0].Value + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
worksheet2.Cells[3, 7].Value = worksheet2.Cells[3, 7].Value + txtMonth.Text.Replace("/", "");
//契約檔
for (f = 0; f < dtAPDK.Rows.Count; f++)
{
kindId = dtAPDK.Rows[f]["apdk_kind_id"].ToString();
if (kindId.Trim() == "STO")
{
kindId = "平均";
}
worksheet2.Cells[5, f + 3].Value = kindId;
}
brkNo = "";
f = 0;
for (f = 0; f < dtContent2.Rows.Count; f++)
{
if (brkNo != dtContent2.Rows[f]["feetrd_fcm_no"].ToString() || accNo != dtContent2.Rows[f]["feetrd_acc_no"].ToString())
{
rowNum = rowNum + 1;
brkNo = dtContent2.Rows[f]["feetrd_fcm_no"].ToString();
accNo = dtContent2.Rows[f]["feetrd_acc_no"].ToString();
worksheet2.Cells[rowNum, 0].Value = brkNo;
worksheet2.Cells[rowNum, 1].Value = dtContent2.Rows[f]["brk_abbr_name"].ToString();
worksheet2.Cells[rowNum, 2].Value = accNo;
}
//long datastore.Find ( stringexpression, longstart, longend )找該值位於資料表的第幾筆
if (dtAPDK.Select("apdk_kind_id='" + dtContent2.Rows[f]["feetrd_kind_id"].ToString().Trim() + "'").Length == 0)
{
colNum = 0;
}
else
{
colNum = dtAPDK.Rows.IndexOf(dtAPDK.Select("apdk_kind_id='" + dtContent2.Rows[f]["feetrd_kind_id"].ToString().Trim() + "'")[0]) + 1;
}
if (rowNum > 0 && colNum > 0)
{
worksheet2.Cells[rowNum, colNum + 2].Value = decimal.Parse(dtContent2.Rows[f]["feetrd_rate"].ToString());
}
}
/*******************
* 刪除空白列
*******************/
if (rowTol > rowNum)
{
worksheet2.Rows.Remove(rowNum + 1, rowTol - rowNum);
}
//存檔
workbook.SaveDocument(file);
#endregion
ShowMsg("轉檔成功");
return(true);
}
catch (Exception ex) {
MessageDisplay.Error("輸出錯誤");
throw ex;
}
finally {
this.Cursor = Cursors.Arrow;
this.Refresh();
Thread.Sleep(5);
txtMonth.Enabled = true;
}
}
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. copy template xls to target path
string excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
//3. open xls
Workbook workbook = new Workbook();
workbook.LoadDocument(excelDestinationPath);
//4. write data
//bool res1 = false, res2 = false, res3 = false;
int row = 1;
flag = 0;
flag += wf_30401(workbook, SheetNo.sheet1, row);
flag += wf_30402(workbook, SheetNo.sheet2, row);
flag += wf_30408(workbook, SheetNo.sheet8, row);
row = 0;
flag += wf_30403(workbook, SheetNo.sheet3, row);
if (txtKindId.Text != "%")
{
row = 1;
flag += wf_30404(workbook, SheetNo.sheet4, row, txtKindId.Text);
}
row = 2;
flag += wf_30405(workbook, SheetNo.sheet5, row);
row = 3;
flag += wf_30406(workbook, SheetNo.sheet6, row);
flag += wf_30407(workbook, SheetNo.sheet7, row);
if (flag == 0)
{
File.Delete(excelDestinationPath);
return(ResultStatus.Fail);
}
//5. save
workbook.SaveDocument(excelDestinationPath);
labMsg.Visible = false;
//測試時直接開檔
if (FlagAdmin)
{
System.Diagnostics.Process.Start(excelDestinationPath);
}
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>
/// 7/2上線前的版本 可供新需求的邏輯參考
/// </summary>
/// <returns></returns>
public string Wf40040()
{
Workbook workbook = new Workbook();
try {
workbook.LoadDocument(_lsFile);
Worksheet worksheet = workbook.Worksheets[(int)SheetName.SheetOne];
DateTime emdate = _emDateText.AsDateTime("yyyy/MM/dd");
worksheet.Cells["O1"].Value = "資料日期：" + emdate.ToLongDateString();
//前一交易日
DateTime dateLast = dao40040.GetDateLast(emdate, (int)SheetName.SheetOne);
//讀取資料
DataTable dt = dao40040.ListData(emdate, $"{_oswGrpVal}%");
if (dt.Rows.Count <= 0)
{
return(MessageDisplay.MSG_NO_DATA);
}
//讀取資料(現貨資料)
DataTable dtMg6 = dao40040.ListMg6Data(emdate, dateLast, "%");
//讀取Mg8資料
DataTable dtMg8 = dao40040.ListMg8Data(emdate, "%");
//保證金變動幅度達10%，分別為第n天
DataTable dtDay = dao40040.ListDayData(emdate);
foreach (DataRow row in dt.Rows)
{
int rowIndex = row["RPT_SEQ_NO"].AsInt();
if (rowIndex <= 0)
{
continue;
}
#region 第B欄
worksheet.Cells[$"B{rowIndex}"].Value = row["MGT2_KIND_ID_OUT"].AsString();
#endregion
if (row["MG1_CHANGE_FLAG"].AsString() != "Y")
{
continue;
}
//1.保證金變動幅度之趨勢/
//15%≧X≧10%
decimal mg1ChangeRange = row["MG1_CHANGE_RANGE"].AsDecimal();
decimal Percent10 = row["MG1_PROD_SUBTYPE"].AsString() == "E" ? 0.05m : 0.1m;
decimal Percent15 = row["MG1_PROD_SUBTYPE"].AsString() == "E" ? 0.1m : 0.15m;
#region 第C欄
if (Math.Abs(mg1ChangeRange) >= Percent10 && Math.Abs(mg1ChangeRange) < Percent15)
{
worksheet.Cells[$"C{rowIndex}"].SetValue(mg1ChangeRange);
}
#endregion
//X≧15%
#region 第D欄
if (Math.Abs(mg1ChangeRange) >= Percent15)
{
worksheet.Cells[$"D{rowIndex}"].SetValue(mg1ChangeRange);
}
#endregion
//前一交易日保證金變動幅度
var ldValue = row["MG1_CHANGE_RANGE_LAST"];
#region 第E欄
worksheet.Cells[$"E{rowIndex}"].SetValue(ldValue == DBNull.Value ? "▲" : ldValue);
#endregion
#region 第F欄
worksheet.Cells[$"F{rowIndex}"].SetValue(ldValue == DBNull.Value ? "▲" : OX(row));
#endregion
//達得調整標準天數
string kindID = row["MG1_KIND_ID"].AsString();
#region 第G欄
int dtDayIndex = dtDay.Rows.IndexOf(dtDay.Select($@"mg1_kind_id ='{kindID}'").FirstOrDefault());
worksheet.Cells[$"G{rowIndex}"].SetValue(dtDayIndex > -1 ? (long)dtDay.Rows[dtDayIndex]["DAY_CNT"].AsDecimal() : 1);
#endregion
//2.未沖銷部位數/
var ai2OI = row["AI2_OI"];
if (ai2OI != DBNull.Value)
{
#region 第H欄
worksheet.Cells[$"H{rowIndex}"].SetValue(ai2OI);
#endregion
#region 第I欄
var oiRate = row["OI_RATE"];
worksheet.Cells[$"I{rowIndex}"].SetValue(oiRate.AsDecimal() < 0.0001m && ai2OI.AsDecimal() > 0 ? "小於0.01%" : oiRate);
#endregion
#region 第J欄
decimal TotOIiRound = Math.Round(dt.Rows[0]["TOT_OI"].AsDecimal() * 0.005m, 0, MidpointRounding.AwayFromZero);
worksheet.Cells[$"J{rowIndex}"].SetValue(ai2OI.AsDecimal() >= TotOIiRound ? "O" : "X");
#endregion
//屆到期日前7個交易日
#region 第K欄
worksheet.Cells[$"K{rowIndex}"].SetValue(
row["APROD_7DATE"].AsDateTime() <= emdate && row["APROD_DELIVERY_DATE"].AsDateTime() > emdate ? "O" : "X"
);
#endregion
}
//3.現貨、期貨漲跌/
int dtMg6Index = dtMg6.Rows.IndexOf(dtMg6.Select($@"F_KIND_ID ='{kindID}' or O_KIND_ID='{kindID}'").FirstOrDefault());
if (dtMg6Index > -1)
{
string colTxt = dtMg6.Rows[dtMg6Index]["O_KIND_ID"] == DBNull.Value ? "PDK" : "O";
string prodSubtype = dtMg6.Rows[dtMg6Index]["APDK_PROD_SUBTYPE"].AsString();
//現貨
#region 第L欄
var updown = dtMg6.Rows[dtMg6Index][colTxt + "_UP_DOWN"];
decimal rateMUL100 = dtMg6.Rows[dtMg6Index][colTxt + "_RETURN_RATE"].AsDecimal() * 100;
string flag = FlagStr(updown.AsDecimal());
worksheet.Cells[$"L{rowIndex}"].SetValue(updown == DBNull.Value ? "-" : WriteUpDownPercent(updown.AsDecimal(), rateMUL100, prodSubtype, flag));
#endregion
//現貨漲跌與保證金調整方向相同
#region 第N欄
switch (kindID)
{
case "GDF":
case "TGF":
case "TGO":
case "GBF":
case "CPF":
flag = "-";
break;
default:
break;
}
if (flag == "-")
{
//ls_flag = '-' then 後面沒有要做什麼 只是做個條件區分
}
else if (row["MG1_CM_LAST"] == DBNull.Value)
{
flag = "";
}
else
{
flag = WriteFlag(flag, row);
}
worksheet.Cells[$"N{rowIndex}"].SetValue(flag);
#endregion
//期貨
#region 第M欄
var fupdown = dtMg6.Rows[dtMg6Index]["F_UP_DOWN"];
string flag2 = FlagStr(fupdown.AsDecimal());
decimal frateMUL100 = dtMg6.Rows[dtMg6Index]["F_RETURN_RATE"].AsDecimal() * 100;
worksheet.Cells[$"M{rowIndex}"].SetValue(WriteUpDownPercent(fupdown.AsDecimal(), frateMUL100, prodSubtype, flag2));
#endregion
//期貨漲跌與保證金調整方向相同
#region 第O欄
var cmlast = row["MG1_CM_LAST"];
worksheet.Cells[$"O{rowIndex}"].SetValue(cmlast == DBNull.Value ? "" : WriteFlag(flag2, row));
#endregion
}// if (dtMg6Index > -1)
//4.與國外水準相較/
int dtMg8Index = dtMg8.Rows.IndexOf(dtMg8.Select($"mg1_kind_id ='{kindID}' and com ='TAIFEX'").FirstOrDefault());
if (dtMg8Index > -1)
{
#region 第P欄
worksheet.Cells[$"P{rowIndex}"].SetValue(Foreign(kindID, "BEF", dtMg8, dtMg8Index));
#endregion
#region 第Q欄
worksheet.Cells[$"Q{rowIndex}"].SetValue(Foreign(kindID, "AFT", dtMg8, dtMg8Index));
#endregion
}
}//foreach (DataRow row in dt.Rows)
//重大事件
StringBuilder sb = new StringBuilder("");
DataTable dtMgt3 = dao40040.ListMgt3Data(emdate);
int mgt3Count = dtMgt3.Rows.Count;
for (int k = 0; k < mgt3Count; k++)
{
DataRow dr = dtMgt3.Rows[k];
string memo = PbFunc.f_conv_date(dr["MGT3_DATE_TO"].AsDateTime(), 3) + dr["MGT3_MEMO"].AsString();
sb.Append(memo);
sb.Append(FlagMerge(mgt3Count, k));
}
if (sb.ToString() != "")
{
int mg1flagYcount = dt.Select("mg1_change_flag = 'Y'").Length;
if (mg1flagYcount > 0)
{
worksheet.Cells[$"R16"].SetValue(sb.ToString());
}
}
//save
worksheet.ScrollTo(0, 0);
}
catch (Exception ex) {
#if DEBUG
throw new Exception($"Wf40040:" + ex.Message);
#else
throw ex;
#endif
}
finally {
workbook.SaveDocument(_lsFile);
}
return(MessageDisplay.MSG_OK);
}
protected override ResultStatus Export()
{
string rptName = ConditionText().Trim();
StartExport(_ProgramID, "造市者報表");
if (rptName == "")
{
rptName = "報表條件：" + "(" + DateText() + ")";
}
else
{
rptName = ConditionText().Trim() + " " + "(" + DateText() + ")";
}
try {
if (_Data == null || _Data.Rows.Count <= 0)
{
MessageDisplay.Info(MessageDisplay.MSG_NO_DATA);
return(ResultStatus.Fail);
}
//複製檔案
string lsFile = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
if (lsFile == "")
{
return(ResultStatus.Fail);
}
_D500Xx.LogText = lsFile;
//開啟檔案
Workbook workbook = new Workbook();
workbook.LoadDocument(lsFile);
/******************
* 切換Sheet
******************/
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells["E3"].Value = rptName;
int rowIndex = 5;
foreach (DataRow row in _Data.Rows)
{
worksheet.Cells[$"A{rowIndex}"].SetValue(row["CP_ROW"]);
worksheet.Cells[$"B{rowIndex}"].SetValue(row["AMM0_BRK_NO"]);
worksheet.Cells[$"C{rowIndex}"].SetValue(row["BRK_ABBR_NAME"]);
worksheet.Cells[$"D{rowIndex}"].SetValue(row["AMM0_ACC_NO"]);
worksheet.Cells[$"E{rowIndex}"].SetValue(row["AMM0_PROD_ID"]);
worksheet.Cells[$"F{rowIndex}"].SetValue(row["AMM0_YMD"].AsString());
worksheet.Cells[$"G{rowIndex}"].SetValue(row["AMM0_OM_QNTY"]);
worksheet.Cells[$"H{rowIndex}"].SetValue(row["AMM0_QM_QNTY"]);
worksheet.Cells[$"I{rowIndex}"].SetValue(row["QNTY"]);
worksheet.Cells[$"J{rowIndex}"].SetValue(row["CP_M_QNTY"]);
worksheet.Cells[$"K{rowIndex}"].SetValue(row["CP_RATE_M"]);
worksheet.Cells[$"L{rowIndex}"].SetValue(row["AMM0_VALID_CNT"]);
worksheet.Cells[$"M{rowIndex}"].SetValue(row["VALID_RATE"]);
worksheet.Cells[$"N{rowIndex}"].SetValue(row["AMM0_MARKET_R_CNT"]);
worksheet.Cells[$"O{rowIndex}"].SetValue(row["AMM0_MARKET_M_QNTY"]);
worksheet.Cells[$"P{rowIndex}"].SetValue(row["AMM0_KEEP_FLAG"]);
rowIndex = rowIndex + 1;
}//foreach (DataRow row in _Data.Rows
workbook.SaveDocument(lsFile);
}
catch (Exception ex) {
WriteLog(ex);
WfRunError();
}
finally {
EndExport();
}
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
try
{
#region 輸入&日期檢核 (exportbefore)
//if (!txtStartMonB.IsDate(txtStartMonB.Text , CheckDate.Start)
// || !txtEndMonB.IsDate(txtEndMonB.Text , CheckDate.End)) {
// return ResultStatus.Fail;
//}
//if (string.Compare(txtStartMonB.Text , txtEndMonB.Text) > 0)
if (string.Compare(txtStartMon.getTextValue(), txtEndMon.getTextValue()) > 0)
{
MessageDisplay.Error("月份起始年月不可小於迄止年月!", GlobalInfo.ErrorText);//若多隻功能皆有相同訊息可再寫入CheckDate Enum中
return(ResultStatus.Fail);
}
//if (txtStartMonB.Text.SubStr(0, 4).AsInt() < txtEndMonB.Text.SubStr(0, 4).AsInt() &&
// txtStartMonB.Text.SubStr(5, 2).AsInt() < txtEndMonB.Text.SubStr(5, 2).AsInt())
if (txtStartMon.getTextValue().SubStr(0, 4).AsInt() < txtEndMon.getTextValue().SubStr(0, 4).AsInt() &&
txtStartMon.getTextValue().SubStr(5, 2).AsInt() < txtEndMon.getTextValue().SubStr(5, 2).AsInt())
{
MessageDisplay.Error("最大查詢範圍為12個月!", GlobalInfo.ErrorText); //若多隻功能皆有相同訊息可再寫入CheckDate Enum中
return(ResultStatus.Fail);
}
#endregion
//1. ready
panFilter.Enabled = false;
labMsg.Visible = true;
labMsg.Text = "開始轉檔...";
this.Cursor = Cursors.WaitCursor;
this.Refresh();
Thread.Sleep(5);
//2. copy template xls to target path
string excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
//3. open xlsx
Workbook workbook = new Workbook();
workbook.LoadDocument(excelDestinationPath);
Worksheet ws = workbook.Worksheets[0];
//4. write data (w_30201)
labMsg.Text = this.Text + "轉檔中...";
//string startMon = txtStartMonB.Text.Replace("/", "");
//string endMon = txtEndMonB.Text.Replace("/", "");
string startMon = txtStartMon.getTextValue().Replace("/", "");
string endMon = txtEndMon.getTextValue().Replace("/", "");
string kindId = "";
int startNum = 0;
DataTable dt = new D30201().ListData(startMon, endMon);
DataTable dtFilter = dt.Filter("rpt_seq_no > 0");
if (dtFilter.Rows.Count <= 0)
{
//MessageDisplay.Info(string.Format("{0}-{1},{2},無任何資料", txtStartMonB, txtEndMonB, this.Text), GlobalInfo.ResultText);
MessageDisplay.Info(string.Format("{0}-{1},{2},無任何資料", txtStartMon, txtEndMon, this.Text), GlobalInfo.ResultText);
return(ResultStatus.Fail);
}//if (dtFilter.Rows.Count <= 0)
int colNum = 0, rowNum = 0;
foreach (DataRow dr in dtFilter.Rows)
{
string ai2KindId = dr["ai2_kind_id"].AsString();
DateTime dSymd = DateTime.ParseExact(dr["dt_symd"].AsString(), "yyyyMM", CultureInfo.InvariantCulture);
string dtSymd = dSymd.ToString("yyyy\\/MM");
DateTime dEymd = DateTime.ParseExact(dr["dt_eymd"].AsString(), "yyyyMM", CultureInfo.InvariantCulture);
string dtEymd = dEymd.ToString("yyyy\\/MM");
decimal avgQnty = dr["avg_qnty"].AsDecimal();
decimal avgOi = dr["avg_oi"].AsDecimal();
if (kindId != ai2KindId)
{
kindId = ai2KindId;
if (startNum == 1)
{
monthCnt = colNum;
} //if (startNum == 1)
startNum++;
} //if (kindId != ai2KindId)
rowNum = dr["rpt_seq_no"].AsInt() - 1;
colNum = dr["month_seq_no"].AsInt();
//首筆填入月份表頭
if (startNum == 1)
{
if (colNum == 1)
{
ws.Cells[3, colNum * 2].Value = string.Format("({0})", dtEymd);
}
else
{
ws.Cells[3, colNum * 2].Value = string.Format("({0}~{1})", dtSymd, dtEymd);
}
}//if (startNum == 1)
//填值
ws.Cells[rowNum, colNum * 2].Value = avgQnty;
ws.Cells[rowNum, colNum * 2 + 1].Value = avgOi;
}//foreach (DataRow dr in dtFilter.Rows)
string chineseNum = PbFunc.f_number_to_ch((long)(monthCnt - 1)); //因index從0開始，減1才正確
ws.Cells[0, 0].Value = string.Format("前{0}個月日均交易量與OI統計", chineseNum);
//5. delete blank
if (monthCnt < 12)
{
ws.Columns.Remove((monthCnt * 2) + 2, (12 - monthCnt) * 2);
}
//6. save
workbook.SaveDocument(excelDestinationPath);
labMsg.Visible = false;
//if (FlagAdmin)
// System.Diagnostics.Process.Start(excelDestinationPath);
return(ResultStatus.Success);
}
catch (Exception ex)
{
WriteLog(ex);
}
finally
{
panFilter.Enabled = true;
labMsg.Text = "";
labMsg.Visible = false;
this.Cursor = Cursors.Arrow;
}
return(ResultStatus.Fail);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
Workbook workbook = new Workbook();
DataTable dtAdd = new DataTable();
DataTable dtSub = new DataTable();
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);//Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, ls_filename);
DateTime date = txtDate.DateTimeValue;
string yearQ = "";
int sheetIndex = exportType.EditValue.AsInt(), rowStart = 6, colStart = 0, rowEnd = 0;
int sheetType = exportType.EditValue.AsInt();
if (txt.EditValue == null)
{
MessageDisplay.Info("請輸入季度資訊 ! ");
ExportShow.Hide();
return(ResultStatus.FailButNext);
}
try {
workbook.LoadDocument(destinationFilePath);
Worksheet worksheet = workbook.Worksheets[sheetType];
yearQ = txt.EditValue.ToString();
dtAdd = dao35020.GenAddReport(yearQ, date, date);
if (dtAdd.Rows.Count <= 0)
{
ExportShow.Text = date.ToShortDateString() + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!";
return(ResultStatus.Fail);
}
dtSub = dao35020.GenSubReport(yearQ, date, date);
if (dtSub.Rows.Count <= 0)
{
ExportShow.Text = date.ToShortDateString() + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!";
return(ResultStatus.Fail);
}
worksheet.Cells[2, 0].Value = worksheet.Cells[2, 0].Value + txt.EditValue.ToString();
switch (sheetType)
{
case 0: {
//新增部分報表
worksheet.Import(dtAdd, false, rowStart, colStart);
worksheet.Import(dtSub, false, rowStart, colStart + 2);
rowEnd = Math.Max(dtAdd.Rows.Count, dtSub.Rows.Count);
break;
}
case 1: {
//減少部分報表
worksheet.Import(dtAdd, false, rowStart, colStart);
rowEnd = dtAdd.Rows.Count;
break;
}
case 2: {
//減少部分報表
worksheet.Import(dtSub, false, rowStart, colStart);
rowEnd = dtSub.Rows.Count;
break;
}
}
//刪除空白列
Range ra = worksheet.Range[(rowEnd + rowStart + 1).ToString() + ":250"];
ra.Delete(DeleteMode.EntireRow);
workbook.SaveDocument(destinationFilePath);
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
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);
}
protected override ResultStatus Export()
{
try {
#region 輸入&日期檢核
if (gbStatistics.EditValue.AsString() == "rbMon")
{
if (string.Compare(txtStartMonth.Text, txtEndMonth.Text) > 0)
{
MessageDisplay.Error("月份起始年月不可小於迄止年月!", GlobalInfo.ErrorText);
return(ResultStatus.Fail);
}
}
else
{
if (string.Compare(txtStartDate.Text, txtEndDate.Text) > 0)
{
MessageDisplay.Error(CheckDate.Datedif, 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);
//1.複製檔案 & 開啟檔案
string excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
Workbook workbook = new Workbook();
workbook.LoadDocument(excelDestinationPath);
//2.填資料
bool res = false;
if (gbStatistics.EditValue.AsString() == "rbMon")
{
res = wf_30611(workbook);
}
else
{
res = wf_30612(workbook);
}
if (!res)
{
workbook = null;
File.Delete(excelDestinationPath);
return(ResultStatus.Fail);
}
//存檔
workbook.SaveDocument(excelDestinationPath);
labMsg.Visible = false;
return(ResultStatus.Success);
} catch (Exception ex) {
WriteLog(ex);
} finally {
panFilter.Enabled = true;
labMsg.Text = "";
labMsg.Visible = false;
this.Cursor = Cursors.Arrow;
}
return(ResultStatus.Fail);
}
protected override ResultStatus Export()
{
try
{
#region 輸入&日期檢核
if (string.Compare(txtStartDate.Text, txtEndDate.Text) > 0)
{
MessageDisplay.Error(CheckDate.Datedif, GlobalInfo.ErrorText);
return(ResultStatus.Fail);
}
#endregion
//1. ready
panFilter.Enabled = false;
labMsg.Visible = true;
labMsg.Text = "開始轉檔...";
this.Cursor = Cursors.WaitCursor;
this.Refresh();
Thread.Sleep(5);
string market = gbMarket.EditValue.AsString();
string pcCode = gbPC.EditValue.AsString();
string kindId = dwKindId.Text.AsString();
string ws = "";
DataTable dt = new DataTable();
//4. 填資料
if (market == "rbTFXM")//現貨
{
dt = dao30418.ListTfxmData(StartDate, EndDate);
ws = "現貨市場三大法人";
}
else if (market == "rbFut")//期貨市場
{
ws = "期貨市場三大法人";
dt = dao30418.ListFutAndOptData(txtStartDate.DateTimeValue, txtEndDate.DateTimeValue, pcCode, kindId);
}
if (dt.Rows.Count == 0)
{
MessageDisplay.Info(MessageDisplay.MSG_NO_DATA);
return(ResultStatus.Fail);
}
//2. copy template xls to target path
string excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
//3. open xls
Workbook workbook = new Workbook();
workbook.LoadDocument(excelDestinationPath);
Worksheet worksheet = workbook.Worksheets[ws];
worksheet.Import(dt, false, 1, 0);
workbook.Worksheets.ActiveWorksheet = worksheet;
//5. save
workbook.SaveDocument(excelDestinationPath);
labMsg.Visible = false;
if (FlagAdmin)
{
System.Diagnostics.Process.Start(excelDestinationPath);
}
return(ResultStatus.Success);
}
catch (Exception ex)
{
WriteLog(ex);
}
finally
{
panFilter.Enabled = true;
labMsg.Text = "";
labMsg.Visible = false;
this.Cursor = Cursors.Arrow;
}
return(ResultStatus.Fail);
}
protected override ResultStatus Export()
{
try {
#region 輸入&日期檢核
string lsRtn = PbFunc.f_get_jsw(_ProgramID, "E", txtEndDate.Text);
DialogResult liRtn;
if (lsRtn != "Y")
{
liRtn = MessageDisplay.Choose(string.Format("{0} 統計資料未轉入完畢,是否要繼續?", txtEndDate.Text), MessageBoxDefaultButton.Button2, GlobalInfo.QuestionText);
if (liRtn == DialogResult.No)
{
labMsg.Visible = false;
Cursor.Current = Cursors.Arrow;
return(ResultStatus.Fail);
} //if (liRtn == DialogResult.Yes)
} //if (lsRtn != "Y")
//if (!txtStartDate.IsDate(txtStartDate.Text , CheckDate.Start)
// || !txtEndDate.IsDate(txtEndDate.Text , CheckDate.End)) {
// return ResultStatus.Fail; ;
//}
if (string.Compare(txtStartDate.Text, txtEndDate.Text) > 0)
{
MessageDisplay.Error(CheckDate.Datedif, GlobalInfo.ErrorText);
return(ResultStatus.Fail);;
}
#endregion
//1. ready
panFilter.Enabled = false;
labMsg.Visible = true;
labMsg.Text = "開始轉檔...";
this.Cursor = Cursors.WaitCursor;
this.Refresh();
Thread.Sleep(5);
//2. copy template xls to target path
string excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
//3. open xls
Workbook workbook = new Workbook();
workbook.LoadDocument(excelDestinationPath);
//4. write data
int row;
bool res1 = false, res2 = false, res3 = false;
row = 3;
res1 = wf_30410(workbook, SheetNo.tradeSum, row);
row = 4; //PB這邊帶1，但進去後帶回4
res2 = wf_30411(workbook, SheetNo.tradeDetail, row);
row = 4; //PB這邊帶1，但進去後帶回4
res3 = wf_30412(workbook, SheetNo.oint, row);
if (!res1 && !res2 && !res3)
{
File.Delete(excelDestinationPath);
return(ResultStatus.Fail);
}
//5. save
workbook.SaveDocument(excelDestinationPath);
labMsg.Visible = false;
//if (FlagAdmin)
// System.Diagnostics.Process.Start(excelDestinationPath);
return(ResultStatus.Success);
} catch (Exception ex) {
WriteLog(ex);
} finally {
panFilter.Enabled = true;
labMsg.Text = "";
labMsg.Visible = false;
this.Cursor = Cursors.Arrow;
}
return(ResultStatus.Fail);
}
protected override ResultStatus Export()
{
try {
lblProcessing.Visible = true;
dao30090 = new D30090();
string rptId, file, rptName, kindId, kindIdName;
int rowNum, colNum;
rptId = "30090";
rptName = "Position-Transfer to TAIFEX by Investor Group";
lblProcessing.Text = rptId + "－" + rptName + " 轉檔中...";
//讀取資料
DataTable dt30090 = dao30090.d_30090(txtSDate.Text.Replace("/", ""), txtEDate.Text.Replace("/", ""));
if (dt30090.Rows.Count == 0)
{
MessageDisplay.Info(GlobalInfo.OCF_DATE.ToString("yyyyMM") + "," + rptId + '－' + rptName + ",無任何資料!");
lblProcessing.Visible = false;
return(ResultStatus.Fail);
}
//複製檔案
file = PbFunc.wf_copy_file(rptId, rptId);
if (file == "")
{
return(ResultStatus.Fail);
}
logTxt = file;
//開啟檔案
Workbook workbook = new Workbook();
workbook.LoadDocument(file);
#region wf_30090
//切換sheet
Worksheet ws30090 = workbook.Worksheets[0];
ws30090.Cells[1, 1].Value = "Date:" + txtSDate.Text + "～" + txtEDate.Text; //填寫搜尋日期
//填入資料
kindId = "";
rowNum = 2;
foreach (DataRow dr in dt30090.Rows)
{
if (kindId != dr["AE1_PARAM_KEY"].AsString())
{
kindId = dr["AE1_PARAM_KEY"].AsString();
rowNum = rowNum + 1;
switch (kindId)
{
case "TXF":
kindIdName = "FTX";
break;
case "TXO":
kindIdName = "OTX";
break;
case "ZZZ":
kindIdName = "合計";
break;
default:
kindIdName = kindId;
break;
}
ws30090.Cells[rowNum, 1].Value = kindIdName;
}
colNum = 0;
switch (dr["AE1_IDFG_TYPE"].AsString())
{
case "1": //證券自營
colNum = 3;
break;
case "2": //證券投信
colNum = 4;
break;
case "3": //外資
colNum = 5;
break;
case "4": //期貨經理事業
colNum = 6;
break;
case "5": //一般法人
colNum = 7;
break;
case "6": //期貨自營商
colNum = 9;
break;
case "7": //自然人
colNum = 2;
break;
case "8": //一般法人
colNum = 8;
break;
default:
continue;
}
if (rowNum > 0 && colNum > 0)
{
if (dr["AE1_ACCEPTED_OI"] != DBNull.Value)
{
ws30090.Cells[rowNum, colNum].Value = dr["AE1_ACCEPTED_OI"].AsDecimal();
}
}
}
#endregion
//存檔
ws30090.ScrollToRow(0);
workbook.SaveDocument(file);
lblProcessing.Text = "轉檔成功";
}
catch (Exception ex) {
MessageDisplay.Error("輸出錯誤");
throw ex;
}
return(ResultStatus.Success);
}
protected override ResultStatus Retrieve()
{
DataTable dt = dao40080.GetData(txtTradeDate.DateTimeValue);
DataTable dtSp2 = dao40080.GetSP2Data(txtTradeDate.DateTimeValue);
#region 設定生效日期
int ll_found = 0;;
//Group1
if (dt.Select("sp1_osw_grp='1' and sp2_value_date is not null").Length != 0) //有找到
{
ll_found = dt.Rows.IndexOf(dt.Select("sp1_osw_grp='1' and sp2_value_date is not null")[0]); //ll_found = 找到的列數(從0開始)
}
else
{
ll_found = -1;
}
if (ll_found >= 0) //有找到
{
string sp2ValueDate = dt.Rows[ll_found]["sp2_value_date"].AsDateTime().ToString("yyyy/MM/dd");
txtDate1.Text = sp2ValueDate;
}
else //沒找到
{
string tmpDate = PbFunc.f_get_ocf_next_n_day(txtTradeDate.DateTimeValue, 1).ToString("yyyy/MM/dd");
txtDate1.Text = tmpDate;
}
//Group2
if (dt.Select("sp1_osw_grp='5' and sp2_value_date is not null").Length != 0)
{
ll_found = dt.Rows.IndexOf(dt.Select("sp1_osw_grp='5' and sp2_value_date is not null")[0]);
}
else
{
ll_found = -1;
}
if (ll_found >= 0)
{
string sp2ValueDate = dt.Rows[ll_found]["sp2_value_date"].AsDateTime().ToString("yyyy/MM/dd");
txtDate2.Text = sp2ValueDate;
}
else
{
string tmpDate = PbFunc.f_get_ocf_next_n_day(txtTradeDate.DateTimeValue, 2).ToString("yyyy/MM/dd");
txtDate2.Text = tmpDate;
}
#endregion
gcMain.Visible = true;
gcMain.DataSource = dt;
GridHelper.SetCommonGrid(gvMain);
gvMain.OptionsBehavior.AllowFixedGroups = DefaultBoolean.True;
gvMain.Columns["OSW_GRP"].Group();
gvMain.Columns["SP1_CHANGE_RANGE"].DisplayFormat.FormatType = FormatType.Numeric;
gvMain.Columns["SP1_CHANGE_RANGE"].DisplayFormat.FormatString = "P";
gvMain.AppearancePrint.HeaderPanel.Options.UseTextOptions = true;
gvMain.ColumnPanelRowHeight = 20;
gvMain.AppearancePrint.HeaderPanel.Font = new Font("Microsoft YaHei", gvMain.AppearancePrint.HeaderPanel.Font.Size);
gvMain.AppearancePrint.Row.Font = new Font("Microsoft YaHei", 12);
gvMain.OptionsPrint.AllowMultilineHeaders = true;
gvMain.AppearancePrint.GroupRow.Font = new Font("Microsoft YaHei", 12);
gvMain.BestFitColumns();
gvMain.ExpandAllGroups();
gcMain.Focus();
return(ResultStatus.Success);
}
/// <summary>
/// 按下[匯出]按鈕時
/// </summary>
/// <returns></returns>
protected override ResultStatus Export()
{
//1.check
if (gvKind.DataRowCount <= 0)
{
MessageDisplay.Normal("選擇的日期必須有契約資訊,請重新選擇日期");
return(ResultStatus.Fail);
}
//1.1檢查最少必須勾選一筆商品
gvDate.CloseEditor(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
gvDate.UpdateCurrentRow(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
gvKind.CloseEditor(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
gvKind.UpdateCurrentRow(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
DataTable dtTemp = (DataTable)gcKind.DataSource;
bool haveKind = false;
foreach (DataRow drKind in dtTemp.Rows)
{
if (drKind["CPR_SELECT"].AsString() == "Y")
{
haveKind = true;
break;
}
}
if (!haveKind)
{
MessageDisplay.Normal("必須勾選一筆契約");
return(ResultStatus.Fail);
}
if (chkModel.CheckedItemsCount < 1)
{
MessageDisplay.Error("請至少勾選一種指標種類", GlobalInfo.ErrorText);
return(ResultStatus.Fail);
}
//1.2檢查統計資料是否已經轉入完畢
string FinishedJob = PbFunc.f_get_jsw(_ProgramID, "E", txtEndDate.Text);
if (FinishedJob != "Y")
{
DialogResult chooseResult = MessageDisplay.Choose(string.Format("{0} 統計資料未轉入完畢,是否要繼續?", txtEndDate.Text), MessageBoxDefaultButton.Button2, GlobalInfo.QuestionText);
if (chooseResult != DialogResult.Yes)
{
return(ResultStatus.Fail);
}
}
try {
//2.開始轉出資料
panFilter.Enabled = panSecond.Enabled = false;
labMsg.Visible = true;
labMsg.Text = "開始轉檔...";
this.Refresh();
//2.1 copy template xlsx to target path and open
Workbook workbook = new Workbook();
string originalFilePath = Path.Combine(GlobalInfo.DEFAULT_EXCEL_TEMPLATE_DIRECTORY_PATH, _ProgramID + "." + FileType.XLSX.ToString().ToLower());
string excelDestinationPath = "";
DataTable dtDate = (DataTable)gcDate.DataSource;
#region 指標:SMA,EWMA,MAX
foreach (CheckedListBoxItem item in chkModel.Items)
{
if (item.CheckState == CheckState.Unchecked)
{
continue;
}
int sheetIndex = 0;
int flag = 0;
string modelType = "";
switch (item.Value)
{
case "chkSma":
excelDestinationPath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH,
_ProgramID + "_SMA_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss.") + FileType.XLSX.ToString().ToLower());
modelType = "S";
break;
case "chkEwma":
excelDestinationPath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH,
_ProgramID + "_EWMA_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss.") + FileType.XLSX.ToString().ToLower());
modelType = "E";
break;
case "chkMax":
excelDestinationPath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH,
_ProgramID + "_MAX_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss.") + FileType.XLSX.ToString().ToLower());
modelType = "M";
break;
}
//copy template and change filename
File.Copy(originalFilePath, excelDestinationPath, true);
workbook.LoadDocument(excelDestinationPath);
#region 時段
//每個時間區間為一個sheet,總共5個
foreach (DataRow drDate in dtDate.Rows)
{
string monDiff = drDate["MON_DIFF"].AsString(); //期間
string startDate = drDate["SDATE"].AsDateTime().ToString("yyyyMMdd"); //資料起日
string endDate = drDate["EDATE"].AsDateTime().ToString("yyyyMMdd"); //資料迄日
//DateTime startDate = drDate["SDATE"].AsDateTime();//資料起日
//DateTime endDate = drDate["EDATE"].AsDateTime();//資料迄日
int dayCount = drDate["DAY_CNT"].AsInt(); //天數
//2.2跳到指定sheet,寫檔頭
Worksheet worksheet = workbook.Worksheets[sheetIndex++];
worksheet.Cells[1, 2].Value = cbxSubType.Text;
worksheet.Cells[2, 11].Value = string.Format("列印日期：{0}", DateTime.Now.ToString("yyyy/MM/dd"));
worksheet.Cells[2, 1].Value = string.Format("{0}({1}～{2})，計{3}天",
monDiff,
drDate["SDATE"].AsDateTime().ToString("yyyy/MM/dd"),
drDate["EDATE"].AsDateTime().ToString("yyyy/MM/dd"),
dayCount.ToString());
#region //2.3分別讀取每個商品的詳細資訊
int rowIndex = 7;
int emptyRowCount = 60; //template 空白行的數量
int kindCount = 0;
//逐一看勾選的商品有哪些
foreach (DataRow drKind in dtTemp.Rows)
{
if (drKind["CPR_SELECT"].AsString() != "Y")
{
continue;
}
kindCount++;
string kindId = drKind["cpr_kind_id"].AsString(); //契約ID
Decimal riskRate = drKind["cpr_price_risk_rate"].AsDecimal(); //現行最小風險價格係數
Decimal interval = drKind["risk_interval"].AsDecimal(); //最小風險價格係數級距
//2.3.1讀取子table data
DataTable dtSingleKind = dao48030.ListKindByKindId(startDate, endDate, riskRate, interval, kindId, modelType);
if (dtSingleKind.Rows.Count <= 0)
{
labMsg.Text += string.Format("{0},{1}~{2}無任何資料!\r\n", kindId, startDate, endDate);
this.Refresh();
continue;
}
//2.3.2寫入明細
//ken,原則上一個商品只會找到一筆明細(已經group by)
DataRow drDetail = dtSingleKind.Rows[0];
int tempCount = drDetail["cnt"].AsInt();
Decimal level_1 = drDetail["level_1"].AsDecimal();
Decimal level_23 = drDetail["level_23"].AsDecimal();
Decimal level_4 = drDetail["level_4"].AsDecimal();
worksheet.Cells[rowIndex, 0].Value = drDetail["mg1_kind_id"].AsString();
worksheet.Cells[rowIndex, 1].Value = drDetail["avg_risk"].AsDecimal();
worksheet.Cells[rowIndex, 2].Value = drDetail["max_risk"].AsDecimal();
worksheet.Cells[rowIndex, 3].Value = drDetail["min_risk"].AsDecimal();
worksheet.Cells[rowIndex, 4].Value = riskRate;
worksheet.Cells[rowIndex, 6].Value = level_1;
worksheet.Cells[rowIndex, 7].Value = Math.Round(level_1 / tempCount, 4, MidpointRounding.AwayFromZero);
worksheet.Cells[rowIndex, 8].Value = level_23;
worksheet.Cells[rowIndex, 9].Value = Math.Round(level_23 / tempCount, 4, MidpointRounding.AwayFromZero);
worksheet.Cells[rowIndex, 10].Value = level_4;
worksheet.Cells[rowIndex, 11].Value = Math.Round(level_4 / tempCount, 4, MidpointRounding.AwayFromZero);
worksheet.Cells[rowIndex, 12].Value = interval;
rowIndex++;
flag++;
}//foreach (DataRow drKind in dtTemp.Rows) 商品
#endregion
//2.4刪除空白列 (結束商品foreach才刪除空白列，跑下一個sheet)
if (kindCount < emptyRowCount)
{
worksheet.Rows.Remove(rowIndex, emptyRowCount - kindCount);
}
}//foreach (DataRow drDate in dtDate.Rows) 時段
#endregion
//2.9存檔
if (flag > 0)
{
workbook.SaveDocument(excelDestinationPath);
}
else
{
File.Delete(excelDestinationPath);
}
}//foreach (CheckedListBoxItem item in chkModel.Items) 指標
#endregion
//if (FlagAdmin)
// System.Diagnostics.Process.Start(excelDestinationPath);
return(ResultStatus.Success);
} catch (Exception ex) {
WriteLog(ex);
} finally {
panFilter.Enabled = panSecond.Enabled = true;
labMsg.Text = "";
labMsg.Visible = false;
}
return(ResultStatus.Fail);
}
/// <summary>
/// wf_30333()
/// 30398,30399,30393相同
/// </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 Wf30333(string IsKindID = "GTF", string SheetName = "data_30398abc", int RowIndex = 3, int RowTotal = 12)
{
try {
//切換Sheet
Worksheet worksheet = _workbook.Worksheets[SheetName];
//總列數
int sumRowIndex = RowTotal + RowIndex + 1; //小計行數
int addRowCount = 0; //總計寫入的行數
worksheet.Rows[sumRowIndex][1 - 1].Value = $"{PbFunc.Left(_emMonthText, 4).AsInt() - 1911}小計";
string lsYMD = "";
//讀取資料
DataTable dt = daoAM2.ListAM2(IsKindID, $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", ""));
//寫入資料
foreach (DataRow row in dt.Rows)
{
if (lsYMD != row["AM2_YMD"].AsString())
{
RowIndex = RowIndex + 1;
lsYMD = row["AM2_YMD"].AsString();
//li_month_cnt = li_month_cnt + 1;
addRowCount++;
worksheet.Rows[RowIndex][1 - 1].Value = $"{PbFunc.Left(lsYMD, 4).AsInt() - 1911}/{PbFunc.Right(lsYMD, 2)}";
}
//判斷欄位
int columnIndex = IDFGtype(row);
worksheet.Rows[RowIndex][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal();//買 or 賣
}
//刪除空白列
if (RowTotal > addRowCount)
{
worksheet.Rows.Hide(RowIndex + 1, RowIndex + (RowTotal - addRowCount)); //隱藏代替刪除，這樣就不用再重新抓取圖表範圍
worksheet.ScrollTo(0, 0); //直接滾動到最上面，不然看起來很像少行數
}
}
catch (Exception ex) {
#if DEBUG
throw new Exception($"Wf30333:" + ex.Message);
#else
throw ex;
#endif
}
return(MessageDisplay.MSG_OK);
}
protected override ResultStatus Export()
{
base.Export();
#region 確認統計資料未轉入完畢
string lsRtn = PbFunc.f_get_jsw(_ProgramID, "E", txtDate.Text);
DialogResult liRtn;
if (lsRtn != "Y")
{
liRtn = MessageDisplay.Choose(String.Format("{0} 統計資料未轉入完畢,是否要繼續?", txtDate.Text));
if (liRtn == DialogResult.No)
{
labMsg.Visible = false;
Cursor.Current = Cursors.Arrow;
return(ResultStatus.Fail);
}
}//if (lsRtn != "Y")
#endregion
try {
//1. 判斷為單一商品還是全部
DataTable dt = dao40130.GetDataList(txtDate.DateTimeValue, "%");
DataTable dtSelect = new DataTable();
string kindId = dwKindId.EditValue.AsString();
if (kindId != "%")
{
dtSelect = dt.Filter("mgt2_kind_id = '" + kindId + "'");
}
else //kindId = "%"
{
dtSelect = dt.Filter("mgt2_kind_id <> '%'");
}
//1.1 準備開檔
string originalFilePath = "";
string destinationFilePath = "";
Workbook workbook = new Workbook();
//2. 填資料
foreach (DataRow dr in dtSelect.Rows)
{
string fileKind = dr["mgt2_kind_id"].AsString();
//2.1 開啟&複製檔案(因可能有多個excel所以在迴圈裡兜路徑)
originalFilePath = Path.Combine(GlobalInfo.DEFAULT_EXCEL_TEMPLATE_DIRECTORY_PATH,
string.Format("{0}.{1}", _ProgramID, FileType.XLS.ToString().ToLower()));
destinationFilePath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH,
string.Format("{0}({1})_{2}.{3}", _ProgramID, fileKind, DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss"), FileType.XLS.ToString().ToLower()));
File.Copy(originalFilePath, destinationFilePath, true);
workbook.LoadDocument(destinationFilePath);
kindId = dr["mgt2_kind_id"].AsString() + "%";
string tmpKindId = kindId.SubStr(2, 1);
if (tmpKindId == "F") //Sheet:期貨data
{
wf_40131(workbook, kindId);
}
else //Sheet:選擇權data
{
wf_40132(workbook, kindId);
}
//3. save
workbook.SaveDocument(destinationFilePath);
}//foreach (DataRow dr in dt.Rows)
labMsg.Visible = false;
return(ResultStatus.Success);
} catch (Exception ex) {
WriteLog(ex);
} finally {
panFilter.Enabled = true;
labMsg.Text = "";
labMsg.Visible = false;
this.Cursor = Cursors.Arrow;
}
return(ResultStatus.Fail);
}
protected override ResultStatus Export()
{
dao30205 = new D30205();
string rptId, file;
rptId = "30205";
//複製檔案
file = PbFunc.wf_copy_file(rptId, rptId);
if (file == "")
{
return(ResultStatus.Fail);
}
logTxt = file;
//開啟檔案
Workbook workbook = new Workbook();
workbook.LoadDocument(file);
#region wf_30205
string rptName, raiseStr, lowerStr;
int f, g, rowNum;
rptName = "指數類歷次部位限制查詢";
rptId = "30205";
//讀取資料
DataTable dt30205 = dao30205.d_30205(txtSDate.Text.Replace("/", ""), txtEDate.Text.Replace("/", ""));
if (dt30205.Rows.Count == 0)
{
MessageDisplay.Info(txtSDate.Text + "～" + txtEDate.Text + "," + rptId + '－' + rptName + ",無任何資料!");
//若所有Sheet皆無資料時，刪除檔案
workbook = null;
System.IO.File.Delete(file);
return(ResultStatus.Fail);
}
//切換sheet
Worksheet ws30205 = workbook.Worksheets[0];
ws30205.Cells[1, 0].Value = ws30205.Cells[1, 0].Value.ToString() + txtSDate.Text + "～" + txtEDate.Text; //填寫公告日期
//填入資料
rowNum = 2;
for (f = 0; f < dt30205.Rows.Count; f++)
{
DataRow dr = dt30205.Rows[f];
rowNum = rowNum + 1;
raiseStr = "";
lowerStr = "";
string pl2Ymd, pl2EffectiveYmd;
pl2Ymd = dr["PL2_YMD"].AsString();
pl2EffectiveYmd = dr["PL2_EFFECTIVE_YMD"].AsString();
//運算欄位cp_grp_cnt("count( pl2_kind_id for group 1 )")
//group 1 ("pl2_ymd" , "pl2_effective_ymd)
int cpGrpCnt = dt30205.Compute("count( pl2_kind_id)", $@"PL2_YMD='{pl2Ymd}' and PL2_EFFECTIVE_YMD='{pl2EffectiveYmd}'").AsInt();
for (g = 1; g <= cpGrpCnt; g++)
{
if (dt30205.Rows[f + g - 1]["PL2_NATURE_ADJ"].AsString() == "+" ||
dt30205.Rows[f + g - 1]["PL2_LEGAL_ADJ"].AsString() == "+" ||
dt30205.Rows[f + g - 1]["PL2_999_ADJ"].AsString() == "+")
{
raiseStr = raiseStr + dt30205.Rows[f + g - 1]["PL2_KIND_ID"].AsString() + "/";
}
else
{
lowerStr = lowerStr + dt30205.Rows[f + g - 1]["PL2_KIND_ID"].AsString() + "/";
}
}
f = f + cpGrpCnt - 1;
if (dr["PL2_YMD"] != DBNull.Value)
{
ws30205.Cells[rowNum, 0].Value = dr["PL2_YMD"].AsDateTime("yyyyMMdd").ToString("yyyy/M/d");
}
if (dr["PL2_EFFECTIVE_YMD"] != DBNull.Value)
{
ws30205.Cells[rowNum, 1].Value = dr["PL2_EFFECTIVE_YMD"].AsDateTime("yyyyMMdd").ToString("yyyy/M/d");
}
ws30205.Cells[rowNum, 2].Value = raiseStr.SubStr(0, raiseStr.Length - 1);
ws30205.Cells[rowNum, 3].Value = lowerStr.SubStr(0, lowerStr.Length - 1);
}
#endregion
//存檔
ws30205.ScrollToRow(0);
workbook.SaveDocument(file);
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 override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
Workbook workbook = new Workbook();
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
string sYm = txtSDate.DateTimeValue.ToString("yyyyMM");
string eYm = txtEDate.DateTimeValue.ToString("yyyyMM");
string eymd = string.IsNullOrEmpty(dao30760.GetMaxDate(eYm)) ? eYm + "01" : dao30760.GetMaxDate(eYm);//讀取迄年月的最大之交易日
int oleRow = 5, oleCol = 0;
try {
workbook.LoadDocument(destinationFilePath);
int noData = 0;
//各產品資料
Worksheet worksheet = workbook.Worksheets[0];
DataTable dtProdData = dao30760.GetProdData(sYm, eYm, eymd);
DataTable dtSum = dao30760.GetSumData(sYm, eYm, eymd);
if (dtProdData.Rows.Count <= 0)
{
ExportShow.Text = "各產品無任何資料!";
noData += 1;
}
ProdData(worksheet, dtProdData, dtSum, oleRow, oleCol);
//個股選擇權資料
worksheet = workbook.Worksheets[1];
DataTable dtTradedData = dao30760.GetTradedData(sYm, eYm, eymd, "O");
if (dtTradedData.Rows.Count <= 0)
{
ExportShow.Text = "個股選擇權無任何資料!";
noData += 1;
}
oleRow = 5;
TradedData(worksheet, dtTradedData, oleRow, oleCol);
//個股期貨資料
worksheet = workbook.Worksheets[2];
DataTable dtFuturesData = dao30760.GetFuturesData(sYm, eYm, eymd, "F");
if (dtFuturesData.Rows.Count <= 0)
{
ExportShow.Text = "個股期貨無任何資料!";
noData += 1;
}
oleRow = 5;
FuturesData(worksheet, dtFuturesData, oleRow, oleCol);
//Save
workbook.SaveDocument(destinationFilePath);
//完全無資料時, 刪檔
if (noData == 3)
{
File.Delete(destinationFilePath);
return(ResultStatus.FailButNext);
}
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
private bool ManipulateExcel()
{
//測試資料查詢日期:2017/12/01
try {
txtFromDate.Enabled = false;
txtToDate.Enabled = false;
this.Cursor = Cursors.WaitCursor;
this.Refresh();
Thread.Sleep(5);
lblProcessing.Visible = true;
ShowMsg("開始轉檔...");
string rptName, rptId, file;
int i;
/*************************************
* ls_rpt_name = 報表名稱
* ls_rpt_id = 報表代號
* li_ole_col = 欄位位置
* ls_param_key = 契約
*************************************/
rptName = "STF報價期貨商明細加總日報表";
rptId = "50072";
ShowMsg(rptId + "－" + rptName + " 轉檔中...");
#region Excel
//讀取資料
DataTable dtContent = dao50072.ListData(txtFromDate.FormatValue, txtToDate.FormatValue);
if (dtContent.Rows.Count == 0)
{
MessageDisplay.Info(string.Format("{0} - {1}, {2} - {3},無任何資料!", txtFromDate.Text, txtToDate.Text, rptId, rptName));
}
DataTable dtValidContent = dao50072.ListData_valid(txtFromDate.FormatValue, txtToDate.FormatValue);
if (dtValidContent.Rows.Count == 0)
{
MessageDisplay.Info(string.Format("{0} - {1}, {2} - {3},無任何資料!", txtFromDate.Text, txtToDate.Text, rptId, "合格報價資料"));
}
//複製檔案
file = PbFunc.wf_copy_file(rptId, rptId);
if (file == "")
{
return(false);
}
//填資料
Workbook workbook = new Workbook();
workbook.LoadDocument(file);
Worksheet worksheet0 = workbook.Worksheets[0];
Worksheet worksheet2 = workbook.Worksheets[2];
//50072
for (i = 0; i < dtContent.Rows.Count; i++)
{
int rowNum = i + 2;
worksheet0.Cells[rowNum, 0].Value = dtContent.Rows[i]["mc_date"].AsString();
worksheet0.Cells[rowNum, 1].Value = dtContent.Rows[i]["fut_id"].AsString();
worksheet0.Cells[rowNum, 2].Value = dtContent.Rows[i]["acctno"].AsString();
worksheet0.Cells[rowNum, 3].Value = dtContent.Rows[i]["param_key"].AsString();
worksheet0.Cells[rowNum, 4].SetValue(dtContent.Rows[i]["valid_cnt"]);
worksheet0.Cells[rowNum, 5].SetValue(dtContent.Rows[i]["valid_time"]);
worksheet0.Cells[rowNum, 6].SetValue(dtContent.Rows[i]["valid_result"]);
worksheet0.Cells[rowNum, 7].SetValue(dtContent.Rows[i]["qty"]);
worksheet0.Cells[rowNum, 8].SetValue(dtContent.Rows[i]["nqty"]);
worksheet0.Cells[rowNum, 9].Value = dtContent.Rows[i]["prod_type"].AsString();
worksheet0.Cells[rowNum, 10].SetValue(dtContent.Rows[i]["drank"]);
}
//合格報價資料
for (i = 0; i < dtValidContent.Rows.Count; i++)
{
int rowNum = i + 1;
worksheet2.Cells[rowNum, 0].Value = dtValidContent.Rows[i]["mc_date"].AsString();
worksheet2.Cells[rowNum, 1].Value = dtValidContent.Rows[i]["fut_id"].AsString();
worksheet2.Cells[rowNum, 2].Value = dtValidContent.Rows[i]["acctno"].AsString();
worksheet2.Cells[rowNum, 3].Value = dtValidContent.Rows[i]["activity_type"].AsString();
worksheet2.Cells[rowNum, 4].Value = dtValidContent.Rows[i]["kind_id"].AsString();
worksheet2.Cells[rowNum, 5].SetValue(dtValidContent.Rows[i]["valid_time"]);
worksheet2.Cells[rowNum, 6].Value = dtValidContent.Rows[i]["prod_type"].AsString();
worksheet2.Cells[rowNum, 7].Value = dtValidContent.Rows[i]["market_close"].AsString();
}
#endregion
#region CSV
/******************
* ETF
******************/
//讀取資料
string asSymEtf = txtFromDate.Text.Replace("/", "").Substring(0, 6);
string asEymEtf = txtToDate.Text.Replace("/", "").Substring(0, 6);
DataTable dtContentETF = dao50072.ListData_etf(asSymEtf, asEymEtf);
if (dtContentETF.Rows.Count == 0)
{
MessageDisplay.Info(string.Format("{0} - {1}, {2} - {3},無任何資料!", txtFromDate.Text, txtToDate.Text, rptId, "ETF"));
}
else
{
//存CSV
string etfFileName = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, "50072_ETF_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss") + ".csv");
File.Create(etfFileName).Close();
StringBuilder sbETF = new StringBuilder();
IEnumerable <string> etfColumnNames = dtContentETF.Columns.Cast <DataColumn>().
Select(column => column.ColumnName);
sbETF.AppendLine(string.Join(",", etfColumnNames));
foreach (DataRow row in dtContentETF.Rows)
{
IEnumerable <string> fields = row.ItemArray.Select(field =>
string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\""));
sbETF.AppendLine(string.Join(",", fields));
}
File.WriteAllText(etfFileName, sbETF.ToString());
}
/******************
* TXF
******************/
//讀取資料
string asSymTxf = txtFromDate.Text.Replace("/", "").Substring(0, 6);
string asEymTxf = txtToDate.Text.Replace("/", "").Substring(0, 6);
DataTable dtContentTXF = dao50072.ListData_txf(asSymTxf, asEymTxf);
if (dtContentTXF.Rows.Count == 0)
{
MessageDisplay.Info(string.Format("{0} - {1}, {2} - {3},無任何資料!", txtFromDate.Text, txtToDate.Text, rptId, "TXF"));
}
else
{
//存CSV
string txfFileName = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, "50072_TXF_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss") + ".csv");
File.Create(txfFileName).Close();
StringBuilder sbTXF = new StringBuilder();
IEnumerable <string> txfColumnNames = dtContentTXF.Columns.Cast <DataColumn>().
Select(column => column.ColumnName);
sbTXF.AppendLine(string.Join(",", txfColumnNames));
foreach (DataRow row in dtContentTXF.Rows)
{
IEnumerable <string> fields = row.ItemArray.Select(field =>
string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\""));
sbTXF.AppendLine(string.Join(",", fields));
}
File.WriteAllText(txfFileName, sbTXF.ToString());
}
#endregion
#region MTX 已廢除
#endregion
//若所有Sheet皆無資料時，刪除檔案
if (dtContent.Rows.Count == 0 && dtValidContent.Rows.Count == 0)
{
try {
workbook = null;
File.Delete(file);
//File.Delete(etfFileName);
//File.Delete(txfFileName);
}
catch (Exception) {
//
}
return(false);
}
//Excel存檔
workbook.SaveDocument(file);
ShowMsg("轉檔成功");
return(true);
}
catch (Exception ex) {
MessageDisplay.Error("輸出錯誤");
throw ex;
}
finally {
this.Cursor = Cursors.Arrow;
this.Refresh();
Thread.Sleep(5);
txtFromDate.Enabled = true;
txtToDate.Enabled = true;
}
}
protected override ResultStatus Export()
{
try {
this.Cursor = Cursors.WaitCursor;
this.Refresh();
Thread.Sleep(5);
lblProcessing.Visible = true;
ShowMsg("開始轉檔...");
string rptId, file, rptName = "";
date = txtSDate.DateTimeValue.Year + "年" + txtSDate.DateTimeValue.Month + "月" + txtSDate.DateTimeValue.Day + "日";
#region ue_export_before
//判斷盤別
int rtnInt, seq;
string rtnStr, grp;
if (ddlType.Text == "16:15收盤")
{
grp = "1";
}
else
{
grp = "2";
}
//判斷統計資料轉檔已完成
for (int f = 1; f <= 2; f++)
{
if (grp == "1")
{
if (f == 1)
{
seq = 13;
}
else
{
seq = 23;
}
}
else
{
seq = 17;
f = 2;
}
//check JSW
rtnStr = PbFunc.f_get_jsw_seq(_ProgramID, "E", seq, txtSDate.DateTimeValue, "0");
if (rtnStr != "")
{
DialogResult result = MessageDisplay.Choose(txtSDate.Text +
"統計資料未轉入完畢，請確認監視批次「AIG5402」執行完成，再按「是」繼續，若選「否」則會跳過作業不執行。",
MessageBoxDefaultButton.Button2, GlobalInfo.QuestionText);
if (result == DialogResult.No)
{
lblProcessing.Visible = false;
return(ResultStatus.Fail);
}
}
}
#endregion
rptId = "30053";
//複製檔案
file = wfCopy30053(rptId, grp);
if (file == "")
{
return(ResultStatus.Fail);
}
//開啟檔案
Workbook workbook = new Workbook();
workbook.LoadDocument(file);
//切換Sheet
Worksheet ws = workbook.Worksheets["期貨"];
#region 11張報表
int rowIndex = 1;
//1.期貨
if (!wf30053f(grp, rowIndex, ws))
{
return(showEmailMsg(cbxNews.Checked));
}
//2.選擇權
rowIndex = rowIndex + 3;
ws = workbook.Worksheets["選擇權"];
if (!wf30053o(rowIndex, ws))
{
return(showEmailMsg(cbxNews.Checked));
}
//3.股票選擇權
rowIndex = rowIndex + 3;
ws = workbook.Worksheets["股票選擇權"];
if (!wf30053stc(rowIndex, ws))
{
return(showEmailMsg(cbxNews.Checked));
}
//4.股票期貨(For工商時報)
//5.股票期貨(For工商時報)50
rowIndex = rowIndex + 3;
ws = workbook.Worksheets["股票期貨(For工商時報)"];
if (!wf30053stfNear(ws))
{
return(showEmailMsg(cbxNews.Checked));
}
ws = workbook.Worksheets["股票期貨(For工商時報) 50大"];
if (!wfCtee50(ws))
{
return(showEmailMsg(cbxNews.Checked));
}
//6.股票期貨Top10檔(For聯合晚報)
ws = workbook.Worksheets["股票期貨Top10檔(For經濟日報)"];
if (!wf30053stfTop10(ws))
{
return(showEmailMsg(cbxNews.Checked));
}
//7.ETF期貨Top10檔
ws = workbook.Worksheets["ETF期貨Top10檔"];
if (!wf30053etfTop10(ws))
{
return(showEmailMsg(cbxNews.Checked));
}
//8.股票選擇權TOP10檔(For聯合晚報)
ws = workbook.Worksheets["股票選擇權TOP10檔(For聯合晚報)"];
if (!wf30053stcTop10(ws))
{
return(showEmailMsg(cbxNews.Checked));
}
//9.ETF選擇權前20大行情表
ws = workbook.Worksheets["ETF選擇權TOP20檔"];
if (!wf30053etcTop20(ws))
{
return(showEmailMsg(cbxNews.Checked));
}
//10.美元兌人民幣選擇權前20大行情表
ws = workbook.Worksheets["美元兌人民幣選擇權(RHO)TOP20檔"];
if (!wf30053rhoTop20(ws))
{
return(showEmailMsg(cbxNews.Checked));
}
//11.小型美元兌人民幣選擇權前20大行情表
ws = workbook.Worksheets["小型美元兌人民幣選擇權(RTO)TOP20檔"];
if (!wf30053rtoTop20(ws))
{
return(showEmailMsg(cbxNews.Checked));
}
#endregion
//存檔
workbook.SaveDocument(file);
ShowMsg("轉檔完成");
//email
rptId = "30053_" + grp;
if (cbxNews.Checked)
{
DataTable dtTxemail = new TXEMAIL().ListData(rptId, 1);
if (dtTxemail.Rows.Count != 0)
{
string TXEMAIL_SENDER = dtTxemail.Rows[0]["TXEMAIL_SENDER"].AsString();
string TXEMAIL_RECIPIENTS = dtTxemail.Rows[0]["TXEMAIL_RECIPIENTS"].AsString();
string TXEMAIL_CC = dtTxemail.Rows[0]["TXEMAIL_CC"].AsString();
string TXEMAIL_TITLE = dtTxemail.Rows[0]["TXEMAIL_TITLE"].AsString();
string TXEMAIL_TEXT = dtTxemail.Rows[0]["TXEMAIL_TEXT"].AsString();
TXEMAIL_TITLE = txtSDate.DateTimeValue.ToString("yyyyMMdd") + TXEMAIL_TITLE;
MailHelper.SendEmail(TXEMAIL_SENDER, TXEMAIL_RECIPIENTS, TXEMAIL_CC, TXEMAIL_TITLE, TXEMAIL_TEXT, file);
}
}
} catch (Exception ex) {
MessageDisplay.Error("輸出錯誤");
throw ex;
} finally {
this.Cursor = Cursors.Arrow;
this.Refresh();
Thread.Sleep(5);
}
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
try {
#region 輸入&日期檢核
if (string.Compare(txtStartDate.Text, txtEndDate.Text) > 0)
{
MessageDisplay.Error(CheckDate.Datedif, GlobalInfo.ErrorText);
return(ResultStatus.Fail);
}
#endregion
//0.檢查日期跟商品是否有選擇正確
//todo check
if (cbxProd.CheckedItemsCount < 1)
{
MessageDisplay.Warning("請勾選商品!", GlobalInfo.WarningText);
return(ResultStatus.Fail);
}
this.Cursor = Cursors.WaitCursor;
this.Refresh();
Thread.Sleep(5);
labMsg.Visible = true;
ShowMsg("訊息：資料轉出中........");
//1.設定一些變數,把邏輯直接寫在該變數屬性內
List <string> listCode = new List <string>();//多筆,用逗號分隔
if (cbxProd.Items[0].CheckState == CheckState.Checked)
{
listCode.Add("'1'");
}
if (cbxProd.Items[1].CheckState == CheckState.Checked)
{
listCode.Add("'5'");
}
DataTable dtTemp = new SPNT1().ListData();
decimal chi_150 = dtTemp.Rows[0][0].AsDecimal();
decimal chi_180 = dtTemp.Rows[0][1].AsDecimal();
decimal v365 = dtTemp.Rows[0][2].AsDecimal();
//2.開始轉出資料
panFilter.Enabled = false;
panProd.Enabled = false;
//labMsg.Visible = true;
//labMsg.Text = "訊息：資料轉出中........";
//this.Refresh();
//2.1 open xls
string excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID, "");
Workbook workbook = new Workbook();
workbook.LoadDocument(excelDestinationPath);
flag = 0;
//3.write sheet data
if (cbxProd.Items[0].CheckState == CheckState.Checked ||
cbxProd.Items[1].CheckState == CheckState.Checked)
{
//3.1 現貨data
wf_40210_3_old(workbook, StartDate, EndDate, listCode, TotalDayCount);
//3.2 指數選擇權VSR
wf_40210_4(workbook, StartDate, EndDate, listCode, chi_150, chi_180, v365, TotalDayCount);
//3.3 期貨data
wf_40210_5(workbook, StartDate, EndDate, listCode, TotalDayCount);
//3.4 期貨契約PSR
wf_40210_6(workbook, EndDate, listCode);
//3.5 Delta折耗比率
wf_40210_7(workbook, StartDate, EndDate, listCode, TotalDayCount);
//3.6 跨商品折抵比率
wf_40210_8(workbook, EndDate, listCode);
}//if (cbxProd.Items[0].CheckState || cbxProd.Items[1].CheckState)
//3.7 STC VSR計算
if (cbxProd.Items[2].CheckState == CheckState.Checked)
{
wf_40210_1(workbook, "STC", StartDate, EndDate, chi_150, chi_180, v365, TotalDayCount);
}
//3.8 ETC VSR計算
if (cbxProd.Items[3].CheckState == CheckState.Checked)
{
wf_40210_1(workbook, "ETC", StartDate, EndDate, chi_150, chi_180, v365, TotalDayCount);
}
if (flag <= 0)
{
File.Delete(excelDestinationPath);
return(ResultStatus.Fail);
}
else
{
//存檔
workbook.SaveDocument(excelDestinationPath);
ShowMsg("轉檔完成");
//if (FlagAdmin)
// System.Diagnostics.Process.Start(excelDestinationPath);
return(ResultStatus.Success);
}
} catch (Exception ex) {
ShowMsg("轉檔錯誤");
WriteLog(ex);
} finally {
panFilter.Enabled = true;
panProd.Enabled = true;
labMsg.Text = "";
labMsg.Visible = false;
this.Cursor = Cursors.Arrow;
this.Refresh();
Thread.Sleep(5);
}
return(ResultStatus.Fail);
}
protected override ResultStatus Export()
{
dao30020 = new D30020();
string rptId, file;
rptId = "30020";
/******************
* 複製檔案
******************/
file = PbFunc.wf_copy_file(rptId, rptId);
if (file == "")
{
return(ResultStatus.Fail);
}
/******************
* 開啟檔案
******************/
Workbook workbook = new Workbook();
workbook.LoadDocument(file);
#region 30021
string rptName;
string accType;
DateTime date, maxDate;
int i, j, rowNum, colNum, accRow, accRowTol;
long found;
rptName = "期貨交易累計開戶及交易戶數統計表";
rptId = "30021";
/******************
* 讀取資料
******************/
DataTable dt30021 = dao30020.d_30021(txtSDate.DateTimeValue, txtEDate.DateTimeValue);
if (dt30021.Rows.Count == 0)
{
MessageDisplay.Info(txtSDate.Text + "～" + txtEDate.Text + "," + rptId + '－' + rptName + ",無任何資料!");
return(ResultStatus.Fail);
}
//ACC_TYPE
//不撈資料，只要schema
DataTable dt30021_acc_type = dao30020.d_30021_acc_type(null, null);
//切換sheet
Worksheet ws30021 = workbook.Worksheets[0];
/******************
* 身份碼總列數
* 隱藏於A2
******************/
accRowTol = ws30021.Cells[1, 0].Value.AsInt();
date = "1900/1/1".AsDateTime();
maxDate = dt30021.Rows[0]["AB1_DATE"].AsDateTime();
rowNum = 1;
accRow = 2;
colNum = 2;
//ACC_TYPE
for (i = accRow; i < accRowTol; i++)
{
dt30021_acc_type.Rows.Add();
dt30021_acc_type.Rows[dt30021_acc_type.Rows.Count - 1]["AB1_ACC_TYPE"] = ws30021.Cells[i, 0].Value;
}
for (j = 0; j < dt30021.Rows.Count; j++)
{
rowNum = rowNum + 1;
/* 換日期時,Row:重頭開始& Col:加1 */
if (date != dt30021.Rows[j]["AB1_DATE"].AsDateTime())
{
rowNum = 1;
accRow = 2;
colNum = colNum + 1;
date = dt30021.Rows[j]["AB1_DATE"].AsDateTime();
/* 日期 */
ws30021.Cells[rowNum, colNum].Value = date;
/* 累計開戶數日期 */
if (j == 0)
{
ws30021.Cells[rowNum, 2].Value = dt30021.Rows[j]["AB1_DATE"].AsDateTime();
}
}
accType = dt30021.Rows[j]["AB1_ACC_TYPE"].AsString();
//DataTable的Select預設不分大小寫，這邊要將它開啟
dt30021_acc_type.CaseSensitive = true;
if (dt30021_acc_type.Select("AB1_ACC_TYPE = '" + accType + "'").Length > 0)
{
rowNum = dt30021_acc_type.Rows.IndexOf(dt30021_acc_type.Select("AB1_ACC_TYPE = '" + accType + "'")[0]);
}
else
{
rowNum = -1;
}
if (rowNum >= 0)
{
/* 每日 */
rowNum = rowNum + 2;
ws30021.Cells[rowNum, colNum].SetValue(dt30021.Rows[j]["AB1_COUNT"]);
/* 累計開戶數 */
if (date == maxDate)
{
ws30021.Cells[rowNum, 2].SetValue(dt30021.Rows[j]["AB1_ACCU_COUNT"]);
}
}
}
#endregion
#region 30022
rptName = "期貨交易累計開戶及交易戶數統計表";
rptId = "30022";
//讀取資料
/* 往前追溯100天 */
DateTime relDate;
relDate = PbFunc.relativedate(txtEDate.DateTimeValue, -100);
DataTable dt30022 = dao30020.d_30022(relDate, txtEDate.DateTimeValue);
if (dt30022.Rows.Count == 0)
{
MessageDisplay.Info(relDate.ToString("yyyy/MM/dd") + "～" + txtEDate.Text + "," + rptId + '－' + rptName + ",無任何資料!");
return(ResultStatus.Fail);
}
//切換sheet
Worksheet ws30022 = workbook.Worksheets[1];
rowNum = 0;
for (j = 0; j < dt30022.Rows.Count; j++)
{
rowNum = rowNum + 1;
ws30022.Cells[rowNum, 0].Value = dt30022.Rows[j]["AB1_DATE"].AsDateTime();
ws30022.Cells[rowNum, 1].SetValue(dt30022.Rows[j]["AB1_COUNT"]);
ws30022.Cells[rowNum, 2].SetValue(dt30022.Rows[j]["AB1_ACCU_COUNT"]);
ws30022.Cells[rowNum, 3].SetValue(dt30022.Rows[j]["AB1_TRADE_COUNT"]);
}
#endregion
//圖表重設
ChartObject chartObjs = ws30021.Charts[0];
string rcnt = (dt30022.Rows.Count + 1).AsString();
ChartData chartDataArgs = new ChartData();
ChartData chartData1 = new ChartData();
ChartData chartData2 = new ChartData();
Range range1 = ws30022.Range["='30022'!$D$2:$D$" + rcnt];
Range range2 = ws30022.Range["='30022'!$C$2:$C$" + rcnt];
Range args = ws30022.Range["='30022'!$A$2:$A$" + rcnt];
chartData1.RangeValue = range1;
chartData2.RangeValue = range2;
chartDataArgs.RangeValue = args;
chartObjs.Series[0].Arguments = chartDataArgs;
chartObjs.Series[0].Values = chartData1;
chartObjs.Series[1].Arguments = chartDataArgs;
chartObjs.Series[1].Values = chartData2;
workbook.SaveDocument(file);
return(ResultStatus.Success);
}
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);
}
/// <summary>
/// 讀取前條件檢核
/// </summary>
/// <param name="sbrkno">造市者代號起始</param>
/// <param name="ebrkno">造市者代號迄止</param>
/// <returns></returns>
private bool StartRetrieve(string sbrkno = "", string ebrkno = "")
{
/*******************
* 條件值檢核
*******************/
_D500Xx.IsCheck = "N";
/*造市者代號 */
_D500Xx.Sbrkno = dwSbrkno.EditValue.AsString();
if (string.IsNullOrEmpty(_D500Xx.Sbrkno))
{
_D500Xx.Sbrkno = sbrkno;
}
_D500Xx.Ebrkno = dwEbrkno.EditValue.AsString();
if (string.IsNullOrEmpty(_D500Xx.Ebrkno))
{
_D500Xx.Ebrkno = ebrkno;
}
if ((string.Compare(dwSbrkno.SelectedText, dwEbrkno.SelectedText) > 0) && !string.IsNullOrEmpty(_D500Xx.Ebrkno))
{
PbFunc.messageBox(GlobalInfo.ErrorText, "造市者代號起始不可大於迄止", MessageBoxIcon.Stop);
dwEbrkno.Focus();
_D500Xx.IsCheck = "Y";
return(false);
}
/* 商品群組 */
_D500Xx.ProdCategory = dwProdCt.EditValue.AsString();
if (string.IsNullOrEmpty(_D500Xx.ProdCategory) || dwProdCt.Enabled == false)
{
_D500Xx.ProdCategory = "";
}
_D500Xx.ProdCategory = _D500Xx.ProdCategory.AsString() + "%";
/* 商品 */
_D500Xx.ProdKindId = "";
_D500Xx.ProdKindIdSto = dwProdKdSto.EditValue.AsString();
if (string.IsNullOrEmpty(_D500Xx.ProdKindIdSto) || dwProdKdSto.Enabled == false)
{
_D500Xx.ProdKindIdSto = "";
}
_D500Xx.ProdKindIdSto = _D500Xx.ProdKindIdSto.AsString() + "%";
//DateTime dtDate;
/* 月報表 */
if (emStartYM.Visible == true)
{
if (!emStartYM.IsDate(emStartYM.Text + "/01", CheckDate.Start))
{
_D500Xx.IsCheck = "Y";
return(false);
}
_D500Xx.Sdate = emStartYM.Text.Replace("/", "").SubStr(0, 6);
}
if (emEndYM.Visible == true)
{
if (!emEndYM.IsDate(emEndYM.Text + "/01", CheckDate.End))
{
_D500Xx.IsCheck = "Y";
return(false);
}
_D500Xx.Edate = emEndYM.Text.Replace("/", "").SubStr(0, 6);
}
/*******************
* 資料類別
*******************/
_D500Xx.DataType = "Q";
/*******************
* 條件值檢核OK
*******************/
_D500Xx.IsCheck = "Y";
/*******************
* //Local Window
* 條件值檢核
* if is_chk <> 'E' then
* is_chk = 'Y'
* end if
*
* 資料類別:
* 報價:
* is_data_type = 'Q'
* 詢價:
* is_data_type = 'R'
*******************/
return(true);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
Workbook workbook = new Workbook();
DataTable dtYearData = new DataTable();
DataTable dtMontData = new DataTable();
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
string inputMonth = txtDate.DateTimeValue.ToString("yyyyMM");
string inputYear = txtDate.DateTimeValue.Year.ToString();
try {
workbook.LoadDocument(destinationFilePath);
Worksheet worksheet = workbook.Worksheets[0];
string startYear = worksheet.Cells[2, 1].Value.ToString();
int rowTol = worksheet.Cells[2, 0].Value.AsInt();
List <int> ListBIndex = new List <int>();
int rowStart = 4;
for (int i = 1; i <= idfgCount; i++)
{
rowStart = 4;
int bIndex = GetBIndex(i, ListBIndex);
int ymd = 0;
ListBIndex.Add(bIndex);
if (i == 6)
{
i = 7;// idfgtype 跳過6
}
dtYearData = dao30530.ListYearData(startYear, inputYear, inputYear + "01", inputMonth, i.ToString(), bIndex.ToString(), (bIndex + 1).ToString());
dtMontData = dao30530.ListMonthData(inputYear + "01", inputMonth, i.ToString(), bIndex.ToString(), (bIndex + 1).ToString());
if (dtYearData.Rows.Count <= 0)
{
ExportShow.Hide();
MessageDisplay.Info(inputMonth + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!");
File.Delete(destinationFilePath);
return(ResultStatus.Fail);
}
// 年分
for (int j = startYear.AsInt(); j <= inputYear.AsInt(); j++)
{
DataTable dtYear = dtYearData.Filter("AM2_YMD = " + j.ToString());
if (ymd != j)
{
//有資料時才增加列數
if (dtYear.Rows.Count > 0)
{
rowStart++;
}
ymd = j;
}
foreach (DataRow dr in dtYear.Rows)
{
worksheet.Cells[rowStart, 0].Value = ymd;
worksheet.Cells[rowStart, dr["BS_Index"].AsInt()].Value = dr["am2_m_qnty"].AsDecimal();
}
}
//每月
for (int j = (inputYear.ToString() + "01").AsInt(); j <= inputMonth.AsInt(); j++)
{
DataTable dtMon = dtMontData.Filter("AM2_YMD = " + j.ToString());
if (ymd != j)
{
//有資料時才增加列數
if (dtMon.Rows.Count > 0)
{
rowStart++;
}
ymd = j;
}
foreach (DataRow dr in dtMon.Rows)
{
worksheet.Cells[rowStart, 0].Value = dr["am2_ymd"].AsDateTime("yyyyMM").ToString("MMM", CultureInfo.CreateSpecificCulture("en-US")) + ".";
worksheet.Cells[rowStart, dr["BS_Index"].AsInt()].Value = dr["am2_m_qnty"].AsDecimal();
}
}
}
if (rowTol < 38)
{
Range ra = worksheet.Range[(rowStart + 2).ToString() + ":39"];
ra.Delete(DeleteMode.EntireRow);
}
worksheet.ScrollToRow(0);
workbook.SaveDocument(destinationFilePath);
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
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()
{
string lsRptName = ConditionText().Trim();
StartExport(_ProgramID, lsRptName);
//開啟檔案
Workbook workbook = new Workbook();
try {
if (gbDetial.EditValue.Equals("rb_detail"))
{
DataTable dt;
if (gbMarket.EditValue.Equals("rb_market_1"))
{
dt = dao50020.List50020dAH(emStartDate.DateTimeValue, emEndDate.DateTimeValue);
}
else
{
dt = dao50020.List50020d(emStartDate.DateTimeValue, emEndDate.DateTimeValue);
}
if (dt.Rows.Count <= 0)
{
MessageDisplay.Info(MessageDisplay.MSG_NO_DATA);
return(ResultStatus.Fail);
}
dt.Columns["DATA_DATE"].ColumnName = "交易日期";
dt.Columns["FCM"].ColumnName = "期貨商";
dt.Columns["PROD"].ColumnName = "商品";
dt.Columns["SEND_TIME"].ColumnName = "詢價送出時間";
string csvFilePath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, $"50020_detail_{DateTime.Now.ToString("yyyy.MM.dd-hh.mm.ss")}.csv");
workbook.CreateNewDocument();
workbook.SaveDocument(csvFilePath, DocumentFormat.Csv);
workbook.Options.Export.Csv.WritePreamble = true;//不加這段中文會是亂碼
workbook.Worksheets[0].Import(dt, true, 0, 0);
workbook.SaveDocument(csvFilePath);
return(ResultStatus.Success);
}
Retrieve();
//讀取資料
if (_Data.Rows.Count <= 0)
{
MessageDisplay.Info(MessageDisplay.MSG_NO_DATA);
return(ResultStatus.Fail);
}
//複製檔案
_D500Xx.Filename = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
if (_D500Xx.Filename == "")
{
return(ResultStatus.Fail);
}
_D500Xx.LogText = _D500Xx.Filename;
if (lsRptName == "")
{
lsRptName = "報表條件：" + "(" + DateText() + ")";
}
else
{
lsRptName = ConditionText().Trim() + " " + "(" + DateText() + ")";
}
workbook.LoadDocument(_D500Xx.Filename);
//切換Sheet
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells["E3"].Value = lsRptName;
worksheet.Import(_Data, false, 4, 0);
workbook.SaveDocument(_D500Xx.Filename);
}
catch (Exception ex) {
WriteLog(ex);
WfRunError();
}
finally {
EndExport();
}
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);
}
private bool ManipulateExcel()
{
try {
//檢查查詢日期格式是否正確
if (txtMonth.Text.SubStr(5, 2) == "00")
{
MessageDisplay.Error("月份輸入錯誤!");
return(false);
}
txtMonth.Enabled = false;
this.Cursor = Cursors.WaitCursor;
this.Refresh();
Thread.Sleep(5);
lblProcessing.Visible = true;
ShowMsg("開始轉檔...");
string rptName, rptId, file;
int f;
rptName = "STF報價每月獎勵活動成績得獎名單月報表";
rptId = "50070";
ShowMsg(rptId + "－" + rptName + " 轉檔中...");
//讀取資料
daoRMM = new R_MARKET_MONTHLY();
string asYM = txtMonth.Text.Replace("/", "");
DataTable dt50070 = daoRMM.ListAllByDate(asYM);
if (dt50070.Rows.Count == 0)
{
MessageDisplay.Info(string.Format("{0},{1},無任何資料!", asYM, rptName));
return(false);
}
//複製檔案
file = PbFunc.wf_copy_file(rptId, rptId);
if (file == "")
{
return(false);
}
//切換Sheet
Workbook workbook = new Workbook();
workbook.LoadDocument(file);
Worksheet ws50070 = workbook.Worksheets[0];
//填資料 Sheet1
ws50070.Import(dt50070, false, 2, 0);
//讀取資料
dt50070 = daoRMM.ListAll2ByDate(asYM);
if (dt50070.Rows.Count == 0)
{
MessageDisplay.Info(string.Format("{0},{1},無任何資料!", asYM, rptName));
return(false);
}
//切換Sheet
ws50070 = workbook.Worksheets[1];
//填資料 Sheet2
ws50070.Import(dt50070, false, 0, 0);
//存檔
workbook.SaveDocument(file);
ShowMsg("轉檔成功");
return(true);
}
catch (Exception ex) {
MessageDisplay.Error("輸出錯誤");
throw ex;
}
finally {
this.Cursor = Cursors.Arrow;
this.Refresh();
Thread.Sleep(5);
txtMonth.Enabled = true;
}
}