Printing Excel using Interop

Question

Does anybody have any idea how to print an excel file programatically using C# and the Excel Interop? If so, can you please provide code?

Answer 1

In order to print, you can make use of the Worksheet.PrintOut() method. You can omit any or all of the optional arguments by passing in Type.Missing. If you omit all of them, it will default to printing out one copy from your active printer. But you can make use of the arguments to set the number of copies to print, collation, etc. See help on the Worksheet.PrintOut() method for more.

The example they show in the help file is:

private void PrintToFile()
{
    // Make sure the worksheet has some data before printing.
    this.Range["A1", missing].Value2 = "123";
    this.PrintOut(1, 2, 1, false, missing, true, false, missing);
}

But unless you need to change the default settings, you can simply pass in Type.Missing for all the arguments. Here's an example using automation to open an Excel Workbook, print the first page, and then shut down:

void PrintMyExcelFile()
{
    Excel.Application excelApp = new Excel.Application();

    // Open the Workbook:
    Excel.Workbook wb = excelApp.Workbooks.Open(
        @"C:\My Documents\Book1.xls",
        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);

    // Get the first worksheet.
    // (Excel uses base 1 indexing, not base 0.)
    Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];

    // Print out 1 copy to the default printer:
    ws.PrintOut(
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
        Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    // Cleanup:
    GC.Collect();
    GC.WaitForPendingFinalizers();

    Marshal.FinalReleaseComObject(ws);

    wb.Close(false, Type.Missing, Type.Missing);
    Marshal.FinalReleaseComObject(wb);

    excelApp.Quit();
    Marshal.FinalReleaseComObject(excelApp);
}

Hope this helps!

Mike

Answer 2

Important improvement is the code for select the Printer, for example:

var printers = System.Drawing.Printing.PrinterSettings.InstalledPrinters;

int printerIndex = 0;

foreach(String s in printers)
{
    if (s.Equals("Name of Printer"))
    {
        break;
    }
    printerIndex++;
}

xlWorkBook.PrintOut(Type.Missing, Type.Missing, Type.Missing, Type.Missing,printers[printerIndex], Type.Missing, Type.Missing, Type.Missing);

Answer 3

All the answers already given are good but I just thought I keep it much simpler with more options for showing a dialogue box and defining the orientation of the page for printing.

private void PrintExcel()
{
    string filePath = "C:\file\location\here\";
            Excel.Application excelApp = new Excel.Application();

    // Open Workbook:
    Excel.Workbook wb = excelApp.Workbooks.Open(filePath);

    // Define the orientation for the page
    ((Excel._Worksheet)wb.ActiveSheet).PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;

    //Decide which worksheet to print
    Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];

    // Option to print with or to show dialogue box
    bool userDidntCancel = excelApp.Dialogs[Excel.XlBuiltInDialog.xlDialogPrint].Show();

    // Option to print out wihtout the dialogue box. 
    // WARNING: Do not use Dialogue option and this at the same time. 
    // It will print the page even if you cancel the dialogue print option.
    ws.PrintOut();

    // Cleanup your code
    GC.Collect();
    GC.WaitForPendingFinalizers();

    Marshal.FinalReleaseComObject(ws);

    wb.Close(false, Type.Missing, Type.Missing);
    Marshal.FinalReleaseComObject(wb);

    // Close/Exit File
    excelApp.Quit();
    Marshal.FinalReleaseComObject(excelApp);

}

I hope this bit helps someone. :D