Excel Formatting Features

Title: Format Excel Range (and make your Excel outputs look good)

Short Description:

• Apply a wide range of formatting features to a single cell or range of cells in a spreadsheet

Formatting Features:

1. FILL CELLS WITH COLOR:

• Fill the Header row or Data rows with any color

Sample 1: Header Row

Sample 2: Header and Data rows

2. CHANGE FONT COLOR:

• Apply the font color of your choice to one or more cells

Sample 1: Font color of Header row to white

Sample 2: Font color of Data rows to blue

3. ALTERNATE ROW SHADING:

• Set alternate rows as per the color of your choice
• The number of rows to be alternate is configurable. Can be 1, 2 or any number

Sample 1: Light Aqua alternate rows

Sample 2: Gray alternate rows

4. HORIZONTAL ALIGNMENT:

• Set horizontal alignment of text in one or more cells
• Alignment options available:
  • Left
  • Center
  • Right

Sample:

5. APPLY BORDERS:

• Apply borders to cells with color, weight and style
• Options available:
  • STYLE: Continuous, Dash, DashDot, DashDotDot, Dot, Double, SlantDashDot, LineStyleNone
  • WEIGHT:  Hairline, Medium, Thin, Thick
  • COLOR: any specified color

Sample: Dash style, Medium thickness and Red colored border

6. APPLY A NUMBER FORMAT:

• Apply any of the available format code to the cells (decimals, currency, percent, date formats, etc)
• Either from the list of existing categories (left) or a Custom category (right)

Sample:

7. AUTOFIT CELLS:

• Auto adjust height of all rows and width of all columns to get a best fit

Sample:

8. TURN OFF GRID LINES:

• Removes the default Excel gridlines

Sample:     

9. CLEAR CONTENTS:

• Clears formulas and values without clearing cell formatting and conditional formatting

Sample:

10. CLEAR:

• Clears cell values, formatting and formulas

Sample:

11, 12, 13. FONT BOLD, UNDERLINE, ITALIC:

• Three different functions to set the font to bold, underline and italicize

Sample:

14. CHANGE FONT SIZE:

• Change the font size to a new number

Sample:

15. CHANGE FONT NAME:

• Change the font to a new name

Sample:

Attachments:

1. Main.txt: Copy the contents of this file and paste it into the editor of the Main flow

2. FormatExcelRange.txt: Copy the contents of this file and paste it into a Subflow by giving it a name as FormatExcelRange. Ensure to give the right name, spelling and case

3. ReadMe.txt: Copy the contents of this file and paste it into a Subflow by giving it a name as ReadMe. Ensure to give the right name, spelling and case

4. In the end you will have a Main Flow and Subflows as below

     5. The Sales Records.xlsx Excel sample used in the above screenshots is available in the attachments.

Some key notes:

• This Flow was built in PAD version 2.18. It is recommended to first run these formatting features in a sample dummy process to avoid any version compatibility issues. Also take a backup of your Excel before applying any formatting feature(s).
• To start with: Simply change the ExcelFilePath and run the sample process. Once everything looks fine make changes by adding/removing as required.
• General exception handling is done wherever possible. You can add your own detailed exception handling as necessary.
• The Main page code looks long because I have shown how to call/use every single formatting feature. You just need to use only the one(s) you need.
• Refer the ReadMe tab inside the Subflow. Also read the comments on the Main Flow.
• Please send me a private message if you are facing any issues.