bool LoadAndDelete(string filepath) { proc_count_ = 0; //string filepath = @"d:\gbs_order_20141006.xls"; Microsoft.Office.Interop.Excel.Application ap = null; Workbook wb = null; Worksheet ws = null; HKExcelHelper.GetWorkSheet(filepath, ref ap, ref wb, ref ws); Int32 Columns = ws.UsedRange.Columns.Count; Int32 RowCount = ws.UsedRange.Rows.Count; Range tRange = null; for (Int32 i = 2; i < RowCount; i++) { //if (i > 2000) // break; proc_count_++; //SellData pSellData = new SellData(); SellData pSellData = DataManager.Instance.GetData(); tRange = ws.Cells[i, 5]; pSellData.CouponNumber_ = Convert.ToString(tRange.Value2); pSellData.CouponNumber_ = pSellData.CouponNumber_.Trim(); tRange = ws.Cells[i, 12]; pSellData.UserName_ = Convert.ToString(tRange.Value2); pSellData.UserName_ = pSellData.UserName_.Trim(); tRange = ws.Cells[i, 13]; pSellData.Cost_ = (float)Convert.ToDouble(tRange.Value2); ListSellData_.Add(pSellData.CouponNumber_, pSellData); } // 초기화 wb.Close(false, Type.Missing, Type.Missing); ap.Quit(); Marshal.FinalReleaseComObject(ws); Marshal.FinalReleaseComObject(wb); Marshal.FinalReleaseComObject(ap); ws = null; wb = null; ap = null; return(true); }
protected override bool Internal_ExcelCancel_Parsing(string filepath) { try { Microsoft.Office.Interop.Excel.Application ap = null; Workbook wb = null; Worksheet ws = null; HKExcelHelper.GetWorkSheet(filepath, ref ap, ref wb, ref ws); Range tRange = null; Int32 nCurrentRow = 7; Int32 StateColumn = 2; Int32 CouponColumn = 3; Int32 CouponColumn2 = 4; Int32 CancelCountColumn = 9; while (true) { try { tRange = ws.Cells[nCurrentRow, CouponColumn]; if (tRange == null) { break; } CCancelData pCCancelData = new CCancelData(); pCCancelData.channelOrderCode_ = Convert.ToString(tRange.Value2); if (string.IsNullOrEmpty(pCCancelData.channelOrderCode_) == true) { break; } tRange = ws.Cells[nCurrentRow, StateColumn]; if (tRange == null) { break; } pCCancelData.State_ = Convert.ToString(tRange.Value2); tRange = ws.Cells[nCurrentRow, CouponColumn2]; if (tRange == null) { break; } pCCancelData.channelOrderCode_ = string.Format("{0}_{1}", pCCancelData.channelOrderCode_, Convert.ToString(tRange.Value2)); tRange = ws.Cells[nCurrentRow, CancelCountColumn]; pCCancelData.CancelCount_ = Convert.ToInt32(tRange.Value2); for (int i = 1; i <= pCCancelData.CancelCount_; i++) { CCancelData tempExcelData = new CCancelData(); tempExcelData.channelOrderCode_ = string.Format("{0}_{1}", pCCancelData.channelOrderCode_, i); tempExcelData.CancelCount_ = 1; tempExcelData.State_ = pCCancelData.State_; Excel_Cancel_List_.Add(tempExcelData.channelOrderCode_, tempExcelData); } } catch (System.Exception ex) { NewLogManager2.Instance.Log(string.Format("Internal_ExcelCancel_Parsing 엑셀 파싱 에러 : {0}/{1}", filepath, ex.Message)); nCurrentRow++; continue; } nCurrentRow++; } wb.Close(false, Type.Missing, Type.Missing); ap.Quit(); Marshal.FinalReleaseComObject(ws); Marshal.FinalReleaseComObject(wb); Marshal.FinalReleaseComObject(ap); ws = null; wb = null; ap = null; GC.Collect(); } catch (System.Exception ex) { NewLogManager2.Instance.Log(string.Format("Error protected override bool Internal_ExcelCancel_Parsing - {0}", ex.Message)); return(false); } return(true); }
protected override bool Internal_Excel_Parsing(ChannelGoodInfo pChannelGoodInfo) { try { if (GoodsDownInfo_.ContainsKey(pChannelGoodInfo.Goods_Code_) == false) { NewLogManager2.Instance.Log(string.Format("!! 상품 코드 엑셀이 없습니다. - {0}", pChannelGoodInfo.Goods_Code_)); return(false); } string filepath = GoodsDownInfo_[pChannelGoodInfo.Goods_Code_]; Microsoft.Office.Interop.Excel.Application ap = null; Workbook wb = null; Worksheet ws = null; HKExcelHelper.GetWorkSheet(filepath, ref ap, ref wb, ref ws); Range tRange = null; string tempString = ""; string comparesitename = ""; Int32 nCurrentRow = LQCrawlerInfo_.ExData_Start_; Int32 ExData_Option = LQCrawlerInfo_.ExData_Option_; Int32 ExData_Coupncode = LQCrawlerInfo_.ExData_Coupncode_; Int32 ExData_Buyer = LQCrawlerInfo_.ExData_Buyer_; Int32 ExData_Cancel = LQCrawlerInfo_.ExData_Cancel_; Int32 ExData_Use = LQCrawlerInfo_.ExData_Use_; Int32 ExData_Buyphone = LQCrawlerInfo_.ExData_Buyphone_; Int32 ExData_Price = LQCrawlerInfo_.ExData_Price_; Int32 ExData_BuyDate = LQCrawlerInfo_.ExData_Buydate_; Int32 ExData_BuyCount = LQCrawlerInfo_.ExData_Count_; Int32 ExData_GoodsName = LQCrawlerInfo_.ExData_GoodName_; Int32 ExData_Coupncode2 = 4; // 11번가만 가지고 있는 주문 순번 쿠폰 코드가 고유하지 않아서 이것과 합쳐야만 고유해진다. ProcessStateManager.Instance.NeedParsingCount_ += ws.UsedRange.Rows.Count; while (true) { try { tRange = ws.Cells[nCurrentRow, 1]; comparesitename = Convert.ToString(tRange.Value2); tRange = ws.Cells[nCurrentRow, ExData_Option]; if (tRange == null) { break; } tempString = tRange.Value2; if (tempString == null) { break; } COrderData pExcelData = new COrderData(); pExcelData.channelSeq_ = LQCrawlerInfo_.Channel_Idx_; pExcelData.goodsSeq_ = pChannelGoodInfo.Idx_; pExcelData.ExData_Option_ = tempString; pExcelData.ExData_OptionOriginal_ = tempString; tRange = ws.Cells[nCurrentRow, ExData_GoodsName]; pExcelData.ExData_GoodsName_ = tRange.Value2; pExcelData.ExData_GoodsNick_ = Regex.Replace(pExcelData.ExData_GoodsName_, @"[^a-zA-Z0-9가-힣]", ""); tRange = ws.Cells[nCurrentRow, ExData_Coupncode]; if (tRange == null) { break; } pExcelData.channelOrderCode_ = Convert.ToString(tRange.Value2); if (pExcelData.channelOrderCode_ == null) { break; } pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Replace("'", ""); pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Trim(); // 공백 제거 tRange = ws.Cells[nCurrentRow, ExData_Coupncode2]; if (tRange == null) { break; } pExcelData.channelOrderCode_ = string.Format("{0}_{1}", pExcelData.channelOrderCode_, Convert.ToString(tRange.Value2)); tRange = ws.Cells[nCurrentRow, ExData_Buyer]; pExcelData.orderName_ = Convert.ToString(tRange.Value2); if (pExcelData.orderName_ == null) { pExcelData.orderName_ = ""; } tRange = ws.Cells[nCurrentRow, ExData_Cancel]; pExcelData.ExData_Cancel_ = tRange.Value2; if (pExcelData.ExData_Cancel_ == null) { pExcelData.ExData_Cancel_ = ""; } tRange = ws.Cells[nCurrentRow, ExData_Use]; pExcelData.ExData_Use_ = tRange.Value2; if (pExcelData.ExData_Use_ == null) { pExcelData.ExData_Use_ = ""; } tRange = ws.Cells[nCurrentRow, ExData_Buyphone]; pExcelData.orderPhone_ = Convert.ToString(tRange.Value2); if (pExcelData.orderPhone_ == null) { pExcelData.orderPhone_ = ""; } pExcelData.orderPhone_ = pExcelData.orderPhone_.Replace("'", ""); if (ExData_Price != 0) { tRange = ws.Cells[nCurrentRow, ExData_Price]; if (tRange.Value2 != null) {// 돈에 , 가 있으면 제거하자. tempString = Convert.ToString(tRange.Value2); tempString = tempString.Replace(",", ""); pExcelData.orderSettlePrice_ = Convert.ToInt32(tempString); } } tRange = ws.Cells[nCurrentRow, ExData_BuyDate]; if (LQCrawlerInfo_.Channel_Idx_ == 9 || LQCrawlerInfo_.Channel_Idx_ == 14 || LQCrawlerInfo_.Channel_Idx_ == 15 || LQCrawlerInfo_.Channel_Idx_ == 18) { double temp = Convert.ToDouble(tRange.Value2); DateTime dta = DateTime.FromOADate(temp); pExcelData.BuyDate_ = dta.ToString("u"); pExcelData.BuyDate_ = pExcelData.BuyDate_.Replace("Z", ""); } else { pExcelData.BuyDate_ = Convert.ToString(tRange.Value2); } pExcelData.BuyDate_ = pExcelData.BuyDate_.Replace('/', '-'); if (ExData_BuyCount != 0)// 구매갯수를 따로 뽑아야 하는 채널에서만 { tRange = ws.Cells[nCurrentRow, ExData_BuyCount]; pExcelData.BuyCount_ = Convert.ToInt32(tRange.Value2); } SplitDealAndInsertExcelData(pExcelData, comparesitename); } catch (System.Exception ex) { NewLogManager2.Instance.Log(string.Format("엑셀 파싱 에러 : {0}", ex.Message)); break; //nCurrentRow++; //continue; } ProcessStateManager.Instance.CurParsingCount_++; nCurrentRow++; } wb.Close(false, Type.Missing, Type.Missing); ap.Quit(); Marshal.FinalReleaseComObject(ws); Marshal.FinalReleaseComObject(wb); Marshal.FinalReleaseComObject(ap); ws = null; wb = null; ap = null; GC.Collect(); } catch (System.Exception ex) { NewLogManager2.Instance.Log(string.Format("Error override bool Internal_Excel_Parsing - {0}", ex.Message)); return(false); } return(true); }
protected override bool Internal_Excel_Parsing(ChannelGoodInfo pChannelGoodInfo) { try { if (GoodsDownInfo_.ContainsKey(pChannelGoodInfo.Goods_Code_) == false) { NewLogManager2.Instance.Log(string.Format("!! 상품 코드 엑셀이 없습니다. - {0}", pChannelGoodInfo.Goods_Code_)); return(false); } string filepath = GoodsDownInfo_[pChannelGoodInfo.Goods_Code_]; Microsoft.Office.Interop.Excel.Application ap = null; Workbook wb = null; Worksheet ws = null; HKExcelHelper.GetWorkSheet(filepath, ref ap, ref wb, ref ws); Range tRange = null; string tempString = ""; string comparesitename = ""; Int32 nCurrentRow = LQCrawlerInfo_.ExData_Start_; Int32 ExData_Option = LQCrawlerInfo_.ExData_Option_; Int32 ExData_Coupncode = LQCrawlerInfo_.ExData_Coupncode_; Int32 ExData_Buyer = LQCrawlerInfo_.ExData_Buyer_; Int32 ExData_Cancel = LQCrawlerInfo_.ExData_Cancel_; Int32 ExData_Use = LQCrawlerInfo_.ExData_Use_; Int32 ExData_Buyphone = LQCrawlerInfo_.ExData_Buyphone_; Int32 ExData_Price = LQCrawlerInfo_.ExData_Price_; Int32 ExData_BuyDate = LQCrawlerInfo_.ExData_Buydate_; Int32 ExData_BuyCount = LQCrawlerInfo_.ExData_Count_; Int32 ExData_GoodsName = LQCrawlerInfo_.ExData_GoodName_; if (nCurrentRow > 0) { ProcessStateManager.Instance.NeedParsingCount_ += (ws.UsedRange.Rows.Count - (nCurrentRow - 1)); } while (true) { try { tRange = ws.Cells[nCurrentRow, 1]; comparesitename = Convert.ToString(tRange.Value2); tRange = ws.Cells[nCurrentRow, ExData_Option]; if (tRange == null) { break; } tempString = tRange.Value2; if (tempString == null) { break; } COrderData pExcelData = new COrderData(); pExcelData.channelSeq_ = LQCrawlerInfo_.Channel_Idx_; pExcelData.goodsSeq_ = pChannelGoodInfo.Idx_; pExcelData.ExData_Option_ = tempString; pExcelData.ExData_OptionOriginal_ = tempString; tRange = ws.Cells[nCurrentRow, ExData_GoodsName]; pExcelData.ExData_GoodsName_ = tRange.Value2; pExcelData.goodsCode_ = pChannelGoodInfo.Goods_Code_; tRange = ws.Cells[nCurrentRow, ExData_Coupncode]; if (tRange == null) { break; } pExcelData.channelOrderCode_ = Convert.ToString(tRange.Value2); if (pExcelData.channelOrderCode_ == null) { break; } pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Replace("'", ""); pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Trim(); // 공백 제거 tRange = ws.Cells[nCurrentRow, ExData_Buyer]; pExcelData.orderName_ = Convert.ToString(tRange.Value2); if (pExcelData.orderName_ == null) { pExcelData.orderName_ = ""; } tRange = ws.Cells[nCurrentRow, ExData_Cancel]; pExcelData.ExData_Cancel_ = tRange.Value2; if (pExcelData.ExData_Cancel_ == null) { pExcelData.ExData_Cancel_ = ""; } tRange = ws.Cells[nCurrentRow, ExData_Use]; pExcelData.ExData_Use_ = tRange.Value2; if (pExcelData.ExData_Use_ == null) { pExcelData.ExData_Use_ = ""; } tRange = ws.Cells[nCurrentRow, ExData_Buyphone]; pExcelData.orderPhone_ = Convert.ToString(tRange.Value2); if (pExcelData.orderPhone_ == null) { pExcelData.orderPhone_ = ""; } pExcelData.orderPhone_ = pExcelData.orderPhone_.Replace("'", ""); pExcelData.orderPhone_ = Regex.Replace(pExcelData.orderPhone_, @"^(01[016789]{1}|02|0[3-9]{1}[0-9]{1})-?([0-9]{3,4})-?([0-9]{4})$", @"$1-$2-$3"); if (ExData_Price != 0) { tRange = ws.Cells[nCurrentRow, ExData_Price]; if (tRange.Value2 != null) {// 돈에 , 가 있으면 제거하자. tempString = Convert.ToString(tRange.Value2); tempString = tempString.Replace(",", ""); pExcelData.orderSettlePrice_ = Convert.ToInt32(tempString); } } tRange = ws.Cells[nCurrentRow, ExData_BuyDate]; pExcelData.BuyDate_ = Convert.ToString(tRange.Value2); pExcelData.BuyDate_ = pExcelData.BuyDate_.Substring(0, pExcelData.BuyDate_.Length - 2); if (ExData_BuyCount != 0)// 구매갯수를 따로 뽑아야 하는 채널에서만 { tRange = ws.Cells[nCurrentRow, ExData_BuyCount]; pExcelData.BuyCount_ = Convert.ToInt32(tRange.Value2); } SplitDealAndInsertExcelData(pExcelData, comparesitename); } catch (System.Exception ex) { NewLogManager2.Instance.Log(string.Format("엑셀 파싱 에러 : {0}", ex.Message)); break; //nCurrentRow++; //continue; } ProcessStateManager.Instance.CurParsingCount_++; nCurrentRow++; } wb.Close(false, Type.Missing, Type.Missing); ap.Quit(); Marshal.FinalReleaseComObject(ws); Marshal.FinalReleaseComObject(wb); Marshal.FinalReleaseComObject(ap); ws = null; wb = null; ap = null; GC.Collect(); } catch (System.Exception ex) { NewLogManager2.Instance.Log(string.Format("Error protected override bool Internal_Excel_Parsing - {0}", ex.Message)); return(false); } return(true); }
public override bool LoadExcelAndInsertList(string filepath, Int32 GoodsAttrType, bool bFixedType, string goodsname) { LQStructures.LQCrawlerInfo pCrawlerInfo = CrawlerManager.Instance.GetCrawlerInfo(); Microsoft.Office.Interop.Excel.Application ap = null; Workbook wb = null; Worksheet ws = null; HKExcelHelper.GetWorkSheet(filepath, ref ap, ref wb, ref ws); Range tRange = null; string tempString = ""; string comparesitename = ""; Int32 nCurrentRow = 0; Int32 ExData_Option = 0; Int32 ExData_Coupncode = 0; Int32 ExData_Buyer = 0; Int32 ExData_Cancel = 0; Int32 ExData_Use = 0; Int32 ExData_Buyphone = 0; Int32 ExData_Price = 0; Int32 ExData_BuyDate = 0; Int32 ExData_BuyCount = 0; if (bFixedType == true) {// 레저큐 양식일때는 고정값으로 nCurrentRow = 2; ExData_Option = 4; ExData_Coupncode = 3; ExData_Buyer = 1; ExData_Cancel = 6; ExData_Use = 6; ExData_Buyphone = 2; ExData_Price = 5; ExData_BuyDate = 7; ExData_BuyCount = 8; } else { nCurrentRow = pCrawlerInfo.ExData_Start_; ExData_Option = pCrawlerInfo.ExData_Option_; ExData_Coupncode = pCrawlerInfo.ExData_Coupncode_; ExData_Buyer = pCrawlerInfo.ExData_Buyer_; ExData_Cancel = pCrawlerInfo.ExData_Cancel_; ExData_Use = pCrawlerInfo.ExData_Use_; ExData_Buyphone = pCrawlerInfo.ExData_Buyphone_; ExData_Price = pCrawlerInfo.ExData_Price_; ExData_BuyDate = pCrawlerInfo.ExData_Buydate_; ExData_BuyCount = pCrawlerInfo.ExData_Count_; // 티몬을 위한 변경 if (GoodsAttrType == 1) { nCurrentRow = 3; ExData_Option = 6; ExData_Coupncode = 3; ExData_Buyer = 1; ExData_Cancel = 8; ExData_Use = 8; ExData_Buyphone = 2; ExData_Price = 7; ExData_BuyDate = 9; } } while (true) { try { tRange = ws.Cells[nCurrentRow, 1]; comparesitename = Convert.ToString(tRange.Value2); tRange = ws.Cells[nCurrentRow, ExData_Option]; if (tRange == null) { break; } tempString = tRange.Value2; if (tempString == null) { break; } Int32 tempgoodSeq = -1; tblOrderData pExcelData = new tblOrderData(); pExcelData.channelSeq_ = pCrawlerInfo.Channel_Idx_; pExcelData.authoritySeq_ = pCrawlerInfo.AuthoritySeq_; //pExcelData.goodsCode_ = pGoodInfo.Goods_Code_; pExcelData.goodsSeq_ = tempgoodSeq; pExcelData.ExData_Option_ = tempString; pExcelData.ExData_OptionOriginal_ = tempString; if (string.IsNullOrEmpty(goodsname) == false) { pExcelData.ExData_GoodsName_ = goodsname; } tRange = ws.Cells[nCurrentRow, ExData_Coupncode]; if (tRange == null) { break; } pExcelData.channelOrderCode_ = Convert.ToString(tRange.Value2); if (pExcelData.channelOrderCode_ == null) { break; } pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Replace("'", ""); pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Trim(); // 공백 제거 tRange = ws.Cells[nCurrentRow, ExData_Buyer]; pExcelData.orderName_ = Convert.ToString(tRange.Value2); if (pExcelData.orderName_ == null) { pExcelData.orderName_ = ""; } tRange = ws.Cells[nCurrentRow, ExData_Cancel]; pExcelData.ExData_Cancel_ = tRange.Value2; if (pExcelData.ExData_Cancel_ == null) { pExcelData.ExData_Cancel_ = ""; } tRange = ws.Cells[nCurrentRow, ExData_Use]; pExcelData.ExData_Use_ = tRange.Value2; if (pExcelData.ExData_Use_ == null) { pExcelData.ExData_Use_ = ""; } tRange = ws.Cells[nCurrentRow, ExData_Buyphone]; pExcelData.orderPhone_ = Convert.ToString(tRange.Value2); if (pExcelData.orderPhone_ == null || pExcelData.orderPhone_ == "") { tRange = ws.Cells[nCurrentRow, 15]; pExcelData.orderPhone_ = Convert.ToString(tRange.Value2); } pExcelData.orderPhone_ = pExcelData.orderPhone_.Replace("'", ""); if (ExData_Price != 0) { tRange = ws.Cells[nCurrentRow, ExData_Price]; if (tRange.Value2 != null) {// 돈에 , 가 있으면 제거하자. tempString = Convert.ToString(tRange.Value2); tempString = tempString.Replace(",", ""); pExcelData.orderSettlePrice_ = Convert.ToInt32(tempString); } } tRange = ws.Cells[nCurrentRow, ExData_BuyDate]; if (pCrawlerInfo.Channel_Idx_ == 9 || pCrawlerInfo.Channel_Idx_ == 14 || pCrawlerInfo.Channel_Idx_ == 15 || pCrawlerInfo.Channel_Idx_ == 18) { double temp = Convert.ToDouble(tRange.Value2); DateTime dta = DateTime.FromOADate(temp); pExcelData.BuyDate_ = dta.ToString("u"); pExcelData.BuyDate_ = pExcelData.BuyDate_.Replace("Z", ""); } else if (pCrawlerInfo.Channel_Idx_ == 22 || pCrawlerInfo.Channel_Idx_ == 23) { pExcelData.BuyDate_ = Convert.ToString(tRange.Value2); pExcelData.BuyDate_ = pExcelData.BuyDate_ + " 00:00:00"; } else { pExcelData.BuyDate_ = Convert.ToString(tRange.Value2); } pExcelData.BuyDate_ = pExcelData.BuyDate_.Replace('.', '-'); if (ExData_BuyCount != 0)// 구매갯수를 따로 뽑아야 하는 채널에서만 { tRange = ws.Cells[nCurrentRow, ExData_BuyCount]; pExcelData.BuyCount_ = Convert.ToInt32(tRange.Value2); } SplitDealAndInsertExcelData(pExcelData, comparesitename); } catch (System.Exception ex) { LogManager.Instance.Log(string.Format("엑셀 파싱 에러 : {0}", ex.Message)); nCurrentRow++; continue; } nCurrentRow++; } wb.Close(false, Type.Missing, Type.Missing); ap.Quit(); Marshal.FinalReleaseComObject(ws); Marshal.FinalReleaseComObject(wb); Marshal.FinalReleaseComObject(ap); ws = null; wb = null; ap = null; GC.Collect(); return(true); }
protected override bool Internal_Excel_Parsing(ChannelGoodInfo pChannelGoodInfo) { try { if (GoodsDownInfo_.ContainsKey(pChannelGoodInfo.Goods_Code_) == false) { NewLogManager2.Instance.Log(string.Format("!! 상품 코드 엑셀이 없습니다. - {0}", pChannelGoodInfo.Goods_Code_)); return(false); } string filepath = GoodsDownInfo_[pChannelGoodInfo.Goods_Code_]; Microsoft.Office.Interop.Excel.Application ap = null; Workbook wb = null; Worksheet ws = null; HKExcelHelper.GetWorkSheet(filepath, ref ap, ref wb, ref ws); Range tRange = null; string tempString = ""; Int32 nCurrentRow = LQCrawlerInfo_.ExData_Start_; Int32 ExData_Option = LQCrawlerInfo_.ExData_Option_; Int32 ExData_Coupncode = LQCrawlerInfo_.ExData_Coupncode_; Int32 ExData_Buyer = LQCrawlerInfo_.ExData_Buyer_; Int32 ExData_Cancel = LQCrawlerInfo_.ExData_Cancel_; Int32 ExData_Use = LQCrawlerInfo_.ExData_Use_; Int32 ExData_Buyphone = LQCrawlerInfo_.ExData_Buyphone_; Int32 ExData_Price = LQCrawlerInfo_.ExData_Price_; Int32 ExData_BuyDate = LQCrawlerInfo_.ExData_Buydate_; Int32 ExData_BuyCount = LQCrawlerInfo_.ExData_Count_; Int32 ExData_GoodsName = LQCrawlerInfo_.ExData_GoodName_; if (nCurrentRow > 0) { ProcessStateManager.Instance.NeedParsingCount_ += (ws.UsedRange.Rows.Count - (nCurrentRow - 1)); } string beforeOrderName = ""; // 이전 주문자명 string beforeOrderPhone = ""; // 이전 주문 전화번호 string beforeOrderDate = ""; // 이전 주문 날짜 while (true) { try { tRange = ws.Cells[nCurrentRow, ExData_Option]; if (tRange == null) { break; } tempString = tRange.Value2; if (tempString == null) { break; } COrderData pExcelData = new COrderData(); pExcelData.channelSeq_ = LQCrawlerInfo_.Channel_Idx_; // 채널 시퀀스 pExcelData.goodsSeq_ = -1; // 상품 시퀀스 pExcelData.ExData_Option_ = tempString; // 옵션명 pExcelData.ExData_OptionOriginal_ = tempString; // 원래 옵션명 tRange = ws.Cells[nCurrentRow, ExData_GoodsName]; pExcelData.ExData_GoodsName_ = tRange.Value2; pExcelData.ExData_GoodsNick_ = Regex.Replace(pExcelData.ExData_GoodsName_, @"[^a-zA-Z0-9가-힣]", ""); tRange = ws.Cells[nCurrentRow, ExData_Coupncode]; if (tRange == null) { break; } pExcelData.channelOrderCode_ = Convert.ToString(tRange.Value2); if (pExcelData.channelOrderCode_ == null) { break; } pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Replace("'", ""); pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Trim(); tRange = ws.Cells[nCurrentRow, ExData_Buyer]; pExcelData.orderName_ = Convert.ToString(tRange.Value2); if (pExcelData.orderName_ == null) { pExcelData.orderName_ = beforeOrderName; } else { beforeOrderName = pExcelData.orderName_; } tRange = ws.Cells[nCurrentRow, ExData_Cancel]; pExcelData.ExData_Cancel_ = tRange.Value2; if (pExcelData.ExData_Cancel_ == null) { pExcelData.ExData_Cancel_ = ""; } tRange = ws.Cells[nCurrentRow, ExData_Use]; pExcelData.ExData_Use_ = tRange.Value2; if (pExcelData.ExData_Use_ == null) { pExcelData.ExData_Use_ = ""; } tRange = ws.Cells[nCurrentRow, ExData_Buyphone]; pExcelData.orderPhone_ = Convert.ToString(tRange.Value2); if (pExcelData.orderPhone_ == null) { pExcelData.orderPhone_ = beforeOrderPhone; } else { beforeOrderPhone = pExcelData.orderPhone_; } pExcelData.orderPhone_ = pExcelData.orderPhone_.Replace("'", ""); if (ExData_Price != 0) { tRange = ws.Cells[nCurrentRow, ExData_Price]; if (tRange.Value2 != null) { tempString = Convert.ToString(tRange.Value2); tempString = tempString.Replace(",", ""); pExcelData.orderSettlePrice_ = Convert.ToInt32(tempString); } } tRange = ws.Cells[nCurrentRow, ExData_BuyDate]; if (tRange.Value2 == null) { pExcelData.BuyDate_ = beforeOrderDate; } else { double temp = Convert.ToDouble(tRange.Value2); DateTime dta = DateTime.FromOADate(temp); pExcelData.BuyDate_ = dta.ToString("u"); pExcelData.BuyDate_ = pExcelData.BuyDate_.Replace("Z", ""); beforeOrderDate = pExcelData.BuyDate_; } if (ExData_BuyCount != 0) { tRange = ws.Cells[nCurrentRow, ExData_BuyCount]; pExcelData.BuyCount_ = Convert.ToInt32(tRange.Value2); } SplitDealAndInsertExcelData(pExcelData); } catch (System.Exception ex) { NewLogManager2.Instance.Log(string.Format("엑셀 파싱 에러 : {0}", ex.Message)); break; //nCurrentRow++; //continue; } ProcessStateManager.Instance.CurParsingCount_++; nCurrentRow++; } wb.Close(false, Type.Missing, Type.Missing); ap.Quit(); Marshal.FinalReleaseComObject(ws); Marshal.FinalReleaseComObject(wb); Marshal.FinalReleaseComObject(ap); ws = null; wb = null; ap = null; GC.Collect(); } catch (System.Exception ex) { NewLogManager2.Instance.Log(string.Format("Error public override bool Internal_Excel_Parsing - {0}", ex.Message)); return(false); } return(true); }
protected override bool Internal_ExcelCancel_Parsing(string filepath) { try { Microsoft.Office.Interop.Excel.Application ap = null; Workbook wb = null; Worksheet ws = null; HKExcelHelper.GetWorkSheetFromText(filepath, ref ap, ref wb, ref ws); Range tRange = null; Int32 nCurrentRow = 2; Int32 CouponColumn = 1; Int32 wow = ws.UsedRange.Rows.Count; while (true) { try { tRange = ws.Cells[nCurrentRow, CouponColumn]; if (tRange == null) { break; } CCancelData pCCancelData = new CCancelData(); pCCancelData.channelOrderCode_ = tRange.Value2; pCCancelData.CancelCount_ = 1; if (string.IsNullOrEmpty(pCCancelData.channelOrderCode_) == true) { break; } Excel_Cancel_List_.Add(pCCancelData.channelOrderCode_, pCCancelData); } catch (System.Exception ex) { NewLogManager2.Instance.Log(string.Format("Internal_ExcelCancel_Parsing 엑셀 파싱 에러 : {0}/{1}", filepath, ex.Message)); nCurrentRow++; continue; } nCurrentRow++; } wb.Close(false, Type.Missing, Type.Missing); ap.Quit(); Marshal.FinalReleaseComObject(ws); Marshal.FinalReleaseComObject(wb); Marshal.FinalReleaseComObject(ap); ws = null; wb = null; ap = null; GC.Collect(); } catch (System.Exception ex) { NewLogManager2.Instance.Log(string.Format("Error public override bool Internal_ExcelCancel_Parsing - {0}", ex.Message)); return(false); } return(true); }
public override bool LoadExcelAndInsertList(string filepath, Int32 GoodsAttrType, bool bFixedType, string goodsname) { LQStructures.LQCrawlerInfo pCrawlerInfo = CrawlerManager.Instance.GetCrawlerInfo(); Microsoft.Office.Interop.Excel.Application ap = null; Workbook wb = null; Worksheet ws = null; HKExcelHelper.GetWorkSheet(filepath, ref ap, ref wb, ref ws); Range tRange = null; string tempString = ""; string comparesitename = ""; string beforeOrderCode = ""; Int32 nCurrentRow = 0; Int32 ExData_Option = 0; Int32 ExData_Coupncode = 0; Int32 ExData_Buyer = 0; Int32 ExData_Cancel = 0; Int32 ExData_Use = 0; Int32 ExData_Buyphone = 0; Int32 ExData_Price = 0; Int32 ExData_BuyDate = 0; Int32 ExData_BuyCount = 0; if (bFixedType == true) {// 레저큐 양식일때는 고정값으로 nCurrentRow = 2; ExData_Option = 4; ExData_Coupncode = 3; ExData_Buyer = 1; ExData_Cancel = 6; ExData_Use = 6; ExData_Buyphone = 2; ExData_Price = 5; ExData_BuyDate = 7; ExData_BuyCount = 8; } else { nCurrentRow = pCrawlerInfo.ExData_Start_; ExData_Option = pCrawlerInfo.ExData_Option_; ExData_Coupncode = pCrawlerInfo.ExData_Coupncode_; ExData_Buyer = pCrawlerInfo.ExData_Buyer_; ExData_Cancel = pCrawlerInfo.ExData_Cancel_; ExData_Use = pCrawlerInfo.ExData_Use_; ExData_Buyphone = pCrawlerInfo.ExData_Buyphone_; ExData_Price = pCrawlerInfo.ExData_Price_; ExData_BuyDate = pCrawlerInfo.ExData_Buydate_; ExData_BuyCount = pCrawlerInfo.ExData_Count_; // 티몬을 위한 변경 if (GoodsAttrType == 1) { nCurrentRow = 3; ExData_Option = 6; ExData_Coupncode = 3; ExData_Buyer = 1; ExData_Cancel = 8; ExData_Use = 8; ExData_Buyphone = 2; ExData_Price = 7; ExData_BuyDate = 9; } } while (true) { try { tRange = ws.Cells[nCurrentRow, 1]; comparesitename = Convert.ToString(tRange.Value2); tRange = ws.Cells[nCurrentRow, ExData_Option]; tempString = Convert.ToString(tRange.Value2); if (tempString == null) { break; } string SecondOrderCode = ""; Int32 tempgoodSeq = -1; tblOrderData pExcelData = new tblOrderData(); pExcelData.channelSeq_ = pCrawlerInfo.Channel_Idx_; pExcelData.authoritySeq_ = pCrawlerInfo.AuthoritySeq_; pExcelData.goodsSeq_ = tempgoodSeq; pExcelData.ExData_Option_ = tempString; pExcelData.ExData_OptionOriginal_ = tempString; if (string.IsNullOrEmpty(goodsname) == false) { pExcelData.ExData_GoodsName_ = goodsname; } tRange = ws.Cells[nCurrentRow, ExData_Coupncode]; if (tRange == null) { break; } pExcelData.channelOrderCode_ = Convert.ToString(tRange.Value2); if (pExcelData.channelOrderCode_ == null) { break; } pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Replace("'", ""); pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Trim(); // 공백 제거 pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_ + "_" + comparesitename; //Second CouponCode tRange = ws.Cells[nCurrentRow, 2]; SecondOrderCode = Convert.ToString(tRange.Value2); pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_ + "_" + SecondOrderCode; tRange = ws.Cells[nCurrentRow, ExData_Buyer]; pExcelData.orderName_ = Convert.ToString(tRange.Value2); pExcelData.orderName_ = pExcelData.orderName_.Trim(); Regex regex = new Regex(@"\((?<OrderName>\S+)\)"); Match mc = regex.Match(pExcelData.orderName_); if (mc.Success) { pExcelData.orderName_ = Convert.ToString(mc.Groups["OrderName"].Value); } if (pExcelData.orderName_ == null) { pExcelData.orderName_ = ""; } tRange = ws.Cells[nCurrentRow, ExData_Cancel]; pExcelData.ExData_Cancel_ = tRange.Value2; if (pExcelData.ExData_Cancel_ == null) { pExcelData.ExData_Cancel_ = ""; } tRange = ws.Cells[nCurrentRow, ExData_Use]; pExcelData.ExData_Use_ = tRange.Value2; if (pExcelData.ExData_Use_ == null) { pExcelData.ExData_Use_ = ""; } tRange = ws.Cells[nCurrentRow, ExData_Buyphone]; pExcelData.orderPhone_ = Convert.ToString(tRange.Value2); pExcelData.orderPhone_ = "0" + pExcelData.orderPhone_; if (pExcelData.orderPhone_ == null) { pExcelData.orderPhone_ = ""; } pExcelData.orderPhone_ = Regex.Replace(pExcelData.orderPhone_, @"^(01[016789]{1}|02|0[3-9]{1}[0-9]{1})-?([0-9]{3,4})-?([0-9]{4})$", @"$1-$2-$3"); if (ExData_Price != 0) { tRange = ws.Cells[nCurrentRow, ExData_Price]; if (tRange.Value2 != null) {// 돈에 , 가 있으면 제거하자. tempString = Convert.ToString(tRange.Value2); tempString = tempString.Replace(",", ""); pExcelData.orderSettlePrice_ = Convert.ToInt32(tempString); } } tRange = ws.Cells[nCurrentRow, ExData_BuyDate]; DateTime dta = Convert.ToDateTime(tRange.Value2); pExcelData.BuyDate_ = dta.ToString("u"); pExcelData.BuyDate_ = pExcelData.BuyDate_.Replace("Z", ""); if (ExData_BuyCount != 0)// 구매갯수를 따로 뽑아야 하는 채널에서만 { tRange = ws.Cells[nCurrentRow, ExData_BuyCount]; pExcelData.BuyCount_ = Convert.ToInt32(tRange.Value2); } SplitDealAndInsertExcelData(pExcelData, comparesitename); } catch (System.Exception ex) { LogManager.Instance.Log(string.Format("엑셀 파싱 에러 : {0}", ex.Message)); nCurrentRow++; continue; } nCurrentRow++; } wb.Close(false, Type.Missing, Type.Missing); ap.Quit(); Marshal.FinalReleaseComObject(ws); Marshal.FinalReleaseComObject(wb); Marshal.FinalReleaseComObject(ap); ws = null; wb = null; ap = null; GC.Collect(); return(true); }
protected override bool Internal_Excel_Parsing(ChannelGoodInfo pChannelGoodInfo) { try { if (actionType != "insert") { return(true); } if (GoodsDownInfo_.ContainsKey(pChannelGoodInfo.Goods_Code_) == false) { NewLogManager2.Instance.Log(string.Format("!! 상품 코드 엑셀이 없습니다. - {0}", pChannelGoodInfo.Goods_Code_)); return(false); } DateTime dtNow = BaseChannel.dtnow; DateTime beforeData = dtNow.AddDays(-2); string sData = string.Format("{0}-{1}-{2} {3}:{4}:{5}", beforeData.Year, beforeData.Month, beforeData.Day, "00", "00", "00"); string filepath = GoodsDownInfo_[pChannelGoodInfo.Goods_Code_]; Microsoft.Office.Interop.Excel.Application ap = null; Workbook wb = null; Worksheet ws = null; HKExcelHelper.GetWorkSheet(filepath, ref ap, ref wb, ref ws); Range tRange = null; string tempString = ""; string comparesitename = ""; Int32 nCurrentRow = LQCrawlerInfo_.ExData_Start_; Int32 ExData_Option = LQCrawlerInfo_.ExData_Option_; Int32 ExData_Coupncode = LQCrawlerInfo_.ExData_Coupncode_; Int32 ExData_Buyer = LQCrawlerInfo_.ExData_Buyer_; Int32 ExData_Cancel = LQCrawlerInfo_.ExData_Cancel_; Int32 ExData_Use = LQCrawlerInfo_.ExData_Use_; Int32 ExData_Buyphone = LQCrawlerInfo_.ExData_Buyphone_; Int32 ExData_Price = LQCrawlerInfo_.ExData_Price_; Int32 ExData_BuyDate = LQCrawlerInfo_.ExData_Buydate_; Int32 ExData_BuyCount = LQCrawlerInfo_.ExData_Count_; if (nCurrentRow > 0) { ProcessStateManager.Instance.NeedParsingCount_ += (ws.UsedRange.Rows.Count - (nCurrentRow - 1)); } while (true) { try { tRange = ws.Cells[nCurrentRow, 1]; comparesitename = Convert.ToString(tRange.Value2); tRange = ws.Cells[nCurrentRow, ExData_Option]; if (tRange == null) { break; } tempString = tRange.Value2; if (tempString == null) { break; } COrderData pExcelData = new COrderData(); pExcelData.channelSeq_ = LQCrawlerInfo_.Channel_Idx_; pExcelData.goodsSeq_ = pChannelGoodInfo.Idx_; pExcelData.ExData_Option_ = tempString; pExcelData.ExData_OptionOriginal_ = tempString; pExcelData.ExData_GoodsName_ = pExcelData.ExData_GoodsNick_ = pChannelGoodInfo.GoodsName_; pExcelData.goodsCode_ = pChannelGoodInfo.Goods_Code_; tRange = ws.Cells[nCurrentRow, ExData_BuyDate]; pExcelData.BuyDate_ = Convert.ToString(tRange.Value2); pExcelData.BuyDate_ = pExcelData.BuyDate_.Replace('.', '-'); pExcelData.BuyDate_ = Regex.Replace(pExcelData.BuyDate_, @"^([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})$", @"$1-$2-$3 $4:$5:$6"); DateTime excelDate = DateTime.Parse(pExcelData.BuyDate_); int checkTime = DateTime.Compare(beforeData, excelDate); //비교날짜보다 더 크면 리스트에 추가하지 말고 넘어간다 if (checkTime > 0) { ProcessStateManager.Instance.PassParsingCount_++; nCurrentRow++; continue; } tRange = ws.Cells[nCurrentRow, ExData_Coupncode]; if (tRange == null) { break; } pExcelData.channelOrderCode_ = Convert.ToString(tRange.Value2); if (pExcelData.channelOrderCode_ == null) { break; } pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Replace("'", ""); pExcelData.channelOrderCode_ = pExcelData.channelOrderCode_.Trim(); // 공백 제거 tRange = ws.Cells[nCurrentRow, ExData_Buyer]; pExcelData.orderName_ = Convert.ToString(tRange.Value2); if (pExcelData.orderName_ == null) { pExcelData.orderName_ = ""; } tRange = ws.Cells[nCurrentRow, ExData_Cancel]; pExcelData.ExData_Cancel_ = tRange.Value2; if (pExcelData.ExData_Cancel_ == null) { pExcelData.ExData_Cancel_ = ""; } tRange = ws.Cells[nCurrentRow, ExData_Use]; pExcelData.ExData_Use_ = tRange.Value2; if (pExcelData.ExData_Use_ == null) { pExcelData.ExData_Use_ = ""; } tRange = ws.Cells[nCurrentRow, ExData_Buyphone]; pExcelData.orderPhone_ = Convert.ToString(tRange.Value2); if (pExcelData.orderPhone_ == null) { pExcelData.orderPhone_ = ""; } pExcelData.orderPhone_ = pExcelData.orderPhone_.Replace("'", ""); if (ExData_Price != 0) { tRange = ws.Cells[nCurrentRow, ExData_Price]; if (tRange.Value2 != null && tRange.Value2 != "") {// 돈에 , 가 있으면 제거하자. tempString = Convert.ToString(tRange.Value2); tempString = tempString.Replace(",", ""); pExcelData.orderSettlePrice_ = Convert.ToInt32(tempString); } } if (ExData_BuyCount != 0)// 구매갯수를 따로 뽑아야 하는 채널에서만 { tRange = ws.Cells[nCurrentRow, ExData_BuyCount]; pExcelData.BuyCount_ = Convert.ToInt32(tRange.Value2); } SplitDealAndInsertExcelData(pExcelData, comparesitename); } catch (System.Exception ex) { NewLogManager2.Instance.Log(string.Format("엑셀 파싱 에러 : {0}", ex.Message)); break; //nCurrentRow++; //continue; } ProcessStateManager.Instance.CurParsingCount_++; nCurrentRow++; } wb.Close(false, filepath, Type.Missing); ap.Quit(); Marshal.FinalReleaseComObject(tRange); Marshal.FinalReleaseComObject(ws); Marshal.FinalReleaseComObject(wb); Marshal.FinalReleaseComObject(ap); tRange = null; ws = null; wb = null; ap = null; GC.Collect(); } catch (System.Exception ex) { NewLogManager2.Instance.Log(string.Format("Error protected override bool Internal_Excel_Parsing - {0}", ex.Message)); return(false); } return(true); }