Solved

Converting Excel files to *.pdf - get only first sheet

Posted on 2008-06-17
3
1,514 Views
Last Modified: 2012-06-27
I have included the method detailed Sample1 (C# 2005) in the COM documentation for PDFCreator into a C# application that can write Excel files.
The Excel workbook files are saved to disc, then converted into *.pdf files.
Using this process, only the first sheet of an Excel workbook gets put into the *.pdf file.

This is the same result obtained in Excel if "File / Print..." is selected from the menu, "PDFCreator" is selected as printer and the "Print what" is left as the default "Active sheet(s)". That is, as if "Entire workbook" is not selected.

Is there a way to get the entire workbook included in the *.pdf file?

I have listed the C# class module code below. This also includes some commented-out code derived from VBA code in an Access database. This used the parameter "Workbook ExcelFile" instead of "string ExcelFile" in the parameter list for PrinttoPDF.
Would this code be useful?
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Diagnostics;
//using PDFCreator;
 
namespace Reports
{
    public class clsPrintPDF
    {
        private string sPDFName = "";
        private string sPDFPath = "";
        //Workbook tmpExcelFile = null;
        //private bool gblIgnoreEmpty = false;
        //private clsPDFCreator pdfjob = null;
 
        private const int maxTime = 20;
 
        private PDFCreator.clsPDFCreator _PDFCreator;
        private PDFCreator.clsPDFCreatorError pErr;
 
        private bool ReadyState;
        private Timer timer1;
 
        public clsPrintPDF()
        {
            try
            {
                //pdfjob = new clsPDFCreator();
                timer1 = new Timer();
                timer1.Tick += new EventHandler(this.timer1_Tick);
                string parameters;
                //statusBar1.Text = "Status: Program is started.";
 
                pErr = new PDFCreator.clsPDFCreatorError();
 
                _PDFCreator = new PDFCreator.clsPDFCreator();
                _PDFCreator.eError += new PDFCreator.__clsPDFCreator_eErrorEventHandler(_PDFCreator_eError);
                _PDFCreator.eReady += new PDFCreator.__clsPDFCreator_eReadyEventHandler(_PDFCreator_eReady);
 
                parameters = "/NoProcessingAtStartup";
 
                if (!_PDFCreator.cStart(parameters, false))
                {
                    //statusBar1.Text = "Status: Error[" + pErr.Number + "]: " + pErr.Description;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "clsPrintPDF: New", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
 
        public void PrinttoPDF(string PdfFilePath, string PdfFileName, string ExcelFile, bool IgnoreEmpty, ref string Msg)
        {
            try
            {
                //int lTtlSheets = 0;
                string DeleteCommand = "";
 
                sPDFName = PdfFileName;
                sPDFPath = PdfFilePath;
                //tmpExcelFile = ExcellFile;
                //gblIgnoreEmpty = IgnoreEmpty;
                PDFCreator.clsPDFCreatorOptions opt;
                string DefaultPrinter;
 
                //if (pdfjob.cStart("/NoProcessingAtStartup", false) == false)
                //{
                //    MessageBox.Show("Can't initialize PDFCreator.", "clsPrintPDF", MessageBoxButtons.OK, MessageBoxIcon.Error);
                //}
                //else
                //{
                if (!_PDFCreator.cIsPrintable(ExcelFile))
                {
                    AddMsg("File '" + ExcelFile + "' is not printable!", ref Msg);
                }
                else
                {
                    DeleteCommand = sPDFPath + (char)92 + sPDFName;
                    if (File.Exists(DeleteCommand)) File.Delete(DeleteCommand);
                    Debug.WriteLine("=================================================");
                    Debug.WriteLine("AutosaveDirectory = " + sPDFPath);
                    Debug.WriteLine("AutosaveFilename = " + sPDFName);
                    Debug.WriteLine("DeleteCommand = " + DeleteCommand);
                    Debug.WriteLine("ExcelFile = " + ExcelFile);
 
                    opt = _PDFCreator.cOptions;
                    opt.UseAutosave = 1;
                    opt.UseAutosaveDirectory = 1;
                    opt.AutosaveDirectory = sPDFPath;
                    opt.AutosaveFilename = sPDFName;
                    opt.AutosaveFormat = 0;
                    _PDFCreator.cOptions = opt;
                    _PDFCreator.cClearCache();
                    DefaultPrinter = _PDFCreator.cDefaultPrinter;
                    _PDFCreator.cDefaultPrinter = "PDFCreator";
                    _PDFCreator.cPrintFile(ExcelFile);
                    _PDFCreator.cPrinterStop = false;
                    ReadyState = false;
                    timer1.Interval = maxTime * 1000;
                    timer1.Enabled = true;
                    while (!ReadyState && timer1.Enabled)
                    {
                        System.Windows.Forms.Application.DoEvents();
                    }
                    timer1.Enabled = false;
                    if (!ReadyState)
                    {
                        AddMsg("Creating printer test page as pdf.\n\r\n\r" + "An error is occured: Time is up!", ref Msg);
                    }
                    _PDFCreator.cPrinterStop = true;
                    _PDFCreator.cDefaultPrinter = DefaultPrinter;
                }
                //    //_clsPDFCreator.set_cOption("UseAutosave", pdfjob) = 1;
                //    //pdfjob._clsPDFCreator.get_cOption("UseAutosave") = 1;
                //    //pdfjob.cOption("UseAutosaveDirectory") = 1;
                //    //pdfjob.cOption("AutosaveDirectory") = sPDFPath;
                //    //pdfjob.cOption("AutosaveFilename") = sPDFName;
                //    //pdfjob.cOption("AutosaveFormat") = 0;
                //    //pdfjob.cClearCache();
 
                    // Checking for the validity of the file
 
                //    // Print the document to PDF
                //    lTtlSheets = tmpExcelFile.Sheets.Count;
                //    for (int lSheet = 1; lSheet <= tmpExcelFile.Application.Sheets.Count; lSheet++)
                //    {
                //        try
                //        {
                //            //if (tmpExcelFile.Sheets[lSheet].UsedRange != null) tmpExcelFile.Sheets[lSheet].PrintOut(1, "PDFCreator");
                //            //else
                //            //{
                //            //    if (gblIgnoreEmpty) tmpExcelFile.Sheets[lSheet].PrintOut(1, "PDFCreator");
                //            //    else lTtlSheets -= 1;
                //            //}
                //            if (!tmpExcelFile.Application.Sheets[lSheet].Visible) lTtlSheets -= 1;
                //        }
                //        catch { }
                //    }
 
                //    //while (pdfjob.cCountOfPrintjobs < lTtlSheets) System.Windows.Forms.Application.DoEvents();
 
                //    // Combine all PDFs into a single file and stop the printer
                //    pdfjob.cCombineAll();
                //    pdfjob.cPrinterStop = false;
 
 
                //    while (!File.Exists(DeleteCommand)) System.Windows.Forms.Application.DoEvents();
                //}
            }
            catch (Exception ex)
            {
                AddMsg("clsPrintPDF: PrinttoPDF\n" + ex.Message, ref Msg);
            }
        }
 
        public void KillPdfMaker()
        {
            try
            {
                _PDFCreator.cClose();
                pErr = null;
                _PDFCreator = null;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "clsPrintPDF: KillPdfMaker", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
 
        private void AddMsg(string NewMsg, ref string Msg)
        {
            try
            {
                if (Msg.Length > 0) Msg += "\n";
                Msg += NewMsg;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "clsPrintPDF: AddMsg", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
 
        private void _PDFCreator_eReady()
        {
            //statusBar1.Text = "Status: \"" + _PDFCreator.cOutputFilename + "\" was created!";
            _PDFCreator.cPrinterStop = true;
            ReadyState = true;
        }
 
        private void _PDFCreator_eError()
        {
            pErr = _PDFCreator.cError;
        }
 
        private void timer1_Tick(object sender, System.EventArgs e)
        {
            timer1.Enabled = false;
        }
    }
}

Open in new window

0
Comment
Question by:MTecho
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
mikeopolo earned 50 total points
ID: 21809854
Should this:
 //    pdfjob.cCombineAll();

be
//    pdfjob.cCombineAll;

?
Regards
Mike

0
 

Author Comment

by:MTecho
ID: 21817667
Thanks for your comment,

I tried adding  _PDFCreator.cCombineAll();
before and after    _PDFCreator.cPrintFile(ExcelFile);
but this has no effect on the output *.pdf file.

The code derived from VBA had been left commented-out as the methods do not exist in Microsoft.Office.Interop.Excel. The code will not compile as the methods "UsedRange", "PrintOut" and "Visible" do not exist.

Typical error message: "'object' does not contain a definition for 'UsedRange'".

I have modified the code to have two versions of the method "PrinttoPDF": one for "string ExcelFile" and one for "Workbook ExcelFile" - should be clearer.
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Diagnostics;
//using PDFCreator;
 
namespace Reports
{
    public class clsPrintPDF
    {
        private string sPDFName = "";
        private string sPDFPath = "";
        Workbook tmpExcelFile = null;
        private bool gblIgnoreEmpty = false;
 
        private const int maxTime = 20;
 
        private PDFCreator.clsPDFCreator _PDFCreator;
        private PDFCreator.clsPDFCreatorError pErr;
 
        private bool ReadyState;
        private Timer timer1;
 
        public clsPrintPDF()
        {
            try
            {
                timer1 = new Timer();
                timer1.Tick += new EventHandler(this.timer1_Tick);
                string parameters;
                //statusBar1.Text = "Status: Program is started.";
 
                pErr = new PDFCreator.clsPDFCreatorError();
 
                _PDFCreator = new PDFCreator.clsPDFCreator();
                _PDFCreator.eError += new PDFCreator.__clsPDFCreator_eErrorEventHandler(_PDFCreator_eError);
                _PDFCreator.eReady += new PDFCreator.__clsPDFCreator_eReadyEventHandler(_PDFCreator_eReady);
 
                parameters = "/NoProcessingAtStartup";
 
                if (!_PDFCreator.cStart(parameters, false))
                {
                    //statusBar1.Text = "Status: Error[" + pErr.Number + "]: " + pErr.Description;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "clsPrintPDF: New", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
 
        public void PrinttoPDF(string PdfFilePath, string PdfFileName, string ExcelFile, ref string Msg)
        {
            try
            {
                string DeleteCommand = "";
 
                sPDFName = PdfFileName;
                sPDFPath = PdfFilePath;
                PDFCreator.clsPDFCreatorOptions opt;
                string DefaultPrinter;
 
                if (!_PDFCreator.cIsPrintable(ExcelFile))
                {
                    AddMsg("File '" + ExcelFile + "' is not printable!", ref Msg);
                }
                else
                {
                    DeleteCommand = sPDFPath + (char)92 + sPDFName;
                    if (File.Exists(DeleteCommand)) File.Delete(DeleteCommand);
                    Debug.WriteLine("=================================================");
                    Debug.WriteLine("AutosaveDirectory = " + sPDFPath);
                    Debug.WriteLine("AutosaveFilename = " + sPDFName);
                    Debug.WriteLine("DeleteCommand = " + DeleteCommand);
                    Debug.WriteLine("ExcelFile = " + ExcelFile);
 
                    opt = _PDFCreator.cOptions;
                    opt.UseAutosave = 1;
                    opt.UseAutosaveDirectory = 1;
                    opt.AutosaveDirectory = sPDFPath;
                    opt.AutosaveFilename = sPDFName;
                    opt.AutosaveFormat = 0;
                    _PDFCreator.cOptions = opt;
                    _PDFCreator.cClearCache();
                    DefaultPrinter = _PDFCreator.cDefaultPrinter;
                    _PDFCreator.cDefaultPrinter = "PDFCreator";
                    _PDFCreator.cPrintFile(ExcelFile);
                    _PDFCreator.cCombineAll();
                    _PDFCreator.cPrinterStop = false;
                    ReadyState = false;
                    timer1.Interval = maxTime * 1000;
                    timer1.Enabled = true;
                    while (!ReadyState && timer1.Enabled)
                    {
                        System.Windows.Forms.Application.DoEvents();
                    }
                    timer1.Enabled = false;
                    if (!ReadyState)
                    {
                        AddMsg("Creating printer test page as pdf.\n\r\n\r" + "An error is occured: Time is up!", ref Msg);
                    }
                    _PDFCreator.cPrinterStop = true;
                    _PDFCreator.cDefaultPrinter = DefaultPrinter;
                }
            }
            catch (Exception ex)
            {
                AddMsg("clsPrintPDF: PrinttoPDF\n" + ex.Message, ref Msg);
            }
        }
 
        public void PrinttoPDF(string PdfFilePath, string PdfFileName, Workbook ExcelFile, bool IgnoreEmpty, ref string Msg)
        {
            try
            {
                int lTtlSheets = 0;
                string DeleteCommand = "";
                PDFCreator.clsPDFCreatorOptions opt;
 
                sPDFName = PdfFileName;
                sPDFPath = PdfFilePath;
                tmpExcelFile = ExcelFile;
                gblIgnoreEmpty = IgnoreEmpty;
 
                if (_PDFCreator.cStart("/NoProcessingAtStartup", false) == false)
                {
                    AddMsg("Can't initialize PDFCreator.", ref Msg);
                }
                else
                {
                    opt = _PDFCreator.cOptions;
                    opt.UseAutosave = 1;
                    opt.UseAutosaveDirectory = 1;
                    opt.AutosaveDirectory = sPDFPath;
                    opt.AutosaveFilename = sPDFName;
                    opt.AutosaveFormat = 0;
                    _PDFCreator.cOptions = opt;
                    _PDFCreator.cClearCache();
 
                    // Checking for the validity of the file
                    DeleteCommand = sPDFPath + (char)92 + sPDFName;
                    if (File.Exists(DeleteCommand)) File.Delete(DeleteCommand);
 
                    // Print the document to PDF
                    lTtlSheets = tmpExcelFile.Sheets.Count;
                    for (int lSheet = 1; lSheet <= tmpExcelFile.Application.Sheets.Count; lSheet++)
                    {
                        try
                        {
                            if (tmpExcelFile.Sheets[lSheet].UsedRange != null) tmpExcelFile.Sheets[lSheet].PrintOut(1, "PDFCreator");
                            else
                            {
                                if (gblIgnoreEmpty) tmpExcelFile.Sheets[lSheet].PrintOut(1, "PDFCreator");
                                else lTtlSheets -= 1;
                            }
                            if (!tmpExcelFile.Application.Sheets[lSheet].Visible) lTtlSheets -= 1;
                        }
                        catch { }
                    }
 
                    while (_PDFCreator.cCountOfPrintjobs < lTtlSheets) System.Windows.Forms.Application.DoEvents();
 
                    // Combine all PDFs into a single file and stop the printer
                    _PDFCreator.cCombineAll();
                    _PDFCreator.cPrinterStop = false;
 
 
                    while (!File.Exists(DeleteCommand)) System.Windows.Forms.Application.DoEvents();
                }
            }
            catch (Exception ex)
            {
                AddMsg("clsPrintPDF: PrinttoPDF\n" + ex.Message, ref Msg);
            }
        }
 
        public void KillPdfMaker()
        {
            try
            {
                _PDFCreator.cClose();
                pErr = null;
                _PDFCreator = null;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "clsPrintPDF: KillPdfMaker", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
 
        private void AddMsg(string NewMsg, ref string Msg)
        {
            try
            {
                if (Msg.Length > 0) Msg += "\n";
                Msg += NewMsg;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "clsPrintPDF: AddMsg", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
 
        private void _PDFCreator_eReady()
        {
            //statusBar1.Text = "Status: \"" + _PDFCreator.cOutputFilename + "\" was created!";
            _PDFCreator.cPrinterStop = true;
            ReadyState = true;
        }
 
        private void _PDFCreator_eError()
        {
            pErr = _PDFCreator.cError;
        }
 
        private void timer1_Tick(object sender, System.EventArgs e)
        {
            timer1.Enabled = false;
        }
    }
}

Open in new window

0
 

Author Comment

by:MTecho
ID: 21818446
Quick update on the previous code snippet - some code removed.
The commented-out code will not compile.

Are there different methods that I should be using, or is there a different Interop library? I have been using Microsoft.Office.Interop.Excel - the other Excel functions in my application work fine with this.
        public void PrinttoPDF(string PdfFilePath, string PdfFileName, Workbook ExcelFile, bool IgnoreEmpty, ref string Msg)
        {
            try
            {
                int lTtlSheets = 0;
                string DeleteCommand = "";
                PDFCreator.clsPDFCreatorOptions opt;
 
                sPDFName = PdfFileName;
                sPDFPath = PdfFilePath;
                tmpExcelFile = ExcelFile;
                gblIgnoreEmpty = IgnoreEmpty;
 
                opt = _PDFCreator.cOptions;
                opt.UseAutosave = 1;
                opt.UseAutosaveDirectory = 1;
                opt.AutosaveDirectory = sPDFPath;
                opt.AutosaveFilename = sPDFName;
                opt.AutosaveFormat = 0;
                _PDFCreator.cOptions = opt;
                _PDFCreator.cClearCache();
 
                // Checking for the validity of the file
                DeleteCommand = sPDFPath + (char)92 + sPDFName;
                if (File.Exists(DeleteCommand)) File.Delete(DeleteCommand);
 
                // Print the document to PDF
                lTtlSheets = tmpExcelFile.Sheets.Count;
                for (int lSheet = 1; lSheet <= tmpExcelFile.Application.Sheets.Count; lSheet++)
                {
                    try
                    {
                        //if (tmpExcelFile.Sheets[lSheet].UsedRange != null) tmpExcelFile.Sheets[lSheet].PrintOut(1, "PDFCreator");
                        //else
                        //{
                        //    if (gblIgnoreEmpty) tmpExcelFile.Sheets[lSheet].PrintOut(1, "PDFCreator");
                        //    else lTtlSheets -= 1;
                        //}
                        //if (!tmpExcelFile.Application.Sheets[lSheet].Visible) lTtlSheets -= 1;
                    }
                    catch { }
                }
 
                while (_PDFCreator.cCountOfPrintjobs < lTtlSheets) System.Windows.Forms.Application.DoEvents();
 
                // Combine all PDFs into a single file and stop the printer
                _PDFCreator.cCombineAll();
                _PDFCreator.cPrinterStop = false;
 
 
                while (!File.Exists(DeleteCommand)) System.Windows.Forms.Application.DoEvents();
            }
            catch (Exception ex)
            {
                AddMsg("clsPrintPDF: PrinttoPDF\n" + ex.Message, ref Msg);
            }
        }

Open in new window

0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question