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