---
title: How to generate the dynamic spreadsheet in DotNet
description: Read these interesting findings to generate the spreadsheet dynamically without using Microsoft interop excel dll.
image: https://blog.e-zest.com/hubfs/How%20to%20generate%20the%20dynamic%20spreadsheet%20in%20DotNet_.jpg
---

[![e-Zest logo\_An accion lab company\_Colour](https://blog.e-zest.com/hubfs/images/Logos%20with%20Tagline/Corporate/SVG/e-Zest%20logo_An%20accion%20lab%20company_Colour.svg "e-Zest logo_An accion lab company_Colour")](https://www.e-zest.com/)

- [WORK](https://www.e-zest.com/our-work)
- [SERVICES](https://www.e-zest.com/our-services) 
    - [Product Engineering](https://www.e-zest.com/digital-product-engineering-services)
    - [Enterprise Data Services](https://www.e-zest.com/enterprise-data-services)
    - [Digital Commerce](https://www.e-zest.com/digital-commerce-services)
    - [Digital Operations](https://www.e-zest.com/digital-operations)
    - [Digital Consulting](https://www.e-zest.com/digital-transformation-consulting-services)
    - [Digital Experience Design](https://www.e-zest.com/digital-experience-design)
- [INSIGHTS](https://blog.e-zest.com/)
- [CAREERS](https://www.e-zest.com/careers)
- [ENTERPRISE DATA SERVICES](https://www.e-zest.com/enterprise-data-services)
- [GENERATIVE AI](https://www.e-zest.com/generative-ai)
- [Healthcare](https://healthcare.e-zest.com)
- [Contact Us](https://www.e-zest.com/contact-us)

- ![Elevating Product Engineering with AI and ML](https://blog.e-zest.com/hubfs/Elevating%20Product%20Engineering%20with%20AI%20and%20ML.jpg)

  [BLOGS](https://blog.e-zest.com)
  
  #### Elevating Product Engineering with AI and ML

  As technology evolves and customer choices broaden, there is an increasing ...

  [Read more](https://blog.e-zest.com/elevating-product-engineering-with-ai-and-ml)
- ![e-Zest – An Accion Labs Company gets Recognition as a Contender in the ISG Provider Lens™ Study on Digital Engineering Services 2024](https://blog.e-zest.com/hubfs/e-Zest%20%E2%80%93%20An%20Accion%20Labs%20Company%20-%20Gets%20Recognition%20as%20a%20Contender%20in%20the%20ISG%20Provider%20LensTM%20Study%20on%20Digital%20Engineering%20Services%202024.jpg)

  [NEWS](https://blog.e-zest.com/news)
  
  #### e-Zest – An Accion Labs Company gets Recognition as a Contender in the ISG Provider Lens™ Study on Digital Engineering Services 2024

  We are delighted to announce that e-Zest – An Accion Labs Company has been ...

  [Read more](https://blog.e-zest.com/news/e-zest-an-accion-labs-company-gets-recognition-as-a-contender-in-the-isg-provider-lens-study-on-digital-engineering-services-2024)
- ![e-Zest Solutions supports Digital Pune Mission](https://blog.e-zest.com/hubfs/blog-files/Digital%20Literacy%20Bus%20e-Zest.png)

  [CSR](https://blog.e-zest.com/csr)
  
  #### e-Zest Solutions supports Digital Pune Mission

  **Pune, India:** e-Zest Solutions, a leading digital technology innovation ...

  [Read more](https://blog.e-zest.com/csr/e-zest-solutions-supports-digital-pune-mission)

- [Work](https://www.e-zest.com/our-work)
- [Services](https://www.e-zest.com/our-services)
- [Insights](https://blog.e-zest.com)
- [Careers](https://www.e-zest.com/careers)
- [Enterprise Data](https://www.e-zest.com/enterprise-data-services)
- [Generative AI](https://www.e-zest.com/generative-ai)
- [Innovations](https://www.e-zest.com/innovations)
- [Healthcare](https://healthcare.e-zest.com)
- [About](https://www.e-zest.com/about-us)
- [Contact](https://www.e-zest.com/contact-us)

- [Blog](https://blog.e-zest.com/)
- [News](https://blog.e-zest.com/news)
- [CSR](https://blog.e-zest.com/csr)

- [![Linkedin Icon](https://blog.e-zest.com/hubfs/icons/linkedin_icon_white.svg)](https://www.linkedin.com/company/e-zest-solutions/)
- [![Twitter Icon](https://blog.e-zest.com/hubfs/twitter-logo-silhouette%20(5).svg)](https://twitter.com/ezest)
- [![Facebook Icon](https://blog.e-zest.com/hubfs/facebook-app-logo%20(4).svg)](https://www.facebook.com/61259256638)
- [![Instagram Icon](https://blog.e-zest.com/hubfs/instagram-logo%20(4).svg)](https://www.instagram.com/ezestsolutions/)
- [![Youtube Icon](https://blog.e-zest.com/hubfs/icons/youtube_icon_white.svg)](https://www.youtube.com/user/ezestcorporate)

[![Go to top icon](https://blog.e-zest.com/hubfs/AA%20Assets/top%20button.svg)](https://blog.e-zest.com/how-to-generate-the-dynamic-spreadsheet-in-dotnet#top)

Hit enter to search or ESC to close

Insights

e-Zest members share technology ideas to foster digital transformation.

- [Blog](https://blog.e-zest.com)
- [News](https://blog.e-zest.com/news)
- [Podcasts](https://www.e-zest.com/podcasts)
- [Healthcare](https://blog.e-zest.com/healthcare)
- [CSR](https://blog.e-zest.com/csr)
- [Whitepaper](https://blog.e-zest.com/whitepaper)
- [Playbook](https://blog.e-zest.com/playbook)
- [Media Kit](https://www.e-zest.com/media-kit)

# How to generate the dynamic spreadsheet in DotNet

[Swapna Patne](https://blog.e-zest.com/author/swapna-patne)  Nov 01, 2017

[.NET](https://blog.e-zest.com/topic/net)

- <https://www.linkedin.com/shareArticle?mini=true&url=https://blog.e-zest.com/how-to-generate-the-dynamic-spreadsheet-in-dotnet&title=&summary=&source=>
- <https://twitter.com/intent/tweet?source=https://blog.e-zest.com/how-to-generate-the-dynamic-spreadsheet-in-dotnet&text=How%20to%20generate%20the%20dynamic%20spreadsheet%20in%20DotNet>
- <https://www.facebook.com/sharer/sharer.php?u=https://blog.e-zest.com/how-to-generate-the-dynamic-spreadsheet-in-dotnet>
- <https://www.instagram.com/ezestsolutions/?u=https://blog.e-zest.com/how-to-generate-the-dynamic-spreadsheet-in-dotnet>

![How to generate the dynamic spreadsheet in DotNet\_.jpg](https://blog.e-zest.com/hs-fs/hubfs/How%20to%20generate%20the%20dynamic%20spreadsheet%20in%20DotNet_.jpg?width=744&name=How%20to%20generate%20the%20dynamic%20spreadsheet%20in%20DotNet_.jpg)  
**Introduction**

In this blog, I would like to share some of my interesting findings to generate the spreadsheet dynamically without using Microsoft interop excel dll. The main disadvantages of using Microsoft interop excel are:

- It is a COM library, so you need to be very careful when using in .NET, as it's hard to find memory leaks and if you don't dispose their objects properly, it leaves the Excel.exe process opened. You need to change COM permissions using run command DCOMCONFIG and usually clients will not allow touching these settings.
- As in many cases we have observed that the client machine needs to have Microsoft office installed
- Performance will be very slow
- Number of lines code will be more in case of updating document using the dynamic data.

 In the below blog, I would like to explain how we can overcome the above problems using OPENXML/ EPPlus third party as EPPlus is a free tool to create the spreadsheet using C#.net.  
EPPlus is a .NET library, which reads and writes Excel 2007/2010 or higher files, using Open Office XML format. It supports .XLSX, .XLSM Excel file format.  
Now, we will see how to implement the OPENXML/ EPPlus. First download the OPENXML .dll from the below sites and include in the solution.

Now add the below references in project solution  
using OfficeOpenXml;  
using OfficeOpenXml.Style;

private void GenerateSpreadSheet()  
{   
 ExcelPackage package = new ExcelPackage();

 try  
 {  
 DataSet dsemp = new DataSet();  
 DataTable dtemp = new DataTable("MyTable");  
 dtemp.Columns.Add(new DataColumn("id", typeof(int)));  
 dtemp.Columns.Add(new DataColumn("name", typeof(string)));  
 dtemp.Columns.Add(new DataColumn("address", typeof(string)));

 DataRow dr = dtemp.NewRow();  
 dr\["id"\] = 1;  
 dr\["name"\] = "John";  
 dr\["address"\] = "India";  
 dtemp.Rows.Add(dr);

 dr = dtemp.NewRow();  
 dr\["id"\] = 2;  
 dr\["name"\] = "Jill";  
 dr\["address"\] = "United States of America";  
 dtemp.Rows.Add(dr);

 dr = dtemp.NewRow();  
 dr\["id"\] = 3;  
 dr\["name"\] = "Jack";  
 dr\["address"\] = "UK";  
 dtemp.Rows.Add(dr);

 dr = dtemp.NewRow();  
 dr\["id"\] = 4;  
 dr\["name"\] = "Jerry";  
 dr\["address"\] = "France";  
 dtemp.Rows.Add(dr);

 dsemp.Tables.Add(dtemp);  
 ExcelWorksheet ExcelWorkSheet = package.Workbook.Worksheets.Add("sample.xlxs");  
 if (dsemp.Tables.Count \> 0)  
 {  
 /\* If you want the header with some styling the ass below \*/  
 using (ExcelRange HeaderCell = ExcelWorkSheet.Cells\["A1:C1"\])  
 {  
 HeaderCell.Value = "Sample Excel Sheet";  
 HeaderCell.Style.Font.Bold = true;  
 HeaderCell.Style.Font.Size = 14;  
 HeaderCell.Merge = true;  
 HeaderCell.Style.WrapText = true;  
 HeaderCell.Style.Fill.PatternType = ExcelFillStyle.None;  
 HeaderCell.Style.Font.Color.SetColor(Color.Blue);  
 }

//Writing Columns Name in Excel Sheet  
int r = 3; // Initialize Excel Row Start Position = 4  
int d = 0; // Initialize Data Start Position = 4

for (int col = 1; col \<= dsemp.Tables\[0\].Columns.Count; col++)  
{  
 ExcelWorkSheet.Cells\[r, col\].Value = dsemp.Tables\[0\].Columns\[d\].ColumnName;  
d++;  
}  
r++;

for (int row = 0; row \< dsemp.Tables\[0\].Rows.Count; row++)   
{  
 d = 0; // Initialize Data Start Position = 4  
 // Excel row and column start positions for writing Row=1 and Col=1  
 for (int col = 1; col \<= dsemp.Tables\[0\].Columns.Count; col++)  
 {  
 ExcelWorkSheet.Cells\[r, col\].Value = dsemp.Tables\[0\].Rows\[row\]\[d\];  
 ExcelWorkSheet.Cells\[r, col\].AutoFitColumns();  
 d++;  
 }  
 r++;  
}

/\* If you want the header with some styling the ass below \*/  
using (ExcelRange CellColor = ExcelWorkSheet.Cells\["A3:C3"\])  
{  
 CellColor.Style.Fill.PatternType = ExcelFillStyle.Solid;  
 CellColor.Style.Fill.BackgroundColor.SetColor(Color.LightGray);  
 CellColor.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);  
 }

 int rowcount = dsemp.Tables\[0\].Rows.Count + 5;  
 // Set the borders  
 string RightCell = ("A3") + ":" + ("C" + (rowcount));  
 ExcelWorkSheet.Cells\[RightCell\].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);

// Freeze the Rows and Columns  
ExcelWorkSheet.View.FreezePanes(4, 2);

string HeaderCell3 = "A" + (rowcount + 2);  
ExcelWorkSheet.Cells\[HeaderCell3\].Value = "Report generated.";

ExcelWorkSheet.Cells\[HeaderCell3\].Style.Font.Size = 9;

string MergeCell3 = ("A" + (rowcount + 2)) + ":" + ("C" + (rowcount + 2));  
ExcelWorkSheet.Cells\[MergeCell3\].Merge = true;

string HeaderCell4 = "A" + (rowcount + 3);  
ExcelWorkSheet.Cells\[HeaderCell4\].Value = "Report design copyright © " + DateTime.Now.ToString("yyyy") + "All rights reserved.";  
ExcelWorkSheet.Cells\[HeaderCell4\].Style.Font.Size = 9;  
string MergeCell4 = ("A" + (rowcount + 3)) + ":" + ("C" + (rowcount + 3));  
ExcelWorkSheet.Cells\[MergeCell4\].Merge = true;

// Set the Width if first cell  
ExcelWorkSheet.Cells\[1, 1, rowcount, 3\].AutoFitColumns();  
}

 if (dsemp.Tables\[0\].Rows.Count \> 0 || dsemp.Tables\[1\].Rows.Count \> 0)  
 {  
 // Save the Excel File  
 string ExcelFileName = "D:\\\\" + "sample.xlsx";  
 FileInfo templateFile = new FileInfo(ExcelFileName);  
 package.Workbook.Properties.Title = "Sample";  
 package.SaveAs(templateFile);  
 }  
}  
catch (Exception ex)  
{  
 throw ex;  
}  
finally  
{  
 package.Dispose();  
}  
}  
}

How to attach EPPlus Library  
• Option 1- Download it from : http://epplus.codeplex.com/  
• Option 2- To install EPPlus, run the command given below in Package Manager Console.  
PM\>: Install-Package EPPlus.

Please find various excelsheet properties that can be used while generating the spreadsheet,

**If you want to format the cell then you need to select the range and assign the format string to the property as show below:**

- ExcelWorkSheet.Cells\[“A1”\].Style.Numberformat.Format = "d-mmm-yy";
- ExcelWorkSheet.Cells\[“B2”\].Style.Numberformat.Format = "+#,##0;\[Red\]-#,##0";
- ExcelWorkSheet.Cells\[“C3”\].Style.Numberformat.Format = "#,##0.00";

**If you want to fill the header column for excel sheet then use below code**

using (ExcelRange CellColor = ExcelWorkSheet.Cells\["A1:C1"\])  
{

CellColor.Style.Fill.PatternType = ExcelFillStyle.Solid;  
 CellColor.Style.Fill.BackgroundColor.SetColor(Color.LightGray);  
 CellColor.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);

}

**If you want to set the border for the column for excel sheet then use below code,**

ExcelWorkSheet.Cells\[“A1:C1”\].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);

**If you want to align the Header column for data, numbers to right for excel sheet then use below code,**

ExcelWorkSheet.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;  
ExcelWorkSheet.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;

**If you want to set font style for Header column in excel sheet then use below code,**

using (ExcelRange HeaderCell1 = ExcelWorkSheet.Cells\["A1:C1"\])  
{

// The below code to set the font size  
 HeaderCell1.Style.Font.Size = 14;  
 HeaderCell1.Style.Font.Bold = true;

 // The below code to set the font color  
 HeaderCell1.Style.Font.Color.SetColor(Color.Red);

// The below code is to merge the cells  
 HeaderCell1.Merge = true;

// The below code is to wrap the cell text  
HeaderCell1.Style.WrapText = true;

}

**If you want to set Formula in excel sheet then use below code,**

ExcelWorkSheet.Cells\["B15"\].Formula = "SUM(B2:B7)";

**If you want to assign Auto fit column width property in excel sheet then use below code,**

ExcelWorkSheet.Cells\[1, 1, 15, 15\].AutoFitColumns();

**If you want to Freeze column in excel sheet then use below code,**

ExcelWorkSheet.View.FreezePanes(4, 2); (assign the row and column number)

Please refer the below Output on an Excel sheet generated from the above code,

![sample-excel-sheet.png](https://blog.e-zest.com/hs-fs/hubfs/sample-excel-sheet.png?width=386&name=sample-excel-sheet.png)  
So friends, I hope this information will help you to implement it in very easier way. There are many more, however, I really feel that these are the most critical parts that we need to know in order to implement the dynamic spreadsheet.

- <https://twitter.com/intent/tweet?source=https://blog.e-zest.com/how-to-generate-the-dynamic-spreadsheet-in-dotnet&text=How%20to%20generate%20the%20dynamic%20spreadsheet%20in%20DotNet>
- <https://www.facebook.com/sharer/sharer.php?u=https://blog.e-zest.com/how-to-generate-the-dynamic-spreadsheet-in-dotnet>
- <https://www.linkedin.com/shareArticle?mini=true&url=https://blog.e-zest.com/how-to-generate-the-dynamic-spreadsheet-in-dotnet&title=&summary=&source=>

### Similar Blog

[![How to apply .NET applications on all platforms?](https://blog.e-zest.com/hubfs/blog-files/.net-platforms-apps.png) How to apply .NET applications on all platforms? What is your recommendation on developing an application using Microsoft platform, which is open source, supports cross-platform ... By Sachin Katkar Feb 12, 2019](https://blog.e-zest.com/how-to-apply-dotnet-applications-on-all-platforms)

[![Elasticsearch made easy with .Net](https://blog.e-zest.com/hubfs/Copy%20of%20Copy%20of%20Copy%20of%20Copy%20of%20GDPR.png) Elasticsearch made easy with .Net Let me put this in very simple words. Elastic Search is a database which stores the data in the document format (JSON) and ... By Rahul More Apr 21, 2018](https://blog.e-zest.com/elasticsearch-made-easy-with-.net)

[Globalization and Localization in ASP.NET MVC application Localization comes into play when you want the implemented application to run in a local language. If your website targets users ... By Snehal Patil Dec 29, 2017](https://blog.e-zest.com/globalization-and-localization-in-asp.net-mvc-application)

[![Techniques for code-based navigation in ASP.Net](https://blog.e-zest.com/hubfs/The%20best%20techniques%20for%20code-based%20navigation%20in%20ASP.Net%20.jpg) Techniques for code-based navigation in ASP.Net In a world of desktop applications, where you build programs with multiple forms, you almost always end up calling one form from ... By Aarti Shinde Nov 17, 2017](https://blog.e-zest.com/techniques-for-code-based-navigation-in-asp.net)

[![How to read the spreadsheet (\*.xls,\*.xlxs and \*.csv) and import into dataset using DotNet](https://blog.e-zest.com/hubfs/How%20to%20read%20%20the%20spreadsheet%20and%20%20import%20into%20dataset%20%20using%20DotNet%20.jpg) How to read the spreadsheet (\*.xls,\*.xlxs and \*.csv) and import into dataset using DotNet **Introduction** In my previous blog, i shared details on how to generate the spreadsheet dynamically without using Microsoft ... By Swapna Patne Nov 16, 2017](https://blog.e-zest.com/how-to-read-the-spreadsheet-.xls.xlxs-and-.csv-and-import-into-dataset-using-dotnet)

[![How to read the spreadsheet (\*.xls,\*.xlxs and \*.csv) and import into dataset using DotNet](https://blog.e-zest.com/hubfs/How%20to%20read%20%20the%20spreadsheet%20and%20%20import%20into%20dataset%20%20using%20DotNet%20.jpg) How to read the spreadsheet (\*.xls,\*.xlxs and \*.csv) and import into dataset using DotNet **Introduction** In my previous blog, i shared details on how to generate the spreadsheet dynamically without using Microsoft ... By Swapna Patne Nov 16, 2017](https://blog.e-zest.com/how-to-read-the-spreadsheet-.xls.xlxs-and-.csv-and-import-into-dataset-using-dotnet)

[![How to Implement SQL Server Replication](https://blog.e-zest.com/hs-fs/file-3395401939-png/blog-files/advantages-of-replications.png) How to Implement SQL Server Replication SQL Server replication is a set of technologies for copying and distributing data and database objects from one database to ... By Swapna Patne Feb 13, 2015](https://blog.e-zest.com/how-to-implement-sql-server-replication/)

[How to improve the performance of the application in ASP .Net **Introduction** By Swapna Patne Aug 25, 2014](https://blog.e-zest.com/how-to-improve-the-performance-of-the-application-in-asp/)

[How to Retrieve Active Directory Information for Authentication in ASP.NET **Introduction** By Swapna Patne Jan 14, 2013](https://blog.e-zest.com/how-to-retrieve-active-directory-information-for-authentication-in-asp-net/)

## Helping Organizations Thrive in the Digital Age

[Contact Us](https://www.e-zest.com/contact-us)

- [About](https://www.e-zest.com/about-us)
- [Services](https://www.e-zest.com/our-services)
- [Work](https://www.e-zest.com/our-work)
- [Insights](https://blog.e-zest.com/)
- [Healthcare](https://healthcare.e-zest.com/)
- [Accion Labs](https://www.accionlabs.com/)

- <https://www.linkedin.com/company/e-zest-solutions/>
- <https://twitter.com/ezest>
- <https://www.facebook.com/eZest/>
- <https://www.instagram.com/ezestsolutions/>
- <https://www.youtube.com/user/ezestcorporate>

- Dallas
- Detroit
- Hannover
- Vienna
- London
- Pune

 © 2025 e-Zest Solutions

- [Privacy Policy](https://www.e-zest.com/privacypolicy)
- [Cookie Policy](https://www.e-zest.com/cookie-policy)

 Contact us

 Contact us

e-Zest is a leading digital innovation partner for enterprises and technology companies that utilizes emerging technologies for creating engaging customers experiences. Being a customer-focused and technology-driven company, it always helps clients in crafting holistic business value for their software development efforts. It offers software development and consulting services for cloud computing, enterprise mobility, big data and analytics, user experience and digital commerce.