private void LoadSettings()
{
cbName.SelectedIndex = ExcelHelpers.ColumnLetterToColumnIndex(Settings.Default.MassExcelColLetterName) - 1;
cbDescription.SelectedIndex = ExcelHelpers.ColumnLetterToColumnIndex(
string.IsNullOrEmpty(Settings.Default.MassExcelColLetterDescription) ? Settings.Default.MassExcelColLetterName : Settings.Default.MassExcelColLetterDescription) - 1;
cbLength.SelectedIndex = ExcelHelpers.ColumnLetterToColumnIndex(Settings.Default.MassExcelColLetterLength) - 1;
cbWidth.SelectedIndex = ExcelHelpers.ColumnLetterToColumnIndex(Settings.Default.MassExcelColLetterWidth) - 1;
cbHeight.SelectedIndex = ExcelHelpers.ColumnLetterToColumnIndex(Settings.Default.MassExcelColLetterHeight) - 1;
cbWeight.SelectedIndex = ExcelHelpers.ColumnLetterToColumnIndex(Settings.Default.MassExcelColLetterWeight) - 1;
cbOutputStart.SelectedIndex = ExcelHelpers.ColumnLetterToColumnIndex(Settings.Default.MassExcelColLetterOutputStart) - 1;
ImageSize = Settings.Default.MassExcelImageSize;
GenerateImage = Settings.Default.MassExcelGenerateImageInRow;
GenerateImageInFolder = Settings.Default.MassExcelGenerateImagesInFolder;
GenerateReport = Settings.Default.MassExcelGenerateReportsInFolder;
DirectoryPathImages = Settings.Default.MassExcelImageFolderPath;
DirectoryPathReports = Settings.Default.MassExcelReportFolderPath;
}
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;
}
}