Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>by default Excel stores strings in the global (1 per workbook) <code>SharedStringTablePart</code>. 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.</p> <p>Here's a sample app (with some inline comments):</p> <pre><code>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&lt;x.Text&gt; sharedStringTextElements = sharedStringsPart.SharedStringTable.Descendants&lt;x.Text&gt;(); DoReplace(sharedStringTextElements, replacementText); // Replace inline strings IEnumerable&lt;WorksheetPart&gt; worksheetParts = workbook.GetPartsOfType&lt;WorksheetPart&gt;(); foreach (var worksheet in worksheetParts) { var allTextElements = worksheet.Worksheet.Descendants&lt;x.Text&gt;(); DoReplace(allTextElements, replacementText); } } // AutoSave enabled } private static void DoReplace(IEnumerable&lt;x.Text&gt; textElements, string replacementText) { foreach (var text in textElements) { if (text.Text.Contains(placeHolder)) text.Text = text.Text.Replace(placeHolder, replacementText); } } </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POopen xml excel Insert actual value in the placeholder
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USmthierba
    1. USmthierba
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POopen xml excel Insert actual value in the placeholder
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHi ServiceGuy Thanks for your reply...the issue am facing from your code is i cant able to save using "new OpenSettings {AutoSave = true }" its getting reference issue for "OpenSetting"..is there any other way to save the workbook as another workbook leaving this as template....
      singulars
      1. PO
      1. USkart
    2. CO@ kart: I updated the sample code to meet your requirements (load template and save to new file). Also, make sure to use the RTM of OpenXML SDK 2.0: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c6e744e5-36e9-45f5-8d8c-331df206e0d0&displaylang=en
      singulars
      1. PO
      1. USmthierba
    3. CO@ServiceGuy:Thanks soo much for your guidence...while debuging i can able to find the text replacement in DoReplace function but those replaced text is not showing in the newly created excel file. for me i can't able to add OpenSetting "SpreadsheetDocument.Open(template, true, new OpenSettings { AutoSave = true })" third parameter is not allowing in the "SpreadsheetDocument.Open()" method its allowing only two parameters...still i can't able to replace the actual data in newly created file..its creating same as template file.
      singulars
      1. PO
      1. USkart
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload