Excel Formatting Using Vb.Net Code | UiPath

To do excel formatting, there is no specific activities available in UiPath, so either you have to go for VBA or Vb.Net to get the required formatting. Both methods are good and fast.

Lets see how we can write a simple code to do our formatting.

First of all, if you are using this Microsoft.Office.Interop.Excel in UiPath, then there are chances that it will show you error, so you have to install this.

• First to go to Manage Packages and install this:

• If still shows error then open your .xaml file in Notepad and paste this below line along with another assemblies present in file:

• Now you are ready to use below Code, Use Invoke Code activity and paste this entire code into that and run.
• FullFilePath, SheetName---- Replace these with your File Path and Sheet Name, you can also make them as Argument and use that variable in place of this.

'--------------Code------------------
Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
Dim rng As Microsoft.Office.Interop.Excel.Range

excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb = excel.Workbooks.Open("FullFilePath")
excel.Visible=False

'===================== Worksheet=========================

ws=CType(wb.Worksheets("SheetName"),Microsoft.Office.Interop.Excel.Worksheet)
'===================== Table Border========================
ws.Range("B2:B3").Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
'=====================Font Bold===========================
ws.Range("D8:D13").Font.Bold = True
'=====================Split===============================
vGetRowNo=vName.Split(":"c)(0).Split("A"c)(1).Trim
'=====================Gray Header========================
ws.Range("A1:Z1").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray)
'====================Alignment, Center, Left, Right, AutoFit======
ws.Range("A1:Z1").VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter
ws.Range("A1:A4").HorizontalAlignment=Microsoft.Office.Interop.Excel.Constants.xlLeft

ws.UsedRange.HorizontalAlignment= Microsoft.Office.Interop.Excel.Constants.xlRight
ws.UsedRange.EntireColumn.AutoFit
wb.Save
wb.Close

Happy Automation!!