Simple C# class to export a DataSet/DataTable to MS-Excel (Without Interop)

Hi Friends,

I found this neat library called EPPlus in codeplex, which allows you to create and read spreadsheets. It is really helpful but it doesn't offer this functionality right out of the box. I've written a small class to encapsulate this functionality. Hope this helps you..

EPPlus Library can be downloaded from http://epplus.codeplex.com/

Code :

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.Data.SqlClient;

using System.IO;

using System.Drawing;

using OfficeOpenXml;        //To download this, please visit: http://excelpackage.codeplex.com/

namespace ExportToExcelLibrary

{

    public class ClsExportToExcel

    {

        static int i = 0;

        public static void ExportToExcel(DataSet oDs, string strFileName)

        {

            try

            {

                FileInfo newFile = new FileInfo(strFileName);

                using (ExcelPackage xlPackage = new ExcelPackage(newFile))

                {

                    //Do the export stuff here..

                    int i = 0;

                    foreach (DataTable odt in oDs.Tables)

                    {

                        i++;

                        string sheetname = null == odt.TableName || odt.TableName.Equals(string.Empty) ? "Sheet" + i.ToString() : odt.TableName;

                        AddSheetsToWorkBookFromDataTable(xlPackage, odt, sheetname);

                    }

                    xlPackage.Save();

                    i = 0;

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

           // i = 0;

        }

        public static void ApplyFormattingToARangeByDataType(ExcelRange oRange, DataColumn oDC)

        {

      

            if (IsDate(oDC))

            {

                oRange.Style.Numberformat.Format = @"dd/mm/yyyy hh:mm:ss AM/PM";

            }

            else if (IsInteger(oDC))

            {

                //Do Nothing

            }

            else if (IsNumeric(oDC))

            {

                oRange.Style.Numberformat.Format = @"#.##";

            }

            oRange.AutoFitColumns();

        }

        public static void AddSheetsToWorkBookFromDataTable(ExcelPackage oPack, DataTable oDT, string SheetName)

        {

            try

            {

                ExcelWorksheet oWs = oPack.Workbook.Worksheets.Add(null == oDT.TableName || oDT.TableName.Equals(string.Empty) ? "Sheet" + i.ToString() : oDT.TableName);

                oWs.Cells.Style.Font.Name = "Calibiri";

                oWs.Cells.Style.Font.Size = 10;

                int ColCnt =

                            oDT.Columns.Count, RowCnt = oDT.Rows.Count;

              

                //Export each row..

                oWs.Cells["A1"].LoadFromDataTable(oDT, true);

                //Format the header

                using (ExcelRange oRange = oWs.Cells["A1:" + GetColumnAlphabetFromNumber(ColCnt) + "1"])

                {

                    oRange.Style.Font.Bold = true;

                    oRange.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;

                    oRange.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));

                }

                int CurrentColCount = 1;

                foreach (DataColumn oDC in oDT.Columns)

                {

                    using (ExcelRange oRange = oWs.Cells[GetColumnAlphabetFromNumber(CurrentColCount) + "1:" + GetColumnAlphabetFromNumber(CurrentColCount) + RowCnt.ToString()])

                    {

                        ApplyFormattingToARangeByDataType(oRange, oDC);

                    }

                    CurrentColCount++;

                }

               

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        public static bool IsInteger( DataColumn col)

        {

            if (col == null)

                return false;

            var numericTypes = new[] { typeof(Byte),  

                                                typeof(Int16), typeof(Int32), typeof(Int64), typeof(SByte),

                                                 typeof(UInt16), typeof(UInt32), typeof(UInt64)};

            return numericTypes.Contains(col.DataType);

        }

        public static bool IsNumeric( DataColumn col)

        {

            if (col == null)

                return false;

                    var numericTypes = new[] {  typeof(Decimal), typeof(Double),

                                                typeof(Single)};

            return numericTypes.Contains(col.DataType);

        }

        public static bool IsDate( DataColumn col)

        {

            if (col == null)

                return false;

            var numericTypes = new[] { typeof(DateTime), typeof(TimeSpan)};

            return numericTypes.Contains(col.DataType);

        }

        public static string GetColumnAlphabetFromNumber(int iColCount)

        {

            string strColAlpha = string.Empty;

            try

            {

                int iloop = iColCount, icount1 = 0, icount2 = 0;

                Char chr = ' ';

                while (iloop > 676)

                {

                    iloop -= 676;

                    icount1++;

                }

                if (icount1 != 0)

                {

                    chr = (Char)(64 + icount1);

                    strColAlpha = chr.ToString();

                }

                while (iloop > 26)

                {

                    iloop -= 26;

                    icount2++;

                }

                if (icount2 != 0)

                {

                    chr = (Char)(64 + icount2);

                    strColAlpha = strColAlpha + chr.ToString();

                }

                chr = (Char)(64 + iloop);

                strColAlpha = strColAlpha + chr.ToString();

            }

            catch (Exception ex)

            {

                throw ex;

            }

            return strColAlpha;

        }

        public static void ExportToExcel(SqlDataReader oReader, string strFileName)

        {

            throw new NotImplementedException();

        }

        public static void ExportToExcel(DataTable oDT, string strFileName)

        {

            try

            {

                FileInfo newFile = new FileInfo(strFileName);

                using (ExcelPackage xlPackage = new ExcelPackage(newFile))

                {

                    //Do the export stuff here..

                    string sheetname = null == oDT.TableName || oDT.TableName.Equals(string.Empty) ? "Sheet1" : oDT.TableName;

                    AddSheetsToWorkBookFromDataTable(xlPackage, oDT, sheetname);

                    xlPackage.Save();

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

    }

}