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);
}
public DataTable ListRPT(string RPT_TXD_ID)
{
return(daoRPT.ListData(RPT_TXD_ID));
}