Created
January 16, 2019 18:10
-
-
Save pradeepmurugan/b308bfdee67d144ab906e24dfd928c09 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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