Skip to content

Instantly share code, notes, and snippets.

@pradeepmurugan
Created January 16, 2019 18:10
Show Gist options
  • Save pradeepmurugan/b308bfdee67d144ab906e24dfd928c09 to your computer and use it in GitHub Desktop.
Save pradeepmurugan/b308bfdee67d144ab906e24dfd928c09 to your computer and use it in GitHub Desktop.
using DocumentFormat.OpenXml.Packaging;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using x = DocumentFormat.OpenXml.Spreadsheet;
class Program
{
private static readonly string placeHolder = "NamePlaceholder";
static void Main()
{
var templatePath = @"C:\Users\PradeepMurugan\Desktop\Invoices\InvoiceTemplateGst.xlsx";
var resultPath = @"C:\Users\PradeepMurugan\Desktop\Invoices\test.xlsx";
string replacementText = "test";
Dictionary<string, string> keyValuePairs = new Dictionary<string, string>()
{
{"NamePlaceholder","Velavan Electricals" },
{"AddressPlaceholder", "Karur" },
{"GstinPlaceholder","33251455654" },
{"InvoiceNumberPlaceholder", "1001" },
{"DatePlaceholder","16-01-2019" }
};
using (Stream xlsxStream = new MemoryStream())
{
// Read template from disk
using (var fileStream = File.OpenRead(templatePath))
fileStream.CopyTo(xlsxStream);
// Do replacements
ProcessTemplate(xlsxStream, keyValuePairs);
// 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, Dictionary<string,string> keyValuePairs)
{
using (var workbook = SpreadsheetDocument.Open(template, true))
{
// Replace shared strings
SharedStringTablePart sharedStringsPart = workbook.WorkbookPart.SharedStringTablePart;
IEnumerable<x.Text> sharedStringTextElements = sharedStringsPart.SharedStringTable.Descendants<x.Text>();
DoReplace(sharedStringTextElements, keyValuePairs);
// Replace inline strings
IEnumerable<WorksheetPart> worksheetParts = workbook.GetPartsOfType<WorksheetPart>();
foreach (var worksheet in worksheetParts)
{
var allTextElements = worksheet.Worksheet.Descendants<x.Text>();
DoReplace(allTextElements, keyValuePairs);
}
} // AutoSave enabled
}
private static void DoReplace(IEnumerable<x.Text> textElements, Dictionary<string, string> keyValuePairs)
{
//textElements.FirstOrDefault(car => car.Text == "Blue");
foreach (var key in keyValuePairs)
{
var item = textElements.Where(x => x.Text == key.Key).FirstOrDefault();
if (item != null)
{
textElements.Select(s => s.Text.Equals(key.Key) ? key.Value : s.Text);
}
//text.Text = text.Text.Replace(placeHolder, replacementText);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment