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:
- Main.txt: Copy the contents of this file and paste it into the editor of the Main flow
- 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
- 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
- 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.
