private InteropWorksheet GetWorkSheet(InteropWorkbook workBook, Workbook workbook, string nameOfsheet) { foreach (InteropWorksheet interopWorksheet in workBook.Sheets) { var ws = new Allors.Excel.Interop.Worksheet(workbook, interopWorksheet); var customProperties = ws.GetCustomProperties(); if (customProperties.Any(v => Equals(AppConstants.KeySheet, v.Key) && Equals(nameOfsheet, v.Value))) { return(interopWorksheet); } } return(null); }
private async void ThisAddIn_WorkbookOpen(InteropWorkbook Wb) { // this has been marked as a showCase workbook. So threat it as one we know. var iWorkbook = this.AddIn.New(Wb); object result = null; if (iWorkbook.TryGetCustomProperty(AppConstants.KeyWorkbook, ref result)) { if (Convert.ToBoolean(result)) { // Check the Custom Properties for existing data, and if so, instantiate those sheets. object tagId = null; { //// We need to have an InvoicesSheet //var interopWorksheet = this.GetWorkSheet(Wb, iWorkbook, nameof(AppConfigSheet)); //if (interopWorksheet == null) //{ // var iWorksheet = iWorkbook.AddWorksheet(0); // iWorksheet.Name = KnownNames.AppConfigSheetName; // interopWorksheet = Wb.ActiveSheet; //} //var worksheet = new Allors.Excel.Interop.Worksheet(iWorkbook, interopWorksheet); //var appConfigSheet = new AppConfigSheet(this.AddIn.Program, worksheet); //await appConfigSheet.Refresh(); //((Program)this.AddIn.Program).SheetByWorksheet.Add(worksheet, appConfigSheet); } if (iWorkbook.TryGetCustomProperty(KnownNames.PaymentTermTag, ref tagId)) { // We need to have an InvoicesSheet var interopWorksheet = this.GetWorkSheet(Wb, iWorkbook, nameof(PaymentTermsSheet)); if (interopWorksheet == null) { var iWorksheet = iWorkbook.AddWorksheet(0); iWorksheet.Name = KnownNames.PaymentTermsSheetName; interopWorksheet = Wb.ActiveSheet; } var worksheet = new Allors.Excel.Interop.Worksheet(iWorkbook, interopWorksheet); var paymentTermsSheet = new PaymentTermsSheet(this.AddIn.Program, worksheet); await paymentTermsSheet.Load(iWorkbook); ((Program)this.AddIn.Program).SheetByWorksheet.Add(worksheet, paymentTermsSheet); } if (iWorkbook.TryGetCustomProperty(KnownNames.InvoiceTag, ref tagId)) { // We need to have an OrganisationsSheet var interopWorksheet = this.GetWorkSheet(Wb, iWorkbook, nameof(InvoicesSheet)); if (interopWorksheet == null) { var iWorksheet = iWorkbook.AddWorksheet(0); iWorksheet.Name = KnownNames.InvoicesSheetName; interopWorksheet = Wb.ActiveSheet; } var worksheet = new Allors.Excel.Interop.Worksheet(iWorkbook, interopWorksheet); var invoicesSheet = new InvoicesSheet(this.AddIn.Program, worksheet); await invoicesSheet.Load(iWorkbook); ((Program)this.AddIn.Program).SheetByWorksheet.Add(worksheet, invoicesSheet); } if (iWorkbook.TryGetCustomProperty(KnownNames.OrganisationTag, ref tagId)) { // We need to have an InvoicesSheet var interopWorksheet = this.GetWorkSheet(Wb, iWorkbook, nameof(OrganisationsSheet)); if (interopWorksheet == null) { var iWorksheet = iWorkbook.AddWorksheet(0); iWorksheet.Name = KnownNames.OrganisationsSheetName; interopWorksheet = Wb.ActiveSheet; } var worksheet = new Allors.Excel.Interop.Worksheet(iWorkbook, interopWorksheet); var organisationsSheet = new OrganisationsSheet(this.AddIn.Program, worksheet); await organisationsSheet.Load(iWorkbook); ((Program)this.AddIn.Program).SheetByWorksheet.Add(worksheet, organisationsSheet); } } } else { //TODO: remove the iWorkbook } }