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