public W40041(string programID, string programName) : base(programID, programName)
{
dao40041 = new D40041();
InitializeComponent();
this.Text = _ProgramID + "─" + _ProgramName;
txtDate.DateTimeValue = DateTime.Now;
oswGrpLookItem.SetDataTable(new OCFG().ListAll(), "OSW_GRP", "OSW_GRP_NAME", TextEditStyles.DisableTextEditor, null);
oswGrpLookItem.ItemIndex = 0;
//設定 下拉選單
//List<LookupItem> prodGrp = new List<LookupItem>(){
// new LookupItem() { ValueMember = "Y", DisplayMember = "1-當日達得調整標準之契約"},
// new LookupItem() { ValueMember = "%", DisplayMember = "%-全部契約" }};
DataTable dtProd = new CODW().ListLookUpEdit("40041", "DATA_TYPE");
prodLookItem.SetDataTable(dtProd, "CODW_ID", "CODW_DESC", TextEditStyles.DisableTextEditor, null);
prodLookItem.ItemIndex = 0; // Y
#if DEBUG
txtDate.DateTimeValue = ("2018/11/01").AsDateTime();
oswGrpLookItem.EditValue = "%";
#endif
radioGroup1.EditValue = "ALL";
ExportShow.Hide();
GridHelper.SetCommonGrid(gvMain);
gcMain.Visible = false;
reCountBtn.Click += reCountBtn_Click;
}
public W35090(string programID, string programName) : base(programID, programName)
{
InitializeComponent();
this.Text = _ProgramID + "─" + _ProgramName;
ExportShow.Hide();
}
public W35030(string programID, string programName) : base(programID, programName)
{
dao35030 = new D35030();
InitializeComponent();
this.Text = _ProgramID + "─" + _ProgramName;
txtDate.DateTimeValue = GlobalInfo.OCF_DATE.ToString("yyyy/MM/01").AsDateTime();
ExportShow.Hide();
}
public WS0020(string programID, string programName) : base(programID, programName)
{
daoS0020 = new DS0020();
InitializeComponent();
this.Text = _ProgramID + "─" + _ProgramName;
txtDate.DateTimeValue = GlobalInfo.OCF_DATE;
ExportShow.Hide();
}
public W60110(string programID, string programName) : base(programID, programName)
{
InitializeComponent();
this.Text = _ProgramID + "─" + _ProgramName;
txtMonth.DateTimeValue = GlobalInfo.OCF_DATE;
dao60110 = new D60110();
ExportShow.Hide();
}
public W30689(string programID, string programName) : base(programID, programName)
{
InitializeComponent();
daoTPRICES_OPT = new TPRICES_OPT();
this.Text = _ProgramID + "─" + _ProgramName;
txtDate.DateTimeValue = GlobalInfo.OCF_DATE;
ExportShow.Hide();
}
public W30722(string programID, string programName) : base(programID, programName)
{
daoRPT = new RPT();
daoAM2 = new AM2();
daoAI2 = new AI2();
daoRamm1 = new RAMM1();
InitializeComponent();
this.Text = _ProgramID + "─" + _ProgramName;
txtDate.DateTimeValue = GlobalInfo.OCF_DATE;
ExportShow.Hide();
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
try {
Workbook workbook = new Workbook();
DataTable dtSP4 = new DataTable();
DataTable dtSP5 = new DataTable();
dtSP4 = daoS0020.GetSP4Data(txtDate.DateTimeValue.ToString("yyyy/MM/dd"));
dtSP5 = daoS0020.GetSP5Data(txtDate.DateTimeValue.ToString("yyyy/MM/dd"));
if (dtSP4.Rows.Count <= 0)
{
ExportShow.Hide();
MessageDisplay.Info("無任何資料");
return(ResultStatus.Fail);
}
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
workbook.LoadDocument(destinationFilePath);
//將SP4資料放入Excel
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells[0, 3].Value = txtDate.DateTimeValue.ToString("yyyy/MM/dd");
for (int i = 0; i < dtSP4.Rows.Count; i++)
{
int row = dtSP4.Rows[i]["SP4_TYPE"].AsInt();
worksheet.Cells[row + 7, 1].Value = dtSP4.Rows[i]["sp4_span_cnt"].AsInt();
worksheet.Cells[row + 7, 2].Value = dtSP4.Rows[i]["sp4_mkt_cnt"].AsInt();
}
//將SP5資料放入Excel(◎尚未申報者:)
if (dtSP5.Rows.Count > 0)
{
//從Excel 第40列開始塞資料
for (int i = 39; i < dtSP5.Rows.Count + 39; i++)
{
worksheet.Cells[i, 0].Value = dtSP5.Rows[i - 39]["sp5_brk_no"].AsString() + '－' +
dtSP5.Rows[i - 39]["sp5_brk_abbr_name"].AsString();
}
}
workbook.SaveDocument(destinationFilePath);
} catch (Exception ex) {
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
public W30500(string programID, string programName) : base(programID, programName)
{
InitializeComponent();
dao30500 = new D30500();
ExportShow.Hide();
this.Text = _ProgramID + "─" + _ProgramName;
gcExport.Hide();
gcMain.Hide();
txtSDate.DateTimeValue = GlobalInfo.OCF_DATE;
txtEDate.DateTimeValue = GlobalInfo.OCF_DATE;
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
Workbook workbook = new Workbook();
DataTable dt = new DataTable();
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);//Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, ls_filename);
DateTime date = txtDate.DateTimeValue;
int rowStart = 4, colStart = 0;
if (txt.EditValue == null)
{
MessageDisplay.Info("請輸入季度資訊 ! ");
ExportShow.Hide();
return(ResultStatus.FailButNext);
}
string yearQ = txt.EditValue.ToString();
try {
workbook.LoadDocument(destinationFilePath);
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells[2, 0].Select();
worksheet.Cells[2, 0].Value = yearQ;
dt = dao35030.GetData(yearQ, date, date);
if (dt.Rows.Count <= 0)
{
ExportShow.Text = date.ToShortDateString() + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!";
return(ResultStatus.Fail);
}
worksheet.Import(dt, false, rowStart, colStart);
//刪除空白列
Range ra = worksheet.Range[(dt.Rows.Count + rowStart + 1).ToString() + ":1004"];
ra.Delete(DeleteMode.EntireRow);
workbook.SaveDocument(destinationFilePath);
}
catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
public W60320(string programID, string programName) : base(programID, programName)
{
InitializeComponent();
this.Text = _ProgramID + "─" + _ProgramName;
int weekNow = Convert.ToInt32(GlobalInfo.OCF_DATE.DayOfWeek);
weekNow = (weekNow == 0 ? 7 - 1 : weekNow - 1) * -1;
//本周第一天(星期一)
txtStartDate.DateTimeValue = GlobalInfo.OCF_DATE.AddDays(weekNow);
txtEndDate.DateTimeValue = GlobalInfo.OCF_DATE;
dao60320 = new D60320();
ExportShow.Hide();
}
public W35020(string programID, string programName) : base(programID, programName)
{
dao35020 = new D35020();
InitializeComponent();
this.Text = _ProgramID + "─" + _ProgramName;
txtDate.DateTimeValue = GlobalInfo.OCF_DATE.ToString("yyyy/MM/01").AsDateTime();
//報表類別 下拉選單
//DataTable exportTypeSource = daoCod.ListByCol2("35020", "ddlb_rpt");
//exportType.SetDataTable(exportTypeSource, "COD_ID", "COD_DESC", DevExpress.XtraEditors.Controls.TextEditStyles.DisableTextEditor, null);
//exportType.EditValue = "0";
exportType.SelectedIndex = 0;
ExportShow.Hide();
}
public W40100(string programID, string programName) : base(programID, programName)
{
InitializeComponent();
this.Text = _ProgramID + "─" + _ProgramName;
txtDate.DateTimeValue = GlobalInfo.OCF_DATE;
//設定 下拉選單
//List<LookupItem> lstType = new List<LookupItem>(){
// new LookupItem() { ValueMember = "0B", DisplayMember = "一般 / 股票"},
// new LookupItem() { ValueMember = "1B", DisplayMember = "長假調整" },
// new LookupItem() { ValueMember = "1E", DisplayMember = "長假回調" },
// new LookupItem() { ValueMember = "2B", DisplayMember = "處置股票調整"}};
DataTable dtType = new CODW().ListLookUpEdit("MGD2", "MGD2_ADJ_TYPE");
//設定下拉選單
ddlAdjType.SetDataTable(dtType, "CODW_ID", "CODW_DESC", TextEditStyles.DisableTextEditor, null);
ddlAdjType.ItemIndex = 0; // 0B
ExportShow.Hide();
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
try {
Workbook workbook = new Workbook();
DataTable dtAM6 = new DataTable();
DataTable dtAM0 = new DataTable();
DateTime date = txtDate.DateTimeValue;
//交易輔助人
dtAM6 = dao30730.GetAM6Data(date.ToString("yyyyMM"));
dtAM0 = dao30730.GetAM0Data(date.ToString("yyyyMM"));
if (dtAM6.Rows.Count <= 0 && dtAM0.Rows.Count <= 0)
{
ExportShow.Hide();
MessageDisplay.Info(date + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!");
return(ResultStatus.Fail);
}
if (dtAM6.Rows.Count <= 0)
{
dtAM6.Rows.Add();
dtAM6.Rows[0]["am6_ym"] = date.ToString("yyyyMM");
dtAM6.Rows[0]["am6_trade_aux"] = 0;
}
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
int oleRow = 1;
workbook.LoadDocument(destinationFilePath);
#region Export
foreach (DataRow r in dtAM0.Rows)
{
int v = r["am0_m_qnty"].AsInt();
string brkNo = r["am0_brk_no"].AsString();
//判斷是否是期貨
if (brkNo == "F")
{
if (r["am0_brk_type"].ToString() == "9")
{
dtAM6.Rows[0]["am6_f999"] = v;
}
else
{
dtAM6.Rows[0]["am6_f000"] = v;
}
}
else
{
if (r["am0_brk_type"].ToString() == "9")
{
dtAM6.Rows[0]["am6_999"] = v;
}
else
{
dtAM6.Rows[0]["am6_000"] = v;
}
}
}
dtAM6.AcceptChanges();
Worksheet worksheet = workbook.Worksheets["30731"];
//寫入資料
dtAM6.Columns.Remove("AM6_YM");
worksheet.Import(dtAM6, false, oleRow, 0);
worksheet = workbook.Worksheets["30730"];
worksheet.Range["A1"].Select();
worksheet.Cells[0, 1].Value = "期貨商及交易輔助人" + date.Year.ToString() + "年" + date.Month.ToString() + "月份期貨交易量統計表";
#endregion
workbook.SaveDocument(destinationFilePath);
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
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);
}
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);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
try {
Workbook workbook = new Workbook();
DataTable dt = new DataTable();
string kindId = "TXF";
DateTime sdate = PbFunc.f_get_last_day("AI3", kindId, txtDate.DateTimeValue.ToString("yyyy/MM"), 1);
DateTime edate = PbFunc.f_get_end_day("AI3", kindId, txtDate.DateTimeValue.ToString("yyyy/MM"));
dt = daoAI3.ListAI3(kindId, sdate, edate);
//查無資料時不產檔
if (dt.Rows.Count <= 0)
{
ExportShow.Hide();
MessageDisplay.Info(sdate + "~" + edate + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!");
return(ResultStatus.Fail);
}
//複製Template
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
workbook.LoadDocument(destinationFilePath);
//切換sheet
Worksheet worksheet = workbook.Worksheets["30711"];
DateTime ldt_ymd = new DateTime(1900, 1, 1);
int row_tol = 33;
//寫入資料
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (ldt_ymd != Convert.ToDateTime(dt.Rows[i]["ai3_date"]))
{
ldt_ymd = Convert.ToDateTime(dt.Rows[i]["ai3_date"]);
worksheet.Cells[i + 1, 0].Value = ldt_ymd;
}
worksheet.Cells[i + 1, 1].Value = float.Parse(dt.Rows[i]["ai3_index"].ToString());
worksheet.Cells[i + 1, 2].Value = float.Parse(dt.Rows[i]["ai3_close_price"].ToString());
}
}
//刪除空白列
if (row_tol > dt.Rows.Count)
{
Range ra = worksheet.Range[(dt.Rows.Count + 2).ToString() + ":" + row_tol.ToString()];
ra.Delete(DeleteMode.EntireRow);
}
//圖表重選範圍
ChartObject chartObjs = workbook.ChartSheets[0].Chart;
chartObjs.Series[0].SeriesName.SetValue("近月份期貨契約指數");
ChartData closePrice = new ChartData();
closePrice.RangeValue = worksheet.Range["C2:C" + (dt.Rows.Count + 1).ToString()];
chartObjs.Series[0].Values = closePrice;
ChartData index = new ChartData();
index.RangeValue = worksheet.Range["B2:B" + (dt.Rows.Count + 1).ToString()];
chartObjs.Series[1].Values = index;
workbook.SaveDocument(destinationFilePath);
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
Workbook workbook = new Workbook();
DataTable dtAM2 = new DataTable();
DataTable dtRPT = new DataTable();
DataTable dtAi2 = new DataTable();
DataTable dtRamm1 = new DataTable();
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
DateTime date = txtDate.DateTimeValue;
string asParamKey = "TXO";
int oleRow = 1;
try {
int noData = 0;
workbook.LoadDocument(destinationFilePath);
#region Get AM2 Data
dtAM2 = daoAM2.ListAm2DataByYmd(date.ToString("yyyyMM"), asParamKey);
if (dtAM2.Rows.Count <= 0)
{
ExportShow.Hide();
MessageDisplay.Info(date + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!");
noData += 1;
File.Delete(destinationFilePath);
return(ResultStatus.Fail);
}
#endregion
Worksheet worksheet = workbook.Worksheets["30720"];
#region Get RPT
dtRPT = daoRPT.ListData("%" + _ProgramID + "%");
if (dtRPT.Rows.Count <= 0)
{
MessageDisplay.Info(_ProgramID + '－' + "RPT無任何資料!");
noData += 1;
}
//填寫日期
TaiwanCalendar tai = new TaiwanCalendar();
worksheet.Cells[0, 6].Value = tai.GetYear(date).ToString() + "年" + tai.GetMonth(date) + "月" + worksheet.Cells[0, 6].Value;
worksheet.Cells[1, 4].Value = date.ToString("MMM", CultureInfo.CreateSpecificCulture("en-US")) + "." + date.Year.ToString() + worksheet.Cells[1, 4].Value;
string paramKey = "";
foreach (DataRow r in dtAM2.Rows)
{
if (paramKey != (r["am2_param_key"].ToString().Trim()))
{
paramKey = r["am2_param_key"].ToString().Trim();
oleRow = dtRPT.Rows.IndexOf(dtRPT.Select("trim(rpt_value) = '" + paramKey + "'")[0]);
if (oleRow >= 0)
{
oleRow = dtRPT.Rows[oleRow]["rpt_seq_no"].AsInt();
}
}
int oleCol = GetCol(r["am2_idfg_type"].AsInt(), r["am2_bs_code"].AsString());
if (oleRow > 0 && oleCol > 0)
{
worksheet.Cells[oleRow - 1, oleCol - 1].Value = r["am2_m_qnty"].AsInt();
}
}
#endregion
#region Get AI2
dtAi2 = daoAI2.ListAI2ByYmd(date.ToString("yyyyMM"), date.ToString("yyyyMM"), asParamKey);
if (dtAi2.Rows.Count <= 0)
{
MessageDisplay.Info(_ProgramID + '－' + "AI2無任何資料!");
noData += 1;
}
//切換sheet
paramKey = "";
foreach (DataRow r in dtAi2.Rows)
{
if (paramKey != (r["ai2_param_key"].ToString().Trim()))
{
paramKey = r["ai2_param_key"].ToString().Trim();
oleRow = dtRPT.Rows.IndexOf(dtRPT.Select("trim(rpt_value) = '" + paramKey + "'")[0]);
if (oleRow >= 0)
{
oleRow = dtRPT.Rows[oleRow]["rpt_seq_no"].AsInt();
}
}
if (oleRow > 0)
{
worksheet.Cells[oleRow - 1, 2].Value = r["ai2_m_qnty"].AsInt();
worksheet.Cells[oleRow - 1, 3].Value = r["ai2_oi"].AsInt();
}
}
#endregion
#region Get Ramm1
dtRamm1 = daoRamm1.ListRamm1Ymd(date.ToString("yyyyMM") + "01", date.ToString("yyyyMM") + "31", asParamKey);
if (dtRamm1.Rows.Count <= 0)
{
MessageDisplay.Info(_ProgramID + '－' + "Ramm1無任何資料!");
noData += 1;
}
paramKey = "";
foreach (DataRow r in dtRamm1.Rows)
{
if (paramKey != (r["param_key"].ToString().Trim()))
{
paramKey = r["param_key"].ToString().Trim();
oleRow = dtRPT.Rows.IndexOf(dtRPT.Select("trim(rpt_value) = '" + paramKey + "'")[0]);
if (oleRow >= 0)
{
oleRow = dtRPT.Rows[oleRow]["rpt_seq_no"].AsInt();
}
}
if (oleRow > 0)
{
worksheet.Cells[oleRow - 1, 16].Value = r["bo"].AsInt();
worksheet.Cells[oleRow - 1, 17].Value = r["bq"].AsInt();
worksheet.Cells[oleRow - 1, 18].Value = r["so"].AsInt();
worksheet.Cells[oleRow - 1, 19].Value = r["sq"].AsInt();
}
}
#endregion
workbook.SaveDocument(destinationFilePath);
//全查無資料時, 刪除檔案
if (noData == 4)
{
File.Delete(destinationFilePath);
}
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
protected override ResultStatus Export()
{
ExportShow.Text = "轉檔中...";
ExportShow.Show();
Workbook workbook = new Workbook();
string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
string sYmd = txtSDate.DateTimeValue.ToString("yyyyMM");
string eYmd = txtEDate.DateTimeValue.ToString("yyyyMM");
int inputYear = txtEDate.DateTimeValue.Year - txtSDate.DateTimeValue.Year;
int oleRow = 2, rowStart = oleRow + 2, rowEnd = rowStart;
int rowTol = 0;
int colTot = dao30750.GetColTot();
try {
workbook.LoadDocument(destinationFilePath);
Worksheet worksheet = workbook.Worksheets[0];
//insert Row (按年分)
if (inputYear > 0)
{
for (int i = 1; i <= inputYear; i++)
{
rowEnd += 12;
//新增12筆空白列
for (int j = 1; j <= 12; j++)
{
worksheet.Rows[rowEnd - 1].Insert();
}
//將上面的12列 copy 過去
Range ra = worksheet.Range[(rowStart).ToString() + ":" + (rowStart + 11).ToString()];
worksheet.Rows[rowEnd - 1].CopyFrom(ra);
}
}
rowTol = rowStart + ((inputYear + 1) * 12);
DataTable dtAI2 = dao30750.GetAI2Data(sYmd, eYmd);
DataTable dtDayCount = dao30750.GetDayCount(sYmd, eYmd);
if (dtAI2.Rows.Count <= 0)
{
ExportShow.Hide();
MessageDisplay.Info(sYmd + "," + _ProgramID + '－' + _ProgramName + ",無任何資料!");
File.Delete(destinationFilePath);
return(ResultStatus.Fail);
}
//補上未選月
//if (txtSDate.DateTimeValue.Month != 1) {
// for (int i = 1; i <= txtSDate.DateTimeValue.Month - 1; i++) {
// DataRow addRow = dtDayCount.NewRow();
// addRow["ai2_ymd"] = txtSDate.DateTimeValue.Year.ToString() + i.ToString("D2");
// addRow["cp_day_count"] = 0;
// dtDayCount.Rows.Add(addRow);
// }
// dtDayCount = dtDayCount.Sort("AI2_YMD");
//}
string lsYear = "";
foreach (DataRow r in dtDayCount.Rows)
{
DateTime aiYm = r["ai2_ymd"].AsDateTime("yyyyMM");
TaiwanCalendar tai = new TaiwanCalendar();
oleRow += 1;
//清除未選月公式
if (r["ai2_ymd"].AsDateTime("yyyyMM") < txtSDate.DateTimeValue)
{
worksheet.Rows[oleRow].ClearContents();
}
if (lsYear != aiYm.Year.ToString())
{
if (aiYm.Month != 1)
{
for (int i = 1; i < aiYm.Month; i++)
{
worksheet.Cells[oleRow, 0].Value = tai.GetYear(aiYm).ToString();
worksheet.Cells[oleRow, 1].Value = i.ToString();
worksheet.Cells[oleRow, 2].Value = "";
oleRow++;
}
}
lsYear = aiYm.Year.ToString();
}
worksheet.Cells[oleRow, 0].Value = tai.GetYear(aiYm).ToString();
worksheet.Cells[oleRow, 1].Value = aiYm.Month.ToString();
worksheet.Cells[oleRow, 2].Value = r["cp_day_count"].ToString() != "0" ? r["cp_day_count"].ToString() : "";
//日均量總計
if (colTot > 0)
{
int cpMQnty = dtAI2.Compute("SUM(ai2_m_qnty)", "ai2_ymd=" + r["ai2_ymd"].ToString()).AsInt();
int cpDayCount = r["cp_day_count"].AsInt();
if (cpDayCount > 0)
{
worksheet.Cells[oleRow, colTot].Value = Math.Round((double)cpMQnty / cpDayCount, 0, MidpointRounding.AwayFromZero);
}
}
if (dtAI2.Select("ai2_ymd=" + r["ai2_ymd"].ToString()).Length != 0)
{
DataTable dtAI2ByYmd = dtAI2.Filter("ai2_ymd=" + r["ai2_ymd"].ToString());
foreach (DataRow row in dtAI2ByYmd.Rows)
{
//月總量
int col = row["rpt_seq_no"].AsInt();
if (col > 0)
{
worksheet.Cells[oleRow, col - 1].Value = row["ai2_m_qnty"].AsInt();
}
//日均量
int col2 = row["rpt_seq_no_2"].AsInt();
if (col2 > 0)
{
int ai2DayCount = row["ai2_day_count"].AsInt();
if (ai2DayCount > 0)
{
worksheet.Cells[oleRow, col2 - 1].Value = Math.Round((double)row["ai2_m_qnty"].AsInt() / ai2DayCount, 0, MidpointRounding.AwayFromZero);
}
}
}
}// if
}
//清除空白列
if (rowTol != oleRow + 2)
{
Range rowRange = worksheet.Range[(oleRow + 2).ToString() + ":" + (rowTol - 1).ToString()];
rowRange.ClearContents();
}
//總計
//insert Row (按年分)
if (inputYear >= 2)
{
rowStart = rowTol;
for (int i = 1; i <= inputYear - 1; i++)
{
worksheet.Rows[rowStart].Insert();
//將上面的總計列 copy 過去
Range ra = worksheet.Rows[rowStart - 1];
worksheet.Rows[rowStart].CopyFrom(ra);
rowStart += 1;
}
}
DataTable dtAI2Sum = dao30750.GetAI2Sum(sYmd, eYmd);
string ymd = "";
oleRow = rowTol - 2;
foreach (DataRow sumR in dtAI2Sum.Rows)
{
if (ymd != sumR["ai2_ymd"].ToString())
{
DateTime aiYm = sumR["ai2_ymd"].AsDateTime("yyyy");
TaiwanCalendar tai = new TaiwanCalendar();
oleRow += 1;
worksheet.Cells[oleRow, 0].Value = tai.GetYear(aiYm).ToString() + "年";
worksheet.Cells[oleRow, 2].Value = dtAI2Sum.Compute("MAX(ai2_day_count)", "ai2_ymd=" + sumR["ai2_ymd"].ToString()).AsInt();
ymd = sumR["ai2_ymd"].ToString();
}
//日均量總計
if (colTot > 0)
{
int cpMQnty = dtAI2Sum.Compute("SUM(ai2_m_qnty)", "ai2_ymd=" + sumR["ai2_ymd"].ToString()).AsInt();
int cpDayCount = dtAI2Sum.Compute("MAX(ai2_day_count)", "ai2_ymd=" + sumR["ai2_ymd"].ToString()).AsInt();
if (cpDayCount > 0)
{
worksheet.Cells[oleRow, colTot].Value = Math.Round((double)cpMQnty / cpDayCount, 0, MidpointRounding.AwayFromZero);
}
}
//月總量
int col = sumR["rpt_seq_no"].AsInt();
if (col > 0)
{
worksheet.Cells[oleRow, col - 1].Value = sumR["ai2_m_qnty"].AsInt();
}
//日均量
int col2 = sumR["rpt_seq_no_2"].AsInt();
if (col2 > 0)
{
int ai2DayCount = sumR["ai2_day_count"].AsInt();
if (ai2DayCount > 0)
{
worksheet.Cells[oleRow, col2 - 1].Value = Math.Round((double)sumR["ai2_m_qnty"].AsInt() / ai2DayCount, 0, MidpointRounding.AwayFromZero);
}
}
}
//刪除空白列
if (rowTol > oleRow)
{
Range ra = worksheet.Range[(oleRow + 2).ToString() + ":" + (rowTol + 2).ToString()];
ra.Delete(DeleteMode.EntireRow);
}
workbook.SaveDocument(destinationFilePath);
} catch (Exception ex) {
ExportShow.Text = "轉檔失敗";
throw ex;
}
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
}
