• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1562
  • Last Modified:

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

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
MTecho
Asked:
MTecho
  • 2
1 Solution
 
mikeopoloCommented:
Should this:
 //    pdfjob.cCombineAll();

be
//    pdfjob.cCombineAll;

?
Regards
Mike

0
 
MTechoAuthor Commented:
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
 
MTechoAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now