open xml excel Insert actual value in the placeholder

Question

I have a cell that contains the placeholder "$$value" in the Excel sheet, the thing is that I need to replace the placeholder's actual value using Open XML and save it as separate workbook.

Here is the code that I tried...it is not replacing the actual value and also I'm unable to save the workbook. I need to sort out this issue.

WorksheetPart worksheetPart = (WorksheetPart)myWorkbook.WorkbookPart.GetPartById(sheet.Id);

DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = worksheetPart.Worksheet;

string _txt1 = "$$value";

if (_txt1.Contains("$$"))

{

     worksheet.InnerText.Replace(_txt1, "test");

}

Answer 1

by default Excel stores strings in the global (1 per workbook) SharedStringTablePart. So, this is the one you need to target. However, the OpenXML format also allows inline text inside the WorksheetParts. hence, a complete solution needs to look there as well.

Here's a sample app (with some inline comments):

using DocumentFormat.OpenXml.Packaging;
using x = DocumentFormat.OpenXml.Spreadsheet;

class Program
{
private static readonly string placeHolder = "$$value";

static void Main()
{
    var templatePath = @"C:\Temp\template.xlsx";
    var resultPath = @"C:\Temp\result.xlsx";
    string replacementText = "test";

    using (Stream xlsxStream = new MemoryStream())
    {
        // Read template from disk
        using (var fileStream = File.OpenRead(templatePath)) 
            fileStream.CopyTo(xlsxStream);

        // Do replacements
        ProcessTemplate(xlsxStream, replacementText);

        // Reset stream to beginning
        xlsxStream.Seek(0L, SeekOrigin.Begin);

        // Write results back to disk
        using (var resultFile = File.Create(resultPath))
            xlsxStream.CopyTo(resultFile);
    }
}

private static void ProcessTemplate(Stream template, string replacementText)
{
    using (var workbook = SpreadsheetDocument.Open(template, true, new OpenSettings { AutoSave = true }))
    {
        // Replace shared strings
        SharedStringTablePart sharedStringsPart = workbook.WorkbookPart.SharedStringTablePart;
        IEnumerable<x.Text> sharedStringTextElements = sharedStringsPart.SharedStringTable.Descendants<x.Text>();
        DoReplace(sharedStringTextElements, replacementText);

        // Replace inline strings
        IEnumerable<WorksheetPart> worksheetParts = workbook.GetPartsOfType<WorksheetPart>();
        foreach (var worksheet in worksheetParts)
        {
            var allTextElements = worksheet.Worksheet.Descendants<x.Text>();
            DoReplace(allTextElements, replacementText);
        }

    } // AutoSave enabled
}

private static void DoReplace(IEnumerable<x.Text> textElements, string replacementText)
{
    foreach (var text in textElements)
    {
        if (text.Text.Contains(placeHolder))
            text.Text = text.Text.Replace(placeHolder, replacementText);
    }
}

Answer 2

Solution:

 private static void ProcessTemplate(Stream template, Dictionary<string,string> toReplace)
        {
            using (var workbook = SpreadsheetDocument.Open(template, true, new OpenSettings { AutoSave = true }))
            {

                workbook.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
                workbook.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;

                //Replace  SheetNames
                foreach (Sheet sheet in workbook.WorkbookPart.Workbook.Sheets)
                    foreach (var key in toReplace.Keys)
                        sheet.Name.Value = sheet.Name.Value.Replace(key, toReplace[key]);

                foreach (WorksheetPart wsheetpart in workbook.WorkbookPart.WorksheetParts)
                    foreach (SheetData sheetd in wsheetpart.Worksheet.Descendants<x.SheetData>())
                        foreach (Row r in wsheetpart.Worksheet.Descendants<x.Row>())
                            foreach (Cell c in r.Descendants<x.Cell>())
                                if (c.CellFormula != null)
                                {
                                    foreach (var key in toReplace.Keys)
                                        c.CellFormula.Text = c.CellFormula.Text.Replace(key, toReplace[key]);
                                }

                                // Replace shared strings
                                SharedStringTablePart sharedStringsPart = workbook.WorkbookPart.SharedStringTablePart;

                IEnumerable<x.Text> sharedStringTextElements = sharedStringsPart.SharedStringTable.Descendants<x.Text>();
                for(int i =0;i<toReplace.Keys.Count; i++)
                    DoReplace(sharedStringTextElements, toReplace);

                IEnumerable<x.Formula> sharedStringTextElementsF = sharedStringsPart.SharedStringTable.Descendants<x.Formula>();
                for (int i = 0; i < toReplace.Keys.Count; i++)
                    DoReplaceFormula(sharedStringTextElementsF, toReplace);

                // Replace inline strings
                IEnumerable<WorksheetPart> worksheetParts = workbook.GetPartsOfType<WorksheetPart>();
                foreach (var worksheet in worksheetParts)
                {

                    var allTextElements = worksheet.Worksheet.Descendants<x.Text>();
                    DoReplace(allTextElements, toReplace);

                    var allTextElements2 = worksheet.Worksheet.Descendants<x.Formula>();
                    DoReplaceFormula(allTextElements2, toReplace);

                }

            } // AutoSave enabled
        }