private void OnCompute(object sender, EventArgs e) { Cursor.Current = Cursors.WaitCursor; StringBuilder sbErrors = new StringBuilder(); try { string startLetter = Settings.Default.ColumnLetterOutputStart; int colStartIndex = ExcelHelpers.ColumnLetterToColumnIndex(Settings.Default.ColumnLetterOutputStart); Excel.Worksheet xlSheet = Globals.StackBuilderAddIn.Application.ActiveSheet as Excel.Worksheet; Excel.Range range = xlSheet.UsedRange; int rowCount = range.Rows.Count; // get list of pallets List <PalletProperties> pallets = GetSelectedPallets(); if (0 == pallets.Count) { MessageBox.Show(Resources.ID_ERROR_NOPALLETSELECTED); return; } int palletColStartIndex = colStartIndex - 1; // pallet loop foreach (PalletProperties palletProperties in pallets) { int iOutputFieldCount = palletColStartIndex; int iNoCols = 0; // modify header // count Excel.Range countHeaderCell = xlSheet.get_Range(ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + 1); countHeaderCell.Value = Resources.ID_RESULT_NOCASES; ++iNoCols; // load weight Excel.Range loadWeightHeaderCell = xlSheet.get_Range(ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + 1); loadWeightHeaderCell.Value = Resources.ID_RESULT_LOADWEIGHT + " (" + UnitsManager.MassUnitString + ")"; ++iNoCols; // total pallet weight Excel.Range totalPalletWeightHeaderCell = xlSheet.get_Range(ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + 1); totalPalletWeightHeaderCell.Value = Resources.ID_RESULT_TOTALPALLETWEIGHT + " (" + UnitsManager.MassUnitString + ")"; ++iNoCols; // efficiency Excel.Range efficiencyHeaderCell = xlSheet.get_Range(ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + 1); efficiencyHeaderCell.Value = Resources.ID_RESULT_EFFICIENCY + " (%)"; ++iNoCols; // image if (GenerateImage) { Excel.Range imageHeaderCell = xlSheet.get_Range(ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + 1); imageHeaderCell.Value = Resources.ID_RESULT_IMAGE; ++iNoCols; } // set bold font for all header row Excel.Range headerRange = xlSheet.get_Range("a" + 1, ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + 1); headerRange.Font.Bold = true; // modify range for images if (GenerateImage) { Excel.Range dataRange = xlSheet.get_Range( "a" + 2, ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + rowCount); dataRange.RowHeight = 128; Excel.Range imageRange = xlSheet.get_Range( ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + 2, ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + rowCount); imageRange.ColumnWidth = 24; } double largestDimensionMinimum = LargestDimMin; string colName = Settings.Default.ColumnLetterName; string colDescription = Settings.Default.ColumnLetterDescription; string colLength = Settings.Default.ColumnLetterLength; string colWidth = Settings.Default.ColumnLetterWidth; string colHeight = Settings.Default.ColumnLetterHeight; string colWeight = Settings.Default.ColumnLetterWeight; // loop through rows for (int iRow = 2; iRow <= rowCount; ++iRow) { iOutputFieldCount = palletColStartIndex - 1; try { // get name string name = (xlSheet.get_Range(colName + iRow, colName + iRow).Value).ToString(); // get description string description = (xlSheet.get_Range(colDescription + iRow, colDescription + iRow).Value).ToString(); // get length double length = xlSheet.get_Range(colLength + iRow, colLength + iRow).Value; // get width double width = xlSheet.get_Range(colWidth + iRow, colWidth + iRow).Value; // get height double height = xlSheet.get_Range(colHeight + iRow, colHeight + iRow).Value; double maxDimension = Math.Max(Math.Max(length, width), height); if (maxDimension < largestDimensionMinimum) { continue; } // get weight double?weight = null; try { weight = xlSheet.get_Range(colWeight + iRow, colWeight + iRow).Value; } catch (Exception /*ex*/) { } // compute stacking int stackCount = 0; double loadWeight = 0.0, totalPalletWeight = 0.0, stackEfficiency = 0.0; string stackImagePath = string.Empty; iOutputFieldCount = palletColStartIndex; // generate result GenerateResult(name, description , length, width, height, weight , palletProperties , ref stackCount , ref loadWeight, ref totalPalletWeight , ref stackEfficiency , ref stackImagePath); // insert count Excel.Range countCel = xlSheet.get_Range(ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow); countCel.Value = stackCount; // insert load weight Excel.Range loadWeightCell = xlSheet.get_Range(ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow); loadWeightCell.Value = loadWeight; // insert total weight Excel.Range totalWeightCell = xlSheet.get_Range(ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow); totalWeightCell.Value = totalPalletWeight; // efficiency Excel.Range efficiencyCell = xlSheet.get_Range(ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow); efficiencyCell.Value = Math.Round(stackEfficiency, 2); // insert image if (GenerateImage) { Excel.Range imageCell = xlSheet.get_Range( ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + iRow, ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow); xlSheet.Shapes.AddPicture(stackImagePath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, imageCell.Left + 1, imageCell.Top + 1, imageCell.Width - 2, imageCell.Height - 2); } } catch (OutOfMemoryException ex) { sbErrors.Append(ex.Message); } catch (EngineException ex) { sbErrors.Append(ex.Message); } catch (Microsoft.CSharp.RuntimeBinder.RuntimeBinderException /*ex*/) { iOutputFieldCount = palletColStartIndex; Excel.Range countCel = xlSheet.get_Range( ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow); countCel.Value = string.Format($"ERROR : Invalid input data!"); } catch (Exception ex) { throw ex; // rethrow } } // loop row // increment palletColStartIndex palletColStartIndex += iNoCols; } // loop pallet } catch (System.Runtime.InteropServices.COMException ex) { switch ((uint)ex.ErrorCode) { case 0x800A03EC: MessageBox.Show("NAME_NOT_FOUND : Could not find cell with given name!"); break; default: MessageBox.Show(ex.Message); break; } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { Cursor.Current = Cursors.Arrow; } }