FastExportExcelHelper

//These Code are for references Only.
//cjjack88.blogspot.com
//ccjwsy@gmail.com

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;

/// <summary>
/// This class is designed to help export the dataset/datatable to excel file.
/// </summary>
public class FastExportExcelHelper : IDisposable
{
    private DataTable localDT;
    private string[] StrIsDecimalColumns;
    private string[] StrIsDate;
    private string[] StrIsBool;

    /// <summary>
    /// initiate FastExportExcelHelper.
    /// </summary>
public FastExportExcelHelper()
{
        StrIsDecimalColumns = new string[0];
        StrIsDate = new string[0];
        StrIsBool = new string[0];
        localDT = new DataTable();
}

    /// <summary>
    /// initiate FastExportExcelHelper with a Dataset
    /// </summary>
    public FastExportExcelHelper(DataSet inDS)
    {
        StrIsDecimalColumns = new string[0];
        StrIsDate = new string[0];
        StrIsBool = new string[0];
        localDT = new DataTable();
        localDT = inDS.Tables[0].Copy();
    }

    /// <summary>
    /// initiate FastExportExcelHelper with a DataTable
    /// </summary>
    public FastExportExcelHelper(DataTable inDT)
    {
        StrIsDecimalColumns = new string[0];
        StrIsDate = new string[0];
        StrIsBool = new string[0];
        localDT = new DataTable();
        localDT = inDT.Copy();
    }

    /// <summary>
    /// Dispose()
    /// </summary>
    public void Dispose()
    {
        localDT.Dispose();
        Array.Clear(StrIsDecimalColumns, 0, StrIsDecimalColumns.Length);
        Array.Clear(StrIsBool, 0, StrIsDecimalColumns.Length);
    }

    /// <summary>
    /// Set the targeted dataset that needed to export.
    /// </summary>
    public void SetDataSet(DataSet inDS)
    {
        localDT = new DataTable();
        localDT = inDS.Tables[0].Copy();
    }

    /// <summary>
    /// Set the targeted datatable that needed to export.
    /// </summary>
    public void SetDataTable(DataTable inDT)
    {
        localDT = new DataTable();
        localDT = inDT.Copy();
    }

    /// <summary>
    /// Rename Header Name
    /// </summary>
    public void RenameHeaderName(string strTargetColumnName, string strRenameTo)
    {
        if (strTargetColumnName != "")
        {
            try
            {
                localDT.Columns[strTargetColumnName].ColumnName = strRenameTo;
            }
            catch
            {
                //Log ur ex here.
            }
        }
    }

    /// <summary>
    /// Tells FastExportExcelHelper which column(s) is decimal format.
    /// </summary>
    public void IsDecimalColumns(string[] inStrIsDecimalColumns)
    {
        StrIsDecimalColumns = new string[inStrIsDecimalColumns.Length];
        for (int i = 0, n = inStrIsDecimalColumns.Length; i < n; i++)
        {
            StrIsDecimalColumns[i] = inStrIsDecimalColumns[i];
        }
    }

    /// <summary>
    /// Tells FastExportExcelHelper which column(s) is Date format.
    /// </summary>
    public void IsDateColumns(string[] inStrIsDate)
    {
        StrIsDate = new string[inStrIsDate.Length];
        for (int i = 0, n = inStrIsDate.Length; i < n; i++)
        {
            StrIsDate[i] = inStrIsDate[i];
        }
    }

    /// <summary>
    /// Tells FastExportExcelHelper which column(s) is Bool format. Note: Please set the dataset/datatable before call this function.
    /// </summary>
    public void IsBoolColumns(string[] inStrIsBool)
    {
        StrIsBool = new string[inStrIsBool.Length];
        for (int i = 0, n = inStrIsBool.Length; i < n; i++)
        {
            StrIsBool[i] = inStrIsBool[i];
        }
    }

    /// <summary>
    /// Finally call this to export.
    /// Example: excelHelper.CreateFastExcel(ExcelName, this.Response);
    /// </summary>
    public void CreateFastExcel(string FileName, HttpResponse inResp)
    {
        try
        {
            HttpResponse resp;
            resp = inResp;
            resp.Clear();
            resp.Buffer = false;
            resp.Charset = "UTF-8";
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            resp.ContentType = "application/vnd.ms-excel";
            string sExcelHeader = @"<meta http-equiv='Content-Type' content='text/html; charset=utf-8' /><style> .text { mso-number-format:\@; } .general { mso-number-format:General; } .decimal { mso-number-format:0\.00; } </style>" + Environment.NewLine;
            resp.Write(sExcelHeader + FastExportTable().ToString());
            resp.End();
 
        }
        catch (System.Exception ex)
        {
            //Log ur ex here.
        }
    }

    /// <summary>
    /// FastExportTable. Format the DataSet to the Excel format. Internal use only.
    /// </summary>
    private string FastExportTable()
    {
        System.IO.StringWriter stringWriter = new System.IO.StringWriter();

        try
        {
            HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
            GridView gv = new GridView();

            gv.DataSource = localDT;
            gv.DataBind();

            for (int i = 0; i < gv.Rows.Count; i++)
            {
                for (int j = 0; j < gv.Rows[i].Cells.Count; j++)
                {

                    if (iSearch(StrIsDecimalColumns, localDT.Columns[j].ToString()))
                    {
                        gv.Rows[i].Cells[j].Attributes.Add("Class", "decimal");
                    }
                    else if (iSearch(StrIsDate, localDT.Columns[j].ToString()))
                    {
                        DateTime tempDateTime = new DateTime();
                        bool isDate = DateTime.TryParse(localDT.Rows[i][j].ToString(), out  tempDateTime);
                        if (isDate)
                            gv.Rows[i].Cells[j].Text = string.Format("{0:yyyy-MM-dd HH:mm:ss}", tempDateTime);
                        else
                            gv.Rows[i].Cells[j].Text = localDT.Rows[i][j].ToString();

                        gv.Rows[i].Cells[j].Attributes.Add("Class", "text");
                    }
                    else if (iSearch(StrIsBool, localDT.Columns[j].ToString()))
                    {
                        bool tempBool = false;
                        bool isBool = bool.TryParse(localDT.Rows[i][j].ToString(), out tempBool);
                        if (isBool)
                        {
                            if ((bool)localDT.Rows[i][j] == true)
                            {
                                gv.Rows[i].Cells[j].Text = "True";
                            }
                            else
                            {
                                gv.Rows[i].Cells[j].Text = "False";
                            }
                        }
                        else
                            gv.Rows[i].Cells[j].Text = localDT.Rows[i][j].ToString();

                        gv.Rows[i].Cells[j].Attributes.Add("Class", "text");
                    }
                    else
                    {
                        gv.Rows[i].Cells[j].Attributes.Add("Class", "text");
                    }
                }
            }

            gv.RenderControl(htmlWriter);
        }
        catch (Exception ex)
        {
           //Log ur ex here.
        }
        return stringWriter.ToString();
    }

    /// <summary>
    /// iSearch. Internal use only.
    /// </summary>
    private Boolean iSearch(string[] inArray, string strKeyword)    //Search is it the given keyword is in the words array or not.
    {
        for (int i = 0, n = inArray.Length; i < n; i++)
        {
            if (string.Compare(strKeyword, inArray[i], false) == 0)
                return true;
        }

        return false;
    }
}