deargem / 我的图书馆 / (C#)excel数据导入SqlServer中 - ziping99...

分享

   

(C#)excel数据导入SqlServer中 - ziping99的日志 - 网易博客

2009-11-20  deargem

(C#)excel数据导入SqlServer中

 

<html xmlns="" >
<head runat="server">
    <title>无标题页</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:FileUpload ID="FuloadExcelFile" runat="server" Height="25px" Width="473px" />
        <asp:Button ID="btnReadDataTo" runat="server" Text="导入" OnClick="btnReadDataTo_Click" /></div>
    </form>
</body>
</html>
  

 

protected void btnReadDataTo_Click(object sender, EventArgs e)
    
{
        
if (FuloadExcelFile.FileName == "")
            
return;
        
string fileExt = System.IO.Path.GetExtension(FuloadExcelFile.FileName);
        
if (fileExt != ".xls")//必须是EXCEL文件
            return;
        
string filepath = FuloadExcelFile.PostedFile.FileName;//文件路径
        DataTable dt = new DataTable();
        dt 
= CallExcel(filepath);//返回EXCEL文件的数据
        if(InsertSQLServer(dt,FuloadExcelFile.FileName.Split('.')[0]))//导入数据库
        {}
    }


    
protected DataTable CallExcel(string filepath)
    
{
        OleDbConnection con 
= new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0");
        con.Open();
        
string sql = "select * from [Sheet1$]";//选择第一个数据SHEET
        
//OleDbCommand command = new OleDbCommand(sql, con);
        
//OleDbDataReader reader = command.ExecuteReader();
        
//if (reader.Read())
        
//{
        
//    reader[0].ToString();//直接读出数据
        
//}
        OleDbDataAdapter adapter = new OleDbDataAdapter(sql, con);
        DataTable dt 
= new DataTable();
        adapter.Fill(dt);
        
//reader.Close();
        
//command.Dispose();
        con.Close();
        con.Dispose();
        
return dt;
    }


    
protected bool InsertSQLServer(DataTable dt,string dataname)
    
{
        
string strCon = @"User ID=SA;Initial Catalog=CarManage;Data Source=(local);Password=SA";//无数据库名连接
        string strTest="testTable";
        
try
        
{
            SqlConnection con 
= new SqlConnection(strCon);//创建数据库
            con.Open();
            
string strSQL = "IF NOT EXISTS(SELECT name FROM sysobjects WHERE name=N'" + dataname + "') CREATE DATABASE [" + dataname + "]";
            SqlCommand command 
= new SqlCommand(strSQL, con);
            command.ExecuteNonQuery();

            
//创建数据库表
            strSQL = "USE[" + dataname + "] IF NOT EXISTS(SELECT * FROM sysobjects WHERE object_id=OBJECT_ID(N'[DBO].[" + strTest + "]') AND type in (N'U')) CREATE TABLE " + strTest + " (";
            
string strColumn=string.Empty;
            
for (int i = 0; i < dt.Columns.Count; i++)
            
{
                strSQL 
+= "[" + dt.Columns[i].ColumnName + "] [nvarchar](256),";
                strColumn
+=dt.Columns[i].ColumnName+",";
            }

            strSQL 
+= " )";
            SqlCommand newcom 
= new SqlCommand(strSQL, con);
            newcom.ExecuteNonQuery();
            
            
//插入数据
            strColumn=strColumn.Substring(0,strColumn.Length-1);
            
for(int i=0;i<dt.Rows.Count;i++)
            
{
                strSQL 
= "USE[" + dataname + "] Insert into "+strTest+" (" + strColumn + ") values (";
                
for (int k = 0; k < dt.Columns.Count; k++)
                
{
                    strSQL 
+= "'"+dt.Rows[i][k].ToString()+"',";
                }

                strSQL 
= strSQL.Substring(0, strSQL.Length - 1);
                strSQL 
+= ")";
                SqlCommand insertCom 
= new SqlCommand(strSQL, con);
                insertCom.ExecuteNonQuery();
            }

            
return true;
        }

        
catch
        
{
            
return false;
        }

    }

 

 

 

从Excel表格中读取数据导入到Access数据库中

    //一、把DataTable插入数据库
    public static void DataTableToDB()
    {
        string _strExcelFileName = @"D:\example.xls";
        DataTable dtExcel = ExcelToDataTable(_strExcelFileName, "Sheet1");
        for (int i = 0; i < dtExcel.Rows.Count; i++)
        {
            InsertDataToAccess(dtExcel.Rows[i][0].ToString(), float.Parse(dtExcel.Rows[i][1].ToString()));
        }
    }

    //二、把Excel数据读入DataTable
    public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
    {
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties=Excel 5.0;";
        string strExcel = string.Format("select * from [{0}$]", strSheetName);
        DataSet ds = new DataSet();

        using (OleDbConnection conn = new OleDbConnection(strConn))
        {
            conn.Open();
            OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
            adapter.Fill(ds, strSheetName);
            conn.Close();
        }

        return ds.Tables[strSheetName];
    }

    //三、向Access数据库表插入数据
    public static void InsertDataToAccess(string _strPara, float _fPara)
    {
        OleDbConnection oleDbConn = new OleDbConnection();
        oleDbConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ExcelData.mdb;User Id=admin;Password=;";
        oleDbConn.Open();

        string strInsertString = "INSERT INTO tb_excelData (strCollumn1,fCollumn2) VALUES (@strCollumn1,@fCollumn2)";
        OleDbCommand oComm = new OleDbCommand(strInsertString, oleDbConn);
        oComm.Parameters.Add("@strCollumn1", OleDbType.Char, 50);
        oComm.Parameters["@strCollumn1"].Value = _strPara;
        oComm.Parameters.Add("@fCollumn2", OleDbType.Double);
        oComm.Parameters["@fCollumn2"].Value = _fPara;

        ocomm.ExecuteNonQuery();
        oleDbConn.Close();
    }

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多