/// <summary> /// return all column names /// </summary> /// <param name="filepath"></param> /// <param name="sheetname"></param> /// <returns></returns> public List <string> GetAllColumnHeaders(string filepath, string sheetname) { List <string> excelHeaders = new List <string>(); using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, false)) { WorkbookPart wbPart = doc.WorkbookPart; // creates an instance of the workbook // int worksheetcount = doc.WorkbookPart.Workbook.Sheets.Count(); //counts the total number of worksheets foreach (var items in doc.WorkbookPart.Workbook.Sheets.ChildElements) { Sheet mysheet = (Sheet)items; if (mysheet.Name == sheetname) { Worksheet Worksheet = ((WorksheetPart)wbPart.GetPartById(mysheet.Id)).Worksheet; SheetData Rows = (SheetData)Worksheet.GetFirstChild <SheetData>(); this.RowCount = Rows.Count(); //SheetData Rows = (SheetData)Worksheet.ChildElements.GetItem(4); if (Rows.ChildElements.Count > 0) { Row currentrow = (Row)Rows.ChildElements.GetItem(0); int columnCount = currentrow.ChildElements.Count; for (int i = 0; i < columnCount; i++) { Cell currentcell = (Cell)currentrow.ChildElements.GetItem(i); string currentcellvalue = string.Empty; if (currentcell.DataType != null) { if (currentcell.DataType == CellValues.SharedString) { int id = -1; if (Int32.TryParse(currentcell.InnerText, out id)) { SharedStringItem item = GetSharedStringItemById(wbPart, id); if (item.Text != null) { //code to take the string value currentcellvalue = item.Text.Text.ToLower(); } else { currentcellvalue = "Empty Header"; } } } } excelHeaders.Add(currentcellvalue); } } } } doc.Close(); } return(excelHeaders); }
public int FindCheckRow(string guid) { string filepath = AppDomain.CurrentDomain.BaseDirectory + @"\" + WebConfigurationManager.AppSettings["root"] + @"\billpay.xlsx"; //open the excel using openxml sdk using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, true)) { WorkbookPart wbPart = doc.WorkbookPart; int worksheetcount = doc.WorkbookPart.Workbook.Sheets.Count(); Sheet mysheet = (Sheet)doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(2); WorksheetPart WorkSheetP = (WorksheetPart)wbPart.GetPartById(mysheet.Id); Worksheet Worksheet = WorkSheetP.Worksheet; int wkschildno = 4; SheetData Rows = (SheetData)Worksheet.ChildElements.GetItem(wkschildno); for (int index = 0; index < Rows.Count(); index++) { Row currentrow = (Row)Rows.ChildElements.GetItem(index); Cell currentcell = (Cell)currentrow.ChildElements.GetItem(1); string currentcellvalue = string.Empty; if (currentcell.DataType != null) { if (currentcell.DataType == CellValues.SharedString) { currentcellvalue = getString(wbPart, currentcell); } } if (currentcellvalue == guid) { return(index + 1); } } return(-1); } }
public ActionResult ImportAssets() { try { HttpPostedFileBase file = Request.Files["file"]; if (file.ContentLength > 0) { string _FileName = Path.GetFileName(file.FileName); string _path = Path.Combine(Server.MapPath("~/ExcelFile"), _FileName); file.SaveAs(_path); using (SpreadsheetDocument doc = SpreadsheetDocument.Open(_path, false)) { WorkbookPart wbPart = doc.WorkbookPart; Sheet mysheet = (Sheet)doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(0); Worksheet Worksheet = ((WorksheetPart)wbPart.GetPartById(mysheet.Id)).Worksheet; //Note: worksheet has 8 children and the first child[1] = sheetviewdimension,....child[4]=sheetdata int wkschildno = 4; SheetData Rows = (SheetData)Worksheet.ChildElements.GetItem(wkschildno); var assetList = new List <ImportAssetViewModel>(); for (int row = 1; row < Rows.Count(); row++) { Row currentrow = (Row)Rows.ChildElements.GetItem(row); if (!string.IsNullOrEmpty(GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart))) { var asset = new ImportAssetViewModel(); asset.AlchemyID = GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart); asset.SSN = GetCellValue((Cell)currentrow.ChildElements.GetItem(1), wbPart); asset.Make = GetCellValue((Cell)currentrow.ChildElements.GetItem(2), wbPart); asset.Model = GetCellValue((Cell)currentrow.ChildElements.GetItem(3), wbPart); asset.Serialno = GetCellValue((Cell)currentrow.ChildElements.GetItem(4), wbPart); asset.Capacity = GetCellValue((Cell)currentrow.ChildElements.GetItem(5), wbPart); asset.Colour = GetCellValue((Cell)currentrow.ChildElements.GetItem(6), wbPart); asset.Grade = GetCellValue((Cell)currentrow.ChildElements.GetItem(7), wbPart); asset.SubGrade = GetCellValue((Cell)currentrow.ChildElements.GetItem(8), wbPart); asset.DatePurchased = GetCellValue((Cell)currentrow.ChildElements.GetItem(9), wbPart); asset.BatteryTest = GetCellValue((Cell)currentrow.ChildElements.GetItem(10), wbPart); asset.IMEI = GetCellValue((Cell)currentrow.ChildElements.GetItem(11), wbPart); assetList.Add(asset); } else { break; } } bool _IsSuccess = ImportAssetService.ImportAssets(assetList); if (_IsSuccess) { TempData["ManualOrder"] = $"Asset Import Processed Successfully!"; } else { TempData["OrderError"] = $"Asset Import Processed Failed!"; } } //Delete the file after reading // System.IO.File.Delete(_path); } else { System.Web.HttpContext.Current.Session["ErrorMessage"] = "File Not choosen Please a file"; } } catch (System.Exception Ex) { System.Web.HttpContext.Current.Session["ErrorMessage"] = Ex.Message; } return(RedirectToAction("Index", "ManageOrders")); }
public static void TelstraFullFeedReprocess(string filepath) { var responsemodel = new FeedResponseModel(); try { using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, false)) { WorkbookPart wbPart = doc.WorkbookPart; Sheet mysheet = (Sheet)doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(0); Worksheet Worksheet = ((WorksheetPart)wbPart.GetPartById(mysheet.Id)).Worksheet; int wkschildno = 4; SheetData Rows = (SheetData)Worksheet.ChildElements.GetItem(wkschildno); int assetrow = 1; int NewFeedCount = 0; int IMEIFeedUpdatedCount = 0; int ServiceNumberFeedUpdatedCount = 0; for (int row = 1; row < Rows.Count(); row++) { Row currentrow = (Row)Rows.ChildElements.GetItem(row); if (!string.IsNullOrEmpty(GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart))) { using (Blancco BlancoContext = new Blancco()) { int assetNo = Convert.ToInt32(GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart)); int serviceNO = Convert.ToInt32(GetCellValue((Cell)currentrow.ChildElements.GetItem(3), wbPart)); string IMEINO = GetCellValue((Cell)currentrow.ChildElements.GetItem(7), wbPart); string TelstraMake = GetCellValue((Cell)currentrow.ChildElements.GetItem(5), wbPart); string TelstraModel = GetCellValue((Cell)currentrow.ChildElements.GetItem(6), wbPart); var ChangedServicNumber = BlancoContext.MCQFeed .Where(x => x.AssetNumber == assetNo && x.FeedVendor == "Telstra" && x.ServiceNumber != serviceNO).FirstOrDefault(); var ChangedIMEI = BlancoContext.MCQFeed.Where(x => x.AssetNumber == assetNo && x.FeedVendor == "Telstra" && x.IMEI != IMEINO).FirstOrDefault(); var _AssetID = BlancoContext.MCQFeed.Where(x => x.AssetNumber == assetNo).FirstOrDefault(); if (ChangedServicNumber != null) { ChangedServicNumber.ServiceNumber = serviceNO; ChangedServicNumber.IMEI = IMEINO.ToString(); ChangedServicNumber.Make = TelstraMake; ChangedServicNumber.Model = TelstraModel; ChangedServicNumber.UpdatedDate = DateTime.Now; BlancoContext.SaveChanges(); Console.WriteLine($"Feed Row {assetrow}, Asset ID {ChangedServicNumber.AssetNumber} IMEI Feed Updated.."); ServiceNumberFeedUpdatedCount++; } else if (ChangedIMEI != null) { ChangedIMEI.ServiceNumber = serviceNO; ChangedIMEI.IMEI = IMEINO.ToString(); ChangedIMEI.Make = TelstraMake; ChangedIMEI.Model = TelstraModel; ChangedIMEI.UpdatedDate = DateTime.Now; BlancoContext.SaveChanges(); Console.WriteLine($"Feed Row {assetrow}, Asset ID {ChangedIMEI.AssetNumber} IMEI Feed Updated.."); IMEIFeedUpdatedCount++; } else if (_AssetID == null && ChangedIMEI == null && ChangedServicNumber == null) { MCQFeed asset = new MCQFeed(); asset.AssetNumber = assetNo; //asset.MCQ_ID = 0; asset.CreatedDate = DateTime.Now; asset.ServiceNumber = serviceNO; asset.Make = TelstraMake; //GetCellValue((Cell)currentrow.ChildElements.GetItem(3), wbPart); asset.Model = TelstraModel; // GetCellValue((Cell)currentrow.ChildElements.GetItem(4), wbPart); asset.IMEI = IMEINO; asset.FeedVendor = "Telstra"; BlancoContext.MCQFeed.Add(asset); BlancoContext.SaveChanges(); Console.WriteLine($"Feed Row {assetrow}, Asset ID {_AssetID.AssetNumber} New Feed Creted.."); NewFeedCount++; } } Console.WriteLine("No Of Assets processing Count... " + assetrow++); } else { break; } } string changefeedresp = $"Total Assets Processed {assetrow}, New Feed Created {NewFeedCount}, Service Number Update Count {ServiceNumberFeedUpdatedCount} and IMEI Update Count {IMEIFeedUpdatedCount}"; responsemodel.FeedFileCount = assetrow; responsemodel.FeedFileName = filepath + changefeedresp; responsemodel.IsFileProcessSuccessful = true; } } catch (Exception ex) { Console.WriteLine(ex.Message); } if (responsemodel.IsFileProcessSuccessful) { SendMailnotification(responsemodel); } }
public static void OptusChangeFeedimport(string filepath) { var responsemodel = new FeedResponseModel(); try { using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, false)) { WorkbookPart wbPart = doc.WorkbookPart; Sheet mysheet = (Sheet)doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(0); Worksheet Worksheet = ((WorksheetPart)wbPart.GetPartById(mysheet.Id)).Worksheet; int wkschildno = 5; SheetData Rows = (SheetData)Worksheet.ChildElements.GetItem(wkschildno); int assetrow = 1; for (int row = 1; row < Rows.Count(); row++) { Row currentrow = (Row)Rows.ChildElements.GetItem(row); if (!string.IsNullOrEmpty(GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart))) { using (Blancco BlancoContext = new Blancco()) { int assetNo = Convert.ToInt32(GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart)); int serviceNO = Convert.ToInt32(GetCellValue((Cell)currentrow.ChildElements.GetItem(2), wbPart)); string IMEINO = GetCellValue((Cell)currentrow.ChildElements.GetItem(5), wbPart); string OptusMake = GetCellValue((Cell)currentrow.ChildElements.GetItem(3), wbPart); string OptusModel = GetCellValue((Cell)currentrow.ChildElements.GetItem(4), wbPart); var ChangedServicNumber = BlancoContext.MCQFeed .Where(x => x.AssetNumber == assetNo && x.ServiceNumber != serviceNO).FirstOrDefault(); var ChangedIMEI = BlancoContext.MCQFeed.Where(x => x.AssetNumber == assetNo && x.IMEI != IMEINO).FirstOrDefault(); if (ChangedServicNumber != null) { ChangedServicNumber.ServiceNumber = serviceNO; ChangedServicNumber.IMEI = IMEINO.ToString(); ChangedServicNumber.Make = OptusMake; ChangedServicNumber.Model = OptusModel; ChangedServicNumber.UpdatedDate = DateTime.Now; BlancoContext.SaveChanges(); } else { if (ChangedIMEI != null) { ChangedIMEI.ServiceNumber = serviceNO; ChangedIMEI.IMEI = IMEINO.ToString(); ChangedIMEI.Make = OptusMake; ChangedIMEI.Model = OptusModel; ChangedIMEI.UpdatedDate = DateTime.Now; BlancoContext.SaveChanges(); } } } Console.WriteLine("No Of Assets processing Count... " + assetrow++); } else { break; } } responsemodel.FeedFileCount = assetrow; responsemodel.FeedFileName = filepath; responsemodel.IsFileProcessSuccessful = true; } } catch (Exception ex) { Console.WriteLine(ex.Message); } if (responsemodel.IsFileProcessSuccessful) { SendMailnotification(responsemodel); } }
public static void OptusNewFeedimport(string filepath) { var responsemodel = new FeedResponseModel(); try { using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, false)) { //create the object for workbook part WorkbookPart wbPart = doc.WorkbookPart; //statement to get the sheet object Sheet mysheet = (Sheet)doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(0); //statement to get the worksheet object by using the sheet id Worksheet Worksheet = ((WorksheetPart)wbPart.GetPartById(mysheet.Id)).Worksheet; //Note: worksheet has 8 children and the first child[1] = sheetviewdimension,....child[4]=sheetdata int wkschildno = 4; //statement to get the sheetdata which contains the rows and cell in table SheetData Rows = (SheetData)Worksheet.ChildElements.GetItem(wkschildno); //List<MCQ_MobileAssetsFeed> assetList = new List<MCQ_MobileAssetsFeed>(); int assetrow = 1; for (int row = 1; row < Rows.Count(); row++) { //getting the row as per the specified index of getitem method Row currentrow = (Row)Rows.ChildElements.GetItem(row); if (!string.IsNullOrEmpty(GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart))) { //using (JMSEntities jms = new JMSEntities()) using (Blancco BlancoContext = new Blancco()) { int assetNo = Convert.ToInt32(GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart)); int serviceNO = Convert.ToInt32(GetCellValue((Cell)currentrow.ChildElements.GetItem(2), wbPart)); string IMEINO = GetCellValue((Cell)currentrow.ChildElements.GetItem(5), wbPart); var assetDB = BlancoContext.MCQFeed .Where(x => x.AssetNumber == assetNo && x.ServiceNumber == serviceNO && x.IMEI == IMEINO).FirstOrDefault(); if (assetDB == null) { MCQFeed asset = new MCQFeed(); asset.AssetNumber = assetNo; asset.MCQ_ID = 0; asset.CreatedDate = DateTime.Now; asset.ServiceNumber = serviceNO; asset.Make = GetCellValue((Cell)currentrow.ChildElements.GetItem(3), wbPart); asset.Model = GetCellValue((Cell)currentrow.ChildElements.GetItem(4), wbPart); asset.IMEI = IMEINO; BlancoContext.MCQFeed.Add(asset); BlancoContext.SaveChanges(); } } Console.WriteLine("No Of Assets processing Count... " + assetrow++); } else { break; } } responsemodel.FeedFileCount = assetrow; responsemodel.FeedFileName = filepath; responsemodel.IsFileProcessSuccessful = true; } } catch (Exception ex) { Console.WriteLine(ex.Message); } if (responsemodel.IsFileProcessSuccessful) { SendMailnotification(responsemodel); } }
public static void OptusFullFeedReprocess(string filepath) { try { using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, false)) { WorkbookPart wbPart = doc.WorkbookPart; Sheet mysheet = (Sheet)doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(0); Worksheet Worksheet = ((WorksheetPart)wbPart.GetPartById(mysheet.Id)).Worksheet; //Note: worksheet has 8 children and the first child[1] = sheetviewdimension,....child[4]=sheetdata int wkschildno = 4; SheetData Rows = (SheetData)Worksheet.ChildElements.GetItem(wkschildno); int assetrow = 1; int NewFeedCount = 0; int IMEIFeedUpdatedCount = 0; int ServiceNumberFeedUpdatedCount = 0; for (int row = 1; row < Rows.Count(); row++) { //getting the row as per the specified index of getitem method Row currentrow = (Row)Rows.ChildElements.GetItem(row); if (!string.IsNullOrEmpty(GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart))) { using (Blancco BlancoContext = new Blancco()) { int assetNo = Convert.ToInt32(GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart)); int serviceNO = Convert.ToInt32(GetCellValue((Cell)currentrow.ChildElements.GetItem(2), wbPart)); string IMEINO = GetCellValue((Cell)currentrow.ChildElements.GetItem(5), wbPart); string OptusMake = GetCellValue((Cell)currentrow.ChildElements.GetItem(3), wbPart); string OptusModel = GetCellValue((Cell)currentrow.ChildElements.GetItem(4), wbPart); var ChangedServicNumber = BlancoContext.MCQFeed .Where(x => x.AssetNumber == assetNo && x.FeedVendor == "Optus" && x.ServiceNumber != serviceNO).FirstOrDefault(); var ChangedIMEI = BlancoContext.MCQFeed.Where(x => x.AssetNumber == assetNo && x.FeedVendor == "Optus" && x.IMEI != IMEINO).FirstOrDefault(); var _AssetID = BlancoContext.MCQFeed.Where(x => x.AssetNumber == assetNo).FirstOrDefault(); if (ChangedServicNumber != null) { ChangedServicNumber.ServiceNumber = serviceNO; ChangedServicNumber.IMEI = IMEINO.ToString(); ChangedServicNumber.Make = OptusMake; ChangedServicNumber.Model = OptusModel; ChangedServicNumber.UpdatedDate = DateTime.Now; BlancoContext.SaveChanges(); Console.WriteLine($"Feed Row {assetrow}, Asset ID {ChangedServicNumber.AssetNumber} IMEI Feed Updated.."); ServiceNumberFeedUpdatedCount++; } else if (ChangedIMEI != null) { ChangedIMEI.ServiceNumber = serviceNO; ChangedIMEI.IMEI = IMEINO.ToString(); ChangedIMEI.Make = OptusMake; ChangedIMEI.Model = OptusModel; ChangedIMEI.UpdatedDate = DateTime.Now; BlancoContext.SaveChanges(); Console.WriteLine($"Feed Row {assetrow}, Asset ID {ChangedIMEI.AssetNumber} IMEI Feed Updated.."); IMEIFeedUpdatedCount++; } else if (_AssetID == null && ChangedIMEI == null && ChangedServicNumber == null) { MCQFeed asset = new MCQFeed(); asset.AssetNumber = assetNo; asset.MCQ_ID = 0; asset.CreatedDate = DateTime.Now; asset.ServiceNumber = serviceNO; asset.Make = GetCellValue((Cell)currentrow.ChildElements.GetItem(3), wbPart); asset.Model = GetCellValue((Cell)currentrow.ChildElements.GetItem(4), wbPart); asset.IMEI = IMEINO; asset.FeedVendor = "Optus"; BlancoContext.MCQFeed.Add(asset); BlancoContext.SaveChanges(); Console.WriteLine($"Feed Row {assetrow}, Asset ID {_AssetID.AssetNumber} New Feed Creted.."); NewFeedCount++; } } Console.WriteLine("No Of Assets processing Count... " + assetrow++); } else { break; } } Console.WriteLine($"Total Assets Processed {assetrow}, New Feed Created {NewFeedCount}, Service Number Update Count {ServiceNumberFeedUpdatedCount} and IMEI Update Count {IMEIFeedUpdatedCount} "); Console.ReadKey(); } } catch (Exception ex) { Console.WriteLine(ex.Message); } }
public static void OptusChangeFeedimport(string filepath) { try { using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filepath, false)) { //create the object for workbook part WorkbookPart wbPart = doc.WorkbookPart; //statement to get the sheet object Sheet mysheet = (Sheet)doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(0); //statement to get the worksheet object by using the sheet id Worksheet Worksheet = ((WorksheetPart)wbPart.GetPartById(mysheet.Id)).Worksheet; //Note: worksheet has 8 children and the first child[1] = sheetviewdimension,....child[4]=sheetdata int wkschildno = 5; //statement to get the sheetdata which contains the rows and cell in table SheetData Rows = (SheetData)Worksheet.ChildElements.GetItem(wkschildno); //List<MCQ_MobileAssetsFeed> assetList = new List<MCQ_MobileAssetsFeed>(); int assetrow = 1; for (int row = 1; row < Rows.Count(); row++) { //getting the row as per the specified index of getitem method Row currentrow = (Row)Rows.ChildElements.GetItem(row); if (!string.IsNullOrEmpty(GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart))) { //using (JMSEntities jms = new JMSEntities()) using (Blancco BlancoContext = new Blancco()) { int assetNo = Convert.ToInt32(GetCellValue((Cell)currentrow.ChildElements.GetItem(0), wbPart)); int serviceNO = Convert.ToInt32(GetCellValue((Cell)currentrow.ChildElements.GetItem(2), wbPart)); string IMEINO = GetCellValue((Cell)currentrow.ChildElements.GetItem(5), wbPart); string OptusMake = GetCellValue((Cell)currentrow.ChildElements.GetItem(3), wbPart); string OptusModel = GetCellValue((Cell)currentrow.ChildElements.GetItem(4), wbPart); var ChangedServicNumber = BlancoContext.MCQFeed .Where(x => x.AssetNumber == assetNo && x.ServiceNumber != serviceNO).FirstOrDefault(); var ChangedIMEI = BlancoContext.MCQFeed.Where(x => x.AssetNumber == assetNo && x.IMEI != IMEINO).FirstOrDefault(); if (ChangedServicNumber != null) { ChangedServicNumber.ServiceNumber = serviceNO; ChangedServicNumber.IMEI = IMEINO.ToString(); ChangedServicNumber.Make = OptusMake; ChangedServicNumber.Model = OptusModel; ChangedServicNumber.UpdatedDate = DateTime.Now; BlancoContext.SaveChanges(); } else { if (ChangedIMEI != null) { ChangedIMEI.ServiceNumber = serviceNO; ChangedIMEI.IMEI = IMEINO.ToString(); ChangedIMEI.Make = OptusMake; ChangedIMEI.Model = OptusModel; ChangedIMEI.UpdatedDate = DateTime.Now; BlancoContext.SaveChanges(); } } } Console.WriteLine("No Of Assets processing Count... " + assetrow++); } else { break; } } } } catch (Exception ex) { Console.WriteLine(ex.Message); } }