Core API Reference

sql2excel

SQL2Excel — Generate Excel reports with charts from SQL queries and Pandas DataFrames.

Public API

Report Top-level orchestrator that executes SQL queries and writes Excel workbooks. QueryConfig Configuration object pairing a SQL query with Excel/chart parameters. SQLExecutor Executes SQL queries via SQLAlchemy and returns pandas DataFrames. parse_sql_file Parse an annotated .sql file into a list of QueryConfig objects. Config Default configuration constants for charts, formatting, and layout. ExcelHelper openpyxl utility layer for cell formatting, chart styling, and layout.

Chart Classes

Chart Base class for all chart types. LineChart Line chart. BarChart Bar chart. PieChart Pie chart with data-label customization. RadarChart Radar (spider) chart. AreaChart Area chart. ScatterChart Scatter plot with flexible data selection. BubbleChart Bubble chart using x, y, and size columns. StackedBarChart Stacked or percent-stacked bar chart. BarLineChart Combined bar and line chart with dual y-axes. SingleAxisBarLineChart Combined bar and line chart on a single y-axis. ImageChart Embed images or matplotlib figures into Excel. LineBasedChart Abstract base class for line and radar charts. TwoAxesChart Abstract base class for charts with two y-axes.

AreaChart(config=None, excel_helper=None)

Bases: Chart

Area chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration object containing chart and general Excel settings.	None
excel_helper	ExcelHelper	Helper object for Excel-specific operations.	None

See Also

Chart : Base class for all chart types.

Initialize an area chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration overrides. Defaults to Config().	None
excel_helper	ExcelHelper	Helper instance. Created from config when not provided.	None

Source code in sql2excel/chart.py

def __init__(self, config=None, excel_helper=None):
    """Initialize an area chart.

    Parameters
    ----------
    config : Config, optional
        Configuration overrides. Defaults to ``Config()``.
    excel_helper : ExcelHelper, optional
        Helper instance. Created from *config* when not provided.
    """
    super().__init__(config, excel_helper)

BarChart(config=None, excel_helper=None)

Bases: Chart

Bar chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration object containing chart and general Excel settings.	None
excel_helper	ExcelHelper	Helper object for Excel-specific operations.	None

See Also

Chart : Base class for all chart types.

Initialize a bar chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration overrides. Defaults to Config().	None
excel_helper	ExcelHelper	Helper instance. Created from config when not provided.	None

Source code in sql2excel/chart.py

def __init__(self, config=None, excel_helper=None):
    """Initialize a bar chart.

    Parameters
    ----------
    config : Config, optional
        Configuration overrides. Defaults to ``Config()``.
    excel_helper : ExcelHelper, optional
        Helper instance. Created from *config* when not provided.
    """
    super().__init__(config, excel_helper)

BarLineChart(config=None, excel_helper=None)

Bases: TwoAxesChart

Combined bar and line chart with dual y-axes.

The bar chart is rendered on the primary y-axis and the line chart on the secondary y-axis.

Parameters:

Name	Type	Description	Default
config	Config	Configuration object containing chart and general Excel settings.	None
excel_helper	ExcelHelper	Helper object for Excel-specific operations.	None

Attributes:

Name	Type	Description
chart	None or Chart	Placeholder for the combined chart object.
chart1	BarChart	The bar chart instance (primary axis).
chart2	LineChart	The line chart instance (secondary axis).

See Also

Chart : Base class for all chart types. TwoAxesChart : Parent class.

Initialize a bar-line chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration overrides. Defaults to Config().	None
excel_helper	ExcelHelper	Helper instance. Created from config when not provided.	None

Source code in sql2excel/chart.py

def __init__(self, config=None, excel_helper=None):
    """Initialize a bar-line chart.

    Parameters
    ----------
    config : Config, optional
        Configuration overrides. Defaults to ``Config()``.
    excel_helper : ExcelHelper, optional
        Helper instance. Created from *config* when not provided.
    """
    super().__init__(config, excel_helper)
    self.chart = None
    self.chart1 = xl.chart.BarChart()
    self.chart2 = xl.chart.LineChart()

plot(df, ws, **kwargs)

Plot a combined bar-line chart from df into ws.

Parameters:

Name	Type	Description	Default
df	DataFrame	Data to plot; first column is the category axis.	required
ws	Worksheet	The target worksheet.	required
**kwargs	dict	Chart customization options (see Chart._plot).	{}

Source code in sql2excel/chart.py

def plot(self, df, ws, **kwargs):
    """Plot a combined bar-line chart from *df* into *ws*.

    Parameters
    ----------
    df : pandas.DataFrame
        Data to plot; first column is the category axis.
    ws : Worksheet
        The target worksheet.
    **kwargs : dict
        Chart customization options (see ``Chart._plot``).
    """

    # Override default
    custom_colors = kwargs.get("custom_colors")
    kwargs["custom_colors"] = kwargs.get("custom_colors", False)

    self.chart1.type = "col"
    self.chart1.style = kwargs.get("bar_chart_style", 10)
    self.chart1.shape = kwargs.get("chart_shape", 4)

    super().plot(df, ws, **kwargs)
    self.chart1.y_axis.majorGridlines = None

    self.chart2.style = kwargs.get("line_chart_style", 10)
    line_width = kwargs.get("line_width", 1.5)
    line_style = kwargs.get("line_style", "sysDash")
    smooth = kwargs.get("smooth", True)
    kwargs["custom_colors"] = custom_colors

    for idx, series in enumerate(self.chart2.series):
        idx += 1
        color = (
            self.config.PRIMARY_COLORS[(idx) % len(self.config.PRIMARY_COLORS)]
            if self.config.CUSTOM_COLORS or kwargs.get("custom_colors")
            # Recycle colors if needed
            else None
        )

        # Use line color if it is user-defined
        if not kwargs.get("line_color") and idx == 1:
            # color = "FD625E"
            pass
        else:
            color = kwargs.get("line_color") or color

        self.excel_helper.set_line_graphical_properties(
            series,
            line_width,
            style=line_style,
            color=color,
            smooth=smooth,
        )

        marker_symbol = kwargs.get("marker_symbol", "circle")
        marker_size = kwargs.get("marker_size", 6)
        self.excel_helper.set_marker_graphical_properties(
            series, marker_symbol, marker_size, color
        )

BubbleChart(config=None, excel_helper=None)

Bases: Chart

Bubble chart using x, y, and size columns from a DataFrame.

The first three columns of the DataFrame are used as the x-axis, y-axis, and bubble size respectively. Each row is plotted as a separate series.

Parameters:

Name	Type	Description	Default
config	Config	Configuration object containing chart and general Excel settings.	None
excel_helper	ExcelHelper	Helper object for Excel-specific operations.	None

See Also

Chart : Base class for all chart types.

Initialize a bubble chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration overrides. Defaults to Config().	None
excel_helper	ExcelHelper	Helper instance. Created from config when not provided.	None

Source code in sql2excel/chart.py

def __init__(self, config=None, excel_helper=None):
    """Initialize a bubble chart.

    Parameters
    ----------
    config : Config, optional
        Configuration overrides. Defaults to ``Config()``.
    excel_helper : ExcelHelper, optional
        Helper instance. Created from *config* when not provided.
    """
    super().__init__(config, excel_helper)

Chart(config=None, excel_helper=None)

Base class for all charts in SQL2Excel.

This class provides the methods and attributes for creating, configuring, and rendering charts in Excel workbooks using pandas DataFrames and openpyxl.

Parameters:

Name	Type	Description	Default
config	Config	Configuration object containing chart and general Excel settings.	None
excel_helper	ExcelHelper	Helper object for Excel-specific operations.	None

Notes

• If Excel-related code fails, a user warning is issued and a default value is used instead of raising an exception. This design choice is to prevent interrupting SQL queries on the failure of Excel-related code.
• Subclasses should override plot and add_image to implement specific charting and image insertion logic.

Initialize the chart with optional configuration and helper.

Parameters:

Name	Type	Description	Default
config	Config	Configuration overrides. Defaults to Config().	None
excel_helper	ExcelHelper	Helper object for Excel-specific operations.	None

Source code in sql2excel/chart.py

def __init__(self, config=None, excel_helper=None):
    """Initialize the chart with optional configuration and helper.

    Parameters
    ----------
    config : Config, optional
        Configuration overrides. Defaults to ``Config()``.
    excel_helper : ExcelHelper, optional
        Helper object for Excel-specific operations.
    """
    self.config = config or Config()
    self.excel_helper = excel_helper or ExcelHelper(self.config)

    # Chart: this must be initialized in `plot` method which should be overriden
    self.chart: xl.chart.Chart = None

    # Data range
    self.min_row = -1
    self.min_col = -1
    self.max_row = -1
    self.max_col = -1

    # Indexes of columns utilized for chart generation
    # This is particularly useful when the DataFrame contains multiple
    # columns, but only a subset is used for the chart
    self.data_columns = None

    # Reference column
    # NOTE 'reference_column' is the column contain a single unique value (baseline)
    self.ref_series_idx = None

add_image(image_path, ws, df=None, **kwargs)

Adds an image to the given worksheet.

Parameters:

Name	Type	Description	Default
image_path	str	The file path to the image to be inserted.	required
ws	object	The worksheet object where the image will be added.	required
df	DataFrame	DataFrame that may be written alongside the image (default is None).	None
**kwargs		Additional keyword arguments for image customization, such as position, size, or formatting.	{}

Raises:

Type	Description
NotImplementedError	If the method is not implemented in the subclass.

Notes

This method should be implemented by subclasses.

Source code in sql2excel/chart.py

def add_image(self, image_path: str, ws, df=None, **kwargs):
    """
    Adds an image to the given worksheet.

    Parameters
    ----------
    image_path : str
        The file path to the image to be inserted.
    ws : object
        The worksheet object where the image will be added.
    df : pandas.DataFrame, optional
        DataFrame that may be written alongside the image (default is None).
    **kwargs
        Additional keyword arguments for image customization, such as position,
        size, or formatting.

    Raises
    ------
    NotImplementedError
        If the method is not implemented in the subclass.

    Notes
    -----
    This method should be implemented by subclasses.
    """

    raise NotImplementedError("This method has not been implemented yet")

plot(df, ws, **kwargs)

Plots data from a DataFrame onto a worksheet.

Parameters:

Name	Type	Description	Default
df	DataFrame	The DataFrame containing the data to plot.	required
ws	object	The worksheet object where the plot will be rendered.	required
**kwargs		Additional keyword arguments for customizing the plot.	{}

Raises:

Type	Description
NotImplementedError	If the method is not implemented.

Notes

This method should be implemented by subclasses.

Source code in sql2excel/chart.py

def plot(self, df, ws, **kwargs):
    """
    Plots data from a DataFrame onto a worksheet.

    Parameters
    ----------
    df : pandas.DataFrame
        The DataFrame containing the data to plot.
    ws : object
        The worksheet object where the plot will be rendered.
    **kwargs
        Additional keyword arguments for customizing the plot.

    Raises
    ------
    NotImplementedError
        If the method is not implemented.

    Notes
    -----
    This method should be implemented by subclasses.
    """

    raise NotImplementedError("This method have not been implemented yet")

write_dataframe(df, ws, **kwargs)

Writes a pandas DataFrame to an Excel worksheet.

Parameters:

Name	Type	Description	Default
df	DataFrame	The DataFrame to write to the worksheet.	required
ws	Worksheet	The openpyxl worksheet where the DataFrame will be written.	required
**kwargs	dict	Additional keyword arguments: section_heading : str, optional A heading to be written above the DataFrame in the worksheet. headings : iterable of str, optional Custom column headings to use instead of DataFrame's columns. Any other keyword arguments required by self.excel_helper.get_starting_position.	{}

Notes

• The first column is always treated as the x-axis (categories).
• If headings is provided, DataFrame's column names are ignored.

Source code in sql2excel/chart.py

def write_dataframe(
    self, df: pd.DataFrame, ws: xl.worksheet.worksheet.Worksheet, **kwargs
):
    """Writes a pandas DataFrame to an Excel worksheet.

    Parameters
    ----------
    df : pd.DataFrame
        The DataFrame to write to the worksheet.
    ws : openpyxl.worksheet.worksheet.Worksheet
        The openpyxl worksheet where the DataFrame will be written.
    **kwargs : dict, optional
        Additional keyword arguments:
            section_heading : str, optional
                A heading to be written above the DataFrame in the worksheet.
            headings : iterable of str, optional
                Custom column headings to use instead of DataFrame's columns.
            Any other keyword arguments required by `self.excel_helper.get_starting_position`.

    Notes
    -----
    - The first column is always treated as the x-axis (categories).
    - If `headings` is provided, DataFrame's column names are ignored.
    """

    # Chart position
    row_start, column_start = self.excel_helper.get_starting_position(ws, **kwargs)

    # A heading describing the data
    section_heading = kwargs.get("section_heading")
    if section_heading:
        ws.cell(row=row_start, column=column_start, value=section_heading)

        self.excel_helper.set_section_heading_font(
            ws.cell(row=row_start, column=column_start)
        )

        # Update
        row_start += 1

    r, c = df.shape
    # `row_min` includes the headings
    self.min_row = row_start
    # First column is always the x-axis (categories) - Not a part of the data range
    self.min_col = column_start + 1
    self.max_row = row_start + r
    self.max_col = column_start + c - 1

    # Column headings should be an iterable of str
    headings = kwargs.get("headings")
    if headings:
        for idx, heading in enumerate(headings):
            ws.cell(row=row_start, column=column_start + idx, value=heading)

        row_start += 1

        # Ignore column names in df
        rows = dataframe_to_rows(df, index=False, header=False)
    else:
        rows = dataframe_to_rows(df, index=False, header=True)

    for row_idx, row in enumerate(rows):
        for col_idx in range(len(row)):
            ws.cell(
                row=row_start + row_idx,
                column=column_start + col_idx,
                value=row[col_idx],
            )

write_dataframes_side_by_side(objs, ws, **kwargs)

Write multiple pandas DataFrames side by side into an Excel worksheet.

Parameters:

Name	Type	Description	Default
objs	Sequence[DataFrame]	Sequence of pandas DataFrames to be written to the worksheet.	required
ws	Worksheet	The worksheet where the DataFrames will be written.	required
**kwargs	dict	Additional keyword arguments: section_heading : str, optional Title to add above the DataFrames section. df_headings : list of str, optional Custom headings for each DataFrame. The length should match the number of DataFrames in objs. row_start : int, optional The starting row for writing DataFrames. Defaults to the value returned by get_starting_position. column_start : int, optional The starting column for writing DataFrames. Defaults to 1 (the first column). headings : list of list of str, optional Custom column headings for each DataFrame. Each element should be a list of headings for the corresponding DataFrame. Other keyword arguments are passed to the underlying DataFrame writing method.	{}

See Also

write_dataframe : Method used to write individual DataFrames.

Notes

• DataFrames are written side by side, separated by a configurable number of columns.
• The method delegates the actual writing of each DataFrame to write_dataframe.

Source code in sql2excel/chart.py

def write_dataframes_side_by_side(
    self,
    objs: Sequence[pd.DataFrame],
    ws: xl.worksheet.worksheet.Worksheet,
    **kwargs,
):
    """
    Write multiple pandas DataFrames side by side into an Excel worksheet.

    Parameters
    ----------
    objs : Sequence[pd.DataFrame]
        Sequence of pandas DataFrames to be written to the worksheet.
    ws : openpyxl.worksheet.worksheet.Worksheet
        The worksheet where the DataFrames will be written.
    **kwargs : dict, optional
        Additional keyword arguments:
            section_heading : str, optional
                Title to add above the DataFrames section.
            df_headings : list of str, optional
                Custom headings for each DataFrame. The length should match the number of DataFrames in `objs`.
            row_start : int, optional
                The starting row for writing DataFrames. Defaults to the value returned by `get_starting_position`.
            column_start : int, optional
                The starting column for writing DataFrames. Defaults to 1 (the first column).
            headings : list of list of str, optional
                Custom column headings for each DataFrame. Each element should be a list of headings for the corresponding DataFrame.
            Other keyword arguments are passed to the underlying DataFrame writing method.

    See Also
    --------
    write_dataframe : Method used to write individual DataFrames.

    Notes
    -----
    - DataFrames are written side by side, separated by a configurable number of columns.
    - The method delegates the actual writing of each DataFrame to `write_dataframe`.
    """

    row_start, column_start = self.excel_helper.get_starting_position(ws, **kwargs)

    # A heading describing the data
    section_heading = kwargs.get("section_heading")
    if section_heading:
        ws.cell(row=row_start, column=column_start, value=section_heading)

        self.excel_helper.set_section_heading_font(
            ws.cell(row=row_start, column=column_start)
        )

        # Delete because `write_dataframe` will be delegated for writing individual df
        del kwargs["section_heading"]

        # Update
        row_start += 1

    df_headings = kwargs.get("df_headings")
    headings_seq = kwargs.get("headings")
    if headings_seq:
        del kwargs["headings"]

    n_columns = 0
    for idx, df in enumerate(objs):
        current_column = column_start + n_columns
        current_row = row_start
        if df_headings:
            try:
                ws.cell(
                    row=row_start, column=current_column, value=df_headings[idx]
                )

                self.excel_helper.set_df_title_font(
                    ws.cell(row=row_start, column=current_column), **kwargs
                )
            except IndexError:
                warnings.warn(
                    "The number of DataFrame headings provided does not match the number of DataFrames",
                    category=UserWarning,
                    stacklevel=1,
                )

            current_row += 1

        # Write the current dataframe at the specified position (current_row, current_column)
        headings = headings_seq[idx] if headings_seq else None
        self.write_dataframe(
            df,
            ws,
            row_start=current_row,
            column_start=current_column,
            headings=headings,
            **kwargs,
        )

        n_columns += len(df.columns) + self.config.DATA_DATA_SEPARATOR

Config

Default configuration constants for charts, formatting, and layout.

All attributes are class-level constants used throughout the package. Override by passing a custom Config instance (or subclass) to Report, Chart, or ExcelHelper.

ExcelHelper(config=None)

Initialize the helper with an optional configuration.

Parameters:

Name	Type	Description	Default
config	Config	Configuration overrides. Defaults to Config().	None

Source code in sql2excel/excel_helper.py

def __init__(self, config=None) -> None:
    """Initialize the helper with an optional configuration.

    Parameters
    ----------
    config : Config, optional
        Configuration overrides. Defaults to ``Config()``.
    """
    self.config = config or Config()

fill(series, color=None, border_line_color=None)

Set solid fill and border line colour for a chart series.

Parameters:

Name	Type	Description	Default
series	Series	The openpyxl chart series to modify.	required
color	str	Fill colour as an sRGB hex string.	None
border_line_color	str	Border line colour as an sRGB hex string. Defaults to color when not provided.	None

Returns:

Type	Description
None

Source code in sql2excel/excel_helper.py

def fill(
    self,
    series: xl.chart.series.Series,
    color: str = None,
    border_line_color=None,
) -> None:
    """Set solid fill and border line colour for a chart series.

    Parameters
    ----------
    series : Series
        The openpyxl chart series to modify.
    color : str, optional
        Fill colour as an sRGB hex string.
    border_line_color : str, optional
        Border line colour as an sRGB hex string. Defaults to *color*
        when not provided.

    Returns
    -------
    None
    """

    color = colors.ColorChoice(srgbClr=color)
    if color:
        series.graphicalProperties.solidFill = color

        border_line_color = (
            colors.ColorChoice(srgbClr=border_line_color)
            if border_line_color
            else color
        )
        series.graphicalProperties.line.solidFill = border_line_color

fill_data_point(series, series_length)

Assign a distinct colour to each data point in a single-series chart.

Parameters:

Name	Type	Description	Default
series	Series	The openpyxl chart series to modify.	required
series_length	int	The number of data points (rows) in the series.	required

Source code in sql2excel/excel_helper.py

def fill_data_point(self, series, series_length):
    """Assign a distinct colour to each data point in a single-series chart.

    Parameters
    ----------
    series : Series
        The openpyxl chart series to modify.
    series_length : int
        The number of data points (rows) in the series.
    """
    # NOTE Accessing series length requires referencing the worksheet and finding the cell range
    # It is easier to let the user provides this
    data_points = []
    for idx in range(series_length):
        pt = xl.chart.marker.DataPoint(idx=idx)
        pt.graphicalProperties.solidFill = xl.drawing.colors.ColorChoice(
            # Recycle colors if needed
            srgbClr=self.config.PRIMARY_COLORS[
                idx % len(self.config.PRIMARY_COLORS)
            ]
        )
        data_points.append(pt)

        series.data_points = data_points

get_column_letter(col)

Normalize a column reference to an Excel column letter.

Parameters:

Name	Type	Description	Default
col	str, int, or None	A column letter ("A"), 1-based integer, or None.	required

Returns:

Type	Description
str	The corresponding column letter. Defaults to "A" for None or invalid values.

Warns:

Type	Description
UserWarning	If col is out of the valid range (1–16384).

Source code in sql2excel/excel_helper.py

def get_column_letter(self, col: str | int | None) -> str:
    """Normalize a column reference to an Excel column letter.

    Parameters
    ----------
    col : str, int, or None
        A column letter (``"A"``), 1-based integer, or ``None``.

    Returns
    -------
    str
        The corresponding column letter. Defaults to ``"A"`` for
        ``None`` or invalid values.

    Warns
    -----
    UserWarning
        If *col* is out of the valid range (1–16384).
    """
    if col is None:
        return "A"
    elif isinstance(col, str):
        col_index = xl.utils.cell.column_index_from_string(col)
        # max number of columns = 16384
        if col_index > 16384:
            # Silently reset the column if invalid
            col = "A"
            warnings.warn(
                "Invalid column. Using column 'A' instead", category=UserWarning
            )
        return col
    elif isinstance(col, int) and col > 0 and col <= 16384:
        return xl.utils.cell.get_column_letter(col)
    else:
        warnings.warn(
            "Invalid column. Using column 'A' instead", category=UserWarning
        )
        return "A"

get_row_start(ws)

Return the next available row index in ws, inserting separator rows when needed.

Parameters:

Name	Type	Description	Default
ws	Worksheet	The worksheet to inspect.	required

Returns:

Type	Description
int	1-based row number where the next content should be written.

Source code in sql2excel/excel_helper.py

def get_row_start(self, ws: Worksheet):
    """Return the next available row index in *ws*, inserting separator rows when needed.

    Parameters
    ----------
    ws : Worksheet
        The worksheet to inspect.

    Returns
    -------
    int
        1-based row number where the next content should be written.
    """
    if self.is_sheet_empty(ws):
        rowstart = 1
    else:
        # rowstart = len(list(ws.rows)) + 1
        rowstart = ws.max_row + self.config.SEPARATOR + 1
        for _ in range(self.config.SEPARATOR):
            ws.append([None])
    return rowstart

get_starting_position(ws, **kwargs)

Resolve the (row, column) start position for writing data.

Parameters:

Name	Type	Description	Default
ws	Worksheet	The target worksheet.	required
**kwargs	dict	Optional overrides: row_start : int, optional Explicit 1-based row number. Must be >= 1. column_start : str, int, or None, optional Column letter, 1-based integer, or None for column A.	{}

Returns:

Type	Description
tuple of (int, int)	(row_start, column_start) as 1-based indices.

Warns:

Type	Description
UserWarning	If row_start is not a positive integer.

Source code in sql2excel/excel_helper.py

def get_starting_position(self, ws, **kwargs):
    """Resolve the (row, column) start position for writing data.

    Parameters
    ----------
    ws : Worksheet
        The target worksheet.
    **kwargs : dict
        Optional overrides:

        row_start : int, optional
            Explicit 1-based row number. Must be >= 1.
        column_start : str, int, or None, optional
            Column letter, 1-based integer, or ``None`` for column A.

    Returns
    -------
    tuple of (int, int)
        *(row_start, column_start)* as 1-based indices.

    Warns
    -----
    UserWarning
        If *row_start* is not a positive integer.
    """
    # Extract and validate row_start
    row_start = kwargs.get("row_start")
    if row_start is not None:
        if not isinstance(row_start, int) or row_start <= 0:
            row_start = None
            warnings.warn(
                "Row start must be an integer >= 1.Choosing row number"
                "dynamically depending on the data already in the sheet"
            )

    row_start = row_start or self.get_row_start(ws)

    # column start
    column_letter = self.get_column_letter(col=kwargs.get("column_start"))
    row_start, column_start = xl.utils.cell.coordinate_to_tuple(
        column_letter + str(row_start)
    )

    return row_start, column_start

insert_rows_for_chart_height(height, ws, df=None, scale=None)

Add empty rows to account for chart or image height.

Parameters:

Name	Type	Description	Default
height	int or float	The chart/image height (in openpyxl units).	required
ws	Worksheet	The worksheet to append rows to.	required
df	DataFrame	If provided, its row count is subtracted from the total rows needed.	None
scale	float	Multiplier applied to height. Defaults to Config.CHART_HEIGHT_SCALE.	None

Source code in sql2excel/excel_helper.py

def insert_rows_for_chart_height(self, height, ws, df=None, scale=None):
    """Add empty rows to account for chart or image height.

    Parameters
    ----------
    height : int or float
        The chart/image height (in openpyxl units).
    ws : Worksheet
        The worksheet to append rows to.
    df : DataFrame, optional
        If provided, its row count is subtracted from the total rows
        needed.
    scale : float, optional
        Multiplier applied to *height*. Defaults to
        ``Config.CHART_HEIGHT_SCALE``.
    """
    scale = scale or self.config.CHART_HEIGHT_SCALE
    # Number of lines consumed by the chart
    n_rows = int(scale * height) + 1

    # Number of rows already occupied by dataframe
    n_rows_df = 0 if df is None else df.shape[0]

    # Number of rows to append: do not append if negative
    n_rows = n_rows - n_rows_df

    for _ in range(n_rows):
        ws.append([None])

is_sheet_empty(ws)

Check whether a worksheet contains any data.

Parameters:

Name	Type	Description	Default
ws	Worksheet	The worksheet to inspect.	required

Returns:

Type	Description
bool	True if the sheet has no rows.

Source code in sql2excel/excel_helper.py

def is_sheet_empty(self, ws: Worksheet):
    """Check whether a worksheet contains any data.

    Parameters
    ----------
    ws : Worksheet
        The worksheet to inspect.

    Returns
    -------
    bool
        ``True`` if the sheet has no rows.
    """
    # NOTE This will return True if the sheet is iterated upon even though it contains "empty" cells
    return len(list(ws.rows)) == 0

reference_column_exists(df)

Check whether the last column of df contains a single unique value.

Parameters:

Name	Type	Description	Default
df	DataFrame	The DataFrame to inspect.	required

Returns:

Type	Description
bool	True if the last column has exactly one unique value.

Source code in sql2excel/excel_helper.py

def reference_column_exists(self, df):
    """Check whether the last column of *df* contains a single unique value.

    Parameters
    ----------
    df : pandas.DataFrame
        The DataFrame to inspect.

    Returns
    -------
    bool
        ``True`` if the last column has exactly one unique value.
    """
    return df.iloc[:, -1].nunique() == 1

rotate_xticks(chart, rotation)

Rotate the x-axis tick labels.

Parameters:

Name	Type	Description	Default
chart	Chart	The chart to modify.	required
rotation	int or float	Rotation in degrees (will be multiplied by 60 000 internally for the openpyxl rot attribute).	required

Warns:

Type	Description
UserWarning	If the rotation cannot be applied.

Source code in sql2excel/excel_helper.py

def rotate_xticks(self, chart, rotation):
    """Rotate the x-axis tick labels.

    Parameters
    ----------
    chart : openpyxl.chart.Chart
        The chart to modify.
    rotation : int or float
        Rotation in degrees (will be multiplied by 60 000 internally
        for the openpyxl ``rot`` attribute).

    Warns
    -----
    UserWarning
        If the rotation cannot be applied.
    """

    rotation *= 60000

    try:
        chart.x_axis.txPr = RichText(
            bodyPr=RichTextProperties(
                anchor="ctr",
                anchorCtr="1",
                rot=rotation,
                spcFirstLastPara="1",
                vertOverflow="ellipsis",
                wrap="square",
            ),
            p=[
                Paragraph(
                    pPr=ParagraphProperties(defRPr=CharacterProperties()),
                    endParaRPr=CharacterProperties(),
                )
            ],
        )
    except Exception:
        warnings.warn(
            "Unable to set rotate xticks", category=UserWarning, stacklevel=1
        )

set_axis_limit(axis, limit)

Set the minimum and maximum values for an axis.

Parameters:

Name	Type	Description	Default
axis	Axis	The axis to configure.	required
limit	tuple of (int or float, int or float)	A (min, max) pair. If min >= max, a warning is issued and the limits are still applied.	required

Warns:

Type	Description
UserWarning	If min >= max.

Source code in sql2excel/excel_helper.py

def set_axis_limit(self, axis, limit):
    """Set the minimum and maximum values for an axis.

    Parameters
    ----------
    axis : openpyxl.chart.axis.Axis
        The axis to configure.
    limit : tuple of (int or float, int or float)
        A *(min, max)* pair. If *min >= max*, a warning is issued and
        the limits are still applied.

    Warns
    -----
    UserWarning
        If *min >= max*.
    """
    mini, maxi = limit
    if mini >= maxi:
        warnings.warn(
            f"Invalid axis limits: {limit}. Axis limits will be ignored",
            category=UserWarning,
            stacklevel=1,
        )
    axis.scaling.min = mini
    axis.scaling.max = maxi

set_chart_axis_label_font(chart_, axis, **kwargs)

Apply font styling to an axis title.

Parameters:

Name	Type	Description	Default
chart_	Chart	The chart whose axis title is styled.	required
axis	str	"x" or "y".	required
**kwargs	dict	Optional overrides: axis_font_name, axis_font_size, axis_font_color, axis_font_bold.	{}

Source code in sql2excel/excel_helper.py

def set_chart_axis_label_font(self, chart_, axis, **kwargs):
    """Apply font styling to an axis title.

    Parameters
    ----------
    chart_ : openpyxl.chart.Chart
        The chart whose axis title is styled.
    axis : str
        ``"x"`` or ``"y"``.
    **kwargs : dict
        Optional overrides: ``axis_font_name``, ``axis_font_size``,
        ``axis_font_color``, ``axis_font_bold``.
    """
    # Do not remove underscore from `chart_` as kwargs might contain a key 'chart'.
    # A query in SQL script can be annotated with 'chart' to export the result

    axis_font_name = kwargs.get("axis_font_name") or self.config.AXIS_FONT_NAME
    axis_font_size = kwargs.get("axis_font_size") or self.config.AXIS_FONT_SIZE
    axis_font_color = kwargs.get("axis_font_color") or self.config.AXIS_FONT_COLOR
    axis_font_bold = kwargs.get("axis_font_bold") or self.config.AXIS_FONT_BOLD

    font = DrawingFont(typeface=axis_font_name)
    axis_font_color = (
        colors.ColorChoice(prstClr=axis_font_color)
        if axis_font_color in self.config.VALID_COLORS
        else colors.ColorChoice(srgbClr=axis_font_color)
    )
    cp = xl.drawing.text.CharacterProperties(
        latin=font,
        sz=axis_font_size,
        solidFill=axis_font_color,
        b=axis_font_bold,
    )

    try:
        if axis.lower() == "x" and chart_.x_axis.title is not None:
            chart_.x_axis.title.tx.rich.p[0].r[0].rPr = cp
        elif axis.lower() == "y" and chart_.y_axis.title is not None:
            chart_.y_axis.title.tx.rich.p[0].r[0].rPr = cp
        else:
            pass
    except AttributeError:
        print(chart_.__class__)
        warnings.warn("Unable to style axis label", category=UserWarning)

set_chart_title_font(chart_, **kwargs)

Apply font styling to the chart title.

Parameters:

Name	Type	Description	Default
chart_	Chart	The chart whose title is styled. Named chart_ (with underscore) to avoid collision with the chart kwarg.	required
**kwargs	dict	Optional overrides: title_font_name, title_font_size, title_font_color, title_font_bold.	{}

Source code in sql2excel/excel_helper.py

def set_chart_title_font(self, chart_, **kwargs):
    """Apply font styling to the chart title.

    Parameters
    ----------
    chart_ : openpyxl.chart.Chart
        The chart whose title is styled.  Named *chart_* (with
        underscore) to avoid collision with the ``chart`` kwarg.
    **kwargs : dict
        Optional overrides: ``title_font_name``, ``title_font_size``,
        ``title_font_color``, ``title_font_bold``.
    """
    # Do not remove underscore from `chart_` as kwargs might contain a key 'chart'.
    # A query in SQL script can be annotated with 'chart' to export the result
    if chart_.title is None:
        return

    font_name = DrawingFont(typeface=self.config.CHART_TITLE_FONT_NAME)
    if kwargs.get("title_font_name"):
        font_name = DrawingFont(typeface=kwargs.get("title_font_name"))
    font_size = kwargs.get("title_font_size") or self.config.CHART_TITLE_FONT_SIZE
    color = kwargs.get("title_font_color") or self.config.CHART_TITLE_FONT_COLOR
    bold = kwargs.get("title_font_bold") or self.config.CHART_TITLE_FONT_BOLD

    color = (
        colors.ColorChoice(prstClr=color)
        if color in self.config.VALID_COLORS
        else colors.ColorChoice(srgbClr=color)
    )

    cp = xl.drawing.text.CharacterProperties(
        latin=font_name,
        sz=font_size,
        solidFill=color,
        b=bold,
    )

    try:
        if chart_.title:
            chart_.title.tx.rich.p[0].r[0].rPr = cp
    except (IndexError, AttributeError):
        warnings.warn(
            "Unable to set chart title", category=UserWarning, stacklevel=1
        )

set_df_title_font(cell, df_font_name=None, df_bold=None, df_font_size=None, df_font_color=None, **kwargs)

Apply DataFrame title font style to a cell.

Parameters:

Name	Type	Description	Default
cell	Cell	The target cell.	required
df_font_name	str	Font name. Defaults to Config.DF_TITLE_FONT_NAME.	None
df_bold	bool	Bold flag. Defaults to Config.DF_TITLE_BOLD.	None
df_font_size	int	Font size. Defaults to Config.DF_TITLE_FONT_SIZE.	None
df_font_color	str	Font colour hex. Defaults to Config.DF_TITLE_FONT_COLOR.	None

Source code in sql2excel/excel_helper.py

def set_df_title_font(
    self,
    cell,
    df_font_name=None,
    df_bold=None,
    df_font_size=None,
    df_font_color=None,
    **kwargs,
):
    """Apply DataFrame title font style to a cell.

    Parameters
    ----------
    cell : openpyxl.cell.Cell
        The target cell.
    df_font_name : str, optional
        Font name. Defaults to ``Config.DF_TITLE_FONT_NAME``.
    df_bold : bool, optional
        Bold flag. Defaults to ``Config.DF_TITLE_BOLD``.
    df_font_size : int, optional
        Font size. Defaults to ``Config.DF_TITLE_FONT_SIZE``.
    df_font_color : str, optional
        Font colour hex. Defaults to ``Config.DF_TITLE_FONT_COLOR``.
    """
    # Use provided arguments or fall back to configuration defaults
    df_font_name = df_font_name or self.config.DF_TITLE_FONT_NAME
    df_bold = df_bold or self.config.DF_TITLE_BOLD
    df_font_size = df_font_size or self.config.DF_TITLE_FONT_SIZE
    df_font_color = df_font_color or self.config.DF_TITLE_FONT_COLOR

    # Apply the font style to the cell
    cell.font = Font(
        name=df_font_name,
        bold=df_bold,
        size=df_font_size,
        color=df_font_color,
    )

set_line_graphical_properties(series, width=None, style=None, color=None, smooth=None, nofill=False)

Set graphic properties for a chart series line.

Parameters:

Name	Type	Description	Default
series	Series	The openpyxl chart series to modify.	required
width	float	Line width in points (converted to EMU internally).	None
style	str	Dash style name, e.g. "solid", "sysDash".	None
color	str	Line colour as an sRGB hex string.	None
smooth	bool	Whether to smooth the line. Ignored for radar charts.	None
nofill	bool	If True, set the line to no-fill and return immediately.	False

Source code in sql2excel/excel_helper.py

def set_line_graphical_properties(
    self,
    series: xl.chart.series.Series,
    width: float = None,
    style: str = None,
    color: str = None,
    smooth: bool = None,
    nofill: bool = False,
) -> None:
    """Set graphic properties for a chart series line.

    Parameters
    ----------
    series : Series
        The openpyxl chart series to modify.
    width : float, optional
        Line width in points (converted to EMU internally).
    style : str, optional
        Dash style name, e.g. ``"solid"``, ``"sysDash"``.
    color : str, optional
        Line colour as an sRGB hex string.
    smooth : bool, optional
        Whether to smooth the line. Ignored for radar charts.
    nofill : bool, optional
        If ``True``, set the line to no-fill and return immediately.
    """

    if nofill:
        series.graphicalProperties.line.noFill = nofill
        return

    if style:
        series.graphicalProperties.line.dashStyle = style

    if width:
        # 1 point = 12700 EMU
        series.graphicalProperties.line.width = 12700 * width

    if color:
        color = colors.ColorChoice(srgbClr=color)
        series.graphicalProperties.solidFill = color
        series.graphicalProperties.line.solidFill = color

    if smooth is not None:
        series.smooth = smooth

set_marker_graphical_properties(series, symbol=None, size=None, color=None)

Set marker graphic properties for a chart series.

Parameters:

Name	Type	Description	Default
series	Series	The openpyxl chart series to modify.	required
symbol	str	Marker shape, e.g. "circle", "diamond".	None
size	int	Marker size. Defaults to Config.MARKER_SIZE.	None
color	str	Marker fill colour as an sRGB hex string.	None

Source code in sql2excel/excel_helper.py

def set_marker_graphical_properties(
    self,
    series: xl.chart.series.Series,
    symbol: str = None,
    size: int = None,
    color: str = None,
) -> None:
    """Set marker graphic properties for a chart series.

    Parameters
    ----------
    series : Series
        The openpyxl chart series to modify.
    symbol : str, optional
        Marker shape, e.g. ``"circle"``, ``"diamond"``.
    size : int, optional
        Marker size. Defaults to ``Config.MARKER_SIZE``.
    color : str, optional
        Marker fill colour as an sRGB hex string.
    """

    if symbol:
        series.marker.symbol = symbol
        series.marker.size = size or self.config.MARKER_SIZE

    if color:
        color = colors.ColorChoice(srgbClr=color)
        series.marker.graphicalProperties.solidFill = color
        series.marker.graphicalProperties.line.solidFill = color

set_section_heading_font(cell, sh_font_name=None, sh_bold=None, sh_font_size=None, sh_font_color=None, **kwargs)

Apply section heading font style to a cell.

Parameters:

Name	Type	Description	Default
cell	Cell	The target cell.	required
sh_font_name	str	Font name. Defaults to Config.SECTION_HEADING_FONT_NAME.	None
sh_bold	bool	Bold flag. Defaults to Config.SECTION_HEADING_BOLD.	None
sh_font_size	int	Font size. Defaults to Config.SECTION_HEADING_FONT_SIZE.	None
sh_font_color	str	Font colour hex. Defaults to Config.SECTION_HEADING_FONT_COLOR.	None

Source code in sql2excel/excel_helper.py

def set_section_heading_font(
    self,
    cell,
    sh_font_name=None,
    sh_bold=None,
    sh_font_size=None,
    sh_font_color=None,
    **kwargs,
):
    """Apply section heading font style to a cell.

    Parameters
    ----------
    cell : openpyxl.cell.Cell
        The target cell.
    sh_font_name : str, optional
        Font name. Defaults to ``Config.SECTION_HEADING_FONT_NAME``.
    sh_bold : bool, optional
        Bold flag. Defaults to ``Config.SECTION_HEADING_BOLD``.
    sh_font_size : int, optional
        Font size. Defaults to ``Config.SECTION_HEADING_FONT_SIZE``.
    sh_font_color : str, optional
        Font colour hex. Defaults to ``Config.SECTION_HEADING_FONT_COLOR``.
    """
    sh_font_name = sh_font_name or self.config.SECTION_HEADING_FONT_NAME
    sh_bold = sh_bold or self.config.SECTION_HEADING_BOLD or sh_bold
    sh_font_size = sh_font_size or self.config.SECTION_HEADING_FONT_SIZE
    sh_font_color = sh_font_color or self.config.SECTION_HEADING_FONT_COLOR

    cell.font = Font(
        name=sh_font_name,
        bold=sh_bold,
        size=sh_font_size,
        color=sh_font_color,
    )

ImageChart(config=None, excel_helper=None)

Bases: Chart

Chart class for embedding images or matplotlib figures into Excel.

Parameters:

Name	Type	Description	Default
config	Config	Configuration object containing chart and general Excel settings.	None
excel_helper	ExcelHelper	Helper object for Excel-specific operations.	None

See Also

Chart : Base class for all chart types.

Initialize an image chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration overrides. Defaults to Config().	None
excel_helper	ExcelHelper	Helper instance. Created from config when not provided.	None

Source code in sql2excel/chart.py

def __init__(self, config=None, excel_helper=None):
    """Initialize an image chart.

    Parameters
    ----------
    config : Config, optional
        Configuration overrides. Defaults to ``Config()``.
    excel_helper : ExcelHelper, optional
        Helper instance. Created from *config* when not provided.
    """
    super().__init__(config, excel_helper)

add_image(image_input, ws, df=None, **kwargs)

Add an image or matplotlib figure to the worksheet.

Parameters:

Name	Type	Description	Default
image_input	str or Figure	A file path (str) or a matplotlib Figure to embed.	required
ws	Worksheet	The target worksheet.	required
df	DataFrame	If provided, the DataFrame is written before the image is placed.	None
**kwargs	dict	Optional overrides: section_heading, width, height, chart_position, row_start, column_start.	{}

Source code in sql2excel/chart.py

def add_image(
    self,
    image_input: str | mpl.figure.Figure,
    ws,
    df=None,
    **kwargs,
):
    """Add an image or matplotlib figure to the worksheet.

    Parameters
    ----------
    image_input : str or matplotlib.figure.Figure
        A file path (str) or a matplotlib ``Figure`` to embed.
    ws : Worksheet
        The target worksheet.
    df : pandas.DataFrame, optional
        If provided, the DataFrame is written before the image is placed.
    **kwargs : dict
        Optional overrides: ``section_heading``, ``width``, ``height``,
        ``chart_position``, ``row_start``, ``column_start``.
    """

    if isinstance(image_input, str):
        img = Image(image_input)
    else:
        img_bytes = io.BytesIO()

        if isinstance(image_input, mpl.figure.Figure):
            image_input.savefig(img_bytes, format="png", bbox_inches="tight")
            plt.close()
        else:
            warnings.warn(
                "Unsupported image input type. No image will be added",
                category=UserWarning,
                stacklevel=1,
            )
            return

        img_bytes.seek(0)  # Rewind the BytesIO object to the beginning
        img = Image(img_bytes)

    if df is None:
        row_start, column_start = self.excel_helper.get_starting_position(
            ws, **kwargs
        )

        section_heading = kwargs.get("section_heading")
        if section_heading:
            ws.cell(row=row_start, column=column_start, value=section_heading)

            self.excel_helper.set_section_heading_font(
                ws.cell(row=row_start, column=column_start)
            )

            # Update
            row_start += 1
    else:
        self.write_dataframe(df, ws, **kwargs)

    # Width and height in pixels
    height = kwargs.get("height") or self.config.IMAGE_HEIGHT or img.height
    img.height = height
    img.width = kwargs.get("width") or self.config.IMAGE_WIDTH or img.width

    chart_position = kwargs.get("chart_position") or self.config.CHART_POSITION

    if chart_position == "right":
        anchor_x = self.min_row if df is not None else row_start
        anchor_y = (
            self.max_col + self.config.DATA_CHART_SEPARATOR + 1
            if df is not None
            else column_start
        )
        ref = xl.utils.cell.get_column_letter(anchor_y) + str(anchor_x)
        ws.add_image(img, ref)
        # Add empty rows after writing data and inserting the figure
        self.excel_helper.insert_rows_for_chart_height(
            height, ws, df=df, scale=self.config.IMAGE_HEIGHT_UNIT
        )
    elif chart_position == "bottom":
        anchor_x = (
            self.max_row + self.config.DATA_CHART_SEPARATOR + 1
            if df is not None
            else row_start
        )
        anchor_y = self.min_col - 1 if df is not None else column_start
        ref = xl.utils.cell.get_column_letter(anchor_y) + str(anchor_x)
        ws.add_image(img, ref)
        # Add empty rows after writing data and inserting the figure
        self.excel_helper.insert_rows_for_chart_height(
            height, ws, df=None, scale=self.config.IMAGE_HEIGHT_UNIT
        )
    else:
        warnings.warn(
            "Unknown chart position. Chart will not be added to the sheet",
            category=UserWarning,
            stacklevel=1,
        )

LineBasedChart(config=None, excel_helper=None)

Bases: Chart

Base class for line-based charts (e.g., line and radar).

Parameters:

Name	Type	Description	Default
config	Config	Configuration object containing chart and general Excel settings.	None
excel_helper	ExcelHelper	Helper object for Excel-specific operations.	None

See Also

Chart : Base class for all chart types.

Notes

Reference series are always formatted according to configuration.

Initialize a line-based chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration overrides. Defaults to Config().	None
excel_helper	ExcelHelper	Helper instance. Created from config when not provided.	None

Source code in sql2excel/chart.py

def __init__(self, config=None, excel_helper=None):
    """Initialize a line-based chart.

    Parameters
    ----------
    config : Config, optional
        Configuration overrides. Defaults to ``Config()``.
    excel_helper : ExcelHelper, optional
        Helper instance. Created from *config* when not provided.
    """
    super().__init__(config, excel_helper)

LineChart(config=None, excel_helper=None)

Bases: LineBasedChart

Line chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration object containing chart and general Excel settings.	None
excel_helper	ExcelHelper	Helper object for Excel-specific operations.	None

See Also

Chart : Base class for all chart types.

Initialize a line chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration overrides. Defaults to Config().	None
excel_helper	ExcelHelper	Helper instance. Created from config when not provided.	None

Source code in sql2excel/chart.py

def __init__(self, config=None, excel_helper=None):
    """Initialize a line chart.

    Parameters
    ----------
    config : Config, optional
        Configuration overrides. Defaults to ``Config()``.
    excel_helper : ExcelHelper, optional
        Helper instance. Created from *config* when not provided.
    """
    super().__init__(config, excel_helper)

plot(df, ws, **kwargs)

Plot a line chart from df into ws.

Parameters:

Name	Type	Description	Default
df	DataFrame	Data to plot; first column is the category axis.	required
ws	Worksheet	The target worksheet.	required
**kwargs	dict	Chart customization options (see Chart._plot).	{}

Source code in sql2excel/chart.py

def plot(self, df, ws, **kwargs):
    """Plot a line chart from *df* into *ws*.

    Parameters
    ----------
    df : pandas.DataFrame
        Data to plot; first column is the category axis.
    ws : Worksheet
        The target worksheet.
    **kwargs : dict
        Chart customization options (see ``Chart._plot``).
    """
    self.write_dataframe(df, ws, **kwargs)

    self.chart = xl.chart.LineChart()

    self._add_data(df, ws, **kwargs)

    super()._plot(df, ws, **kwargs)

PieChart(config=None, excel_helper=None)

Bases: Chart

Pie chart with data-label customization.

Parameters:

Name	Type	Description	Default
config	Config	Configuration object containing chart and general Excel settings.	None
excel_helper	ExcelHelper	Helper object for Excel-specific operations.	None

See Also

Chart : Base class for all chart types.

Initialize a pie chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration overrides. Defaults to Config().	None
excel_helper	ExcelHelper	Helper instance. Created from config when not provided.	None

Source code in sql2excel/chart.py

def __init__(self, config=None, excel_helper=None):
    """Initialize a pie chart.

    Parameters
    ----------
    config : Config, optional
        Configuration overrides. Defaults to ``Config()``.
    excel_helper : ExcelHelper, optional
        Helper instance. Created from *config* when not provided.
    """
    super().__init__(config, excel_helper)

QueryConfig(sql=None, sql_params=None, from_sql_script=False, **xl_params)

Initialize the object with SQL query, parameters, and additional Excel parameters.

Parameters:

Name	Type	Description	Default
sql	str	The SQL query to execute.	None
sql_params	dict or Sequence	Parameters to be passed to the SQL query.	None
from_sql_script	bool	Whether this query config was read from a SQL script. Default is False.	False
**xl_params	dict	Additional parameters for configuring the Excel chart. The keys can include: chart : str The chart class. E.g. line for LineChart, pie for PieChart, etc. section_heading : str The heading placed directly above the data and chart. title : str The title of the chart. ylabel : str The label for the y-axis. xlabel : str The label for the x-axis. rotation : int The rotation angle for the x-axis labels. xlim : tuple The limits for the x-axis (min, max). ylim : tuple The limits for the y-axis (min, max). y_orientation : str The orientation of the y-axis ('minMax', 'maxMin'). width : int The width of the chart. height : int The height of the chart. no_legend : bool Whether to hide the legend. legend_position : str The position of the legend. chart_position : str The position of the chart ('right', 'bottom').	{}

Source code in sql2excel/sqlexec.py

def __init__(
    self, sql=None, sql_params=None, from_sql_script=False, **xl_params
) -> None:
    """
    Initialize the object with SQL query, parameters, and additional Excel parameters.

    Parameters
    ----------
    sql : str, optional
        The SQL query to execute.
    sql_params : dict or Sequence, optional
        Parameters to be passed to the SQL query.
    from_sql_script : bool, optional
        Whether this query config was read from a SQL script.
        Default is ``False``.
    **xl_params : dict
        Additional parameters for configuring the Excel chart. The keys can include:

        - chart : str
            The chart class. E.g. line for LineChart, pie for PieChart, etc.
        - section_heading : str
            The heading placed directly above the data and chart.
        - title : str
            The title of the chart.
        - ylabel : str
            The label for the y-axis.
        - xlabel : str
            The label for the x-axis.
        - rotation : int
            The rotation angle for the x-axis labels.
        - xlim : tuple
            The limits for the x-axis (min, max).
        - ylim : tuple
            The limits for the y-axis (min, max).
        - y_orientation : str
            The orientation of the y-axis ('minMax', 'maxMin').
        - width : int
            The width of the chart.
        - height : int
            The height of the chart.
        - no_legend : bool
            Whether to hide the legend.
        - legend_position : str
            The position of the legend.
        - chart_position : str
            The position of the chart ('right', 'bottom').
    """
    self.sql = sql
    self.sql_params = sql_params
    self.from_sql_script = from_sql_script
    self.xl_params = xl_params

RadarChart(config=None, excel_helper=None)

Bases: LineBasedChart

Radar (spider) chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration object containing chart and general Excel settings.	None
excel_helper	ExcelHelper	Helper object for Excel-specific operations.	None

See Also

Chart : Base class for all chart types.

Initialize a radar chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration overrides. Defaults to Config().	None
excel_helper	ExcelHelper	Helper instance. Created from config when not provided.	None

Source code in sql2excel/chart.py

def __init__(self, config=None, excel_helper=None):
    """Initialize a radar chart.

    Parameters
    ----------
    config : Config, optional
        Configuration overrides. Defaults to ``Config()``.
    excel_helper : ExcelHelper, optional
        Helper instance. Created from *config* when not provided.
    """
    super().__init__(config, excel_helper)

plot(df, ws, **kwargs)

Plot a radar chart from df into ws.

Parameters:

Name	Type	Description	Default
df	DataFrame	Data to plot; first column is the category axis.	required
ws	Worksheet	The target worksheet.	required
**kwargs	dict	Chart customization options (see Chart._plot).	{}

Source code in sql2excel/chart.py

def plot(self, df, ws, **kwargs):
    """Plot a radar chart from *df* into *ws*.

    Parameters
    ----------
    df : pandas.DataFrame
        Data to plot; first column is the category axis.
    ws : Worksheet
        The target worksheet.
    **kwargs : dict
        Chart customization options (see ``Chart._plot``).
    """
    self.write_dataframe(df, ws, **kwargs)

    self.chart = xl.chart.RadarChart()

    # 'marker', 'filled', 'standard'
    self.chart.type = kwargs.get("chart_type", "standard")

    self._add_data(df, ws, **kwargs)

    radar_unit = kwargs.get("radar_unit")
    radar_unit_steps = (
        kwargs.get("radar_unit_steps") or self.config.RADAR_UNIT_STEPS
    )
    if radar_unit_steps:
        # TODO Fix the code below to select data columns when specified with data_column_start or data_columns
        radar_unit_steps = (
            np.max(df.iloc[:, 1:].values) - np.min(df.iloc[:, 1:].values)
        ) / radar_unit_steps

    unit = radar_unit or radar_unit_steps

    if unit:
        self.chart.y_axis.majorUnit = round(unit, 1) if unit < 1 else round(unit)

    mini = 0
    maxi = float(np.max(df.iloc[:, 1:].values))
    self.chart.y_axis.scaling = xl.chart.axis.Scaling(min=mini, max=maxi + 0.5)

    kwargs["line_width"] = kwargs.get("line_width") or self.config.RADAR_REF_WIDTH

    super()._plot(df, ws, **kwargs)

Report(conn=None, session=None, engine=None, db_url=None, config=None, silent=False)

Initialize the Report orchestrator.

Parameters:

Name	Type	Description	Default
conn	Connection	An existing database connection.	None
session	Session	An existing SQLAlchemy session.	None
engine	Engine	An existing SQLAlchemy engine.	None
db_url	str	A SQLAlchemy database URL used to create a new engine and connection.	None
config	Config	Configuration overrides. Defaults to Config().	None
silent	bool	If True, query errors produce warnings instead of raising.	False

Source code in sql2excel/report.py

def __init__(
    self,
    conn=None,
    session=None,
    engine=None,
    db_url=None,
    config=None,
    silent=False,
) -> None:
    """Initialize the Report orchestrator.

    Parameters
    ----------
    conn : sqlalchemy.engine.Connection, optional
        An existing database connection.
    session : sqlalchemy.orm.Session, optional
        An existing SQLAlchemy session.
    engine : sqlalchemy.engine.Engine, optional
        An existing SQLAlchemy engine.
    db_url : str, optional
        A SQLAlchemy database URL used to create a new engine and connection.
    config : Config, optional
        Configuration overrides. Defaults to ``Config()``.
    silent : bool, optional
        If ``True``, query errors produce warnings instead of raising.
    """
    self.config = config or Config()
    self.excel_helper = ExcelHelper(config=self.config)
    self.executor = SQLExecutor(
        conn=conn,
        session=session,
        engine=engine,
        db_url=db_url,
        silent=silent,
    )

close()

Close all open database resources held by the executor.

Source code in sql2excel/report.py

def close(self):
    """Close all open database resources held by the executor."""
    self.executor.close()

generate(query_config, **kwarg)

Execute queries, render results and charts, and save to an Excel file.

Parameters:

Name	Type	Description	Default
query_config	QueryConfig or Sequence[QueryConfig]	One or more QueryConfig objects to process.	required
**kwarg	dict	Optional keyword arguments: file_name : str, optional Output file name. Default is "result.xlsx". sheetname : str, optional Name of the worksheet (only used for the first sheet created).	{}

Source code in sql2excel/report.py

def generate(self, query_config: QueryConfig | Sequence[QueryConfig], **kwarg):
    """Execute queries, render results and charts, and save to an Excel file.

    Parameters
    ----------
    query_config : QueryConfig or Sequence[QueryConfig]
        One or more ``QueryConfig`` objects to process.
    **kwarg : dict
        Optional keyword arguments:

        file_name : str, optional
            Output file name. Default is ``"result.xlsx"``.
        sheetname : str, optional
            Name of the worksheet (only used for the first sheet created).
    """

    fname = kwarg.get("file_name", "result.xlsx")
    wb = xl.Workbook()
    ws = wb.active
    sheetname = kwarg.get("sheetname")
    if sheetname:
        ws.title = sheetname

    if isinstance(query_config, QueryConfig):
        queries_config = [query_config]
    else:
        queries_config = query_config

    try:

        # results = self.executor.executeall(queries_config)
        query_results = []
        for qc in queries_config:
            if (
                qc.from_sql_script
                and "chart" not in qc.xl_params.keys()
                and "exec" not in qc.xl_params.keys()
            ):
                continue

            df = self.executor.execute(qc)

            if df is None or "exec" in qc.xl_params.keys():
                continue

            index = qc.xl_params.get("index")
            if index:
                columns = qc.xl_params.get("columns")
                values = qc.xl_params.get("values")
                df = pd.pivot(
                    df, index=index, columns=columns, values=values
                ).reset_index(drop=False)

            query_results.append((qc, df))

        for idx, (qc, df) in enumerate(query_results):

            sheetname = qc.xl_params.get("sheetname") or qc.xl_params.get(
                "sheet name"
            )
            sheetname = sheetname.strip() if sheetname else None
            if sheetname:
                if idx == 0:
                    del wb[ws.title]

                if sheetname not in wb.sheetnames:
                    ws = wb.create_sheet(sheetname)
                else:
                    ws = wb[sheetname]

            chart = CHART_MAP.get(qc.xl_params.get("chart"))
            if chart:
                chart = chart(config=self.config, excel_helper=self.excel_helper)

                if chart.__class__.__name__ == "Chart":
                    chart.write_dataframe(df, ws, **qc.xl_params)
                else:
                    chart.plot(df, ws, **qc.xl_params)
    finally:
        self.executor.close()

        wb.save(fname)

SQLExecutor(conn=None, session=None, engine=None, db_url=None, silent=False)

Initialize the SQLExecutor with a database connection.

Exactly one of conn, session, engine, or db_url must be provided.

Parameters:

Name	Type	Description	Default
conn	Connection	An existing database connection.	None
session	Session	An existing SQLAlchemy session.	None
engine	Engine	An existing SQLAlchemy engine; a connection will be created from it.	None
db_url	str	A SQLAlchemy database URL used to create a new engine and connection.	None
silent	bool	If True, query execution errors are caught and only produce warnings instead of raising.	False

Raises:

Type	Description
ValueError	If none of conn, session, engine, or db_url is provided.

Source code in sql2excel/sqlexec.py

def __init__(self, conn=None, session=None, engine=None, db_url=None, silent=False):
    """Initialize the SQLExecutor with a database connection.

    Exactly one of *conn*, *session*, *engine*, or *db_url* must be
    provided.

    Parameters
    ----------
    conn : sqlalchemy.engine.Connection, optional
        An existing database connection.
    session : sqlalchemy.orm.Session, optional
        An existing SQLAlchemy session.
    engine : sqlalchemy.engine.Engine, optional
        An existing SQLAlchemy engine; a connection will be created from it.
    db_url : str, optional
        A SQLAlchemy database URL used to create a new engine and connection.
    silent : bool, optional
        If ``True``, query execution errors are caught and only produce
        warnings instead of raising.

    Raises
    ------
    ValueError
        If none of *conn*, *session*, *engine*, or *db_url* is provided.
    """
    self.conn = conn
    self.session = session
    self.engine = engine
    self.silent = silent
    self.closed = None

    if not any([conn, session, engine, db_url]):
        raise ValueError("Cannot establish a connection to the database")

    if db_url:
        self.engine = create_engine(db_url)
        self.conn = self.engine.connect()
    elif engine:
        self.conn = self.engine.connect()
    elif conn:
        pass
    elif session:
        pass

    self.closed = False

close()

Close all open database resources.

Disposes of the connection, session, and engine (whichever are set) and marks the executor as closed.

Source code in sql2excel/sqlexec.py

def close(self):
    """Close all open database resources.

    Disposes of the connection, session, and engine (whichever are set)
    and marks the executor as closed.
    """
    if self.conn:
        self.conn.close()
    if self.session:
        self.session.close()
    if self.engine:
        self.engine.dispose()
    self.closed = True

execute(query_config)

Execute the SQL query defined in the QueryConfig object and return a DataFrame.

Parameters:

Name	Type	Description	Default
query_config	QueryConfig	An instance of QueryConfig containing the SQL query and parameters.	required

Returns:

Type	Description
DataFrame or None	A DataFrame with the query results, or None if the statement does not produce rows.

Raises:

Type	Description
ValueError	If query_config.sql is None.
Exception	Re-raises any database exception when silent is False.

Source code in sql2excel/sqlexec.py

def execute(self, query_config):
    """Execute the SQL query defined in the QueryConfig object and return a DataFrame.

    Parameters
    ----------
    query_config : QueryConfig
        An instance of QueryConfig containing the SQL query and parameters.

    Returns
    -------
    pandas.DataFrame or None
        A DataFrame with the query results, or ``None`` if the statement
        does not produce rows.

    Raises
    ------
    ValueError
        If ``query_config.sql`` is ``None``.
    Exception
        Re-raises any database exception when *silent* is ``False``.
    """
    # Validate query
    if query_config.sql is None:
        raise ValueError("No SQL query is found in QueryConfig")

    sql = query_config.sql
    params = query_config.sql_params
    result = None

    try:
        # NOTE sqlalchemy 2.0 does not support positional parameters
        # https://github.com/sqlalchemy/sqlalchemy/issues/5178
        # Positional parameters are easier to work with even though named
        # parameters are more verbose. The issue above suggests using
        # `exec_driver_sql`.  However, passing parameters this way is not
        # DB-agnostic. An alternative way of handling this is to use bind.
        #
        # See Also
        # --------
        # _bind_positional_parameters

        if isinstance(params, Sequence):
            sql, params = _bind_positional_parameters(sql, params)

        else:
            sql = text(sql)

        if self.conn:
            result = self.conn.execute(sql, params)
        elif self.session:
            result = self.session.execute(sql, params)
        else:
            pass

    except Exception as e:
        if self.silent:
            warnings.warn(
                f"Unable to execute the query due to the exception below: {sql}"
            )
            traceback.print_exc()
        else:
            raise e

    try:
        columns = result.keys()
        data = result.fetchall()
        df = pd.DataFrame(data, columns=columns)
        return df
    # Raises sqlalchemy.exc.ResourceClosedError if sql stmt does not produce rows
    except Exception:
        return None

executeall(query_configs)

Execute a sequence of QueryConfig objects and collect the results.

Parameters:

Name	Type	Description	Default
query_configs	Sequence[QueryConfig]	A sequence of QueryConfig objects to execute.	required

Returns:

Type	Description
list of pandas.DataFrame or None	One result per query, in the same order.

Source code in sql2excel/sqlexec.py

def executeall(self, query_configs):
    """Execute a sequence of QueryConfig objects and collect the results.

    Parameters
    ----------
    query_configs : Sequence[QueryConfig]
        A sequence of QueryConfig objects to execute.

    Returns
    -------
    list of pandas.DataFrame or None
        One result per query, in the same order.
    """
    results = []
    for query in query_configs:
        results.append(self.execute(query))
    return results

ScatterChart(config=None, excel_helper=None)

Bases: Chart

Scatter plot with flexible data selection.

Parameters:

Name	Type	Description	Default
config	Config	Configuration object containing chart and general Excel settings.	None
excel_helper	ExcelHelper	Helper object for Excel-specific operations.	None

See Also

Chart : Base class for all chart types.

Initialize a scatter chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration overrides. Defaults to Config().	None
excel_helper	ExcelHelper	Helper instance. Created from config when not provided.	None

Source code in sql2excel/chart.py

def __init__(self, config=None, excel_helper=None):
    """Initialize a scatter chart.

    Parameters
    ----------
    config : Config, optional
        Configuration overrides. Defaults to ``Config()``.
    excel_helper : ExcelHelper, optional
        Helper instance. Created from *config* when not provided.
    """
    super().__init__(config, excel_helper)

plot(df, ws, **kwargs)

Plot a scatter chart from df into ws.

Parameters:

Name	Type	Description	Default
df	DataFrame	Data to plot; first column is the x-axis.	required
ws	Worksheet	The target worksheet.	required
**kwargs	dict	Chart customization options (see Chart._plot).	{}

Source code in sql2excel/chart.py

def plot(self, df, ws, **kwargs):
    """Plot a scatter chart from *df* into *ws*.

    Parameters
    ----------
    df : pandas.DataFrame
        Data to plot; first column is the x-axis.
    ws : Worksheet
        The target worksheet.
    **kwargs : dict
        Chart customization options (see ``Chart._plot``).
    """
    self.write_dataframe(df, ws, **kwargs)

    self.chart = xl.chart.ScatterChart()

    self._add_data(df, ws, **kwargs)

    chart_style = kwargs.get("chart_style", 13)
    self.chart.style = chart_style

    nofill = kwargs.get("nofill")
    nofill = True if nofill is None else nofill
    kwargs["nofill"] = nofill

    super()._plot(df, ws, **kwargs)

    for idx, series in enumerate(self.chart.series):
        # Recycle colors if required
        marker_color = self.config.PRIMARY_COLORS[
            idx % len(self.config.PRIMARY_COLORS)
        ]

        marker_symbols = kwargs.get("marker_symbols")
        marker_symbol = (
            marker_symbols[idx % len(marker_symbols)]
            if marker_symbols
            else self.config.MARKER_SYMOBLS[idx % len(self.config.MARKER_SYMOBLS)]
        )

        marker_size = kwargs.get("marker_size")
        self.excel_helper.set_marker_graphical_properties(
            series,
            symbol=marker_symbol,
            size=marker_size,
            color=marker_color,
        )

        self.excel_helper.set_line_graphical_properties(series, nofill=nofill)

SingleAxisBarLineChart(config=None, excel_helper=None)

Bases: Chart

Combined bar and line chart on a single y-axis.

Parameters:

Name	Type	Description	Default
config	Config	Configuration object containing chart and general Excel settings.	None
excel_helper	ExcelHelper	Helper object for Excel-specific operations.	None

See Also

Chart : Base class for all chart types.

Initialize a single-axis bar-line chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration overrides. Defaults to Config().	None
excel_helper	ExcelHelper	Helper instance. Created from config when not provided.	None

Source code in sql2excel/chart.py

def __init__(self, config=None, excel_helper=None):
    """Initialize a single-axis bar-line chart.

    Parameters
    ----------
    config : Config, optional
        Configuration overrides. Defaults to ``Config()``.
    excel_helper : ExcelHelper, optional
        Helper instance. Created from *config* when not provided.
    """
    super().__init__(config, excel_helper)
    self.chart = None
    self.chart1 = xl.chart.BarChart()
    self.chart2 = xl.chart.LineChart()

plot(df, ws, **kwargs)

Plot a single-axis bar-line chart from df into ws.

Parameters:

Name	Type	Description	Default
df	DataFrame	Data to plot; first column is the category axis.	required
ws	Worksheet	The target worksheet.	required
**kwargs	dict	Chart customization options including bar_columns and line_columns (see Chart._plot).	{}

Source code in sql2excel/chart.py

def plot(self, df, ws, **kwargs):
    """Plot a single-axis bar-line chart from *df* into *ws*.

    Parameters
    ----------
    df : pandas.DataFrame
        Data to plot; first column is the category axis.
    ws : Worksheet
        The target worksheet.
    **kwargs : dict
        Chart customization options including ``bar_columns`` and
        ``line_columns`` (see ``Chart._plot``).
    """
    self.write_dataframe(df, ws, **kwargs)

    # Identifying bar and line columns
    bar_cols = kwargs.get("bar_columns")
    line_cols = kwargs.get("line_columns")

    # Default logic: first data column is bar, the rest are lines
    if bar_cols is None and line_cols is None:
        bar_cols = [2]
        line_cols = list(range(3, len(df.columns) + 1))
    elif bar_cols is None:
        all_data_cols = set(range(2, len(df.columns) + 1))
        bar_cols = sorted(list(all_data_cols - set(line_cols or [])))
    elif line_cols is None:
        all_data_cols = set(range(2, len(df.columns) + 1))
        line_cols = sorted(list(all_data_cols - set(bar_cols or [])))

    # Cache settings for final plot
    chart_position = kwargs.get("chart_position")

    # Setup Bar Chart
    self.chart = self.chart1
    self.chart1.type = "col"
    self.chart1.style = kwargs.get("bar_chart_style", 10)
    self.chart1.shape = kwargs.get("chart_shape", 4)

    bar_kwargs = kwargs.copy()
    bar_kwargs["data_columns"] = bar_cols
    bar_kwargs["set_categories"] = True
    bar_kwargs["chart_position"] = "Unknown"
    bar_kwargs["suppress_chart_position_warning"] = True

    self._add_data(df, ws, **bar_kwargs)

    # Apply bar specific styling here, if any (e.g., vary_color)
    vary_color = kwargs.get("vary_color") or self.config.BAR_CHART_VARYING_COLOR
    if vary_color and len(self.chart1.series) == 1:
        self.excel_helper.fill_data_point(self.chart1.series[0], len(df))
    else:
        # Apply colors for multiple bar series if custom_colors is enabled
        if self.config.CUSTOM_COLORS or kwargs.get("custom_colors"):
            for idx, series in enumerate(self.chart1.series):
                color = self.config.PRIMARY_COLORS[
                    idx % len(self.config.PRIMARY_COLORS)
                ]
                border_line_color = kwargs.get("border_line_color")
                self.excel_helper.fill(series, color, border_line_color)

    # Setup Line Chart
    self.chart = self.chart2
    self.chart2.style = kwargs.get("line_chart_style", 10)

    line_kwargs = kwargs.copy()
    line_kwargs["data_columns"] = line_cols
    line_kwargs["set_categories"] = False
    line_kwargs["chart_position"] = "Unknown"
    line_kwargs["suppress_chart_position_warning"] = True
    self._add_data(df, ws, **line_kwargs)

    # Combine
    self.chart1 += self.chart2
    self.chart = self.chart1
    self.chart1.y_axis.majorGridlines = None

    # Apply line specific styling here
    line_width = kwargs.get("line_width", self.config.LINE_WIDTH)
    line_style = kwargs.get("line_style", self.config.LINE_STYLE)
    smooth = kwargs.get("smooth", self.config.LINE_SMOOTH)
    marker_symbol = kwargs.get("marker_symbol", self.config.MARKER_SYMOBLS[0])
    marker_size = kwargs.get("marker_size", self.config.MARKER_SIZE)
    nofill = kwargs.get("nofill")

    for i, series in enumerate(self.chart2.series):
        series_color = None
        if self.config.CUSTOM_COLORS or kwargs.get("custom_colors"):
            series_color = self.config.PRIMARY_COLORS[
                (len(bar_cols) + i) % len(self.config.PRIMARY_COLORS)
            ]
        elif kwargs.get("line_color"):
            series_color = kwargs.get("line_color")
        else:
            # if i == 0:
            #     series_color = "FD625E"
            pass

        self.excel_helper.set_line_graphical_properties(
            series,
            width=line_width,
            style=line_style,
            color=series_color,
            smooth=smooth,
        )
        self.excel_helper.set_marker_graphical_properties(
            series, marker_symbol, marker_size, series_color
        )

    # Call Chart._plot for general properties, but prevent it from re-applying colors
    # as we've already done it for both bar and line series.
    kwargs["chart_position"] = chart_position or self.config.CHART_POSITION
    kwargs["suppress_chart_position_warning"] = False
    kwargs["custom_colors"] = (
        False  # Prevent Chart._plot from re-applying custom colors
    )
    kwargs["nofill"] = True  # Prevent Chart._plot from filling anything
    super()._plot(df, ws, **kwargs)

StackedBarChart(config=None, excel_helper=None)

Bases: Chart

Stacked or percent-stacked bar chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration object containing chart and general Excel settings.	None
excel_helper	ExcelHelper	Helper object for Excel-specific operations.	None

See Also

Chart : Base class for all chart types.

Initialize a stacked bar chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration overrides. Defaults to Config().	None
excel_helper	ExcelHelper	Helper instance. Created from config when not provided.	None

Source code in sql2excel/chart.py

def __init__(self, config=None, excel_helper=None):
    """Initialize a stacked bar chart.

    Parameters
    ----------
    config : Config, optional
        Configuration overrides. Defaults to ``Config()``.
    excel_helper : ExcelHelper, optional
        Helper instance. Created from *config* when not provided.
    """
    super().__init__(config, excel_helper)

TwoAxesChart(config=None, excel_helper=None)

Bases: Chart

Base class for charts with two y-axes (primary and secondary).

The first data column is plotted on the primary y-axis; additional columns are plotted on the secondary y-axis.

Parameters:

Name	Type	Description	Default
config	Config	Configuration object containing chart and general Excel settings.	None
excel_helper	ExcelHelper	Helper object for Excel-specific operations.	None

Attributes:

Name	Type	Description
chart	Chart or None	The main chart object (combined chart).
chart1	Chart or None	The chart object for the primary y-axis.
chart2	Chart or None	The chart object for the secondary y-axis.

See Also

Chart : Base class for all chart types.

Initialize a two-axes chart.

Parameters:

Name	Type	Description	Default
config	Config	Configuration overrides. Defaults to Config().	None
excel_helper	ExcelHelper	Helper instance. Created from config when not provided.	None

Source code in sql2excel/chart.py

def __init__(self, config=None, excel_helper=None):
    """Initialize a two-axes chart.

    Parameters
    ----------
    config : Config, optional
        Configuration overrides. Defaults to ``Config()``.
    excel_helper : ExcelHelper, optional
        Helper instance. Created from *config* when not provided.
    """
    super().__init__(config, excel_helper)
    self.chart = None
    self.chart1 = None
    self.chart2 = None

plot(df, ws, **kwargs)

Plot a two-axes chart from df into ws.

The first data column is plotted on the primary axis and the remaining columns on the secondary axis. Subclasses should set self.chart1 and self.chart2 before calling this method.

Parameters:

Name	Type	Description	Default
df	DataFrame	Data to plot.	required
ws	Worksheet	The target worksheet.	required
**kwargs	dict	Chart customization options (see Chart._plot).	{}

Source code in sql2excel/chart.py

def plot(self, df, ws, **kwargs):
    """Plot a two-axes chart from *df* into *ws*.

    The first data column is plotted on the primary axis and the
    remaining columns on the secondary axis.  Subclasses should set
    ``self.chart1`` and ``self.chart2`` before calling this method.

    Parameters
    ----------
    df : pandas.DataFrame
        Data to plot.
    ws : Worksheet
        The target worksheet.
    **kwargs : dict
        Chart customization options (see ``Chart._plot``).
    """

    self.write_dataframe(df, ws, **kwargs)

    # Keep chart settings that should not be set twice
    title = kwargs.get("title")
    show_legend = kwargs.get("show_legend")
    legend_position = kwargs.get("legend_position")
    chart_position = kwargs.get("chart_position")
    xlabel = kwargs.get("xlabel")
    kwargs["title"] = None
    kwargs["show_legend"] = None
    kwargs["legend_position"] = None
    # To prevent adding the chart to the sheet more than once
    kwargs["chart_position"] = "Unknown"
    kwargs["suppress_chart_position_warning"] = True
    kwargs["xlabel"] = None

    # Leave settings that need to be applied to both figure if present:
    # width, height, nofill, etc

    # Add data to chart1
    data_columns = kwargs.get("data_columns")
    # Default to second column as the first column is the category
    kwargs["data_columns"] = [data_columns[0]] if data_columns else [2]
    kwargs["set_categories"] = True

    # Set the parent self.chart to delegate to the parent class
    self.chart = self.chart1

    self._add_data(
        df,
        ws,
        **kwargs,
    )

    # Finalize the plot setting for chart1
    # super()._plot(df, ws, **kwargs)

    # Add data to chart 2
    kwargs["data_columns"] = (
        data_columns[1:]
        if data_columns
        else [i for i in range(3, len(df.columns) + 1)]
    )
    kwargs["set_categories"] = False  # Set to False to avoid duplication

    # Reset the chart of the super class to delegate to the parent class
    self.chart = self.chart2

    # Options for chart 2
    kwargs["chart_style"] = kwargs.get("chart_style2")
    kwargs["chart_shape"] = kwargs.get("chart_shape2")
    kwargs["ylabel"] = kwargs.get("ylabel2")
    kwargs["ylim"] = kwargs.get("ylim2")
    kwargs["y_orientation"] = kwargs.get("y_orientation2")
    kwargs["yaxis_major_unit"] = kwargs.get("yaxis_major_unit2")
    kwargs["y_log_base"] = kwargs.get("y_log_base2")

    self._add_data(df, ws, **kwargs)

    # Finalize the plot setting for chart2
    super()._plot(df, ws, **kwargs)

    # self.chart2.y_axis.title = kwargs.get("ylabel2")

    # Set the second y-axis
    self.chart2.y_axis.axId = 200
    self.chart1.y_axis.crosses = "max"

    # Remove secondary x-axis so both y-axes share the same x-axis
    # self.chart2.x_axis.delete = True

    # Concat the two charts
    # At this point you need to use `self.chart` of the super class
    self.chart1 += self.chart2
    self.chart = self.chart1

    # kwargs["ylabel"] = kwargs.get("ylabel") or kwargs.get("ylabel1")

    kwargs["chart_style"] = kwargs.get("chart_style1")
    kwargs["chart_shape"] = kwargs.get("chart_shape1")
    kwargs["ylabel"] = kwargs.get("ylabel1")
    kwargs["ylim"] = kwargs.get("ylim1")
    kwargs["y_orientation"] = kwargs.get("y_orientation1")
    kwargs["yaxis_major_unit"] = kwargs.get("yaxis_major_unit1")
    kwargs["y_log_base"] = kwargs.get("y_log_base1")
    # Restore general settings
    kwargs["title"] = title
    kwargs["show_legend"] = show_legend
    kwargs["legend_position"] = legend_position
    kwargs["chart_position"] = chart_position or self.config.CHART_POSITION
    kwargs["suppress_chart_position_warning"] = False
    kwargs["xlabel"] = xlabel

    # Delegate
    super()._plot(df=df, ws=ws, **kwargs)

parse_sql_file(filepath)

Parse an annotated SQL file into a list of QueryConfig objects.

The file is split on semicolons into individual queries. Comment lines (starting with --) are interpreted as directives that configure chart type, title, section heading, and other Excel parameters. Supported directives include chart, title, sheet_param, query_param, and any other key=value pair consumed by QueryConfig.

Parameters:

Name	Type	Description	Default
filepath	str	Path to the .sql file to parse.	required

Returns:

Type	Description
list of QueryConfig	One QueryConfig per query (or per combination of query_param / sheet_param values).

Raises:

Type	Description
ValueError	If sheet_param directives contain duplicate parameter names, if more than one sheet_param name is used, or if option formatting is invalid.

Source code in sql2excel/parser.py

def parse_sql_file(filepath: str) -> List[QueryConfig]:
    """Parse an annotated SQL file into a list of ``QueryConfig`` objects.

    The file is split on semicolons into individual queries.  Comment lines
    (starting with ``--``) are interpreted as directives that configure
    chart type, title, section heading, and other Excel parameters.
    Supported directives include ``chart``, ``title``, ``sheet_param``,
    ``query_param``, and any other ``key=value`` pair consumed by
    ``QueryConfig``.

    Parameters
    ----------
    filepath : str
        Path to the ``.sql`` file to parse.

    Returns
    -------
    list of QueryConfig
        One ``QueryConfig`` per query (or per combination of
        ``query_param`` / ``sheet_param`` values).

    Raises
    ------
    ValueError
        If ``sheet_param`` directives contain duplicate parameter names,
        if more than one ``sheet_param`` name is used, or if option
        formatting is invalid.
    """
    with open(filepath, "r") as file:
        content = file.read()

    # Split the content by semicolons to separate queries
    raw_queries = content.split(";")

    queries_config = []
    # Each entry is a dict: {param_name: [val1, val2, ...]}
    sheet_params = []
    for query in raw_queries:

        if not query.strip():
            continue

        # Split each segment by lines
        lines = query.splitlines()

        # Extract comments for QueryConfig
        excel_options = {}
        # A list of list of dictionaries
        query_params = []
        for line in lines:
            if line.strip().startswith("--"):
                line = line.replace("--", "")

                # Sheet parameters
                if "sheet_param" in line:
                    line = line.replace("sheet_param", "")
                    parsed = _from_pipe_sep_values_to_list_of_dict(line)
                    if isinstance(parsed, list) and len(parsed) > 0:
                        param_dict = parsed[0]
                        param_name = list(param_dict.keys())[0]
                        param_values = [list(d.values())[0] for d in parsed]
                        # Check for duplicate parameter names
                        existing_names = [list(sp.keys())[0] for sp in sheet_params]
                        if param_name in existing_names:
                            raise ValueError(
                                f"Duplicate sheet_param name '{param_name}' is not allowed"
                            )
                        sheet_params.append({param_name: param_values})
                    continue

                # Query parameters
                if "query_param" in line:
                    line = line.replace("query_param", "")
                    qp = _from_pipe_sep_values_to_list_of_dict(line)
                    query_params.append(qp)
                    continue

                # Arguments that are list-like or tuple-like
                data_columns = re.findall(_data_columns_pattern, line)
                data_columns = data_columns[0] if data_columns else []
                line = re.sub(_data_columns_pattern, "", line)
                headings = re.findall(_headings_pattern, line)
                headings = headings[0] if headings else []
                line = re.sub(_headings_pattern, "", line)

                # Parse the comment
                options = line.split(",")
                # options += data_columns
                if data_columns:
                    options.append(data_columns)

                if headings:
                    options.append(headings)
                try:
                    for option in options:
                        option = (
                            option.split(":") if ":" in option else option.split("=")
                        )

                        # Skips irrelevant comments
                        # `'chart'` is an exception because it can have no value
                        if (
                            len(option) < 2
                            and option[0].strip() != "chart"
                            and option[0].strip() != "exec"
                        ):
                            # print("skipped: ", option)
                            continue

                        key = option[0].strip().lower().replace(" ", "_")
                        try:
                            value = option[1].strip()
                        except IndexError:
                            value = "chart"

                        if value.startswith("[") or value.startswith("("):
                            value = _parse_list_or_tuple(value)

                        value = _convert(value)

                        if key:
                            excel_options[key] = value
                except Exception as e:
                    raise ValueError(
                        f"Incorrectly formatted SQL file. Error parsing options: {e}"
                    )

        # Create QueryConfig object
        query = query.strip() + ";"
        if query_params:
            query_params = [
                {k: v for d in combination for k, v in d.items()}
                for combination in product(*query_params)
            ]

            for qp in query_params:
                query_config = QueryConfig(
                    sql=query, from_sql_script=True, **excel_options
                )
                query_config.sql_params = qp

                # Replace parameter name in section_heading for query_param
                if "section_heading" in query_config.xl_params:
                    heading = str(query_config.xl_params["section_heading"])
                    for qp_name, qp_value in qp.items():
                        heading = heading.replace(":" + qp_name, str(qp_value))
                        heading = heading.replace(qp_name, str(qp_value))
                    query_config.xl_params["section_heading"] = heading

                queries_config.append(query_config)
        else:
            query_config = QueryConfig(sql=query, from_sql_script=True, **excel_options)

            queries_config.append(query_config)

    # Create multiple copies of query_config for each combination of sheet parameters
    if sheet_params:
        # Build the cartesian product of all parameter value lists
        param_names = [list(sp.keys())[0] for sp in sheet_params]
        param_value_lists = [list(sp.values())[0] for sp in sheet_params]

        # NOTE: sheet parameters should be used to repeat the same analysis for different entities (e.g. countries, etc)
        if len(param_names) > 1:
            raise ValueError(
                "Multiple `sheet_param` names are not supported. Please use only one `sheet_param`."
            )

        queries_config_extended = []
        for combination in product(*param_value_lists):
            param_combo = dict(zip(param_names, combination))

            for query_config in queries_config:
                # Avoid modifying the original objects which is reused across iterations
                new_xl_params = query_config.xl_params.copy()

                new_sql_params = (
                    query_config.sql_params.copy() if query_config.sql_params else {}
                )

                new_config = QueryConfig(
                    sql=query_config.sql,
                    sql_params=new_sql_params,
                    from_sql_script=query_config.from_sql_script,
                    **new_xl_params,
                )

                # Replace :param_name in SQL and add to sql_params
                for sp_name, sp_value in param_combo.items():
                    new_config.sql_params[sp_name] = sp_value

                # Use first combination's value as the sheet name
                if "sheetname" not in new_config.xl_params:
                    new_config.xl_params["sheetname"] = str(
                        list(param_combo.values())[0]
                    )

                # Replace parameter name in section_heading (with or without colon)
                if "section_heading" in new_config.xl_params:
                    heading = str(new_config.xl_params["section_heading"])
                    for sp_name, sp_value in param_combo.items():
                        heading = heading.replace(":" + sp_name, str(sp_value))
                        heading = heading.replace(sp_name, str(sp_value))
                    new_config.xl_params["section_heading"] = heading

                queries_config_extended.append(new_config)
        queries_config = queries_config_extended

    return queries_config