public ParameterEditorViewModel(WaterQualityParameter parameter)
{
Parameter = parameter.Clone();
AddYearCommand = new RelayCommand(AddYear);
AddTimestepCommand = new RelayCommand(AddTimestep);
RemoveTimestepCommand = new RelayCommand(RemoveTimestep);
AddMetricCommand = new RelayCommand(AddMetric);
RemoveMetricCommand = new RelayCommand(RemoveMetric);
PasteCommand = new RelayCommand(Paste);
ApplyMetricsCommand = new RelayCommand(ApplyMetrics);
Timestep = new TimeseriesDatum();
Metric = new ObjectiveMetricSingleValue();
SelectedYear = Model.Attributes.AssessmentYear;
if (Parameter.Objective.Function == null)
{
SelectedObjective = _objectives.Keys.First();
}
else
{
foreach (var objective in _objectives)
{
if (Parameter.Objective.Function.GetType() == objective.Value)
{
_selectedObjective = objective.Key;
}
}
}
Parameter.Objective.Metrics.CollectionChanged +=
(sender, args) => RaisePropertyChanged(nameof(CanChangeObjective));
}
private void Type2Special(ExcelPackage excel, WaterQualityIndicator wq)
{
var worksheet = excel.Workbook.Worksheets[1];
for (var row = 2; row <= worksheet.Dimension.End.Row; row++)
{
_errorRow = row;
var comment = new StringBuilder();
var gaugeName = String.Empty;
var year = 0;
var month = String.Empty;
var parameters = new List <(string name, string unit, double value)>();
for (var column = 1; column <= worksheet.Dimension.End.Column; column++)
{
_errorColumn = column;
switch (worksheet.Cells[1, column].Text.ToLowerInvariant())
{
case "name":
comment.AppendLine($"Name: {worksheet.Cells[row, column].Text}");
gaugeName = worksheet.Cells[row, column].Text;
break;
case "water body":
comment.AppendLine($"Water body: {worksheet.Cells[row, column].Text}");
break;
case "river":
comment.AppendLine($"River: {worksheet.Cells[row, column].Text}");
break;
case "year":
int.TryParse(worksheet.Cells[row, column].Text, NumberStyles.Any,
CultureInfo.InvariantCulture, out year);
break;
case "month":
month = worksheet.Cells[row, column].Text;
break;
default:
string n;
var u = String.Empty;
if (String.IsNullOrWhiteSpace(worksheet.Cells[row, column].Text) ||
worksheet.Cells[row, column].Text.ToLowerInvariant().Contains("na"))
{
break;
}
if (!Double.TryParse(worksheet.Cells[row, column].Text, NumberStyles.Any, CultureInfo.InvariantCulture, out var v))
{
var sv = worksheet.Cells[row, column].Text;
if (String.IsNullOrWhiteSpace(sv) || !sv.Contains("x"))
{
break;
}
if (sv.StartsWith("<"))
{
v = 0;
}
else
{
var ssv = sv.Split('x');
if (ssv.Length != 2)
{
break;
}
if (!Double.TryParse(ssv[1], NumberStyles.Any, CultureInfo.InvariantCulture, out var mult))
{
break;
}
if (!Double.TryParse(ssv[0], NumberStyles.Any, CultureInfo.InvariantCulture, out var b))
{
break;
}
v = b * mult;
}
}
var s = worksheet.Cells[1, column].Text?.Split('_');
if (s.Length == 1)
{
n = worksheet.Cells[1, column].Text;
}
else if (s.Length == 2)
{
n = s[0];
u = s[1];
}
else
{
break;
}
parameters.Add((n, u, v));
break;
}
}
if (!_monthNames.ContainsKey(month.ToLowerInvariant()))
{
continue;
}
var date = new DateTime(year, _monthNames[month.ToLowerInvariant()], 1);
var gauge = wq.Gauges.FirstOrDefault(x => x.Name == gaugeName);
if (gauge == null)
{
gauge = new Gauge {
Name = gaugeName
};
wq.Gauges.Add(gauge);
}
gauge.Notes = comment.ToString();
foreach (var(name, unit, value) in parameters)
{
var p = gauge.Parameters.FirstOrDefault(x => x.Name == name);
if (p == null)
{
p = new WaterQualityParameter {
Name = name, Units = unit
};
gauge.Parameters.Add(p);
}
p.Data.Add(new TimeseriesDatum {
Time = date, Value = value
});
}
}
}
private void ImportFromExcel()
{
var dialog = new OpenFileDialog
{
Title = "Open Water Quality Excel File",
Filter = "Microsoft Excel (*.xlsx)|*.xlsx",
DefaultExt = ".xlsx"
};
if (dialog.ShowDialog() != true)
{
return;
}
var filename = dialog.FileName;
try
{
using (new WaitCursor())
{
using (var excel = new ExcelPackage(new FileInfo(filename)))
{
if (excel.Workbook.Worksheets.Count == 0)
{
MessageBox.Show("Import error: no worksheets.");
return;
}
var wq = Indicator;
// can make this more complicated, ask user and so forth
if (excel.Workbook.Worksheets.Count == 1 &&
wq.Gauges.FirstOrDefault(x => x.Name == excel.Workbook.Worksheets[1].Name) == null)
{
if (excel.Workbook.Worksheets[1].Cells[1, 1].Text.Contains("COUNTRY"))
{
Type1Special(excel, wq);
}
else
{
Type2Special(excel, wq);
}
MessageBox.Show($"Imported Water Quality from {filename}.");
return;
}
foreach (var worksheet in excel.Workbook.Worksheets)
{
var gauge = wq.Gauges.FirstOrDefault(x => x.Name == worksheet.Name);
if (gauge == null)
{
gauge = new Gauge {
Name = worksheet.Name
};
wq.Gauges.Add(gauge);
}
const int baseRow = 3;
var dates = new List <DateTime?>();
for (var row = baseRow; row <= worksheet.Dimension.End.Row; row++)
{
_errorRow = row;
_errorColumn = 1;
switch (worksheet.Cells[row, 1].Value)
{
case null:
continue;
case DateTime time:
dates.Add(time);
break;
case Double d:
dates.Add(DateTime.FromOADate(d));
break;
default:
dates.Add(null);
break;
}
}
for (var column = 2; column <= worksheet.Dimension.End.Column; column++)
{
var name = worksheet.Cells[1, column].Text;
var units = worksheet.Cells[2, column].Text;
var parameter = gauge.Parameters.FirstOrDefault(x => x.Name == name);
if (parameter == null)
{
parameter = new WaterQualityParameter {
Name = name
};
gauge.Parameters.Add(parameter);
}
parameter.Units = units;
parameter.Data.Clear();
for (var row = baseRow; row <= worksheet.Dimension.End.Row; row++)
{
_errorRow = row;
_errorColumn = column;
var date = dates[row - baseRow];
if (date == null)
{
continue;
}
var value = worksheet.Cells[row, column].Value as Double?;
if (value == null)
{
continue;
}
parameter.Data.Add(new TimeseriesDatum {
Value = value.Value, Time = date.Value
});
}
}
}
}
}
MessageBox.Show($"Imported Water Quality from {filename}.");
}
catch (Exception ex)
{
MessageBox.Show($"Error while importing row {_errorRow}, column {_errorColumn}: {ex.Message}");
}
}
