This project has moved and is read-only. For the latest updates, please go here.

SaveAs PDF option

Aug 24, 2012 at 10:32 PM

Is it possible to save the workbook as a PDF?  Does such an option exist now, and if not, could it be added as a new feature?  I will be streaming output, since I'm writing this as an ASP.Net application, so I thought that I might be able to just specify that type in the header.

 

Barring that, I have saved workbooks (as 2010 .xlsx format, then convert 'down' to 2003 .xls) to /temp folder, from where they can be manipulated.  I would think they could be saved as PDF as well.  This 2nd option would use ClosedXML to create the workbook.  Other methods in C# would convert it to PDF.

 

Please let me know what options there are.

 

Cheers

Steve

Aug 24, 2012 at 10:36 PM

Nope, I don't think PDF export is anywhere near the horizon.

Sep 25, 2012 at 8:36 PM

Hi,

 

I solved this by first saving the .xlsx to a temp folder, then using the following code to 'Save As' .pdf:

 

            if (pdf) //export as PDF
            {
 
                var filePathName = Server.MapPath("~/temp"+ "\\";
                // save the book to pdf
                var targetPath = Path.GetFileNameWithoutExtension(outputFile) + ".pdf";
                var newFileName = @filePathName + targetPath;
 
                wb.SaveAs(outputFile);
                try
                {
                    ExcelToPdfConverter.ConvertExcelToPdf(outputFile, newFileName);
 
                    Response.Redirect("~/temp/" +targetPath);
                }
                catch
                { }
 
            }

The ExcelToPDFConverter Class:

using ExcelOffice = Microsoft.Office.Interop.Excel;
public class ExcelToPdfConverter     {         private static object missing = System.Reflection.Missing.Value;         public static void ConvertExcelToPdf(string excelFileIn, string pdfFileOut)         {             var excel = new Application();             try             {                 excel.Visible = false;                 excel.ScreenUpdating = false;                 excel.DisplayAlerts = false;                 FileInfo excelFile = new FileInfo(excelFileIn);                 string filename = excelFile.FullName;                 Workbook wbk = excel.Workbooks.Open(filename, missing,                     missing, missing, missing, missing, missing,                     missing, missing, missing, missing, missing,                     missing, missing, missing);                                  wbk.Activate();                 ((_Worksheet)wbk.ActiveSheet).PageSetup.Orientation =XlPageOrientation.xlLandscape;                  object outputFileName = pdfFileOut;                 var fileFormat = XlFixedFormatType.xlTypePDF;                 // Save document into PDF Format                 wbk.ExportAsFixedFormat(fileFormat, outputFileName,                     missing, missing, missing,                     missing, missing, missing,                     missing);                 object saveChanges = XlSaveAction.xlDoNotSaveChanges;                 (wbk).Close(saveChanges, missing, missing);                 wbk = null;             }             finally             {                 (excel).Quit();                 excel = null;             }         }     }

Hope others find if useful
Sep 26, 2012 at 5:18 AM
Edited Sep 27, 2012 at 7:06 AM

This require the server to install Microsoft Office 2007/2010.

The above runs the actual Excel Program and generates the PDF file.

There are some missing steps above.

After the process done, if you open the Windows Task Manager, you'll find out there are several EXCEL process running in background (If you have converted the document multiple times.

They are needed to be killed manually. By making the variable to null like this:

excel = null;

does not kill the process.

here are the codes based on yours that I've modified:

UPDATE: THESE CODES IN THIS REPLY CONTAIN BUGS, PLEASE READ THE REPLY BELOW FOR FIXED CODES.

*** Codes deleted. ***

If there are multiple client request the PDF file and the processes are execute at the same time, its hard to identify the correct process id that used by respective client. Possible killing the wrong process.

Sep 26, 2012 at 6:23 PM
Edited Sep 26, 2012 at 6:26 PM

Hi Adrian,

 

Yes you are correct on 2 points:

1. To apply a conversion (whether to .PDF or .xls (Excel 2003), it will require Excel to be installed on the server.  This has a lot of folks nervous.  I was able to get my DBA to agree to it, but we may use another solution such as active reports for the .PDF.  I do not know of a way to use ClosedXML and also save it as .xls, except to open it in Excel and run a SaveAs, much the same as I'm doing here- so Excel does have to be on the server if that is where it will be converted.

2. The excel.exe processes do remain open.  I posted this code before checking it for memory leaks, because it worked.  In the prior mentioned example of converting a .xlsx file created by ClosedXML to .xls, I used the NetOffice project from Codeplex to do all the garbage collection and clean-up.  I will probably do the same for the .PDF, because I have this working.

Unfortunately, your code does not produce the .xlsx file (perhaps it's gets corrupted).  Did you test this?  Since it can't, it eventually hangs, and the error is cleanly trapped from my calling code.  No .PDF can be generated.  If you can get it working, it may be worth posting.  Thanks for the effort- at the very least, it made me look at the memory leaks, which still must be handled.

 Also, the 3rd 'missing' param should be set to true, so the file is not Read-Only when you try to create a .PDF from it, and the 7th to true to avoid a prompt if it ends up R/O for any other reason.

Steve

Sep 27, 2012 at 4:31 AM
Edited Sep 27, 2012 at 7:07 AM

Hi, SteveMets,

Sorry for that, the above code is not fully tested and contains some minor bugs at method of ReleaseExcelResources().

I have created a small project in codeplex for this library, source code and demo project are available at 

http://excelconverter.codeplex.com/

Below are the fully tested working code, you may however modify some of the parameters to suite your need. Cheers.

using System;
using Excel = Microsoft.Office.Interop.Excel;
using System.Collections;
using System.Diagnostics;
using System.IO;

namespace ExcelHelper
{
    public class ExcelDocConverter
    {
        Hashtable myHashtable;
        int MyExcelProcessId;

        Excel.Application excel;
        Excel.Workbook wbk;
        Excel.Worksheet worksheet1;

        object missing = System.Reflection.Missing.Value;

        public enum FormatType
        {
            XLS,
            XLSX,
            PDF,
            XPS
        }

        public void Convert(FormatType formatType, string originalFile, string targetFile)
        {
            CheckForExistingExcellProcesses();

            excel = new Microsoft.Office.Interop.Excel.Application();
            excel.Visible = false;
            excel.ScreenUpdating = false;
            excel.DisplayAlerts = false;

            GetTheExcelProcessIdThatUsedByThisInstance();

            wbk = excel.Workbooks.Open(originalFile, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

            switch (formatType)
            {
                case FormatType.XLS:
                    {
                        wbk.SaveAs(targetFile, Excel.XlFileFormat.xlExcel8, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
                    }
                    break;
                case FormatType.XLSX:
                    {
                        wbk.SaveAs(targetFile, Excel.XlFileFormat.xlWorkbookDefault, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
                    }
                    break;
                case FormatType.PDF:
                    {
                        wbk.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, targetFile, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard, false, true, missing, missing, false, missing);
                    }
                    break;
                case FormatType.XPS:
                    {
                        wbk.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypeXPS, targetFile, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard, false, true, missing, missing, false, missing);
                    }
                    break;
                default:
                    break;
            }

            ReleaseExcelResources();
            KillExcelProcessThatUsedByThisInstance();
        }

        void ReleaseExcelResources()
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet1);
            }
            catch
            { }
            finally
            {
                worksheet1 = null;
            }

            try
            {
                if (wbk != null)
                    wbk.Close(false, missing, missing);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(wbk);
            }
            catch
            { }
            finally
            {
                wbk = null;
            }

            try
            {
                excel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            }
            catch
            { }
            finally
            {
                excel = null;
            }
        }

        void CheckForExistingExcellProcesses()
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");
            myHashtable = new Hashtable();
            int iCount = 0;

            foreach (Process ExcelProcess in AllProcesses)
            {
                myHashtable.Add(ExcelProcess.Id, iCount);
                iCount = iCount + 1;
            }
        }

        void GetTheExcelProcessIdThatUsedByThisInstance()
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");

            // Search For the Right Excel
            foreach (Process ExcelProcess in AllProcesses)
            {
                if (myHashtable == null)
                    return;

                if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
                {
                    // Get the process ID
                    MyExcelProcessId = ExcelProcess.Id;
                }
            }

            AllProcesses = null;
        }

        void KillExcelProcessThatUsedByThisInstance()
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");

            foreach (Process ExcelProcess in AllProcesses)
            {
                if (myHashtable == null)
                    return;

                if (ExcelProcess.Id == MyExcelProcessId)
                    ExcelProcess.Kill();
            }

            AllProcesses = null;
        }
    }
}

Sep 27, 2012 at 5:08 PM
Edited Sep 27, 2012 at 5:10 PM

Hi Adrian,

 

Yes, I fixed what you had- mostly just checking to see if an object was null before trying to use it, and prefer it to the other libraries I was using.  I also like the additions you made for the other format types.  I'm thinking of including MS Word docs as well, maybe some other types.

Of course, what I would really like is an open source project that will allow these to be created without having MS Office installed on the server, and just stream them to the browser.  It's not a safe practice, and I barely got my DBA to agree to it.  Do you know of anything on CodePlex or Nuget that does that?

 

Thanks!

 

Steve

Nov 6, 2012 at 9:01 AM

Hi, steve, you may want to have a look at here:

http://www.codeproject.com/Questions/489423/ConvertplusaplusExcelplustoplusapluspdf

There are lots of methods introduced for converting Excel Documents into PDF.

One of the method I found in that link to be interesting is this:

Programmatically Convert Documents to PDFs the Easy Way