Skip to main content

Create Excel files in C# using Open XML and closed XML

           Simplest way to convert some large Datasets to Excel files is using OpenXML and Closed XML, this can be used for console application as well as Web applications. To create the Excel files (.XLSX) we need to Add the Open XML and Closed XML reference from nugget packages as below.


Closed XML

Here am converting Dataset into Excel file, if data set contains multiple DataTables, it can be looped into same Excel with different sheet. Below is the sample code to connect Generate Excel(.xlsx) and save in some application path.
To use the OpenXML and Closed XML we need to use below namespaces

using ClosedXML.Excel
using DocumentFormat.OpenXml.Spreadsheet

Sample Code :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ClosedXML.Excel;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data;

namespace MyAPP3
    class Program
        static void Main(string[] args)
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            dt.Rows.Add("Venkatesh", "India");
            dt.Rows.Add("Santhosh", "USA");
            dt.Rows.Add("Venkat Sai", "Dubai");
            dt.Rows.Add("Venkat Teja", "Pakistan");

        public static void  ExportDataSetToExcel(DataSet ds)
            string AppLocation = "";
            AppLocation = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().CodeBase);
            AppLocation = AppLocation.Replace("file:\\", "");
            string date = DateTime.Now.ToShortDateString();
            date = date.Replace("/", "_");
            string filepath = AppLocation + "\\ExcelFiles\\" + "RECEIPTS_COMPARISON_" + date + ".xlsx";

            using (XLWorkbook wb = new XLWorkbook())
                for (int i = 0; i < ds.Tables.Count; i++)
                    wb.Worksheets.Add(ds.Tables[i], ds.Tables[i].TableName);
                wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                wb.Style.Font.Bold = true;



  1. ZetExcel aso is very helpful

    Jeremy Gore

  2. Really? I will quickly check ZetExcel very soon
    Peter Ferguson

  3. Thanks for Distribution a Nice Post about Excel and I must Say it’s actually helpful for us. We Learn So Many Things through Your Post So Please Keep Going & Never Stop Your Writing

  4. Great post! This is very useful for me and gain more information, Thanks for sharing with us.

    Article submission sites

  5. How can we Bind Data into Excel using ClosedXML without Header ?

  6. how can we open the excel file instead of downloading????

  7. This comment has been removed by a blog administrator.

  8. Wonderful article, regarding this issue I can suggest you to use develop high performance applications to Create, Edit, Convert or Print Excel spreadsheet file formats without requiring Microsoft Excel.

  9. thanks a lot this helped me i was stuck actually. i just followed the code excel file generated after the changes according to this.

  10. how to remove the autofilters

    Autofilter= false
    not working in console application please help


Post a Comment