Forum Discussion

hannecroonen's avatar
hannecroonen
Contributor
4 years ago

Write values to excel in different fonts/font colours

Hi guys,

 

small question for you all:

I am saving the time it took to run certain test into an excel file as following (javascript based):

var fileName = "Y:\\Automation\\Automation\\Testscenario's\\Timings.xlsx";
var excelFile;
var excelSheet;
if (aqFile.Exists(fileName))
{
// Open the existing Excel file
excelFile = Excel.Open(fileName);
excelSheet = excelFile.SheetByIndex(8);
// Write the data into a new row of the existing file
var rowIndex = excelSheet.RowCount + 1;

excelSheet.Cell("A", rowIndex).Value = feature.Tags.Item(0);
rowIndex++;
excelSheet.Cell("A", rowIndex).Value = aqDateTime.Today();
excelFile.Save();
}

 

This works just fine but I was wondering if I could maybe use some different fonts or colours for these values?

Would be nice to distinguish some timings from the others by using a different font/colour but I can't seem to find a way to do this anywhere...

 

Thanks for the help!

  • A_Roskoshnyi's avatar
    A_Roskoshnyi
    Contributor
    4 years ago

    Take a look at this example (C#Script):

      var fileName = "Y:\\Automation\\Automation\\Testscenario's\\Timings.xlsx";
  var excel = Sys["OleObject"]("Excel.Application");
  excel["Workbooks"]["Open"](fileName);
  excel["Sheets"]("YOUR_SHEET_NAME")["Select"]();
  
  excel["Cells"](1,1)["Font"]["Color"] = dotNET["System_Drawing"]["ColorTranslator"]["ToOle"](dotNET["System_Drawing"]["Color"]["Red"]);
  excel["Cells"](1,1)["Value"] = feature["Tags"]["Item"](0);
  
  excel["Cells"](1,2)["Font"]["Color"] = dotNET["System_Drawing"]["ColorTranslator"]["ToOle"](dotNET["System_Drawing"]["Color"]["DarkOrange"]);
  excel["Cells"](1,2)["Value"] = aqDateTime["Today"]();
  
  excel["ActiveWorkbook"]["Save"]();
  excel["Quit"]();
    • sonya_m's avatar
      sonya_m
      SmartBear Alumni (Retired)
      4 years ago

      Thanks a lot, everyone! What a great discussion.

       

      hannecroonen how did you solve this in the end? Please share🙂

      • hannecroonen's avatar
        hannecroonen
        Contributor
        4 years ago

        Hi, I have not solved it yet, tried all of the above but it does not seem to work for me as it should... I don't know which cells need color beforehand so I could not really use the solutions above.

    • hannecroonen's avatar
      hannecroonen
      Contributor
      4 years ago

      Thanks for the suggestion.

      It was a very good possibility but I cannot seem to find the variable to enter the hotkeys... It is not possible on the value, nor the sheet nor the file...

      Maybe you have any idea?