示例#1
0
        private void AddAction(Excel.Worksheet sheet, User user)
        {
            //get empty row
            var emptyRow = sheet.UsedRange;

            var col = sheet.Columns[1].Rows.Last();
        }
示例#2
0
        public RatingGroups(string tbnme, bool rb)
        {

            this.oSheet = (Worksheet) AxCalcEngine.shts[tbnme];
            this.oSheet.Activate();
            this.i = 0;
            this.j = 0;
            
            int multiplicationFactorCountCategories = 0;


            object[,] excelData = ExcelUtility.PullInExcelData(4, 1, oSheet);
            excelData = Utility.RebaseArrayToZero(excelData);
            SetFactorLength(excelData);
            

            this.i -= this.additionFactorCountAllColumns;
            multiplicationFactorCountCategories = this.i / 2;

            List<System.Data.DataTable> factorsInDatatable = ReturnMultFactorInTable(excelData, multiplicationFactorCountCategories);
            List<string> factorNameList = SetFactorNames(factorsInDatatable);
            List<string> source = FullJoinAndReturnStringWithSeparatorCharacter(factorsInDatatable);
            List<List<string>> parsedJoinedFactorsAsString = ParseFullJoinStringsAndReturnStringList(source);
            List<double> combinedFactorList = CombineFactors(parsedJoinedFactorsAsString);
            List<List<string>> factorsInStringListReadyForNumericalFactor = CreateStringListOfFactors(parsedJoinedFactorsAsString);
            CreateOutputTableColumnNames(factorNameList);
            CreateDataTablesWithFactorNames(factorsInStringListReadyForNumericalFactor);
            List<string> tempTbl = GenerateCheckingList();
            AddFactorToFactorTable(combinedFactorList);
            ValidateColumnNames(tempTbl);

        }
示例#3
0
 public Census(string tbnme)
 {
     this.oSheet = (Worksheet) AxCalcEngine.AxCalcEngine.shts[tbnme];
     this.oSheet.Activate();
     int row = this.oSheet.UsedRange.Rows[this.oSheet.UsedRange.Rows.Count].Row;
     int column = this.oSheet.UsedRange.Columns[this.oSheet.UsedRange.Columns.Count].Column;
     object[,] objArray = (object[,]) this.oSheet.Range(this.oSheet.Cells[4, 1], this.oSheet.Cells[row, column]).get_Value(XlRangeValueDataType.xlRangeValueDefault);
     int num3 = column;
     int num4 = row - 3;
     int item = 0;
     for (int i = 1; i <= num4; i++)
     {
         this.cnLstRw = new List<object>();
         for (int j = 1; j <= num3; j++)
         {
             object obj2 = objArray[i, j];
             if (obj2 == null)
             {
                 throw new Exception("Unable to read Census: Incomplete Census");
             }
             this.cnLstRw.Add(obj2);
         }
         if (i == 1)
         {
             this.cnLstRw.Add("Record ID");
         }
         else
         {
             this.cnLstRw.Add(item);
         }
         item++;
         this.cnLst.Add(this.cnLstRw);
     }
 }
示例#4
0
        public TestResult DoTest()
        {
            Excel.Application application = null;
            DateTime          startTime   = DateTime.Now;

            try
            {
                application = COMObject.Create <Excel.Application>(COMObjectCreateOptions.CreateNewCore);
                application.DisplayAlerts = false;
                application.Workbooks.Add();
                Excel.Worksheet sheet = application.Workbooks[1].Sheets[1] as Excel.Worksheet;

                foreach (Excel.Range item in sheet.Range("$A1:$B100"))
                {
                    item.Value = DateTime.Now;
                }

                return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, ""));
            }
            catch (Exception exception)
            {
                return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, ""));
            }
            finally
            {
                if (null != application)
                {
                    application.Quit();
                    application.Dispose();
                }
            }
        }
示例#5
0
        public void Run()
        {
            // this examples shows how i can use variant types(object in NetOffice) at runtime
            // the reason for the most variant definitions in office is a more flexible value set.(95%)
            // here is the code to demonstrate this

            // start application
            Excel.Application application = new Excel.Application();
            application.DisplayAlerts = false;

            // create new Workbook and a new named style
            Excel.Workbook  book    = application.Workbooks.Add();
            Excel.Worksheet sheet   = (Excel.Worksheet)book.Worksheets[1];
            Excel.Range     range   = sheet.Cells[1, 1];
            Excel.Style     myStyle = book.Styles.Add("myUniqueStyle");

            // Range.Style is defined as Variant in Excel and represents as object in NetOffice
            // You got always an Excel.Style instance if you ask for
            Excel.Style style = (Excel.Style)range.Style;

            // and here comes the magic. both sets are valid because the variants was very flexible in the setter
            range.Style = "myUniqueStyle";
            range.Style = myStyle;

            // Name, Bold, Size are string, bool and double but defined as Variant
            style.Font.Name = "Arial";
            style.Font.Bold = true; // you can also set "true" and it works. variants makes it possible
            style.Font.Size = 14;

            // quit & dipose
            application.Quit();
            application.Dispose();

            HostApplication.ShowFinishDialog();
        }
示例#6
0
 public static List<ExcelHeader> get2Headers(Worksheet xlWs, object[,] ExcelData, int startRow)
 {
     List<ExcelHeader> list = new List<ExcelHeader>();
     string str = string.Empty;
     for (int i = ExcelData.GetLowerBound(1); i <= ExcelData.GetUpperBound(1); i++)
     {
         if (ExcelData[startRow - 1, i] != null)
         {
             string str2 = string.Empty;
             string str3 = ExcelData[startRow - 1, i].ToString();
             if (ExcelData[startRow - 2, i] != null)
             {
                 str2 = ExcelData[startRow - 2, i].ToString();
                 if ((xlWs.Cells[startRow - 2, i].MergeArea.Cells.Count > 1) || (((XlHAlign) xlWs.Cells[startRow - 2, i].HorizontalAlignment) == XlHAlign.xlHAlignCenterAcrossSelection))
                 {
                     str = str2;
                 }
                 else
                 {
                     str = string.Empty;
                 }
             }
             else if ((xlWs.Cells[startRow - 2, i].MergeArea.Cells.Count > 1) || (((XlHAlign) xlWs.Cells[startRow - 2, i].HorizontalAlignment) == XlHAlign.xlHAlignCenterAcrossSelection))
             {
                 str2 = str;
             }
             list.Add(new ExcelHeader(str2, str3, i));
         }
     }
     xlWs.DisposeChildInstances();
     return list;
 }
示例#7
0
        public InputOptionsPage(Worksheet oSheet)
        {
            this.ancPln = (string)oSheet.Range("anchorPlan").Value;
            this.subsidyOption = (string)oSheet.Range("subType").Value;
            this.mgOpt = (string)oSheet.Range("migType").Value;
            this.projName = (string)oSheet.Range("projNme").Value;
            this.inMode = (string)oSheet.Range("modal").Value;
            this.outMode = (string)oSheet.Range("modalOut").Value;
            this.clientName = (string)oSheet.Range("clientNme").Value;
            this.clientID = oSheet.Range("clientID").Value.ToString();
            this.subsidyValue = double.Parse(oSheet.Range("Sub").Value.ToString());
            this.subsidyMin = double.Parse(oSheet.Range("Min").Value == null ? 0.ToString() : oSheet.Range("Min").Value.ToString());
            this.subsidyMax = double.Parse(oSheet.Range("Max").Value == null ? 9999999999.ToString() : oSheet.Range("Max").Value.ToString());
            defaultAnchorPlan = "";
            this.modalAdj = Utility.modalConvert(inMode, outMode);
            this.displayName = "";
            this.reviewerName = (string)oSheet.Range("peerReviewer").Value;
            this.finalModelIndicator = (((string)oSheet.Range("finalRun").Value).Equals("Yes", StringComparison.OrdinalIgnoreCase));


            ClientNameIDCheck();
            CheckProjName();
            CheckValidSubsidyPercent();
            SetAndCheckDisplayNameAndConnection();

        }
示例#8
0
        private static Excel.Range PutSampleData(Excel.Worksheet workSheet)
        {
            workSheet.Cells[2, 2].Value = "Datum";
            workSheet.Cells[3, 2].Value = DateTime.Now.ToShortDateString();
            workSheet.Cells[4, 2].Value = DateTime.Now.ToShortDateString();
            workSheet.Cells[5, 2].Value = DateTime.Now.ToShortDateString();
            workSheet.Cells[6, 2].Value = DateTime.Now.ToShortDateString();

            workSheet.Cells[2, 3].Value = "Columns1";
            workSheet.Cells[3, 3].Value = 25;
            workSheet.Cells[4, 3].Value = 33;
            workSheet.Cells[5, 3].Value = 30;
            workSheet.Cells[6, 3].Value = 22;

            workSheet.Cells[2, 4].Value = "Column2";
            workSheet.Cells[3, 4].Value = 25;
            workSheet.Cells[4, 4].Value = 33;
            workSheet.Cells[5, 4].Value = 30;
            workSheet.Cells[6, 4].Value = 22;

            workSheet.Cells[2, 5].Value = "Column3";
            workSheet.Cells[3, 5].Value = 25;
            workSheet.Cells[4, 5].Value = 33;
            workSheet.Cells[5, 5].Value = 30;
            workSheet.Cells[6, 5].Value = 22;

            return(workSheet.Range("$B2:$E6"));
        }
示例#9
0
        internal void Run()
        {
            Excel.Application app = null;
            try
            {
                Settings.Default.PerformanceTrace.Alert += new PerformanceTrace.PerformanceAlertEventHandler(PerformanceTrace_Alert);
                Settings.Default.PerformanceTrace["ExcelApi"].Enabled    = true;
                Settings.Default.PerformanceTrace["ExcelApi"].IntervalMS = 0;

                app         = new Excel.Application();
                app.Visible = true;
                Contribution.CommonUtils utils = new Contribution.CommonUtils(app, typeof(Form1).Assembly);
                app.DisplayAlerts = false;
                Excel.Workbook  book  = app.Workbooks.Add();
                Excel.Worksheet sheet = book.Sheets[1] as Excel.Worksheet;
                sheet.Cells[1, 1].Value = "This is a sample value";
                sheet.Protect();

                utils.Dialog.SuppressOnAutomation = false;
                utils.Dialog.SuppressOnHide       = false;
                utils.Dialog.ShowDiagnostics(true);
            }
            catch (Exception exception)
            {
                Console.WriteLine(exception.ToString());
            }
            finally
            {
                if (null != app)
                {
                    app.Quit();
                    app.Dispose();
                }
            }
        }
示例#10
0
        public void RunExample()
        {
            // start excel and turn off msg boxes
            Excel.Application excelApplication = new Excel.Application();
            excelApplication.DisplayAlerts = false;

            // add a new workbook
            Excel.Workbook  workBook  = excelApplication.Workbooks.Add();
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];

            // we need some data to display
            Excel.Range dataRange = PutSampleData(workSheet);

            // create a nice diagram
            Excel.ChartObject chart = ((Excel.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225);
            chart.Chart.SetSourceData(dataRange);

            // save the book
            string fileExtension = GetDefaultExtension(excelApplication);
            string workbookFile  = string.Format("{0}\\Example05{1}", _hostApplication.RootDirectory, fileExtension);

            workBook.SaveAs(workbookFile);

            // close excel and dispose reference
            excelApplication.Quit();
            excelApplication.Dispose();

            // show dialog for the user(you!)
            _hostApplication.ShowFinishDialog(null, workbookFile);
        }
示例#11
0
        public void RunExample()
        {
            // start excel and turn off msg boxes
            Excel.Application excelApplication = new Excel.Application();
            excelApplication.DisplayAlerts = false;

            // create a utils instance, no need for but helpful to keep the lines of code low
            CommonUtils utils = new CommonUtils(excelApplication);

            // add a new workbook
            Excel.Workbook workBook = excelApplication.Workbooks.Add();
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];

            // we need some data to display
            Excel.Range dataRange = PutSampleData(workSheet);

            // create a nice diagram
            Excel.ChartObject chart = ((Excel.ChartObjects)workSheet.ChartObjects()).Add(70, 100, 375, 225);
            chart.Chart.SetSourceData(dataRange);

            // save the book
            string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example05", DocumentFormat.Normal);
            workBook.SaveAs(workbookFile);

            // close excel and dispose reference
            excelApplication.Quit();
            excelApplication.Dispose();

            // show end dialog
            HostApplication.ShowFinishDialog(null, workbookFile);
        }
示例#12
0
        private void listViewSearchResults_DoubleClick(object sender, EventArgs e)
        {
            try
            {
                if (listViewSearchResults.SelectedItems.Count > 0)
                {
                    Excel.Worksheet activeSheet = Addin.Application.ActiveSheet as Excel.Worksheet;
                    Excel.Range     activeCell  = Addin.Application.ActiveCell;
                    if (null != activeCell)
                    {
                        int rowIndex    = activeCell.Row;
                        int columnIndex = activeCell.Column;

                        string targetRangeAddress = CalculateRangeArea(rowIndex, columnIndex, 7);

                        Customer selectedCustomer = listViewSearchResults.SelectedItems[0].Tag as Customer;

                        Excel.Range targetRange = activeSheet.Range(targetRangeAddress);
                        targetRange.Value2 = ToStringArray(selectedCustomer);
                        targetRange.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                        activeSheet.Columns[targetRange.Column].AutoFit();

                        activeCell.Dispose();
                        activeSheet.Dispose();
                    }
                }
            }
            catch (Exception exception)
            {
                MessageBox.Show(this, exception.Message, "An error occured", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
示例#13
0
        public TestResult DoTest()
        {
            Excel.Application application = null;
            DateTime          startTime   = DateTime.Now;

            try
            {
                application = COMObject.Create <Excel.Application>(COMObjectCreateOptions.CreateNewCore);
                application.DisplayAlerts = false;
                application.Workbooks.Add();
                Excel.Worksheet sheet = application.Workbooks[1].Sheets[1] as Excel.Worksheet;

                for (int i = 1; i <= 200; i++)
                {
                    sheet.Cells[i, 1].Value = string.Format("Test {0}", i);
                    sheet.Range(string.Format("$B{0}", i)).Value = 42.3;
                }

                return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, ""));
            }
            catch (Exception exception)
            {
                return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, ""));
            }
            finally
            {
                if (null != application)
                {
                    application.Quit();
                    application.Dispose();
                }
            }
        }
示例#14
0
        private IEnumerable <Excel.Range> FindAllCellsContainingFormulaCandidates(Excel.Worksheet sheet, string pattern)
        {
            var result = new List <Excel.Range>();

            Excel.Range range = sheet.Cells.Find(
                pattern,
                after: null,
                lookIn: Excel.Enums.XlFindLookIn.xlFormulas,
                lookAt: Excel.Enums.XlLookAt.xlPart,
                searchOrder: null,
                searchDirection: null,
                matchCase: false);

            if (range != null)
            {
                string firstAddress = range.Address;

                do
                {
                    result.Add(range);
                    range = sheet.Cells.FindNext(range);
                } while (range != null && range.Address != firstAddress);
            }

            return(result);
        }
示例#15
0
        public void RunExample()
        {
            // start excel and turn off msg boxes
            Excel.Application excelApplication = new Excel.Application();
            excelApplication.DisplayAlerts = false;

            // add a new workbook
            Excel.Workbook  workBook  = excelApplication.Workbooks.Add();
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];

            // draw back color and perform the BorderAround method
            workSheet.Range("$B2:$B5").Interior.Color = ToDouble(Color.DarkGreen);
            workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic);

            // draw back color and border the range explicitly
            workSheet.Range("$D2:$D5").Interior.Color = ToDouble(Color.DarkGreen);
            workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble;
            workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Weight    = 4;
            workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Color     = ToDouble(Color.Black);

            workSheet.Cells[1, 1].Value = "We have 2 simple shapes created.";

            // save the book
            string fileExtension = GetDefaultExtension(excelApplication);
            string workbookFile  = string.Format("{0}\\Example01{1}", _hostApplication.RootDirectory, fileExtension);

            workBook.SaveAs(workbookFile);

            // close excel and dispose reference
            excelApplication.Quit();
            excelApplication.Dispose();

            // show dialog for the user(you!)
            _hostApplication.ShowFinishDialog(null, workbookFile);
        }
示例#16
0
        public void RunExample()
        {
            bool   isFailed     = false;
            string workbookFile = null;

            Excel.Application excelApplication = null;
            try
            {
                // start excel and turn off msg boxes
                excelApplication = new Excel.Application();
                excelApplication.DisplayAlerts = false;
                excelApplication.Visible       = false;

                // create a utils instance, not need for but helpful to keep the lines of code low
                Excel.Tools.CommonUtils utils = new Excel.Tools.CommonUtils(excelApplication);

                // add a new workbook
                Excel.Workbook workBook = excelApplication.Workbooks.Add();

                // add new global Code Module
                VB.VBComponent globalModule = workBook.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
                globalModule.Name = "MyNewCodeModule";

                // add a new procedure to the modul
                globalModule.CodeModule.InsertLines(1, "Public Sub HelloWorld(Param as string)\r\n MsgBox \"Hello from NetOffice!\" & vbnewline & Param\r\nEnd Sub");

                // create a click event trigger for the first worksheet
                int linePosition = workBook.VBProject.VBComponents[2].CodeModule.CreateEventProc("BeforeDoubleClick", "Worksheet");
                workBook.VBProject.VBComponents[2].CodeModule.InsertLines(linePosition + 1, "HelloWorld \"BeforeDoubleClick\"");

                // display info in the worksheet
                Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets[1];

                sheet.Cells[2, 2].Value = "This workbook contains dynamic created VBA Moduls and Event Code";
                sheet.Cells[5, 2].Value = "Open the VBA Editor to see the code";
                sheet.Cells[8, 2].Value = "Do a double click to catch the BeforeDoubleClick Event from this Worksheet.";

                // save the book
                XlFileFormat fileFormat = GetFileFormat(excelApplication);
                workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example07", Excel.Tools.DocumentFormat.Macros);
                workBook.SaveAs(workbookFile, fileFormat);
            }
            catch (System.Runtime.InteropServices.COMException throwedException)
            {
                isFailed = true;
                HostApplication.ShowErrorDialog("VBA Error", throwedException);
            }
            finally
            {
                // close excel and dispose reference
                excelApplication.Quit();
                excelApplication.Dispose();

                if ((null != workbookFile) && (!isFailed))
                {
                    HostApplication.ShowFinishDialog(null, workbookFile);
                }
            }
        }
示例#17
0
        private static void AddDataRows(Excel.Worksheet sheet, DataSet dataset, object[,] tempArray)
        {
            var range = sheet.Range(sheet.Cells[1, 1],
                                    sheet.Cells[(dataset.Tables["dataTable"].Rows.Count), (dataset.Tables["dataTable"].Columns.Count)]);

            sheet.Name  = "Relatório";
            range.Value = tempArray;
        }
示例#18
0
 private static void AddExcelHeadingText(Excel.Worksheet sheet)
 {
     //Apply styling to heading text
     sheet.Cells[1, 1].Value          = "Girl Agency Financial Report";
     sheet.Cells[1, 1].Font.Name      = "Impact";
     sheet.Cells[1, 1].Font.Underline = true;
     sheet.Cells[1, 1].Font.Size      = 14;
 }
示例#19
0
        private static void AddColumnNames(Excel.Worksheet sheet, object[] tempHeadingArray)
        {
            var columnNameRange = sheet.get_Range(sheet.Cells[3, 3], sheet.Cells[3, tempHeadingArray.Length + 2]);

            columnNameRange.Style            = "NewStyle";
            columnNameRange.Value            = tempHeadingArray;
            columnNameRange.UseStandardWidth = true;
        }
示例#20
0
        public void RunExample()
        {
            // start excel and turn off msg boxes
            Excel.Application excelApplication = new Excel.Application();
            excelApplication.DisplayAlerts = false;

            // create a utils instance, not need for but helpful to keep the lines of code low
            CommonUtils utils = new CommonUtils(excelApplication);

            // add a new workbook
            //Excel.Workbook workBook = excelApplication.Workbooks.Add();
            Excel.Workbook  workBook  = excelApplication.Workbooks.Open(@"C:\Users\Gilbert Perlaza\Dropbox\HPC\MttoApp\Docs\FORMATO EWO EN BLANCO UNIFICADO OT.XLSX");
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];

            workSheet.Cells[1, 1].Value = "NetOffice Excel Example 04";

            // create a star
            Excel.Shape starShape = workSheet.Shapes.AddShape(MsoAutoShapeType.msoShape32pointStar, 10, 50, 200, 20);

            // create a simple textbox
            Excel.Shape textBox = workSheet.Shapes.AddTextbox(MsoTextOrientation.msoTextOrientationHorizontal, 10, 150, 200, 50);
            Excel.Shape rb      = workSheet.Shapes.AddFormControl(Excel.Enums.XlFormControl.xlOptionButton, 100, 100, 100, 100);

            var sh = workSheet.Shapes;

            foreach (var item in sh)
            {
                if (item.Name.Equals("Option Button 1"))
                {
                    item.ControlFormat.Value = 1;
                }
            }
            rb.TextFrame.Characters().Text = "Hola prueba";
            rb.ControlFormat.Value = 0;
            textBox.TextFrame.Characters().Text = "text";
            textBox.TextFrame.Characters().Font.Size = 14;

            // create a wordart
            Excel.Shape textEffect = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect14, "WordArt", "Arial", 12,
                                                                    MsoTriState.msoTrue, MsoTriState.msoFalse, 10, 250);

            // create text effect
            Excel.Shape textDiagram = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect11, "Effect", "Arial", 14,
                                                                     MsoTriState.msoFalse, MsoTriState.msoFalse, 10, 350);

            // save the book
            string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example04", DocumentFormat.Normal);

            workBook.SaveAs(workbookFile);

            // close excel and dispose reference
            excelApplication.Quit();
            excelApplication.Dispose();

            // show end dialog
            HostApplication.ShowFinishDialog(null, workbookFile);
        }
示例#21
0
        private const int rowNum = 4;  //Row where data starts in excel sheet (1 index)


        public PriorPremiums(string tbnme)
        {
            this.oSheet = (Worksheet) AxCalcEngine.shts[tbnme];
            this.oSheet.Activate();

            object[,] objArray = ExcelUtility.PullInExcelData(rowNum, colNum, oSheet);
            ListifyOldPremiums(objArray);

        }
        public void AbrirArchivo(string sNombrePlantilla)
        {
            AplicacionExcel = new Excel.Application();
            AplicacionExcel.DisplayAlerts = false;
            AplicacionExcel.Visible = false;

            Libro = AplicacionExcel.Workbooks.Open(Environment.CurrentDirectory + "\\Plantillas\\" + sNombrePlantilla);
            Hoja = (Excel.Worksheet)Libro.Sheets[1];
        }
示例#23
0
        public NewPremiums(string tbnme)
        {
            this.oSheet = (Worksheet) AxCalcEngine.shts[tbnme];
            this.oSheet.Activate();
            
            object[,] objArray = ExcelUtility.PullInExcelData(startRow, startCol, this.oSheet);
            ListifyNewPremiums(objArray);
            RatingBandThrowException();

        }
示例#24
0
 public PriorSurcharge(string tbnme, double modalAdj)
 {
     this.oSheet = (Worksheet) AxCalcEngine.AxCalcEngine.shts[tbnme];
     this.oSheet.Activate();
     this.i = 0;
     this.j = 0;
     while (!string.IsNullOrEmpty((string) this.oSheet.Range("A3").Offset(1, this.i).Value))
     {
         if (this.oSheet.Range("A3").Offset(1, this.i + 1).Value.ToString().Equals("Addition", StringComparison.OrdinalIgnoreCase) && (this.oSheet.Range("A3").Offset(1, this.i).Value.ToString().IndexOf("Prior", StringComparison.OrdinalIgnoreCase) >= 0))
         {
             this.SCNme = this.oSheet.Range("A3").Offset(1, this.i).Value.ToString();
             this.SCNme = Regex.Replace(this.SCNme, "Prior ", "", RegexOptions.IgnoreCase);
             this.PSCat = new Dictionary<string, List<object>>();
             if (string.IsNullOrEmpty((string) this.oSheet.Range("A3").Offset(1, this.i + 1).Value))
             {
                 throw new Exception("All surcharges must have a corresponding additive value");
             }
             System.Data.DataTable item = new System.Data.DataTable();
             item.Columns.Add(this.SCNme);
             item.Columns.Add((string) this.oSheet.Range("A3").Offset(1, this.i + 1).Value);
             this.j = 0;
             while (!string.IsNullOrEmpty((string) this.oSheet.Range("A3").Offset(this.j + 2, this.i).Value))
             {
                 this.PSHold = new List<object>();
                 this.PSHold.Add(this.oSheet.Range("A3").Offset(this.j + 2, this.i).Value.ToString());
                 if (this.oSheet.Range("A3").Offset(this.j + 2, this.i + 1).Value.ToString() == "")
                 {
                     throw new Exception("Category does not have an additive value");
                 }
                 this.PSHold.Add(double.Parse(this.oSheet.Range("A3").Offset(this.j + 2, this.i + 1).Value.ToString()) * modalAdj);
                 this.PSHold.Add(this.SCNme);
                 try
                 {
                     this.PSCat.Add((string) this.PSHold[0], this.PSHold);
                 }
                 catch
                 {
                     throw new Exception("Duplicate Category");
                 }
                 item.Rows.Add(new object[] { this.oSheet.Range("A3").Offset(this.j + 2, this.i).Value.ToString(), double.Parse(this.oSheet.Range("A3").Offset(this.j + 2, this.i + 1).Value.ToString()) * modalAdj });
                 this.j++;
             }
             try
             {
                 this.TempReadIn.Add(this.SCNme, this.PSCat);
             }
             catch
             {
                 throw new Exception("Duplicated category");
             }
             this.tblHld.Add(item);
         }
         this.i += 2;
     }
 }
示例#25
0
 private static void AddExcelHeadingText(Excel.Worksheet sheet)
 {
     //Apply styling to heading text
     sheet.Columns[1].Cells[1, 1].Value = "Name";
     sheet.Columns[2].Cells[1, 1].Value = "Type";
     sheet.Columns[3].Cells[1, 1].Value = "Path";
     sheet.Columns[4].ColumnWidth       = 30;
     sheet.Columns[6].Cells[1, 1].Value = "Date";
     sheet.Cells[3, 1].ColumnWidth      = 20;
     sheet.Cells.Font.Size = 14;
 }
示例#26
0
        static void Main(string[] args)
        {
            try
            {
                //
                Console.WriteLine("NetOffice Utils Concept Test");
                Console.WriteLine("0 Milliseconds trace values is not a bug - its just to fast\r\n");

                NetOffice.Settings.Default.PerformanceTrace.Enabled = true;
                NetOffice.Settings.Default.PerformanceTrace.Alert  += new NetOffice.PerformanceTrace.PerformanceAlertEventHandler(PerformanceTrace_Alert);

                // Criteria 1
                // Enable performance trace in excel generaly. set interval limit to 100 to see all actions there need >= 100 milliseconds
                NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi"].Enabled    = true;
                NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi"].IntervalMS = 100;

                // Criteria 2
                // Enable additional performance trace for all members of Range in excel. set interval limit to 20 to see all actions there need >=20 milliseconds
                NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Range"].Enabled    = true;
                NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Range"].IntervalMS = 20;

                // Criteria 3
                // Enable additional performance trace for WorkSheet Range property in excel. set interval limit to 0 to see all calls anywhere
                NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet", "Range"].Enabled    = true;
                NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Worksheet", "Range"].IntervalMS = 0;

                // Criteria 4
                // Enable additional performance trace for Range this[] indexer in excel. set interval limit to 0 to see all calls anywhere
                NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Range", "_Default"].Enabled    = true;
                NetOffice.Settings.Default.PerformanceTrace["NetOffice.ExcelApi", "Range", "_Default"].IntervalMS = 0;

                Excel.Application application = new Excel.ApplicationClass();
                application.DisplayAlerts = false;
                Excel.Workbook  book  = application.Workbooks.Add();
                Excel.Worksheet sheet = book.Sheets.Add() as Excel.Worksheet;
                for (int i = 1; i <= 5; i++)
                {
                    Excel.Range range = sheet.Range("A" + i.ToString());
                    range.Value       = "Test123";
                    range[1, 1].Value = "Test234";
                }

                application.Quit();
                application.Dispose();

                Console.WriteLine("\r\nTest passed");
                Console.ReadKey();
            }
            catch (Exception exception)
            {
                Console.WriteLine(exception.Message);
                Console.ReadKey();
            }
        }
示例#27
0
        public NewSurcharge(string tbnme, double modalAdj)
        {
            this.oSheet = (Worksheet) AxCalcEngine.shts[tbnme];
            this.oSheet.Activate();
            this.i = 0;
            this.j = 0;

            object[,] excelData = ExcelUtility.PullInExcelData(4, 1, oSheet);
            object[,] rebasedExcelData = Utility.RebaseArrayToZero(excelData);
            ParseNewSurchargeIntoTable(rebasedExcelData, modalAdj);
        }
示例#28
0
        public ProjectionDetailTab(DataTable finalCns, Excel.Workbook oWB, string prjNme)
        {
            this.fnlCns = finalCns;
            this.projectionNameOfSummaryTabs = prjNme;
            this.shts = (Excel.Sheets)oWB.Sheets;
            this.oSheet = (Excel.Worksheet)this.shts.Add();

            SetupSheetNameAndLocationInWorkbook();
            OutputAndFormatRateGridAndSubsidy();
            FinalizeSheetName();

        }
示例#29
0
        public ProjectionRatesTab(Excel.Workbook oWB, DataTable newInputPrm, string prjNme)
        {

            this.projectionNameOfSummaryTabs = prjNme;
            this.shts = (Excel.Sheets)oWB.Sheets;
            this.oSheet = (Excel.Worksheet)this.shts.Add();
            this.nwPrm = newInputPrm;

            SetupSheetNameAndLocationInWorkbook();
            OutputAndFormatRateGridAndSubsidy();
            FinalizeSheetName();
        }
示例#30
0
        private static void Default_CreateInstance(Core sender, Core.OnCreateInstanceEventArgs args)
        {
            // we replace all Worksheet instances with MyCustomWorksheet

            Excel.Worksheet sheet = args.Instance as Excel.Worksheet;
            if (null != sheet)
            {
                args.Replace = typeof(MyCustomWorksheet);
            }

            // keep in your mind: args.Instance.DisposeChildInstances() is called after this event trigger so dont handle your business logic here
        }
示例#31
0
 private static void AddExcelHeadingText(Excel.Worksheet sheet)
 {
     sheet.Cells[1, 1].Font.Bold  = true;
     sheet.Cells[1, 2].Font.Bold  = true;
     sheet.Cells[1, 3].Font.Bold  = true;
     sheet.Cells[1, 4].Font.Bold  = true;
     sheet.Cells[1, 5].Font.Bold  = true;
     sheet.Cells[1, 6].Font.Bold  = true;
     sheet.Cells[1, 7].Font.Bold  = true;
     sheet.Cells[1, 8].Font.Bold  = true;
     sheet.Cells[1, 9].Font.Bold  = true;
     sheet.Cells[1, 10].Font.Bold = true;
 }
        public ProjectionAssumptionResults(InputOptionsPage inputOptions, Excel.Workbook oWB, string prjNme)
        {
            this.inputOptions = inputOptions;
            this.projectionNameOfSummaryTabs = prjNme;
            this.shts = (Excel.Sheets)oWB.Sheets;
            this.oSheet = (Excel.Worksheet)this.shts.Add();

            SetupSheetNameAndLocationInWorkbook();
            OutputAndFormatInputAssumptions();

            FinalizeSheetName();

        }
示例#33
0
        public TestResult DoTest()
        {
            Excel.Application application = null;
            DateTime          startTime   = DateTime.Now;

            try
            {
                application               = new NetOffice.ExcelApi.Application();
                application.Visible       = true;
                application.DisplayAlerts = false;
                application.Workbooks.Add();
                Excel.Worksheet sheet = application.Workbooks[1].Sheets[1] as Excel.Worksheet;

                Office.COMAddIn addin = (from a in application.COMAddIns where a.ProgId == "NOTestsMain.ExcelTestAddinCSharp" select a).FirstOrDefault();
                if (null == addin || null == addin.Object)
                {
                    return(new TestResult(false, DateTime.Now.Subtract(startTime), "NOTestsMain.ExcelTestAddinCSharp or addin.Object not found.", null, ""));
                }

                bool   addinStatusOkay  = false;
                string errorDescription = string.Empty;
                if (null != addin.Object)
                {
                    COMObject addinProxy = new COMObject(addin.Object);
                    addinStatusOkay  = (bool)Invoker.Default.PropertyGet(addinProxy, "StatusOkay");
                    errorDescription = (string)Invoker.Default.PropertyGet(addinProxy, "StatusDescription");
                    addinProxy.Dispose();
                }

                if (addinStatusOkay)
                {
                    return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, ""));
                }
                else
                {
                    return(new TestResult(false, DateTime.Now.Subtract(startTime), string.Format("NOTestsMain.ExcelTestAddinCSharp Addin Status {0}", errorDescription), null, ""));
                }
            }
            catch (Exception exception)
            {
                return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, ""));
            }
            finally
            {
                if (null != application)
                {
                    application.Quit();
                    application.Dispose();
                }
            }
        }
示例#34
0
 private static void Thread2Method(object mre)
 {
     Excel.Worksheet sheet = _application.ActiveSheet as Excel.Worksheet;
     foreach (Excel.Range range in sheet.Range("A1:B200"))
     {
         Excel.Workbook book = range.Application.ActiveWorkbook;
         foreach (object item in book.Sheets)
         {
             Excel.Worksheet otherSheet = item as Excel.Worksheet;
             Excel.Range     rng        = otherSheet.Cells[1, 1];
         }
     }
     (mre as ManualResetEvent).Set();
 }
示例#35
0
        public static void runScenarioSummary()
        {
            runCalcAction( ()=>{
                shts = oWB.Worksheets;
                oSheet = (Worksheet) shts["Projection Input Options"];
                InputOptionsPage userOptionsAndVerification = new InputOptionsPage(oSheet);

                RatingGroups groups = new RatingGroups("Rating Groups", true);
                PriorSurcharge priorSurcharge = new PriorSurcharge("Rating Groups", userOptionsAndVerification.modalAdj);
                NewSurcharge newSurcharge = new NewSurcharge("Rating Groups", userOptionsAndVerification.modalAdj);
                PriorPremiums priorPremiums = new PriorPremiums("Prior Premiums");
                NewPremiums newPremiums = new NewPremiums("New Premiums");
                Census census = new Census("Census");

                System.Data.DataTable rfTableMultipliers = groups.RFactors;
                System.Data.DataTable newPrm = Utility.ConvertListToDataTable(newPremiums.NewPrmLst);
                System.Data.DataTable oldPrm = Utility.ConvertListToDataTable(priorPremiums.OldPrmLst);
                newPrm = Utility.mdlPrm(newPrm, userOptionsAndVerification.modalAdj);
                oldPrm = Utility.mdlPrm(oldPrm, userOptionsAndVerification.modalAdj);
                System.Data.DataTable cns = Utility.ConvertListToDataTable(census.CnLst);
                List<System.Data.DataTable> prSurChrg = priorSurcharge.PSCharge;
                List<System.Data.DataTable> nwSurChrg = newSurcharge.NSCharge;


                //Code works in following way
                //1) optimalCarrierRates chooses best CARRIER rates for MIGRATION ONLY
                //2) ratesToAnchorOffOf is the CARRIER rates to anchor off of (this could be the same as 1) or it could be the second lowest cost carrier)
                //      these are all metallic levels at this point, the anchor plan level is selected later                    
                //3) optimalRatesAndFactors joins the subsidy factors to the optimal rates
                //4) anchoredPlans takes ratesToAnchorOffOf and only keeps the correct metallic level that the user selected
                //5) nwPrm uses the Anchored plans, calcs the subsidy based on the user selected subsidy option and then applies this to the optimalCarrierRates
                //6) fnlCns migrates the population to the correct plan level, then attaches surcharge amounts to the premiums
                //7) finalRateGrid simply attaches the non lowest cost carriers to the rates to provide a complete set of carrier rates. These are treated as a 
                //      buy up with the EE shouldering any burden of buying a non anchored plan.
                //8) prjTb produces exhibit output tabs
                //9) sendCalcLog logs the model use with the model logger

                System.Data.DataTable optimalCarrierRates = FindOptimalCarrierPremiums.FindPremiumCarrier(newPrm, userOptionsAndVerification.defaultAnchorPlan);  //Finds optimal rates
                System.Data.DataTable ratesToAnchorOffOf = FindOptimalCarrierPremiums.FindPremiumCarrier(newPrm, userOptionsAndVerification.ancPln);     //Allows the anchored plan to be different than optimal carrier
                System.Data.DataTable optimalRatesAndFactors = Utility.Join(rfTableMultipliers, optimalCarrierRates);     //Join on factors to optimal rates
                System.Data.DataTable anchoredPlans = AnchorPlans.GetAnchorPlans(ratesToAnchorOffOf, userOptionsAndVerification.ancPln, groups.baselineCoverageTier);     //Get anchor plan and join onto optimal rates and factors
                System.Data.DataTable nwPrm = SubsidyCalculation.CalcBaseSubsidy(optimalRatesAndFactors, anchoredPlans, userOptionsAndVerification);   //calculate Subsidy
                System.Data.DataTable fnlCns = SurchargeCombine.SurChrgCombine(MigrationEngine.mrgCnsPrm(cns, oldPrm, nwPrm, ref userOptionsAndVerification), prSurChrg, nwSurChrg);
                System.Data.DataTable finalRateGrid = Utility.CreateCombinedPremiumExhibit(newPrm, nwPrm);

                ProjectionResults.prjTb(fnlCns, finalRateGrid, userOptionsAndVerification, oWB);
                AxCalcEngineAPI.sendCalcLog(userOptionsAndVerification, urlName, modVersion);
            });
        }
示例#36
0
        private void UseColorUtils()
        {
            // Colors in excel use a double representation
            // NetOffice color utils help to deal with them

            if (Application.Workbooks.Count > 0 && Application.Workbooks[1].Worksheets.Count > 0)
            {
                Excel.Worksheet sheet = Application.Workbooks[1].Worksheets[1] as Excel.Worksheet;

                double setColor = Utils.Color.ToDouble(Color.Red);
                sheet.Range("A1:B4").Interior.Color = setColor;

                Color getColor = Utils.Color.ToColor(sheet.Range("A1:B4").Interior.Color);
            }
        }
示例#37
0
        public TestResult DoTest()
        {
            Excel.Application application = null;
            DateTime          startTime   = DateTime.Now;

            try
            {
                // start excel and turn off msg boxes
                application = COMObject.Create <Excel.Application>(COMObjectCreateOptions.CreateNewCore);
                application.DisplayAlerts = false;

                // add a new workbook
                Excel.Workbook  workBook  = application.Workbooks.Add();
                Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];

                workSheet.Cells[1, 1].Value = "these sample shapes was dynamicly created by code.";

                // create a star
                Excel.Shape starShape = workSheet.Shapes.AddShape(MsoAutoShapeType.msoShape32pointStar, 10, 50, 200, 20);

                // create a simple textbox
                Excel.Shape textBox = workSheet.Shapes.AddTextbox(MsoTextOrientation.msoTextOrientationHorizontal, 10, 150, 200, 50);
                textBox.TextFrame.Characters().Text = "text";
                textBox.TextFrame.Characters().Font.Size = 14;

                // create a wordart
                Excel.Shape textEffect = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect14, "WordArt", "Arial", 12,
                                                                        MsoTriState.msoTrue, MsoTriState.msoFalse, 10, 250);

                // create text effect
                Excel.Shape textDiagram = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect11, "Effect", "Arial", 14,
                                                                         MsoTriState.msoFalse, MsoTriState.msoFalse, 10, 350);

                return(new TestResult(true, DateTime.Now.Subtract(startTime), "", null, ""));
            }
            catch (Exception exception)
            {
                return(new TestResult(false, DateTime.Now.Subtract(startTime), exception.Message, exception, ""));
            }
            finally
            {
                if (null != application)
                {
                    application.Quit();
                    application.Dispose();
                }
            }
        }
示例#38
0
 public NewPremiums(string tbnme)
 {
     this.oSheet = (Worksheet) AxCalcEngine.AxCalcEngine.shts[tbnme];
     this.oSheet.Activate();
     int row = this.oSheet.UsedRange.Rows[this.oSheet.UsedRange.Rows.Count].Row;
     int column = this.oSheet.UsedRange.Columns[this.oSheet.UsedRange.Columns.Count].Column;
     object[,] objArray = (object[,]) this.oSheet.Range(this.oSheet.Cells[4, 1], this.oSheet.Cells[row, column]).get_Value(XlRangeValueDataType.xlRangeValueDefault);
     int num3 = column;
     int num4 = row - 3;
     for (int i = 1; i <= num4; i++)
     {
         this.newPrmRw = new List<object>();
         for (int k = 1; k <= num3; k++)
         {
             object item = objArray[i, k];
             if ((i == 1) && "Rating Band".Equals((string) item))
             {
                 this.hasRatingBand = true;
             }
             try
             {
                 this.newPrmRw.Add(item);
             }
             catch
             {
                 if (item == null)
                 {
                     throw new Exception("Unable to read new premiums: No records found.");
                 }
             }
         }
         this.newPrmLst.Add(this.newPrmRw);
     }
     int count = this.newPrmLst[0].Count;
     int num8 = 0;
     for (int j = 0; j < count; j++)
     {
         if ((j > 2) && (j < (count - 1)))
         {
             this.mergeCols.Columns.Add((string) this.newPrmLst[0][j]);
             num8++;
         }
     }
     if (!this.hasRatingBand)
     {
         throw new Exception("No Rating Band. If premiums vary by rating band, create column \"Rating Band\"");
     }
 }
示例#39
0
        public void RunExample()
        {
            // start excel and turn off msg boxes
            Excel.Application excelApplication = new Excel.Application();
            excelApplication.DisplayAlerts = false;

            // create a utils instance, not need for but helpful to keep the lines of code low
            CommonUtils utils = new CommonUtils(excelApplication);

            // add a new workbook
            Excel.Workbook  workBook  = excelApplication.Workbooks.Add();
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];

            // draw back color and perform the BorderAround method
            workSheet.Range("$B2:$B5").Interior.Color = utils.Color.ToDouble(Color.DarkGreen);
            workSheet.Range("$B2:$B5").BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic);

            // draw back color and border the range explicitly
            workSheet.Range("$D2:$D5").Interior.Color = utils.Color.ToDouble(Color.DarkGreen);
            workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble;
            workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Weight    = 4;
            workSheet.Range("$D2:$D5").Borders[XlBordersIndex.xlInsideHorizontal].Color     = utils.Color.ToDouble(Color.Black);

            workSheet.Cells[1, 1].Value = "We have 2 simple shapes created.";

            string workbookFile = null;

            if (workSheet.EntityIsAvailable("ExportAsFixedFormat"))
            {
                // save the sheet as PDF
                workbookFile = System.IO.Path.Combine(HostApplication.RootDirectory, "Example10.pdf");
                workSheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, workbookFile, XlFixedFormatQuality.xlQualityStandard);
            }
            else
            {
                // we are sorry - pdf export is not supported in Excel 2003 or below
                workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example10", Excel.Tools.DocumentFormat.Normal);
                workBook.SaveAs(workbookFile);
            }

            // close excel and dispose reference
            excelApplication.Quit();
            excelApplication.Dispose();

            // show dialog for the user(you!)
            HostApplication.ShowFinishDialog(null, workbookFile);
        }
示例#40
0
        static void Main(string[] args)
        {
            Console.WriteLine("Write 1 million cells in excel.");

            NetOffice.Settings.Default.PerformanceTrace.Alert += new NetOffice.PerformanceTrace.PerformanceAlertEventHandler(PerformanceTrace_Alert);
            NetOffice.Settings.Default.PerformanceTrace["ExcelApi"].Enabled    = true;
            NetOffice.Settings.Default.PerformanceTrace["ExcelApi"].IntervalMS = 20;

            Excel.Application application = new Excel.ApplicationClass();

            application.DisplayAlerts  = false;
            application.Interactive    = false;
            application.ScreenUpdating = false;

            application.Workbooks.Add();

            Excel.Worksheet workSheet  = (Excel.Worksheet)application.Workbooks[1].Worksheets[1];
            Excel.Range     rangeCells = workSheet.Cells;

            // row
            int      counter   = 0;
            DateTime startTime = DateTime.Now;

            for (int i = 1; i <= 10000; i++)
            {
                // column
                for (int y = 1; y <= 100; y++)
                {
                    Excel.Range range = rangeCells[i, y];
                    range.Value = "TestValue";
                    range.Dispose();
                    counter++;
                }
                if (i % 100 == 0)
                {
                    Console.WriteLine("{0} Cells written. Time elapsed: {1}", counter, DateTime.Now - startTime);
                }
            }

            // quit and dispose
            application.Quit();
            application.Dispose();

            Console.WriteLine("Done!");
        }
示例#41
0
        public static int Open()
        {
            int return_code = 0;

            string complete_filename = FilePath + "\\" + FileName + fileExt;
            try
            {
                System.IO.File.Exists(complete_filename);
                workBook = excelApplication.Workbooks.Open(complete_filename);
                workSheet = (_Excel.Worksheet)workBook.Worksheets[1];
                return_code = 0;
            }
            catch
            {
                return_code = 9;
            }
            return return_code;
        }
示例#42
0
        static void Main(string[] args)
        {
            Console.WriteLine("NetOffice Release Performance Test - 15000 Cells.");
            Console.WriteLine("Write simple text, change Font, NumberFormat, WrapText and add a comment.");

            // start excel, and get a new sheet reference
            Excel.Application excelApplication = CreateExcelApplication();
            Excel.Worksheet   sheet            = excelApplication.Workbooks.Add().Worksheets.Add() as Excel.Worksheet;

            // do test 10 times
            List <TimeSpan> timeElapsedList = new List <TimeSpan>();

            for (int i = 1; i <= 10; i++)
            {
                sheet.UsedRange.ClearComments();
                DateTime timeStart = DateTime.Now;
                for (int y = 1; y <= 15000; y++)
                {
                    string      rangeAdress = "$A" + y.ToString();
                    Excel.Range cellRange   = sheet.get_Range(rangeAdress);
                    cellRange.Value        = "value";
                    cellRange.Font.Name    = "Verdana";
                    cellRange.NumberFormat = "@";
                    cellRange.WrapText     = true;
                    cellRange.AddComment("Sample Comment");
                }
                TimeSpan timeElapsed = DateTime.Now - timeStart;

                // display info and dispose references
                Console.WriteLine("Time Elapsed: {0}", timeElapsed);
                timeElapsedList.Add(timeElapsed);
                sheet.DisposeChildInstances();
            }

            // display info & log to file
            TimeSpan timeAverage = AppendResultToLogFile(timeElapsedList, "Test3-NetOffice.log");

            Console.WriteLine("Time Average: {0}{1}Press any key...", timeAverage, Environment.NewLine);
            Console.Read();

            // release & quit
            excelApplication.Quit();
            excelApplication.Dispose();
        }
示例#43
0
        public void Run()
        {
            // start application
            Excel.Application application = new Excel.Application();
            application.DisplayAlerts = false;

            // create new Workbook
            Excel.Workbook  book  = application.Workbooks.Add();
            Excel.Worksheet sheet = (Excel.Worksheet)book.Worksheets[1];
            Excel.Range     range = sheet.Cells[1, 1];

            // Style is defined as Variant in Excel and represents as object in NetOffice
            // You can cast them at runtime without problems
            Excel.Style style = (Excel.Style)range.Style;

            // variant types can be a scalar type at runtime
            // another example way to use is
            if (range.Style is string)
            {
                string myStyle = range.Style as string;
            }
            else if (range.Style is Excel.Style)
            {
                Excel.Style myStyle = (Excel.Style)range.Style;
            }

            // Name, Bold, Size are bool but defined as Variant and also converted to object
            style.Font.Name = "Arial";
            style.Font.Bold = true;
            style.Font.Size = 14;


            // Please note: the reason for the most variant definition is a more flexible value set.
            // the Style property from Range returns always a Style object
            // but if you have a new named style created with the name "myStyle" you can set range.Style = myNewStyleObject; or range.Style = "myStyle"
            // this kind of flexibility is the primary reason for Variants in Office
            // in any case, you dont lost the COM Proxy management from NetOffice for Variants.

            // quit & dipose
            application.Quit();
            application.Dispose();

            _hostApplication.ShowFinishDialog();
        }
示例#44
0
        public void Convert(Excel.Worksheet worksheet, IDelimitedTextWriter textWriter)
        {
            using (Excel.Range usedRange = worksheet.UsedRange)
            {
                Int32 rowCount;
                using (Excel.Range rows = usedRange.Rows)
                    rowCount = rows.Count;


                Int32 columnCount;
                using (Excel.Range columns = usedRange.Columns)
                    columnCount = columns.Count;

                for (Int32 rowIndex = 1; rowIndex <= rowCount; rowIndex += 1)
                {
                    Int32 rightColumn = 0;

                    for (Int32 columnIndex = 1; columnIndex <= columnCount; columnIndex += 1)
                    {
                        if (rowIndex > 1 && columnIndex == 1)
                        {
                            textWriter.EndRecord();
                        }
                        else if (columnIndex > 1)
                        {
                            textWriter.EndField();
                        }

                        using (Excel.Range cellRange = (Excel.Range)usedRange.Cells[rowIndex, columnIndex])
                        {
                            String text = (String)cellRange.Text;
                            textWriter.Write(text);

                            if (String.IsNullOrEmpty(text) == false)
                            {
                                rightColumn = columnIndex;
                            }
                        }
                    }

                    this.OnRowConverted(rowIndex, rowCount);
                }
            }
        }
示例#45
0
        public void RunExample()
        {
            // start excel and turn off msg boxes
            Excel.Application excelApplication = new Excel.Application();
            excelApplication.DisplayAlerts = false;

            // create a utils instance, not need for but helpful to keep the lines of code low
            CommonUtils utils = new CommonUtils(excelApplication);

            // add a new workbook
            Excel.Workbook  workBook  = excelApplication.Workbooks.Add();
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];

            workSheet.Cells[1, 1].Value = "NetOffice Excel Example 04";

            // create a star
            Excel.Shape starShape = workSheet.Shapes.AddShape(MsoAutoShapeType.msoShape32pointStar, 10, 50, 200, 20);

            // create a simple textbox
            Excel.Shape textBox = workSheet.Shapes.AddTextbox(MsoTextOrientation.msoTextOrientationHorizontal, 10, 150, 200, 50);
            textBox.TextFrame.Characters().Text = "text";
            textBox.TextFrame.Characters().Font.Size = 14;

            // create a wordart
            Excel.Shape textEffect = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect14, "WordArt", "Arial", 12,
                                                                    MsoTriState.msoTrue, MsoTriState.msoFalse, 10, 250);

            // create text effect
            Excel.Shape textDiagram = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect11, "Effect", "Arial", 14,
                                                                     MsoTriState.msoFalse, MsoTriState.msoFalse, 10, 350);

            // save the book
            string workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example04", DocumentFormat.Normal);

            workBook.SaveAs(workbookFile);

            // close excel and dispose reference
            excelApplication.Quit();
            excelApplication.Dispose();

            // show end dialog
            HostApplication.ShowFinishDialog(null, workbookFile);
        }
示例#46
0
        public TestSheetParser(Excel.Worksheet sheet, string headerTableName, string actionsTableName, string checksTableName, SheetReport report)
        {
            this.report = report;
            this.sheet = sheet;
                        if (!Regex.IsMatch(sheet.Name, PR_TEST_PREFIX + ".*"))
                throw new FormatException(String.Format("Sheet name doesn't comply with naming rules (begins with \"{0}\").", PR_TEST_PREFIX));

            try
            {
                logger.Debug(String.Format("Trying to retrieve action table \"{0}\".", actionsTableName));
                loActionsTable = sheet.ListObjects[actionsTableName];

                actionTableName = String.Format("'{0}'!{1}", sheet.Name, actionsTableName);
                checkTableName = String.Format("'{0}'!{1}", sheet.Name, checksTableName);

                logger.Debug(String.Format("Extracting columns for action table."));
                lcActionsTableColumns = loActionsTable.ListColumns;
            }
            catch (Exception ex)
            {
                logger.Error(String.Format("Action table \"{0}\" retrieval has failed.", actionsTableName), ex);
                throw new FormatException(String.Format("Action table \"{0}\" retrieval has failed.", actionsTableName));
            }

            try
            {
                logger.Debug(String.Format("Trying to retrieve test table \"{0}\".", checksTableName));
                loChecksTable = sheet.ListObjects[checksTableName];

                logger.Debug(String.Format("Extracting columns for checks table."));
                lcChecksTableColumns = loChecksTable.ListColumns;
            }
            catch (Exception ex)
            {
                logger.Error(String.Format("Check table \"{0}\" retrieval has failed.", checksTableName), ex);
                throw new FormatException(String.Format("Check table \"{0}\" retrieval has failed.", checksTableName));
            }

            if(lcActionsTableColumns.Count != lcChecksTableColumns.Count)
                throw new FormatException(String.Format("Action ({0} columns) and check ({1} columns)  tables has not same number of columns", lcActionsTableColumns.Count, lcChecksTableColumns.Count));
        }
示例#47
0
        public Census(string tbnme)
        {
            this.oSheet = (Worksheet) AxCalcEngine.shts[tbnme];
            this.oSheet.Activate();
            objArray = ExcelUtility.PullInExcelData(4, 1, oSheet);

            this.colNum = objArray.GetUpperBound(1);
            this.rowNum = objArray.GetUpperBound(0);


            SetTotalColumns();


            for (int i = 1; i <= this.rowNum; i++)
            {
                if (EndAddingRows(i) == true)
                {
                    break;
                }
                AssignFieldInfo(i);
            }
        }
示例#48
0
 public PriorPremiums(string tbnme)
 {
     this.oSheet = (Worksheet) AxCalcEngine.AxCalcEngine.shts[tbnme];
     this.oSheet.Activate();
     int row = this.oSheet.UsedRange.Rows[this.oSheet.UsedRange.Rows.Count].Row;
     int column = this.oSheet.UsedRange.Columns[this.oSheet.UsedRange.Columns.Count].Column;
     object[,] objArray = (object[,]) this.oSheet.Range(this.oSheet.Cells[4, 1], this.oSheet.Cells[row, column]).get_Value(XlRangeValueDataType.xlRangeValueDefault);
     int num3 = column;
     int num4 = row - 3;
     for (int i = 1; i <= num4; i++)
     {
         this.oldPrmRw = new List<object>();
         for (int j = 1; j <= num3; j++)
         {
             object item = objArray[i, j];
             if (item == null)
             {
                 throw new Exception("Unable to read prior premiums: No records found.");
             }
             this.oldPrmRw.Add(item);
         }
         this.oldPrmLst.Add(this.oldPrmRw);
     }
 }
示例#49
0
 private void worker_DoWork(object sender, DoWorkEventArgs e)
 {
     worksheet = LoopWorksheet[CurrentLoopIndex];
     CreateWorkSheet();
 }
 public void CambiarHoja(int indice)
 {
     Hoja = (Excel.Worksheet)Libro.Sheets[indice];
 }
示例#51
0
        public static Object[,] PullInExcelData(int startRow, int startColumn, Worksheet oSheet)
        {
            int row = oSheet.UsedRange.Rows[oSheet.UsedRange.Rows.Count].Row;
            int column = oSheet.UsedRange.Columns[oSheet.UsedRange.Columns.Count].Column;

            object[,] objArray = (object[,]) oSheet.Range(oSheet.Cells[startRow, startColumn], oSheet.Cells[row, column])
                .get_Value(XlRangeValueDataType.xlRangeValueDefault);

            return objArray;
        }
示例#52
0
        internal void CreateWorkSheet(Worksheet worksheet)
        {
            try
            {
                worksheet.DisplayRightToLeft = false;
                worksheet.Name = "Loop" + channekNumber;
                worksheet.Range("B1:D1").Font.Bold = true;
                worksheet.Range("B1").Value = "loop" + channekNumber;
                worksheet.Range("C1").Value = "x";
                worksheet.Range("D1").Value = "y";

                worksheet.Range("A1").EntireColumn.ColumnWidth = 8.38;
                worksheet.Range("B1").EntireColumn.ColumnWidth = 11.25;
                worksheet.Range("C1").EntireColumn.ColumnWidth = 11.25;
                worksheet.Range("D1").EntireColumn.ColumnWidth = 11.25;

                worksheet.Range("A1:D50").HorizontalAlignment = XlHAlign.xlHAlignCenter;

                List<System.Windows.Point> OrderPoints = Points.OrderBy(x => x.X).ToList();
                List<System.Windows.Point> OrderDetectorPoints = DetectorPoints.OrderBy(x => x.X).ToList();

                worksheet.Range("C1:D" + (Freqs.Length + 1)).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic);
                worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlDouble;
                worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDouble;
                worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideVertical].Weight = 4;
                worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideHorizontal].Weight = 4;
                worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideVertical].Color = ToDouble(Color.Black);
                worksheet.Range("C1:D" + (Freqs.Length + 1)).Borders[XlBordersIndex.xlInsideHorizontal].Color = ToDouble(Color.Black);

                int startrow = 2;
                int i;
                for (i = 0; i < Freqs.Length; i++)
                {
                    worksheet.Range("A" + (i + startrow)).Value = "f" + (i + 1);
                    worksheet.Range("B" + (i + startrow)).Value = Freqs[i];
                    worksheet.Range("C" + (i + startrow)).Value = Math.Round(OrderPoints[i].X, 2);
                    worksheet.Range("D" + (i + startrow)).Value = Math.Round(OrderPoints[i].Y, 2);

                    worksheet.Range("F" + (i + startrow)).Value = Math.Round(OrderDetectorPoints[i].X, 2);
                    worksheet.Range("G" + (i + startrow)).Value = Math.Round(OrderDetectorPoints[i].Y, 2);
                }

                startrow = i + 3;
                worksheet.Range("A" + (startrow) + ":C" + (Freqs.Length + startrow)).Borders.LineStyle = XlLineStyle.xlContinuous;
                worksheet.Range("A" + (startrow) + ":C" + (Freqs.Length + startrow)).Borders.Weight = 2d;
                worksheet.Range("B" + (startrow)).Value = "Ltot";
                worksheet.Range("C" + (startrow)).Value = "L";
                startrow++;
                for (i = 0; i < Points.Length; i++)
                {
                    worksheet.Range("A" + (i + startrow)).Value = (i + 1);
                    worksheet.Range("B" + (i + startrow)).Value = Math.Round(Points[i].X, 2);
                    worksheet.Range("C" + (i + startrow)).Value = Math.Round(Points[i].Y, 2);
                }
                startrow += i + 2;
                worksheet.Range("A2:A" + startrow).HorizontalAlignment = XlHAlign.xlHAlignLeft;

                worksheet.Range("A" + (startrow) + ":C" + (2 + startrow)).Borders.LineStyle = XlLineStyle.xlContinuous;
                worksheet.Range("A" + (startrow) + ":C" + (2 + startrow)).Borders.Weight = 2d;

                worksheet.Range("A" + startrow + ":C" + startrow).Merge();
                worksheet.Range("A" + startrow + ":C" + startrow).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
                worksheet.Range("A" + startrow + ":C" + startrow).Value = PolinomFunction1;
                startrow++;
                worksheet.Range("A" + startrow).Value = "a1";
                worksheet.Range("B" + startrow).Value = "b1";
                worksheet.Range("C" + startrow).Value = "c1";
                startrow++;
                worksheet.Range("A" + startrow).Value = A1;
                worksheet.Range("B" + startrow).Value = B1;
                worksheet.Range("C" + startrow).Value = C1;

                startrow++;
                startrow++;
                worksheet.Range("A" + (startrow) + ":C" + (2 + startrow)).Borders.LineStyle = XlLineStyle.xlContinuous;
                worksheet.Range("A" + (startrow) + ":C" + (2 + startrow)).Borders.Weight = 2d;

                worksheet.Range("A" + startrow + ":C" + startrow).Merge();
                worksheet.Range("A" + startrow + ":C" + startrow).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
                worksheet.Range("A" + startrow + ":C" + startrow).Value = PolinomFunction2;
                startrow++;
                worksheet.Range("A" + startrow).Value = "a2";
                worksheet.Range("B" + startrow).Value = "b2";
                worksheet.Range("C" + startrow).Value = "c2";
                startrow++;
                worksheet.Range("A" + startrow).Value = A2;
                worksheet.Range("B" + startrow).Value = B2;
                worksheet.Range("C" + startrow).Value = C2;

                startrow++;
                startrow++;
                worksheet.Range("A" + (startrow) + ":B" + (2 + startrow)).Borders.LineStyle = XlLineStyle.xlContinuous;
                worksheet.Range("A" + (startrow) + ":B" + (2 + startrow)).Borders.Weight = 2d;

                worksheet.Range("A" + startrow + ":B" + startrow).Merge();
                worksheet.Range("A" + startrow + ":B" + startrow).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
                worksheet.Range("A" + startrow + ":B" + startrow).Value = LineFunction;
                startrow++;
                worksheet.Range("A" + startrow).Value = "k";
                worksheet.Range("B" + startrow).Value = "l";
                startrow++;
                worksheet.Range("A" + startrow).Value = K;
                worksheet.Range("B" + startrow).Value = L;

                ChartObject chart = ((ChartObjects)worksheet.ChartObjects()).Add(300, 0, 770, 500);

                chart.Chart.ChartType = XlChartType.xlXYScatterSmoothNoMarkers;
                chart.Chart.HasLegend = true;
                chart.Chart.Legend.Position = XlLegendPosition.xlLegendPositionRight;
                chart.Chart.Legend.Left = 680;
                chart.Chart.Legend.Top = 10;
                chart.Chart.Legend.Format.Fill.ForeColor.RGB = ColorTranslator.ToOle(Color.LightGray);
                chart.Chart.Legend.Border.Color = ColorTranslator.ToOle(Color.DarkGray);
                chart.Chart.HasTitle = true;
                chart.Chart.ChartTitle.Text = "Loop " + channekNumber;
                Axis axisy = (Axis)chart.Chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
                axisy.HasTitle = true;
                axisy.AxisTitle.Text = "L Loop";
                Axis axisx = (Axis)chart.Chart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
                axisx.HasTitle = true;
                axisx.AxisTitle.Text = "L Total";

                SeriesCollection seriesCollection = (SeriesCollection)chart.Chart.SeriesCollection();

                #region Calibration Point
                using (Series oSeries = seriesCollection.NewSeries())
                {
                    oSeries.XValues = worksheet.get_Range("C2", "C9");
                    oSeries.Values = worksheet.get_Range("D2", "D9");
                    oSeries.ChartType = XlChartType.xlXYScatter;
                    oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowLabel);
                    oSeries.HasDataLabels = true;
                    DataLabels labels = (DataLabels)oSeries.DataLabels();
                    labels.Separator = " , ";
                    labels.ShowCategoryName = true;
                    labels.ShowValue = true;
                    oSeries.MarkerForegroundColor = ColorTranslator.ToOle(GetColor(CalibrateModel.Table1Color));
                    oSeries.MarkerBackgroundColor = ColorTranslator.ToOle(GetColor(CalibrateModel.Table1Color));
                    oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleCircle;
                    oSeries.MarkerSize = 10;
                    oSeries.Name = "Calibration";
                }
                #endregion
                #region Measurements Point
                using (Series oSeries = seriesCollection.NewSeries())
                {
                    oSeries.XValues = worksheet.get_Range("F2", "F9");
                    oSeries.Values = worksheet.get_Range("G2", "G9");
                    oSeries.ChartType = XlChartType.xlXYScatter;
                    oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone);
                    oSeries.MarkerForegroundColor = ColorTranslator.ToOle(GetColor(CalibrateModel.Table2Color));
                    oSeries.MarkerBackgroundColor = ColorTranslator.ToOle(GetColor(CalibrateModel.Table2Color));
                    oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleDiamond;
                    oSeries.MarkerSize = 10;
                    oSeries.Name = "Measurements";
                }
                 #endregion
                #region Line
                using (Series oSeries = seriesCollection.NewSeries())
                {
                    oSeries.XValues = worksheet.get_Range("C2", "C3");
                    oSeries.Values = worksheet.get_Range("D2", "D3");
                    oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone);
                    oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone;
                    oSeries.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
                    oSeries.Format.Line.ForeColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F3Color));
                    oSeries.Format.Line.BackColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F3Color));
                    oSeries.Name = LineFunctionNumber.ToString();
                }
                #endregion
                #region F1
                startrow = 100;
                foreach (var item in Calc1000Ponts(A1, B1, C1, Gragh1))
                {
                    worksheet.Range("A" + (startrow)).Value = Math.Round(item.X, 2);
                    worksheet.Range("B" + (startrow)).Value = Math.Round(item.Y, 2);
                    startrow++;
                }

                using (Series oSeries = seriesCollection.NewSeries())
                {
                    oSeries.XValues = worksheet.get_Range("A100", "A" + startrow);
                    oSeries.Values = worksheet.get_Range("B100", "B" + startrow);
                    oSeries.ChartType = XlChartType.xlXYScatterSmoothNoMarkers;
                    oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone);
                    oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone;
                    oSeries.Format.Line.ForeColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F1Color));
                    oSeries.Format.Line.BackColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F1Color));
                    oSeries.Name = PolinomFunction1Number.ToString();
                }
                #endregion
                #region F1
                startrow = 100;
                foreach (var item in Calc1000Ponts(A2, B2, C2, Gragh2))
                {
                    worksheet.Range("C" + (startrow)).Value = Math.Round(item.X, 2);
                    worksheet.Range("D" + (startrow)).Value = Math.Round(item.Y, 2);
                    startrow++;
                }
                using (Series oSeries = seriesCollection.NewSeries())
                {
                    oSeries.XValues = worksheet.get_Range("C100", "C" + startrow);
                    oSeries.Values = worksheet.get_Range("D100", "D" + startrow);
                    oSeries.ChartType = XlChartType.xlXYScatterSmoothNoMarkers;
                    oSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowNone);
                    oSeries.MarkerStyle = XlMarkerStyle.xlMarkerStyleNone;
                    oSeries.Format.Line.ForeColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F2Color));
                    oSeries.Format.Line.BackColor.RGB = ColorTranslator.ToOle(GetColor(CalibrateModel.F2Color));
                    oSeries.Name = PolinomFunction2Number.ToString();
                }
                #endregion
                
                if (!System.IO.Directory.Exists(CalibrateModel.filespath))
                    System.IO.Directory.CreateDirectory(CalibrateModel.filespath);

                try
                {
                    chart.Chart.Export(CalibrateModel.filespath + @"ChartLoop" + channekNumber + ".jpeg", "JPEG");
                }
                catch (Exception xe)
                {
                    Console.WriteLine(xe.Message);
                }

            }
            catch
            {
                releaseObject(worksheet);
            }
        }
示例#53
0
 public static void prjTb(System.Data.DataTable fnlCns, System.Data.DataTable nwPrm, string prjNme)
 {
     string str;
     int num;
     DataRow row2;
     double num2;
     int num4;
     double num5;
     if (prjNme.Length > 0x17)
     {
         str = prjNme.Substring(0, 0x17);
     }
     else
     {
         str = prjNme;
     }
     List<string> list = new List<string>();
     foreach (DataColumn column in fnlCns.Columns)
     {
         if (((column.ColumnName == "Prior Premium") || (column.ColumnName == "Prior Subsidy")) || (column.ColumnName == "Prior EE Contribution"))
         {
             list.Add(column.ColumnName);
         }
         if (column.ColumnName.Contains("Prior") && column.ColumnName.Contains("Surcharge"))
         {
             list.Add(column.ColumnName);
         }
     }
     System.Data.DataTable table = new System.Data.DataTable();
     for (num = 0; num < list.Count; num++)
     {
         table.Columns.Add(list[num]);
     }
     foreach (DataRow row in fnlCns.Rows)
     {
         row2 = table.NewRow();
         foreach (DataColumn column in table.Columns)
         {
             row2[column.ColumnName] = row[column.ColumnName];
         }
         table.Rows.Add(row2);
     }
     List<double> list2 = new List<double>();
     foreach (DataColumn column in table.Columns)
     {
         num2 = 0.0;
         foreach (DataRow row in table.Rows)
         {
             num2 += double.Parse(row[column.ColumnName].ToString());
         }
         list2.Add(num2);
     }
     List<string> list3 = new List<string>();
     foreach (DataColumn column in fnlCns.Columns)
     {
         if (((column.ColumnName == "New Premium") || (column.ColumnName == "New Subsidy")) || (column.ColumnName == "New EE Contribution"))
         {
             list3.Add(column.ColumnName);
         }
         if (column.ColumnName.Contains("New") && column.ColumnName.Contains("Surcharge"))
         {
             list3.Add(column.ColumnName);
         }
     }
     System.Data.DataTable table2 = new System.Data.DataTable();
     for (num = 0; num < list3.Count; num++)
     {
         table2.Columns.Add(list3[num]);
     }
     foreach (DataRow row in fnlCns.Rows)
     {
         row2 = table2.NewRow();
         foreach (DataColumn column in table2.Columns)
         {
             row2[column.ColumnName] = row[column.ColumnName];
         }
         table2.Rows.Add(row2);
     }
     List<double> list4 = new List<double>();
     foreach (DataColumn column in table2.Columns)
     {
         num2 = 0.0;
         foreach (DataRow row in table2.Rows)
         {
             num2 += double.Parse(row[column.ColumnName].ToString());
         }
         list4.Add(num2);
     }
     bool flag = false;
     foreach (Worksheet worksheet in oWB.Worksheets)
     {
         if (worksheet.Name.Equals(str + " Summary"))
         {
             flag = true;
         }
     }
     if (flag)
     {
         throw new Exception("Projection with same name already exists");
     }
     oSheet = (Worksheet) shts.Add();
     oSheet.Name = str + " Summary";
     oSheet.Activate();
     int count = 0;
     count = oWB.Sheets.Count;
     oSheet.Move(null, shts[count]);
     NetOffice.ExcelApi.Range entireColumn = null;
     NetOffice.ExcelApi.Range range2 = null;
     for (num = 0; num < table.Columns.Count; num++)
     {
         oSheet.Cells[5, num + 1].Value = table.Columns[num].ColumnName;
         oSheet.Cells[5, num + 1].Interior.Color = Color.FromArgb(0xc5d9f1);
         oSheet.Cells[6, num + 1].Value = list2[num];
         oSheet.Cells[6, num + 1].HorizontalAlignment = HorizontalAlignment.Center;
         oSheet.Cells[6, num + 1].NumberFormat = "$ #,###,###.00";
         range2 = oSheet.Cells[6, num + 1];
         entireColumn = range2.EntireColumn;
         entireColumn.AutoFit();
     }
     for (num = 0; num < table2.Columns.Count; num++)
     {
         oSheet.Cells[8, num + 1].Value = table2.Columns[num].ColumnName;
         oSheet.Cells[8, num + 1].Interior.Color = Color.FromArgb(0xc5d9f1);
         oSheet.Cells[9, num + 1].Value = list4[num];
         oSheet.Cells[9, num + 1].HorizontalAlignment = HorizontalAlignment.Center;
         oSheet.Cells[9, num + 1].NumberFormat = "$ #,###,###.00";
         range2 = oSheet.Cells[6, num + 1];
         entireColumn = range2.EntireColumn;
         entireColumn.AutoFit();
     }
     oSheet.Cells[1, 1].Value = prjNme + " Summary";
     oSheet.Cells[1, 1].Font.Bold = true;
     oSheet.Cells[1, 1].Font.Size = 20;
     oSheet = (Worksheet) shts.Add();
     oSheet.Activate();
     count = oWB.Sheets.Count;
     oSheet.Move(null, shts[count]);
     oSheet.Name = str + " Rates";
     List<int> list5 = new List<int>();
     for (num = 0; num < nwPrm.Columns.Count; num++)
     {
         oSheet.Cells[4, num + 1].Value = nwPrm.Columns[num].ColumnName;
         oSheet.Cells[4, num + 1].Interior.Color = Color.FromArgb(0xc5d9f1);
         if ((nwPrm.Columns[num].ColumnName == "AV") || (nwPrm.Columns[num].ColumnName == "Factor"))
         {
             list5.Add(num);
         }
     }
     object[,] objArray = new object[nwPrm.Rows.Count, nwPrm.Columns.Count];
     for (num = 0; num < nwPrm.Rows.Count; num++)
     {
         num4 = 0;
         while (num4 < nwPrm.Columns.Count)
         {
             try
             {
                 objArray[num, num4] = double.Parse(nwPrm.Rows[num][num4].ToString());
             }
             catch
             {
                 objArray[num, num4] = nwPrm.Rows[num][num4];
             }
             num4++;
         }
     }
     NetOffice.ExcelApi.Range range3 = oSheet.Cells[5, 1];
     NetOffice.ExcelApi.Range range4 = oSheet.Cells[nwPrm.Rows.Count + 4, nwPrm.Columns.Count];
     oSheet.Range(range3, range4).Value = objArray;
     for (num = 0; num < nwPrm.Columns.Count; num++)
     {
         range2 = oSheet.Cells[4, num + 1];
         try
         {
             num5 = double.Parse(oSheet.Cells[5, num + 1].Value.ToString());
             if ((oSheet.Cells[4, num + 1].Value.ToString() != "AV") && (oSheet.Cells[4, num + 1].Value.ToString() != "Factor"))
             {
                 entireColumn = range2.EntireColumn;
                 entireColumn.NumberFormat = "$ #,###.00";
             }
             entireColumn.HorizontalAlignment = HorizontalAlignment.Center;
             entireColumn.AutoFit();
         }
         catch
         {
             entireColumn = range2.EntireColumn;
             entireColumn.HorizontalAlignment = HorizontalAlignment.Center;
             entireColumn.AutoFit();
         }
     }
     oSheet.Cells[1, 1].Value = prjNme + " New Rates and Contributions";
     oSheet.Cells[1, 1].Font.Bold = true;
     oSheet.Cells[1, 1].Font.Size = 20;
     oSheet = (Worksheet) shts.Add();
     oSheet.Activate();
     count = oWB.Sheets.Count;
     oSheet.Move(null, shts[count]);
     oSheet.Name = str + " Detail";
     for (num = 0; num < fnlCns.Columns.Count; num++)
     {
         oSheet.Cells[4, num + 1].Value = fnlCns.Columns[num].ColumnName;
         oSheet.Cells[4, num + 1].Interior.Color = Color.FromArgb(0xc5d9f1);
     }
     objArray = new object[fnlCns.Rows.Count, fnlCns.Columns.Count];
     for (num = 0; num < fnlCns.Rows.Count; num++)
     {
         for (num4 = 0; num4 < fnlCns.Columns.Count; num4++)
         {
             try
             {
                 objArray[num, num4] = double.Parse(fnlCns.Rows[num][num4].ToString());
             }
             catch
             {
                 objArray[num, num4] = fnlCns.Rows[num][num4];
             }
         }
     }
     range3 = oSheet.Cells[5, 1];
     range4 = oSheet.Cells[fnlCns.Rows.Count + 4, fnlCns.Columns.Count];
     oSheet.Range(range3, range4).Value = objArray;
     for (num = 0; num < fnlCns.Columns.Count; num++)
     {
         range2 = oSheet.Cells[4, num + 1];
         try
         {
             num5 = double.Parse(oSheet.Cells[5, num + 1].Value.ToString());
             entireColumn = range2.EntireColumn;
             if (num > 1)
             {
                 entireColumn.NumberFormat = "$ #,###.00";
             }
             entireColumn.HorizontalAlignment = HorizontalAlignment.Center;
             entireColumn.AutoFit();
         }
         catch
         {
             entireColumn = range2.EntireColumn;
             entireColumn.HorizontalAlignment = HorizontalAlignment.Center;
             entireColumn.AutoFit();
         }
     }
     oSheet.Cells[1, 1].Value = prjNme + " Detailed Census and Financial Information";
     oSheet.Cells[1, 1].Font.Bold = true;
     oSheet.Cells[1, 1].Font.Size = 20;
     oSheet = (Worksheet) shts[str + " Summary"];
     oSheet.Select();
 }
示例#54
0
 public static void runScenarioSummary()
 {
     runCalcAction(delegate {
         AxCalcEngineAPI.AxCalcEngineAPI.Initialize();
         shts = oWB.Worksheets;
         oSheet = (Worksheet) shts["Projection Input Options"];
         string ancPln = (string) oSheet.Range("anchorPlan").Value;
         string option = (string) oSheet.Range("subType").Value;
         string mgOpt = (string) oSheet.Range("migType").Value;
         string str4 = (string) oSheet.Range("projNme").Value;
         string inMode = (string) oSheet.Range("modal").Value;
         string outMode = (string) oSheet.Range("modalOut").Value;
         string str7 = (string) oSheet.Range("clientNme").Value;
         string str8 = oSheet.Range("clientID").Value.ToString();
         string str9 = "";
         double modalAdj = Utility.modalConvert(inMode, outMode);
         if ((string.IsNullOrEmpty(str7) || string.IsNullOrEmpty(str8)) || (str8.Length != 8))
         {
             throw new Exception("Enter valid client name and ID");
         }
         if (string.IsNullOrEmpty(str4))
         {
             throw new Exception("You must enter a projection name");
         }
         string displayName = "";
         try
         {
             displayName = AxCalcEngineAPI.AxCalcEngineAPI._user.DisplayName;
         }
         catch
         {
             throw new Exception("Establishing a Connection");
         }
         if (displayName.Equals("Offline User"))
         {
             throw new Exception("You must be connected to the Aon Network to run Ax Model");
         }
         RatingGroups groups = new RatingGroups("Rating Groups", true);
         PriorSurcharge surcharge = new PriorSurcharge("Rating Groups", modalAdj);
         NewSurcharge surcharge2 = new NewSurcharge("Rating Groups", modalAdj);
         PriorPremiums premiums = new PriorPremiums("Prior Premiums");
         NewPremiums premiums2 = new NewPremiums("New Premiums");
         Census census = new Census("Census");
         System.Data.DataTable first = groups.RFactors;
         System.Data.DataTable prm = Utility.convertListToDataTable(premiums2.NewPrmLst);
         System.Data.DataTable table3 = Utility.convertListToDataTable(premiums.OldPrmLst);
         prm = Utility.mdlPrm(prm, modalAdj);
         table3 = Utility.mdlPrm(table3, modalAdj);
         System.Data.DataTable cns = Utility.convertListToDataTable(census.CnLst);
         List<System.Data.DataTable> prSurChrg = surcharge.PSCharge;
         List<System.Data.DataTable> nwSurChrg = surcharge2.NSCharge;
         System.Data.DataTable second = Utility.findPremiumCarrier(prm, str9);
         System.Data.DataTable dt = Utility.findPremiumCarrier(prm, ancPln);
         System.Data.DataTable table7 = Utility.Join(first, second);
         System.Data.DataTable anc = Utility.getAnchorPlans(dt, ancPln, groups.bsCvTr);
         System.Data.DataTable nwPrm = Utility.calcBaseSubsidy(table7, anc, option, modalAdj);
         System.Data.DataTable fnlCns = Utility.SurChrgCombine(Utility.mrgCnsPrm(cns, table3, nwPrm, mgOpt), prSurChrg, nwSurChrg);
         groups = null;
         surcharge = null;
         surcharge2 = null;
         premiums = null;
         premiums2 = null;
         census = null;
         prjTb(fnlCns, nwPrm, str4);
         AxCalcEngineAPI.AxCalcEngineAPI.sendCalcLog(str7, str8, "http://axmodellogger.azurewebsites.net/Default.aspx", "AxModel_0.9");
     });
 }
示例#55
0
 public RatingGroups(string tbnme, bool rb)
 {
     Func<string, IEnumerable<string>> selector = null;
     Func<string, IEnumerable<string>> func2 = null;
     this.oSheet = (Worksheet) AxCalcEngine.AxCalcEngine.shts[tbnme];
     this.oSheet.Activate();
     this.i = 0;
     this.j = 0;
     int count = 0;
     int num2 = 0;
     int num3 = 0;
     while (!string.IsNullOrEmpty((string) this.oSheet.Range("A4").Offset(0, this.i).Value))
     {
         if (!this.oSheet.Range("A4").Offset(0, this.i + 1).Value.ToString().Equals("Addition", StringComparison.OrdinalIgnoreCase))
         {
             if (string.IsNullOrEmpty((string) this.oSheet.Range("A4").Offset(0, this.i + 1).Value))
             {
                 throw new Exception("All Rating Factors Must Have a Corresponding Multiplier");
             }
             this.hldNme = (string) this.oSheet.Range("A4").Offset(0, this.i).Value;
             this.hdr.Add(this.hldNme);
             this.j = 0;
             while (!string.IsNullOrEmpty((string) this.oSheet.Range("A4").Offset(this.j + 1, this.i).Value))
             {
                 if ((this.hldNme == "Coverage Tier") && (this.j == 0))
                 {
                     this.bsLneCvTr = (string) this.oSheet.Range("A4").Offset(this.j + 1, this.i).Value;
                 }
                 if (this.oSheet.Range("A4").Offset(this.j + 1, this.i + 1).Value.ToString() == "")
                 {
                     throw new Exception("A Category Does Not Have A Multiplier Value");
                 }
                 this.j++;
             }
         }
         else
         {
             num3 += 2;
         }
         this.rwCnt.Add(this.j);
         this.i += 2;
     }
     this.i -= num3;
     num2 = ((this.i - 2) / 2) + 1;
     for (int i = 0; i < this.rwCnt.Count; i++)
     {
         if (i > 0)
         {
             this.intHld *= (int) this.rwCnt[i];
         }
         else
         {
             this.intHld = (int) this.rwCnt[i];
         }
     }
     this.rfCntChk = count;
     count = this.intHld + 1;
     List<System.Data.DataTable> list = new List<System.Data.DataTable>();
     this.i = 0;
     while (this.i < num2)
     {
         System.Data.DataTable table = new System.Data.DataTable();
         table.Columns.Add((string) this.oSheet.Range("A4").Offset(0, this.i * 2).Value, typeof(string));
         table.Columns.Add(((string) this.oSheet.Range("A4").Offset(0, this.i * 2).Value) + " mult", typeof(string));
         this.j = 0;
         while (this.j < ((int) this.rwCnt[this.i]))
         {
             table.Rows.Add(new object[] { this.oSheet.Range("A4").Offset(this.j + 1, this.i * 2).Value.ToString(), this.oSheet.Range("A4").Offset(this.j + 1, (this.i * 2) + 1).Value.ToString() });
             this.j++;
         }
         list.Add(table);
         this.i++;
     }
     List<string> list2 = new List<string>();
     this.i = 0;
     while (this.i < list.Count)
     {
         list2.Add(list[this.i].Columns[0].ColumnName);
         this.i++;
     }
     List<string> source = new List<string>();
     List<string> tempTbl = new List<string>();
     System.Data.DataTable table2 = new System.Data.DataTable();
     this.i = 0;
     while (this.i < list.Count)
     {
         string str;
         if (this.i == 0)
         {
             table2 = list[this.i];
             foreach (DataRow row in table2.Rows)
             {
                 str = row[0].ToString() + "Ǝ" + row[1].ToString();
                 source.Add(str);
             }
             table2 = list[this.i + 1];
             foreach (DataRow row in table2.Rows)
             {
                 str = "Ǝ" + row[0].ToString() + "Ǝ" + row[1].ToString();
                 tempTbl.Add(str);
             }
             if (selector == null)
             {
                 selector = a => from b in tempTbl select a + b;
             }
             source = source.SelectMany<string, string>(selector).ToList<string>();
         }
         else if (this.i != 1)
         {
             table2 = list[this.i];
             tempTbl = new List<string>();
             foreach (DataRow row in table2.Rows)
             {
                 str = "Ǝ" + row[0].ToString() + "Ǝ" + row[1].ToString();
                 tempTbl.Add(str);
             }
             if (func2 == null)
             {
                 func2 = a => from b in tempTbl select a + b;
             }
             source = source.SelectMany<string, string>(func2).ToList<string>();
         }
         this.i++;
     }
     List<string> item = new List<string>();
     List<List<string>> list5 = new List<List<string>>();
     this.i = 0;
     while (this.i < source.Count)
     {
         item = source[this.i].Split(new char[] { 'Ǝ' }).ToList<string>();
         list5.Add(item);
         this.i++;
     }
     List<double> list6 = new List<double>();
     this.i = 0;
     while (this.i < list5.Count)
     {
         double num5 = 1.0;
         this.j = 1;
         while (this.j < list5[this.i].Count)
         {
             num5 *= double.Parse(list5[this.i][this.j].ToString());
             this.j += 2;
         }
         list6.Add(num5);
         this.i++;
     }
     this.i = 0;
     while (this.i < list2.Count)
     {
         this.rfTbl.Columns.Add(list2[this.i]);
         this.i++;
     }
     List<List<string>> list8 = new List<List<string>>();
     this.i = 0;
     while (this.i < list5.Count)
     {
         List<string> list7 = new List<string>();
         this.j = 0;
         while (this.j < list5[this.i].Count)
         {
             list7.Add(list5[this.i][this.j]);
             this.j += 2;
         }
         list8.Add(list7);
         this.i++;
     }
     this.i = 0;
     while (this.i < list8.Count)
     {
         this.rfTbl.Rows.Add(list8[this.i].ToArray());
         this.i++;
     }
     tempTbl = new List<string>();
     foreach (DataColumn column in this.rfTbl.Columns)
     {
         tempTbl.Add(column.ColumnName);
     }
     this.i = 0;
     this.rfTbl.Columns.Add("Factor", typeof(double));
     foreach (DataRow row in this.rfTbl.Rows)
     {
         row["Factor"] = list6[this.i];
         this.i++;
     }
     count = this.rfTbl.Rows.Count;
     string[] columnNames = tempTbl.ToArray();
     DataView view = new DataView(this.rfTbl);
     if (view.ToTable(true, columnNames).Rows.Count != count)
     {
         throw new Exception("There are duplicate categories inside rating factors, your input should be checked");
     }
 }
示例#56
0
 public static void writeColumn(Worksheet xlWs, object[] Column, int ColumnNo, int startRow)
 {
     try
     {
         object[,] objArray = new object[Column.Length, 1];
         for (int i = 0; i < Column.Length; i++)
         {
             objArray[i, 0] = Column[i];
         }
         Range range = xlWs.Cells[startRow, ColumnNo];
         Range range2 = xlWs.Cells[startRow + objArray.GetUpperBound(0), ColumnNo];
         xlWs.Range(range, range2).Value2 = objArray;
         xlWs.DisposeChildInstances();
     }
     catch
     {
     }
 }
示例#57
0
 public static void writeRow(Worksheet xlWs, Dictionary<int, object> rowData, int tgtRow)
 {
     try
     {
         foreach (KeyValuePair<int, object> pair in rowData)
         {
             Range range = xlWs.Cells[tgtRow, pair.Key];
             range.Value2 = pair.Value;
         }
         xlWs.DisposeChildInstances();
     }
     catch
     {
     }
 }
示例#58
0
 private static void OpenWorkSheet()
 {
     // add a new workbook
     workBook = excelApplication.Workbooks.Add();
     workSheet = (_Excel.Worksheet)workBook.Worksheets[1];
 }
示例#59
-1
        public ProjectionSummaryTab(Tuple<List<string>, DataTable, List<double>, List<string>, DataTable, List<double>> projectionData,
            Excel.Workbook oWB, string prjNme)
        {
            this.projectionNameOfSummaryTabs = prjNme;
            this.shts = (Excel.Sheets)oWB.Sheets;
            this.oSheet = (Excel.Worksheet)this.shts.Add();

            SetupSheetNameAndLocationInWorkbook();
            OutputSummaryResultsAndFormat(projectionData.Item2, projectionData.Item3, 5);  //Output for Prior Year
            OutputSummaryResultsAndFormat(projectionData.Item5, projectionData.Item6, 8);  //Output for Current Year
            FinalizeSheetName();
        }