Formatting and addition of the formula in excel

Help Activities
, ,
1

I have excel sheet with 6 columns
i want columns named Amount1 Amount2 and Amount3 in currency format from General

and columns named Tax1 , Tax2 and Tax3 columns in numbers format from general and also want to add the numbers columns with 3 decimals.

and then need to create a new column as New Tax% in H column and calculate sum of columns of column A,
format excel.xlsx (8.2 KB)
C and E/100 in the New Tax% change to percentage format with 3 decimals

2
  1. Excel Application Scope (Input: Excel file path)
    2. Read Range (Output: DataTable)
    3. Add Data Column (DisplayName: “Amount1”, “Amount2”, “Amount3”, “Tax1”, “Tax2”, “Tax3”, “New Tax%”)
    4. For Each Row (Input: DataTable)
    5. Assign activity: row(“Amount1”) = Convert.ToDecimal(row(“Column1”)).ToString(“C”)
    6. Assign activity: row(“Amount2”) = Convert.ToDecimal(row(“Column2”)).ToString(“C”)
    7. Assign activity: row(“Amount3”) = Convert.ToDecimal(row(“Column3”)).ToString(“C”)
    8. Assign activity: row(“Tax1”) = Convert.ToDecimal(row(“Column4”)).ToString(“N3”)
    9. Assign activity: row(“Tax2”) = Convert.ToDecimal(row(“Column5”)).ToString(“N3”)
    10. Assign activity: row(“Tax3”) = Convert.ToDecimal(row(“Column6”)).ToString(“N3”)
    11. Assign activity: row(“New Tax%”) = (Convert.ToDecimal(row(“Column1”)) + Convert.ToDecimal(row(“Column3”)) + Convert.ToDecimal(row(“Column5”))) / 100
    .ToString(“P3”)
    12. Write Range (Input: DataTable, Output: Excel file path)

Regards,
Mohamed Farhan

3

may i know what is .ToString(“P3”)

4

iam getting error as

image
image1005×610 32 KB

5
  • ToString() is a method used to convert the value to a string.
  • "P3" is a format specifier indicating that the number should be formatted as a percentage with three decimal places.

For example, if you have a decimal value 0.12345, using .ToString("P3") would result in "12.345%". It multiplies the value by 100 and adds the percentage symbol (“%”) at the end.

So, in the context of UiPath, row("New Tax%") = (Convert.ToDecimal(row("Column1")) + Convert.ToDecimal(row("Column3")) + Convert.ToDecimal(row("Column5"))) / 100 .ToString("P3") would calculate the sum of certain columns, divide the result by 100, and then convert it to a string representing a percentage with three decimal places.

Regards
Mohamed Farhan

6

getting error

image
image1356×327 14.5 KB

7

@T_Y_Raju,

You can achieve this using Excel Process Scope. Here is sample solution. You haven’t mentioned what to do with column G so it’s kept as it is but if you have to do anything with it you can reuse the logic of other columns.

Execution:
Excel Formatting

Solution:

image
image591×2139 100 KB

Sample Code:
Excel Formatting.xaml (15.3 KB)

Output File:
format excel.xlsx (9.2 KB)

Thanks,
Ashok :slight_smile:

8

how can we do this dynamically, it should not be hard coded for range

9

@T_Y_Raju,

In this case you should be finding the Column using Find/Replace Activity.

So whatever Column you you find the columns named Amount1 Amount2 and Amount3 etc, you can pass it to my code and it will work.

I hope you would be able to add this simple logic.

Thanks,
Ashok :slight_smile: