c# 导入 excel 文件到数据库

最新推荐文章于 2023-01-12 20:51:31 发布

a54240622

最新推荐文章于 2023-01-12 20:51:31 发布

阅读量93

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/charrot/archive/2009/05/29/1491672.html

版权

Code
try
        {
            string path;
            if (!(Path.GetExtension(fuImport.PostedFile.FileName) == ".xls" || Path.GetExtension(fuImport.PostedFile.FileName) == ".xlsx"))
            {
                Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "al", "alert('Please Import a valid Excel file.');", true);

            }
            else
            {
                path = Server.MapPath(".");
                if (Path.GetExtension(fuImport.PostedFile.FileName) == ".xlsx" || Path.GetExtension(fuImport.PostedFile.FileName) == ".xls")
                    path = "~\\ImportFile\\Employee\\asc.xlsx";
                if (Path.GetExtension(fuImport.PostedFile.FileName) == ".xls")
                    path = "~\\ImportFile\\Employee\\asc.xls";

                fuImport.PostedFile.SaveAs(Server.MapPath(path));
                fuImport.Dispose();
                //Importing Data from .xls file into FFISM Grid
                ImportFFISMData(path);

                fuImport.Dispose();

            }

        }
        catch (Exception ex)
        {
            Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "invalid", "alert('Error in importing data :" + ex.Message.ToString() + "');", true);
            return;
        }

// Import Employee details from .xls file.
    private void ImportFFISMData(string path)
    {
        int dateRowIndex = -1; // Row Index of Date Column
        int dateColIndex = -1; // Column Index of Date Column
        int nextdateRowIndex = -1; //Column Index of Next Date Column

        string excelConnectionString = string.Empty;
        if (Path.GetExtension(path) == ".xlsx")
            excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(path) + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
        else
            excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(path) + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";

        OleDbConnection oleDbConn = new OleDbConnection(excelConnectionString);
        OleDbCommand oleDbCmd;
        OleDbDataAdapter oleDbAdp;
        DataTable oleDt = new DataTable();
        DataTable dtTableName = new DataTable();
        try
        {
            oleDbConn.Open();
            dtTableName = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string[] tableNames = new string[dtTableName.Rows.Count];
            for (int j = 0; j < dtTableName.Rows.Count; j++)
            {
                tableNames[j] = dtTableName.Rows[j]["TABLE_NAME"].ToString();
            }
            oleDbCmd = new OleDbCommand("SELECT * FROM [" + tableNames[0] + "]", oleDbConn);
            oleDbAdp = new OleDbDataAdapter(oleDbCmd);
            oleDbAdp.Fill(oleDt);
            oleDbConn.Close();
            Session["XLSData"] = oleDt;
        }
        catch (Exception ex)
        {
            try
            {
                string delimeter = "\t";
                DataSet ds = new DataSet();
                ds = BuildDataSet(Server.MapPath(path).ToString(), "dt", delimeter);
                oleDt = ds.Tables[0];
                oleDt.Rows.RemoveAt(oleDt.Rows.Count -1);

                Session["XLSData"] = oleDt;
                oleDbConn.Close();
            }
            catch
            {
                oleDbConn.Close();
                Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "invalid", "alert('Error in importing data :" + ex.Message.ToString() + "');", true);
                return;
            }

        }
    }

转载于:https://www.cnblogs.com/charrot/archive/2009/05/29/1491672.html