Post
Ask Question

Working with COM Interop Excel Using C#

  1. // Add NameSpace  
  2. using Excel = Microsoft.Office.Interop.Excel;  
  3. // Intialize the Excel  
  4. object oMissing = System.Reflection.Missing.Value;  
  5. Excel.Application xlApp = new Excel.Application();  
  6. Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"" + txtFolderPath.Text + "/" + FileName);  
  7. // Loop for excel worksheets  
  8. for (int i = 1; i <= xlWorkbook.Worksheets.Count; i++)  
  9. {  
  10. // Access the excel worksheets  
  11. Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[i];  
  12. if (i == 1)  
  13. {  
  14. xlWorksheet.Name = "Executive Summary";  
  15. xlWorksheet.Hyperlinks.Add(xlWorksheet.get_Range("A3", Type.Missing), string.Empty, "'Approver Report'!A1", Type.Missing, Type.Missing);  
  16. xlWorksheet.Hyperlinks.Add(xlWorksheet.get_Range("A4", Type.Missing), string.Empty, "'Pending Approvals - SAP'!A1", Type.Missing, Type.Missing);  
  17. xlWorksheet.Hyperlinks.Add(xlWorksheet.get_Range("A6", Type.Missing), string.Empty, "'Pending Approvals - Policy'!A1", Type.Missing, Type.Missing);  
  18. }  
  19. else if (i == 2)  
  20. {  
  21. xlWorksheet.Name = "Approver Report";  
  22. xlWorksheet.Activate();  
  23. xlWorksheet.Application.ActiveWindow.SplitRow = 3;  
  24. xlWorksheet.Application.ActiveWindow.FreezePanes = true;  
  25. xlWorksheet.get_Range("E1", Type.Missing).Font.Bold = true;  
  26. xlWorksheet.Hyperlinks.Add(xlWorksheet.get_Range("E1", Type.Missing), string.Empty, "'Executive Summary'!A1", Type.Missing, Type.Missing);  
  27. }  
  28. else if (i == 3)  
  29. {  
  30. xlWorksheet.Name = "Pending Approvals - SAP";  
  31. xlWorksheet.Activate();  
  32. xlWorksheet.Application.ActiveWindow.SplitRow = 3;  
  33. xlWorksheet.Application.ActiveWindow.FreezePanes = true;  
  34. xlWorksheet.get_Range("G1", Type.Missing).Font.Bold = true;  
  35. xlWorksheet.Hyperlinks.Add(xlWorksheet.get_Range("G1", Type.Missing), string.Empty, "'Executive Summary'!A1", Type.Missing, Type.Missing);  
  36. }  
  37. else if (i == 4)  
  38. {  
  39. xlWorksheet.Name = "Pending Approvals - Policy";  
  40. xlWorksheet.Activate();  
  41. xlWorksheet.Application.ActiveWindow.SplitRow = 3;  
  42. xlWorksheet.Application.ActiveWindow.FreezePanes = true;  
  43. xlWorksheet.get_Range("F1", Type.Missing).Font.Bold = true;  
  44. xlWorksheet.Hyperlinks.Add(xlWorksheet.get_Range("F1", Type.Missing), string.Empty, "'Executive Summary'!A1", Type.Missing, Type.Missing);  
  45. }  
  46. }  
  47. xlWorkbook.SaveAs(@"" + FilePath + "/" + FileName);  
  48. // TO Stop the process  
  49. var processes = from p in Process.GetProcessesByName("EXCEL")  
  50. select p;  
  51. foreach (var process in processes)  
  52. {  
  53. if (process.MainWindowTitle == "Microsoft Excel - " + fileD.Name)  
  54. process.Kill();  
  55. }  
  56. }  
  57. MessageBox.Show("Records rename successfully !", "Done");