private void Init(AdminLevelType type, IWizardStep step, bool demoOnly, int? cid) { countryDemoId = cid; isDemoOnly = demoOnly; locationType = type; prev = step; SettingsRepository settings = new SettingsRepository(); DemoRepository demo = new DemoRepository(); if (!isDemoOnly) demoDate = demo.GetCountryDemoRecent().DateDemographyData; if (!isSingleImport) { nextType = settings.GetNextLevel(locationType.LevelNumber); stepTitle = isDemoOnly ? Translations.UpdateDemography + " - " + locationType.DisplayName : Translations.ImportAdminLevels + locationType.DisplayName; importer = new AdminLevelDemoImporter(locationType, countryDemoId); } else stepTitle = Translations.Demography + " - " + locationType.DisplayName; updater = new AdminLevelDemoUpdater(locationType, countryDemoId); InitializeComponent(); }
public void CreateImportFile(string filename, bool importDemography, int rows, AdminLevel filterLevel) { // Get data filterBy = filterLevel; int dropdownCol = GetParams(); DataTable data = CreateNewImportDataTable(importDemography); DemoRepository demo = new DemoRepository(); CountryDemography recentCountryDemo = demo.GetCountryDemoRecent(); // Create excel System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlsWorkbook; Microsoft.Office.Interop.Excel.Worksheet xlsWorksheet; Microsoft.Office.Interop.Excel.Worksheet xlsValidation; object oMissing = System.Reflection.Missing.Value; validationRanges = new Dictionary<string, string>(); //Create new workbook xlsWorkbook = xlsApp.Workbooks.Add(true); //Get the first worksheet xlsWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)(xlsWorkbook.Worksheets[1]); // add hidden validation worksheet xlsValidation = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorkbook.Worksheets.Add(oMissing, xlsWorksheet, oMissing, oMissing); xlsValidation.Name = validationSheetName; xlsValidation.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden; // Add columns int iCol = 0; foreach (DataColumn c in data.Columns) { iCol++; xlsWorksheet.Cells[1, iCol] = c.ColumnName; } string totalPopColumn = "F"; // Add rows for (int r = 1; r <= rows + 1; r++) { for (int i = 1; i < data.Columns.Count + 1; i++) { if (r == 1) { // Add the header the first time through xlsWorksheet.Cells[1, i] = data.Columns[i - 1].ColumnName; } else { if (i == 1 && filterByType != null) xlsWorksheet.Cells[r, i] = filterLevel.Name; if (dropdownCol == i && dropdownValues.Count > 0) { AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(i), r, dropdownBy.DisplayName, TranslationLookup.GetValue("PleaseSelect"), dropdownValues, oldCI); } if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("UrbanOrRural")) AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(i), r, TranslationLookup.GetValue("UrbanOrRural"), TranslationLookup.GetValue("PleaseSelect"), new List<string> { TranslationLookup.GetValue("AdminUnitUrban"), TranslationLookup.GetValue("AdminUnitRural"), TranslationLookup.GetValue("AdminUnitPeriRural")}, oldCI); if (importDemography) { if (data.Columns[i - 1].ColumnName == "* " + TranslationLookup.GetValue("YearCensus")) xlsWorksheet.Cells[r, i] = recentCountryDemo.YearCensus; if (data.Columns[i - 1].ColumnName == "* " + TranslationLookup.GetValue("GrowthRate")) xlsWorksheet.Cells[r, i] = recentCountryDemo.GrowthRate; if (data.Columns[i - 1].ColumnName == "* " + TranslationLookup.GetValue("TotalPopulation")) totalPopColumn = Util.GetExcelColumnName(i); if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("Pop0Month") && recentCountryDemo.Percent6mos.HasValue) xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.Percent6mos, r, totalPopColumn); if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("PopPsac") && recentCountryDemo.PercentPsac.HasValue) xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentPsac, r, totalPopColumn); if (data.Columns[i - 1].ColumnName == "* " + TranslationLookup.GetValue("PopSac") && recentCountryDemo.PercentSac.HasValue) xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentSac, r, totalPopColumn); if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("Pop5yo") && recentCountryDemo.Percent5yo.HasValue) xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.Percent5yo, r, totalPopColumn); if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("PopAdult") && recentCountryDemo.PercentAdult.HasValue) xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentAdult, r, totalPopColumn); if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("PopFemale") && recentCountryDemo.PercentFemale.HasValue) xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentFemale, r, totalPopColumn); if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("PopMale") && recentCountryDemo.PercentMale.HasValue) xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentMale, r, totalPopColumn); if (data.Columns[i - 1].ColumnName == TranslationLookup.GetValue("PercentRural") && recentCountryDemo.PercentRural.HasValue) xlsWorksheet.Cells[r, i] = string.Format("={2}{1}*{0}/100", recentCountryDemo.PercentRural, r, totalPopColumn); } } } } var last = xlsWorksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing); var range = xlsWorksheet.get_Range("A1", last); range.Columns.AutoFit(); xlsApp.DisplayAlerts = false; xlsWorkbook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, oMissing, oMissing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, oMissing, oMissing, oMissing); xlsApp.Visible = true; xlsWorksheet = null; xlsValidation = null; xlsWorkbook = null; xlsApp = null; System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; }