Esempio n. 1
0
        protected override ResultStatus Export()
        {
            gvMain.CloseEditor();
            gvMain.UpdateCurrentRow();

            ExportShow.Text = "轉檔中...";
            ExportShow.Show();
            ResultStatus result = ResultStatus.Fail;

            try {
                DataTable exportDt = (DataTable)gcMain.DataSource;
                exportDt = exportDt.Filter("RUN_FLAG ='Y'");

                Workbook workbook = new Workbook();

                //user 所選之要匯出商品
                foreach (DataRow exportDr in exportDt.Rows)
                {
                    int noData = 0;
                    //user 所選要匯出商品的 modelType
                    foreach (CheckedListBoxItem ch in ModelTypies.CheckedItems)
                    {
                        string kindId      = exportDr["MG1_KIND_ID"].AsString();
                        string subType     = exportDr["MG1_PROD_SUBTYPE"].AsString();
                        string prodType    = exportDr["MG1_PROD_TYPE"].AsString();
                        string newFileName = _ProgramID + "(" + kindId + ")_" + ch.Description + "_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss") + ".xlsx";
                        int    sheetIndex  = prodType == "O" ? 2 : 0;

                        DataTable importData     = dao40041.GetExportData(kindId, exportDr["DATA_SDATE"].AsDateTime(), txtDate.DateTimeValue, ch.Value.ToString());
                        DataTable accountingData = dao40041.GetExportData(kindId, exportDr["MG1_SDATE"].AsDateTime(), exportDr["MG1_SDATE"].AsDateTime(), ch.Value.ToString());

                        if (importData == null)
                        {
                            MessageDisplay.Info($"({kindId }_{ch.Description})資料不足2筆,無法產出報表!");
                            result = ResultStatus.FailButNext;
                            noData++;
                            continue;
                        }

                        if (importData.Rows.Count < 2)
                        {
                            MessageDisplay.Info($"({kindId }_{ch.Description})資料不足2筆,無法產出報表!");
                            result = ResultStatus.FailButNext;
                            noData++;
                            continue;
                        }

                        string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID, newFileName);
                        destinationFilePath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, destinationFilePath);

                        workbook.LoadDocument(destinationFilePath);

                        #region Write Data

                        Worksheet worksheet = workbook.Worksheets[sheetIndex];

                        worksheet.Cells[2, 2].Value = prodType == "O" ? kindId : kindId + "結算價";
                        worksheet.Cells[0, 1].Value = accountingData.Rows[0]["MG1_DATE"].AsDateTime();

                        for (int i = 2; i <= accountingData.Columns.Count - 1; i++)
                        {
                            worksheet.Cells[0, i].Value = accountingData.Rows[0][i].AsDecimal();
                        }
                        worksheet.Import(importData, false, 3, 0);

                        //delete empty Rows
                        Range emptyRa = worksheet.Range[(importData.Rows.Count + 4).ToString() + ":1003"];
                        emptyRa.Delete(DeleteMode.EntireRow);

                        #endregion

                        #region Gen Figure 重組圖表資料
                        sheetIndex = prodType == "O" ? 3 : 1;

                        worksheet = workbook.Worksheets[sheetIndex];

                        if (subType != "S")
                        {
                            Range ra = worksheet.Range["4:5"];
                            ra.Delete(DeleteMode.EntireRow);
                        }
                        else
                        {
                            worksheet.Cells[4, 1].Value = exportDr["APDK_NAME"].AsString();
                            worksheet.Cells[4, 2].Value = exportDr["APDK_STOCK_ID"].AsString();
                            worksheet.Cells[4, 3].Value = exportDr["PID_NAME"].AsString();

                            Range ra = worksheet.Range["2:3"];
                            ra.Delete(DeleteMode.EntireRow);
                        }

                        //表頭日期
                        worksheet.Cells[3, 8].Value = importData.Rows[importData.Rows.Count - 1]["MG1_DATE"].AsDateTime();

                        //填寫圖表資料來源
                        int count = 2;
                        for (int f = 7; f >= 6; f--)
                        {
                            DataRow dataRow = importData.Rows[importData.Rows.Count - count];
                            count--;

                            worksheet.Cells[f, 1].Value = dataRow[3].AsDecimal();
                            worksheet.Cells[f, 3].Value = dataRow[2].AsDecimal();
                            worksheet.Cells[f, 5].Value = dataRow[4].AsDecimal();
                            worksheet.Cells[f, 6].Value = dataRow[5].AsDecimal();
                            worksheet.Cells[f, 7].Value = dataRow[6].AsDecimal();
                            worksheet.Cells[f, 8].Value = dataRow[7].AsDecimal();
                            worksheet.Cells[f, 9].Value = dataRow[8].AsDecimal();
                        }
                        #endregion

                        workbook.SaveDocument(destinationFilePath);
                        result = ResultStatus.Success;
                    }
                    //如果不相等, 表示並非所有參數都無資料
                    if (noData != ModelTypies.CheckedItems.Count)
                    {
                        result = ResultStatus.Success;
                    }
                }
            } catch (Exception ex) {
                ExportShow.Text = "轉檔失敗";
                WriteLog(ex);
            }

            ExportShow.Text = "轉檔成功!";
            return(result);
        }
Esempio n. 2
0
        private bool ManipulateExcel()
        {
            try {
                #region wf_55030 造市者各商品交易經手費折減比率月計表
                txtMonth.Enabled = false;
                this.Cursor      = Cursors.WaitCursor;
                this.Refresh();
                Thread.Sleep(5);
                lblProcessing.Visible = true;
                ShowMsg("開始轉檔...");
                string brkNo, accNo, session = "0", rptName, rptId, file;
                int    f, colNum, datacount, rowTol, rowNum;

                rptName = "造市者各商品交易經手費折減比率月計表";
                rptId   = "55030";
                ShowMsg(rptId + "-" + rptName + " 轉檔中...");

                //讀取資料
                DataTable dtContent = dao55030.ListByDate(txtMonth.Text.Replace("/", ""));
                if (dtContent.Rows.Count == 0)
                {
                    MessageDisplay.Info(string.Format("{0},{1},無任何資料!", txtMonth.Text, this.Text));
                    return(false);
                }

                //複製檔案
                file = PbFunc.wf_copy_file(rptId, rptId);
                if (file == "")
                {
                    return(false);
                }

                //切換Sheet
                Workbook workbook = new Workbook();
                workbook.LoadDocument(file);
                Worksheet worksheet = workbook.Worksheets[0];

                //填資料
                rowNum    = 5;
                datacount = int.Parse(worksheet.Cells[0, 0].Value.ToString());

                if (datacount == null || datacount == 0)
                {
                    datacount = dtContent.Rows.Count;
                }
                rowTol = rowNum + datacount;
                worksheet.Cells[3, 0].Value = worksheet.Cells[3, 0].Value + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                worksheet.Cells[3, 7].Value = worksheet.Cells[3, 7].Value + txtMonth.Text.Replace("/", "");

                brkNo = "";
                accNo = "";
                for (f = 0; f < dtContent.Rows.Count; f++)
                {
                    if (brkNo != dtContent.Rows[f]["feetrd_fcm_no"].ToString() || accNo != dtContent.Rows[f]["feetrd_acc_no"].ToString())
                    {
                        rowNum = rowNum + 1;
                        brkNo  = dtContent.Rows[f]["feetrd_fcm_no"].ToString();
                        accNo  = dtContent.Rows[f]["feetrd_acc_no"].ToString();
                        worksheet.Cells[rowNum, 0].Value = brkNo;
                        worksheet.Cells[rowNum, 1].Value = dtContent.Rows[f]["brk_abbr_name"].ToString();
                        worksheet.Cells[rowNum, 2].Value = accNo;
                    }
                    colNum = int.Parse(dtContent.Rows[f]["rpt_seq_no"].ToString());
                    if (rowNum > 0 && colNum > 0)
                    {
                        worksheet.Cells[rowNum, colNum].Value = decimal.Parse(dtContent.Rows[f]["feetrd_rate"].ToString());
                    }
                }

                /*******************
                *  刪除空白列
                *******************/
                if (rowTol > rowNum)
                {
                    worksheet.Rows.Remove(rowNum + 1, rowTol - rowNum);
                }

                #endregion

                #region wf_55031
                string kindId;
                session = "0";

                /******************
                *  讀取資料
                ******************/
                DataTable dtContent2 = dao55031.ListByDate(txtMonth.Text.Replace("/", ""));
                if (dtContent.Rows.Count == 0)
                {
                    MessageDisplay.Info(string.Format("{0},{1},無任何資料!", txtMonth.Text, this.Text));
                    return(false);
                }
                //契約檔
                DataTable dtAPDK = daoAPDK.ListAll_55031();

                /******************
                *  切換Sheet
                ******************/
                Worksheet worksheet2 = workbook.Worksheets[1];

                rowNum    = 5;
                datacount = int.Parse(worksheet2.Cells[0, 0].Value.ToString());
                if (datacount == null || datacount == 0)
                {
                    datacount = dtContent2.Rows.Count;
                }
                rowTol = rowNum + datacount;
                worksheet2.Cells[3, 0].Value = worksheet2.Cells[3, 0].Value + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                worksheet2.Cells[3, 7].Value = worksheet2.Cells[3, 7].Value + txtMonth.Text.Replace("/", "");
                //契約檔
                for (f = 0; f < dtAPDK.Rows.Count; f++)
                {
                    kindId = dtAPDK.Rows[f]["apdk_kind_id"].ToString();
                    if (kindId.Trim() == "STO")
                    {
                        kindId = "平均";
                    }
                    worksheet2.Cells[5, f + 3].Value = kindId;
                }

                brkNo = "";
                f     = 0;
                for (f = 0; f < dtContent2.Rows.Count; f++)
                {
                    if (brkNo != dtContent2.Rows[f]["feetrd_fcm_no"].ToString() || accNo != dtContent2.Rows[f]["feetrd_acc_no"].ToString())
                    {
                        rowNum = rowNum + 1;
                        brkNo  = dtContent2.Rows[f]["feetrd_fcm_no"].ToString();
                        accNo  = dtContent2.Rows[f]["feetrd_acc_no"].ToString();
                        worksheet2.Cells[rowNum, 0].Value = brkNo;
                        worksheet2.Cells[rowNum, 1].Value = dtContent2.Rows[f]["brk_abbr_name"].ToString();
                        worksheet2.Cells[rowNum, 2].Value = accNo;
                    }
                    //long datastore.Find ( stringexpression, longstart, longend )找該值位於資料表的第幾筆
                    if (dtAPDK.Select("apdk_kind_id='" + dtContent2.Rows[f]["feetrd_kind_id"].ToString().Trim() + "'").Length == 0)
                    {
                        colNum = 0;
                    }
                    else
                    {
                        colNum = dtAPDK.Rows.IndexOf(dtAPDK.Select("apdk_kind_id='" + dtContent2.Rows[f]["feetrd_kind_id"].ToString().Trim() + "'")[0]) + 1;
                    }
                    if (rowNum > 0 && colNum > 0)
                    {
                        worksheet2.Cells[rowNum, colNum + 2].Value = decimal.Parse(dtContent2.Rows[f]["feetrd_rate"].ToString());
                    }
                }

                /*******************
                *  刪除空白列
                *******************/
                if (rowTol > rowNum)
                {
                    worksheet2.Rows.Remove(rowNum + 1, rowTol - rowNum);
                }

                //存檔
                workbook.SaveDocument(file);
                #endregion
                ShowMsg("轉檔成功");
                return(true);
            }
            catch (Exception ex) {
                MessageDisplay.Error("輸出錯誤");
                throw ex;
            }
            finally {
                this.Cursor = Cursors.Arrow;
                this.Refresh();
                Thread.Sleep(5);
                txtMonth.Enabled = true;
            }
        }
Esempio n. 3
0
        protected override ResultStatus Export()
        {
            try {
                //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 xls
                Workbook workbook = new Workbook();
                workbook.LoadDocument(excelDestinationPath);

                //4. write data
                //bool res1 = false, res2 = false, res3 = false;
                int row = 1;
                flag  = 0;
                flag += wf_30401(workbook, SheetNo.sheet1, row);
                flag += wf_30402(workbook, SheetNo.sheet2, row);
                flag += wf_30408(workbook, SheetNo.sheet8, row);
                row   = 0;
                flag += wf_30403(workbook, SheetNo.sheet3, row);
                if (txtKindId.Text != "%")
                {
                    row   = 1;
                    flag += wf_30404(workbook, SheetNo.sheet4, row, txtKindId.Text);
                }
                row   = 2;
                flag += wf_30405(workbook, SheetNo.sheet5, row);
                row   = 3;
                flag += wf_30406(workbook, SheetNo.sheet6, row);
                flag += wf_30407(workbook, SheetNo.sheet7, row);

                if (flag == 0)
                {
                    File.Delete(excelDestinationPath);
                    return(ResultStatus.Fail);
                }

                //5. 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);
        }
Esempio n. 4
0
        /// <summary>
        /// 7/2上線前的版本 可供新需求的邏輯參考
        /// </summary>
        /// <returns></returns>
        public string Wf40040()
        {
            Workbook workbook = new Workbook();

            try {
                workbook.LoadDocument(_lsFile);
                Worksheet worksheet = workbook.Worksheets[(int)SheetName.SheetOne];
                DateTime  emdate    = _emDateText.AsDateTime("yyyy/MM/dd");
                worksheet.Cells["O1"].Value = "資料日期:" + emdate.ToLongDateString();

                //前一交易日
                DateTime dateLast = dao40040.GetDateLast(emdate, (int)SheetName.SheetOne);

                //讀取資料
                DataTable dt = dao40040.ListData(emdate, $"{_oswGrpVal}%");
                if (dt.Rows.Count <= 0)
                {
                    return(MessageDisplay.MSG_NO_DATA);
                }

                //讀取資料(現貨資料)
                DataTable dtMg6 = dao40040.ListMg6Data(emdate, dateLast, "%");

                //讀取Mg8資料
                DataTable dtMg8 = dao40040.ListMg8Data(emdate, "%");

                //保證金變動幅度達10%,分別為第n天
                DataTable dtDay = dao40040.ListDayData(emdate);

                foreach (DataRow row in dt.Rows)
                {
                    int rowIndex = row["RPT_SEQ_NO"].AsInt();
                    if (rowIndex <= 0)
                    {
                        continue;
                    }

                    #region 第B欄
                    worksheet.Cells[$"B{rowIndex}"].Value = row["MGT2_KIND_ID_OUT"].AsString();
                    #endregion

                    if (row["MG1_CHANGE_FLAG"].AsString() != "Y")
                    {
                        continue;
                    }

                    //1.保證金變動幅度之趨勢/
                    //15%≧X≧10%
                    decimal mg1ChangeRange = row["MG1_CHANGE_RANGE"].AsDecimal();
                    decimal Percent10      = row["MG1_PROD_SUBTYPE"].AsString() == "E" ? 0.05m : 0.1m;
                    decimal Percent15      = row["MG1_PROD_SUBTYPE"].AsString() == "E" ? 0.1m : 0.15m;
                    #region 第C欄
                    if (Math.Abs(mg1ChangeRange) >= Percent10 && Math.Abs(mg1ChangeRange) < Percent15)
                    {
                        worksheet.Cells[$"C{rowIndex}"].SetValue(mg1ChangeRange);
                    }
                    #endregion
                    //X≧15%
                    #region 第D欄
                    if (Math.Abs(mg1ChangeRange) >= Percent15)
                    {
                        worksheet.Cells[$"D{rowIndex}"].SetValue(mg1ChangeRange);
                    }
                    #endregion

                    //前一交易日保證金變動幅度
                    var ldValue = row["MG1_CHANGE_RANGE_LAST"];
                    #region 第E欄
                    worksheet.Cells[$"E{rowIndex}"].SetValue(ldValue == DBNull.Value ? "▲" : ldValue);
                    #endregion
                    #region 第F欄
                    worksheet.Cells[$"F{rowIndex}"].SetValue(ldValue == DBNull.Value ? "▲" : OX(row));
                    #endregion

                    //達得調整標準天數
                    string kindID = row["MG1_KIND_ID"].AsString();
                    #region 第G欄
                    int dtDayIndex = dtDay.Rows.IndexOf(dtDay.Select($@"mg1_kind_id ='{kindID}'").FirstOrDefault());
                    worksheet.Cells[$"G{rowIndex}"].SetValue(dtDayIndex > -1 ? (long)dtDay.Rows[dtDayIndex]["DAY_CNT"].AsDecimal() : 1);
                    #endregion

                    //2.未沖銷部位數/
                    var ai2OI = row["AI2_OI"];
                    if (ai2OI != DBNull.Value)
                    {
                        #region 第H欄
                        worksheet.Cells[$"H{rowIndex}"].SetValue(ai2OI);
                        #endregion
                        #region 第I欄
                        var oiRate = row["OI_RATE"];
                        worksheet.Cells[$"I{rowIndex}"].SetValue(oiRate.AsDecimal() < 0.0001m && ai2OI.AsDecimal() > 0 ? "小於0.01%" : oiRate);
                        #endregion
                        #region 第J欄
                        decimal TotOIiRound = Math.Round(dt.Rows[0]["TOT_OI"].AsDecimal() * 0.005m, 0, MidpointRounding.AwayFromZero);
                        worksheet.Cells[$"J{rowIndex}"].SetValue(ai2OI.AsDecimal() >= TotOIiRound ? "O" : "X");
                        #endregion
                        //屆到期日前7個交易日
                        #region 第K欄
                        worksheet.Cells[$"K{rowIndex}"].SetValue(
                            row["APROD_7DATE"].AsDateTime() <= emdate && row["APROD_DELIVERY_DATE"].AsDateTime() > emdate ? "O" : "X"
                            );
                        #endregion
                    }

                    //3.現貨、期貨漲跌/
                    int dtMg6Index = dtMg6.Rows.IndexOf(dtMg6.Select($@"F_KIND_ID ='{kindID}' or O_KIND_ID='{kindID}'").FirstOrDefault());
                    if (dtMg6Index > -1)
                    {
                        string colTxt      = dtMg6.Rows[dtMg6Index]["O_KIND_ID"] == DBNull.Value ? "PDK" : "O";
                        string prodSubtype = dtMg6.Rows[dtMg6Index]["APDK_PROD_SUBTYPE"].AsString();
                        //現貨
                        #region 第L欄
                        var     updown     = dtMg6.Rows[dtMg6Index][colTxt + "_UP_DOWN"];
                        decimal rateMUL100 = dtMg6.Rows[dtMg6Index][colTxt + "_RETURN_RATE"].AsDecimal() * 100;
                        string  flag       = FlagStr(updown.AsDecimal());
                        worksheet.Cells[$"L{rowIndex}"].SetValue(updown == DBNull.Value ? "-" : WriteUpDownPercent(updown.AsDecimal(), rateMUL100, prodSubtype, flag));
                        #endregion
                        //現貨漲跌與保證金調整方向相同
                        #region 第N欄
                        switch (kindID)
                        {
                        case "GDF":
                        case "TGF":
                        case "TGO":
                        case "GBF":
                        case "CPF":
                            flag = "-";
                            break;

                        default:
                            break;
                        }

                        if (flag == "-")
                        {
                            //ls_flag = '-'  then 後面沒有要做什麼 只是做個條件區分
                        }
                        else if (row["MG1_CM_LAST"] == DBNull.Value)
                        {
                            flag = "";
                        }
                        else
                        {
                            flag = WriteFlag(flag, row);
                        }

                        worksheet.Cells[$"N{rowIndex}"].SetValue(flag);
                        #endregion

                        //期貨
                        #region 第M欄
                        var     fupdown     = dtMg6.Rows[dtMg6Index]["F_UP_DOWN"];
                        string  flag2       = FlagStr(fupdown.AsDecimal());
                        decimal frateMUL100 = dtMg6.Rows[dtMg6Index]["F_RETURN_RATE"].AsDecimal() * 100;
                        worksheet.Cells[$"M{rowIndex}"].SetValue(WriteUpDownPercent(fupdown.AsDecimal(), frateMUL100, prodSubtype, flag2));
                        #endregion
                        //期貨漲跌與保證金調整方向相同
                        #region 第O欄
                        var cmlast = row["MG1_CM_LAST"];
                        worksheet.Cells[$"O{rowIndex}"].SetValue(cmlast == DBNull.Value ? "" : WriteFlag(flag2, row));
                        #endregion
                    }// if (dtMg6Index > -1)

                    //4.與國外水準相較/
                    int dtMg8Index = dtMg8.Rows.IndexOf(dtMg8.Select($"mg1_kind_id ='{kindID}' and com ='TAIFEX'").FirstOrDefault());
                    if (dtMg8Index > -1)
                    {
                        #region 第P欄
                        worksheet.Cells[$"P{rowIndex}"].SetValue(Foreign(kindID, "BEF", dtMg8, dtMg8Index));
                        #endregion
                        #region 第Q欄
                        worksheet.Cells[$"Q{rowIndex}"].SetValue(Foreign(kindID, "AFT", dtMg8, dtMg8Index));
                        #endregion
                    }
                }//foreach (DataRow row in dt.Rows)

                //重大事件
                StringBuilder sb        = new StringBuilder("");
                DataTable     dtMgt3    = dao40040.ListMgt3Data(emdate);
                int           mgt3Count = dtMgt3.Rows.Count;
                for (int k = 0; k < mgt3Count; k++)
                {
                    DataRow dr   = dtMgt3.Rows[k];
                    string  memo = PbFunc.f_conv_date(dr["MGT3_DATE_TO"].AsDateTime(), 3) + dr["MGT3_MEMO"].AsString();
                    sb.Append(memo);
                    sb.Append(FlagMerge(mgt3Count, k));
                }

                if (sb.ToString() != "")
                {
                    int mg1flagYcount = dt.Select("mg1_change_flag = 'Y'").Length;
                    if (mg1flagYcount > 0)
                    {
                        worksheet.Cells[$"R16"].SetValue(sb.ToString());
                    }
                }
                //save
                worksheet.ScrollTo(0, 0);
            }
            catch (Exception ex) {
#if DEBUG
                throw new Exception($"Wf40040:" + ex.Message);
#else
                throw ex;
#endif
            }
            finally {
                workbook.SaveDocument(_lsFile);
            }
            return(MessageDisplay.MSG_OK);
        }
Esempio n. 5
0
        protected override ResultStatus Export()
        {
            string rptName = ConditionText().Trim();

            StartExport(_ProgramID, "造市者報表");

            if (rptName == "")
            {
                rptName = "報表條件:" + "(" + DateText() + ")";
            }
            else
            {
                rptName = ConditionText().Trim() + " " + "(" + DateText() + ")";
            }

            try {
                if (_Data == null || _Data.Rows.Count <= 0)
                {
                    MessageDisplay.Info(MessageDisplay.MSG_NO_DATA);
                    return(ResultStatus.Fail);
                }

                //複製檔案
                string lsFile = PbFunc.wf_copy_file(_ProgramID, _ProgramID);

                if (lsFile == "")
                {
                    return(ResultStatus.Fail);
                }
                _D500Xx.LogText = lsFile;

                //開啟檔案
                Workbook workbook = new Workbook();
                workbook.LoadDocument(lsFile);

                /******************
                *  切換Sheet
                ******************/
                Worksheet worksheet = workbook.Worksheets[0];
                worksheet.Cells["E3"].Value = rptName;

                int rowIndex = 5;
                foreach (DataRow row in _Data.Rows)
                {
                    worksheet.Cells[$"A{rowIndex}"].SetValue(row["CP_ROW"]);
                    worksheet.Cells[$"B{rowIndex}"].SetValue(row["AMM0_BRK_NO"]);
                    worksheet.Cells[$"C{rowIndex}"].SetValue(row["BRK_ABBR_NAME"]);
                    worksheet.Cells[$"D{rowIndex}"].SetValue(row["AMM0_ACC_NO"]);
                    worksheet.Cells[$"E{rowIndex}"].SetValue(row["AMM0_PROD_ID"]);
                    worksheet.Cells[$"F{rowIndex}"].SetValue(row["AMM0_YMD"].AsString());
                    worksheet.Cells[$"G{rowIndex}"].SetValue(row["AMM0_OM_QNTY"]);
                    worksheet.Cells[$"H{rowIndex}"].SetValue(row["AMM0_QM_QNTY"]);
                    worksheet.Cells[$"I{rowIndex}"].SetValue(row["QNTY"]);
                    worksheet.Cells[$"J{rowIndex}"].SetValue(row["CP_M_QNTY"]);
                    worksheet.Cells[$"K{rowIndex}"].SetValue(row["CP_RATE_M"]);
                    worksheet.Cells[$"L{rowIndex}"].SetValue(row["AMM0_VALID_CNT"]);
                    worksheet.Cells[$"M{rowIndex}"].SetValue(row["VALID_RATE"]);
                    worksheet.Cells[$"N{rowIndex}"].SetValue(row["AMM0_MARKET_R_CNT"]);
                    worksheet.Cells[$"O{rowIndex}"].SetValue(row["AMM0_MARKET_M_QNTY"]);
                    worksheet.Cells[$"P{rowIndex}"].SetValue(row["AMM0_KEEP_FLAG"]);
                    rowIndex = rowIndex + 1;
                }//foreach (DataRow row in _Data.Rows

                workbook.SaveDocument(lsFile);
            }
            catch (Exception ex) {
                WriteLog(ex);
                WfRunError();
            }
            finally {
                EndExport();
            }

            return(ResultStatus.Success);
        }
Esempio n. 6
0
        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);
        }
Esempio n. 7
0
        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);
        }
Esempio n. 8
0
        private bool wf_30611(Workbook workbook)
        {
            try {
                string rptName = "每月現、期貨市場振幅、波動度、成交量彙集";
                string rptId   = "30611";
                ShowMsg(string.Format("{0}-{1} 轉檔中...", rptId, rptName));

                Worksheet worksheet = workbook.Worksheets[(int)SheetNo.mon]; //切換sheet

                int ii_ole_row     = 4;
                int li_ole_row_tol = 184;

                //每月
                DateTime  endDay    = PbFunc.f_get_end_day("AI2", "TXF", txtEndMonth.Text);
                DataTable dtContent = dao30610.GetMonData(txtStartMonth.DateTimeValue, endDay);; //月明細表
                if (dtContent.Rows.Count <= 0)
                {
                    MessageDisplay.Info(string.Format("{0}~{1},{2}-{3},無任何資料!", txtStartMonth.Text, txtEndMonth.Text, rptId, rptName), GlobalInfo.ResultText);
                    return(false);
                }

                int li_ole_end_row = 0;
                for (int w = 1; w <= dtContent.Rows.Count; w++)
                {
                    if (w == dtContent.Rows.Count)
                    {
                        li_ole_end_row = ii_ole_row;
                        ii_ole_row     = li_ole_row_tol;
                    }
                    else
                    {
                        ii_ole_row++;
                    }

                    worksheet.Cells[ii_ole_row - 1, 0].Value = dtContent.Rows[w - 1]["AMIF_YM"].AsString().SubStr(0, 4).AsInt() - 1911 +
                                                               dtContent.Rows[w - 1]["AMIF_YM"].AsString().SubStr(4, 2);
                    worksheet.Cells[ii_ole_row - 1, 1].Value  = dtContent.Rows[w - 1]["AMIF_TOT_CNT"].AsInt();
                    worksheet.Cells[ii_ole_row - 1, 2].Value  = dtContent.Rows[w - 1]["TFXM_AVG_UP_DOWN"].AsDecimal();
                    worksheet.Cells[ii_ole_row - 1, 4].Value  = dtContent.Rows[w - 1]["TFXM_CNT"].AsInt();
                    worksheet.Cells[ii_ole_row - 1, 5].Value  = dtContent.Rows[w - 1]["RETURN_P2"].AsDecimal() * 100;
                    worksheet.Cells[ii_ole_row - 1, 6].Value  = dtContent.Rows[w - 1]["TFXM_AVG_CLOSE_PRICE"].AsDecimal();
                    worksheet.Cells[ii_ole_row - 1, 7].Value  = dtContent.Rows[w - 1]["TFXM_M_QNTY_TAL"].AsDecimal();
                    worksheet.Cells[ii_ole_row - 1, 8].Value  = dtContent.Rows[w - 1]["AMIF_AVG_UP_DOWN"].AsDecimal();
                    worksheet.Cells[ii_ole_row - 1, 10].Value = dtContent.Rows[w - 1]["AMIF_CNT"].AsInt();
                    worksheet.Cells[ii_ole_row - 1, 11].Value = dtContent.Rows[w - 1]["RETURN_P1"].AsDecimal() * 100;
                    worksheet.Cells[ii_ole_row - 1, 12].Value = dtContent.Rows[w - 1]["AMIF_AVG_CLOSE_PRICE"].AsDecimal();
                    worksheet.Cells[ii_ole_row - 1, 13].Value = dtContent.Rows[w - 1]["AI2_AVG_QTY_TXF"].AsDecimal();
                    worksheet.Cells[ii_ole_row - 1, 14].Value = dtContent.Rows[w - 1]["AI2_AVG_QTY_TXO"].AsDecimal();
                    worksheet.Cells[ii_ole_row - 1, 15].Value = dtContent.Rows[w - 1]["AI2_AVG_TOT_QTY"].AsDecimal();
                }

                //刪除空白列
                //if (li_ole_end_row < li_ole_row_tol) {
                //   worksheet.Rows.Remove(ii_ole_row , li_ole_row_tol - ii_ole_row);
                //}
                if (li_ole_row_tol > dtContent.Rows.Count + 4)
                {
                    Range ra = worksheet.Range[(dtContent.Rows.Count + 4).AsString() + ":183"];
                    ra.Delete(DeleteMode.EntireRow);
                }

                worksheet.Range["A1"].Select();
                worksheet.ScrollToRow(0);
                return(true);
            } catch (Exception ex) {
                WriteLog(ex);
            }
            return(false);
        }
Esempio n. 9
0
        protected override ResultStatus Export()
        {
            try {
                #region 輸入&日期檢核
                if (gbStatistics.EditValue.AsString() == "rbMon")
                {
                    if (string.Compare(txtStartMonth.Text, txtEndMonth.Text) > 0)
                    {
                        MessageDisplay.Error("月份起始年月不可小於迄止年月!", GlobalInfo.ErrorText);
                        return(ResultStatus.Fail);
                    }
                }
                else
                {
                    if (string.Compare(txtStartDate.Text, txtEndDate.Text) > 0)
                    {
                        MessageDisplay.Error(CheckDate.Datedif, GlobalInfo.ErrorText);
                        return(ResultStatus.Fail);
                    }
                }
                #endregion

                //0. ready
                panFilter.Enabled = false;
                labMsg.Visible    = true;
                labMsg.Text       = "開始轉檔...";
                this.Cursor       = Cursors.WaitCursor;
                this.Refresh();
                Thread.Sleep(5);

                //1.複製檔案 & 開啟檔案
                string   excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
                Workbook workbook             = new Workbook();
                workbook.LoadDocument(excelDestinationPath);

                //2.填資料
                bool res = false;
                if (gbStatistics.EditValue.AsString() == "rbMon")
                {
                    res = wf_30611(workbook);
                }
                else
                {
                    res = wf_30612(workbook);
                }

                if (!res)
                {
                    workbook = null;
                    File.Delete(excelDestinationPath);
                    return(ResultStatus.Fail);
                }

                //存檔
                workbook.SaveDocument(excelDestinationPath);
                labMsg.Visible = false;
                return(ResultStatus.Success);
            } catch (Exception ex) {
                WriteLog(ex);
            } finally {
                panFilter.Enabled = true;
                labMsg.Text       = "";
                labMsg.Visible    = false;
                this.Cursor       = Cursors.Arrow;
            }
            return(ResultStatus.Fail);
        }
Esempio n. 10
0
        protected override ResultStatus Export()
        {
            try
            {
                #region 輸入&日期檢核

                if (string.Compare(txtStartDate.Text, txtEndDate.Text) > 0)
                {
                    MessageDisplay.Error(CheckDate.Datedif, GlobalInfo.ErrorText);
                    return(ResultStatus.Fail);
                }
                #endregion

                //1. ready
                panFilter.Enabled = false;
                labMsg.Visible    = true;
                labMsg.Text       = "開始轉檔...";
                this.Cursor       = Cursors.WaitCursor;
                this.Refresh();
                Thread.Sleep(5);


                string market = gbMarket.EditValue.AsString();
                string pcCode = gbPC.EditValue.AsString();
                string kindId = dwKindId.Text.AsString();
                string ws     = "";

                DataTable dt = new DataTable();
                //4. 填資料
                if (market == "rbTFXM")//現貨
                {
                    dt = dao30418.ListTfxmData(StartDate, EndDate);
                    ws = "現貨市場三大法人";
                }
                else if (market == "rbFut")//期貨市場
                {
                    ws = "期貨市場三大法人";
                    dt = dao30418.ListFutAndOptData(txtStartDate.DateTimeValue, txtEndDate.DateTimeValue, pcCode, kindId);
                }
                if (dt.Rows.Count == 0)
                {
                    MessageDisplay.Info(MessageDisplay.MSG_NO_DATA);
                    return(ResultStatus.Fail);
                }

                //2. copy template xls to target path
                string excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);

                //3. open xls
                Workbook workbook = new Workbook();
                workbook.LoadDocument(excelDestinationPath);

                Worksheet worksheet = workbook.Worksheets[ws];
                worksheet.Import(dt, false, 1, 0);
                workbook.Worksheets.ActiveWorksheet = worksheet;


                //5. 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);
        }
Esempio n. 11
0
        protected override ResultStatus Export()
        {
            try {
                #region 輸入&日期檢核
                string       lsRtn = PbFunc.f_get_jsw(_ProgramID, "E", txtEndDate.Text);
                DialogResult liRtn;

                if (lsRtn != "Y")
                {
                    liRtn = MessageDisplay.Choose(string.Format("{0} 統計資料未轉入完畢,是否要繼續?", txtEndDate.Text), MessageBoxDefaultButton.Button2, GlobalInfo.QuestionText);
                    if (liRtn == DialogResult.No)
                    {
                        labMsg.Visible = false;
                        Cursor.Current = Cursors.Arrow;
                        return(ResultStatus.Fail);
                    } //if (liRtn == DialogResult.Yes)
                }     //if (lsRtn != "Y")

                //if (!txtStartDate.IsDate(txtStartDate.Text , CheckDate.Start)
                //      || !txtEndDate.IsDate(txtEndDate.Text , CheckDate.End)) {
                //   return ResultStatus.Fail; ;
                //}

                if (string.Compare(txtStartDate.Text, txtEndDate.Text) > 0)
                {
                    MessageDisplay.Error(CheckDate.Datedif, GlobalInfo.ErrorText);
                    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 xls
                Workbook workbook = new Workbook();
                workbook.LoadDocument(excelDestinationPath);

                //4. write data
                int  row;
                bool res1 = false, res2 = false, res3 = false;
                row  = 3;
                res1 = wf_30410(workbook, SheetNo.tradeSum, row);

                row  = 4; //PB這邊帶1,但進去後帶回4
                res2 = wf_30411(workbook, SheetNo.tradeDetail, row);

                row  = 4; //PB這邊帶1,但進去後帶回4
                res3 = wf_30412(workbook, SheetNo.oint, row);

                if (!res1 && !res2 && !res3)
                {
                    File.Delete(excelDestinationPath);
                    return(ResultStatus.Fail);
                }

                //5. 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);
        }
Esempio n. 12
0
        protected override ResultStatus Export()
        {
            try {
                lblProcessing.Visible = true;
                dao30090 = new D30090();
                string rptId, file, rptName, kindId, kindIdName;
                int    rowNum, colNum;
                rptId              = "30090";
                rptName            = "Position-Transfer to TAIFEX by Investor Group";
                lblProcessing.Text = rptId + "-" + rptName + " 轉檔中...";

                //讀取資料
                DataTable dt30090 = dao30090.d_30090(txtSDate.Text.Replace("/", ""), txtEDate.Text.Replace("/", ""));
                if (dt30090.Rows.Count == 0)
                {
                    MessageDisplay.Info(GlobalInfo.OCF_DATE.ToString("yyyyMM") + "," + rptId + '-' + rptName + ",無任何資料!");
                    lblProcessing.Visible = false;
                    return(ResultStatus.Fail);
                }

                //複製檔案
                file = PbFunc.wf_copy_file(rptId, rptId);
                if (file == "")
                {
                    return(ResultStatus.Fail);
                }
                logTxt = file;

                //開啟檔案
                Workbook workbook = new Workbook();
                workbook.LoadDocument(file);

                #region wf_30090

                //切換sheet
                Worksheet ws30090 = workbook.Worksheets[0];
                ws30090.Cells[1, 1].Value = "Date:" + txtSDate.Text + "~" + txtEDate.Text; //填寫搜尋日期

                //填入資料
                kindId = "";
                rowNum = 2;
                foreach (DataRow dr in dt30090.Rows)
                {
                    if (kindId != dr["AE1_PARAM_KEY"].AsString())
                    {
                        kindId = dr["AE1_PARAM_KEY"].AsString();
                        rowNum = rowNum + 1;
                        switch (kindId)
                        {
                        case "TXF":
                            kindIdName = "FTX";
                            break;

                        case "TXO":
                            kindIdName = "OTX";
                            break;

                        case "ZZZ":
                            kindIdName = "合計";
                            break;

                        default:
                            kindIdName = kindId;
                            break;
                        }
                        ws30090.Cells[rowNum, 1].Value = kindIdName;
                    }
                    colNum = 0;
                    switch (dr["AE1_IDFG_TYPE"].AsString())
                    {
                    case "1":    //證券自營
                        colNum = 3;
                        break;

                    case "2":    //證券投信
                        colNum = 4;
                        break;

                    case "3":    //外資
                        colNum = 5;
                        break;

                    case "4":    //期貨經理事業
                        colNum = 6;
                        break;

                    case "5":    //一般法人
                        colNum = 7;
                        break;

                    case "6":    //期貨自營商
                        colNum = 9;
                        break;

                    case "7":    //自然人
                        colNum = 2;
                        break;

                    case "8":    //一般法人
                        colNum = 8;
                        break;

                    default:
                        continue;
                    }
                    if (rowNum > 0 && colNum > 0)
                    {
                        if (dr["AE1_ACCEPTED_OI"] != DBNull.Value)
                        {
                            ws30090.Cells[rowNum, colNum].Value = dr["AE1_ACCEPTED_OI"].AsDecimal();
                        }
                    }
                }
                #endregion

                //存檔
                ws30090.ScrollToRow(0);
                workbook.SaveDocument(file);
                lblProcessing.Text = "轉檔成功";
            }
            catch (Exception ex) {
                MessageDisplay.Error("輸出錯誤");
                throw ex;
            }
            return(ResultStatus.Success);
        }
Esempio n. 13
0
        protected override ResultStatus Retrieve()
        {
            DataTable dt    = dao40080.GetData(txtTradeDate.DateTimeValue);
            DataTable dtSp2 = dao40080.GetSP2Data(txtTradeDate.DateTimeValue);

            #region 設定生效日期
            int ll_found = 0;;
            //Group1
            if (dt.Select("sp1_osw_grp='1' and sp2_value_date is not null").Length != 0)                    //有找到
            {
                ll_found = dt.Rows.IndexOf(dt.Select("sp1_osw_grp='1' and sp2_value_date is not null")[0]); //ll_found = 找到的列數(從0開始)
            }
            else
            {
                ll_found = -1;
            }

            if (ll_found >= 0) //有找到
            {
                string sp2ValueDate = dt.Rows[ll_found]["sp2_value_date"].AsDateTime().ToString("yyyy/MM/dd");
                txtDate1.Text = sp2ValueDate;
            }
            else  //沒找到
            {
                string tmpDate = PbFunc.f_get_ocf_next_n_day(txtTradeDate.DateTimeValue, 1).ToString("yyyy/MM/dd");
                txtDate1.Text = tmpDate;
            }

            //Group2
            if (dt.Select("sp1_osw_grp='5' and sp2_value_date is not null").Length != 0)
            {
                ll_found = dt.Rows.IndexOf(dt.Select("sp1_osw_grp='5' and sp2_value_date is not null")[0]);
            }
            else
            {
                ll_found = -1;
            }

            if (ll_found >= 0)
            {
                string sp2ValueDate = dt.Rows[ll_found]["sp2_value_date"].AsDateTime().ToString("yyyy/MM/dd");
                txtDate2.Text = sp2ValueDate;
            }
            else
            {
                string tmpDate = PbFunc.f_get_ocf_next_n_day(txtTradeDate.DateTimeValue, 2).ToString("yyyy/MM/dd");
                txtDate2.Text = tmpDate;
            }
            #endregion

            gcMain.Visible    = true;
            gcMain.DataSource = dt;
            GridHelper.SetCommonGrid(gvMain);
            gvMain.OptionsBehavior.AllowFixedGroups = DefaultBoolean.True;

            gvMain.Columns["OSW_GRP"].Group();
            gvMain.Columns["SP1_CHANGE_RANGE"].DisplayFormat.FormatType   = FormatType.Numeric;
            gvMain.Columns["SP1_CHANGE_RANGE"].DisplayFormat.FormatString = "P";

            gvMain.AppearancePrint.HeaderPanel.Options.UseTextOptions = true;
            gvMain.ColumnPanelRowHeight               = 20;
            gvMain.AppearancePrint.HeaderPanel.Font   = new Font("Microsoft YaHei", gvMain.AppearancePrint.HeaderPanel.Font.Size);
            gvMain.AppearancePrint.Row.Font           = new Font("Microsoft YaHei", 12);
            gvMain.OptionsPrint.AllowMultilineHeaders = true;
            gvMain.AppearancePrint.GroupRow.Font      = new Font("Microsoft YaHei", 12);

            gvMain.BestFitColumns();
            gvMain.ExpandAllGroups();
            gcMain.Focus();

            return(ResultStatus.Success);
        }
Esempio n. 14
0
        /// <summary>
        /// 按下[匯出]按鈕時
        /// </summary>
        /// <returns></returns>
        protected override ResultStatus Export()
        {
            //1.check
            if (gvKind.DataRowCount <= 0)
            {
                MessageDisplay.Normal("選擇的日期必須有契約資訊,請重新選擇日期");
                return(ResultStatus.Fail);
            }

            //1.1檢查最少必須勾選一筆商品
            gvDate.CloseEditor();      //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
            gvDate.UpdateCurrentRow(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
            gvKind.CloseEditor();      //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
            gvKind.UpdateCurrentRow(); //ken,一定要先呼叫這兩個函數,最後點選的那筆才會被記錄起來
            DataTable dtTemp   = (DataTable)gcKind.DataSource;
            bool      haveKind = false;

            foreach (DataRow drKind in dtTemp.Rows)
            {
                if (drKind["CPR_SELECT"].AsString() == "Y")
                {
                    haveKind = true;
                    break;
                }
            }

            if (!haveKind)
            {
                MessageDisplay.Normal("必須勾選一筆契約");
                return(ResultStatus.Fail);
            }

            if (chkModel.CheckedItemsCount < 1)
            {
                MessageDisplay.Error("請至少勾選一種指標種類", GlobalInfo.ErrorText);
                return(ResultStatus.Fail);
            }

            //1.2檢查統計資料是否已經轉入完畢
            string FinishedJob = PbFunc.f_get_jsw(_ProgramID, "E", txtEndDate.Text);

            if (FinishedJob != "Y")
            {
                DialogResult chooseResult = MessageDisplay.Choose(string.Format("{0} 統計資料未轉入完畢,是否要繼續?", txtEndDate.Text), MessageBoxDefaultButton.Button2, GlobalInfo.QuestionText);
                if (chooseResult != DialogResult.Yes)
                {
                    return(ResultStatus.Fail);
                }
            }

            try {
                //2.開始轉出資料
                panFilter.Enabled = panSecond.Enabled = false;
                labMsg.Visible    = true;
                labMsg.Text       = "開始轉檔...";
                this.Refresh();

                //2.1 copy template xlsx to target path and open
                Workbook workbook             = new Workbook();
                string   originalFilePath     = Path.Combine(GlobalInfo.DEFAULT_EXCEL_TEMPLATE_DIRECTORY_PATH, _ProgramID + "." + FileType.XLSX.ToString().ToLower());
                string   excelDestinationPath = "";

                DataTable dtDate = (DataTable)gcDate.DataSource;

                #region 指標:SMA,EWMA,MAX
                foreach (CheckedListBoxItem item in chkModel.Items)
                {
                    if (item.CheckState == CheckState.Unchecked)
                    {
                        continue;
                    }

                    int    sheetIndex = 0;
                    int    flag       = 0;
                    string modelType  = "";
                    switch (item.Value)
                    {
                    case "chkSma":
                        excelDestinationPath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH,
                                                            _ProgramID + "_SMA_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss.") + FileType.XLSX.ToString().ToLower());
                        modelType = "S";
                        break;

                    case "chkEwma":
                        excelDestinationPath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH,
                                                            _ProgramID + "_EWMA_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss.") + FileType.XLSX.ToString().ToLower());
                        modelType = "E";
                        break;

                    case "chkMax":
                        excelDestinationPath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH,
                                                            _ProgramID + "_MAX_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss.") + FileType.XLSX.ToString().ToLower());
                        modelType = "M";
                        break;
                    }

                    //copy template and change filename
                    File.Copy(originalFilePath, excelDestinationPath, true);
                    workbook.LoadDocument(excelDestinationPath);

                    #region 時段
                    //每個時間區間為一個sheet,總共5個
                    foreach (DataRow drDate in dtDate.Rows)
                    {
                        string monDiff   = drDate["MON_DIFF"].AsString();                     //期間
                        string startDate = drDate["SDATE"].AsDateTime().ToString("yyyyMMdd"); //資料起日
                        string endDate   = drDate["EDATE"].AsDateTime().ToString("yyyyMMdd"); //資料迄日
                                                                                              //DateTime startDate = drDate["SDATE"].AsDateTime();//資料起日
                                                                                              //DateTime endDate = drDate["EDATE"].AsDateTime();//資料迄日
                        int dayCount = drDate["DAY_CNT"].AsInt();                             //天數

                        //2.2跳到指定sheet,寫檔頭
                        Worksheet worksheet = workbook.Worksheets[sheetIndex++];
                        worksheet.Cells[1, 2].Value  = cbxSubType.Text;
                        worksheet.Cells[2, 11].Value = string.Format("列印日期:{0}", DateTime.Now.ToString("yyyy/MM/dd"));
                        worksheet.Cells[2, 1].Value  = string.Format("{0}({1}~{2}),計{3}天",
                                                                     monDiff,
                                                                     drDate["SDATE"].AsDateTime().ToString("yyyy/MM/dd"),
                                                                     drDate["EDATE"].AsDateTime().ToString("yyyy/MM/dd"),
                                                                     dayCount.ToString());

                        #region                 //2.3分別讀取每個商品的詳細資訊
                        int rowIndex      = 7;
                        int emptyRowCount = 60; //template 空白行的數量
                        int kindCount     = 0;

                        //逐一看勾選的商品有哪些
                        foreach (DataRow drKind in dtTemp.Rows)
                        {
                            if (drKind["CPR_SELECT"].AsString() != "Y")
                            {
                                continue;
                            }

                            kindCount++;
                            string  kindId   = drKind["cpr_kind_id"].AsString();          //契約ID
                            Decimal riskRate = drKind["cpr_price_risk_rate"].AsDecimal(); //現行最小風險價格係數
                            Decimal interval = drKind["risk_interval"].AsDecimal();       //最小風險價格係數級距

                            //2.3.1讀取子table data
                            DataTable dtSingleKind = dao48030.ListKindByKindId(startDate, endDate, riskRate, interval, kindId, modelType);
                            if (dtSingleKind.Rows.Count <= 0)
                            {
                                labMsg.Text += string.Format("{0},{1}~{2}無任何資料!\r\n", kindId, startDate, endDate);
                                this.Refresh();
                                continue;
                            }

                            //2.3.2寫入明細
                            //ken,原則上一個商品只會找到一筆明細(已經group by)
                            DataRow drDetail  = dtSingleKind.Rows[0];
                            int     tempCount = drDetail["cnt"].AsInt();
                            Decimal level_1   = drDetail["level_1"].AsDecimal();
                            Decimal level_23  = drDetail["level_23"].AsDecimal();
                            Decimal level_4   = drDetail["level_4"].AsDecimal();

                            worksheet.Cells[rowIndex, 0].Value = drDetail["mg1_kind_id"].AsString();
                            worksheet.Cells[rowIndex, 1].Value = drDetail["avg_risk"].AsDecimal();
                            worksheet.Cells[rowIndex, 2].Value = drDetail["max_risk"].AsDecimal();
                            worksheet.Cells[rowIndex, 3].Value = drDetail["min_risk"].AsDecimal();
                            worksheet.Cells[rowIndex, 4].Value = riskRate;

                            worksheet.Cells[rowIndex, 6].Value  = level_1;
                            worksheet.Cells[rowIndex, 7].Value  = Math.Round(level_1 / tempCount, 4, MidpointRounding.AwayFromZero);
                            worksheet.Cells[rowIndex, 8].Value  = level_23;
                            worksheet.Cells[rowIndex, 9].Value  = Math.Round(level_23 / tempCount, 4, MidpointRounding.AwayFromZero);
                            worksheet.Cells[rowIndex, 10].Value = level_4;
                            worksheet.Cells[rowIndex, 11].Value = Math.Round(level_4 / tempCount, 4, MidpointRounding.AwayFromZero);

                            worksheet.Cells[rowIndex, 12].Value = interval;

                            rowIndex++;
                            flag++;
                        }//foreach (DataRow drKind in dtTemp.Rows) 商品
                        #endregion

                        //2.4刪除空白列 (結束商品foreach才刪除空白列,跑下一個sheet)
                        if (kindCount < emptyRowCount)
                        {
                            worksheet.Rows.Remove(rowIndex, emptyRowCount - kindCount);
                        }
                    }//foreach (DataRow drDate in dtDate.Rows) 時段
                    #endregion

                    //2.9存檔
                    if (flag > 0)
                    {
                        workbook.SaveDocument(excelDestinationPath);
                    }
                    else
                    {
                        File.Delete(excelDestinationPath);
                    }
                }//foreach (CheckedListBoxItem item in chkModel.Items) 指標
                #endregion

                //if (FlagAdmin)
                //   System.Diagnostics.Process.Start(excelDestinationPath);

                return(ResultStatus.Success);
            } catch (Exception ex) {
                WriteLog(ex);
            } finally {
                panFilter.Enabled = panSecond.Enabled = true;
                labMsg.Text       = "";
                labMsg.Visible    = false;
            }
            return(ResultStatus.Fail);
        }
Esempio n. 15
0
        /// <summary>
        /// wf_30333()
        /// 30398,30399,30393相同
        /// </summary>
        /// <param name="IsKindID">商品代號</param>
        /// <param name="SheetName">工作表</param>
        /// <param name="RowIndex">Excel的Row位置</param>
        /// <param name="RowTotal">Excel的Column預留數</param>
        /// <returns></returns>
        public string Wf30333(string IsKindID = "GTF", string SheetName = "data_30398abc", int RowIndex = 3, int RowTotal = 12)
        {
            try {
                //切換Sheet
                Worksheet worksheet = _workbook.Worksheets[SheetName];
                //總列數
                int sumRowIndex = RowTotal + RowIndex + 1; //小計行數
                int addRowCount = 0;                       //總計寫入的行數
                worksheet.Rows[sumRowIndex][1 - 1].Value = $"{PbFunc.Left(_emMonthText, 4).AsInt() - 1911}小計";
                string lsYMD = "";
                //讀取資料
                DataTable dt = daoAM2.ListAM2(IsKindID, $"{PbFunc.Left(_emMonthText, 4)}01", _emMonthText.Replace("/", ""));
                //寫入資料
                foreach (DataRow row in dt.Rows)
                {
                    if (lsYMD != row["AM2_YMD"].AsString())
                    {
                        RowIndex = RowIndex + 1;
                        lsYMD    = row["AM2_YMD"].AsString();
                        //li_month_cnt = li_month_cnt + 1;
                        addRowCount++;
                        worksheet.Rows[RowIndex][1 - 1].Value = $"{PbFunc.Left(lsYMD, 4).AsInt() - 1911}/{PbFunc.Right(lsYMD, 2)}";
                    }
                    //判斷欄位
                    int columnIndex = IDFGtype(row);

                    worksheet.Rows[RowIndex][columnIndex].Value = row["AM2_M_QNTY"].AsDecimal();//買 or 賣
                }
                //刪除空白列
                if (RowTotal > addRowCount)
                {
                    worksheet.Rows.Hide(RowIndex + 1, RowIndex + (RowTotal - addRowCount)); //隱藏代替刪除,這樣就不用再重新抓取圖表範圍
                    worksheet.ScrollTo(0, 0);                                               //直接滾動到最上面,不然看起來很像少行數
                }
            }
            catch (Exception ex) {
#if DEBUG
                throw new Exception($"Wf30333:" + ex.Message);
#else
                throw ex;
#endif
            }

            return(MessageDisplay.MSG_OK);
        }
Esempio n. 16
0
        protected override ResultStatus Export()
        {
            base.Export();

            #region 確認統計資料未轉入完畢
            string       lsRtn = PbFunc.f_get_jsw(_ProgramID, "E", txtDate.Text);
            DialogResult liRtn;

            if (lsRtn != "Y")
            {
                liRtn = MessageDisplay.Choose(String.Format("{0} 統計資料未轉入完畢,是否要繼續?", txtDate.Text));
                if (liRtn == DialogResult.No)
                {
                    labMsg.Visible = false;
                    Cursor.Current = Cursors.Arrow;
                    return(ResultStatus.Fail);
                }
            }//if (lsRtn != "Y")
            #endregion

            try {
                //1. 判斷為單一商品還是全部
                DataTable dt       = dao40130.GetDataList(txtDate.DateTimeValue, "%");
                DataTable dtSelect = new DataTable();
                string    kindId   = dwKindId.EditValue.AsString();
                if (kindId != "%")
                {
                    dtSelect = dt.Filter("mgt2_kind_id = '" + kindId + "'");
                }
                else //kindId = "%"
                {
                    dtSelect = dt.Filter("mgt2_kind_id <> '%'");
                }

                //1.1 準備開檔
                string   originalFilePath    = "";
                string   destinationFilePath = "";
                Workbook workbook            = new Workbook();

                //2. 填資料
                foreach (DataRow dr in dtSelect.Rows)
                {
                    string fileKind = dr["mgt2_kind_id"].AsString();

                    //2.1 開啟&複製檔案(因可能有多個excel所以在迴圈裡兜路徑)
                    originalFilePath = Path.Combine(GlobalInfo.DEFAULT_EXCEL_TEMPLATE_DIRECTORY_PATH,
                                                    string.Format("{0}.{1}", _ProgramID, FileType.XLS.ToString().ToLower()));
                    destinationFilePath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH,
                                                       string.Format("{0}({1})_{2}.{3}", _ProgramID, fileKind, DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss"), FileType.XLS.ToString().ToLower()));

                    File.Copy(originalFilePath, destinationFilePath, true);
                    workbook.LoadDocument(destinationFilePath);
                    kindId = dr["mgt2_kind_id"].AsString() + "%";
                    string tmpKindId = kindId.SubStr(2, 1);
                    if (tmpKindId == "F") //Sheet:期貨data
                    {
                        wf_40131(workbook, kindId);
                    }
                    else //Sheet:選擇權data
                    {
                        wf_40132(workbook, kindId);
                    }

                    //3. save
                    workbook.SaveDocument(destinationFilePath);
                }//foreach (DataRow dr in dt.Rows)

                labMsg.Visible = false;

                return(ResultStatus.Success);
            } catch (Exception ex) {
                WriteLog(ex);
            } finally {
                panFilter.Enabled = true;
                labMsg.Text       = "";
                labMsg.Visible    = false;
                this.Cursor       = Cursors.Arrow;
            }
            return(ResultStatus.Fail);
        }
Esempio n. 17
0
        protected override ResultStatus Export()
        {
            dao30205 = new D30205();
            string rptId, file;

            rptId = "30205";

            //複製檔案
            file = PbFunc.wf_copy_file(rptId, rptId);
            if (file == "")
            {
                return(ResultStatus.Fail);
            }
            logTxt = file;

            //開啟檔案
            Workbook workbook = new Workbook();

            workbook.LoadDocument(file);

            #region wf_30205
            string rptName, raiseStr, lowerStr;
            int    f, g, rowNum;
            rptName = "指數類歷次部位限制查詢";
            rptId   = "30205";

            //讀取資料
            DataTable dt30205 = dao30205.d_30205(txtSDate.Text.Replace("/", ""), txtEDate.Text.Replace("/", ""));
            if (dt30205.Rows.Count == 0)
            {
                MessageDisplay.Info(txtSDate.Text + "~" + txtEDate.Text + "," + rptId + '-' + rptName + ",無任何資料!");
                //若所有Sheet皆無資料時,刪除檔案
                workbook = null;
                System.IO.File.Delete(file);
                return(ResultStatus.Fail);
            }

            //切換sheet
            Worksheet ws30205 = workbook.Worksheets[0];
            ws30205.Cells[1, 0].Value = ws30205.Cells[1, 0].Value.ToString() + txtSDate.Text + "~" + txtEDate.Text; //填寫公告日期

            //填入資料
            rowNum = 2;
            for (f = 0; f < dt30205.Rows.Count; f++)
            {
                DataRow dr = dt30205.Rows[f];
                rowNum   = rowNum + 1;
                raiseStr = "";
                lowerStr = "";
                string pl2Ymd, pl2EffectiveYmd;
                pl2Ymd          = dr["PL2_YMD"].AsString();
                pl2EffectiveYmd = dr["PL2_EFFECTIVE_YMD"].AsString();
                //運算欄位cp_grp_cnt("count(  pl2_kind_id  for group 1 )")
                //group 1 ("pl2_ymd" , "pl2_effective_ymd)
                int cpGrpCnt = dt30205.Compute("count( pl2_kind_id)", $@"PL2_YMD='{pl2Ymd}' and PL2_EFFECTIVE_YMD='{pl2EffectiveYmd}'").AsInt();
                for (g = 1; g <= cpGrpCnt; g++)
                {
                    if (dt30205.Rows[f + g - 1]["PL2_NATURE_ADJ"].AsString() == "+" ||
                        dt30205.Rows[f + g - 1]["PL2_LEGAL_ADJ"].AsString() == "+" ||
                        dt30205.Rows[f + g - 1]["PL2_999_ADJ"].AsString() == "+")
                    {
                        raiseStr = raiseStr + dt30205.Rows[f + g - 1]["PL2_KIND_ID"].AsString() + "/";
                    }
                    else
                    {
                        lowerStr = lowerStr + dt30205.Rows[f + g - 1]["PL2_KIND_ID"].AsString() + "/";
                    }
                }
                f = f + cpGrpCnt - 1;
                if (dr["PL2_YMD"] != DBNull.Value)
                {
                    ws30205.Cells[rowNum, 0].Value = dr["PL2_YMD"].AsDateTime("yyyyMMdd").ToString("yyyy/M/d");
                }
                if (dr["PL2_EFFECTIVE_YMD"] != DBNull.Value)
                {
                    ws30205.Cells[rowNum, 1].Value = dr["PL2_EFFECTIVE_YMD"].AsDateTime("yyyyMMdd").ToString("yyyy/M/d");
                }
                ws30205.Cells[rowNum, 2].Value = raiseStr.SubStr(0, raiseStr.Length - 1);
                ws30205.Cells[rowNum, 3].Value = lowerStr.SubStr(0, lowerStr.Length - 1);
            }
            #endregion

            //存檔
            ws30205.ScrollToRow(0);
            workbook.SaveDocument(file);

            return(ResultStatus.Success);
        }
Esempio n. 18
0
        protected override ResultStatus Export()
        {
            string showMsg = "";

            try {
                this.Cursor = Cursors.WaitCursor;
                this.Refresh();
                Thread.Sleep(5);
                lblProcessing.Visible = true;
                ShowMsg("開始轉檔...");
                dao30202 = new D30202();
                //判斷是否有檔案,決定是否要寫入DB.
                showMsg = "讀取既有計算資料錯誤";
                string    cpYmd = txtDate.DateTimeValue.ToString("yyyyMMdd");
                DataTable dtPL1 = dao30202.d_30202_pl1(cpYmd);
                if (dtPL1.Rows.Count > 0)
                {
                    DialogResult result = MessageDisplay.Choose("已有計算資料,是否要更新資料庫資料?");
                    if (result == DialogResult.No)
                    {
                        cbxDB.Checked = false;
                    }
                }
                txtPrevEymd.DateTimeValue = PbFunc.f_get_end_day("DATE", "", txtEMonth.Text);
                txtCurEymd.DateTimeValue  = PbFunc.f_get_end_day("DATE", "", txtCurEMonth.Text);

                string rptId = "30202", rptName = "股價指數暨黃金類商品部位限制數檢視表", file,
                       curSMonth  = txtCurSMonth.Text.Replace("/", ""),
                       curEMonth  = txtCurEMonth.Text.Replace("/", ""),
                       sMonth     = txtSMonth.Text.Replace("/", ""),
                       eMonth     = txtEMonth.Text.Replace("/", "");
                decimal natureSdt = txtMultiNature.Text.AsDecimal() / 100;
                decimal legalSdt  = txtMultiLegal.Text.AsDecimal() / 100;

                //讀取資料
                showMsg = "讀取資料錯誤";
                DataTable dt30202 = dao30202.d_30202(cpYmd, sMonth, eMonth, curSMonth, curEMonth, natureSdt, legalSdt);
                if (dt30202.Rows.Count == 0)
                {
                    MessageDisplay.Info(eMonth + "," + rptId + '-' + rptName + ",無任何資料!");
                    lblProcessing.Visible = false;
                    return(ResultStatus.Fail);
                }

                ShowMsg(rptId + '-' + rptName + " 轉檔中...");
                //複製檔案
                showMsg = "複製檔案錯誤";
                file    = PbFunc.wf_copy_file(rptId, rptId);
                if (file == "")
                {
                    return(ResultStatus.Fail);
                }

                //開啟檔案
                showMsg = "開啟檔案錯誤";
                Workbook workbook = new Workbook();
                workbook.LoadDocument(file);

                //切換Sheet
                showMsg = "切換Sheet錯誤";
                Worksheet ws30202 = workbook.Worksheets[0];

                //寫入資料
                showMsg = "寫入資料錯誤";
                int rowIndex = 2;
                lblProcessing.Text = rptId + "-" + rptName + " 轉檔中...";

                #region wf_30202
                string  str, natureType, legalType, col;
                decimal value1, changeRange, curNature, curLegal, cpNature, cpLegal, nature, legal;
                if (!cbxDB.Checked)
                {
                    ws30202.Cells[0, 0].Value = ws30202.Cells[0, 0].Value.AsString() + "(試算)";
                }
                //(一)
                ws30202.Cells[3, 1].Value = "(" + txtSMonth.Text + "/01" + "~" + txtPrevEymd.Text + ")";
                //(二)
                ws30202.Cells[3, 3].Value = "(" + txtCurSMonth.Text + "/01" + "~" + txtCurEymd.Text + ")";
                //(六)
                str = txtCurEMonth.Text;
                DateTime date = (str + "/01").AsDateTime("yyyy/MM/dd");
                do
                {
                    date = PbFunc.relativedate(date, date.Day * -1);
                    str  = str + "、" + date.ToString("yyyy/MM~") + txtCurEMonth.Text;
                } while ((date.ToString("yyyy/MM") + "/01").AsDateTime("yyyy/MM/dd") > txtSMonth.DateTimeValue);//只比年月
                ws30202.Cells[3, 12].Value = str;

                foreach (DataRow dr in dt30202.Rows)
                {
                    rowIndex = dr["RPT_SEQ_NO"].AsInt() - 1;
                    decimal pAvgQnty = dr["P_AVG_QNTY"].AsDecimal();
                    decimal pAvgOi   = dr["P_AVG_OI"].AsDecimal();
                    //前次檢視之數值
                    ws30202.Cells[rowIndex, 1].Value = pAvgQnty;
                    ws30202.Cells[rowIndex, 2].Value = pAvgOi;
                    //本次檢視之數值
                    ws30202.Cells[rowIndex, 3].SetValue(dr["C_AVG_QNTY"]);
                    ws30202.Cells[rowIndex, 4].SetValue(dr["C_AVG_OI"]);
                    //相較前次數值增減幅度
                    if (pAvgQnty > pAvgOi)
                    {
                        value1 = pAvgQnty;
                    }
                    else
                    {
                        value1 = pAvgOi;
                    }
                    if (value1 == 0)
                    {
                        changeRange = -1;
                    }
                    else
                    {
                        changeRange = Math.Round(dr["C_MAX_VALUE"].AsDecimal() / value1 - 1, 4, MidpointRounding.AwayFromZero);
                    }
                    ws30202.Cells[rowIndex, 5].Value = changeRange;
                    dr["CHANGE_RANGE"] = changeRange;
                    //現行部位限制數
                    curNature = dr["PL2_NATURE"] == DBNull.Value ? -1 : dr["PL2_NATURE"].AsDecimal(); //當該欄位的值為DBNull時等於-1 (權宜做法)
                    curLegal  = dr["PL2_LEGAL"] == DBNull.Value ? -1 : dr["PL2_LEGAL"].AsDecimal();   //當該欄位的值為DBNull時等於-1 (權宜做法)
                    if (dr["PL2_NATURE"] != DBNull.Value)
                    {
                        ws30202.Cells[rowIndex, 6].Value = curNature;
                    }
                    if (dr["PL2_LEGAL"] != DBNull.Value)
                    {
                        ws30202.Cells[rowIndex, 7].Value = curLegal;
                    }

                    //按交易規則檢視後之部位限制數
                    //自然人
                    value1 = dr["PLT1_T1_MULTIPLE"] == DBNull.Value ? -1 : dr["PLT1_T1_MULTIPLE"].AsDecimal();//當該欄位的值為DBNull時等於-1 (權宜做法)
                    if (dr["PLT1_T1_MIN_NATURE"] == DBNull.Value)
                    {
                        cpNature = dr["C_MAX_VALUE"].AsDecimal() * natureSdt;
                        if (value1 > 0)
                        {
                            cpNature = Math.Truncate(cpNature / value1) * value1;
                        }
                    }
                    else
                    {
                        cpNature = dr["PLT1_T1_MIN_NATURE"].AsDecimal();
                    }
                    ws30202.Cells[rowIndex, 9].Value = cpNature;
                    dr["CP_NATURE"] = cpNature;
                    //法人
                    value1 = dr["PLT1_T2_MULTIPLE"] == DBNull.Value ? -1 : dr["PLT1_T2_MULTIPLE"].AsDecimal();//當該欄位的值為DBNull時等於-1 (權宜做法)
                    if (dr["PLT1_T2_MIN_LEGAL"] == DBNull.Value)
                    {
                        cpLegal = dr["C_MAX_VALUE"].AsDecimal() * legalSdt;
                        if (value1 > 0)
                        {
                            cpLegal = Math.Truncate(cpLegal / value1) * value1;
                        }
                    }
                    else
                    {
                        cpLegal = dr["PLT1_T2_MIN_LEGAL"].AsDecimal();
                    }
                    ws30202.Cells[rowIndex, 10].Value = cpLegal;
                    dr["CP_LEGAL"] = cpLegal;
                    dr["CP_999"]   = cpLegal * 3;

                    //近1~6月日均交易量與未沖銷量

                    /****************************
                    *  1. 檢視部位限制級距時,若該期間之每日平均交易量或未沖銷量與前次調整時相較,其增減未逾百分之二‧五時,雖達調整級距標準,仍不調整。
                    *  2.針對須降低部位限制數之商品再增加以最近1、2、4、5、6個月區間資料檢視,並取其數額大者為基準數,惟計算後不得超過前次之部位限制數。
                    ****************************/
                    //(isnull(ld_cur_nature) and isnull(ld_cur_legal))這個條件拿掉,因為不會成立
                    if (Math.Abs(changeRange) <= 0.025m ||
                        (curNature == -1 && curLegal == -1) ||
                        (curNature == cpNature && curLegal == cpLegal))
                    {
                        str = "不適用";
                        if (curNature != -1 && curLegal != -1)
                        {
                            nature = curNature;
                            legal  = curLegal;
                        }
                        else
                        {
                            nature = cpNature;
                            legal  = cpLegal;
                        }
                        natureType = "不變";
                        legalType  = "不變";
                    }
                    else
                    {
                        if (cpNature < curNature || cpLegal < curLegal)
                        {
                            col = "max";                                            //最大者
                        }
                        else
                        {
                            col = "min"; //最小者
                        }
                        str = "近" + dr[col + "_MONTH_SEQ_NO"].AsString() + "個月";
                        if (dr[col + "_TYPE"].AsString() == "OI")
                        {
                            str = str + "未沖銷量";
                        }
                        else
                        {
                            str = str + "交易量";
                        }
                        if (col == "max")
                        {
                            str = str + "最大者";
                        }
                        else
                        {
                            str = str + "最小者";
                        }
                        str = str + "(" + dr[col + "_VALUE"].AsDecimal().ToString("#,##0") + ")";
                        //自然人
                        value1 = dr["PLT1_R1_MULTIPLE"] == DBNull.Value ? -1 : dr["PLT1_R1_MULTIPLE"].AsDecimal();//當該欄位的值為DBNull時等於-1 (權宜做法)
                        if (dr["PLT1_R1_MIN_NATURE"] == DBNull.Value)
                        {
                            nature = dr[col + "_VALUE"].AsDecimal() * natureSdt;
                            if (value1 > 0)
                            {
                                nature = Math.Truncate(nature / value1) * value1;
                            }
                        }
                        else
                        {
                            nature = dr["PLT1_R1_MIN_NATURE"].AsDecimal();
                        }
                        natureType = "不變";
                        if (nature < curNature)
                        {
                            natureType = "降低";
                        }
                        else if (nature > curNature)
                        {
                            natureType = "調高";
                        }
                        //法人
                        value1 = dr["PLT1_R2_MULTIPLE"] == DBNull.Value ? -1 : dr["PLT1_R2_MULTIPLE"].AsDecimal();//當該欄位的值為DBNull時等於-1 (權宜做法)
                        if (dr["PLT1_R2_MIN_LEGAL"] == DBNull.Value)
                        {
                            legal = dr[col + "_VALUE"].AsDecimal() * legalSdt;
                            if (value1 > 0)
                            {
                                legal = Math.Truncate(legal / value1) * value1;
                            }
                        }
                        else
                        {
                            legal = dr["PLT1_R2_MIN_LEGAL"].AsDecimal();
                        }
                        legalType = "不變";
                        if (legal < curLegal)
                        {
                            legalType = "降低";
                        }
                        else if (legal > curLegal)
                        {
                            legalType = "調高";
                        }
                    }
                    ws30202.Cells[rowIndex, 12].Value = str;
                    ws30202.Cells[rowIndex, 14].Value = nature;
                    ws30202.Cells[rowIndex, 15].Value = legal;
                    ws30202.Cells[rowIndex, 16].Value = natureType;
                    ws30202.Cells[rowIndex, 17].Value = legalType;
                    dr["NATURE"] = nature;
                    dr["LEGAL"]  = legal;
                    dr["P999"]   = legal * 3;
                    switch (natureType)
                    {
                    case "降低":
                        str = "-";
                        break;

                    case "調高":
                        str = "+";
                        break;

                    default:
                        str = " ";
                        break;
                    }
                    dr["NATURE_ADJ"] = str;
                    switch (legalType)
                    {
                    case "降低":
                        str = "-";
                        break;

                    case "調高":
                        str = "+";
                        break;

                    default:
                        str = " ";
                        break;
                    }
                    dr["LEGAL_ADJ"] = str;
                    dr["P999_ADJ"]  = str;
                    dr["YMD"]       = cpYmd;
                    //針對須調降之商品再增加檢視標準後之部位限制數
                }//foreach (DataRow dr in dt30202.Rows)

                //表尾
                rowIndex = dao30202.row_index();
                if (rowIndex > 0)
                {
                    ws30202.Cells[rowIndex - 1, 0].Value = ws30202.Cells[rowIndex - 1, 0].Value.ToString() +
                                                           "自然人乘以" + txtMultiNature.Text +
                                                           "%,法人乘以" + txtMultiLegal.Text + "%)";
                }

                //存檔
                ws30202.ScrollToRow(0);
                workbook.SaveDocument(file);
                ShowMsg("轉檔成功");
                #endregion

                if (!cbxDB.Checked)
                {
                    return(ResultStatus.Success);
                }
                #region wf_30202_write
                bool dbCommit = false;
                //刪除PL0的資料
                showMsg  = "PL0刪除失敗";
                dbCommit = dao30202.DeletePL0ByDate(cpYmd);
                if (!dbCommit)
                {
                    MessageDisplay.Error(showMsg);
                    return(ResultStatus.Fail);
                }
                //新增PL0的資料
                showMsg  = "PL0新增失敗";
                dbCommit = dao30202.InsertPL0(cpYmd, sMonth, eMonth, curSMonth, curEMonth, GlobalInfo.USER_ID);
                if (!dbCommit)
                {
                    MessageDisplay.Error(showMsg);
                    return(ResultStatus.Fail);
                }
                //刪除PL2的資料
                showMsg  = "PL2刪除失敗";
                dbCommit = dao30202.DeletePL2ByDate(cpYmd);
                if (!dbCommit)
                {
                    MessageDisplay.Error(showMsg);
                    return(ResultStatus.Fail);
                }
                //刪除PL1的資料
                showMsg  = "PL1刪除失敗";
                dbCommit = dao30202.DeletePL1ByDate(cpYmd);
                if (!dbCommit)
                {
                    MessageDisplay.Error(showMsg);
                    return(ResultStatus.Fail);
                }

                //寫入
                dtPL1.Clear();
                dtPL1.AcceptChanges();
                for (int f = 0; f < dt30202.Rows.Count; f++)
                {
                    dtPL1.Rows.Add();
                    for (int g = 0; g < 24; g++)
                    {
                        dtPL1.Rows[dtPL1.Rows.Count - 1][g] = dt30202.Rows[f][g];
                        //現行自然人,現行法人PL
                        if (g == 9 || g == 11)
                        {
                            if (dt30202.Rows[f][g] == DBNull.Value)
                            {
                                dtPL1.Rows[dtPL1.Rows.Count - 1][g] = 0;
                            }
                        }
                    }
                    dtPL1.Rows[dtPL1.Rows.Count - 1][24] = DateTime.Now;
                    dtPL1.Rows[dtPL1.Rows.Count - 1][25] = GlobalInfo.USER_ID;
                }
                try {
                    ResultData myResultData = dao30202.updatePL1(dtPL1);
                }
                catch (Exception ex) {
                    MessageDisplay.Error("計算結果新增至資料庫錯誤! ");
                    WriteLog(ex, "", false);
                    return(ResultStatus.Fail);
                }
                #endregion
            }
            catch (Exception ex) {
                MessageDisplay.Error(showMsg);
                throw ex;
            }
            finally {
                this.Cursor = Cursors.Arrow;
                this.Refresh();
                Thread.Sleep(5);
            }
            return(ResultStatus.Success);
        }
Esempio n. 19
0
        protected override ResultStatus Export()
        {
            ExportShow.Text = "轉檔中...";
            ExportShow.Show();

            Workbook workbook = new Workbook();

            string destinationFilePath = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
            string sYm = txtSDate.DateTimeValue.ToString("yyyyMM");
            string eYm = txtEDate.DateTimeValue.ToString("yyyyMM");
            string eymd = string.IsNullOrEmpty(dao30760.GetMaxDate(eYm)) ? eYm + "01" : dao30760.GetMaxDate(eYm);//讀取迄年月的最大之交易日
            int    oleRow = 5, oleCol = 0;

            try {
                workbook.LoadDocument(destinationFilePath);
                int noData = 0;

                //各產品資料
                Worksheet worksheet  = workbook.Worksheets[0];
                DataTable dtProdData = dao30760.GetProdData(sYm, eYm, eymd);
                DataTable dtSum      = dao30760.GetSumData(sYm, eYm, eymd);
                if (dtProdData.Rows.Count <= 0)
                {
                    ExportShow.Text = "各產品無任何資料!";
                    noData         += 1;
                }
                ProdData(worksheet, dtProdData, dtSum, oleRow, oleCol);

                //個股選擇權資料
                worksheet = workbook.Worksheets[1];
                DataTable dtTradedData = dao30760.GetTradedData(sYm, eYm, eymd, "O");
                if (dtTradedData.Rows.Count <= 0)
                {
                    ExportShow.Text = "個股選擇權無任何資料!";
                    noData         += 1;
                }
                oleRow = 5;
                TradedData(worksheet, dtTradedData, oleRow, oleCol);

                //個股期貨資料
                worksheet = workbook.Worksheets[2];
                DataTable dtFuturesData = dao30760.GetFuturesData(sYm, eYm, eymd, "F");
                if (dtFuturesData.Rows.Count <= 0)
                {
                    ExportShow.Text = "個股期貨無任何資料!";
                    noData         += 1;
                }
                oleRow = 5;
                FuturesData(worksheet, dtFuturesData, oleRow, oleCol);

                //Save
                workbook.SaveDocument(destinationFilePath);

                //完全無資料時, 刪檔
                if (noData == 3)
                {
                    File.Delete(destinationFilePath);
                    return(ResultStatus.FailButNext);
                }
            } catch (Exception ex) {
                ExportShow.Text = "轉檔失敗";
                throw ex;
            }
            ExportShow.Text = "轉檔成功!";
            return(ResultStatus.Success);
        }
Esempio n. 20
0
        private bool ManipulateExcel()
        {
            //測試資料查詢日期:2017/12/01
            try {
                txtFromDate.Enabled = false;
                txtToDate.Enabled   = false;

                this.Cursor = Cursors.WaitCursor;
                this.Refresh();
                Thread.Sleep(5);
                lblProcessing.Visible = true;
                ShowMsg("開始轉檔...");
                string rptName, rptId, file;
                int    i;

                /*************************************
                *  ls_rpt_name = 報表名稱
                *  ls_rpt_id = 報表代號
                *  li_ole_col = 欄位位置
                *  ls_param_key = 契約
                *************************************/
                rptName = "STF報價期貨商明細加總日報表";
                rptId   = "50072";
                ShowMsg(rptId + "-" + rptName + " 轉檔中...");

                #region Excel

                //讀取資料
                DataTable dtContent = dao50072.ListData(txtFromDate.FormatValue, txtToDate.FormatValue);
                if (dtContent.Rows.Count == 0)
                {
                    MessageDisplay.Info(string.Format("{0} - {1}, {2} - {3},無任何資料!", txtFromDate.Text, txtToDate.Text, rptId, rptName));
                }
                DataTable dtValidContent = dao50072.ListData_valid(txtFromDate.FormatValue, txtToDate.FormatValue);
                if (dtValidContent.Rows.Count == 0)
                {
                    MessageDisplay.Info(string.Format("{0} - {1}, {2} - {3},無任何資料!", txtFromDate.Text, txtToDate.Text, rptId, "合格報價資料"));
                }

                //複製檔案
                file = PbFunc.wf_copy_file(rptId, rptId);
                if (file == "")
                {
                    return(false);
                }

                //填資料
                Workbook workbook = new Workbook();
                workbook.LoadDocument(file);
                Worksheet worksheet0 = workbook.Worksheets[0];
                Worksheet worksheet2 = workbook.Worksheets[2];

                //50072
                for (i = 0; i < dtContent.Rows.Count; i++)
                {
                    int rowNum = i + 2;

                    worksheet0.Cells[rowNum, 0].Value = dtContent.Rows[i]["mc_date"].AsString();
                    worksheet0.Cells[rowNum, 1].Value = dtContent.Rows[i]["fut_id"].AsString();
                    worksheet0.Cells[rowNum, 2].Value = dtContent.Rows[i]["acctno"].AsString();
                    worksheet0.Cells[rowNum, 3].Value = dtContent.Rows[i]["param_key"].AsString();
                    worksheet0.Cells[rowNum, 4].SetValue(dtContent.Rows[i]["valid_cnt"]);
                    worksheet0.Cells[rowNum, 5].SetValue(dtContent.Rows[i]["valid_time"]);
                    worksheet0.Cells[rowNum, 6].SetValue(dtContent.Rows[i]["valid_result"]);
                    worksheet0.Cells[rowNum, 7].SetValue(dtContent.Rows[i]["qty"]);
                    worksheet0.Cells[rowNum, 8].SetValue(dtContent.Rows[i]["nqty"]);
                    worksheet0.Cells[rowNum, 9].Value = dtContent.Rows[i]["prod_type"].AsString();
                    worksheet0.Cells[rowNum, 10].SetValue(dtContent.Rows[i]["drank"]);
                }

                //合格報價資料
                for (i = 0; i < dtValidContent.Rows.Count; i++)
                {
                    int rowNum = i + 1;

                    worksheet2.Cells[rowNum, 0].Value = dtValidContent.Rows[i]["mc_date"].AsString();
                    worksheet2.Cells[rowNum, 1].Value = dtValidContent.Rows[i]["fut_id"].AsString();
                    worksheet2.Cells[rowNum, 2].Value = dtValidContent.Rows[i]["acctno"].AsString();
                    worksheet2.Cells[rowNum, 3].Value = dtValidContent.Rows[i]["activity_type"].AsString();
                    worksheet2.Cells[rowNum, 4].Value = dtValidContent.Rows[i]["kind_id"].AsString();
                    worksheet2.Cells[rowNum, 5].SetValue(dtValidContent.Rows[i]["valid_time"]);
                    worksheet2.Cells[rowNum, 6].Value = dtValidContent.Rows[i]["prod_type"].AsString();
                    worksheet2.Cells[rowNum, 7].Value = dtValidContent.Rows[i]["market_close"].AsString();
                }

                #endregion

                #region CSV

                /******************
                *  ETF
                ******************/
                //讀取資料
                string    asSymEtf     = txtFromDate.Text.Replace("/", "").Substring(0, 6);
                string    asEymEtf     = txtToDate.Text.Replace("/", "").Substring(0, 6);
                DataTable dtContentETF = dao50072.ListData_etf(asSymEtf, asEymEtf);
                if (dtContentETF.Rows.Count == 0)
                {
                    MessageDisplay.Info(string.Format("{0} - {1}, {2} - {3},無任何資料!", txtFromDate.Text, txtToDate.Text, rptId, "ETF"));
                }
                else
                {
                    //存CSV

                    string etfFileName = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, "50072_ETF_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss") + ".csv");
                    File.Create(etfFileName).Close();
                    StringBuilder sbETF = new StringBuilder();

                    IEnumerable <string> etfColumnNames = dtContentETF.Columns.Cast <DataColumn>().
                                                          Select(column => column.ColumnName);
                    sbETF.AppendLine(string.Join(",", etfColumnNames));

                    foreach (DataRow row in dtContentETF.Rows)
                    {
                        IEnumerable <string> fields = row.ItemArray.Select(field =>
                                                                           string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\""));
                        sbETF.AppendLine(string.Join(",", fields));
                    }

                    File.WriteAllText(etfFileName, sbETF.ToString());
                }


                /******************
                *  TXF
                ******************/
                //讀取資料
                string    asSymTxf     = txtFromDate.Text.Replace("/", "").Substring(0, 6);
                string    asEymTxf     = txtToDate.Text.Replace("/", "").Substring(0, 6);
                DataTable dtContentTXF = dao50072.ListData_txf(asSymTxf, asEymTxf);
                if (dtContentTXF.Rows.Count == 0)
                {
                    MessageDisplay.Info(string.Format("{0} - {1}, {2} - {3},無任何資料!", txtFromDate.Text, txtToDate.Text, rptId, "TXF"));
                }
                else
                {
                    //存CSV
                    string txfFileName = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, "50072_TXF_" + DateTime.Now.ToString("yyyy.MM.dd-HH.mm.ss") + ".csv");
                    File.Create(txfFileName).Close();

                    StringBuilder sbTXF = new StringBuilder();

                    IEnumerable <string> txfColumnNames = dtContentTXF.Columns.Cast <DataColumn>().
                                                          Select(column => column.ColumnName);
                    sbTXF.AppendLine(string.Join(",", txfColumnNames));

                    foreach (DataRow row in dtContentTXF.Rows)
                    {
                        IEnumerable <string> fields = row.ItemArray.Select(field =>
                                                                           string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\""));
                        sbTXF.AppendLine(string.Join(",", fields));
                    }

                    File.WriteAllText(txfFileName, sbTXF.ToString());
                }


                #endregion

                #region MTX 已廢除

                #endregion

                //若所有Sheet皆無資料時,刪除檔案
                if (dtContent.Rows.Count == 0 && dtValidContent.Rows.Count == 0)
                {
                    try {
                        workbook = null;
                        File.Delete(file);
                        //File.Delete(etfFileName);
                        //File.Delete(txfFileName);
                    }
                    catch (Exception) {
                        //
                    }
                    return(false);
                }

                //Excel存檔
                workbook.SaveDocument(file);
                ShowMsg("轉檔成功");
                return(true);
            }
            catch (Exception ex) {
                MessageDisplay.Error("輸出錯誤");
                throw ex;
            }
            finally {
                this.Cursor = Cursors.Arrow;
                this.Refresh();
                Thread.Sleep(5);
                txtFromDate.Enabled = true;
                txtToDate.Enabled   = true;
            }
        }
Esempio n. 21
0
        protected override ResultStatus Export()
        {
            try {
                this.Cursor = Cursors.WaitCursor;
                this.Refresh();
                Thread.Sleep(5);
                lblProcessing.Visible = true;
                ShowMsg("開始轉檔...");
                string rptId, file, rptName = "";
                date = txtSDate.DateTimeValue.Year + "年" + txtSDate.DateTimeValue.Month + "月" + txtSDate.DateTimeValue.Day + "日";

                #region ue_export_before
                //判斷盤別
                int    rtnInt, seq;
                string rtnStr, grp;
                if (ddlType.Text == "16:15收盤")
                {
                    grp = "1";
                }
                else
                {
                    grp = "2";
                }

                //判斷統計資料轉檔已完成
                for (int f = 1; f <= 2; f++)
                {
                    if (grp == "1")
                    {
                        if (f == 1)
                        {
                            seq = 13;
                        }
                        else
                        {
                            seq = 23;
                        }
                    }
                    else
                    {
                        seq = 17;
                        f   = 2;
                    }
                    //check JSW
                    rtnStr = PbFunc.f_get_jsw_seq(_ProgramID, "E", seq, txtSDate.DateTimeValue, "0");
                    if (rtnStr != "")
                    {
                        DialogResult result = MessageDisplay.Choose(txtSDate.Text +
                                                                    "統計資料未轉入完畢,請確認監視批次「AIG5402」執行完成,再按「是」繼續,若選「否」則會跳過作業不執行。",
                                                                    MessageBoxDefaultButton.Button2, GlobalInfo.QuestionText);

                        if (result == DialogResult.No)
                        {
                            lblProcessing.Visible = false;
                            return(ResultStatus.Fail);
                        }
                    }
                }
                #endregion

                rptId = "30053";

                //複製檔案
                file = wfCopy30053(rptId, grp);
                if (file == "")
                {
                    return(ResultStatus.Fail);
                }

                //開啟檔案
                Workbook workbook = new Workbook();
                workbook.LoadDocument(file);

                //切換Sheet
                Worksheet ws = workbook.Worksheets["期貨"];

                #region 11張報表

                int rowIndex = 1;
                //1.期貨
                if (!wf30053f(grp, rowIndex, ws))
                {
                    return(showEmailMsg(cbxNews.Checked));
                }

                //2.選擇權
                rowIndex = rowIndex + 3;
                ws       = workbook.Worksheets["選擇權"];
                if (!wf30053o(rowIndex, ws))
                {
                    return(showEmailMsg(cbxNews.Checked));
                }

                //3.股票選擇權
                rowIndex = rowIndex + 3;
                ws       = workbook.Worksheets["股票選擇權"];
                if (!wf30053stc(rowIndex, ws))
                {
                    return(showEmailMsg(cbxNews.Checked));
                }

                //4.股票期貨(For工商時報)
                //5.股票期貨(For工商時報)50
                rowIndex = rowIndex + 3;
                ws       = workbook.Worksheets["股票期貨(For工商時報)"];
                if (!wf30053stfNear(ws))
                {
                    return(showEmailMsg(cbxNews.Checked));
                }
                ws = workbook.Worksheets["股票期貨(For工商時報) 50大"];
                if (!wfCtee50(ws))
                {
                    return(showEmailMsg(cbxNews.Checked));
                }

                //6.股票期貨Top10檔(For聯合晚報)
                ws = workbook.Worksheets["股票期貨Top10檔(For經濟日報)"];
                if (!wf30053stfTop10(ws))
                {
                    return(showEmailMsg(cbxNews.Checked));
                }

                //7.ETF期貨Top10檔
                ws = workbook.Worksheets["ETF期貨Top10檔"];
                if (!wf30053etfTop10(ws))
                {
                    return(showEmailMsg(cbxNews.Checked));
                }

                //8.股票選擇權TOP10檔(For聯合晚報)
                ws = workbook.Worksheets["股票選擇權TOP10檔(For聯合晚報)"];
                if (!wf30053stcTop10(ws))
                {
                    return(showEmailMsg(cbxNews.Checked));
                }

                //9.ETF選擇權前20大行情表
                ws = workbook.Worksheets["ETF選擇權TOP20檔"];
                if (!wf30053etcTop20(ws))
                {
                    return(showEmailMsg(cbxNews.Checked));
                }

                //10.美元兌人民幣選擇權前20大行情表
                ws = workbook.Worksheets["美元兌人民幣選擇權(RHO)TOP20檔"];
                if (!wf30053rhoTop20(ws))
                {
                    return(showEmailMsg(cbxNews.Checked));
                }

                //11.小型美元兌人民幣選擇權前20大行情表
                ws = workbook.Worksheets["小型美元兌人民幣選擇權(RTO)TOP20檔"];
                if (!wf30053rtoTop20(ws))
                {
                    return(showEmailMsg(cbxNews.Checked));
                }

                #endregion

                //存檔
                workbook.SaveDocument(file);
                ShowMsg("轉檔完成");

                //email
                rptId = "30053_" + grp;
                if (cbxNews.Checked)
                {
                    DataTable dtTxemail = new TXEMAIL().ListData(rptId, 1);

                    if (dtTxemail.Rows.Count != 0)
                    {
                        string TXEMAIL_SENDER     = dtTxemail.Rows[0]["TXEMAIL_SENDER"].AsString();
                        string TXEMAIL_RECIPIENTS = dtTxemail.Rows[0]["TXEMAIL_RECIPIENTS"].AsString();
                        string TXEMAIL_CC         = dtTxemail.Rows[0]["TXEMAIL_CC"].AsString();
                        string TXEMAIL_TITLE      = dtTxemail.Rows[0]["TXEMAIL_TITLE"].AsString();
                        string TXEMAIL_TEXT       = dtTxemail.Rows[0]["TXEMAIL_TEXT"].AsString();

                        TXEMAIL_TITLE = txtSDate.DateTimeValue.ToString("yyyyMMdd") + TXEMAIL_TITLE;
                        MailHelper.SendEmail(TXEMAIL_SENDER, TXEMAIL_RECIPIENTS, TXEMAIL_CC, TXEMAIL_TITLE, TXEMAIL_TEXT, file);
                    }
                }
            } catch (Exception ex) {
                MessageDisplay.Error("輸出錯誤");
                throw ex;
            } finally {
                this.Cursor = Cursors.Arrow;
                this.Refresh();
                Thread.Sleep(5);
            }
            return(ResultStatus.Success);
        }
Esempio n. 22
0
        protected override ResultStatus Export()
        {
            try {
                #region 輸入&日期檢核
                if (string.Compare(txtStartDate.Text, txtEndDate.Text) > 0)
                {
                    MessageDisplay.Error(CheckDate.Datedif, GlobalInfo.ErrorText);
                    return(ResultStatus.Fail);
                }
                #endregion

                //0.檢查日期跟商品是否有選擇正確
                //todo check
                if (cbxProd.CheckedItemsCount < 1)
                {
                    MessageDisplay.Warning("請勾選商品!", GlobalInfo.WarningText);
                    return(ResultStatus.Fail);
                }
                this.Cursor = Cursors.WaitCursor;
                this.Refresh();
                Thread.Sleep(5);
                labMsg.Visible = true;
                ShowMsg("訊息:資料轉出中........");
                //1.設定一些變數,把邏輯直接寫在該變數屬性內
                List <string> listCode = new List <string>();//多筆,用逗號分隔
                if (cbxProd.Items[0].CheckState == CheckState.Checked)
                {
                    listCode.Add("'1'");
                }
                if (cbxProd.Items[1].CheckState == CheckState.Checked)
                {
                    listCode.Add("'5'");
                }

                DataTable dtTemp  = new SPNT1().ListData();
                decimal   chi_150 = dtTemp.Rows[0][0].AsDecimal();
                decimal   chi_180 = dtTemp.Rows[0][1].AsDecimal();
                decimal   v365    = dtTemp.Rows[0][2].AsDecimal();


                //2.開始轉出資料
                panFilter.Enabled = false;
                panProd.Enabled   = false;
                //labMsg.Visible = true;
                //labMsg.Text = "訊息:資料轉出中........";
                //this.Refresh();

                //2.1 open xls
                string   excelDestinationPath = PbFunc.wf_copy_file(_ProgramID, _ProgramID, "");
                Workbook workbook             = new Workbook();
                workbook.LoadDocument(excelDestinationPath);
                flag = 0;

                //3.write sheet data
                if (cbxProd.Items[0].CheckState == CheckState.Checked ||
                    cbxProd.Items[1].CheckState == CheckState.Checked)
                {
                    //3.1 現貨data
                    wf_40210_3_old(workbook, StartDate, EndDate, listCode, TotalDayCount);

                    //3.2 指數選擇權VSR
                    wf_40210_4(workbook, StartDate, EndDate, listCode, chi_150, chi_180, v365, TotalDayCount);

                    //3.3 期貨data
                    wf_40210_5(workbook, StartDate, EndDate, listCode, TotalDayCount);

                    //3.4 期貨契約PSR
                    wf_40210_6(workbook, EndDate, listCode);

                    //3.5 Delta折耗比率
                    wf_40210_7(workbook, StartDate, EndDate, listCode, TotalDayCount);

                    //3.6 跨商品折抵比率
                    wf_40210_8(workbook, EndDate, listCode);
                }//if (cbxProd.Items[0].CheckState || cbxProd.Items[1].CheckState)

                //3.7 STC VSR計算
                if (cbxProd.Items[2].CheckState == CheckState.Checked)
                {
                    wf_40210_1(workbook, "STC", StartDate, EndDate, chi_150, chi_180, v365, TotalDayCount);
                }

                //3.8 ETC VSR計算
                if (cbxProd.Items[3].CheckState == CheckState.Checked)
                {
                    wf_40210_1(workbook, "ETC", StartDate, EndDate, chi_150, chi_180, v365, TotalDayCount);
                }

                if (flag <= 0)
                {
                    File.Delete(excelDestinationPath);
                    return(ResultStatus.Fail);
                }
                else
                {
                    //存檔
                    workbook.SaveDocument(excelDestinationPath);
                    ShowMsg("轉檔完成");
                    //if (FlagAdmin)
                    //   System.Diagnostics.Process.Start(excelDestinationPath);
                    return(ResultStatus.Success);
                }
            } catch (Exception ex) {
                ShowMsg("轉檔錯誤");
                WriteLog(ex);
            } finally {
                panFilter.Enabled = true;
                panProd.Enabled   = true;
                labMsg.Text       = "";
                labMsg.Visible    = false;
                this.Cursor       = Cursors.Arrow;
                this.Refresh();
                Thread.Sleep(5);
            }
            return(ResultStatus.Fail);
        }
Esempio n. 23
0
        protected override ResultStatus Export()
        {
            dao30020 = new D30020();

            string rptId, file;

            rptId = "30020";

            /******************
            *  複製檔案
            ******************/
            file = PbFunc.wf_copy_file(rptId, rptId);
            if (file == "")
            {
                return(ResultStatus.Fail);
            }

            /******************
            *  開啟檔案
            ******************/
            Workbook workbook = new Workbook();

            workbook.LoadDocument(file);

            #region 30021
            string   rptName;
            string   accType;
            DateTime date, maxDate;
            int      i, j, rowNum, colNum, accRow, accRowTol;
            long     found;

            rptName = "期貨交易累計開戶及交易戶數統計表";
            rptId   = "30021";

            /******************
            *  讀取資料
            ******************/
            DataTable dt30021 = dao30020.d_30021(txtSDate.DateTimeValue, txtEDate.DateTimeValue);
            if (dt30021.Rows.Count == 0)
            {
                MessageDisplay.Info(txtSDate.Text + "~" + txtEDate.Text + "," + rptId + '-' + rptName + ",無任何資料!");
                return(ResultStatus.Fail);
            }
            //ACC_TYPE
            //不撈資料,只要schema
            DataTable dt30021_acc_type = dao30020.d_30021_acc_type(null, null);

            //切換sheet
            Worksheet ws30021 = workbook.Worksheets[0];

            /******************
            *  身份碼總列數
            *  隱藏於A2
            ******************/
            accRowTol = ws30021.Cells[1, 0].Value.AsInt();
            date      = "1900/1/1".AsDateTime();
            maxDate   = dt30021.Rows[0]["AB1_DATE"].AsDateTime();
            rowNum    = 1;
            accRow    = 2;
            colNum    = 2;
            //ACC_TYPE
            for (i = accRow; i < accRowTol; i++)
            {
                dt30021_acc_type.Rows.Add();
                dt30021_acc_type.Rows[dt30021_acc_type.Rows.Count - 1]["AB1_ACC_TYPE"] = ws30021.Cells[i, 0].Value;
            }

            for (j = 0; j < dt30021.Rows.Count; j++)
            {
                rowNum = rowNum + 1;
                /* 換日期時,Row:重頭開始& Col:加1 */
                if (date != dt30021.Rows[j]["AB1_DATE"].AsDateTime())
                {
                    rowNum = 1;
                    accRow = 2;
                    colNum = colNum + 1;
                    date   = dt30021.Rows[j]["AB1_DATE"].AsDateTime();
                    /* 日期 */
                    ws30021.Cells[rowNum, colNum].Value = date;
                    /* 累計開戶數日期 */
                    if (j == 0)
                    {
                        ws30021.Cells[rowNum, 2].Value = dt30021.Rows[j]["AB1_DATE"].AsDateTime();
                    }
                }
                accType = dt30021.Rows[j]["AB1_ACC_TYPE"].AsString();
                //DataTable的Select預設不分大小寫,這邊要將它開啟
                dt30021_acc_type.CaseSensitive = true;
                if (dt30021_acc_type.Select("AB1_ACC_TYPE = '" + accType + "'").Length > 0)
                {
                    rowNum = dt30021_acc_type.Rows.IndexOf(dt30021_acc_type.Select("AB1_ACC_TYPE = '" + accType + "'")[0]);
                }
                else
                {
                    rowNum = -1;
                }
                if (rowNum >= 0)
                {
                    /* 每日 */
                    rowNum = rowNum + 2;
                    ws30021.Cells[rowNum, colNum].SetValue(dt30021.Rows[j]["AB1_COUNT"]);
                    /* 累計開戶數 */
                    if (date == maxDate)
                    {
                        ws30021.Cells[rowNum, 2].SetValue(dt30021.Rows[j]["AB1_ACCU_COUNT"]);
                    }
                }
            }
            #endregion

            #region 30022

            rptName = "期貨交易累計開戶及交易戶數統計表";
            rptId   = "30022";

            //讀取資料
            /* 往前追溯100天 */
            DateTime relDate;
            relDate = PbFunc.relativedate(txtEDate.DateTimeValue, -100);
            DataTable dt30022 = dao30020.d_30022(relDate, txtEDate.DateTimeValue);
            if (dt30022.Rows.Count == 0)
            {
                MessageDisplay.Info(relDate.ToString("yyyy/MM/dd") + "~" + txtEDate.Text + "," + rptId + '-' + rptName + ",無任何資料!");
                return(ResultStatus.Fail);
            }
            //切換sheet
            Worksheet ws30022 = workbook.Worksheets[1];

            rowNum = 0;
            for (j = 0; j < dt30022.Rows.Count; j++)
            {
                rowNum = rowNum + 1;
                ws30022.Cells[rowNum, 0].Value = dt30022.Rows[j]["AB1_DATE"].AsDateTime();
                ws30022.Cells[rowNum, 1].SetValue(dt30022.Rows[j]["AB1_COUNT"]);
                ws30022.Cells[rowNum, 2].SetValue(dt30022.Rows[j]["AB1_ACCU_COUNT"]);
                ws30022.Cells[rowNum, 3].SetValue(dt30022.Rows[j]["AB1_TRADE_COUNT"]);
            }

            #endregion

            //圖表重設
            ChartObject chartObjs     = ws30021.Charts[0];
            string      rcnt          = (dt30022.Rows.Count + 1).AsString();
            ChartData   chartDataArgs = new ChartData();
            ChartData   chartData1    = new ChartData();
            ChartData   chartData2    = new ChartData();
            Range       range1        = ws30022.Range["='30022'!$D$2:$D$" + rcnt];
            Range       range2        = ws30022.Range["='30022'!$C$2:$C$" + rcnt];
            Range       args          = ws30022.Range["='30022'!$A$2:$A$" + rcnt];
            chartData1.RangeValue         = range1;
            chartData2.RangeValue         = range2;
            chartDataArgs.RangeValue      = args;
            chartObjs.Series[0].Arguments = chartDataArgs;
            chartObjs.Series[0].Values    = chartData1;
            chartObjs.Series[1].Arguments = chartDataArgs;
            chartObjs.Series[1].Values    = chartData2;

            workbook.SaveDocument(file);
            return(ResultStatus.Success);
        }
Esempio n. 24
0
        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);
        }
Esempio n. 25
0
        /// <summary>
        /// 讀取前條件檢核
        /// </summary>
        /// <param name="sbrkno">造市者代號起始</param>
        /// <param name="ebrkno">造市者代號迄止</param>
        /// <returns></returns>
        private bool StartRetrieve(string sbrkno = "", string ebrkno = "")
        {
            /*******************
            *  條件值檢核
            *******************/
            _D500Xx.IsCheck = "N";
            /*造市者代號 */
            _D500Xx.Sbrkno = dwSbrkno.EditValue.AsString();
            if (string.IsNullOrEmpty(_D500Xx.Sbrkno))
            {
                _D500Xx.Sbrkno = sbrkno;
            }
            _D500Xx.Ebrkno = dwEbrkno.EditValue.AsString();
            if (string.IsNullOrEmpty(_D500Xx.Ebrkno))
            {
                _D500Xx.Ebrkno = ebrkno;
            }
            if ((string.Compare(dwSbrkno.SelectedText, dwEbrkno.SelectedText) > 0) && !string.IsNullOrEmpty(_D500Xx.Ebrkno))
            {
                PbFunc.messageBox(GlobalInfo.ErrorText, "造市者代號起始不可大於迄止", MessageBoxIcon.Stop);

                dwEbrkno.Focus();
                _D500Xx.IsCheck = "Y";
                return(false);
            }

            /* 商品群組 */
            _D500Xx.ProdCategory = dwProdCt.EditValue.AsString();
            if (string.IsNullOrEmpty(_D500Xx.ProdCategory) || dwProdCt.Enabled == false)
            {
                _D500Xx.ProdCategory = "";
            }
            _D500Xx.ProdCategory = _D500Xx.ProdCategory.AsString() + "%";

            /* 商品 */
            _D500Xx.ProdKindId    = "";
            _D500Xx.ProdKindIdSto = dwProdKdSto.EditValue.AsString();
            if (string.IsNullOrEmpty(_D500Xx.ProdKindIdSto) || dwProdKdSto.Enabled == false)
            {
                _D500Xx.ProdKindIdSto = "";
            }
            _D500Xx.ProdKindIdSto = _D500Xx.ProdKindIdSto.AsString() + "%";

            //DateTime dtDate;
            /* 月報表 */
            if (emStartYM.Visible == true)
            {
                if (!emStartYM.IsDate(emStartYM.Text + "/01", CheckDate.Start))
                {
                    _D500Xx.IsCheck = "Y";
                    return(false);
                }
                _D500Xx.Sdate = emStartYM.Text.Replace("/", "").SubStr(0, 6);
            }
            if (emEndYM.Visible == true)
            {
                if (!emEndYM.IsDate(emEndYM.Text + "/01", CheckDate.End))
                {
                    _D500Xx.IsCheck = "Y";
                    return(false);
                }
                _D500Xx.Edate = emEndYM.Text.Replace("/", "").SubStr(0, 6);
            }

            /*******************
            *  資料類別
            *******************/
            _D500Xx.DataType = "Q";

            /*******************
            *  條件值檢核OK
            *******************/
            _D500Xx.IsCheck = "Y";


            /*******************
            *  //Local Window
            *  條件值檢核
            *  if		is_chk <> 'E'	then
            *     is_chk = 'Y'
            *  end	if
            *
            *  資料類別:
            *  報價:
            *  is_data_type = 'Q'
            *  詢價:
            *  is_data_type = 'R'
            *******************/
            return(true);
        }
Esempio n. 26
0
        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);
        }
Esempio n. 27
0
        /// <summary>
        /// wf_30404 (sheet4 data)
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheetNo">SheetNo.tradeSum</param>
        /// <param name="row"> 1 </param>
        /// <returns></returns>
        protected int wf_30404(Workbook workbook, SheetNo sheetNo, int rowNum, string strKindId)
        {
            string rptName = "個別股票期貨成交量及未平倉量變化表";

            ShowMsg("30404-" + rptName + " 轉檔中...");

            try {
                //1. 取日期
                string sDate = PbFunc.f_get_last_day("AI3", strKindId, txtMon.Text, 2, "yyyyMMdd").ToString("yyyy/MM/dd"); //前月倒數2天交易日
                string eDate = PbFunc.f_get_end_day("AI3", strKindId, txtMon.Text).ToString("yyyy/MM/dd");                 //抓當月最後交易日

                //2. 讀取資料
                DataTable dt30404 = dao30400.Get30404Data(strKindId, sDate, eDate);
                if (dt30404.Rows.Count <= 0)
                {
                    MessageDisplay.Info(String.Format("{0}~{1},30404 - {2},無任何資料!", sDate, eDate, rptName), GlobalInfo.ResultText);
                    return(0);
                } //if (dt30404.Rows.Count <= 0)

                //3. 切換Sheet
                Worksheet ws4 = workbook.Worksheets[(int)sheetNo];

                //4. 處理資料
                int       rowTotal = 35;
                DataTable dtAPDK   = dao30400.GetAdpkData(strKindId); //好像只會有一筆資料
                if (dtAPDK.Rows.Count <= 0)
                {
                    return(0);
                }
                else if (dtAPDK.Rows.Count > 0)
                {
                    ws4.Cells[0, 0].Value = dtAPDK.Rows[0]["apdk_name"].AsString();
                    ws4.Cells[0, 1].Value = txtKindId.Text;
                    ws4.Cells[0, 8].Value = dtAPDK.Rows[0]["apdk_stock_id"].AsString();
                }

                DateTime ymd = DateTime.ParseExact("1900/01/01", "yyyy/MM/dd", null);
                foreach (DataRow dr in dt30404.Rows)
                {
                    DateTime ai3Date    = dr["ai3_date"].AsDateTime();
                    decimal  closePrice = dr["ai3_close_price"].AsDecimal();
                    decimal  mQnty      = dr["ai3_m_qnty"].AsDecimal();
                    decimal  ai3Oi      = dr["ai3_oi"].AsDecimal();
                    decimal  ai3Index   = dr["ai3_index"].AsDecimal();
                    decimal  ai3Amount  = dr["ai3_amount"].AsDecimal();
                    if (ymd != ai3Date)
                    {
                        ymd = ai3Date;
                        rowNum++;
                        ws4.Cells[rowNum, 0].Value = ymd.ToString("MM/dd");
                    }

                    ws4.Cells[rowNum, 1].Value = closePrice;
                    ws4.Cells[rowNum, 3].Value = mQnty;
                    ws4.Cells[rowNum, 4].Value = ai3Oi;
                    ws4.Cells[rowNum, 5].Value = ai3Index;
                    ws4.Cells[rowNum, 7].Value = ai3Amount;
                }//foreach (DataRow dr in dt30404.Rows)

                //5. 刪除空白列
                if (rowTotal > dt30404.Rows.Count)
                {
                    Range ra = ws4.Range[(dt30404.Rows.Count + 3).ToString() + ":" + rowTotal.ToString()];
                    ra.Delete(DeleteMode.EntireRow);
                }

                ws4.Range["A1"].Select();
                ws4.ScrollToRow(0);

                return(1);
            } catch (Exception ex) {
                WriteLog(ex);
                return(0);
            }
        }
Esempio n. 28
0
        protected override ResultStatus Export()
        {
            string lsRptName = ConditionText().Trim();

            StartExport(_ProgramID, lsRptName);

            //開啟檔案
            Workbook workbook = new Workbook();

            try {
                if (gbDetial.EditValue.Equals("rb_detail"))
                {
                    DataTable dt;
                    if (gbMarket.EditValue.Equals("rb_market_1"))
                    {
                        dt = dao50020.List50020dAH(emStartDate.DateTimeValue, emEndDate.DateTimeValue);
                    }
                    else
                    {
                        dt = dao50020.List50020d(emStartDate.DateTimeValue, emEndDate.DateTimeValue);
                    }

                    if (dt.Rows.Count <= 0)
                    {
                        MessageDisplay.Info(MessageDisplay.MSG_NO_DATA);
                        return(ResultStatus.Fail);
                    }

                    dt.Columns["DATA_DATE"].ColumnName = "交易日期";
                    dt.Columns["FCM"].ColumnName       = "期貨商";
                    dt.Columns["PROD"].ColumnName      = "商品";
                    dt.Columns["SEND_TIME"].ColumnName = "詢價送出時間";

                    string csvFilePath = Path.Combine(GlobalInfo.DEFAULT_REPORT_DIRECTORY_PATH, $"50020_detail_{DateTime.Now.ToString("yyyy.MM.dd-hh.mm.ss")}.csv");
                    workbook.CreateNewDocument();
                    workbook.SaveDocument(csvFilePath, DocumentFormat.Csv);
                    workbook.Options.Export.Csv.WritePreamble = true;//不加這段中文會是亂碼
                    workbook.Worksheets[0].Import(dt, true, 0, 0);
                    workbook.SaveDocument(csvFilePath);
                    return(ResultStatus.Success);
                }

                Retrieve();

                //讀取資料
                if (_Data.Rows.Count <= 0)
                {
                    MessageDisplay.Info(MessageDisplay.MSG_NO_DATA);
                    return(ResultStatus.Fail);
                }

                //複製檔案
                _D500Xx.Filename = PbFunc.wf_copy_file(_ProgramID, _ProgramID);
                if (_D500Xx.Filename == "")
                {
                    return(ResultStatus.Fail);
                }
                _D500Xx.LogText = _D500Xx.Filename;

                if (lsRptName == "")
                {
                    lsRptName = "報表條件:" + "(" + DateText() + ")";
                }
                else
                {
                    lsRptName = ConditionText().Trim() + " " + "(" + DateText() + ")";
                }

                workbook.LoadDocument(_D500Xx.Filename);
                //切換Sheet
                Worksheet worksheet = workbook.Worksheets[0];
                worksheet.Cells["E3"].Value = lsRptName;

                worksheet.Import(_Data, false, 4, 0);
                workbook.SaveDocument(_D500Xx.Filename);
            }
            catch (Exception ex) {
                WriteLog(ex);
                WfRunError();
            }
            finally {
                EndExport();
            }

            return(ResultStatus.Success);
        }
Esempio n. 29
0
            protected virtual int ExportSummary(Workbook workbook, int sheetIndex, int rowBegin)
            {
                //1.1 前月倒數2天交易日
                DateTime ldt_sdate = PbFunc.f_get_last_day("AI3", KindId, StartMonth, 2);

                //1.2 抓當月最後交易日
                DateTime ldt_edate = PbFunc.f_get_end_day("AI3", KindId, StartMonth);

                Worksheet ws            = workbook.Worksheets[sheetIndex];
                int       rowIndex      = rowBegin;
                int       emptyRowCount = rowIndex + 1 + 32;

                //1.3 get ai3 data
                DataTable dtTemp = Dao.d_ai3(KindId, ldt_sdate, ldt_edate);

                if (dtTemp.Rows.Count <= 0)
                {
                    //刪除空白列
                    if (rowIndex < emptyRowCount)
                    {
                        string selectBegin = (rowIndex + 2).ToString();
                        string selectEnd   = (emptyRowCount).ToString();
                        string cellRange   = string.Format("A{0}:G{1}", selectBegin, selectEnd);
                        ws.DeleteCells(ws.Range[cellRange], DeleteMode.EntireRow);
                    }
                    return(0);
                }

                //1.4 export to sheet
                DateTime ldt_ymd = DateTime.MinValue;

                foreach (DataRow dr in dtTemp.Rows)
                {
                    DateTime ai3_date = dr["ai3_date"].AsDateTime();
                    if (ldt_ymd != ai3_date)
                    {
                        ldt_ymd = ai3_date;
                        rowIndex++;
                        ws.Cells[rowIndex, 0].Value = ldt_ymd.ToString("MM/dd");
                    }

                    ws.Cells[rowIndex, 1].Value = dr["ai3_close_price"].AsDecimal();
                    ws.Cells[rowIndex, 3].Value = dr["ai3_m_qnty"].AsDecimal();
                    ws.Cells[rowIndex, 4].Value = dr["ai3_oi"].AsDecimal();
                    ws.Cells[rowIndex, 5].Value = dr["ai3_index"].AsDecimal();
                }//foreach (DataRow dr in dtTemp.Rows) {

                //1.5 刪除空白列(注意,沒刪除好會影響到後面sheet的圖表)
                if (rowIndex < emptyRowCount)
                {
                    string selectBegin = (rowIndex + 2).ToString();
                    string selectEnd   = (emptyRowCount).ToString();
                    string cellRange   = string.Format("A{0}:G{1}", selectBegin, selectEnd);
                    ws.DeleteCells(ws.Range[cellRange], DeleteMode.EntireRow);
                    //ken,用DeleteCells還是不行,測試結果似乎xlsx的圖表公式一直固定,不會更新
                }

                //1.6 圖表範圍重選
                ChartObject chartObjs    = workbook.ChartSheets[$"{ws.Name}a"].Chart;
                ChartData   newChartData = new ChartData();

                //黃金 or 台幣黃金期貨總成交量
                newChartData.RangeValue    = ws.Range["D4:D" + (rowIndex + 1).AsString()];
                chartObjs.Series[0].Values = newChartData;
                //黃金 or 台幣黃金期貨總未平倉量
                newChartData.RangeValue    = ws.Range["E4:E" + (rowIndex + 1).AsString()];
                chartObjs.Series[1].Values = newChartData;
                //黃金 or 台幣黃金期貨價格
                newChartData.RangeValue    = ws.Range["B4:B" + (rowIndex + 1).AsString()];
                chartObjs.Series[2].Values = newChartData;
                //現貨價格
                newChartData.RangeValue    = ws.Range["F4:F" + (rowIndex + 1).AsString()];
                chartObjs.Series[3].Values = newChartData;

                return(1);
            }
Esempio n. 30
0
        private bool ManipulateExcel()
        {
            try {
                //檢查查詢日期格式是否正確
                if (txtMonth.Text.SubStr(5, 2) == "00")
                {
                    MessageDisplay.Error("月份輸入錯誤!");
                    return(false);
                }
                txtMonth.Enabled = false;
                this.Cursor      = Cursors.WaitCursor;
                this.Refresh();
                Thread.Sleep(5);
                lblProcessing.Visible = true;
                ShowMsg("開始轉檔...");

                string rptName, rptId, file;
                int    f;
                rptName = "STF報價每月獎勵活動成績得獎名單月報表";
                rptId   = "50070";
                ShowMsg(rptId + "-" + rptName + " 轉檔中...");

                //讀取資料
                daoRMM = new R_MARKET_MONTHLY();
                string    asYM    = txtMonth.Text.Replace("/", "");
                DataTable dt50070 = daoRMM.ListAllByDate(asYM);
                if (dt50070.Rows.Count == 0)
                {
                    MessageDisplay.Info(string.Format("{0},{1},無任何資料!", asYM, rptName));
                    return(false);
                }

                //複製檔案
                file = PbFunc.wf_copy_file(rptId, rptId);
                if (file == "")
                {
                    return(false);
                }

                //切換Sheet
                Workbook workbook = new Workbook();
                workbook.LoadDocument(file);
                Worksheet ws50070 = workbook.Worksheets[0];

                //填資料 Sheet1
                ws50070.Import(dt50070, false, 2, 0);

                //讀取資料
                dt50070 = daoRMM.ListAll2ByDate(asYM);
                if (dt50070.Rows.Count == 0)
                {
                    MessageDisplay.Info(string.Format("{0},{1},無任何資料!", asYM, rptName));
                    return(false);
                }

                //切換Sheet
                ws50070 = workbook.Worksheets[1];

                //填資料 Sheet2
                ws50070.Import(dt50070, false, 0, 0);

                //存檔
                workbook.SaveDocument(file);
                ShowMsg("轉檔成功");
                return(true);
            }
            catch (Exception ex) {
                MessageDisplay.Error("輸出錯誤");
                throw ex;
            }
            finally {
                this.Cursor = Cursors.Arrow;
                this.Refresh();
                Thread.Sleep(5);
                txtMonth.Enabled = true;
            }
        }