Exporting the data to a Templated Excel sheet using Excel Application object

Question

I am creating a windows application with C#, in which requirement is to show Excel spreadsheet in the windows form.

There are some predefined excel templates which are filled by the data from the database. The data coming from data access layer is in the form of IEnumerable objects, which will fill the data in the excel template. I am able to fill the data in the excel template but problem is related to fill the predefined template.

Any help appreciated.

Thanks in Advance.

Answer 1

Unless you have no choice, I would not recommend using the MS Excel API, we had lots of troubles with them.

We use EPPlus opensource library, and had greadt success so far. It's (IMHO) easy to learn, has an active development, NuGet support, and has quite a lot of features, can do a lot of fancy stuff. An example for filling templates is here:

using (var templateXls = new ExcelPackage(new FileInfo(templateFilePath)))
{
    var sheet = templateXls .Workbook.Worksheets[1];
    ...
    sheet.Cells["A1"].Value = "your content";
    ...
    templateXls.SaveAs(new FileInfo(newFilePath));                   ) 
}