private ResultStatus ManipulateExcel()
{
string excelDestinationPath = "";
Workbook workbook = new Workbook();
DataTable dtContent = dao60330.ListData(txtStartMonth.FormatValue, txtEndMonth.FormatValue);
var cpDayCnt = dtContent.AsEnumerable().GroupBy(t => new { })
.Select(g => new
{
CP_DAY_CNT = g.Max(s => s.Field <decimal>("AI2_DAY_COUNT"))
}).FirstOrDefault();
if (dtContent.Rows.Count == 0)
{
MessageDisplay.Info(string.Format("{0},{1},無任何資料!", txtStartMonth.Text, this.Text));
ExportShow.Text = "轉檔失敗";
return(ResultStatus.Fail);
}
else
{
#region 明細
workbook.LoadDocument(excelDestinationPath);
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells[2, 0].Value = txtStartMonth.Text + "～" + txtEndMonth.Text + worksheet.Cells[2, 0].Value;
worksheet.Cells[2, 8].Value = cpDayCnt.CP_DAY_CNT.AsString();
int rowIndex = 4;
foreach (DataRow row in dtContent.Rows)
{
worksheet.Cells[rowIndex, 0].Value = row["AI2_PARAM_KEY"].AsString();
worksheet.Cells[rowIndex, 1].Value = row["PARAM_NAME"].AsString();
worksheet.Cells[rowIndex, 2].SetValue(row["AI2_M_QNTY"]);
worksheet.Cells[rowIndex, 3].SetValue(row["AM2_QNTY1"]);
worksheet.Cells[rowIndex, 4].SetValue(row["AM2_QNTY2"]);
worksheet.Cells[rowIndex, 5].SetValue(row["AM2_QNTY3"]);
worksheet.Cells[rowIndex, 6].SetValue(row["AM2_QNTY4"]);
worksheet.Cells[rowIndex, 7].SetValue(row["AM2_QNTY5"]);
worksheet.Cells[rowIndex, 8].SetValue(row["TAX"]);
rowIndex++;
}
if (rowIndex < 104)
{
worksheet.Rows.Remove(rowIndex, 103 - rowIndex + 1);
}
worksheet.Range["A1"].Select();
workbook.SaveDocument(excelDestinationPath);
ExportShow.Text = "轉檔成功!";
return(ResultStatus.Success);
#endregion 明細
}
}
public DataTable ListData(string symd, string eymd)
{
return(dao60330.ListData(symd, eymd));
}