public List <API.Common.Model.ChargePoint> Process(API.Common.Model.CoreReferenceData coreRefData) { List <ChargePoint> outputList = new List <ChargePoint>(); string importMappingJSON = "{ID:0,LocationTitle:1, AddressLine1:2, AddressLine2:3, Town:4, StateOrProvince:5, Postcode:6, Country:7, Latitude:8, Longitude:9,Addr_ContactTelephone1:10, Addr_ContactTelephone2:11, Addr_ContactEmail:12, Addr_AccessComments:13, Addr_GeneralComments:14, Addr_RelatedURL:15, UsageType:16, NumberOfPoints:17, GeneralComments:18, DateLastConfirmed:19, StatusType:20, DateLastStatusUpdate:21, UsageCost:22, Connection1_Type:23, Connection1_Amps:24, Connection1_Volts:25, Connection1_Level:26, Connection1_Quantity:27, Connection2_Type:28, Connection2_Amps:29, Connection2_Volts:30, Connection2_Level:31, Connection2_Quantity:32, Connection3_Type:33, Connection3_Amps:34, Connection3_Volts:35, Connection3_Level:36, Connection3_Quantity:37}"; if (ImportFormat == ExcelTemplateFormat.FormatV2) { importMappingJSON = "{ID:0,LocationTitle:1, AddressLine1:2, AddressLine2:3, Town:4, StateOrProvince:5, Postcode:6, Country:7, Latitude:8, Longitude:9,Addr_ContactTelephone1:10, Addr_ContactTelephone2:11, Addr_ContactEmail:12, Addr_AccessComments:13, Addr_GeneralComments:14, Addr_RelatedURL:15, UsageType:16, NumberOfPoints:17, GeneralComments:18, DateLastConfirmed:19, StatusType:20, DateLastStatusUpdate:21, UsageCost:22, Operator:23, Connection1_Type:24, Connection1_kW:25, Connection1_Amps:26, Connection1_Volts:27, Connection1_Level:28, Connection1_Quantity:29, Connection2_Type:30, Connection2_kW:31, Connection2_Amps:32, Connection2_Volts:33, Connection2_Level:34, Connection2_Quantity:35, Connection3_Type:36, Connection3_kW:37, Connection3_Amps:38, Connection3_Volts:39, Connection3_Level:40, Connection3_Quantity:41,Connection4_Type:42, Connection4_kW:43, Connection4_Amps:44, Connection4_Volts:45, Connection4_Level:46, Connection4_Quantity:47,Connection5_Type:48, Connection5_kW:49, Connection5_Amps:50, Connection5_Volts:51, Connection5_Level:52, Connection5_Quantity:53,Connection6_Type:54, Connection6_kW:55, Connection6_Amps:56, Connection6_Volts:57, Connection6_Level:58, Connection6_Quantity:58, POI_Types:59}"; } //get import column mappings from JSON format config item into a Dictionary IDictionary <string, JToken> tmpMap = JObject.Parse(importMappingJSON); Dictionary <string, int> lookup = tmpMap.ToDictionary(pair => pair.Key, pair => (int)pair.Value); try { //Open the Excel workbook. //example: http://msdn.microsoft.com/library/dd920313.aspx using (SpreadsheetDocument document = SpreadsheetDocument.Open(this.InputPath, false)) { //References to the workbook and Shared String Table. var workBook = document.WorkbookPart.Workbook; var workSheets = workBook.Descendants <Sheet>(); var sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable; var docProperties = document.PackageProperties; //fetch first sheet in workbook var importSheetID = workSheets.First().Id; var importSheet = (WorksheetPart)document.WorkbookPart.GetPartById(importSheetID); Row headerRow = importSheet.RootElement.Descendants <Row>().First(r => r.RowIndex == 1); List <Cell> headerCells = headerRow.Descendants <Cell>().ToList(); //LINQ query to skip first row with column names. IEnumerable <Row> dataRows = from row in importSheet.RootElement.Descendants <Row>() where row.RowIndex > 1 select row; int sourceRowIndex = 0; foreach (Row row in dataRows) { sourceRowIndex++; ChargePoint import = new ChargePoint(); try { List <Cell> cellList = row.Elements <Cell>().ToList(); var col = new string[headerCells.Count]; for (int i = 0; i < col.Length; i++) { string headerCellref = Regex.Replace(headerCells[i].CellReference.Value, @"[\d-]", string.Empty); //if cell has value, populate array position var cell = cellList.FirstOrDefault(c => c.CellReference.Value == (headerCellref + (sourceRowIndex + 1))); if (cell != null) { col[i] = (cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString ? sharedStrings.ChildElements[int.Parse(cell.CellValue.InnerText)].InnerText : (cell.CellValue != null ? cell.CellValue.InnerText : null)); } else { //empty cell } } if (col.Any(t => t != null)) { //load import row values from value in worksheet row import.DataProvidersReference = col[lookup["ID"]]; import.DataQualityLevel = 3; if (this.DefaultDataProvider != null) { import.DataProvider = DefaultDataProvider; } /*try * { * importRow.DateLastConfirmed = DateTime.FromOADate( * Double.Parse( * textArray[importMappings["DateLastConfirmed"]] * ) * ); //excel date is a double value (from OLE Automation) in days since 1900/1/1 * } */ import.DataProviderID = (int)StandardDataProviders.OpenChargeMapContrib; import.AddressInfo = new AddressInfo(); import.AddressInfo.Title = col[lookup["LocationTitle"]]; import.AddressInfo.AddressLine1 = col[lookup["AddressLine1"]]; import.AddressInfo.AddressLine2 = col[lookup["AddressLine2"]]; import.AddressInfo.Town = col[lookup["Town"]]; import.AddressInfo.StateOrProvince = col[lookup["StateOrProvince"]]; import.AddressInfo.Postcode = col[lookup["Postcode"]]; if (string.IsNullOrEmpty(import.AddressInfo.AddressLine1) && string.IsNullOrEmpty(import.AddressInfo.Postcode)) { import.AddressCleaningRequired = true; } var country = col[lookup["Country"]]; import.AddressInfo.CountryID = coreRefData.Countries.FirstOrDefault(c => c.Title.Equals(country, StringComparison.InvariantCultureIgnoreCase))?.ID; import.AddressInfo.ContactTelephone1 = col[lookup["Addr_ContactTelephone1"]]; import.AddressInfo.ContactTelephone2 = col[lookup["Addr_ContactTelephone2"]]; import.AddressInfo.ContactEmail = col[lookup["Addr_ContactEmail"]]; import.AddressInfo.RelatedURL = col[lookup["Addr_RelatedURL"]]; import.GeneralComments = col[lookup["GeneralComments"]]; if (!String.IsNullOrEmpty(import.AddressInfo.RelatedURL) && !import.AddressInfo.RelatedURL.StartsWith("http")) { import.AddressInfo.RelatedURL = "http://" + import.AddressInfo.RelatedURL; } string countryName = col[lookup["Country"]]; import.AddressInfo.Country = coreRefData.Countries.FirstOrDefault(c => c.Title == countryName); //latitude try { import.AddressInfo.Latitude = double.Parse(col[lookup["Latitude"]]); } catch (Exception exp) { //failed to parse value throw exp; } //longitude try { import.AddressInfo.Longitude = double.Parse(col[lookup["Longitude"]]); } catch (Exception exp) { //failed to parse value throw exp; } //Usage type var usageCol = col[lookup["UsageType"]]; if (!String.IsNullOrWhiteSpace(usageCol)) { var usageType = coreRefData.UsageTypes.FirstOrDefault(u => u.Title == usageCol); if (usageType != null) { import.UsageTypeID = usageType.ID; } else { Log("Unknown usage type: " + usageCol); } } //status type var statusCol = col[lookup["StatusType"]]; if (!String.IsNullOrWhiteSpace(statusCol)) { var statusType = coreRefData.StatusTypes.FirstOrDefault(u => u.Title == statusCol); if (statusType != null) { import.StatusTypeID = statusType.ID; } else { Log("Unknown status type: " + statusCol); } } //number of points if (!String.IsNullOrWhiteSpace(col[lookup["NumberOfPoints"]])) { import.NumberOfPoints = int.Parse(col[lookup["NumberOfPoints"]]); } // Operator var operatorCol = col[lookup["Operator"]]; if (!String.IsNullOrWhiteSpace(operatorCol)) { var operatorInfo = coreRefData.Operators.FirstOrDefault(u => u.Title.StartsWith(operatorCol, StringComparison.InvariantCultureIgnoreCase)); if (operatorInfo != null) { import.OperatorID = operatorInfo.ID; } else { Log("Unknown Operator: " + operatorCol); } } //TODO: parse POI_Types (Parking Lot) //poi type metadata: var poiCol = lookup["POI_Types"]; if (col.Length >= poiCol) { var poiTypeCol = col[poiCol]; if (!String.IsNullOrWhiteSpace(poiTypeCol)) { import.MetadataValues = new List <MetadataValue>(); var vals = poiTypeCol.Split(','); foreach (var p in vals) { if (p == "Parking Lot") { import.MetadataValues.Add(new MetadataValue { MetadataFieldID = (int)StandardMetadataFields.POIType, MetadataFieldOptionID = (int)StandardMetadataFieldOptions.Parking }); } else { Log("Unknown POI type:" + p); } } } } if (import.Connections == null) { import.Connections = new List <ConnectionInfo>(); } //connection info 1 int maxConnections = 6; for (int i = 1; i <= maxConnections; i++) { try { var conn = new ConnectionInfo(); var connectionTypeCol = col[lookup["Connection" + i + "_Type"]]; if (!String.IsNullOrWhiteSpace(connectionTypeCol)) { connectionTypeCol = connectionTypeCol.Trim(); if (connectionTypeCol == "Mennekes") { conn.ConnectionTypeID = (int)StandardConnectionTypes.MennekesType2; conn.CurrentTypeID = (int)StandardCurrentTypes.SinglePhaseAC; } else if (connectionTypeCol == "CCS2") { conn.ConnectionTypeID = (int)StandardConnectionTypes.CCSComboType2; conn.CurrentTypeID = (int)StandardCurrentTypes.DC; } else if (connectionTypeCol == "CEE 7/4 - Schuko - Type F") { conn.ConnectionTypeID = (int)StandardConnectionTypes.Schuko; conn.CurrentTypeID = (int)StandardCurrentTypes.SinglePhaseAC; } else { var connectiontype = coreRefData.ConnectionTypes.FirstOrDefault(c => c.Title == col[lookup["Connection1_Type"]]); if (connectiontype != null) { conn.ConnectionTypeID = connectiontype.ID; } else { Log("Failed to match connection type:" + col[lookup["Connection" + i + "_Type"]]); } } } if (!String.IsNullOrWhiteSpace(col[lookup["Connection" + i + "_kW"]])) { conn.PowerKW = double.Parse(col[lookup["Connection" + i + "_kW"]]); } if (!String.IsNullOrWhiteSpace(col[lookup["Connection" + i + "_Amps"]])) { conn.Amps = int.Parse(col[lookup["Connection" + i + "_Amps"]]); } if (!String.IsNullOrWhiteSpace(col[lookup["Connection" + i + "_Volts"]])) { conn.Voltage = int.Parse(col[lookup["Connection" + i + "_Volts"]]); } if (!String.IsNullOrWhiteSpace(col[lookup["Connection" + i + "_Level"]])) { conn.LevelID = int.Parse(col[lookup["Connection" + i + "_Level"]]); } if (!String.IsNullOrWhiteSpace(col[lookup["Connection" + i + "_Quantity"]])) { conn.Quantity = int.Parse(col[lookup["Connection" + i + "_Quantity"]]); } if (!IsConnectionInfoBlank(conn)) { import.Connections.Add(conn); } } catch { } } } else { //blank row import = null; } } catch (Exception exp) { //exception parsing current row System.Diagnostics.Debug.WriteLine("Excel Import: " + exp.Message); } finally { if (import != null) { outputList.Add(import); } } } } } catch (Exception exp) { //could not read from file throw (exp); } return(outputList); }
public List <API.Common.Model.ChargePoint> Process(API.Common.Model.CoreReferenceData coreRefData) { List <ChargePoint> outputList = new List <ChargePoint>(); string importMappingJSON = "{ID:0,LocationTitle:1, AddressLine1:2, AddressLine2:3, Town:4, StateOrProvince:5, Postcode:6, Country:7, Latitude:8, Longitude:9,Addr_ContactTelephone1:10, Addr_ContactTelephone2:11, Addr_ContactEmail:12, Addr_AccessComments:13, Addr_GeneralComments:14, Addr_RelatedURL:15, UsageType:16, NumberOfPoints:17, GeneralComments:18, DateLastConfirmed:19, StatusType:20, DateLastStatusUpdate:21, UsageCost:22, Connection1_Type:23, Connection1_Amps:24, Connection1_Volts:25, Connection1_Level:26, Connection1_Quantity:27, Connection2_Type:28, Connection2_Amps:29, Connection2_Volts:30, Connection2_Level:31, Connection2_Quantity:32, Connection3_Type:33, Connection3_Amps:34, Connection3_Volts:35, Connection3_Level:36, Connection3_Quantity:37}"; //get import column mappings from JSON format config item into a Dictionary IDictionary <string, JToken> tmpMap = JObject.Parse(importMappingJSON); Dictionary <string, int> importMappings = tmpMap.ToDictionary(pair => pair.Key, pair => (int)pair.Value); try { //Open the Excel workbook. //example: http://msdn.microsoft.com/library/dd920313.aspx using (SpreadsheetDocument document = SpreadsheetDocument.Open(this.InputPath, false)) { //References to the workbook and Shared String Table. var workBook = document.WorkbookPart.Workbook; var workSheets = workBook.Descendants <Sheet>(); var sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable; //fetch first sheet in workbook var importSheetID = workSheets.First().Id; var importSheet = (WorksheetPart)document.WorkbookPart.GetPartById(importSheetID); Row headerRow = importSheet.RootElement.Descendants <Row>().First(r => r.RowIndex == 1); List <Cell> headerCells = headerRow.Descendants <Cell>().ToList(); //LINQ query to skip first row with column names. IEnumerable <Row> dataRows = from row in importSheet.RootElement.Descendants <Row>() where row.RowIndex > 1 select row; int sourceRowIndex = 0; foreach (Row row in dataRows) { sourceRowIndex++; ChargePoint import = new ChargePoint(); try { List <Cell> cellList = row.Elements <Cell>().ToList(); var textArray = new string[headerCells.Count]; for (int i = 0; i < textArray.Length; i++) { string headerCellref = Regex.Replace(headerCells[i].CellReference.Value, @"[\d-]", string.Empty); //if cell has value, populate array position var cell = cellList.FirstOrDefault(c => c.CellReference.Value == (headerCellref + (sourceRowIndex + 1))); if (cell != null) { textArray[i] = (cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString ? sharedStrings.ChildElements[int.Parse(cell.CellValue.InnerText)].InnerText : (cell.CellValue != null ? cell.CellValue.InnerText : null)); } else { //empty cell } } if (textArray.Any(t => t != null)) { //load import row values from value in worksheet row import.DataProvidersReference = textArray[importMappings["ID"]]; import.DataQualityLevel = 3; if (this.DefaultDataProvider != null) { import.DataProvider = DefaultDataProvider; } /*try * { * importRow.InvoiceDate = DateTime.FromOADate( * Double.Parse( * textArray[importMappings["DateLastConfirmed"]] * ) * ); //excel date is a double value (from OLE Automation) in days since 1900/1/1 * } */ import.AddressInfo = new AddressInfo(); import.AddressInfo.Title = textArray[importMappings["LocationTitle"]]; import.AddressInfo.AddressLine1 = textArray[importMappings["AddressLine1"]]; import.AddressInfo.AddressLine2 = textArray[importMappings["AddressLine2"]]; import.AddressInfo.Town = textArray[importMappings["Town"]]; import.AddressInfo.StateOrProvince = textArray[importMappings["StateOrProvince"]]; import.AddressInfo.Postcode = textArray[importMappings["Postcode"]]; string countryName = textArray[importMappings["Country"]]; import.AddressInfo.Country = coreRefData.Countries.FirstOrDefault(c => c.Title == countryName); //latitude try { import.AddressInfo.Latitude = double.Parse(textArray[importMappings["Latitude"]]); } catch (Exception exp) { //failed to parse value throw exp; } //longitude try { import.AddressInfo.Longitude = double.Parse(textArray[importMappings["Longitude"]]); } catch (Exception exp) { //failed to parse value throw exp; } if (import.Connections == null) { import.Connections = new List <ConnectionInfo>(); } //connection info 1 if (textArray[importMappings["Connection1_Type"]] != null) { var conn = new ConnectionInfo(); conn.ConnectionType = coreRefData.ConnectionTypes.FirstOrDefault(c => c.Title == textArray[importMappings["Connection1_Type"]]); import.Connections.Add(conn); } if (textArray[importMappings["Connection2_Type"]] != null) { var conn = new ConnectionInfo(); conn.ConnectionType = coreRefData.ConnectionTypes.FirstOrDefault(c => c.Title == textArray[importMappings["Connection2_Type"]]); import.Connections.Add(conn); } } else { //blank row import = null; } } catch (Exception exp) { //exception parsing current row System.Diagnostics.Debug.WriteLine("Excel Import: " + exp.Message); } finally { if (import != null) { outputList.Add(import); } } } } } catch (Exception exp) { //could not read from file throw (exp); } return(outputList); }