占位符的Open XML Excel中插入实际值 [英] open xml excel Insert actual value in the placeholder
问题描述
我有一个包含占位符在Excel工作表$$值的单元格,事情是我需要使用Open XML来替换占位符的实际值并将其保存为单独的工作簿。
下面是我想......它不是取代实际值,也是我无法保存工作簿的代码。我需要理清这个问题。
WorksheetPart worksheetPart =(WorksheetPart)myWorkbook.WorkbookPart.GetPartById(sheet.Id);
DocumentFormat.OpenXml.Spreadsheet.Worksheet工作表= worksheetPart.Worksheet;
串_txt1 =$$价值;
如果(_txt1.Contains($$))
{
worksheet.InnerText.Replace(_txt1,测试);
}
在默认情况下在全球(1每个工作簿),Excel存储字符串 SharedStringTablePart
。所以,这是你需要的目标之一。但是,处理OpenXML格式还允许WorksheetParts内嵌入式文本。 。因此,一个完整的解决方案需要看那里,以及
下面是一个示例应用程序(有一些内嵌注释):
使用DocumentFormat.OpenXml.Packaging;使用X = DocumentFormat.OpenXml.Spreadsheet
;
类节目
{
私人静态只读字符串占位=$$价值;
静态无效的主要()
{
VAR =的templatePath @C:\Temp\template.xlsx
VAR resultPath = @C:\Temp\result.xlsx
串replacementText =测试;使用(流xlsxStream =新的MemoryStream())
{
//从磁盘中读取
模板使用
(VAR FILESTREAM = File.OpenRead(的templatePath))
fileStream.CopyTo(xlsxStream);
//完成替换
ProcessTemplate(xlsxStream,replacementText);
//重置流开始
xlsxStream.Seek(0L,SeekOrigin.Begin);
//使用结果写回磁盘
(VAR resultFile = File.Create(resultPath))
xlsxStream.CopyTo(resultFile);
}
}
私有静态无效ProcessTemplate(流模板,串replacementText)
{
使用(VAR工作簿= SpreadsheetDocument.Open(模板,真,新OpenSettings {自动保存=真}))
{
//替换字符串共享
SharedStringTablePart sharedStringsPart = workbook.WorkbookPart.SharedStringTablePart;
IEnumerable的< x.Text> sharedStringTextElements = sharedStringsPart.SharedStringTable.Descendants< x.Text>();
DoReplace(sharedStringTextElements,replacementText);
//替换字符串直列
IEnumerable的< WorksheetPart> worksheetParts = workbook.GetPartsOfType< WorksheetPart>();
的foreach(在worksheetParts VAR工作表)
{
VAR allTextElements = worksheet.Worksheet.Descendants< x.Text>();
DoReplace(allTextElements,replacementText);
}
} //自动保存启用
}
私有静态无效DoReplace(IEnumerable的< x.Text> textElements,串replacementText)
{
的foreach(在textElements VAR文本)
{
如果(text.Text.Contains(占位符))
text.Text = text.Text.Replace(占位符,replacementText );
}
}
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");
}
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);
}
}
这篇关于占位符的Open XML Excel中插入实际值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!