Merge and color Headers

Help Forum
, ,
1

Hi everyone,

My Requirement is I get datatable in a loop like

DT1
image

DT2
image

DT3
image

These are result from for each of list of datatables

Now i need the output like this in a single sheet

image

With headers merged and renamed and colored , please help me with suggestions
@ppr @Yoichi @Palaniyappan @Rahul_Unnikrishnan

Thanks in advance

2

Hi @kavya.s16 ,

Could you let us know if you are reading the Data from the excel file or is it available as a processed datatable only ?

3

@supermanPunch Data is available as processed datatable

4

Hello @kavya.s16

Here you will have to use a combination of uiPath activities.

1)For loop
2)Find First\Last cell activity ( to get the first and last free row in the excel to write the datatable)
3)Write range. Range you will get from step 2
4)Invoke code(you can find the details in the below doc, and you need to merge the cells dynamically)…Cells you will get from step2

1 Like
5

Hi @kavya.s16 ,

Could you check the below workflow :
Excel_AddRow_MergeAndColor.zip (12.6 KB)

It uses Interop.Excel for Merging and colouring the rows/cells.

  1. We create the row for Points, assign the point value, add it at the beginning of the datatable and add an Empty row at the end.
  2. We Merge datatable after the add rows operation is done for the datatable.
  3. Next, we write the merged datatable to the excel sheet.
  4. We find the cell positions where points value will be present by using a Linq Query and formulate the Cell Ranges for Merging.
  5. Next, the cell positions are provided to the Invoke Code Activity where the Merging and colouring will be done for the cells.

Let us know if this doesn’t work or gives out any error.

1 Like
6

Hi @supermanPunch
Thanks for the above solution
Please check the below link:

I have managed to do the coloring and merge headers , just i need to add in the single sheet with highest first with one line space between with formatting and color intact

Is there any way to do that?

7

@supermanPunch I made some changes to the above code and altered it according to the requirement and it worked perfectly fine and code is amazing Thank you for the help!!

1 Like
8

@supermanPunch just one more thing after merging and coloring the headers i need to auto fit column width so it looks even , is that possible ? for now i am using vba to autofit the result excel , is there any other way?

9

@kavya.s16 , Could you show us a Screenshot as to How the excel looks without the Auto fit, so we can understand what you are trying to achieve.

10

@supermanPunch
image

11

@kavya.s16 ,

Could you add the below Expression in the Invoke Code Activity as shown in the Screenshot below :

xlWorkSheet.Columns.Autofit()

image

Let us know if it doesn’t work.

12

@supermanPunch thanks it worked , can the headers also be left instead of center in alignment?

13

@kavya.s16 ,

You could try changing the Alignment as you require by modifying the HorizontalAlignment value :
image

1 Like
14

ok thanks!!

1 Like
15

@supermanPunch Interop.Excel this dependency from microsoft should only be used ? for this flow to work , is there any dependency from UiPath which will work for this flow

Since any dependency apart from UiPath is restricted to use…

16

@kavya.s16 , There might be other ways but they are either again from Microsoft or other companies.

I do not think UiPath has a Direct set of activities suitable for this i.e majorly the activities required for Merging the Cells and Colouring the Font as well as Highlighting the Merged Cells.

Currently, if you are using the Modern Design we can find the Format Cells Activity, which can colour the Font and Highlight the cells but Merge cells is not available.
image

Hence, we either do it using other External packages or VBA/vb.net/c# codes.

Maybe you can suggest a feature request for this purpose :slight_smile: Hopefully we should be able to use such features in the near future directly using UiPath.Excel.Activities package.

17

@supermanPunch okay thanks got it!!
Headers background to black , text 1 , lighter 25% and bold the headers

Is this possible, i couldn’t find property for lighter black @supermanPunch

18

@supermanPunch sorry for disturbing
but when i installed this Microsoft.office.interop.excel package

I am getting this error from workflow analyzer rule
Error ST-USG-010 Unused Dependencies Dependency package Microsoft.Office.Interop.Excel is not used.
Recommendation
Remove unused packages in order to improve process execution time. Learn more.

This is there sub or dependency , please help

19

@kavya.s16 , You could try Removing the dependency package and check whether it works fine, but mostly the Invoke code Activity would give out a validation error. In this case, you would have to reinstall it again.

20

@supermanPunch i tried installing and reinstalling i am not getting error in invoke , but from the workflow analyzer rule set stating unused dependency which will not allow to run untill resolved, below link

Error ST-USG-010 Unused Dependencies Dependency package Microsoft.Office.Interop.Excel is not used.
Recommendation
Remove unused packages in order to improve process execution time. Learn more.