How to format excel sheet

excel
uiautomation
activities
studio
uipath

#1

While adding data to excel sheet through write range, is it possible to

  1. add different column title?
  2. add formatting to the sheet, such as alignment, font size, font style etc?

#2

To change column name, you can use this method inside Attach Window Activity:

“I6” being your cell position and anything after the plus sign inside parenthesis is your new text.

Can’t help you with #2, sorry.

image


#3

I have found that for formatting Excel that TypeInto key combinations work best, atleast until some of the VB internal Excel manipulation is unlocked.

To do this in Excel press the key “ALT”. You will notice some characters appear on the menu, and if you press one, another set of characters appear. So the goal is to find the key combination you need. For example, Bold would be “alt+h1” or rename sheet would be “alt+hor+text+enter”

To implement this, you set your combination in a string into the TypeInto activity
For example,

Select Range
TypeInto
"[k(alt)]hfc[k(down)][k(down)][k(down)][k(enter)]"

You can do pretty much all formatting styles as far as I can tell using these key combinations. You can also group multiple ones in one line to shorten your steps, and it is quicker than using element-based steps.

For Select Range, you will need to find out the Row or Column numbers using the datatable and .IndexOf()
For example,
dt.Rows.IndexOf(row).ToString+":"+dt.Rows.IndexOf(row).ToString

The above would return the range of “rownumber:rownumber” like if you are inside a ForEach, and you’ll get something like “3:3” or “5:5” to highlight that row.

Then, after the range is selected correctly the TypeInto will do the formatting.

Also, another trick is if you need to hold down a key like Ctrl+c, you would use “d” and “u” instead of “k”, like “[d(ctrl)]c[u(ctrl)]”

Hope this will be helpful, assuming you understood it all lol.