private void AddAction(Excel.Worksheet sheet, User user)
{
//get empty row
var emptyRow = sheet.UsedRange;
var col = sheet.Columns[1].Rows.Last();
}
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);
}
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);
}
}
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();
}
}
}
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();
}
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;
}
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();
}
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"));
}
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();
}
}
}
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);
}
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);
}
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);
}
}
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();
}
}
}
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);
}
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);
}
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);
}
}
}
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;
}
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;
}
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;
}
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);
}
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];
}
public NewPremiums(string tbnme)
{
this.oSheet = (Worksheet) AxCalcEngine.shts[tbnme];
this.oSheet.Activate();
object[,] objArray = ExcelUtility.PullInExcelData(startRow, startCol, this.oSheet);
ListifyNewPremiums(objArray);
RatingBandThrowException();
}
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;
}
}
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;
}
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();
}
}
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);
}
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();
}
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();
}
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
}
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();
}
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();
}
}
}
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();
}
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);
});
}
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);
}
}
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();
}
}
}
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\"");
}
}
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);
}
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!");
}
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;
}
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();
}
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();
}
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);
}
}
}
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);
}
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));
}
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);
}
}
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);
}
}
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
worksheet = LoopWorksheet[CurrentLoopIndex];
CreateWorkSheet();
}
public void CambiarHoja(int indice)
{
Hoja = (Excel.Worksheet)Libro.Sheets[indice];
}
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;
}
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);
}
}
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();
}
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");
});
}
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");
}
}
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
{
}
}
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
{
}
}
private static void OpenWorkSheet()
{
// add a new workbook
workBook = excelApplication.Workbooks.Add();
workSheet = (_Excel.Worksheet)workBook.Worksheets[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();
}
