Solved

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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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