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.

OPENXML

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.Columns.Add("Name");

            dt.Columns.Add("Country");

            dt.Rows.Add("Venkatesh", "India");

            dt.Rows.Add("Santhosh", "USA");

            dt.Rows.Add("Venkat Sai", "Dubai");

            dt.Rows.Add("Venkat Teja", "Pakistan");

            ds.Tables.Add(dt);

            ExportDataSetToExcel(ds);

        }

        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;

                wb.SaveAs(filepath);

            }

        }

    }

}

Output: