Replace tag with text in a textbox inside the Worksheet with C++

Possible Usage Scenarios

Text boxes can have tags that can be replaced with text at runtime to configure them according to requirements. Tags are labels enclosed in angle brackets ‘<’ and ‘>’. Multiple tags can exist within a single textbox.

Sample Code

The following sample code replaces the tags TAG_1 and TAG_2 with the texts ‘ys’ and ‘1’, respectively. The sample file for testing the code can be downloaded from the link below:

sampleReplaceTagWithText.xlsx

#include <Aspose.Cells.h>
#include <regex>
#include <vector>
#include <string>

using namespace Aspose::Cells;

U16String Replace(const U16String& ustr, const std::u16string& from, const std::u16string& to, bool replaceAll) {
    std::u16string str(ustr.GetData());
    if (!from.empty())
    {
        size_t start_pos = 0;
        while ((start_pos = str.find(from, start_pos)) != std::u16string::npos) {
            str.replace(start_pos, from.length(), to);
            if (!replaceAll)
            {
                break;
            }
            start_pos += to.length();
        }
    }
    return U16String(str.c_str());
}

std::vector<U16String> Split(const U16String& str, const char16_t delim)
{
    std::u16string u16str(str.GetData());
    std::vector<U16String> result;
    
    size_t start = 0;
    size_t end = u16str.find(delim);
    
    while (end != std::u16string::npos)
    {
        std::u16string substr = u16str.substr(start, end - start);
        result.push_back(U16String(substr.c_str()));
        start = end + 1;
        end = u16str.find(delim, start);
    }
    
    // Add the last part
    std::u16string substr = u16str.substr(start);
    result.push_back(U16String(substr.c_str()));
    
    return result;
}

void sheetReplace(Workbook& workbook, U16String sFind, const U16String& sReplace)
{
    U16String finding = sFind;

    WorksheetCollection sheets = workbook.GetWorksheets();
    int sheetCount = sheets.GetCount();

    // Replace text in cells and headers/footers
    for (int i = 0; i < sheetCount; ++i)
    {
        Worksheet sheet = sheets.Get(i);
        sheet.Replace(finding, sReplace);

        PageSetup pageSetup = sheet.GetPageSetup();
        for (int j = 0; j < 3; ++j)
        {
            U16String header = pageSetup.GetHeader(j);
            if (!header.IsEmpty())
            {
                header = Replace(header, std::u16string(finding.GetData()), std::u16string(sReplace.GetData()), true);
                pageSetup.SetHeader(j, header);
            }

            U16String footer = pageSetup.GetFooter(j);
            if (!footer.IsEmpty())
            {
                footer = Replace(footer, std::u16string(finding.GetData()), std::u16string(sReplace.GetData()), true);
                pageSetup.SetFooter(j, footer);
            }
        }
    }

    // Replace text in textboxes with HTML entities
    for (int i = 0; i < sheetCount; ++i)
    {
        Worksheet sheet = sheets.Get(i);

        U16String modifiedFind = sFind;
        modifiedFind = Replace(modifiedFind, std::u16string(u"<"), std::u16string(u"&lt;"), true);
        modifiedFind = Replace(modifiedFind, std::u16string(u">"), std::u16string(u"&gt;"), true);

        TextBoxCollection textBoxes = sheet.GetTextBoxes();
        int textBoxCount = textBoxes.GetCount();
        for (int j = 0; j < textBoxCount; ++j)
        {
            TextBox textBox = textBoxes.Get(j);
            U16String htmlText = textBox.GetHtmlText();
            int32_t findPos = htmlText.IndexOf(modifiedFind);
            if (!htmlText.IsEmpty() && findPos >= 0)
            {
                htmlText = Replace(htmlText, std::u16string(modifiedFind.GetData()), std::u16string(sReplace.GetData()), true);
                textBox.SetHtmlText(htmlText);
            }
        }
    }
}

int main() {
    Aspose::Cells::Startup();

    Workbook wb(U16String(u"G:\\sampleReplaceTagWithText.xlsx"));

    U16String tag(u"TAG_2$TAG_1");
    U16String replace(u"1$ys");

    std::vector<U16String> tagParts = Split(tag, u'$');
    std::vector<U16String> replaceParts = Split(replace, u'$');
    
    for (size_t i = 0; i < tagParts.size() && i < replaceParts.size(); i++)
    {
        U16String findStr = U16String(u"<") + tagParts[i] + U16String(u">");
        sheetReplace(wb, findStr, replaceParts[i]);
    }

    PdfSaveOptions opts;
    wb.Save("outputReplaceTagWithText.pdf", opts);

    Aspose::Cells::Cleanup();
    return 0;
}