After first code review 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; }
}
