CODEWITHSUNDEEP
c#excelpdfgembox-spreadsheet
bellpatricia
bellpatricia

Reputation: 49

Convert Excel to PDF with sheet names as bookmarks

I want to save all sheets from my Excel file to a PDF. Currently, I'm using GemBox.Spreadsheet for this:

var workbook = ExcelFile.Load("file.xlsx");
var options = new PdfSaveOptions() { SelectionType = SelectionType.EntireFile };
workbook.Save("file.pdf", options);

Now I want to have the sheet names (each ExcelWorksheet.Name) shown as bookmarks in that output PDF. How can I do that?

Upvotes: 2

Views: 1275

Answers (1)

Mario Z
Mario Z

Reputation: 4381

Currently, that's not possible with just GemBox.Spreadsheet, but you can do that together with GemBox.Pdf.

If each sheet is exported as a single page, then you can do this:

var workbook = ExcelFile.Load("input.xlsx");

// Set each worksheet to be exported as a single page.
foreach (var worksheet in workbook.Worksheets)
{
    worksheet.PrintOptions.FitWorksheetWidthToPages = 1;
    worksheet.PrintOptions.FitWorksheetHeightToPages = 1;
}

// Save the whole workbook as a PDF file.
var options = new GemBox.Spreadsheet.PdfSaveOptions();
options.SelectionType = SelectionType.EntireFile;
var stream = new MemoryStream();
workbook.Save(stream, options);

using (var document = PdfDocument.Load(stream))
{
    // Add bookmark (or outline) for each PDF page (or Excel sheet).
    for (int i = 0; i < document.Pages.Count; i++)
    {
        var page = document.Pages[i];
        var sheet = workbook.Worksheets[i];
        document.Outlines.AddLast(sheet.Name).SetDestination(page, PdfDestinationViewType.FitPage);
    }

    document.Save("output.pdf");
}

If each sheet can be exported as multiple pages, then you can do this:

var workbook = ExcelFile.Load("input.xlsx");
var options = new GemBox.Spreadsheet.PdfSaveOptions();

using (var document = new PdfDocument())
{
    foreach (var worksheet in workbook.Worksheets)
    {
        // Save each worksheet as a PDF file.
        workbook.Worksheets.ActiveWorksheet = worksheet;
        var stream = new MemoryStream();
        workbook.Save(stream, options);

        using (var temp = PdfDocument.Load(stream))
        {
            // Copy PDF pages to destination PDF and add a bookmark.
            var firstPage = true;
            foreach (var tempPage in temp.Pages)
            {
                var page = document.Pages.AddClone(tempPage);
                if (firstPage)
                {
                    document.Outlines.AddLast(worksheet.Name).SetDestination(page, PdfDestinationViewType.FitPage);
                    firstPage = false;
                }
            }
        }
    }

    document.Save("output.pdf");
}

Upvotes: 4

Related Questions