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();
}
static void Main(string[] args)
{
Application app = new Application();
try
{
FileInfo fiSource = new FileInfo(args[0]);
FileInfo fiDest = new FileInfo(args[1]);
Workbook wb = app.Workbooks.Open(fiSource.FullName,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Worksheet sheet = (Worksheet)wb.Sheets[1];
Dictionary <string, DateTime> hashNewest = new Dictionary <string, DateTime>();
for (int iRow = 1; iRow < (double)sheet.Cells.Height; ++iRow)
{
object oID = sheet.get_Range("A" + (iRow + 1), Type.Missing).get_Value(Type.Missing);
if (oID == null || oID.ToString().Trim().Length <= 0)
{
break;
}
object oName = sheet.get_Range("B" + (iRow + 1), Type.Missing).get_Value(Type.Missing);
string strName = "" + oName;
object oDate = sheet.get_Range("C" + (iRow + 1), Type.Missing).get_Value(Type.Missing);
DateTime dt = Convert.ToDateTime(oDate);
if (!hashNewest.ContainsKey(strName))
{
hashNewest.Add(strName, dt);
}
else if (hashNewest[strName].CompareTo(dt) < 0)
{
hashNewest[strName] = dt;
}
}
for (int iRow = 1; iRow < (double)sheet.Cells.Height; ++iRow)
{
object oID = sheet.get_Range("A" + (iRow + 1), Type.Missing).get_Value(Type.Missing);
if (oID == null || oID.ToString().Trim().Length <= 0)
{
break;
}
object oName = sheet.get_Range("B" + (iRow + 1), Type.Missing).get_Value(Type.Missing);
string strName = "" + oName;
object oDate = sheet.get_Range("C" + (iRow + 1), Type.Missing).get_Value(Type.Missing);
DateTime dt = Convert.ToDateTime(oDate);
if (!hashNewest[strName].Equals(dt))
{
sheet.get_Range(
string.Format("A{0}:D{0}", iRow + 1),
Type.Missing
).Delete(XlDeleteShiftDirection.xlShiftUp);
--iRow;
}
}
File.Delete(fiDest.FullName);
wb.SaveAs(fiDest.FullName, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.Close(false, Type.Missing, Type.Missing);
}
finally
{
app.Workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
}
Console.WriteLine("Hit any key to continue");
Console.ReadKey();
}