Change format of the column automatically in destination excel

[Muralikrishna_Basani]

Hi Team,

I am trying to perform calculations in master sheet in some coloumn which is the percentage. After that, I am filtering some values according to some conditions and writing those rows in another excel.

Now in another excel, that percentage coloumn is not percentage anymore, it is changing to general. Example: 100.00% is changing to 1.

1. How to do this automatically.
2. If not how to change it later after another sheet is created.

I have tried it the following code after creating a sheet in invoke code and invoke vba activity but it is not working.

Sub ChangeFormat()
Range(“R1:R20”).NumberFormat=“0.00%”
End Sub

Also, I want it for full coloumn, not for any specific range

[ashwin.ashok ((TheCodingTheory))]

Hi @Muralikrishna_Basani ,

To preserve format, you can prepend a single quote to the value and it forces Excel to interpret it in the format it comes it.

E.g.

01 -> 1 //After pressing enter

'01 -> 01 //After pressing enter

So what you can do is, use a For Each Row in DataTable and prepend a comma like so:

Assign → To CurrentRow

"'"+CurrentRow(IndexOrColumnName).ToString

If the number of rows are large, then could you please let us know how many columns are there, and which position this particular column is on?

Providing us with some sample data to work with will put us in a better position to assist you.

Kind Regards,
Ashwin A.K

[ppr (Peter Preuss)]

@Muralikrishna_Basani

If not how to change it later after another sheet is created.

we can reformat with the help of Balreva Excel Activities package and activity Change Cell type
When working within Modern Design (Activities) we can use:

Activities - Format Cells

@Muralikrishna_Basani

full coloumn, not for any specific range

we could use e.g. A:A as range for entire a column. For some reasons we would recommend to use the row count and calculate the range for the used data block

[Muralikrishna_Basani]

I could not understand, please eloborate.

there might be 100-1000 rows after filtering and it is writing to another excel.

I need a percentage in only coloumn: Total Contribution like 100.00%, 54.83% like in original sheet. But it is writing like this as below

Location	EMP ID	Name of the Employee	DOJ	Designation	Level(L1,L2,L3,PM, SME)	Reporting Manager	Cost Center	Sub Cost Center	Bonus in appraisal	CTC	Monthly	Additional	Deductions	Dec	Weekly Hours	Worked Hours	Total Contribution
U	57	A	1-Jun-21			A	A								40	160	1
U	51	B	1-Jun-21			B	B								40	160	1
U	52	C	5-Jul-21			C	C								40	160	1
H	30	D	1-Oct-18			D	D								40	160	0.5483870967741935

[Palaniyappan (Forum Leader)]

Hi

Have a view on this marketplace component with which we can change the column format

UiPathGo - BalaReva Excel Activities

Hello Friends, I have successfully uploaded the BalaReva XL Activities in UiPath Go. This package contains 36 custom activities. It is related to the Workbook, worksheet and sheet data. It helps and simplifies more processes into the excel file. All components can be configured very user friendly. https://go.uipath.com/component/balareva-xl-activities This package is having the below activities. Pie Chart - Using these activities you can able to generate the Pie Chart Column Chart - Using…

Cheers @Muralikrishna_Basani

[ashwin.ashok ((TheCodingTheory))]

Hi @Muralikrishna_Basani ,

Is the format of that column preserved when you view the DataTable in the immediate panel?
Does the column value contain 100% or is it in the form you have shared with us when its in the bot memory?

Kind Regards,
Ashwin A.K

[Muralikrishna_Basani]

It is in 100%, 76% etc in original sheer where I am performing some calculations with few conditions:

Yes I checked “preserved format” in read range before writing

[ashwin.ashok ((TheCodingTheory))]

Hi @Muralikrishna_Basani ,

After reading the sheet, could you try querying the DataTable in the Immediate Panel and check if the percentages are present?

If so, then formats is maintained when the bot reads it, then you can simply use a For Each Row in DataTable Activity and assign this to the CurrentRow →

CurrentRow("Total Contribution") = "'"+CurrentRow("Total Contribution").ToString

The single quote forces excel to interpret the values as text, hence retaining the percentages.

If that is unsuccessful, then we would appreciate it if you could share the sample data with us so that we can test out few other scenarios from our end.

Kind Regards,
Ashwin A.K

[Muralikrishna_Basani]

@ashwin.ashok

After reading the sheet, could you try querying the DataTable in the Immediate Panel and check if the percentages are present?

After reading the sheet, could you try querying the DataTable in the Immediate Panel and check if the percentages are present? How to do this??

[ashwin.ashok ((TheCodingTheory))]

Hi @Muralikrishna_Basani ,

Right after reading the Excel(Read Range) and a Breakpoint on the next activity in the Sequence and run the process in Debug Mode.

The Bot will stop at the point, and if you look at the left hand side, you will notice that there is a tab called Immediate

Enter your DataTable variable name and check how the column values are being interpreted by the Bot.

As for your initial query, we would appreciate it if you could provide some sample data to work with.

Kind Regards,
Ashwin A.K

[ushu (Usha kiranmai)]

Use the below link it would help you

How to change a column type from General to Percentage in DataTable?

@gaurav.singh5 Firstly in Excel convert column into percentage with “Format cell”. secondly while reading Excel → Read Range → properties → Select “Preserve format”.

[Muralikrishna_Basani]

nothing in immediate panel nor in watch, i cant see the data table values. Even i gave write line