?
Solved

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

Posted on 2008-06-17
3
Medium Priority
?
1,543 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
mikeopolo earned 100 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

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.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

770 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