Get Python to pdf/image print selected cells in MS Excel

Question

From a python program, I need to capture a selection of cells from an Excel file as an image. Right now, my solution is complex and involves using win32com to save the Excel file as a PDF, then creating an image of the PDF using an image printer, then cropping the area desired. The problem is that sometimes the location of the desired cells shifts slightly in the saved PDF, which makes accurately/repeatably cropping the desired cells in the image impossible.

I want to select a range of cells in Excel as shown below

Then I want to print those selected cells (I don't mean print to screen, but to a printer configured in the OS) from Python using win32com, or similar. Is this possible? If so, please provide some sample code, or a link to a tutorial, etc.

Excel has this "Print Selection" option in its printing options (see below).

If this is possible, I could use the image printer I have installed to print that selection of cells directly to an image.

Answer 1

OK, I found a way to do this myself, so I thought I'd post how I did it. The solution launches a custom Excel Macro from a Python routine.

The first thing I needed was to create a dummy/blank Excel file with an .xlsm extension (I called mine Selection_Printer.xlsm. Then in one of the VBA modules in that Excel file I wrote the following code:

Sub PrintSelection(wbPath As String, minRow As Integer, maxRow As Integer, minCol As Integer, maxCol As Integer)

    Dim wb As Workbook, sh As Worksheet

    'Get path of this file
    'myPath = Application.ThisWorkbook.Path

    'Get the workbook objects needed
    Set wb = Workbooks.Open(wbPath)

    'Get the worksheet objects needed
    'All of the sheets I wanted to print were "Sheet1", 
    '  but another parameter could be added to make this dynamic
    Set sh = wb.Sheets("Sheet1")

    'Select the cells desired
    Call SelectRange(sh, minRow, maxRow, minCol, maxCol)

    'Print the selection only
    Selection.PrintOut From:=1, To:=1, Copies:=1, Collate:=True

End Sub

Sub SelectRange(sh As Worksheet, minRow As Integer, maxRow As Integer, minCol As Integer, maxCol As Integer)

    Dim selRange As Range

    With sh
      .Cells(minRow, maxCol).Select 'start by selecting upper left corner
      Set selRange = .Range(.Cells(minRow, minCol), .Cells(maxRow, maxCol))
    End With

    selRange.Select

End Sub

This prints a cell range defined by minRow, maxRow, minCol, and maxCol in the worksheet at the Path location of wbPath.

In my Python code I call this macro using the following code:

import os    
from win32com import client
import win32print

def excel_jpg_macro(xl_path, row_min, row_max, col_min, col_max):
    """
    Program that calls the selection printing Macro in Excel from Python

    :param xl_path: Full path to XL file that has cell selection to be printed
    :param row_min: starting row of the selection desired (Excel uses indexing 
                    starting at 1) as an integer
    :param row_max: ending row of the selection desired as an integer
    :param col_min: starting column of the selection desired as an integer
    :param col_max: ending column of the selection desired as an integer
    """

    print_path = os.path.abspath('./Selection_Printer.xlsm')
    xl_macro = 'PrintSelection'
    # Use win32 to run the Excel Macro from Python
    xl = client.Dispatch("Excel.Application")
    xl.Workbooks.Open(Filename=print_path, ReadOnly=True)
    xl.Application.Run(xl_macro, xl_path, row_min, row_max, col_min, col_max)
    xl.Application.Quit() 
    del xl


def jpg_excel_selection(xl_path, selection, save_name):
    """
    Prints a selection from an excel file to a jpg image printer
    :param xl_path: Full OS path to the Excel file that you want to print from
    :param selection: tuple with the (minimum row, maximum row, minimum column,
                      maximum column) with each value inside the tuple as an 
                      integer.  NOTE: Excel uses indexing starting at 1
    """

    try:
        # Capture the current default printer
        curr_printer = win32print.GetDefaultPrinter()

        # Temporarily change the default printer to be the image printer
        temp_printer = "ImagePrinter Pro"
        win32print.SetDefaultPrinter(temp_printer)

        # Run the macro that prints just the selection
        xl_path = os.path.abspath(xl_path)
        excel_jpg_macro(xl_path, *selection)

        # Change the default printer back to the original setting
        win32print.SetDefaultPrinter(curr_printer)
    except:
        print("Printing XL Selection Image Failed")