private void ExportWorkingStep_Load(object sender, EventArgs e) { if (!DesignMode) { questions = repo.GetExportQuestions(); if (!questions.JrfYearReporting.HasValue || questions.JrfYearReporting.Value == 0) questions.JrfYearReporting = DateTime.Now.Year; Localizer.TranslateControl(this); h3bLabel1.SetMaxWidth(500); h3bLabel2.SetMaxWidth(500); h3bLabel3.SetMaxWidth(500); h3bLabel4.SetMaxWidth(500); h3bLabel5.SetMaxWidth(500); CreateEndemicityDropdown(comboBox1); CreateEndemicityDropdown(comboBox2); CreateEndemicityDropdown(comboBox3); CreateEndemicityDropdown(comboBox4); bindingSource1.DataSource = questions; //ClearErrors(); this.saveFileDialog1.DefaultExt = "xlsx"; this.saveFileDialog1.Filter = "Excel (.xlsx)|*.xlsx"; var allLevelTypes = settings.GetAllAdminLevels(); var reportingType = allLevelTypes.First(); questions.AdminLevelType = reportingType; bindingSource2.DataSource = allLevelTypes; } }
public ExportResult ExportData(string filePath, int userId, ExportJrfQuestions questions) { try { int yearReported = questions.JrfYearReporting.Value; System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); excel.Application xlsApp = new excel.ApplicationClass(); excel.Workbook xlsWorkbook; excel.Worksheet xlsWorksheet; excel.Worksheet xlsSummary; excel.Range rng = null; object missing = System.Reflection.Missing.Value; // Open workbook xlsWorkbook = xlsApp.Workbooks.Open(Path.Combine(Environment.CurrentDirectory, @"Exports\WHO_JRF_PC.xls"), missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); var districtLevel = questions.AdminLevelType; CountryDemography countryStats = demo.GetCountryLevelStatsRecent(); Country country = demo.GetCountry(); List<AdminLevel> demography = new List<AdminLevel>(); DateTime startDate = new DateTime(yearReported, 1, 1); DateTime endDate = startDate.AddYears(1).AddDays(-1); List<AdminLevel> tree = demo.GetAdminLevelTreeForDemography(districtLevel.LevelNumber, startDate, endDate, ref demography); var reportingLevelUnits = demography.Where(d => d.LevelNumber == districtLevel.LevelNumber).ToList(); Dictionary<int, DataRow> aggIntvs = GetIntvsAggregatedToReportingLevel(startDate, endDate, reportingLevelUnits); // Info page xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[1]; xlsWorksheet.Unprotect(); AddQuestions(xlsWorksheet, rng, questions, countryStats, demography, districtLevel.LevelNumber, country); // run macro to create district rows. xlsApp.DisplayAlerts = false; xlsApp.Run("Sheet1.DISTRICT"); xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[2]; xlsWorksheet.Unprotect(); AddDemo(xlsWorksheet, rng, demography, districtLevel.LevelNumber, startDate, endDate); // Summary xlsSummary = (excel.Worksheet)xlsWorkbook.Sheets["SUMMARY"]; // ALL THE INTVS AddT3(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs); AddT2(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs); AddT1(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs); AddMDA4(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs); AddMDA3(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs); AddMDA2(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs); AddMDA1(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs); AddDistricts(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs); xlsWorkbook.SaveAs(filePath, excel.XlFileFormat.xlOpenXMLWorkbook, missing, missing, false, false, excel.XlSaveAsAccessMode.xlNoChange, excel.XlSaveConflictResolution.xlUserResolution, true, missing, missing, missing); xlsApp.ScreenUpdating = true; xlsApp.Visible = true; rng = null; Marshal.ReleaseComObject(xlsWorksheet); Marshal.ReleaseComObject(xlsWorkbook); Marshal.ReleaseComObject(xlsApp); System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; return new ExportResult { WasSuccess = true }; } catch (Exception ex) { return new ExportResult(ex.Message); } }
public void UpdateExportQuestions(ExportJrfQuestions questions) { OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString); using (connection) { connection.Open(); try { OleDbCommand command = new OleDbCommand(@"Update ExportJrfQuestions SET JrfYearReporting=@JrfYearReporting, JrfEndemicLf=@JrfEndemicLf, JrfEndemicOncho=@JrfEndemicOncho, JrfEndemicSth=@JrfEndemicSth, JrfEndemicSch=@JrfEndemicSch WHERE ID = @id", connection); command.Parameters.Add(OleDbUtil.CreateNullableParam("@JrfYearReporting", questions.JrfYearReporting)); command.Parameters.Add(OleDbUtil.CreateNullableParam("@JrfEndemicLf", questions.JrfEndemicLf)); command.Parameters.Add(OleDbUtil.CreateNullableParam("@JrfEndemicOncho", questions.JrfEndemicOncho)); command.Parameters.Add(OleDbUtil.CreateNullableParam("@JrfEndemicSth", questions.JrfEndemicSth)); command.Parameters.Add(OleDbUtil.CreateNullableParam("@JrfEndemicSch", questions.JrfEndemicSch)); command.Parameters.Add(new OleDbParameter("@id", questions.Id)); command.ExecuteNonQuery(); } catch (Exception) { throw; } } }
private void AddQuestions(excel.Worksheet xlsWorksheet, excel.Range rng, ExportJrfQuestions questions, CountryDemography countryDemo, List<AdminLevel> demography, int districtLevel, Country country) { AddValueToRange(xlsWorksheet, rng, "E34", country.Name); AddValueToRange(xlsWorksheet, rng, "E36", questions.JrfYearReporting.Value); AddValueToRange(xlsWorksheet, rng, "E38", TranslationLookup.GetValue(questions.JrfEndemicLf, questions.JrfEndemicLf)); AddValueToRange(xlsWorksheet, rng, "E40", TranslationLookup.GetValue(questions.JrfEndemicOncho, questions.JrfEndemicOncho)); AddValueToRange(xlsWorksheet, rng, "E42", TranslationLookup.GetValue(questions.JrfEndemicSth, questions.JrfEndemicSth)); AddValueToRange(xlsWorksheet, rng, "E44", TranslationLookup.GetValue(questions.JrfEndemicSch, questions.JrfEndemicSch)); AddValueToRange(xlsWorksheet, rng, "E46", demography.Where(d => d.LevelNumber == districtLevel).Count()); if (countryDemo.PercentPsac.HasValue) AddValueToRange(xlsWorksheet, rng, "E48", countryDemo.PercentPsac.Value / 100); else AddValueToRange(xlsWorksheet, rng, "E48", 0); if (countryDemo.PercentSac.HasValue) AddValueToRange(xlsWorksheet, rng, "E49", countryDemo.PercentSac.Value / 100); else AddValueToRange(xlsWorksheet, rng, "E49", 0); if (countryDemo.PercentAdult.HasValue) AddValueToRange(xlsWorksheet, rng, "E50", countryDemo.PercentAdult.Value / 100); else AddValueToRange(xlsWorksheet, rng, "E50", 0); }
public ExportJrfQuestions GetExportQuestions() { ExportJrfQuestions questions = new ExportJrfQuestions(); OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString); using (connection) { connection.Open(); OleDbCommand command = new OleDbCommand(); command = new OleDbCommand(@"Select ID, JrfYearReporting, JrfEndemicLf, JrfEndemicOncho, JrfEndemicSth, JrfEndemicSch FROM ExportJrfQuestions WHERE ID = 1 ", connection); using (OleDbDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { reader.Read(); questions.Id = reader.GetValueOrDefault<int>("ID"); questions.JrfYearReporting = reader.GetValueOrDefault<int>("JrfYearReporting"); questions.JrfEndemicLf = reader.GetValueOrDefault<string>("JrfEndemicLf"); questions.JrfEndemicOncho = reader.GetValueOrDefault<string>("JrfEndemicOncho"); questions.JrfEndemicSth = reader.GetValueOrDefault<string>("JrfEndemicSth"); questions.JrfEndemicSch = reader.GetValueOrDefault<string>("JrfEndemicSch"); } reader.Close(); } } return questions; }