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);
}
}
}
