Depicting CSV or SDF in XLSX (Excel)

Problem

You want to depict molecules along with their associated data read from a CSV file in an xlsx Excel file. See example in drugs.xlsx and in Figure 1.

../_images/csv2xlsx-screenshot-small.png — **Figure 1. Example of XLSX generated with csv2xlsx**

Ingredients

OEChem TK - cheminformatics toolkit
OEDepict TK - molecule depiction toolkit
XlsxWriter - Python module for creating Excel XLSX files.

Difficulty Level

🌶️🌶️

Download

Download code

csv2xlsx.py

See also the Usage subsection.

Source Code

csv2xlsx#!/usr/bin/env python3
# (C) 2026 Cadence Design Systems, Inc. (Cadence)
# All rights reserved.
# TERMS FOR USE OF SAMPLE CODE The software below ("Sample Code") is
# provided to current licensees or subscribers of Cadence products or
# SaaS offerings (each a "Customer").
# Customer is hereby permitted to use, copy, and modify the Sample Code,
# subject to these terms. Cadence claims no rights to Customer's
# modifications. Modification of Sample Code is at Customer's sole and
# exclusive risk. Sample Code may require Customer to have a then
# current license or subscription to the applicable Cadence offering.
# THE SAMPLE CODE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
# EXPRESS OR IMPLIED.  OPENEYE DISCLAIMS ALL WARRANTIES, INCLUDING, BUT
# NOT LIMITED TO, WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
# PARTICULAR PURPOSE AND NONINFRINGEMENT. In no event shall Cadence be
# liable for any damages or liability in connection with the Sample Code
# or its use.

"""Convert a CSV or an SDF file into EXCEL XLSX file."""

import argparse
import io
import os
import pathlib
import sys
from pathlib import Path

import xlsxwriter
from openeye import oechem, oedepict
from rich_argparse import HelpPreviewAction, RichHelpFormatter

__SCRIPT_NAME__ = Path(__file__).absolute().stem
__SCRIPT_DESC__ = "Convert an CSV or an SDF file into EXCEL XLSX file."
__SCRIPT_TOOLKITS__ = ["oechem", "oedepict"]
__SCRIPT_KEYWORDS__ = ["depiction", "XLSX", "chem-informatics"]
__SCRIPT_CATEGORIES__ = ["depiction"]


def parse_options() -> argparse.Namespace:
    """Set up command line options."""
    parser = argparse.ArgumentParser(
        add_help=True,
        formatter_class=RichHelpFormatter,
        description="[yellow]" + __SCRIPT_DESC__ + "[/yellow]",
    )

    # input options
    io_group = parser.add_argument_group("Input/Output options")
    io_group.add_argument(
        "--mol",
        type=str,
        required=True,
        metavar="MOL-FILE",
        help="input MOL file (.sdf, .csv)",
    )
    io_group.add_argument(
        "--xlsx",
        type=str,
        required=True,
        metavar="CVS-FILE",
        help="output XLSX file (.xlsx)",
    )

    parser.add_argument("--help-image", action=HelpPreviewAction)
    return parser.parse_args()


def main() -> int:
    """Convert an CSV or an SDF file into EXCEL XLSX file."""
    args = parse_options()

    xlsx_file = pathlib.Path(args.xlsx)
    if xlsx_file.suffix.lower() != ".xlsx":
        oechem.OEThrow.Fatal("Invalid file extension expected .xlsx!")

    mol_list: list[oechem.OEMolBase] = read_molecules(args.mol)
    data_tags = collect_data_tags(mol_list)

    width, height = 250, 250
    opts = oedepict.OE2DMolDisplayOptions(width, height, oedepict.OEScale_AutoScale)
    opts.SetBackgroundColor(oechem.OETransparentColor)
    opts.SetTitleLocation(oedepict.OETitleLocation_Hidden)

    write_xlsx_file(args.xlsx, mol_list, pathlib.Path(args.mol).name, data_tags, opts)

    return os.EX_OK


def read_molecules(mol_filename: str) -> list[oechem.OEMolBase]:
    """Read molecules from a CSV or SDF file and return a list of OEMolBase objects."""
    mol_path = pathlib.Path(mol_filename)
    if not mol_path.exists():
        oechem.OEThrow.Fatal(f"Cannot open input file '{mol_path.name}'!")

    ifs = oechem.oemolistream()
    if not ifs.open(str(mol_path)):
        oechem.OEThrow.Fatal(f"Cannot open input file '{mol_path.name}'!")

    if ifs.GetFormat() not in [oechem.OEFormat_CSV, oechem.OEFormat_SDF]:
        oechem.OEThrow.Fatal("Input must be a CSV or SDF file!")
    mol_list: list[oechem.OEMolBase] = [
        oechem.OEGraphMol(m) for m in ifs.GetOEGraphMols()
    ]
    return mol_list


def collect_data_tags(mol_list: list[oechem.OEMolBase]) -> list[str]:
    """Collect unique data tags from a list of molecules."""
    tags: list[str] = []
    for mol in mol_list:
        for dp in oechem.OEGetSDDataIter(mol):
            if dp.GetTag() not in tags:
                tags.append(dp.GetTag())
    return tags


def write_xlsx_file(
    output_filename: str,
    mol_list: list[oechem.OEMolBase],
    input_filename: str,
    data_tags: list[str],
    opts: oedepict.OE2DMolDisplayOptions,
) -> None:
    """Write the collected data to an XLSX file."""
    workbook = xlsxwriter.Workbook(output_filename)
    worksheet = workbook.add_worksheet()

    head_font, head_format = add_head_format(workbook)
    data_font, data_format_even, data_format_odd = add_data_formats(workbook)

    # estimate width of columns
    max_widths = []
    max_widths.append(opts.GetWidth() * 0.15)

    for tag in data_tags:
        maxwidth = oedepict.OEEstimateTextWidth(tag, head_font) * 2.0
        for mol in mol_list:
            if oechem.OEHasSDData(mol, tag):
                value = oechem.OEGetSDData(mol, tag)
                estimated_width = oedepict.OEEstimateTextWidth(value, data_font)
                maxwidth = max(maxwidth, estimated_width)
        max_widths.append(maxwidth * 0.12)

    # generate header
    row, col = 0, 0
    worksheet.set_row(row, None, head_format)
    worksheet.merge_range("A1:D1", input_filename)

    row, col = 1, 0
    worksheet.set_row(row, None, head_format)
    worksheet.set_column(col, col, max_widths[col])
    worksheet.write(row, col, "Molecule")

    for tag in data_tags:
        col += 1
        worksheet.set_column(col, col, max_widths[col])
        worksheet.write(row, col, tag)

    for mol in mol_list:
        row += 1
        data_format = data_format_even if row % 2 == 0 else data_format_odd
        worksheet.set_row(row, opts.GetHeight() * 0.75, data_format)

        col = 0
        image_data = get_molecule_image(mol, opts)
        image_options = {
            "object_position": 1,
            "image_data": image_data,
            "x_scale": 1.0,
            "y_scale": 1.0,
        }
        worksheet.insert_image(row, col, "python.png", options=image_options)

        for tag in data_tags:
            col += 1
            value = "N/A"
            if oechem.OEHasSDData(mol, tag):
                value = oechem.OEGetSDData(mol, tag)
            worksheet.write(row, col, value)

    workbook.close()


def get_molecule_image(
    mol: oechem.OEMolBase, opts: oedepict.OE2DMolDisplayOptions
) -> io.BytesIO:
    """Get a molecule image as a byte stream."""
    image = oedepict.OEImage(
        opts.GetWidth(), opts.GetHeight(), oechem.OETransparentColor
    )
    oedepict.OEPrepareDepiction(mol)
    disp = oedepict.OE2DMolDisplay(mol, opts)
    oedepict.OERenderMolecule(image, disp, False)
    return io.BytesIO(oedepict.OEWriteImageToString("png", image))


def add_head_format(
    workbook: xlsxwriter.Workbook,
) -> tuple[oedepict.OEFont, xlsxwriter.format]:
    """Add header format to the workbook."""
    font = oedepict.OEFont(
        oedepict.OEFontFamily_Default,
        oedepict.OEFontStyle_Bold,
        18,
        oedepict.OEAlignment_Center,
        oechem.OEBlack,
    )
    head_format = workbook.add_format(
        {"bold": True, "align": "center", "valign": "vcenter", "size": 18}
    )
    head_format.set_bg_color("#F4F4F4")
    head_format.set_border_color("#DDDDDD")
    head_format.set_border()

    return font, head_format


def add_data_formats(
    workbook: xlsxwriter.Workbook,
) -> tuple[oedepict.OEFont, xlsxwriter.format, xlsxwriter.format]:
    """Add data formats to the workbook."""
    font = oedepict.OEFont(
        oedepict.OEFontFamily_Default,
        oedepict.OEFontStyle_Default,
        12,
        oedepict.OEAlignment_Center,
        oechem.OEBlack,
    )

    format_even = workbook.add_format(
        {"bold": False, "align": "center", "valign": "vcenter", "size": 12}
    )
    format_even.set_shrink()
    format_even.set_bg_color("#FFFFF4")
    format_even.set_border_color("#DDDDDD")
    format_even.set_border()

    format_odd = workbook.add_format(
        {"bold": False, "align": "center", "valign": "vcenter", "size": 12}
    )
    format_odd.set_shrink()
    format_odd.set_bg_color("#FFF4FF")
    format_odd.set_border_color("#DDDDDD")
    format_odd.set_border()

    return font, format_even, format_odd


setattr(main, "__SCRIPT_NAME__", __SCRIPT_NAME__)
setattr(main, "__SCRIPT_DESC__", __SCRIPT_DESC__)
setattr(main, "__SCRIPT_TOOLKITS__", __SCRIPT_TOOLKITS__)
setattr(main, "__SCRIPT_KEYWORDS__", __SCRIPT_KEYWORDS__)
setattr(main, "__SCRIPT_CATEGORIES__", __SCRIPT_CATEGORIES__)

if __name__ == "__main__":
    sys.exit(main())

Solution

The CSV file format is a text file format containing comma-separated values. In OEChem TK this file format is implemented to enable data exchange with a wide variety of other software. Each line of a CSV file stores data for a molecule that is represented by a SMILES string.

See also

CSV File Format section of the OEChem TK documentation about the layout of the CSV file format.

When reading a CSV file, the fields of the file are attached to each molecule as SD data. This data can be accessed by the OEGetSDDataIter function that returns an iterator over all the SD data (tag - value) pairs of a molecule. The collect_data_tags function iterates over a list of molecules and returns the unique tags of the data attached to the molecules.

def collect_data_tags(mol_list: list[oechem.OEMolBase]) -> list[str]:
    """Collect unique data tags from a list of molecules."""
    tags: list[str] = []
    for mol in mol_list:
        for dp in oechem.OEGetSDDataIter(mol):
            if dp.GetTag() not in tags:
                tags.append(dp.GetTag())
    return tags

The write_xlsx_file function takes a list of molecules read from a CSV file along with the data tags returned by the collect_data_tags function.

First, a worksheet is created and the cell styles to be used throughout the spreadsheet are defined. Next, the width of each column is estimated using the OEEstimateTextWidth function. The table header is then generated by writing the input filename into the first row and each of the data tags into the second row. As the function iterates over the molecules, each molecule is depicted in a new row together with its associated data, using alternating row styles.

def write_xlsx_file(
    output_filename: str,
    mol_list: list[oechem.OEMolBase],
    input_filename: str,
    data_tags: list[str],
    opts: oedepict.OE2DMolDisplayOptions,
) -> None:
    """Write the collected data to an XLSX file."""
    workbook = xlsxwriter.Workbook(output_filename)
    worksheet = workbook.add_worksheet()

    head_font, head_format = add_head_format(workbook)
    data_font, data_format_even, data_format_odd = add_data_formats(workbook)

    # estimate width of columns
    max_widths = []
    max_widths.append(opts.GetWidth() * 0.15)

    for tag in data_tags:
        maxwidth = oedepict.OEEstimateTextWidth(tag, head_font) * 2.0
        for mol in mol_list:
            if oechem.OEHasSDData(mol, tag):
                value = oechem.OEGetSDData(mol, tag)
                estimated_width = oedepict.OEEstimateTextWidth(value, data_font)
                maxwidth = max(maxwidth, estimated_width)
        max_widths.append(maxwidth * 0.12)

    # generate header
    row, col = 0, 0
    worksheet.set_row(row, None, head_format)
    worksheet.merge_range("A1:D1", input_filename)

    row, col = 1, 0
    worksheet.set_row(row, None, head_format)
    worksheet.set_column(col, col, max_widths[col])
    worksheet.write(row, col, "Molecule")

    for tag in data_tags:
        col += 1
        worksheet.set_column(col, col, max_widths[col])
        worksheet.write(row, col, tag)

    for mol in mol_list:
        row += 1
        data_format = data_format_even if row % 2 == 0 else data_format_odd
        worksheet.set_row(row, opts.GetHeight() * 0.75, data_format)

        col = 0
        image_data = get_molecule_image(mol, opts)
        image_options = {
            "object_position": 1,
            "image_data": image_data,
            "x_scale": 1.0,
            "y_scale": 1.0,
        }
        worksheet.insert_image(row, col, "python.png", options=image_options)

        for tag in data_tags:
            col += 1
            value = "N/A"
            if oechem.OEHasSDData(mol, tag):
                value = oechem.OEGetSDData(mol, tag)
            worksheet.write(row, col, value)

    workbook.close()

The write_image_to_file function generates a molecule depiction to a binary stream.

def get_molecule_image(
    mol: oechem.OEMolBase, opts: oedepict.OE2DMolDisplayOptions
) -> io.BytesIO:
    """Get a molecule image as a byte stream."""
    image = oedepict.OEImage(
        opts.GetWidth(), opts.GetHeight(), oechem.OETransparentColor
    )
    oedepict.OEPrepareDepiction(mol)
    disp = oedepict.OE2DMolDisplay(mol, opts)
    oedepict.OERenderMolecule(image, disp, False)
    return io.BytesIO(oedepict.OEWriteImageToString("png", image))

Usage

See Download section to download the script.

> csv2xlsx --help

The following command converts (drugs.csv) to (drugs.xlsx)

> csv2xlsx --mol drugs.csv --xlsx drugs.xlsx

Reading the columns of a CSV file into SD data fields means that the OEChem TK provides a meta-data interchange between sdf files and CSV files. Consequently, the same Python script can be used to generate a xlsx file reading an sdf file.

The following command converts (drugs.csv) to (drugs.xlsx)

> csv2xlsx --mol drugs.sdf --xlsx drugs.xlsx

Depicting CSV or SDF in XLSX (Excel)

Problem

Ingredients

Difficulty Level

Download

Source Code

Solution

Usage

See also

See also in OEChem TK manual

See also in OEDepict TK manual

Depicting CSV or SDF in XLSX (Excel)

Problem

Ingredients

Difficulty Level

Download

Source Code

Solution

Usage

Related Python Cookbook Recipes

See also

See also in OEChem TK manual

See also in OEDepict TK manual