打开 xml Excel 在占位符中插入实际值

Question

我有一个单元格，其中包含Excel工作表中的占位符" $$value"，问题是我需要使用Open XML替换占位符的实际值并将其另存为单独的工作簿。

这是我尝试过的代码...它没有替换实际值，而且我也无法保存工作簿。 我需要解决这个问题。

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

默认情况下，

Excel 将字符串存储在全局（每个工作簿 1 个）SharedStringTablePart 中。因此，这是您需要定位的目标。但是，OpenXML 格式还允许在工作表部件内内内联文本。因此，一个完整的解决方案也需要在那里寻找。

下面是一个示例应用（带有一些内联注释）：

using DocumentFormat.OpenXml.Packaging;
using x = DocumentFormat.OpenXml.Spreadsheet;
class Program
{
private static readonly string placeHolder = "$$value";
static void Main()
{
    var templatePath = @"C:Temptemplate.xlsx";
    var resultPath = @"C:Tempresult.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

解决方案：

 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
        }