SQL Server Requires Chilkat v11.7.0+

Fill a PDF Template: Replace {{placeholders}} with Values

See more PDF Text Replacement Examples

Demonstrates the TextReplace method on an invoice template whose values are written as placeholders such as {{customer}} and {{total}}. The "placeholders" object names each placeholder and the text to put in; every occurrence on the page is replaced, including a placeholder inside a sentence or inside a label such as "Tax ({{taxRate}}):". The text is redrawn with the font the template used, so the result looks like the template, and a value that is right-aligned in the template stays right-aligned. The changes are written by WriteFile. SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @sTmp1 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  This example requires the Chilkat API to have been previously unlocked.
    --  See Global Unlock Sample for sample code.

    --  The PDF used in this example is available at invoice_template.pdf

    DECLARE @pdf int
    EXEC @hr = sp_OACreate 'Chilkat.Pdf', @pdf OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @pdf, 'LoadFile', @success OUT, 'qa_data/pdf/invoice_template.pdf'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        RETURN
      END

    --  The template has these placeholders in its page text:
    --    {{customer}} (twice: "Bill To:" and the closing sentence), {{date}}, {{po}},
    --    two item rows: {{qty1}} {{desc1}} {{price1}} {{amount1}} and {{qty2}} {{desc2}} {{price2}} {{amount2}},
    --    {{subtotal}}, "Tax ({{taxRate}}):" with {{tax}}, and {{total}}.
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'placeholders.customer', 'Acme Corp'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'placeholders.date', '2026-10-18'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'placeholders.po', 'PO-88213'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'placeholders.qty1', '2'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'placeholders.desc1', 'Cordless drill, 18 V'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'placeholders.price1', '$460.00'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'placeholders.amount1', '$920.00'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'placeholders.qty2', '6'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'placeholders.desc2', 'Galvanized hinge, 4 inch'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'placeholders.price2', '$12.50'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'placeholders.amount2', '$75.00'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'placeholders.subtotal', '$995.00'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'placeholders.taxRate', '7.35%'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'placeholders.tax', '$73.13'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'placeholders.total', '$1,068.13'

    --  "apply" makes every replacement, or none: if any value cannot be drawn with the
    --  template's font, the method returns false, the PDF is unchanged, and jsonOut says why.
    DECLARE @jsonOut int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonOut OUT

    EXEC sp_OAMethod @pdf, 'TextReplace', @success OUT, 'apply', @json, @jsonOut
    IF @success = 0
      BEGIN

        EXEC sp_OAMethod @jsonOut, 'Emit', @sTmp0 OUT
        PRINT 'Not applied: ' + @sTmp0
        IF @success = 0
          BEGIN
            EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @pdf
            EXEC @hr = sp_OADestroy @json
            EXEC @hr = sp_OADestroy @jsonOut
            RETURN
          END
      END

    --  One entry per placeholder: the pages it was found on and how many times it was replaced.
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @jsonOut, 'SizeOfArray', @count_i OUT, 'replacements'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jsonOut, 'I', @i
        EXEC sp_OAMethod @jsonOut, 'StringOf', @sTmp0 OUT, 'replacements[i].field'

        EXEC sp_OAMethod @jsonOut, 'IntOf', @iTmp0 OUT, 'replacements[i].count'

        EXEC sp_OAMethod @jsonOut, 'StringOf', @sTmp1 OUT, 'replacements[i].pages'
        PRINT @sTmp0 + ': ' + @iTmp0 + ' on page(s) ' + @sTmp1
        SELECT @i = @i + 1
      END

    --  The replacements stay in memory until the PDF is written.
    EXEC sp_OAMethod @pdf, 'WriteFile', @success OUT, 'qa_output/invoice_filled.pdf'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jsonOut
        RETURN
      END

    PRINT 'Wrote qa_output/invoice_filled.pdf'

    --  Sample output:

    --  customer: 2 on page(s) 1
    --  date: 1 on page(s) 1
    --  po: 1 on page(s) 1
    --  qty1: 1 on page(s) 1
    --  desc1: 1 on page(s) 1
    --  price1: 1 on page(s) 1
    --  amount1: 1 on page(s) 1
    --  qty2: 1 on page(s) 1
    --  desc2: 1 on page(s) 1
    --  price2: 1 on page(s) 1
    --  amount2: 1 on page(s) 1
    --  subtotal: 1 on page(s) 1
    --  taxRate: 1 on page(s) 1
    --  tax: 1 on page(s) 1
    --  total: 1 on page(s) 1
    --  Wrote qa_output/invoice_filled.pdf

    EXEC @hr = sp_OADestroy @pdf
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @jsonOut


END
GO