C#做excel导入sql server2000数据库的问题

Question

数据表的字段有整形、字符串。Excel通过程序来导入库。导不进去，出现错误。分析应该是excel表里的数据类型与数据库字段类型不一致，导不进去。如何让Excel表同一列数据的格式一至，通过程序来实现。

Answer 1

SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\a.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\a.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
读取EXCEL数据到dataset实现数据更新

Answer 2

首先将数据读取到dataset代码如下
private DataSet CreateDataSource()
    {
        string strCon;
        strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("../excel.xls") + "; Extended Properties=Excel 8.0;";
        OleDbConnection olecon = new OleDbConnection(strCon);
        OleDbDataAdapter myda = new OleDbDataAdapter("SELECT * FROM [abc$]", strCon);
        DataSet myds = new DataSet();
        myda.Fill(myds);
        return myds;
    }
在讲dataset的数据保存到数据库里面去

Answer 3

应该是你拼接sql字符串导致的问题吧，把你的代码贴出来看看？

Answer 4

string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelSrc + ";Extended Properties=Excel 8.0";
                    string query = "select * from [Sheet1$]";
                    OleDbCommand oleCommand = new OleDbCommand(query, new OleDbConnection(ConnStr));
                    OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);
                    DataSet ds = new DataSet();
                    oleAdapter.Fill(ds, "[Sheet1$]");
                    DataTable dt = new DataTable();
                    dt = ds.Tables[0];
                    dtCount = dt.Rows.Count;
                    cn.conn.Open();
                                        SqlCommand sCmd = new SqlCommand("sp_UserSel", cn.conn);
                    sCmd.CommandType = CommandType.StoredProcedure;                    sCmd.Parameters.Add("@InsName", SqlDbType.NVarChar, 50);
                    sCmd.Parameters["@InsName"].Direction = ParameterDirection.Input;                    sCmd.Parameters.Add("@CredNum", SqlDbType.NVarChar, 50);
                    sCmd.Parameters["@CredNum"].Direction = ParameterDirection.Input;                    sCmd.Parameters.Add("@UserCount", SqlDbType.Int);
                    sCmd.Parameters["@UserCount"].Direction = ParameterDirection.Output;                    SqlCommand cmd = new SqlCommand("sp_UserAdd", cn.conn);
                    cmd.CommandType = CommandType.StoredProcedure;                    cmd.Parameters.Add("@ConPer", SqlDbType.NVarChar, 50);
                    cmd.Parameters["@ConPer"].Direction = ParameterDirection.Input;                    cmd.Parameters.Add("@ConTel", SqlDbType.NVarChar, 50);
                    cmd.Parameters["@ConTel"].Direction = ParameterDirection.Input;                    cmd.Parameters.Add("@InsName", SqlDbType.NVarChar, 50);
                    cmd.Parameters["@InsName"].Direction = ParameterDirection.Input;                    cmd.Parameters.Add("@InsTel", SqlDbType.NVarChar, 50);
                    cmd.Parameters["@InsTel"].Direction = ParameterDirection.Input;                    cmd.Parameters.Add("@Cred", SqlDbType.NVarChar, 50);
                    cmd.Parameters["@Cred"].Direction = ParameterDirection.Input;                    cmd.Parameters.Add("@CredNum", SqlDbType.NVarChar, 50);
                    cmd.Parameters["@CredNum"].Direction = ParameterDirection.Input;                    cmd.Parameters.Add("@AccountName", SqlDbType.NVarChar, 50);
                    cmd.Parameters["@AccountName"].Direction = ParameterDirection.Input;                    cmd.Parameters.Add("@Bank", SqlDbType.NVarChar, 50);
                    cmd.Parameters["@Bank"].Direction = ParameterDirection.Input;                    cmd.Parameters.Add("@AccountNum", SqlDbType.NVarChar, 50);
                    cmd.Parameters["@AccountNum"].Direction = ParameterDirection.Input;                    cmd.Parameters.Add("@InsPhone", SqlDbType.NVarChar, 50);
                    cmd.Parameters["@InsPhone"].Direction = ParameterDirection.Input;                    cmd.Parameters.Add("@Addr", SqlDbType.NVarChar, 50);
                    cmd.Parameters["@Addr"].Direction = ParameterDirection.Input;                    cmd.Parameters.Add("@Department", SqlDbType.NVarChar, 50);
                    cmd.Parameters["@Department"].Direction = ParameterDirection.Input;                    cmd.Parameters.Add("@Unit", SqlDbType.NVarChar, 50);
                    cmd.Parameters["@Unit"].Direction = ParameterDirection.Input;                    cmd.Parameters.Add("@IsTrue", SqlDbType.NVarChar, 2);
                    cmd.Parameters["@IsTrue"].Direction = ParameterDirection.Input;                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        sCmd.Parameters["@InsName"].Value = dt.Rows[i][3].ToString();
                        sCmd.Parameters["@CredNum"].Value = dt.Rows[i][6].ToString();
                        sCmd.ExecuteNonQuery();
                        string userCount = sCmd.Parameters["@UserCount"].Value.ToString();                        cmd.Parameters["@ConPer"].Value = dt.Rows[i][0].ToString();
                        cmd.Parameters["@ConTel"].Value = dt.Rows[i][1].ToString();
                        cmd.Parameters["@InsName"].Value = dt.Rows[i][3].ToString();
                        cmd.Parameters["@InsTel"].Value = Convert.ToString(dt.Rows[i][4]);
                        cmd.Parameters["@Cred"].Value = dt.Rows[i][5].ToString();
                        cmd.Parameters["@CredNum"].Value = dt.Rows[i][6].ToString();
                        cmd.Parameters["@AccountName"].Value = dt.Rows[i][7].ToString();
                        cmd.Parameters["@Bank"].Value = dt.Rows[i][8].ToString();
                        cmd.Parameters["@AccountNum"].Value = dt.Rows[i][9].ToString();                        cmd.Parameters["@InsPhone"].Value = Convert.ToString(dt.Rows[i][2]);
                        cmd.Parameters["@Addr"].Value = dt.Rows[i][10].ToString();
                        cmd.Parameters["@Department"].Value = dt.Rows[i][11].ToString();
                        cmd.Parameters["@Unit"].Value = dt.Rows[i][12].ToString();
                        cmd.Parameters["@IsTrue"].Value = userCount;                        cmd.ExecuteNonQuery();
                        isTrue = 1;                    }
                    dt.Dispose();
这是我的代码