Forum Discussion

synchi64's avatar
synchi64
Occasional Contributor
4 years ago
Solved

Is there a way to edit an excel file with TestComplete?

Greetings,

 

The question is here, I'm trying to edit an excel file with TestComplete,

more specifically, I need to know if there is a way to change the color of a cell in an excel file from TestComplete,

 

thanks in advance

  • Use Interior.ColorIndex to change background color of a cell

     

    function testWriteExcel() {
      let excelObject, excelWorkbookObj, excelMainSheet, sheetName;
      try {
        sheetName                 = "test";
        excelObject               = Sys.OleObject("Excel.Application");
        if (excelObject == null)
          throw Error("Excel n'est pas installé !");
        excelObject.Visible       = false;
        excelObject.DisplayAlerts = false;
        excelWorkbookObj          = excelObject.Workbooks.Open("c:\\temp\\test.xlsx");
        excelMainSheet            = excelWorkbookObj.Sheets.item(sheetName);
        excelMainSheet.Cells.Item(3, 3).Value2 = "Test";
        excelMainSheet.Cells.Item(3, 3).Interior.ColorIndex = 6;
        excelObject.AlertBeforeOverwriting         = false;
        excelObject.Application.ActiveWorkbook.SaveAs("c:\\temp\\testcolor.xlsx");
      }
      catch(e) {
        Log.Message("Erreur durant la mise à jour Excel", e.message, pmHigher, system.logWarning);
      }
      finally {
        if (excelObject != null) {
          excelObject.Application.ActiveWorkbook.Close;
          excelObject.Quit;
          excelObject      = null;
          excelWorkbookObj = null;
          excelMainSheet   = null;
          killProcess("EXCEL", false, false, 10);
        }
      }
    }
    

     

1 Reply

  • BenoitB's avatar
    BenoitB
    Community Hero

    Use Interior.ColorIndex to change background color of a cell

     

    function testWriteExcel() {
      let excelObject, excelWorkbookObj, excelMainSheet, sheetName;
      try {
        sheetName                 = "test";
        excelObject               = Sys.OleObject("Excel.Application");
        if (excelObject == null)
          throw Error("Excel n'est pas installé !");
        excelObject.Visible       = false;
        excelObject.DisplayAlerts = false;
        excelWorkbookObj          = excelObject.Workbooks.Open("c:\\temp\\test.xlsx");
        excelMainSheet            = excelWorkbookObj.Sheets.item(sheetName);
        excelMainSheet.Cells.Item(3, 3).Value2 = "Test";
        excelMainSheet.Cells.Item(3, 3).Interior.ColorIndex = 6;
        excelObject.AlertBeforeOverwriting         = false;
        excelObject.Application.ActiveWorkbook.SaveAs("c:\\temp\\testcolor.xlsx");
      }
      catch(e) {
        Log.Message("Erreur durant la mise à jour Excel", e.message, pmHigher, system.logWarning);
      }
      finally {
        if (excelObject != null) {
          excelObject.Application.ActiveWorkbook.Close;
          excelObject.Quit;
          excelObject      = null;
          excelWorkbookObj = null;
          excelMainSheet   = null;
          killProcess("EXCEL", false, false, 10);
        }
      }
    }