Uncategorized

Formatting Options

November 1, 2024January 28, 2025 by Melanie van Wyk

Introduction

In Formatted Reports, Formatting Options are essential for transforming raw data into a polished, functional Excel report. With formatting tools, you can control data layout, specify style elements, and apply detailed cell-level customizations to enhance readability and improve user interaction.

Creating a Stylesheet

The first step in applying formatting is creating a stylesheet. This stylesheet is generated from the data matrix using IronPython:

data = Qx.ToMatrix([
  ["Code", "Name", "Date", "Percentage", "Value", "Select"],
  ["DELL", "DELL STOCK", Qx.Date("2024-01-01"), 0.45, 1000.00, "Red"],
  ["MSFT", "MICROSOFT STOCK", Qx.Date("2024-01-02"), 0.30, 2500.00, "Green"],
  ["APPL", "APPLE CORPORATION", Qx.Date("2024-08-02"), 0.25, 2000.00, "Blue"]
])
sheet = Qx.MakeStyleSheet(data)

This creates a formatted template where further adjustments to layout and style can be made.

Customizing Layout and Dimensions

Setting Column Widths and Row Heights

To improve the appearance and readability of the report, specify the width of columns and the height of rows:

# Column width
sheet.SetColumnWidth(1, 30)  # Adjusts the width of the second column to 30
sheet.SetColumnWidth(2, 11)  # Adjusts the width of the third column to 11
sheet.SetColumnWidths(3, 2, 15)  # Adjusts width of multiple columns, starting at column 4 and adjusts the width of 2 columns (Columns 4 and 5) to 15

# Row height
sheet.SetRowHeights(0, sheet.Height - 1, 15)  # Sets height across specified range. Sets the height of rows, starting at the first, for the number of rows = to the number in the sheet -1 to 15

Naming the Sheet

Assigning a unique name to the sheet helps with identification, especially during debugging in Excel:

sheet.Name = "MySheetName"

Extracting Raw Data

If you need the underlying data without formatting, use the ToMatrix function to return the data in a two-dimensional array:

rawData = sheet.ToMatrix()

Selecting Ranges and Cells

Range Selection

Formatted Reports allow selecting specific ranges of cells for further customization. Here are several selection methods:

# Selecting rows and columns
firstRow = sheet.Row(0)  # Select the first row and assign it to firstRow
multiplerows = sheet.Rows(1, 3)  # Select rows from 2 to 4 and assign to variable multipleRows

# Selecting a box range
box = sheet.Box(0, 0, 3, 4)  # Select a range starting at the first row and column with a height of 3 rows and a width of 4 columns

# Selecting all cells
allcells = sheet.All  # Select all the rows from the sheet variable

# Selecting a range within a range
somecells = box.Columns(0, 2, 3, 1)  # Select a 3x1 range from the box variable

Single Cell Selection

To apply formatting or operations to an individual cell, you can use:

# Selecting a specific cell
cell = sheet.Cell(1, 1)  # Select the cell at row 2, column 2 from the sheet variable
cell = somecells.Cell(0, 0)  # Select the cell at the first row, first column from a different range called somecells

Style Configuration

With a selected range, you can apply various styles to enhance visual structure and emphasize key data.

General Format Settings

styleRange.Format = "0.00"  # Any valid Excel format can be used here e.g., General, 0.00%, dd/mm/yyyy, etc.

Borders

Specify border styles for each side or region (inside, outside, top, left, bottom, right):

styleRange.Borders.Inside.Style = "Continuous"  # or Dash, DashDot, Dot, etc.
styleRange.Borders.Inside.Weight = "Thin"       # or Hairline, Medium, Thick
styleRange.Borders.Inside.Color = "255,0,0"     # Red as RGB

# Example for an outside border
styleRange.Borders.Outside.Style = "Double"
styleRange.Borders.Outside.Weight = "Thick"
styleRange.Borders.Outside.Color = "0,255,0"    # Green

Background Colour

Set a background colour for emphasis:

styleRange.BackgroundColor = "200,200,255" # Light blue in RGB

Font Settings

Define font details, such as typeface, size, color, and styles like bold or italic:

styleRange.Font.Name = "Arial"
styleRange.Font.Size = 12
styleRange.Font.Color = "0,0,255"  # Blue font
styleRange.Font.Bold = True
styleRange.Font.Italic = False
styleRange.Font.Underline = True

Alignment

Set horizontal and vertical alignment for optimal text positioning:

styleRange.HorizontalAlignment = "Center"     # Other options: Left, Right, etc.
styleRange.VerticalAlignment = "Top"          # Other options: Bottom, Center, etc.

Additional Cell Methods

For single-cell manipulation, methods such as Merge enable further customization:

# Merge cells across specified width and height
cell.Merge(Width=2, Height=3, Across=True)