Ask a Question

Write values to excel in different fonts/font colours

hannecroonen
Occasional Contributor

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!

6 REPLIES 6
mattb
Staff

Re: Write values to excel in different fonts/font colours

Just an idea here, 

If it is possible to change the color or font with hot keys that would be a very robust way to handle write or change the font/color. 

In TestComplete, the keys method allows us to enter hot keys: https://support.smartbear.com/testcomplete/docs/reference/test-objects/members/common-for-sys-deskto...

 

 

hannecroonen
Occasional Contributor

Re: Write values to excel in different fonts/font colours

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?

AlexKaras
Community Hero

Re: Write values to excel in different fonts/font colours

Hi,

 

Would be nice to distinguish some timings from the others by using a different font/colour

You need to work with Excel using COM and Excel's COM object model.

https://www.google.com/search?q=excel+com+script+change+cell+color

 

Regards,
  /Alex [Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame
================================
A_Roskoshnyi
Community Leader

Re: Write values to excel in different fonts/font colours

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
Community Manager

Re: Write values to excel in different fonts/font colours

Thanks a lot, everyone! What a great discussion.

 

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


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
hannecroonen
Occasional Contributor

Re: Write values to excel in different fonts/font colours

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.

cancel
Showing results for 
Search instead for 
Did you mean: