예제 #1
0
        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);
        }
예제 #2
0
        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());
            }
        }
예제 #3
0
        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);
            }
        }