Reading excel (.xls) template, replacing variables with textbox values and saving excel file in C#

Question

I noticed while preparing to my business scale, I realized too long time was spent to this work. An excel file is sent to us from the council via Internet, we calculate our works and send them using same way. So I want to automate preparing business scale.

For this, I created a windows forms application. My application is a desktop application (WinForms). All datas are written into textboxes. When we click OK button, programme must read "template.xls" and replace all variables which shows brackets "{}" with textboxes values. But I don't know how reading .xls file, replacing variables with values and saving template as a finished document. I have added screenshot of excel file below. enter image description here

Could you help me how can I do this?
Kind regards.

If your text just needs to be inserted in specific cells and not interact with the sheet in terms of formulae or macros, I would suggest building the sheet using a template rather than trying to update an existing one. — Gayot Fow, Aug 17 '13 at 09:40
Thanks for comments but I am beginner in C#. So I dont know how I do. — , Aug 17 '13 at 09:51
I don't want to wait 2 days for bounty. The project was choked. — , Aug 17 '13 at 17:27
If {name} is simple text, you can try Reading whole file as string & call string.Replace and save text as xls file. But you will have to use new xlsx format which is zipped file with text documents. save your template as xlst and rename it to .zip & see its content. Then you can use SharpZip library to extract text, replace & create zip & save as xlsx — Akash Kava, Aug 25 '13 at 07:55

score 5 · Answer 1 · answered Aug 19 '13 at 10:09

IF your application is a desktop application (WinForms, WPF) then you can use Interop. It requires Excel locally installed. BUT BEWARE: Interop is not supported in sever-scenarios by MS.

If Excel is not locally installed and/or this is something you want to do server-side (like ASP.NET or similar) there are many options to read/edit/create Excel files without Interop/installing Excel on the server:

MS provides the free OpenXML SDK V 2.0 - see http://msdn.microsoft.com/en-us/library/bb448854%28office.14%29.aspx (XLSX only)

This can read+write MS Office files (including Excel).

Another free option see http://www.codeproject.com/KB/office/OpenXML.aspx (XLSX only)

IF you need more like handling older Excel versions (like XLS, not only XLSX), rendering, creating PDFs, formulas etc. then there are different free and commercial libraries like ClosedXML (free, XLSX only), EPPlus (free, XLSX only), Aspose.Cells, SpreadsheetGear, LibXL and Flexcel etc.

score 1 · Answer 2 · answered Aug 19 '13 at 13:07

EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx).

Usage examples can be found here and in the project site.

It is as simple as

ws.Cells["B1"].Value = "My Cell Value";

You can read from the excel file (the cells that have your bracket fields {*}), replace them as needed and save.

To check if the cell has a field you can use regular expression like Regex.IsMatch(cellValue, "\{.*\}")

Its seamless, since it uses the Open Office format, then you don't need Excel to be installed.

score 0 · Answer 3 · answered Aug 19 '13 at 12:52

If you choose, on the other hand, to make this a web-base app or even a desktop PHP app, I can highly recommend the PHP Excel library, While producing spreadsheets takes quite a bit of time, reading the data from a sheet is a few simple lines of code and you're presented with an array that holds values of all the active cells. It can handle sheets with multiple pages and even formulaic data.

alfoks · Answer 4 · 2013-08-21T21:54:51.923

My company uses NPOI. Can be found here:

http://npoi.codeplex.com/

It does not require excel to be installed and can work with .net 2.0 and above. It's documentation is not complete, but you can search it's java equivalent (it has the same classes and methods) here:

http://poi.apache.org/

If you know that you'll have excel installed you can try using oledb

http://www.codeproject.com/Articles/8500/Reading-and-Writing-Excel-using-OLEDB

but I think it works only for xls not xlsx.

score 0 · Answer 5 · answered Aug 23 '13 at 07:48

For reading xls document you can use this code. After

1.you can add all of value at string arraylist.
2. you can use regex. for example

if(a.include("{"))

replaceall("{","") like that.

I give you algorithm. Umarım yardımcı olmuştur dostum :)

// Get the file we are going to process
var existingFile = new FileInfo(filePath);
// Open and read the XlSX file.
using (var package = new ExcelPackage(existingFile))
{
    // Get the work book in the file
    ExcelWorkbook workBook = package.Workbook;
    if (workBook != null)
    {
        if (workBook.Worksheets.Count > 0)
        {
            // Get the first worksheet
            ExcelWorksheet currentWorksheet = workBook.Worksheets.First();

            // read some data
            object col1Header = currentWorksheet.Cells[0, 1].Value;

score 0 · Answer 6 · answered Aug 24 '13 at 17:11

Sorry to hear that, but you don't need to attack it using C#. VB Script would have been a better fit. But either way, I would have maintained a separate mapping (as in source fields and excel cell location) than filling the excel cells with tags. It's my pet peeve to preserve the original "template".

score 0 · Answer 7 · edited May 23 '17 at 12:23

For this solution, I'll assume you'll run it on a PC with Excel installed.

First, add reference to library Microsoft.Office.Interop.Excel. Then, use its namespace:

using Excel = Microsoft.Office.Interop.Excel;

Be warned that because it's COM Interop, you need to retain each and every reference to objects from this library to properly release them from memory with Marshal. This is golden rule to use COM Interop objects from .NET projects. For more info, read it here: How do I properly clean up Excel interop objects? So, this is another important import:

using System.Runtime.InteropServices;

I don't know how you're filling this form, but I'll suppose there is one field for each data between brackets: {CITY}, {NUM_I1}, and so on.

So, in your form submission, you could create a dictionary out of your inputs:

Dictionary<string, string> replacing = new Dictionary<string, string>();
replacing.Add("{CITY}",txtCity.Text);
...

And then, for filling the Workbook:

//Opening Excel Application with desirable template Workbook 
//and instantiating the desirable Worksheet and Range
Excel.Application xlApplication = new Excel.Application();
Excel.Workbooks xlWorkbooks = xlApplication.Workbooks;
Excel.Workbook xlWorkbook = xlWorkbooks.Open(templateFilename, ReadOnly: true);
Excel.Sheets xlSheets = xlWorkbook.Sheets;
Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlSheets[sheetNameOrIndex];
Excel.xlFirstCell = xlWorksheet.Cells[firstRow, firstCol];
Excel.xlLastCell = xlWorksheet.Cells[lastRow, lastCol];
Excel.Range xlRange = xlWorksheet.Cells[xlFirstCell, xlLastCell];

//all of the replacing
foreach (string key in replacing.Keys)
    xlRange.Replace(key, replacing[key]);

//saving
xlWorkbook.SaveAs(newFilename);

//important part: releasing references
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlFirstCell);
Marshal.ReleaseComObject(xlLastCell);
Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlSheets);
xlWorkbook.Close(SaveChanges: false);
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlWorkbooks);
xlApplication.Exit();
Marshal.ReleaseComObject(xlApplication);

I've made it using c# 4.0. If you need it to develop it under Visual Studio 2008 or older, which don't support optional parameters, you just need to pass Type.Missing to parameters you don't want to use under Excel library methods (Workbooks.Open and Range.Replace).

Reading excel (.xls) template, replacing variables with textbox values and saving excel file in C#

7 Answers7