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 void CreateTranslationKeys()
{
string path = ConfigurationManager.AppSettings["AutomatedListsSavePath"];
DataTable table = new DataTable();
Dictionary<string, DataRow> rowDict = new Dictionary<string, DataRow>();
table.Columns.Add(new DataColumn("Key"));
table.Columns.Add(new DataColumn("English"));
table.Columns.Add(new DataColumn("French"));
table.Columns.Add(new DataColumn("Portuguese"));
table.Columns.Add(new DataColumn("Bahasa"));
// id-ID;Bahasa|en-US;English|fr-FR;Français|pt-PT;Português"
var english = new TranslationLookupInstance(new CultureInfo("en-US"));
foreach (var key in english.Keys)
{
var dr = table.NewRow();
dr["Key"] = key;
dr["English"] = english.GetValue(key);
table.Rows.Add(dr);
rowDict.Add(key, dr);
}
var french = new TranslationLookupInstance(new CultureInfo("fr-FR"));
foreach (var key in english.Keys)
{
var dr = rowDict[key];
dr["French"] = french.GetValue(key);
}
var port = new TranslationLookupInstance(new CultureInfo("pt-PT"));
foreach (var key in english.Keys)
{
var dr = rowDict[key];
dr["Portuguese"] = port.GetValue(key);
}
var bahasa = new TranslationLookupInstance(new CultureInfo("id-ID"));
foreach (var key in english.Keys)
{
var dr = rowDict[key];
dr["Bahasa"] = bahasa.GetValue(key);
}
using (ExcelPackage pck = new ExcelPackage())
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");
ws.Cells["A1"].LoadFromDataTable(table, true);
File.WriteAllBytes(path + string.Format("TranslationKeys_{0}.xlsx", DateTime.Now.ToString("yyyyMMdd")), pck.GetAsByteArray());
}
}
public ExportResult DoExport(string fileName, int userId, ExportType exportType)
{
try
{
transLookup = new TranslationLookupInstance(ExportCulture);
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();
xlsApp.DisplayAlerts = false;
excel.Workbook xlsWorkbook;
excel.Worksheet xlsWorksheet;
excel.Range rng = null;
object missing = System.Reflection.Missing.Value;
xlsWorkbook = xlsApp.Workbooks.Open(Path.Combine(Environment.CurrentDirectory, @"Exports\" + transLookup.GetValue("RtiWorkbookLocation", "RtiWorkbookLocation")),
missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
xlsWorkbook.Unprotect("NTDM&E101");
Country country = demo.GetCountry();
var cAdminDemo = demo.GetRecentDemography(1, StartDate, EndDate);
var countryDemo = demo.GetCountryDemoById(cAdminDemo.Id);
List<AdminLevel> reportingLevelUnits = demo.GetAdminUnitsWithParentsAndChildren(AdminLevelType.LevelNumber, StartDate, EndDate);
reportingLevelUnits = reportingLevelUnits.Where(d => d.LevelNumber == AdminLevelType.LevelNumber).OrderBy(a => a.Name).ToList();
var intvs = intvRepo.GetAll(new List<int> { 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23 }, StartDate, EndDate);
Dictionary<int, DataRow> aggIntvs = GetIntvsAggregatedToReportingLevel(StartDate, EndDate, reportingLevelUnits);
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Sheets[transLookup.GetValue("RtiTabCountry", "RtiTabCountry")];
xlsWorksheet.Unprotect("NTDM&E101");
AddInfo(xlsWorksheet, rng, country, exportType, reportingLevelUnits.Count, intvs);
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Sheets[transLookup.GetValue("RtiTabDemo", "RtiTabDemo")];
xlsWorksheet.Unprotect("NTDM&E101");
AddDemo(xlsWorksheet, rng, reportingLevelUnits, countryDemo);
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Sheets[transLookup.GetValue("RtiTabLf", "RtiTabLf")];
xlsWorksheet.Unprotect("NTDM&E101");
AddLf(xlsWorksheet, rng, StartDate, EndDate, reportingLevelUnits, aggIntvs);
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Sheets[transLookup.GetValue("RtiTabOncho", "RtiTabOncho")];
xlsWorksheet.Unprotect("NTDM&E101");
AddOncho(xlsWorksheet, rng, StartDate, EndDate, reportingLevelUnits, aggIntvs);
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Sheets[transLookup.GetValue("RtiTabSch", "RtiTabSch")];
xlsWorksheet.Unprotect("NTDM&E101");
AddSchisto(xlsWorksheet, rng, StartDate, EndDate, reportingLevelUnits, aggIntvs);
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Sheets[transLookup.GetValue("RtiTabSth", "RtiTabSth")];
xlsWorksheet.Unprotect("NTDM&E101");
AddSth(xlsWorksheet, rng, StartDate, EndDate, reportingLevelUnits, aggIntvs);
xlsWorksheet = (excel.Worksheet)xlsWorkbook.Sheets[transLookup.GetValue("RtiTabTra", "RtiTabTra")];
xlsWorksheet.Unprotect("NTDM&E101");
AddTrachoma(xlsWorksheet, rng, StartDate, EndDate, reportingLevelUnits, aggIntvs);
xlsWorkbook.SaveAs(fileName, 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);
}
}
