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); }