private static void AssemblyHoursExcelTask(string message)
{
var filePath = @"C:\Dev\Source\DotNetTestProjectRepo\ExcelInterop\Assembly_time_calculator.xlsx";
Debug.WriteLine(message);
Application
xlAppObject = new Application();
xlAppObject.Visible = false;
xlAppObject.ScreenUpdating = true;
Workbook wb = xlAppObject.Workbooks.Open(filePath, Editable: true, IgnoreReadOnlyRecommended: true);
Debug.WriteLine($"Nr of Sheet: {wb.Sheets.Count}");
Sheets sheets = wb.Worksheets;
Worksheet worksheet = (Worksheet)sheets.Item[1];
var rangeCells = worksheet.Range["J20:L29"];
//var findCell = (rangeCells.Find("Conveyor type") as Range);
//var addressForCell = findCell.AddressLocal[false, false, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1];
int i = 0;
int j = 100;
foreach (Range cell in rangeCells.Rows)
{
Debug.WriteLine($"Column:{cell.Address}");
for (int k = 1; k <= cell.Columns.Count; k++)
{
Debug.WriteLine($"Column: {cell.Columns[k].Address}");
Range cellRng = worksheet.Range[cell.Columns[k].Address];
cellRng.Value2 = j++;
}
//foreach (Range cellColumn in cell.Columns)
//{
// Debug.WriteLine($"Column: {cellColumn.Address}");
// Range cellRng = worksheet.Range[cellColumn.Address];
// cellRng.Value2 = i++;
//}
wb.Save();
}
Debug.WriteLine($"Sheet Name: {worksheet.Name}");
////Get cell value
//Range excelRange = worksheet.UsedRange;
var cellsTuple = (
conveyorTypeCell : worksheet.Range["$C$5"].Value,
totalLengthOfTheConveyorCell : worksheet.Range["$C$6"].Value,
nrOfTheConveyorCell : worksheet.Range["$C$7"].Value,
nrOfBendsInTheSystemCell : worksheet.Range["$C$8"].Value,
tSlotCoveringCell : worksheet.Range["$C$9"].Value,
steelSlideRailCell : worksheet.Range["$C$10"].Value,
typeOfSupportsCell : worksheet.Range["$C$11"].Value,
distanceBetweenTheSupportsCell : worksheet.Range["$C$12"].Value,
nrOdSupportCell : worksheet.Range["$D$12"].Value,
assemplyOnTheHeightCell : worksheet.Range["$C$13"].Value,
connectionOfTwoConveyorBeamCell : worksheet.Range["$C$14"].Value,
guideRail : worksheet.Range["$B$292"].Value
);
var cellResult = worksheet.Range["$J$39"]?.Value;
Debug.WriteLine($"Value: " +
$"{cellsTuple.conveyorTypeCell}\n" +
$"{cellsTuple.totalLengthOfTheConveyorCell}\n" +
$"{cellsTuple.nrOfTheConveyorCell}\n" +
$"{cellsTuple.nrOfBendsInTheSystemCell}\n" +
$"{cellsTuple.tSlotCoveringCell}\n" +
$"{cellsTuple.steelSlideRailCell}\n" +
$"{cellsTuple.typeOfSupportsCell}\n" +
$"{cellsTuple.nrOdSupportCell}\n" +
$"{cellsTuple.assemplyOnTheHeightCell}\n" +
$"{cellsTuple.distanceBetweenTheSupportsCell}\n" +
$"{cellsTuple.connectionOfTwoConveyorBeamCell}" +
$"{cellsTuple.guideRail}" +
$"Result: {Math.Round(cellResult)}"
);
//Parse value to cell
Range rng = worksheet.Range["$C$5"];
rng.Value2 = "XLX-X85X";
rng = worksheet.Range["$B$292"];
rng.Value2 = true;
rng = worksheet.Range["$C$6"];
rng.Value2 = 100;
wb.Save();
cellsTuple = (
conveyorTypeCell : worksheet.Range["$C$5"].Value,
totalLengthOfTheConveyorCell : worksheet.Range["$C$6"].Value,
nrOfTheConveyorCell : worksheet.Range["$C$7"].Value,
nrOfBendsInTheSystemCell : worksheet.Range["$C$8"].Value,
tSlotCoveringCell : worksheet.Range["$C$9"].Value,
steelSlideRailCell : worksheet.Range["$C$10"].Value,
typeOfSupportsCell : worksheet.Range["$C$11"].Value,
distanceBetweenTheSupportsCell : worksheet.Range["$C$12"].Value,
nrOdSupportCell : worksheet.Range["$D$12"].Value,
assemplyOnTheHeightCell : worksheet.Range["$C$13"].Value,
connectionOfTwoConveyorBeamCell : worksheet.Range["$C$14"].Value,
guideRail : worksheet.Range["$B$292"].Value
);
cellResult = worksheet.Range["$J$39"]?.Value;
Debug.WriteLine($"Value: {cellsTuple.conveyorTypeCell}\n" +
$"{cellsTuple.totalLengthOfTheConveyorCell}\n" +
$"{cellsTuple.guideRail}\n" +
$"Result: {Math.Round(cellResult)}");
//wb.Save();
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(worksheet);
wb.Close(0);
xlAppObject.Quit();
}
