예제 #1
0
        public static string getCardNumberFromEDC(string excelRoute, string pwd, string storeCaseNumber, string unKnowCardNumber, string approvalCode, string money)
        {
            string    result_cardNumberFromEDC = "null";
            ExcelTool e1 = new ExcelTool();

            e1.openExcel(excelRoute, pwd);
            e1.selectSheet(DateTime.Now.ToString("MMdd"));
            tmp_stroeCaseNoCol = 8;  //商店代號
            tmp_cardNumberCol  = 7;  //卡號
            tmp_approveCol     = 14; //授權碼
            tmp_moneyCol       = 18; //金額
            int lastRow = e1.getLastRow();

            for (int i = 2; i <= lastRow; i++)
            {
                string excel_unKonwCardNumber;
                string excel_storeCaseNumber = e1.getCellValue(i, tmp_stroeCaseNoCol);
                string excel_cardNumber      = e1.getCellValue(i, tmp_cardNumberCol);
                string excel_approvel        = e1.getCellValue(i, tmp_approveCol);
                string excel_money           = e1.getDoubleCellValue(i, tmp_moneyCol).ToString();
                excel_unKonwCardNumber = excel_cardNumber.Substring(0, 6) + "XXXXXX" + excel_cardNumber.Substring(12, 4);
                if ("822" + storeCaseNumber == excel_storeCaseNumber && unKnowCardNumber == excel_unKonwCardNumber && approvalCode == excel_approvel && (money == excel_money || "-" + money == excel_money))
                {
                    result_cardNumberFromEDC = excel_cardNumber;
                    break;
                }
            }
            e1.closeExcel();
            return(result_cardNumberFromEDC);
        }
예제 #2
0
        // 複製sheet,並將卡號與正負項金額資料撈出
        public static void writeIntoExcel(string excelPwd, bool excelType)  //true-> 開啟全聯異常帳 false -> 全聯異常帳-錢包
        {
            int       lastRow    = 35;
            string    currentDay = DateTime.Now.ToString("MMdd");
            string    year       = (DateTime.Today.Year - 1911).ToString();
            ExcelTool e1         = new ExcelTool();

            if (excelType)
            {
                e1.openExcel(@"C:\CRPADATA\U111705\downloadFile\全聯異常帳" + year + currentDay.Substring(0, 2) + ".xlsx", excelPwd);
            }
            else
            {
                e1.openExcel(@"C:\CRPADATA\U111705\downloadFile\全聯異常帳" + year + currentDay.Substring(0, 2) + "-錢包.xlsx", excelPwd);
            }

            if (tempStructureList.Count > 0)
            {
                e1.selectSheet(currentDay);
                foreach (customers c in tempStructureList)
                {
                    e1.setCellValue("'" + c.storeCaseNo, lastRow, 1);
                    e1.setCellValue(c.storeName, lastRow, 2);
                    e1.setCellValue(c.accountDate, lastRow, 3);
                    e1.setCellValue("'" + c.cardNumber, lastRow, 4);
                    e1.setCellValue(c.tradeCaseNo, lastRow, 5);
                    e1.setCellValue(c.transationDate, lastRow, 6);
                    e1.setCellValue(c.tradeMoney, lastRow, 7);
                    e1.setCellValue("'" + c.approvalCaseNo, lastRow, 8);
                    lastRow = lastRow + 1;
                }
            }
            tempStructureList.Clear();
            e1.closeExcel();
        }
예제 #3
0
        public static bool splitTxt()
        {
            string today   = DateTime.Now.ToString("MMdd");
            int    lastRow = 2;
            bool   flag    = false;

            if (allLinesText.Count == 0)
            {
                Console.WriteLine("TxtFile is empty");
            }
            else
            {
                ExcelTool e1 = new ExcelTool();
                e1.openExcel(@"C:\CRPADATA\U111705\downloadFile\全聯EDC異常.xlsx", "12010499");
                e1.addSheet(today);
                e1.selectSheet(today);
                foreach (string line in allLinesText)
                {
                    e1.setCellValue(line, lastRow, 2);
                    lastRow = lastRow + 1;
                }
                e1.dataSplit(allLinesText.Count);
                for (int i = 2; i <= allLinesText.Count + 1; i++)
                {
                    e1.setCellValue(DateTime.Now.ToString("MM月dd日"), i, 1);
                }
                e1.setExcelTitle();
                e1.closeExcel();
                flag = true;
            }
            return(flag);
        }
예제 #4
0
        public static string getCardNumner(string excelRoute, string pwd, string unKnowCardNumber, string approvalCode, string money)
        {
            string    result_cardNumber = "null";
            bool      haveThisCard      = false;
            ExcelTool e1 = new ExcelTool();

            e1.openExcel(excelRoute, pwd);
            e1.selectSheet("Sheet1");
            tmp_stroeCaseNoCol    = e1.findValueColumn("A1", "S1", "商店代號");
            tmp_stroeNameCol      = e1.findValueColumn("A1", "S1", "商店名稱");
            tmp_accountDateCol    = e1.findValueColumn("A1", "S1", "帳務日期");
            tmp_unKnowCardCol     = e1.findValueColumn("A1", "S1", "卡號前6後4碼");
            tmp_cardNumberCol     = e1.findValueColumn("A1", "S1", "卡號");
            tmp_tradeCaseNoCol    = e1.findValueColumn("A1", "S1", "交易碼");
            tmp_transationDateCol = e1.findValueColumn("A1", "S1", "交易日期");
            tmp_moneyCol          = e1.findValueColumn("A1", "S1", "交易金額");
            tmp_approveCol        = e1.findValueColumn("A1", "S1", "授權碼");

            var tempIndex_List = e1.findConditionIndex(unKnowCardNumber, tmp_unKnowCardCol);

            foreach (int i in tempIndex_List)
            {
                string excel_unKonwCardNumber = e1.getCellValue(i, tmp_unKnowCardCol);
                string excel_approvel         = e1.getCellValue(i, tmp_approveCol);
                string excel_money            = System.Convert.ToString(e1.getDoubleCellValue(i, tmp_moneyCol));
                if (unKnowCardNumber == excel_unKonwCardNumber && approvalCode == excel_approvel && (money == excel_money || "-" + money == excel_money))
                {
                    result_cardNumber = e1.getCellValue(i, tmp_cardNumberCol);
                    haveThisCard      = true;
                    break;
                }
            }

            if (haveThisCard)
            {
                foreach (int i in tempIndex_List)
                {
                    string excel_unKonwCardNumber = e1.getCellValue(i, tmp_unKnowCardCol);
                    string excel_approvel         = e1.getCellValue(i, tmp_approveCol);
                    string excel_money            = System.Convert.ToString(e1.getDoubleCellValue(i, tmp_moneyCol));
                    if (unKnowCardNumber == excel_unKonwCardNumber && (money == excel_money || "-" + money == excel_money))
                    {
                        var c = new customers();
                        c.storeCaseNo    = e1.getCellValue(i, tmp_stroeCaseNoCol);
                        c.storeName      = e1.getCellValue(i, tmp_stroeNameCol);
                        c.accountDate    = e1.getDoubleCellValue(i, tmp_accountDateCol).ToString();
                        c.cardNumber     = e1.getCellValue(i, tmp_cardNumberCol);
                        c.tradeCaseNo    = e1.getCellValue(i, tmp_tradeCaseNoCol);
                        c.transationDate = e1.getCellValue(i, tmp_transationDateCol);
                        c.tradeMoney     = e1.getDoubleCellValue(i, tmp_moneyCol).ToString();
                        c.approvalCaseNo = e1.getCellValue(i, tmp_approveCol);
                        tempStructureList.Add(c);
                    }
                }
            }

            e1.closeExcel();
            return(result_cardNumber);
        }
예제 #5
0
        public static void copyExcelSheet(string excelRoute, string pwd, string sheetName)
        {
            ExcelTool e1 = new ExcelTool();

            e1.openExcel(excelRoute, pwd);
            e1.copySheet(sheetName);
            e1.closeExcel();
        }