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