public ActionResult Index()
{
var model = new SpreadsheetData()
{
DocumentId = Guid.NewGuid().ToString(),
DocumentFormat = DocumentFormat.Xlsx,
Document = DataHelper.GetDocument()
};
return(View(model));
}
public override void Process()
{
// Process attributes
base.Process();
AbilityValues = new List <Dictionary <object, object> >();
// Get headings that are processed automatically
List <string> Headings = GetSpreadsheetColumnFieldHeadings();
// Find all headings that aren't processed automatically
List <string> ValueHeadings = new List <string>();
foreach (var Key in SpreadsheetData.Keys)
{
if (!Headings.Contains(Key))
{
ValueHeadings.Add(Key);
}
}
// Get number of abilities in the sheet
int NumberOfAbilities = 0;
if (Headings.Count > 0 && SpreadsheetData.ContainsKey(Headings[0]))
{
NumberOfAbilities = SpreadsheetData[Headings[0]].Count;
}
// Run through all abilities and build our value dictionary
for (int i = 0; i < NumberOfAbilities; ++i)
{
Dictionary <object, object> ValuesDict = new Dictionary <object, object>();
foreach (var Heading in ValueHeadings)
{
// Check we actually have a value
string ValueString = SpreadsheetData[Heading][i];
if (!string.IsNullOrWhiteSpace(ValueString))
{
// Split the column into key and value and add to dictionary
string[] SplitValues = ValueString.Split(':');
ValuesDict.Add(SplitValues[0].Trim(), SplitValues[1].Trim());
}
}
AbilityValues.Add(ValuesDict);
}
}
private void ReadFileContents(string filePath, string endMonth)
{
Dispatcher.Invoke(() =>
{
spUpload.Visibility = Visibility.Collapsed;
progressIndicator.BusyContent = $"Reading mappings file";
btnValidate.Content = "Validating...";
});
XmlSerializer ser = new XmlSerializer(typeof(mappings));
mappings mappings;
using (XmlReader reader = XmlReader.Create(Path.Combine(System.Configuration.ConfigurationSettings.AppSettings["PrincessTrustRootDirectory"], System.Configuration.ConfigurationSettings.AppSettings["MappingsFilePathDirectory"], System.Configuration.ConfigurationSettings.AppSettings["MappingsFileName"])))
{
try
{
mappings = (mappings)ser.Deserialize(reader);
}
catch (InvalidOperationException ex)
{
// invalid XML file
_mappingErrors.Add($"The was a problem with the mappings file: {ex.Message}");
return;
}
}
if (MappingsFileIsValid(mappings))
{
Dispatcher.Invoke(() =>
{
progressIndicator.BusyContent = $"Reading {Path.GetFileName(filePath)} file";
});
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath, ReadOnly: true);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int i = mappings.templatemaster.firstDataRowId; i <= rowCount; i++)
{
var columnData = new string[colCount];
var month = GetColumnValue(xlRange, i, Utility.ExcelColumnNameToNumber(mappings.templatemaster.endMonthColumnId));
if (month == endMonth)
{
var countryName = GetColumnValue(xlRange, i, Utility.ExcelColumnNameToNumber(mappings.templatemaster.countryColumnId));
Country country = null;
if (!string.IsNullOrEmpty(countryName))
{
if (_schoolAndStudentData.ContainsKey(countryName))
{
country = _schoolAndStudentData[countryName];
}
else
{
country = new Country {
Name = countryName, Schools = new Dictionary <string, School>()
};
_schoolAndStudentData.Add(countryName, country);
}
}
else
{
_errors.Add($"Invalid or blank country name at row {i} column {mappings.templatemaster.countryColumnId}");
}
if (country != null)
{
var schoolName = GetColumnValue(xlRange, i, Utility.ExcelColumnNameToNumber(mappings.templatemaster.schoolColumnId));
School school = null;
if (!string.IsNullOrEmpty(schoolName))
{
if (country.Schools.ContainsKey(schoolName))
{
school = country.Schools[schoolName];
}
else
{
school = new School {
Name = schoolName, Spreadsheets = new Dictionary <string, SpreadsheetData>()
};
country.Schools.Add(schoolName, school);
}
}
else
{
_errors.Add($"Invalid or blank school name at row {i} column {mappings.templatemaster.schoolColumnId}");
}
if (school != null)
{
if (mappings.templatemaster.spreadsheets.Any(x => x.countries.Any(y => y.name == countryName)))
{
foreach (var spreadsheet in mappings.templatemaster.spreadsheets.Where(x => x.countries.Any(y => y.name == countryName)))
{
if (spreadsheet.countries.Any(x => x.name == country.Name))
{
var yearGroup = GetColumnValue(xlRange, i, Utility.ExcelColumnNameToNumber(mappings.templatemaster.yearGroupColumnId));
if (spreadsheet.yeargroups.Any(x => x.name == yearGroup))
{
_studentsLocated++;
var countries = _schoolAndStudentData.Values.Where(x => x.Schools.Any(y => y.Value.Spreadsheets.Any(z => z.Value.Students.Any())));
var schools = 0;
foreach (var countryItem in countries)
{
foreach (var schoolItem in country.Schools)
{
if (schoolItem.Value.Spreadsheets.Any())
{
schools++;
}
}
}
Dispatcher.Invoke(() =>
{
progressIndicator.BusyContent = $"Countries located = {countries.Count()}, Schools located = {schools}, Students located = {_studentsLocated}";
});
SpreadsheetData spreadSheetData = null;
if (school.Spreadsheets.ContainsKey(spreadsheet.templatename))
{
spreadSheetData = school.Spreadsheets[spreadsheet.templatename];
}
else
{
_spreadsheetsToGenerate++;
spreadSheetData = new SpreadsheetData {
Name = spreadsheet.templatename, YearGroup = yearGroup, Students = new List <Student>(), TargetRowId = spreadsheet.targetFirstRowId
};
school.Spreadsheets.Add(spreadsheet.templatename, spreadSheetData);
}
var student = new Student {
Values = new List <mappingsTemplatemasterSpreadsheetMapping>()
};
foreach (var column in spreadsheet.columnMappings)
{
var studentValue = GetColumnValue(xlRange, i, Utility.ExcelColumnNameToNumber(column.sourceColumnId));
student.Values.Add(new mappingsTemplatemasterSpreadsheetMapping {
targetColumnId = column.targetColumnId, value = studentValue
});
}
spreadSheetData.Students.Add(student);
}
}
}
}
else
{
_errors.Add($"There is no mapping for this country: {countryName} in the mapping file at row {i} column {mappings.templatemaster.countryColumnId}");
}
}
}
else if (!_months.Contains(month))
{
_errors.Add($"Invalid end month at row: {i} column: {mappings.templatemaster.endMonthColumnId}");
}
}
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//close and release
xlWorkbook.Close(false, null, null);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlApp);
}
}
public void UpdateCell(int col, int row, string value)
{
SpreadsheetData.SetValue(col, row, value);
}
public void SetSelection(int col, int row)
{
SpreadsheetData.SetSelection(col, row);
}
public void CellBackgroundColor(int col, int row, Color color)
{
SpreadsheetData.SetCellBackground(col, row, color);
}
