Solved

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

Posted on 2008-06-17
3
1,481 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvieā€¦
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

759 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now