• 1

• 20.5k
• 6
• 69
• 155

• 177
• 2
• 7

After code review (http://codereview.stackexchange.com/a/122358/15199) i have fixed the found issues. Could you please review my new refactored application?

Main class (entry point)

 static class MainClass
{
 private static readonly ILog Log = LogManager.GetLogger(typeof(MainClass));
 static void Main(string[] args)
 {
 log4net.Config.XmlConfigurator.Configure();
 Log.Info("this app converts XLS data to export.json");
 var settings = SettingsController.LoadSettings(@"settings.json");
 var result = Converter.ConvertExcelToJson(settings);
 File.WriteAllText(@"export.json", result);
 Log.Info("converting finished.");
 }
}

Converter.cs

 static class Converter
{
 private static GoogleGeocoder GoogleGeocoder { get; set; }
 private static readonly ILog Log = LogManager.GetLogger(typeof(Converter));
 public static string ConvertExcelToJson(SettingsModel settings)
 {
 try
 {
 GoogleGeocoder = new GoogleGeocoder(); // default api key used
 var nameField = GetSourceFieldNumber(settings, "Name");
 var datesField = GetSourceFieldNumber(settings, "Dates");
 var timesField = GetSourceFieldNumber(settings, "Times");
 var zipField = GetSourceFieldNumber(settings, "PostalCode");
 var cityField = GetSourceFieldNumber(settings, "City");
 var streetField = GetSourceFieldNumber(settings, "Street");
 var book = new LinqToExcel.ExcelQueryFactory(settings.FileName);
 var worksheetName = book.GetWorksheetNames().FirstOrDefault();
 if (worksheetName == null)
 throw new Exception("Cannot find any worksheet");
 Log.Info("start converting, please wait...");
 ConsoleSpiner consoleSpiner = new ConsoleSpiner();
 var test = book
 .WorksheetRange(settings.CellToStart, settings.CellToEnd, worksheetName)
 .AsEnumerable()
 .Select(item =>
 {
 try
 {
 return new ExportsModel
 {
 Name = GetFiledValue(item, nameField),
 ActionDates = GetActionDatesArray(GetFiledValue(item, datesField), GetFiledValue(item, timesField)),
 City = GetFiledValue(item, cityField),
 Geocode = GetCoordinates(GetFiledValue(item, zipField), GetFiledValue(item, cityField), GetFiledValue(item, streetField), consoleSpiner),
 Street = GetFiledValue(item, streetField),
 PostalCode = GetFiledValue(item, zipField),
 LastDate = GetLastDate(GetFiledValue(item, datesField))
 };
 }
 catch (Exception ex)
 {
 Log.Error("Cannot convert the row. Don't stop the application. Other rows will be converted.", ex);
 return null;
 }
 })
 .Where(obj => obj != null)
 .ToJSON();
 return test;
 }
 catch (Exception ex)
 {
 Log.Error("Cannot convert data. Please check if all settings are correct", ex);
 return "";
 }
 }
 private static string GetFiledValue(Row item, int nameField)
 {
 return item[nameField].Cast<string>();
 }
 private static int GetSourceFieldNumber(SettingsModel settings, string destinationFieldName)
 {
 return settings.FieldsMapping.Single(n => n.DestinationFieldName.Equals(destinationFieldName)).SourceFieldNumber;
 }
 private static DateTime? GetLastDate(string dates)
 {
 DateTime lastDate;
 if (DateTime.TryParse(dates.Split('|').Select(n => n.Trim()).LastOrDefault(), out lastDate))
 {
 return lastDate.SetEndOfTheDay();
 }
 throw new Exception("The shop will not be added to list. Cannot parse last date field.Value of the field: " + dates);
 }
 private static IEnumerable<ActionDate> GetActionDatesArray(string dates, string times)
 {
 List<string> timesArray;
 List<string> datesArray;
 try
 {
 timesArray = times.Split('|').Select(n => n.Trim()).ToList();
 datesArray = dates.Split('|').Select(n => n.Trim()).ToList();
 }
 catch (Exception ex)
 {
 throw new Exception(string.Format("The shop will not be added to list. Cannot parse date or time field. Values needed to be parsed: dates={0}, times={1}", dates, times), ex);
 }
 for (var i = 0; i < datesArray.Count(); i++)
 {
 DateTime date;
 yield return new ActionDate()
 {
 Label = string.Format("{0} {1} Uhr", datesArray[i], timesArray[i]),
 Date = DateTime.TryParse(datesArray[i], out date) ? date.SetEndOfTheDay() : (DateTime?)null
 };
 }
 }
 private static Geocode GetCoordinates(string zip, string city, string street, ConsoleSpiner consoleSpiner)
 {
 try
 {
 IEnumerable<Address> addresses;
 try
 {
 addresses = GoogleGeocoder.Geocode(String.Format("{0} {1} {2}",zip, city, street)).ToList();
 }
 catch (Exception)
 {
 // try one more time
 Thread.Sleep(2000);
 addresses = GoogleGeocoder.Geocode(String.Format("{0} {1} {2}", zip, city, street)).ToList();
 }
 consoleSpiner.Turn();
 return new Geocode()
 {
 Lat = addresses.First().Coordinates.Latitude,
 Lon = addresses.First().Coordinates.Longitude
 };
 }
 catch (Exception ex)
 {
 throw new Exception(string.Format("The shop will not be added to list. The following data cannot be geocoded: ({0}-{1}-{2})", zip, city, street), ex);
 }
 }
}

SettingsController.cs

 static class SettingsController
{
 private static readonly ILog Log = LogManager.GetLogger(typeof(SettingsController));
 public static SettingsModel LoadSettings(string settingsFileName)
 {
 try
 {
 if (!File.Exists(settingsFileName))
 {
 CreateSettingsFile(settingsFileName);
 }
 var settings = JsonConvert.DeserializeObject<SettingsModel>(File.ReadAllText(settingsFileName));
 Log.Info("reading settings finished");
 return settings;
 }
 catch (Exception ex)
 {
 Log.Error("Cannot open/read settings file. Please check if all settings are correct", ex);
 Environment.Exit(0);
 return null;
 }
 }
 private static void CreateSettingsFile(string settingsFileName)
 {
 Log.Info("settings file was not found...");
 File.WriteAllText(settingsFileName, JsonConvert.SerializeObject(new SettingsModel(true), Formatting.Indented));
 Log.Info("settings file was created, please edit it and start the programm again...");
 Environment.Exit(0);
 }
}

ExtensionMethods.cs

 public static class ExtensionMethods
{
 public static DateTime SetEndOfTheDay(this DateTime dateTime)
 {
 return dateTime.AddHours(23).AddMinutes(59);
 }
}

SettingsModel.cs

 public class SettingsModel
{
 public SettingsModel(bool addDefaultValues=false)
 {
 if (addDefaultValues)
 {
 FieldsMapping = new List<Field>()
 {
 new Field() { DestinationFieldName = "Name",SourceFieldNumber = 6},
 new Field() { DestinationFieldName = "Dates",SourceFieldNumber = 12},
 new Field() { DestinationFieldName = "Times",SourceFieldNumber = 13},
 new Field() { DestinationFieldName = "PostalCode",SourceFieldNumber = 8},
 new Field() { DestinationFieldName = "City",SourceFieldNumber = 9},
 new Field() { DestinationFieldName = "Street",SourceFieldNumber = 7}
 };
 FileName = "source.xls";
 CellToStart = "A4";
 CellToEnd = "N133";
 }
 }
 public string FileName { get; set; }
 public string CellToStart { get; set; }
 public string CellToEnd { get; set; }
 public IEnumerable<Field> FieldsMapping { get; set; }
}
public class Field
{
 public string DestinationFieldName { get; set; }
 public int SourceFieldNumber { get; set; }
}