[keakTheGEEK]

Description: Requires the following namespaces: using System.Data.OleDb; using System.Data; using System.IO; Also, HttpServerUtility is needed (for Server.MapPath()) //Sample usage: gvFiles.DataSource = GetFileContent(filePath, true, "[Sheet1$]"); gvFiles.DataBind();This function will read the contents of an excel spreadsheet (.xlsx || .xls) or a comma separated value file (.csv) into a data table. (Works with Excel 2003 and Excel 2007)

    /// 

    /// Reads the contents of an excel spreadsheet (.xlsx || .xls) 
    /// or a comma separated value file (.csv) into a data table. 
    /// (Works with Excel 2003 and Excel 2007)
    /// 
    /// Path to file on server, type System.string
    /// True or false to indicate if first row is a header row or not
    /// Name of the spreadsheet to select data from. Use null for .csv file
    /// System.Data.DataTable
    protected DataTable GetFileContent(string path, bool hasHeader, string sheet)
    {
        #region Data Providers
        /*Office 2007*/
        string ace = "Microsoft.ACE.OLEDB.12.0";

        /*Office 97 - 2003*/
        string jet = "Microsoft.Jet.OLEDB.4.0";
        #endregion

        #region Excel Properties
        string xl2007 = "Excel 12.0 Xml";
        string xl2003 = "Excel 8.0";
        string imex = "IMEX=1";
        #endregion

        #region CSV Properties
        string text = "text";
        string fmt = "FMT=Delimited";
        #endregion

        string hdr = hasHeader ? "Yes" : "No";
        string conn = "Provider={0};Data Source={1};Extended Properties="{2};HDR={3};{4}";";
        string select = "SELECT * FROM {0}";
        string ext = Path.GetExtension(path);

        OleDbDataAdapter oda;
        DataTable dt = new DataTable("data");

        switch (ext.ToLower())
        {
            case ".xlsx":
                conn = String.Format(conn, ace, Path.GetFullPath(path), xl2007, hasHeader, imex);                
                break;
            case ".xls":
                conn = String.Format(conn, jet, Path.GetFullPath(path), xl2003, hasHeader, imex);
                break;
            case ".csv":
                conn = String.Format(conn, jet, Path.GetDirectoryName(path), text, hasHeader, fmt);
                sheet = Path.GetFileName(path);
                break;
            default:
                throw new Exception("File Not Supported!");
        }

        select = String.Format(select, sheet);
        oda = new OleDbDataAdapter(select, conn);

        oda.Fill(dt);
        return dt;
 
    }