How to create a PDF file from an Excel Sheet (using C#)

The task is to create a PDF file from each sheet in each Excel file in a folder using C#. Here is one way to do it.

Import the following libraries.

using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Application = Microsoft.Office.Interop.Excel.Application;

Access the Excel files as follows:

var directoryinfo = new DirectoryInfo(folderPath);
var files = from f in directoryinfo.GetFiles() where f.Extension.ToLower().Contains("xls") || f.Extension.ToLower().Contains("xlsx") select f;
var fileInfosList = files.ToList();
Workbook workBook = null;
object misValue = System.Reflection.Missing.Value;
Application app = new Application();

Loop through each file, then loop through each sheet in each file.

foreach (FileInfo fileInfo in fileInfosList)
{
 try
 {
  workBook = app.Workbooks.Open(fileInfo.FullName, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
  Sheets workSheets = workBook.Worksheets;
  string fileName = Path.GetFileNameWithoutExtension(fileInfo.FullName);
  string folderName = fileInfo.DirectoryName;

  foreach (Worksheet workSheet in workSheets)
  {
   workSheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
   workSheet.PageSetup.FitToPagesWide = 1;
   workSheet.PageSetup.Zoom = false;
   workSheet.PageSetup.LeftMargin = 10;
   workSheet.PageSetup.RightMargin = 10;
   workSheet.PageSetup.TopMargin = 10;
   workSheet.PageSetup.BottomMargin = 10;

   string sheetname = workSheet.Name;
if (folderName != null)
{
    string destinationPath = Path.Combine(folderName, fileName + "_" + sheetname + ".pdf");

    if (!File.Exists(destinationPath))
{
     workSheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, destinationPath);
    }
   }
 }
catch (Exception ex)
{
// Log the exception
workBook?.Close(false, misValue, misValue);
app.Quit();
}
app.Quit();
}

That should do it!

The magic method is “ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, destinationPath)” that creates the PDF file.

Before running the code, you will have to “Autofit Column Width” in Excel for each sheet to make sure the content is not truncated in the PDF.