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