Is there a way to create and reference an invisible mark when saved to PDF

GemBox.Spreadsheet
1

I am saving my Spreadsheet to PDF. Later the saved PDF is loaded to Gembox.PDF for further processing.
I would like to mark a specific place/cell/coordinate on the spreadsheet and reference it at a later point in time when the document is loaded I will paint something using Gembox.PDF at that specific place / coordinate

Is there a way I can achieve that. I tried finding a way to convert from Excel width-height to PDF X-Y but it is unreliable as when automatic page break happen during rendering-to-pdf, the excel renderer seems to add some space for un-breakable area

Something like this

        resultExcel.Save(resultExcelStream, GemBox.Spreadsheet.SaveOptions.PdfDefault);
        using var document = PdfDocument.Load(resultExcelStream);
        var elements = document.Pages[0].Content.Elements;
        elements.FirstOrDefault(e => e.Id == "SomeHiddenIdFromExcelFile")

Thanks!

2

Hi,

One way that comes to my mind is to add a small white text in a specific cell, or in a textbox on a specific location. Such a text would be invisible when viewing the spreadsheet, but it could be selected (if that matters).
Then in the output PDF, you could look for the PdfTextContent element which has that text, and use its PdfTextContent.Bounds to retrieve the position.

Alternatively, you could add a picture with some metadata information. Such a picture will be exported as a PdfImageContent element with PdfXmlMetadataStream that will contain that same information.

So for this approach, you’ll need to use the current latest version of GemBox.Spreadsheet:
https://www.gemboxsoftware.com/spreadsheet/nightlybuilds/GBS49v1346.zip
Or the current latest NuGet package:
Install-Package GemBox.Spreadsheet -Version 49.0.1346-hotfix

And add a picture with metadata like this:

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

var picture = worksheet.Pictures.Add("image.png", "B2");
picture.Metadata.Title = "My Title";

workbook.Save("output.pdf");

This will result in having a PdfImage with the following metadata:

<?xpacket begin="" id="W5M0MpCehiHzreSzNTczkc9d"?>
<x:xmpmeta xmlns:x="adobe:ns:meta/" x:xmptk="3.1-701">
    <rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#">
        <rdf:Description rdf:about=""
            xmlns:dc="http://purl.org/dc/elements/1.1/">
            <dc:title>
                <rdf:Alt>
                    <rdf:li xml:lang="x-default">My Title</rdf:li>
                </rdf:Alt>
            </dc:title>
        </rdf:Description>
    </rdf:RDF>
</x:xmpmeta>
<?xpacket end="w"?>

So now with GemBox.Pdf, you can find the targeted PdfImageContent element like this:

using (var document = PdfDocument.Load("output.pdf"))
{
    foreach (var page in document.Pages)
    {
        var enumerator = page.Content.Elements.All(page.Transform).GetEnumerator();
        while (enumerator.MoveNext())
        {
            if (enumerator.Current.ElementType == PdfContentElementType.Image)
            {
                var imageElement = (PdfImageContent)enumerator.Current;
                string metadata = imageElement.Image.Metadata?.Value;

                if (metadata == null || !metadata.Contains("My Title"))
                    continue;

                var bounds = imageElement.Bounds;
                enumerator.Transform.Transform(ref bounds);

                Console.WriteLine($"From: ({bounds.Left:#.##},{bounds.Bottom:#.##}) To: ({bounds.Right:#.##},{bounds.Top:#.##})");
                return;
            }
        }
    }
}

I hope this helps.

Regards,
Mario

1 Like