Create Combined PDF from SQL Server Data, MS Office documents and Images

We can create combined pdf from various sources using iTextSharp.dll and Microsoft Interop dlls and to Convert Office document to PDF we need to install SaveAsPDF.exe Add-on. It can be downloaded in the following URL
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=9943

Office documents can be converted to PDF and then append to stream/PDF. Other documents such as images, sql server data etc can be directly append to stream/PDF. The stream/PDF can be outputted to user.

Pre-requisties

To create Combined PDF we need to pursue the following steps
•    Need to Install Microsoft Office for using interop dll in project

o    Microsoft.Office.Interop.Word;
o    Microsoft.Office.Interop.Excel;
o    Microsoft.Office.Interop.PowerPoint;
o    Microsoft.Office.Interop.Access

•    Need to Install SaveAsPdf.exe for converting office document to PDF
•    Need to download iTextSharp.dll to manipulate with PDF

Codings

Namespace

using iTextSharp.text.pdf;

using iTextSharp.text;

using Microsoft.Office.Interop.Word;

using Microsoft.Office.Interop.Excel;

using Microsoft.Office.Core;

using PowerPoint = Microsoft.Office.Interop.PowerPoint;

Intializations

static string strWordDocumentPath = "";

Microsoft.Office.Interop.Word.ApplicationClass oWord1;

Microsoft.Office.Interop.Word.Document oDoc1;

object objFalse = false;

object objTrue = true;

object missing = System.Reflection.Missing.Value;

Microsoft.Office.Interop.Word.Find fnd;

Microsoft.Office.Interop.Word.Range rngdoc;

MemoryStream MStream = new MemoryStream();

iTextSharp.text.Document pdfdoc = new iTextSharp.text.Document(PageSize.A4, 10, 10, 10, 10);

PdfWriter pdfwriter;

…..

Opening PDF Document in Memory Stream

pdfwriter = PdfWriter.GetInstance(pdfdoc, MStream);

pdfdoc.Open();

//Code to Add pdf contents

pdfdoc.Close();

Add Image to PDF

iTextSharp.text.Image imgpdf = iTextSharp.text.Image.GetInstance(Server.MapPath(strinputDocumentPath));

imgpdf.ScaleToFit(570, 1000);

pdfdoc.Add(imgpdf);

Add Notepad to PDF

  string strnotepadtext = OpenDocument();

                iTextSharp.text.Paragraph pdfnotepad = new iTextSharp.text.Paragraph(strnotepadtext);

                pdfdoc.NewPage();

                pdfdoc.Add(pdfnotepad);

……

  public string OpenDocument()

    {

        if (oWord1 != null && oDoc1 != null)

        {

            oDoc1.Close(ref objTrue, ref missing, ref missing);

            oWord1.Quit(ref objTrue, ref missing, ref missing);

            while (Marshal.ReleaseComObject(oDoc1) != 0)

            { }

            while (Marshal.ReleaseComObject(oWord1) != 0)

            { }

            oDoc1 = null;

            oWord1 = null;

        }

        if (oWord1 == null && oDoc1 == null)

        {

            //If word object is nul then create objects for accessing the wod document

            try

            {

                oWord1 = new Microsoft.Office.Interop.Word.ApplicationClass();

                oDoc1 = new Microsoft.Office.Interop.Word.Document();

            }

            catch (Exception ex)

            {

            }

        }

        if (!Directory.Exists(Server.MapPath("~/Temp/")))

        {

            Directory.CreateDirectory(Server.MapPath("~/Temp/"));

        }

        object sFileName = Server.MapPath(strWordDocumentPath);

        oDoc1 = oWord1.Documents.Open(ref sFileName, ref objFalse, ref objFalse,

                     ref objFalse, ref missing, ref missing, ref missing, ref missing,

                     ref missing, ref missing, ref missing,

                     ref objTrue, ref missing, ref missing, ref missing);

        oDoc1.Activate();

        System.Object start = 0;

        System.Object end = oDoc1.Characters.Count;

        rngdoc = oDoc1.Range(ref start, ref end);

        string txtwordcontents = rngdoc.Text;

        oDoc1.Close(ref objTrue, ref missing, ref missing);

        oWord1.Quit(ref objTrue, ref missing, ref missing);

        return txtwordcontents;

    }

Add SQL Table to PDF

DataSet ds = new DataSet();

SqlConnection sqlcon = new SqlConnection();

            sqlcon.ConnectionString = System.Configuration.ConfigurationManager.AppSettings["SqlConnectionString"];

            sqlcon.Open();

  SqlDataAdapter dataAdapter = new SqlDataAdapter("select * from Tbl_User", sqlcon);

  dataAdapter.Fill(ds);

CreatePages(pdfdoc, ds);

…….

public void CreatePages(iTextSharp.text.Document document , DataSet dataset)

    {

        bool first = true;

       foreach (System.Data.DataTable table in ds.Tables)

        {

            if (first)

               first = false;

            else

               document.NewPage();

            document.Add(FormatHeaderPhrase(table.TableName));

            PdfPTable pdfTable = new PdfPTable(table.Columns.Count);

            pdfTable.DefaultCell.Padding = 3;

            pdfTable.WidthPercentage = 100; // percentage

            pdfTable.DefaultCell.BorderWidth = 2;

            pdfTable.DefaultCell.HorizontalAlignment = Element.ALIGN_CENTER;

            foreach (DataColumn column in table.Columns)

            {

                pdfTable.AddCell(column.ColumnName);

            }

            pdfTable.HeaderRows = 1;  // this is the end of the table header

            pdfTable.DefaultCell.BorderWidth = 1;

            Color altRow = new Color(242, 242, 242);

            int i = 0;

            foreach (DataRow row in table.Rows)

            {

                i++;

                if (i % 2 == 1)

                    pdfTable.DefaultCell.BackgroundColor = altRow;

                foreach (object cell in row.ItemArray)

                {

                    //assume toString produces valid output

                    pdfTable.AddCell(FormatPhrase(cell.ToString()));

                }

                if (i % 2 == 1)

                    pdfTable.DefaultCell.BackgroundColor = Color.WHITE;

          }

           document.Add(pdfTable);

        }

    }

Merging Office Document PDF to Current PDF

public void MergeFiles(string sourceFile)

    {

        try

        {

            // we create a reader for a certain document

            PdfReader reader = new PdfReader(Server.MapPath(sourceFile));

            // we retrieve the total number of pages

            int n = reader.NumberOfPages;

       

            PdfContentByte cb = pdfwriter.DirectContent;

            PdfImportedPage page;

            int rotation;

       

                int i = 0;

                while (i < n)

                {

                    i++;

                   pdfdoc.SetPageSize(reader.GetPageSizeWithRotation(i));

                    pdfdoc.NewPage();

                    page = pdfwriter.GetImportedPage(reader, i);

                    rotation = reader.GetPageRotation(i);

                    if (rotation == 90 || rotation == 270)

                    {

                        cb.AddTemplate(page, 0, -1f, 1f, 0, 0, reader.GetPageSizeWithRotation(i).Height);

                    }

                    else

                    {

                        cb.AddTemplate(page, 1f, 0, 0, 1f, 0, 0);

                    }

                                

                }

            }

        catch (Exception e)

        {

            string strOb = e.Message;

        }

    }

Converting doc,docx,rtf,accdb files to PDF

public void ConvertWordToPdf(string strsource, string strTarget)

    {

        object source = Server.MapPath(strsource);

        object Target = Server.MapPath(strTarget);

        Microsoft.Office.Interop.Word.ApplicationClass MSdoc = new Microsoft.Office.Interop.Word.ApplicationClass();

        //Use for the parameter whose type are not known or say Missing

        object Unknown = Type.Missing;

        //Creating the instance of Word Application         

        if (MSdoc == null) MSdoc = new Microsoft.Office.Interop.Word.ApplicationClass();

        try

        {

            MSdoc.Visible = false;

            MSdoc.Documents.Open(ref source, ref Unknown,

                 ref Unknown, ref Unknown, ref Unknown,

                 ref Unknown, ref Unknown, ref Unknown,

                 ref Unknown, ref Unknown, ref Unknown,

                 ref Unknown, ref Unknown, ref Unknown, ref Unknown, ref Unknown);

            MSdoc.Application.Visible = false;

            MSdoc.WindowState = Microsoft.Office.Interop.Word.WdWindowState.wdWindowStateMinimize;

            object format = Microsoft.Office.Interop.Word.WdSaveFormat.wdFormatPDF;

            MSdoc.ActiveDocument.SaveAs(ref Target, ref format,

                    ref Unknown, ref Unknown, ref Unknown,

                    ref Unknown, ref Unknown, ref Unknown,

                    ref Unknown, ref Unknown, ref Unknown,

                    ref Unknown, ref Unknown, ref Unknown,

                   ref Unknown, ref Unknown);

        }

        catch (Exception e)

        {

        }

        finally

        {

            if (MSdoc != null)

            {

                //MSdoc.Documents.Close(ref Unknown, ref Unknown, ref Unknown);

                MSdoc.Application.Quit(ref Unknown, ref Unknown, ref Unknown);

            }

         

        }

    }

Converting xls,xlsx,xml files to PDF/XPS

public void ConvertExcelToPdf(string strsource, string strTarget)

    {

        object source = Server.MapPath(strsource);

        object Target = Server.MapPath(strTarget);

        // Create an instance of the Excel ApplicationClass object.         

        Microsoft.Office.Interop.Excel.ApplicationClass excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();

        // Declare a variable to hold the reference to the workbook.

        Workbook excelWorkBook = null;

        object paramMissing = Type.Missing;

        XlFixedFormatType paramExportFormat = XlFixedFormatType.xlTypePDF;

        //XlFixedFormatType paramExportFormat = XlFixedFormatType.xlTypeXPS;

        XlFixedFormatQuality paramExportQuality = XlFixedFormatQuality.xlQualityStandard;

        bool paramOpenAfterPublish = false;

        bool paramIncludeDocProps = true;

        bool paramIgnorePrintAreas = true;

        object paramFromPage = Type.Missing;

        object paramToPage = Type.Missing;

        try

        {

            // Open the source workbook.

            excelWorkBook = excelApplication.Workbooks.Open((string)source, paramMissing, paramMissing, paramMissing,

                paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing,

                paramMissing, paramMissing, paramMissing, paramMissing, paramMissing);

            // Save it in the target format.

            if (excelWorkBook != null)

                excelWorkBook.ExportAsFixedFormat(paramExportFormat, Target, paramExportQuality, paramIncludeDocProps,

                    paramIgnorePrintAreas, paramFromPage, paramToPage, paramOpenAfterPublish, paramMissing);

        }

        catch (Exception ex)

        {

            // Respond to the error.

            Console.WriteLine(ex.Message);

        }

        finally

        {

            // Close the workbook object.

            if (excelWorkBook != null)

            {

                excelWorkBook.Close(false, paramMissing, paramMissing);

                excelWorkBook = null;

            }

            // Close the ApplicationClass object.

            if (excelApplication != null)

            {

                excelApplication.Quit();

                excelApplication = null;

            }

            GC.Collect();

            GC.WaitForPendingFinalizers();

            GC.Collect();

            GC.WaitForPendingFinalizers();

        }

    }

Converting ppt,pps files to PDF/XPS

public void ConvertPPTToPdf(string strsource, string strTarget)

    {

        var app = new PowerPoint.Application();

        var pres = app.Presentations;

        var file = pres.Open(Server.MapPath(strsource), MsoTriState.msoTrue, MsoTriState.msoTrue, MsoTriState.msoFalse);

        file.SaveCopyAs(Server.MapPath(strTarget), Microsoft.Office.Interop.PowerPoint.PpSaveAsFileType.ppSaveAsPDF, MsoTriState.msoTrue);

    }