#!/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())