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