Excel导入到DataTable

最新推荐文章于 2023-07-18 17:23:25 发布

weixin_34267123

最新推荐文章于 2023-07-18 17:23:25 发布

阅读量74

点赞数

原文链接：http://www.cnblogs.com/Yellowshorts/p/3598369.html

版权

1.前台代码

<asp:FileUpload ID="fupFiles" runat="server" />
<asp:Button ID="btnImprot" runat="server" Text="导入" OnClick="btnImprot_Click" />

2.后台代码

  protected void btnImprot_Click(object sender, EventArgs e)
    {
        String fileName = System.IO.Path.GetFileName(fupFiles.FileName);
        String path = Server.MapPath("~/" + fileName);
        fupFiles.SaveAs(path);

        DataTable dt = ImportExcelByDB(path);
       
    }
    public static DataTable ImportExcelByDB(string physicalPath)
    {
        string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + physicalPath + ";Extended Properties='Excel 8.0;HDR=yes'";
        //  Excel 2007
        if (physicalPath.ToLower().IndexOf(".xlsx") > 0 && physicalPath.ToLower().EndsWith("xlsx"))
        {
            //strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + physicalPath + "';Extended Properties='Excel 12.0;HDR=YES'";
            //strConn = "'Microsoft.ACE.OLEDB.12.0','Data Source=" + physicalPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"'";
            strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + physicalPath + ";Extended Properties='Excel 8.0;HDR=yes'";
        }
        //  Excel 2003
        if (physicalPath.ToLower().IndexOf(".xls") > 0 && physicalPath.ToLower().EndsWith("xls"))
        {
            //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + physicalPath + "';Extended Properties='Excel 8.0;HDR=YES;'";
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + physicalPath + "';Extended Properties=Excel 8.0";
        }
        DataSet dst = new DataSet();
        OleDbConnection conn = new OleDbConnection(strConn);
        try
        {
            if (conn.State.ToString() == "Closed")
            {
                conn.Open();
            }
            dst.Clear();
            string strSql = "SELECT * FROM  [Sheet1$]";
            OleDbDataAdapter adapter = new OleDbDataAdapter(strSql, conn);
            adapter.Fill(dst, "[Sheet1$]");
            conn.Close();
        }
        catch (Exception ee)
        {
            return null;
        }
        return dst.Tables[0];
    }