static int GetSheetNumber(Tools.Workbook theWorkbook, string theWorksheetName) { int myNMax = theWorkbook.Worksheets.Count; int i = 1; while (i <= myNMax) { string myName = theWorkbook.Worksheets[i].Name; if (theWorksheetName == myName) { return(i); } i++; } return(0); }
private void OKButton_Click(object sender, EventArgs e) { if (Globals.ThisAddIn.Application.ActiveWorkbook != null) { Tools.Workbook myWorkbook = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook); Tools.Worksheet myWorksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveSheet); mvExcelGet.mParam[0].SetValuesWithCells(StdevInMeanRefEdit.Text, myWorksheet.Name, myWorkbook.Name); Globals.ThisAddIn.mAddInModel.AddOneCondMean(mvExcelGet); } else { Globals.ThisAddIn.mAddInModel = new cExcelModelClass(Globals.ThisAddIn.mAddInBackupModel); } Owner.Show(); Owner.Activate(); Owner.RemoveOwnedForm(this); Close(); }
private void OKBouton_Click(object sender, EventArgs e) { if (Globals.ThisAddIn.Application.ActiveWorkbook != null) { Tools.Workbook myWorkbook = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook); Tools.Worksheet myWorksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveSheet); //if (constBoolBox.Checked) mvExcelGet.mParam[0].SetValuesWithCells(ConstRefEdit.Text, myWorksheet.Name, myWorkbook.Name); mvExcelGet.mParam[1].SetValuesWithCells(ArchRefEdit.Text, myWorksheet.Name, myWorkbook.Name); mvExcelGet.mParam[2].SetValuesWithCells(GarchRefEdit.Text, myWorksheet.Name, myWorkbook.Name); mvExcelGet.mParam[3].SetValuesWithCells(BetaRefEdit.Text, myWorksheet.Name, myWorkbook.Name); Globals.ThisAddIn.mAddInModel.AddCondVar(mvExcelGet); } else { Globals.ThisAddIn.mAddInModel = new cExcelModelClass(Globals.ThisAddIn.mAddInBackupModel); } Owner.Show(); Owner.Activate(); Owner.RemoveOwnedForm(this); Close(); }
/// <summary> /// Application_WorkbookOpen Event will be called whenever a saved Workbook is Opened /// </summary> /// <param name="Wb">The Activeworkbook information is passed as parameter</param> void Application_WorkbookOpen(Excel.Workbook Wb) { try { ExcelTool.Workbook excelWorkbook = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook); List <string> sheetNames = new List <string>(); foreach (Excel.Worksheet sheet in excelWorkbook.Sheets) { sheetNames.Add(sheet.Name); } //if (sheetNames.Contains(clsInformation.PROMO_INPUT_TOOL)) //{ //} //Worksheet worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.Sheets[clsInformation.PROMO_INPUT_TOOL]); Worksheet worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.Sheets[sheetNames[1]]); if (worksheet != null) { int range = worksheet.Rows.Count;; if (range > 11) { ClsPromotions.promoUploadOnline(); FAST._verifyDownloadForUpload = true; } } FAST.updateControl(); } catch { } }
private void OKBoutton_Click(object sender, EventArgs e) { Tools.Workbook myWorkBook = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook); string myModelStr = this.ModeleRefEdit.Text; int myLength = myModelStr.Length; int myWorksheetNameIndex = myModelStr.IndexOf("!"); string myWorkSheetName; Tools.Worksheet myWorkSheet; if (myWorksheetNameIndex >= 0) { myWorkSheetName = myModelStr.Substring(0, myWorksheetNameIndex); myModelStr = myModelStr.Substring(myWorksheetNameIndex + 1, myLength - myWorksheetNameIndex - 1); myWorkSheet = myWorkBook.Worksheets[myWorkSheetName]; } else { myWorkSheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); } myWorkSheet.Range[myModelStr].Select(); Excel.Range myExcelRange = Globals.ThisAddIn.Application.Selection.Cells; int myNCol = myExcelRange.Columns.Count; int myNRow = myExcelRange.Rows.Count; int i = 1; if (((int)myExcelRange[i, 3].Cells.Value == 0) && ((int)myExcelRange[i, 4].Cells.Value == -1)) { Globals.ThisAddIn.mAddInModel.mNCondMean = 0; for (int j = 0; j < Globals.ThisAddIn.gvMaxNCondMean; j++) { Globals.ThisAddIn.mAddInModel.DeleteCondMean(j); } Globals.ThisAddIn.mAddInModel.mCondMeanDone = true; i++; } while (i <= myNRow) { if ((int)myExcelRange[i, 3].Cells.Value == 0) // CondMean { int myCondMeanType = (int)myExcelRange[i, 4].Cells.Value; Globals.ThisAddIn.mAddInModel.DeleteCondMean(myCondMeanType); int j = 1; while (((int)myExcelRange[i + j, 3].Cells.Value == 0) && ((int)myExcelRange[i + j, 4].Cells.Value == myCondMeanType)) { j++; } cExcelStockModel myAuxStockModel = new cExcelStockModel(j, myCondMeanType); for (int k = 0; k < j; k++) { cExcelRangeClass myExcelRangeClass = new cExcelRangeClass(); myExcelRangeClass.mWorkbook = myExcelRange[i + k, 1].Cells.Value; myExcelRangeClass.mWorksheet = myExcelRange[i + k, 2].Cells.Value; myExcelRangeClass.mLeftUpCell = myExcelRange[i + k, 5].Cells.Value; myExcelRangeClass.mRightDownCell = myExcelRange[i + k, 6].Cells.Value; myExcelRangeClass.mCells = myExcelRangeClass.mLeftUpCell + ":" + myExcelRangeClass.mRightDownCell; Tools.Workbook myTempWorkBook = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.Workbooks[myExcelRangeClass.mWorkbook]); int myNumber = GetSheetNumber(myTempWorkBook, myExcelRangeClass.mWorksheet); Tools.Worksheet myTempWorkSheet = Globals.Factory.GetVstoObject(myTempWorkBook.Worksheets[myNumber]); myTempWorkBook.Activate(); myTempWorkSheet.Activate(); myTempWorkSheet.Range[myExcelRangeClass.mLeftUpCell, myExcelRangeClass.mRightDownCell].Select(); Excel.Range myTempExcelRange = Globals.ThisAddIn.Application.Selection.Cells; myExcelRangeClass.mNColumn = myTempExcelRange.Columns.Count; myExcelRangeClass.mNRow = myTempExcelRange.Rows.Count; myExcelRangeClass.mNSize = myTempExcelRange.Count; myAuxStockModel.AddParam(myExcelRangeClass, k); myWorkBook.Activate(); myWorkSheet.Activate(); } Globals.ThisAddIn.mAddInModel.AddOneCondMean(myAuxStockModel); myAuxStockModel.Delete(); i += j; } else { if ((int)myExcelRange[i, 3].Cells.Value == 1) // CondVar { int myCondVarType = (int)myExcelRange[i, 4].Cells.Value; Globals.ThisAddIn.mAddInModel.DeleteCondVar(); int j = 1; while (((int)myExcelRange[i + j, 3].Cells.Value == 1) && ((int)myExcelRange[i + j, 4].Cells.Value == myCondVarType)) { j++; } cExcelStockModel myAuxStockModel = new cExcelStockModel(j, myCondVarType); for (int k = 0; k < j; k++) { cExcelRangeClass myExcelRangeClass = new cExcelRangeClass(); myExcelRangeClass.mWorkbook = myExcelRange[i + k, 1].Cells.Value; myExcelRangeClass.mWorksheet = myExcelRange[i + k, 2].Cells.Value; myExcelRangeClass.mLeftUpCell = myExcelRange[i + k, 5].Cells.Value; myExcelRangeClass.mRightDownCell = myExcelRange[i + k, 6].Cells.Value; myExcelRangeClass.mCells = myExcelRangeClass.mLeftUpCell + ":" + myExcelRangeClass.mRightDownCell; Tools.Workbook myTempWorkBook = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.Workbooks[myExcelRangeClass.mWorkbook]); int myNumber = GetSheetNumber(myTempWorkBook, myExcelRangeClass.mWorksheet); Tools.Worksheet myTempWorkSheet = Globals.Factory.GetVstoObject(myTempWorkBook.Worksheets[myNumber]); myTempWorkBook.Activate(); myTempWorkSheet.Activate(); Excel.Range myTempExcelRange = Globals.ThisAddIn.Application.Selection.Cells; myExcelRangeClass.mNColumn = myTempExcelRange.Columns.Count; myExcelRangeClass.mNRow = myTempExcelRange.Rows.Count; myExcelRangeClass.mNSize = myTempExcelRange.Count; myAuxStockModel.AddParam(myExcelRangeClass, k); myWorkBook.Activate(); myWorkSheet.Activate(); } Globals.ThisAddIn.mAddInModel.AddCondVar(myAuxStockModel); myAuxStockModel.Delete(); i += j; } else { int myCondDistrType = (int)myExcelRange[i, 4].Cells.Value; Globals.ThisAddIn.mAddInModel.DeleteCondDistr(); if (myCondDistrType == (int)(eDistrTypeEnumCli.eNormal)) { cExcelStockModel myAuxStockModel = new cExcelStockModel(0, myCondDistrType); Globals.ThisAddIn.mAddInModel.AddCondDistr(myAuxStockModel); i++; } else { int j = 1; if (i + j <= myNRow) { while (((int)myExcelRange[i + j, 3].Cells.Value == 2) && ((int)myExcelRange[i + j, 4].Cells.Value == myCondDistrType)) { j++; } } cExcelStockModel myAuxStockModel = new cExcelStockModel(j, myCondDistrType); for (int k = 0; k < j; k++) { cExcelRangeClass myExcelRangeClass = new cExcelRangeClass(); myExcelRangeClass.mWorkbook = myExcelRange[i + k, 1].Cells.Value; myExcelRangeClass.mWorksheet = myExcelRange[i + k, 2].Cells.Value; myExcelRangeClass.mLeftUpCell = myExcelRange[i + k, 5].Cells.Value; myExcelRangeClass.mRightDownCell = myExcelRange[i + k, 6].Cells.Value; myExcelRangeClass.mCells = myExcelRangeClass.mLeftUpCell + ":" + myExcelRangeClass.mRightDownCell; Tools.Workbook myTempWorkBook = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.Workbooks[myExcelRangeClass.mWorkbook]); int myNumber = GetSheetNumber(myTempWorkBook, myExcelRangeClass.mWorksheet); Tools.Worksheet myTempWorkSheet = Globals.Factory.GetVstoObject(myTempWorkBook.Worksheets[myNumber]); myTempWorkBook.Activate(); myTempWorkSheet.Activate(); Excel.Range myTempExcelRange = Globals.ThisAddIn.Application.Selection.Cells; myExcelRangeClass.mNColumn = myTempExcelRange.Columns.Count; myExcelRangeClass.mNRow = myTempExcelRange.Rows.Count; myExcelRangeClass.mNSize = myTempExcelRange.Count; myAuxStockModel.AddParam(myExcelRangeClass, k); myWorkBook.Activate(); myWorkSheet.Activate(); } Globals.ThisAddIn.mAddInModel.AddCondDistr(myAuxStockModel); myAuxStockModel.Delete(); i += j; } } } } Globals.ThisAddIn.mAddInModel.SetDescription(); Globals.ThisAddIn.mAddInBackupModel = new cExcelModelClass(Globals.ThisAddIn.mAddInModel); // bool myModelDone = Globals.ThisAddIn.mAddInModel.mCondDistrDone & Globals.ThisAddIn.mAddInModel.mCondMeanDone & Globals.ThisAddIn.mAddInModel.mCondVarDone; Globals.ThisAddIn.mRuban.RefreshRegArchRibbon(); Close(); }
// get the column data type for each column in the table public static void GetExcelTableColumnDataTypes(Win32Window excelHandle, Microsoft.Office.Tools.Excel.Workbook wb, Interop.ListObject excelTable, out List <string> excelTableColumnsDataTypes) { int rowIndex = 1; excelTableColumnsDataTypes = new List <string>(); string excelColumnDataType = String.Empty; Interop.Worksheet activeSheet = ((Interop.Worksheet)wb.ActiveSheet); Interop.Range tableBodyRange = excelTable.DataBodyRange; try { foreach (Interop.Range column in tableBodyRange.Columns) { List <string> cellsOfAcolumnDataTypes = new List <string>(); foreach (Interop.Range cell in column.Rows) { object value = cell.Value; string typeName; if (value == null) { typeName = "null"; } else { //// v2 to find out time -- PUT AN OPTION ON the "Get the table name" window to look for time in column //typeName = value.GetType().ToString().Replace("System.", "").ToLower(); //if (typeName == "double") //{ // // validte time in cell.Text with regex // // if the typeName includes AM/PM ^(((0?[0-9])|(1[0-2])):[0-5]?\d(:[0-5]?\d)?\s(AM|PM))$ for AM/PM time // // if typeName DOESN'T include AM/PM ^(((0?\d)|(1\d)|2[0-3]):[0-5]?\d(:[0-5]?\d)?)$ for 00:00:00 to 23:00:00 // // if it's not valid, it's data type will be string // // when using functions use cell.value2 // // for testing regex http://regexstorm.net/tester //} //else //{ // typeName = value.GetType().ToString().Replace("System.", "").ToLower(); //} //// v2 END to find out time // v1 that works ! typeName = value.GetType().ToString().Replace("System.", "").ToLower(); } // write cell data type to excell cell // ((Interop.Range)activeSheet.Cells[cell.Row, cell.Column - 6]).Value = typeName; cellsOfAcolumnDataTypes.Add(typeName); rowIndex = cell.Row; } // aproximate the table's current column data type based on it's cells' data type excelColumnDataType = GetExcelColumnDataTypeBasedOnCellsDataTypes(cellsOfAcolumnDataTypes, column); excelTableColumnsDataTypes.Add(excelColumnDataType); // write to cell the column's aproximated type // ((Interop.Range)activeSheet.Cells[rowIndex + 2, column.Column - 6]).Value = excelColumnDataType; } } catch (Exception ex) { MessageBox.Show(excelHandle, $"{ex.Message}", "", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { if (activeSheet != null) { Marshal.ReleaseComObject(activeSheet); } if (tableBodyRange != null) { Marshal.ReleaseComObject(tableBodyRange); } } }