private void OnCompute(object sender, EventArgs e) { Cursor.Current = Cursors.WaitCursor; SaveSettings(); StringBuilder sbErrors = new StringBuilder(); try { string colName = ColumnLetterName; string colDescription = ColumnLetterDescription; string colLength = ColumnLetterLength; string colWidth = ColumnLetterWidth; string colHeight = ColumnLetterHeight; string colWeight = ColumnLetterWeight; string filePath = InputFilePath; string outputPath = Path.Combine(Path.GetDirectoryName(InputFilePath), Path.GetFileNameWithoutExtension(InputFilePath) + "_output"); string filePathCopy = Path.ChangeExtension(outputPath, Path.GetExtension(InputFilePath)); File.Copy(filePath, filePathCopy, true); // get the collection of work sheets Excel.Application xlApp = new Excel.Application() { Visible = true, DisplayAlerts = false }; Excel.Workbook xlWbk = xlApp.Workbooks.Open(filePathCopy, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Excel.Worksheet xlSheet = (Excel.Worksheet)xlApp.Sheets[SheetName]; Excel.Range range = xlSheet.UsedRange; int rowCount = range.Rows.Count; int colStartIndex = ExcelHelpers.ColumnLetterToColumnIndex(ColumnLetterOutputStart) - 1; int palletColStartIndex = colStartIndex - 1; // pallet loop var pallets = SelectedPallets; foreach (var palletProperties in pallets) { int iOutputFieldCount = palletColStartIndex; int iNoCols = 0; // ### header : begin // count Excel.Range countHeaderCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + 1]; countHeaderCell.Value = Resources.ID_RESULT_NOCASES; ++iNoCols; // byLayerCount Excel.Range layerCountHeader = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + 1]; layerCountHeader.Value = Resources.ID_RESULT_LAYERCOUNT; ++iNoCols; // load weight Excel.Range loadWeightHeaderCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + 1]; loadWeightHeaderCell.Value = Resources.ID_RESULT_LOADWEIGHT + " (" + UnitsManager.MassUnitString + ")"; ++iNoCols; // total pallet weight Excel.Range totalPalletWeightHeaderCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + 1]; totalPalletWeightHeaderCell.Value = Resources.ID_RESULT_TOTALPALLETWEIGHT + " (" + UnitsManager.MassUnitString + ")"; ++iNoCols; // efficiency Excel.Range efficiencyHeaderCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + 1]; efficiencyHeaderCell.Value = Resources.ID_RESULT_EFFICIENCY + " (%)"; ++iNoCols; // image if (GenerateImage) { Excel.Range imageHeaderCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + 1]; imageHeaderCell.Value = Resources.ID_RESULT_IMAGE; ++iNoCols; } // set bold font for all header row Excel.Range headerRange = xlSheet.Range["a" + 1, ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + 1]; headerRange.Font.Bold = true; // modify range for images if (GenerateImage) { Excel.Range dataRange = xlSheet.Range["a" + 2, ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + rowCount]; dataRange.RowHeight = 128; Excel.Range imageRange = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + 2, ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + rowCount]; imageRange.ColumnWidth = 24; } // ### header : end // ### rows : begin for (var iRow = 2; iRow <= rowCount; ++iRow) { try { iOutputFieldCount = palletColStartIndex; // free version should exit after MaxNumberRowFree if (!Program.IsSubscribed && iRow > MaxNumberRowFree + 1) { var cell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow]; cell.Value = string.Format(Resources.ID_MASSEXCEL_FREEVERSIONLIMITEDNUMBER, MaxNumberRowFree); break; } // get name string name = (xlSheet.Range[colName + iRow, colName + iRow].Value).ToString(); // get description string description = string.IsNullOrEmpty(colDescription) ? string.Empty : (xlSheet.Range[colDescription + iRow, colDescription + iRow].Value).ToString(); // get length double length = (double)xlSheet.Range[colLength + iRow, colLength + iRow].Value; // get width double width = (double)xlSheet.Range[colWidth + iRow, colWidth + iRow].Value; // get height double height = (double)xlSheet.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 = (double)xlSheet.Range[colWeight + iRow, colWeight + iRow].Value; } catch (Exception /*ex*/) { } // compute stacking int stackCount = 0, layerCount = 0, byLayerCount = 0; double loadWeight = 0.0, totalPalletWeight = 0.0, stackEfficiency = 0.0; string stackImagePath = string.Empty; // generate result GenerateResult(name, description , length, width, height, weight , palletProperties, Overhang , ref stackCount , ref layerCount, ref byLayerCount , ref loadWeight, ref totalPalletWeight , ref stackEfficiency , ref stackImagePath); // insert count var countCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow]; countCell.Value = stackCount; // insert layer count var layerCountCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow]; layerCountCell.Value = $"{layerCount} x {byLayerCount}"; // insert load weight var loadWeightCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow]; loadWeightCell.Value = loadWeight; // insert total weight var totalWeightCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow]; totalWeightCell.Value = totalPalletWeight; // efficiency var efficiencyCell = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow]; efficiencyCell.Value = Math.Round(stackEfficiency, 2); // insert image if (GenerateImage) { var imageCell = xlSheet.Range[ ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount) + iRow, ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow]; xlSheet.Shapes.AddPicture( stackImagePath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, (float)Convert.ToDecimal(imageCell.Left) + 1.0f, (float)Convert.ToDecimal(imageCell.Top) + 1.0f, (float)Convert.ToDecimal(imageCell.Width) - 2.0f, (float)Convert.ToDecimal(imageCell.Height) - 2.0f ); } } catch (OutOfMemoryException ex) { sbErrors.Append(ex.Message); } catch (EngineException ex) { sbErrors.Append(ex.Message); } catch (Microsoft.CSharp.RuntimeBinder.RuntimeBinderException /*ex*/) { iOutputFieldCount = ExcelHelpers.ColumnLetterToColumnIndex(ColumnLetterOutputStart) - 1;; var countCel = xlSheet.Range[ExcelHelpers.ColumnIndexToColumnLetter(iOutputFieldCount++) + iRow]; countCel.Value = string.Format($"ERROR : Invalid input data!"); } catch (Exception ex) { throw ex; // rethrow } } // loop row // ### rows : end // increment palletColStartIndex palletColStartIndex += iNoCols; } // loop pallets } 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; } }