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);
}
