public void CreateImportFile(IndicatorManagerOptions indicatorManagerOptions, string filename)
{
ImporterBase importer = new ImporterBase();
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
xlsWorksheet.Cells[1, 1] = "Indicator ID";
xlsWorksheet.Cells[1, 2] = "Type ID";
xlsWorksheet.Cells[1, 3] = "Type Name";
xlsWorksheet.Cells[1, 4] = "Form Name"; // Drop down
xlsWorksheet.Cells[1, 5] = "Indicator Name English";
xlsWorksheet.Cells[1, 6] = "Indicator Name French";
xlsWorksheet.Cells[1, 7] = "Indicator Name Portuguese";
xlsWorksheet.Cells[1, 8] = "Indicator Name Bahasa";
xlsWorksheet.Cells[1, 9] = "Indicator Type"; // Drop down
xlsWorksheet.Cells[1, 10] = "Is Required";
xlsWorksheet.Cells[1, 11] = "Aggregation Rule"; // Drop down
xlsWorksheet.Cells[1, 12] = "Merge Rule"; // Drop down
xlsWorksheet.Cells[1, 13] = "Split Rule"; // Drop down
xlsWorksheet.Cells[1, 14] = "Sort Order";
// row 2+ indicators
int xlsRowCount = 2;
foreach (var ind in indicatorManagerOptions.SelectedIndicators)
{
xlsWorksheet.Cells[xlsRowCount, 1] = ind.ID;
xlsWorksheet.Cells[xlsRowCount, 2] = (int)indicatorManagerOptions.EntityType;
xlsWorksheet.Cells[xlsRowCount, 3] = indicatorManagerOptions.EntityType.ToString();
importer.AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(4), xlsRowCount, "", "", indicatorManagerOptions.FormTypes, System.Threading.Thread.CurrentThread.CurrentCulture);
xlsWorksheet.Cells[xlsRowCount, 4] = ind.FormName;
var english = new TranslationLookupInstance(new CultureInfo("en-US"));
xlsWorksheet.Cells[xlsRowCount, 5] = english.GetValue(ind.Key, ind.Key);
var french = new TranslationLookupInstance(new CultureInfo("fr-FR"));
xlsWorksheet.Cells[xlsRowCount, 6] = french.GetValue(ind.Key, ind.Key);
var port = new TranslationLookupInstance(new CultureInfo("pt-PT"));
xlsWorksheet.Cells[xlsRowCount, 7] = port.GetValue(ind.Key, ind.Key);
var bahasa = new TranslationLookupInstance(new CultureInfo("id-ID"));
xlsWorksheet.Cells[xlsRowCount, 8] = bahasa.GetValue(ind.Key, ind.Key);
importer.AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(9), xlsRowCount, "", "", Enum.GetNames(typeof(IndicatorDataType)).ToList(), System.Threading.Thread.CurrentThread.CurrentCulture);
xlsWorksheet.Cells[xlsRowCount, 9] = ((IndicatorDataType)ind.DataTypeId).ToString();
xlsWorksheet.Cells[xlsRowCount, 10] = ind.IsRequired.ToString();
importer.AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(11), xlsRowCount, "", "", Enum.GetNames(typeof(IndicatorAggType)).ToList(), System.Threading.Thread.CurrentThread.CurrentCulture);
xlsWorksheet.Cells[xlsRowCount, 11] = ((IndicatorAggType)ind.AggregationRuleId).ToString();
importer.AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(12), xlsRowCount, "", "", Enum.GetNames(typeof(MergingRule)).ToList(), System.Threading.Thread.CurrentThread.CurrentCulture);
xlsWorksheet.Cells[xlsRowCount, 12] = ((MergingRule)ind.MergeRule).ToString();
importer.AddDataValidation(xlsWorksheet, xlsValidation, Util.GetExcelColumnName(13), xlsRowCount, "", "", Enum.GetNames(typeof(RedistrictingRule)).ToList(), System.Threading.Thread.CurrentThread.CurrentCulture);
xlsWorksheet.Cells[xlsRowCount, 13] = ((RedistrictingRule)ind.SplitRule).ToString();
xlsWorksheet.Cells[xlsRowCount, 14] = ind.SortOrder;
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);
}
public IndStepIndicators(IndicatorManagerOptions o)
: base()
{
opts = o;
InitializeComponent();
}