private ResultStatus ManipulateExcel()
{
string excelDestinationPath = "";
Workbook workbook = new Workbook();
DataTable dtContent = dao60320.ListData(txtStartDate.FormatValue, txtEndDate.FormatValue);
if (dtContent.Rows.Count == 0)
{
MessageDisplay.Info(string.Format("{0},{1},無任何資料!", txtStartDate.Text, this.Text));
ExportShow.Text = "轉檔失敗";
return(ResultStatus.Fail);
}
else
{
excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
workbook.LoadDocument(excelDestinationPath);
Worksheet worksheet = workbook.Worksheets[0];
#region 明細
int rowIndex = 7;
foreach (DataRow row in dtContent.Rows)
{
worksheet.Cells[rowIndex, 0].Value = row["YMD"].AsDouble().ToString("0000/00/00");
worksheet.Cells[rowIndex, 1].Value = row["M_QNTY_I"].AsDouble();
worksheet.Cells[rowIndex, 2].Value = row["S_QNTY_I"].AsDouble() + row["M_QNTY_I"].AsDouble();
worksheet.Cells[rowIndex, 3].SetValue(row["T5_QNTY"]);
worksheet.Cells[rowIndex, 4].SetValue(row["T3_QNTY"]);
worksheet.Cells[rowIndex, 5].SetValue(row["M_AMT_T_I"]);
worksheet.Cells[rowIndex, 6].SetValue(row["M_AMT_I"]);
worksheet.Cells[rowIndex, 7].SetValue(row["M_QNTY_S"]);
worksheet.Cells[rowIndex, 8].SetValue(row["S_QNTY_S"].AsDouble() + row["M_QNTY_S"].AsDouble());
worksheet.Cells[rowIndex, 9].SetValue(row["M_AMT_T_S"]);
worksheet.Cells[rowIndex, 10].SetValue(row["M_AMT_S"]);
worksheet.Cells[rowIndex, 11].SetValue(row["S_QNTY_I"].AsDouble() + row["M_QNTY_I"].AsDouble() + row["S_QNTY_S"].AsDouble() + row["M_QNTY_S"].AsDouble());
worksheet.Cells[rowIndex, 12].SetValue(Math.Round(row["ACCU_QNTY"].AsDouble() / row["DAY_COUNT"].AsDouble(), 0));
worksheet.Cells[rowIndex, 13].SetValue(row["M_AMT_I"].AsDouble() + row["M_AMT_S"].AsDouble());
worksheet.Cells[rowIndex, 14].SetValue(row["STWD_QNTY"]);
worksheet.Cells[rowIndex, 15].SetValue(row["STWD_AMT"]);
worksheet.Cells[rowIndex, 16].SetValue(row["AMIF_SUM_AMT"]);
rowIndex++;
}
if (rowIndex < 307)
{
worksheet.Rows.Remove(rowIndex, 306 - rowIndex + 1);
}
worksheet.Range["A1"].Select();
workbook.SaveDocument(excelDestinationPath);
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
#endregion 明細
}
}
public DataTable ListData(string symd, string eymd)
{
return(dao60320.ListData(symd, eymd));
}