private void ExcelApplication_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel)
{
Excel.Sheets excelSheets = Wb.Worksheets;
Excel.Worksheet excelWorksheet = HelperUtil.GetSheetNameFromGroupOfSheets(GlobalMembers.InstanceGlobalMembers.MetaDataSheetName, excelSheets);
Excel.Range excelRange = excelWorksheet.UsedRange;
excelRange.ClearContents();
excelRange.ClearFormats();
excelRange.ClearComments();
}
private void ExcelWorkbook_SheetFollowHyperlink(object Sh, Excel.Hyperlink Target)
{
try
{
if ("Drop Down Values".Equals(Target.Range.Text))
{
Excel._Worksheet ws = (Excel._Worksheet)Sh;
int rowNumber = Target.Range.Row;
int fnColIndex = ExcelHelper.ConvertColumnNameToColumnIndex("FieldName", ws);
Excel.Range r = ws.Cells[rowNumber, fnColIndex] as Excel.Range;
Excel.Workbook workbook = (Excel.Workbook)ws.Parent;
Excel.Sheets oSheets = workbook.Worksheets;
Excel._Worksheet oSheet = HelperUtil.GetSheetNameFromGroupOfSheets(GlobalMembers.InstanceGlobalMembers.DropDownSheetName, oSheets);
int targetSheetRow = oSheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
targetSheetRow++;
fnColIndex = ExcelHelper.ConvertColumnNameToColumnIndex("Field Id", oSheet);
oSheet.Cells[targetSheetRow, fnColIndex] = r.Text;
}
}
catch (Exception ex)
{ throw (ex); }
}
private void OnAddButton_Click(object sender, RibbonControlEventArgs e)
{
RibbonButton rb = (RibbonButton)sender;
RibbonMenu rm = (RibbonMenu)rb.Parent;
string addMenuLabel = string.Empty;
addMenuLabel = rb.Label;
rm.Label = rb.Label;
Excel.Sheets sheets = Globals.ThisAddIn.excelApplication.Sheets;
Excel._Worksheet worksheet;
Excel.Range oRange;
ExcelHelper.ToggleExcelEvents(Globals.ThisAddIn.excelApplication, false);
switch (addMenuLabel)
{
case "Fields":
{
if (btnImport.Enabled)
{
btnImport.Enabled = false;
}
worksheet = HelperUtil.GetSheetNameFromGroupOfSheets(GlobalMembers.InstanceGlobalMembers.MetaDataSheetName, sheets);
oRange = worksheet.UsedRange;
oRange.ClearContents();
worksheet.Visible = Excel.XlSheetVisibility.xlSheetVisible;
ExcelHelper.DisplayMetaDataInformationOnSheet(worksheet, dictModules, addMenuLabel);
break;
}
case "Descriptions":
{
if (!btnImport.Enabled)
{
btnImport.Enabled = true;
}
worksheet = HelperUtil.GetSheetNameFromGroupOfSheets(GlobalMembers.InstanceGlobalMembers.TranslationSheetName, sheets);
oRange = worksheet.UsedRange;
oRange.ClearContents();
ExcelHelper.PopulateTranslationHeader(worksheet);
worksheet.Visible = Excel.XlSheetVisibility.xlSheetVisible;
worksheet.Activate();
break;
}
case "Dropdowns":
{
if (btnImport.Enabled)
{
btnImport.Enabled = false;
}
worksheet = HelperUtil.GetSheetNameFromGroupOfSheets(GlobalMembers.InstanceGlobalMembers.DropDownSheetName, sheets);
oRange = worksheet.UsedRange;
oRange.ClearContents();
ExcelHelper.PopulateDropDownValuesHeader(worksheet);
worksheet.Visible = Excel.XlSheetVisibility.xlSheetVisible;
worksheet.Activate();
break;
}
case "Fields And Dropdowns":
{
if (btnImport.Enabled)
{
btnImport.Enabled = false;
}
worksheet = HelperUtil.GetSheetNameFromGroupOfSheets(GlobalMembers.InstanceGlobalMembers.MetaDataSheetName, sheets);
oRange = worksheet.UsedRange;
oRange.ClearContents();
Excel._Worksheet worksheet1 = HelperUtil.GetSheetNameFromGroupOfSheets(GlobalMembers.InstanceGlobalMembers.DropDownSheetName, sheets);
oRange = worksheet1.UsedRange;
oRange.ClearContents();
ExcelHelper.PopulateDropDownValuesHeader(worksheet1);
worksheet.Visible = Excel.XlSheetVisibility.xlSheetVisible;
ExcelHelper.DisplayMetaDataInformationOnSheet(worksheet, dictModules, addMenuLabel);
worksheet1.Visible = Excel.XlSheetVisibility.xlSheetVisible;
worksheet.Activate();
break;
}
}
if (!btnExport.Enabled)
{
btnExport.Enabled = true;
}
ExcelHelper.HideExcelSheetsExceptTheSelectedOne(rm.Label.ToString(), sheets);
ExcelHelper.ToggleExcelEvents(Globals.ThisAddIn.excelApplication, true);
}
private void BackgroundWorker1_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
{
string addMenuLabel = string.Empty;
try
{
BackgroundWorker worker = sender as BackgroundWorker;
bool isExported = false;
ExitEditMode();
RibbonMenu ribbonMenu = (RibbonMenu)dynamicMenuAdd;
addMenuLabel = ribbonMenu.Label;
IHttpRequest httpRequest = GlobalMembers.InstanceGlobalMembers.Container.Resolve <IHttpRequest>();
RibbonController ribbonController = new RibbonController(httpRequest);
Excel._Worksheet worksheet = Globals.ThisAddIn.excelApplication.ActiveSheet;
int colCount = worksheet.UsedRange.Columns.Count;
int rowCount = worksheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
string moduleId = ExcelHelper.GetObjectIdFromText(dynamicMenuModules);
Dictionary <string, string> dictLanguage = GlobalMembers.InstanceGlobalMembers.DictionaryLanguageType;
switch (addMenuLabel)
{
case "Fields":
{
ExcelHelper.ClearLogColumnsForAllTheRows(worksheet);
for (int i = 4; i <= rowCount; i++)
{
if (worker.CancellationPending == true)
{
e.Cancel = true;
break;
}
else
{
ribbonController.ValidateExcelData(i, colCount, worksheet);
ribbonController.BuildAndExportFieldsJson(i, colCount, worksheet, moduleId, userId);
}
worker.ReportProgress((i * 100) / rowCount);
}
break;
}
case "Descriptions":
{
for (int i = 3; i <= rowCount; i++)
{
if (worker.CancellationPending == true)
{
e.Cancel = true;
break;
}
else
{
if (!isExported)
{
ribbonController.BuildTranlatedDataJsonAndExport(worksheet, dictLanguage);
}
isExported = true;
worker.ReportProgress((i * 100) / rowCount);
}
}
break;
}
case "Dropdowns":
{
for (int i = 3; i <= rowCount; i++)
{
if (worker.CancellationPending == true)
{
e.Cancel = true;
break;
}
else
{
if (!isExported)
{
ribbonController.BuildDropDownJsonAndExport(worksheet, dictLanguage);
}
isExported = true;
worker.ReportProgress((i * 100) / rowCount);
}
}
break;
}
case "Fields And Dropdowns":
{
Excel.Workbook workbook = GlobalMembers.InstanceGlobalMembers.ExcelApplication.ActiveWorkbook;
Excel.Sheets sheets = workbook.Sheets;
Excel._Worksheet dropDownWorkSheet = HelperUtil.GetSheetNameFromGroupOfSheets(GlobalMembers.InstanceGlobalMembers.DropDownSheetName, sheets);;
for (int i = 4; i <= rowCount; i++)
{
if (worker.CancellationPending == true)
{
e.Cancel = true;
break;
}
else
{
ribbonController.ValidateExcelData(i, colCount, worksheet);
ribbonController.BuildAndExportFieldsJson(i, colCount, worksheet, moduleId, userId);
if (i == rowCount)
{
ribbonController.BuildDropDownJsonAndExport(dropDownWorkSheet, dictLanguage);
}
}
worker.ReportProgress((i * 100) / rowCount);
}
break;
}
}
}
catch (Exception ex)
{ throw (ex); }
}