读取Excel / CSV 文件到数据库

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

    /// <summary>
    /// 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)
    /// </summary>
    /// <param name="path">Path to file on server,type System.string</param>
    /// <param name="hasHeader">True or false to indicate if first row is a header row or not</param>
    /// <param name="sheet">Name of the spreadsheet to select data from. Use null for .csv file</param>
    /// <returns>System.Data.DataTable</returns>
    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,xl2003,imex);
                break;
            case ".csv":
                conn = String.Format(conn,Path.GetDirectoryName(path),text,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;
 
    }

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!