How to print a chart from an Excel Chartsheet using Python

Question

I'm completely new to coding (it's just for fun and hopefully to save some time at work) and I've been trying to make my first lines of code working.
Specifically, I want my code to open a certain Excel workbook, find certain worksheets which are actually chartsheets (each one with only one chart in it) and print them as pdf/jpeg files in a specific folder. I went for the ExportAsFixedFormat, but I encountered the following error.

AttributeError: 'Chartsheet' object has no attribute 'ExportAsFixedFormat'

Could you please help me? Is there any way to print/save a Chartsheet?
I went through the Chartsheet Object's methods, but I couldn't find anything helpful. I'm sure I'm missing something.

Some info about my configuration:
Windows 10 Home x64
Excel for Microsoft 365 MSO (16.0.13628.20318) 64 bit
Python 3.8 32 bit
Pywin32 version 227

Below the chunk of code that I'm having problems with.
[Edit]: below the whole code I wrote, maybe the error is not where I think it is.
Thank you in advance and sorry for my broken English.

First of all, I've imported a ton of things, I'm aware I most probably need just half of them.

import plotly
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import matplotlib
import matplotlib.cm as cm
import matplotlib.mlab as mlab
import win32com.client as win32
import openpyxl
import os, sys
import math
import openpyxl
from openpyxl import Workbook, load_workbook
from openpyxl import chart
from openpyxl import chartsheet
from openpyxl.chartsheet.publish import WebPublishItem, WebPublishItems
from openpyxl.drawing.spreadsheet_drawing import SpreadsheetDrawing
#from .drawings import find_images
from openpyxl.chartsheet import Chartsheet
import openpyxl.chart
import win32com.client
from pdf2image import convert_from_path
from pathlib import Path
import xlsxwriter

And here is the code I wrote:

path_filePy = Path(__file__).resolve()
current_folder = path_filePy.parent

image_folder_name = "Immages"
image_folder_path = os.path.join(current_folder, image_folder_name)  
try:  
    os.mkdir(image_folder_path)  
except OSError:
    files = os.listdir(image_folder_path)
    for f in files:
        os.remove(image_folder_path + '\\'+ f)

folder_list = os.listdir(current_folder)
excel_list=[]
for l in folder_list:
    if l.endswith('.xlsx'):
        excel_list.append(l)

chartsheets_names=['Chartsheet1', 'Chartsheet2', 'Chartsheet3', 'Chartsheet4']

excel = win32.gencache.EnsureDispatch('Excel.Application')

for excelfile in excel_list:    
    wb = load_workbook(os.path.join(current_folder, excelfile))      
    for sheet in chartsheets_names:    
        ws=wb[sheet]   
        image_file_name = excelfile[:-5]+'_'+sheet+'.pdf'        
        image_file_path = os.path.join(image_folder_path,image_file_name)        
        ws.ExportAsFixedFormat(0, image_file_path)                     
        convert_from_path(image_file_path, dpi=300, output_folder=image_folder_path,fmt='jpeg')
    wb.Close()

Answer 1

I managed to get what I wanted in the end. Below is the code I'm using now, maybe it could be helpful to someone else too. I think I was messing with code related to win32com and code related to openpxl.

Now I would like my Chartsheets to stretch all over the printing area prior to printing (I tried to set margins to zero, it does not work). I think I should use wb_sheet.PageSetup.ChartSize with the value FullPage, but I do not get how to assign it.

import os
import sys  
from pathlib import Path  
import win32com.client as w3c
from pdf2image import convert_from_path

# find the parent folder of the .py file
path_filePy = Path(__file__).resolve()
current_folder = path_filePy.parent
print(current_folder)

# create the destination folder or empty it if existing
image_folder_name = "Immages"
image_folder_path = os.path.join(current_folder, image_folder_name)  
#print(image_folder_path)
try:  
    os.mkdir(image_folder_path)  
except OSError:
    files = os.listdir(image_folder_path)
    for f in files:
        os.remove(image_folder_path + '\\'+ f)

# list of file in the folder
folder_list = os.listdir(current_folder)

# list of only *.xlsx files
excel_list=[]
for l in folder_list:
    if l.endswith('.xlsx'):
        excel_list.append(l)

# listof sheets' names I want to print
chartsheets_names=['Sheet1', 'Sheet2', 'Sheet3', 'Sheet4']

o = w3c.Dispatch("Excel.Application")
o.Visible = False

# for each sheet names as in my list, in each xlsx file, it prints in both pdf and jpeg
for excel_file in excel_list:
    try:
        wb_path = os.path.join(os.path.abspath(current_folder), excel_file)    
        wb = o.Workbooks.Open(wb_path)            
        for wb_sheet in wb.Sheets: 
            if wb_sheet.Name in chartsheets_names:
                path_to_pdf = os.path.join(os.path.abspath(image_folder_path), excel_file[:-5] + ' - ' + str(wb_sheet.Name) + '.pdf') 
                wb_sheet.SaveAs(path_to_pdf, FileFormat=57)
                convert_from_path(
                    path_to_pdf, # the input pdf file
                    dpi=300, 
                    output_folder=image_folder_path, 
                    fmt='jpeg',
                    output_file=str(excel_file[:-5] + ' - ' + str(wb_sheet.Name)),               
                    poppler_path = r"C:\where\your\poppler\bin folder is", 
                    use_pdftocairo=False)  
            else: next
        wb.Close(False) 
    except OSError:
        next
o.Quit

`

Answer 2

See updated answer further down. See further update below.

After pip install comtypes this works for me:

import os
import comtypes.client

SOURCE_DIR = r'C:\Users\xyz\SO-samples'  # adjust to your needs
TARGET_DIR = r'C:\Users\xyz\SO-samples'  # adjust to your needs

app = comtypes.client.CreateObject('Excel.Application')
app.Visible = False

infile = os.path.join(os.path.abspath(SOURCE_DIR), 'an-excel-file.xlsx')
outfile = os.path.join(os.path.abspath(TARGET_DIR), 'an-excel-file.pdf')

doc = app.Workbooks.Open(infile)
doc.ExportAsFixedFormat(0, outfile, 1, 0)
doc.Close()

app.Quit()

Updated answer - selectable sheets:

import os
import win32com.client

SOURCE_DIR = r'C:\Users\xyz\SO-samples'  # adjust
TARGET_DIR = r'C:\Users\xyz\SO-samples'  # adjust

wb_path = os.path.join(os.path.abspath(SOURCE_DIR), 'xyzzy.xlsx')
wb = o.Workbooks.Open(wb_path)

o = win32com.client.Dispatch("Excel.Application")
o.Visible = False

# print 1 sheet to 1 file
path_to_pdf = os.path.join(os.path.abspath(TARGET_DIR), 'xyzzy2.pdf')
ws_index_list = [2]  # say you want to print this sheet
wb.WorkSheets(ws_index_list).Select()
wb.ActiveSheet.ExportAsFixedFormat(0, path_to_pdf)

# print 2 sheets to 1 file
path_to_pdf = os.path.join(os.path.abspath(TARGET_DIR), 'xyzzy-1-3.pdf')
ws_index_list = [1,3]  # say you want to print these sheets
wb.WorkSheets(ws_index_list).Select()
wb.ActiveSheet.ExportAsFixedFormat(0, path_to_pdf)

# print 3 sheets to 1 file each
ws_index_list = [1,2,3]  # say you want to print these sheets
for ws_index in ws_index_list:
    path_to_pdf = os.path.join(os.path.abspath(TARGET_DIR), 'xyzzy-' + str(ws_index) + '.pdf')
    wb.WorkSheets([ws_index]).Select()
    wb.ActiveSheet.ExportAsFixedFormat(0, path_to_pdf)

# select sheet by name, print 1 sheet to 1 file
ws_sheet_name = 'named_sheet'
path_to_pdf = os.path.join(os.path.abspath(TARGET_DIR), 'xyzzy-' + ws_sheet_name + '.pdf')
wb.WorkSheets(ws_sheet_name).Select()
wb.ActiveSheet.ExportAsFixedFormat(0, path_to_pdf)

Further update - printing sheet names, select sheet by name:

import win32com.client as w3c
import os, sys

SOURCE_DIR = r'C:\Users\xyz\SO-samples'
TARGET_DIR = r'C:\Users\xyz\SO-samples'

wb_path = os.path.join(os.path.abspath(SOURCE_DIR), 'xyzzy.xlsx')

o = w3c.Dispatch("Excel.Application")
o.Visible = False

wb = o.Workbooks.Open(wb_path) 

for wb_sheet in wb.Sheets:
    print(wb_sheet.Name)

### this works
ws_sheet_name = [1,3]

path_to_pdf = os.path.join(os.path.abspath(TARGET_DIR), 'xyzzy' + '.pdf')
wb.Worksheets(ws_sheet_name).Select()
wb.ActiveSheet.ExportAsFixedFormat(0, path_to_pdf)

### this works
ws_sheet_name = 'xyzzy'

path_to_pdf = os.path.join(os.path.abspath(TARGET_DIR), 'xyzzy-xyzzy' + '.pdf')
wb.Worksheets(ws_sheet_name).Select()
wb.ActiveSheet.ExportAsFixedFormat(0, path_to_pdf)

wb.Close()