0

Export To Excel using Microsoft Office Library

Hi Developers, I have created this Helper Class to export DataTable To Excel with formatting using Microsoft Office library.

Required “Microsoft.Office.Interop.Excel.dll”

Below is the helper class code

using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;

namespace CodingJugaad.Helpers
{
    public static class ExcelExpoterHelper
    {
        static Application oXL;
        static Workbook oWB;
        static Worksheet oSheet;
        static int noRows;
        public static void ExportDataTableToExcel(System.Data.DataTable tbl, string strReportName, out string filename)
        {
            filename = "";
            noRows = tbl.Rows.Count;
            Range oRange;
            //Start Excel and get Application object.
            oXL = new Application();
           

            // Set some properties
            oXL.Visible = true;
            oXL.DisplayAlerts = false;
            oXL.Workbooks.Add();

            // Get a new workbook.
            oWB = oXL.Workbooks.Add(Missing.Value);

            // Get the Active sheet
            oSheet = (Worksheet)oWB.ActiveSheet;
            oSheet.Name = "Data1";

            int rowCount = 1;

            foreach (DataRow dr in tbl.Rows)
            {
                rowCount += 1;

                for (int i = 1; i < tbl.Columns.Count + 1; i++)
                {
                    // Add the header the first time through
                    if (rowCount == 2)
                    {
                        oSheet.Cells[1, i] = tbl.Columns[i - 1].ColumnName;
                    }
                    oSheet.Cells[rowCount, i] = dr[i - 1].ToString();


                }
            }

            //  Code patch added to fix the "get_Range" issue
            //  Resize the columns
            Microsoft.Office.Interop.Excel.Range c1 = oSheet.Cells[1, 1];
            Microsoft.Office.Interop.Excel.Range c2 = oSheet.Cells[rowCount, tbl.Columns.Count];
            oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.get_Range(c1, c2);
            oRange.EntireColumn.AutoFit();          
            
            // Save the sheet and close
            oSheet = null;
            oRange = null;
            filename = strReportName + DateTime.Now.ToString("_yyyyMMddHHmmssfff") + ".xls";
            oWB.SaveAs(ConfigurationManager.AppSettings["File_Path"] + filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);
            oWB.Close(Missing.Value, Missing.Value, Missing.Value);
            oWB = null;
            oXL.Quit();


        }
        #region "Common fuctions"

        private static void setBold(string strRange)
        {
            if (!string.IsNullOrEmpty(strRange))
            {
                oSheet.get_Range(strRange).Font.Bold = true;
            }
        }
        private static void setIndianFormat(string strRange)
        {
            if (!string.IsNullOrEmpty(strRange))
            {
                oSheet.get_Range(strRange).NumberFormat = "@";
                oSheet.get_Range(strRange).NumberFormat = "[>=10000000]##\\,##\\,##\\,##0.00;[>=100000]##\\,##\\,##0.00;##,##0.00";
            }
        }
        private static void setIndianFormat4(string strRange)
        {
            if (!string.IsNullOrEmpty(strRange))
            {
                oSheet.get_Range(strRange).NumberFormat = "@";
                oSheet.get_Range(strRange).NumberFormat = "[>=10000000]##\\,##\\,##\\,##0.0000;[>=100000]##\\,##\\,##0.0000;##,##0.0000";
            }
        }

        private static void setCustomDateFormat(string strRange, string customFormat)
        {
            if (!string.IsNullOrEmpty(strRange))
            {
                oSheet.get_Range(strRange).NumberFormat = customFormat;

            }
        }

        private static void setExcelAutoSize()
        {
            oSheet.Cells.EntireRow.AutoFit();
            oSheet.Cells.EntireColumn.AutoFit();
        }
        # endregion

    }
}

Santosh Shelar

Leave a Reply

Your email address will not be published. Required fields are marked *