Ejemplo n.º 1
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();
 }