OfficeWriter 10.0 introduces the ability to save a Workbook, Worksheet, or Area to a PDF document. This makes it possible to produce a searchable, vector-format rendering of your spreadsheet.

Using the Imaging Extension DLL

The PDF functionality is included in the rendering extensions DLL (SoftArtisans.OfficeWriter.ExcelWriter.Imaging.dll). The first thing you will need to do is include this DLL as a reference in your project in Visual Studio. You will also need to tell the compiler to use the imaging namespace in your source file. This can be accomplished by adding a using statement to the top of the file where you want to save a PDF document:

using SoftArtisans.OfficeWriter.ExcelWriter.Imaging;

Setting up your workbook

In order to save an Excel workbook to a PDF document, you first need a workbook with contents in it. For this example, let’s create a simple workbook with three worksheets:

ExcelApplication xla = new ExcelApplication();
Workbook WB = xla.Create(ExcelApplication.FileFormat.Xlsx);
Worksheet ws0 = WB[0];
Worksheet ws1 = WB.Worksheets.CreateWorksheet("Sheet2");
Worksheet ws2 = WB.Worksheets.CreateWorksheet("Sheet3");

ws0[0, 0].Value = “Sheet 1, Cell A1”;
ws1[0, 0].Value = “Sheet 2, Cell A1”;
ws2[0, 0].Value = “Sheet 3, Cell A1”;

For this example, we are just exporting some cells with text in them. However, the rendering extensions support more dynamic content as well, such as cell formatting, charts, images, comments, or conditional formats. We could also use a workbook that we opened from a file, that already had contents and formatting applied.

Saving a PDF document

There are three ways to save a PDF document: through the workbook, through a worksheet, or through a specific area. Specific PDF rendering options can be specified by setting a worksheet’s PageSetup properties; this will be covered in a later tutorial. If you have not set any of the worksheet’s PageSetup properties, then default settings will be used.

You can save multiple PDF files from one workbook. First, let’s save multiple worksheets to a single PDF document. This can be achieved by using the Workbook.SavePdf method. The first parameter is a Boolean; if this is set to true, then only selected worksheets will be saved. Otherwise all visible worksheets will be saved.

For this example, lets save the first and the third worksheet to a single PDF document. Continuing from the code above, we can achieve this with the following two lines of code:

WB.Worksheets.Select(new object[]{0, 2});
WB.SavePdf(true, “MultipleWorksheets.pdf”);

We can also save the remaining worksheet to a separate PDF file containing only the contents of that worksheet:

ws1.SavePdf(“sheet2.pdf”);

This allows to either generate multiple PDF documents, each containing one worksheet, or a single PDF document containing multiple sheets.

Calls to SavePdf must be made before saving a workbook to an xlsx/xlsm file. Once you have exported all of the PDF documents that you want, you can then also save the workbook to an Excel file as you normally would:

xla.Save(WB, “workbook.xlsx”);

Putting it all together

Here’s what the final code looks like:

using SoftArtisans.OfficeWriter.ExcelWriter;
using SoftArtisans.OfficeWriter.ExcelWriter.Imaging;

public class SampleProgram
{
public static void Main(string[] args)
{
ExcelApplication xla = new ExcelApplication();
Workbook WB = xla.Create(ExcelApplication.FileFormat.Xlsx);

// Set up the workbook with some content
Worksheet ws0 = WB[0];
Worksheet ws1 = WB.Worksheets.CreateWorksheet(“Sheet2”);
Worksheet ws2 = WB.Worksheets.CreateWorksheet(“Sheet3”);
ws0[0, 0].Value = “Sheet 1, Cell A1”;
ws1[0, 0].Value = “Sheet 2, Cell A1”;
ws2[0, 0].Value = “Sheet 3, Cell A1”;

// Select two worksheets and save them to a single PDF document
WB.Worksheets.Select(new object[]{0, 2});

// The ‘true’ argument tells the rendering method to only save worksheets
// that are currently selected. If it were ‘false’, then all worksheets
// would be saved to the PDF.
WB.SavePdf(true, “MultipleWorksheets.pdf”);

// Save one worksheet to a separate PDF
ws1.SavePdf(“sheet2.pdf”);

// Finally, save the workbook to an Excel file in case we need to edit it later
xla.Save(WB, “workbook.xlsx”);
}
}

Follow