Joseph Michael Pesch
VP Programming

Excel to PDF Conversion Using Office Automation and/or Open Office

by 30. January 2012 10:23

One option to converting Excel documents to PDF is using Microsoft Office automation.  However, this requires installing the full application on the server that will be performing the automation (see this link for more details on that: ).  Another alternate solution is to switch to OpenOffice which only requires installing a few DLL's on the automation server.  Another option that I will review in the future is using the "ExcelLibrary" http://code.google.com/p/excellibrary/downloads/list

Microsoft Excel Sample Code:

// Start application
Application excel = new ApplicationClass();
// Turn off message boxes
excel.DisplayAlerts = false;

string sourceFilePath = @"C:\SourceFile.xls"; 
string targetFilePath = @"C:\TargetFile.pdf";  

// Open the file
// Important, do not double up on objects in COM
// Notice the Workbooks variable used to open the workbook
// DO NOT DO THIS: Workbook wkb = excel.Workbooks.Open(sourceFilePath, false, true);
Workbooks wkbs = excel.Workbooks;
Workbook wkb = wkbs.Open(sourceFilePath, false, true);

// Refresh data connections and do some formatting
wkb.EnableConnections();
wkb.RefreshAll();
Worksheet sht = wkb.Worksheets["Summary"] as Worksheet;
sht.get_Range("A1").EntireColumn.ColumnWidth = 21;
sht.get_Range("B1", "E1").EntireColumn.AutoFit();
sht.get_Range("A1").Select();

// Delete target file
File.Delete(targetFilePath);

// Export to PDF
sht.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF
, targetFilePath, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard
, true, false, Type.Missing, Type.Missing, false, Type.Missing);

// Clean up to release resources
// IMPORTANT: This cleanup sequence was required when running
// this inside a service that remains open and recycles on a schedule
// otherwise multiple EXCEL processes would be running and resources
// left open causing file access problems and memory leaks.
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(sht);
wkb.Close(false, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(wkb);
Marshal.ReleaseComObject(wkbs);
excel.Quit();
Marshal.ReleaseComObject(excel);
GC.Collect();
GC.WaitForPendingFinalizers();

Here is a good article on cleaning up Excel COM objects: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c-sharp.

OpenOffice Sample Code:

/*
You need the following DLL's from OpenOffice and reference them in your project:

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

//...
using System;
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 sourceFilePath = "file:///C:/SourceFile.xls"; 
string targetFilePath = "file:///C:/TargetFile.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");  

// Set the file open properties
PropertyValue[] openProperties = new PropertyValue[2]; 
// Hidden...
openProperties[1] = new PropertyValue(); 
openProperties[1].Name = "Hidden"; 
openProperties[1].Value = new uno.Any(true);  
// Read only...
openProperties[0] = new PropertyValue(); 
openProperties[0].Name = "ReadOnly"; 
openProperties[0].Value = new uno.Any(true); 
// Open the file 
XComponent sourceDocument = componentLoader.loadComponentFromURL(sourceFilePath, "_blank", 0, openProperties);  

// 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);  
// Export the PDF 
XStorable xstorable = (XStorable)sourceDoc; 
xstorable.storeToURL(pdfFile, conversionProperties);  

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

OpenOffice API documentation can be found here: http://www.openoffice.org/api/.  Here is a link to the artical I used for basis of OpenOffice version of code: http://stackoverflow.com/questions/769246/xls-to-pdf-conversion-inside-net).

Tags:

C# | Excel

Comments are closed