public void BindData(AdminLevel e, List<TaskForceAdminUnit> u)
{
Localizer.TranslateControl(this);
existing = e;
taskForceUnits = u;
h3Required1.SetMaxWidth(300);
DemoRepository demo = new DemoRepository();
var parentNames = demo.GetAdminLevelParentNames(existing.Id);
if (parentNames.Count == 0)
h3Required1.Text = e.Name;
else
h3Required1.Text = "";
foreach (var pname in parentNames)
h3Required1.Text += pname.Name + " > ";
if (h3Required1.Text.EndsWith(" > "))
h3Required1.Text = h3Required1.Text.Substring(0, h3Required1.Text.LastIndexOf(" > "));
taskForceUnits.Insert(0, new TaskForceAdminUnit { Id = -1, Name = "" });
bindingSource1.DataSource = taskForceUnits;
if (taskForceUnits.Count > 1)
cbUnits.DropDownWidth = BaseForm.GetDropdownWidth(taskForceUnits.Select(a => a.Name));
}
public virtual void CreateImportFile(string filename, List<AdminLevel> adminLevels, AdminLevelType adminLevelType, ImportOptions opts)
{
options = opts;
ReloadDropdownValues();
LoadRelatedLists();
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.Workbooks xlsWorkbooks;
Microsoft.Office.Interop.Excel.Sheets xlsWorksheets;
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
xlsWorkbooks = xlsApp.Workbooks;
xlsWorkbook = xlsWorkbooks.Add(true);
xlsWorksheets = xlsWorkbook.Worksheets;
//Get the first worksheet
xlsWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)(xlsWorkbook.Worksheets[1]);
// add hidden validation worksheet
xlsValidation = (Microsoft.Office.Interop.Excel.Worksheet)xlsWorksheets.Add(oMissing, xlsWorksheet, oMissing, oMissing);
xlsValidation.Name = validationSheetName;
xlsValidation.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden;
// row 1 column headers
DemoRepository repo = new DemoRepository();
List<string> names = adminLevelType == null? new List<string>() : repo.GetAdminLevelTypeNames(adminLevelType.Id);
if (options.SurveyNames.Count > 0) // Multiple admin units for surveys
xlsWorksheet.Cells[1, 1] = "* " + TranslationLookup.GetValue("SurveyName");
else
xlsWorksheet.Cells[1, 1] = "* " + TranslationLookup.GetValue("ID");
for (int i = 0; i < names.Count; i++)
xlsWorksheet.Cells[1, 2 + i] = "* " + names[i];
int locationCount = names.Count + 1;
int xlsColCount = names.Count + 1; // add one for id;
xlsColCount = AddTypeSpecific(xlsWorksheet, xlsColCount);
int colCountAfterStatic = xlsColCount;
foreach (var item in Indicators)
{
if (item.Value.DataTypeId == (int)IndicatorDataType.SentinelSite || item.Value.IsCalculated || item.Value.IsMetaData)
continue;
string isReq = "";
if (item.Value.IsRequired)
isReq = "* ";
// if the filtered list still has more than 7 possible multiselect values, do some weird shit.
if (options.IndicatorValuesSublist.ContainsKey(item.Value.DisplayName) && options.IndicatorValuesSublist[item.Value.DisplayName].Count > 6)
{
int optionNumber = 1;
foreach (string opt in options.IndicatorValuesSublist[item.Value.DisplayName])
{
xlsColCount++;
xlsWorksheet.Cells[1, xlsColCount] = isReq + TranslationLookup.GetValue(item.Key, item.Key) + Translations.ImportSelectionOption + optionNumber;
optionNumber++;
}
}
else
{
xlsColCount++;
xlsWorksheet.Cells[1, xlsColCount] = isReq + TranslationLookup.GetValue(item.Key, item.Key);
}
}
//xlsWorksheet.Cells[1, xlsColCount + 1] = TranslationLookup.GetValue("Notes");
// row 2+ admin levels
int xlsRowCount = 2;
if (options.SurveyNames.Count > 0) // Multiple admin units for surveys
{
foreach (var survey in options.SurveyNames.OrderBy(a => a.DisplayName))
{
xlsWorksheet.Cells[xlsRowCount, 1] = survey.DisplayName;
AddTypeSpecificLists(xlsWorksheet, xlsValidation, 0, xlsRowCount, oldCI, locationCount);
xlsRowCount = AddIndicatorColumns(oldCI, xlsWorksheet, xlsValidation, colCountAfterStatic, xlsRowCount);
}
}
else
{
foreach (AdminLevel l in adminLevels.OrderBy(a => a.SortOrder))
{
xlsWorksheet.Cells[xlsRowCount, 1] = l.Id;
List<AdminLevel> parents = repo.GetAdminLevelParentNames(l.Id);
int aCol = 2;
foreach (AdminLevel adminlevel in parents)
{
xlsWorksheet.Cells[xlsRowCount, aCol] = adminlevel.Name;
aCol++;
}
AddTypeSpecificLists(xlsWorksheet, xlsValidation, l.Id, xlsRowCount, oldCI, locationCount);
xlsRowCount = AddIndicatorColumns(oldCI, xlsWorksheet, xlsValidation, colCountAfterStatic, xlsRowCount);
}
}
// Auto fit
var last = xlsWorksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
var range = xlsWorksheet.get_Range("A1", last);
range.Columns.AutoFit();
// Save and display
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;
Marshal.ReleaseComObject(xlsWorksheets);
Marshal.ReleaseComObject(xlsWorksheet);
Marshal.ReleaseComObject(xlsValidation);
Marshal.ReleaseComObject(xlsWorkbooks);
Marshal.ReleaseComObject(xlsWorkbook);
Marshal.ReleaseComObject(xlsApp);
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
}