XLS to PDF conversion inside .net

Question

I've been looking extensively for a tool that takes an existing XLS file and converts it to PDF that then gets saved out to disk.

The catch is I need to be able to call it from within my .net console application, which eliminates most tools I've looked at. Has anyone used something like this that they would recommend? My client is willing to pay for a 3rd party tool if it does what we need.

Thanks,

PS, I forgot to include this: The client doesn't want to install Office into their production environment. Asprose seems to be the best fit so far, but it is losing some formatting when it converts the XLS files to PDF. Any other thoughts/tools?

Answer 1

I looked around and didn't find much. The one that caught my attention (because I've heard of it and it's good reputation before) is Aspose.

http://www.aspose.com/

Their PDF kit should do the trick, but you may also need other components (for reading in the Excel files and whatnot).

See the following post for the code to do it (it also mentions that you need both Aspose.Cells and Aspose.Pdf to complete the conversion):

http://aspadvice.com/forums/thread/50998.aspx

Answer 2

I have used Aspose products such as Aspose.Word to do document editing via .NET. They also have products for manipulating XLS files and conversion to PDF.

I believe their products can be used from console apps successfully.

I think you will need Aspose.Cells and possible Aspose.PDF too.

Answer 3

Does your client have a license for Excel? If so, have you considered using the COM libraries to open the XLS file and then using a PDF printer driver to print it? The Excel object model has print functionality built in. It's an old-fashioned approach (90s technology), but I don't know of any reason it couldn't work.

Answer 4

Have you considered looking for XSL FO?

Answer 5

What you need to look at are the PIA libraries for .NET.

http://www.microsoft.com/downloads/details.aspx?FamilyID=59daebaa-bed4-4282-a28c-b864d8bfa513&displaylang=en

Install Microsoft Excel 2007 on the box you're going to be running. Then install the addon for Office that lets you export PDFs.

http://www.microsoft.com/downloads/details.aspx?FamilyID=4d951911-3e7e-4ae6-b059-a2e79ed87041&displaylang=en

Once you have all of that installed, you'll need to add a reference to

Imports Microsoft.Office.Interop.Excel

to your .NET project and class.

To start Excel, use the object

    Dim excel As Microsoft.Office.Interop.Excel.Application
    excel = New Microsoft.Office.Interop.Excel.Application
    Dim tmpWorkbooks As Workbooks
    tmpWorkbooks = excel.Workbooks
Dim wb As Microsoft.Office.Interop.Excel.Workbook
    wb = tmpWorkbooks.Open(pathOfExcelFile)
    excel.Visible = False
    wb.Activate()

...... Do any editing of the workbook here if you want

To export the PDF file, call the function as directed below.

wb.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, fileName, , , , 1, 1, False, )

Finally, make sure to close the workbook and Excel after you finish with the file.

Answer 6

Existing answers all look good, I note that the target site does not want to have to install Office 2007, and so this answer will not work in that instance. In my case, I wanted the answer to this question where Office 2007 was already installed.

Hopefully the C# code below is useful to the next person who comes looking.

Office 2007

Clearly you will need the Office 2007 PIAs installed on your development machine and on the target machine for this solution to work.

Create a reference in your project to Microsoft.Office.Interop.Excel. The Office 2007 Version I am coding to below is Version 12.0.0.0. Hopefully higher versions will continue to work, but lower versions are unlikely to.

The Code

using System;
using System.IO;
using msExcel = Microsoft.Office.Interop.Excel;

namespace scpm {

    public class ExcelToPdfConverter {

        private static object missing = System.Reflection.Missing.Value;

        public static void ConvertExcelToPdf(string excelFileIn, string pdfFileOut) {
            msExcel.Application excel = new msExcel.Application();
            try {
                excel.Visible = false;
                excel.ScreenUpdating = false;
                excel.DisplayAlerts = false;

                FileInfo excelFile = new FileInfo(excelFileIn);

                string filename = excelFile.FullName;

                msExcel.Workbook wbk = excel.Workbooks.Open(filename, missing,
                    missing, missing, missing, missing, missing,
                    missing, missing, missing, missing, missing,
                    missing, missing, missing);
                wbk.Activate();

                object outputFileName = pdfFileOut;
                msExcel.XlFixedFormatType fileFormat = msExcel.XlFixedFormatType.xlTypePDF;

                // Save document into PDF Format
                wbk.ExportAsFixedFormat(fileFormat, outputFileName,
                    missing, missing, missing,
                    missing, missing, missing,
                    missing);

                object saveChanges = msExcel.XlSaveAction.xlDoNotSaveChanges;
                ((msExcel._Workbook)wbk).Close(saveChanges, missing, missing);
                wbk = null;
            }
            finally {
                ((msExcel._Application)excel).Quit();
                excel = null;
            }
        }

    }
}

References

MSDN article with similar code: http://msdn.microsoft.com/en-us/library/bb407651.aspx and the official docs on ExportAsFixedFormat: http://msdn.microsoft.com/en-us/library/bb238907.aspx

A Caveat

I have one problem with this. If I supply a temporary filename, e.g. from using System.IO.Path.GetTempFileName(); - to give me c:\Users\scpm\AppData\Local\Temp\tmp5166.tmp, excel will actually save it as c:\Users\scpm\AppData\Local\Temp\tmp5166.tmp.pdf which is a bit of a pain.

If anyone has a resolution to this issue, please post in the comments and I will update the code.

If you keep the extension in the pdfFileOut as .pdf then this code works reasonably well.

Answer 7

I'd stay away from the PIAs for a number of reasons that I won't get into.

If I were doing this myself, I'd do the following:

• Read the data using OleDbConnection into a DataSet
• Output the DataSet to XML
• Run an XSLT transform to convert the XML into HTML (with styles)
• Use abcPDF (or Aspose) to render the HTML into a PDF

Edit: Obviously, using this method you will lose any existing formatting.

Answer 8

Another solution is automating OpenOffice.

I've had no end of problems (poor performance, hanging processes, crashing processes etc) using Microsoft Excel, Word and PowerPoint through interop in a web service to print Office documents to PDF format. My solution was to switch to automating OpenOffice, which seems to be much more stable.

There is plenty documentation available here.

How to get started isn't so well documented though! Once you've installed OpenOffice you need to extract the following files from the CAB file that is extracted during installation:

cli_basetypes.dll
cli_cppuhelper.dll
cli_oootypes.dll
cli_uno.dll
cli_ure.dll
cli_uretypes.dll

Next step is to reference all of these assemblies in your Visual Studio project, and you can get started! To get you running quickly, here's some rough-and-ready code to convert an Excel document to a PDF file:

using uno.util;
using unoidl.com.sun.star.lang;
using unoidl.com.sun.star.uno;
using unoidl.com.sun.star.bridge;
using unoidl.com.sun.star.frame;
using unoidl.com.sun.star.text;
using unoidl.com.sun.star.beans;
using unoidl.com.sun.star.util;

...

string excelFile = "file:///C:/Documents/myFile.xls";
string pdfFile = "file:///C:/Documents/myFile.pdf";

// Start OpenOffce or get a reference to an existing session
XComponentContext localContext = Bootstrap.bootstrap();
XMultiServiceFactory multiServiceFactory = (XMultiServiceFactory)localContext.getServiceManager();
XComponentLoader componentLoader = (XComponentLoader)multiServiceFactory.createInstance("com.sun.star.frame.Desktop");

// Open file hidden in read-only mode
PropertyValue[] loadProps = new PropertyValue[2];
loadProps[0] = new PropertyValue();
loadProps[0].Name = "ReadOnly";
loadProps[0].Value = new uno.Any(true);
loadProps[1] = new PropertyValue();
loadProps[1].Name = "Hidden";
loadProps[1].Value = new uno.Any(true);

// Open the file
XComponent sourceDoc = componentLoader.loadComponentFromURL(excelFile, "_blank", 0, loadProps);

// Conversion parameters - overwrite existing file, use PDF exporter
PropertyValue[] conversionProperties = new PropertyValue[3];
conversionProperties[0] = new PropertyValue();
conversionProperties[0].Name = "Overwrite";
conversionProperties[0].Value = new uno.Any(true);
conversionProperties[1] = new PropertyValue();
conversionProperties[1].Name = "FilterName";
conversionProperties[1].Value = new uno.Any("calc_pdf_Export");

// Set PDF export parameters
PropertyValue[] filterData = new PropertyValue[3];

// JPEG compression quality 70
filterData[0] = new PropertyValue();
filterData[0].Name = "Quality";
filterData[0].Value = new uno.Any(70);
filterData[0].State = PropertyState.DIRECT_VALUE;

// Max image resolution 300dpi
filterData[1] = new PropertyValue();
filterData[1].Name = "ReduceImageResolution";
filterData[1].Value = new uno.Any(true);
filterData[1].State = PropertyState.DIRECT_VALUE;
filterData[2] = new PropertyValue();
filterData[2].Name = "MaxImageResolution";
filterData[2].Value = new uno.Any(300);
filterData[2].State = PropertyState.DIRECT_VALUE;

conversionProperties[2] = new PropertyValue();
conversionProperties[2].Name = "FilterData";
conversionProperties[2].Value = new uno.Any(filterData.GetType(), filterData);

// Save as PDF
XStorable xstorable = (XStorable)sourceDoc;
xstorable.storeToURL(pdfFile, conversionProperties);

// Close document
((XCloseable)sourceDoc).close(false);
((XCloseable)xstorable).close(false);

Answer 9

Hi,

There is a native .Net component to convert XLS to PDF without using MS Office.

It names Excel to PDF .Net.

The component created in C#, but it's not free.

This is a sample in C#:

SautinSoft.XlsToPdf x = new SautinSoft.XlsToPdf();
byte[] pdfBytes = null;
int result = x.ConvertFiletoBytes(@"c:\Sample.xls",ref pdfBytes);
if (result == 0)
{
    System.Console.WriteLine("Converted successfully!");
    WriteToFile(@"c:\Sample.pdf",pdfBytes);
}
else
{
    System.Console.WriteLine("Converting Error!");
}

Answer 10

I knew there is something in Aspose.Cells for the conversion (Xls2Pdf). I think one can try their new approach where we don't really need two products really for the conversion. The conversion speed is really great even if you have big spreadsheets using the new approach: http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/converting-to-pdf-files.html We had a few formatting problems but these are now sorted out. I think it's better to try some latest versions of the product for the conversion

Answer 11

I have converted the excel file to pdf file, but I need landscape pdf. Here is the code

private void ExportExcelToPDF() {

        string sourceFilePath = Server.MapPath("~/aaa.xlsx");
        string destinationFilePath = Server.MapPath("~/aaa.pdf");

        Microsoft.Office.Interop.Excel.Application myExcelApp;

        Microsoft.Office.Interop.Excel.Workbooks myExcelWorkbooks = null;

        Microsoft.Office.Interop.Excel.Workbook myExcelWorkbook = null;

        try
        {

            object misValue = System.Reflection.Missing.Value;

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

            myExcelApp.Visible = true;
            object varMissing = Type.Missing;

            myExcelWorkbooks = myExcelApp.Workbooks;


            //if file already exist then delete the file
            if (System.IO.File.Exists(destinationFilePath))
            {
                System.IO.File.Delete(destinationFilePath);
            }

            myExcelWorkbook = myExcelWorkbooks.Open(sourceFilePath, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);

            myExcelWorkbook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF,
                destinationFilePath, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard,
                varMissing, false, varMissing, varMissing, false, varMissing);

            myExcelWorkbooks.Close();

            myExcelApp.Quit();

        }
        catch
        {

        }
        finally
        {
            myExcelApp = null;
        }


    }