MS Excel - Cell Formatting for UiPath Studio

Description

Provides additional cell formatting actions for MS Excel workbooks in conjunction with the standard UiPath Excel activities. Please note these activities are not mandatory to be used within Excel application scope. The Excel sheet gets attached to the UiPath with the workbook name provided as input.

Prerequisites

NA

Execution

Kindly import the NuGet package to use the activities

Inputs 

CellsAutofit

Range (string) – Optional. 0 = Both (default); 1 = Rows only; 2 = Columns only.

WorksheetName (string) – Optional. Default to be the active Worksheet

WorkbookName (string) – Optional. Default to be the active Worksheet.

ColumnsOrRows (int) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

FontBold

Range (string) – Optional. Default to be the used range

WorksheetName (string) – Optional. Default to be the active Worksheet

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

Bold (bool) - Optional. Default to be True. Set cells bold or not bold

FontItalics

Range (string) – Optional. Default to be the used range.

WorksheetName (string) – Optional. Default to be the active Worksheet.

WorkbookName (string) - Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

Italics (bool) - Optional. Default to be True. Set cells in range to italics.

FontStrikethrough

Range (string) – Optional. Default to be the used

WorksheetName (string) – Optional. Default to be the active

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

Strikethrough (bool) - Optional. Default to be true. Set strikethrough or not strikethrough

FontUnderline

Range (string) – Optional. Default to be the used range.

WorksheetName (string) – Optional. Default to be the active Worksheet.

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

Underline (bool) – Optional. Default to be True. Set cell format in range to be underlined.

FormatCellAlignment

Range (string) –

Optional. Default to be the used range.

WorksheetName (string) - Optional. Default to be the active Worksheet.

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

VerticalAlignment (string) – Optional. Specify one of: Center; Distributed; Justify; Bottom; Top. Remains unchanged if not specified.

HorizontalAlignment (string) – Optional. Specify one of: Center; Distributed; Justify; Left; Right. Remains unchanged if not specified.

FormatPainter

Range (string) – Required. The range to copy format.

WorksheetName (string) – Optional. Default to be the active Worksheet

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

TargetRange (string) - Required.

SetNumberFormat

Range (string) – Required. The range to fill. The first cell of this range will apply the format

WorksheetName (string) – Optional. Default to be the active Worksheet

WorkbookName (string) – Optional. Default to be the active Workbook

NumberFormatText (string) – Optional. Default to be Unchanged

HighlightRange

Range (string) – Optional. Default to be the used range

WorksheetName (string) – Optional. Default to be the active Worksheet

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

colorIndex (int) – Optional. 1~56, there are 56 colors. The default highlight color is No Color.

SkipHidden (bool) – Optional. Defaul to be false

MergeorUnmergeRange

Range (string) – Required. The range of cells to be merged.

WorksheetName (string) - Optional. Default to be the active Worksheet

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

Merge (bool) – True for Merge and False for Unmerge

MergeAcross (bool) – Optional. Default is False, means merge range into a single cell. True to merge cells in each row of the specified range as separate merged cells.

SetRowHeightandColumnWidth

Range (string) – Optional. Default to be the used range

WorksheetName (string) - Optional. Default to be the active Worksheet

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

Width (int) – Optional. Set column width in the range.

Default to be unchanged. Unit is pixel.

Height (int) – Optional. Set row height in the range. Default to be unchanged. Unit is pixel. The value must be less or equal than 546.

HideRowsorColumns

WorksheetName (string) – Optional. Default to be the active Worksheet

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

RowsOrColumnsRef (string) – Required. Example: "5:8", "G: W"

UnhideRowsColumns

WorksheetName (string) – Optional. Default to be the active Worksheet

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

Ungrouping

WorksheetName (string) – Optional. Default to be the active Worksheet

WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.

OpenWorkbookByPassword

WorkbookFullPath (string) – Required. Full Path of Workbook

Password (string) – Optional. If no password, just leave it empty.

MaxTimeForWaiting (int) – Optional. Default to be 120s.

CloseAllInstances

Nil

Outputs

CellsAutofit

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

FontBold

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

FontItalics

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

FontStrikethrough

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

FontUnderline

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

FormatCellAlignment

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

FormatPainter

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

SetNumberFormat

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

HighlightRange

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

MergeorUnmergeRange

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

SetRowHeightandColumnWidth

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

HideRowsorColumns

Nil

UnhideRowsColumns

Success (bool) – True if succeeded

ErrorMes (string) – Error message if any

Ungrouping

Nil

OpenWorkbookByPassword

Name (string) - The name used by Excel to identify the workbook - usually this is the file name (without the path).

CloseAllInstances

Nil

Components Included

Process

Main

NuGet Package

MSExcelCellFormatting

List of Functionality

CellsAutofit - Changes the width of the columns in the range or the height of the rows in the range to achieve the best fit .Example :Changes the width of columns A through E to achieve the best fit, based only on the contents of cells A1:E1  range=A1:E1Changes the width of columns A through I  to achieve the best fit: range = A:I

FontBold - Set a range of cells to bold or not bold.

FontItalics - Set a range of cells to italics or not italics.

FontStrikethrough – Set a range of cells to strikethrough or un-strikethrough.

FontUnderline - Set format of a range of cells to underline or not underlined.

FormatCellAlignment - Sets the vertical and/or horizontal cell alignment for a range of cells.

FormatPainter - Copy the range A's format and apply the format to range B.

SetNumberFormat - Example:

General: General

Text: @

Number: 0

Currency: $#,##0.00

Date: m/d/yy

Time: h:mm:ss am/pm

Percentage: 0.00%

Scientific: 0.00E+00

Any Other Customized format

HighlightRange - Highlight the range with specified color. Example: ("A1,A4:C4,P11:V12",3,,)................. ColorIndex: 0: no color; 1: black; 2: white; 3: red; 4: green (Default); 5: Blue; 6: Yellow; 7: Pink; ............56.

MergeorUnmergeRange - Separate a merged area into individual cells or vice versa

SetRowHeightandColumnWidth - Set the Column Width or RowHeight of a range. The maximum height for an excel cell is 409.5 Points, equals to 546 pixels (1 Point = 4/3 Pixel).

The unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used. The transfer formula between character and point is: 1 character = ( 1 Pixel - 5)/7. Sometimes we set the rate of Width to Height to be 4:3, so if the row height is 546, the width should be 546*4/3=728. So width = 728, height = 546 is one of the recommended combo. Example: ("A1:F3", 728, 546,,)..........

Example: ("A1:F3", , ,,) -> RowHeight and ColumnWidth keep unchanged.

HideRowsorColumns - Hide rows Or columns in a worksheet

UnhideRowsColumns - Unhide all rows and columns in a worksheet

Ungrouping All - Data -> Ungroup -> Clear outline

OpenWorkbookByPassword - The handler is from the default instance which is the first instance. This function could open the Excel file directly from Remove Server by using the http or https path or UNC path, while the standard Excel VBO could only use UNC path.

CloseAllInstances - Closes all instances of excel which have been opened by this business object, either through "Create Instance" or "Open Instance" and which have not been discarded through "Close Instance" or "Release Instance"