Print Excel files with Microsoft.Office.Interop.Excel and CLR in Python?

Question

I have been searching many ways to print an excel file without opening Excel app, I used Microsoft.Office.Interop.Excel in C# and it works pretty well so I decided to look for a way to make it works in python too, I found IronPython but I need just python, then I found pythonnet to make .NET assemblies work in python.

The problem is that intalling it (or trying to) from the source https://github.com/pythonnet/pythonnet It gives me an error about Windows SDK not found or something.

Then I installed it by pip and the installation was successful, but when I try to import or to add a reference another error is shown:

 Unable to find assembly 'Microsoft.Office.Interop.Excel'.
   at Python.Runtime.CLRModule.AddReference(String name)

I have downloaded and installed Interop Assemblies from here so they are supposed to be installed.

I found a way to print excel files in here using IronPython and the Interop dll.

My main problem is that I need to print some excel files without opening Excel application, if you have another option is welcome

If there is no other option, what should I do to make it find the assemblies?

Additional Info: To print in C# I used this:

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
String CompletePath = path;
Microsoft.Office.Interop.Excel.Workbook wb = excelApp.Workbooks.Open(CompletePath,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];
try
{

    // Open the Workbook:

    // Get the first worksheet.
    // (Excel uses base 1 indexing, not base 0.)
    ws.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
    ws.PageSetup.FitToPagesTall = 1;
    ws.PageSetup.FitToPagesWide = 1;

    ws.PageSetup.TopMargin = 0;
    ws.PageSetup.HeaderMargin = 0;
    ws.PageSetup.RightMargin = 0;
    ws.PageSetup.LeftMargin = 0;
    ws.PageSetup.BottomMargin = 0;
    ws.PageSetup.FooterMargin = 0;
    ws.PageSetup.CenterVertically = true;
    ws.PageSetup.CenterHorizontally = true;

    //ws.PageSetup.Zoom = false;
    ws.PrintOut(Type.Missing, Type.Missing, Type.Missing, Type.Missing, PrinterName, Type.Missing, Type.Missing, Type.Missing);
    return true;
}
catch (Exception ex)
{
    LogSystem.TextLog.create();
    LogSystem.TextLog.Write("ERROR ", LogSystem.ErrorType.Error, DateTime.Now, ex.Message);
    return false;
}

finally 
{
    // Cleanup:
    GC.Collect();
    GC.WaitForPendingFinalizers();
    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ws);
    wb.Close(false, Type.Missing, Type.Missing);
    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wb);
    excelApp.Quit();
    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp);
}

have you looked at pandas, or lower-level xlrd and openpyxl? — denfromufa, Feb 16 '15 at 17:51
here is a wrapper function https://gist.github.com/denfromufa/ec559b5af41060c5ac318f7f59d8b415#file-excel_interop_vsto-ipynb — denfromufa, Sep 06 '16 at 20:02

score 1 · Answer 1 · edited May 23 '17 at 12:22

While I believe there is a way to automate Excel using win32com.client (there is a good example) it could be useful to wrap com interop code with .net and expose it using Python.Net. The consideration could be the better support and simplicity of com interop by .Net, but you should take into account that Python.Net itself isn't perfect and wasn't actively developed for years.

The approach is next:

Write the code using .net and Expose this code using as simple interface as possible
Avoid exposing the COM types!
Avoid managing a state!
Consume the library from python.

Step 1

Create new .Net ClassLibrary project named ExcelToolsLibrary
Add a reference to Microsoft.Office.Interop.Excel

Place next code there:

namespace ExcelTools
{
using System;
using Microsoft.Office.Interop.Excel;
public class ExcelPrinter
{
    public bool PrintFile(string fileName, string printerName) 
    {
        var excel = new Application();
        var workbook = excel.Workbooks.Open(fileName);
        var worksheet = (Worksheet)workbook.Worksheets[1];
        try
        {
            SetupPage(worksheet);
            worksheet.PrintOut(ActivePrinter: printerName ?? Type.Missing);
            workbook.Close(false);
            return true;
        }
        catch (Exception ex)
        {
            LogError(ex);
            return false;
        }
        finally
        {
            DisposeExcelObjects(excel,worksheet,workbook);
        }
    }
    private void SetupPage(Worksheet worksheet)
    {
        worksheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
        //put additional page setup here
    }

    private void LogError(Exception e)
    {
        //add your logging
    }

    private void DisposeExcelObjects(Application excelApp,params object[] comObjects)
    {
        try
        {
            foreach (var obj in comObjects)
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj);    
            excelApp.Quit();
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp);    
        }
        catch (Exception e)
        {
            LogError(e);
        }
    }

}
}

Step 2

To use this library in python:

Build the project and place ExcelToolsLibrary.dll from the output to any location available from path (you can use .pth file for that).

Import the library and use it like this:

import clr
clr.AddReference("ExcelToolsLibrary") # 'ExcelToolsLibrary' is a dll name without the path
import ExcelTools # 'ExcelTools' is a .Net namespace
printer = ExcelTools.ExcelPrinter()
printer.PrintFile(fileName,None) # None means that will be printed on default printer

https://gist.github.com/denfromufa/ec559b5af41060c5ac318f7f59d8b415#file-excel_interop_vsto-ipynb — denfromufa, Sep 06 '16 at 20:14
I was googling how to setup a page before print, and found my own post. Good work Igor :) — igorushi, Sep 08 '16 at 10:02

Print Excel files with Microsoft.Office.Interop.Excel and CLR in Python?

1 Answers1

Step 1

Step 2