C#求助！将excel表导入到server 2000

[zuopeixiao]

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;


namespace OA
{
public partial class Course : Form
{
public Course()
{
InitializeComponent();
}

/// <summary>
/// 打开对话框
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>


/// <summary>
/// 导入文件的具体方法
/// 代云超
/// </summary>
/// <param name="file">要导入的文件</param>
/// <returns></returns>
public static DataSet ImportExcel(string file)
{
FileInfo fileInfo = new FileInfo(file);
if (!fileInfo.Exists) return null; string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
OleDbConnection objConn = new OleDbConnection(strConn);
DataSet dsExcel = new DataSet();
try
{
objConn.Open();
string strSql = "select * from [Sheet1$]";
OleDbDataAdapter odbcExcelDataAdapter = new OleDbDataAdapter(strSql, objConn);
odbcExcelDataAdapter.Fill(dsExcel); return dsExcel;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 这是将ds对象导入数据库的方法
/// 代云超
/// </summary>
/// <returns>执行是否成功 如: 成功=true 失败=false</returns>
public static bool ExportInfo(DataSet ds)
{
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)//如果ds中是有值的话 执行下面的操作
{
return Do(ds);//执行成功
}
}
return false;//执行失败
}
public static bool Do(DataSet ds)
{
SqlConnection conn1 = OAconn.conn();

//SqlConnection conNorthwind = new SqlConnection("Data Source=web1;User Id=UserName;Password=UserPassword;Integrated Security=no;");//链接字符串
SqlCommand commandNorthwind = new SqlCommand();
try
{
conn1.Open();
//conNorthwind.Open();//打开数据库链接
SqlTransaction tranNorthwind =conn1.BeginTransaction();//开始事务
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
SqlParameter[] parameters = null;//为了得到插入数据库的参数 定义参数对象 为空
string sql = GetSqlString(dr, out parameters);//执行sql -->用out关键字得到参数 赋到parameters对象上
//插入数据库中
PrepareCommand(commandNorthwind, conn1, tranNorthwind, sql, parameters);
commandNorthwind.ExecuteNonQuery();//执行操作
}
commandNorthwind.Transaction.Commit();//提交事务
conn1.Close();//关闭数据库链接资源
return true;
}
catch//如果有异常 不一定要捕捉异常 但要rollback事务
{
if (commandNorthwind.Transaction != null && conn1 != null)
{
commandNorthwind.Transaction.Rollback();//rollback事务
conn1.Close();//关闭数据库链接
}
return false;
}
}
/// <summary>
/// 每一行信息插入数据库中
/// </summary>
/// <param name="dr">要插入的这一行ds-datarow对象</param>
/// <returns>sql语句和用out关键字的参数数组对象</returns>
public static string GetSqlString(DataRow dr, out SqlParameter[] parameters)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO TEXT VALUES(:ID,:NAME)");
parameters = new SqlParameter[] { new SqlParameter(":ID", Convert.ToString(dr[0])), new SqlParameter(":NAME", Convert.ToString(dr[1])) };
return sb.ToString();//将sqlreturn出去
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
{
PrepareCommand(cmd, conn, trans, cmdText, CommandType.Text, cmdParms);
}
//参数设定 此方法被重载
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn,SqlTransaction trans, string cmdText, CommandType cmdType,SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.CommandType = cmdType; // CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parameter in cmdParms)
{
if (parameter != null)
{
if (parameter.Value == null)
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
}

private void button1_Click(object sender, System.EventArgs e)
{
openFileDialog1.Filter = "电子表格(*.xls)|*.xls";
openFileDialog1.ShowDialog();//打开对话框
this.textBox1.Text = openFileDialog1.FileName;//得到文件=路径+名称
}

private void button2_Click(object sender, System.EventArgs e)
{
try
{

DataSet ds = ImportExcel(this.textBox1.Text);//将excel的对象先放到ds 中
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)//如果ds中是有值的话 执行下面的操作
{
if (ExportInfo(ds))
{
MessageBox.Show("导入数据库成功!");
}
else
{
MessageBox.Show("导入数据库失败!");
}
}

}
}
catch
{

MessageBox.Show("导入数据库失败 请检查导入文件是否填写正确!");
}
}

/// <summary>
/// 点击导入文件到数据库中
/// 代云超
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>



}
}


单击button1可以实现选择文件功能，但是单击button2总是出现消息框提示说“导入数据库失败”，不知道为什么，请大家指点一下是哪里错了。

[binhe521]

跟踪一下嘛

[呦呦]

加个断点看看在哪里报的错

[呦呦]

 parameters = new SqlParameter[] { new SqlParameter(":ID", Convert.ToString(dr[0])),

ID是int类型吧？这个地方传参有问题

[zuopeixiao]

[Quote=引用 3 楼 happy_gril 的回复:]
C# code

parameters = new SqlParameter[] { new SqlParameter(":ID", Convert.ToString(dr[0])),


ID是int类型吧？这个地方传参有问题
[/Quote]

这个ID指的是什么？我不是很理解这段代码，能帮我解释下吗？

[呦呦]

[Quote=引用 4 楼 zuopeixiao 的回复:]
引用 3 楼 happy_gril 的回复:
C# code

这个ID指的是什么？我不是很理解这段代码，能帮我解释下吗？
[/Quote]
你数据库里的ID是什么类型的？如果是INT类型：

public static string GetSqlString(DataRow dr, out SqlParameter[] parameters)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO G_GoodsType VALUES(@ID,'@Code','@NAME','@Note')");
            parameters = new SqlParameter[] 
            { 
                new SqlParameter("@ID",SqlDbType.Int, Convert.ToInt32(dr[0])),
                new SqlParameter("@Code",SqlDbType.VarChar,32, Convert.ToString(dr[1])),
                new SqlParameter("@NAME",SqlDbType.VarChar,32, Convert.ToString(dr[2])),
                new SqlParameter("@Note",SqlDbType.VarChar,32, Convert.ToString(dr[3])) 
            };
            return sb.ToString();//将sqlreturn出去   
        }

[changjiangzhibin]

1 看你引用的DLL与读取的Excel是否对应 2003 - 11.0；2007 - 12.0

2 把Excel看成一个表格，与System.Data.DataTable对应就可以随意操作了

[changjiangzhibin]

补充一下：每个Sheet对应一个DataTable，Excel可对应成DataSet

[呦呦]

你的读EXCEL是没问题的，问题出在Do方法：修改如下

        public  bool Do(DataSet ds)
        {
            using (SqlConnection conn1 = new SqlConnection(DAO.connectionString))
            {
                if (conn1.State != ConnectionState.Open) conn1.Open();

                SqlCommand commandNorthwind = conn1.CreateCommand();
                commandNorthwind.CommandType = CommandType.Text;
                SqlTransaction tranNorthwind = conn1.BeginTransaction();//开始事务   
                commandNorthwind.Transaction = tranNorthwind;
                try
                {

                    for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
                    {
                        DataRow dr = ds.Tables[0].Rows[i];

                        string sql = "INSERT INTO G_GoodsType VALUES(" + int.Parse(dr[0].ToString())
                            + ",'" + dr[1].ToString() + "','" + dr[2].ToString() + "','" + dr[3].ToString() + "')  ";

                        commandNorthwind.CommandText = sql;
                        commandNorthwind.ExecuteNonQuery();//执行操作   
                    }

                    commandNorthwind.Transaction.Commit();//提交事务   
                    conn1.Close();//关闭数据库链接资源   
                    return true;
                }
                catch//如果有异常 不一定要捕捉异常 但要rollback事务   
                {
                    if (commandNorthwind.Transaction != null && conn1 != null)
                    {
                        commandNorthwind.Transaction.Rollback();//rollback事务   
                        conn1.Close();//关闭数据库链接   
                    }
                    return false;
                }
            }
            
        }

[isjoe]

首先把ExportInfo（）方法的异常信息报出来，别屏蔽他， 看看系统抛出的异常信息到底是什么，在做处理

try
{
}
catch(Exception me)
{
MessageBox。Show（ me.Message）;
}

[zuopeixiao]

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
namespace OA
{
public partial class Course : Form
{
public Course()
{
InitializeComponent();
}

public static DataSet ImportExcel(string file)
{
FileInfo fileInfo = new FileInfo(file);
if (!fileInfo.Exists) return null; string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
OleDbConnection objConn = new OleDbConnection(strConn);
DataSet dsExcel = new DataSet();
try
{
objConn.Open();
string strSql = "select * from [Sheet1$]";
OleDbDataAdapter odbcExcelDataAdapter = new OleDbDataAdapter(strSql, objConn);
odbcExcelDataAdapter.Fill(dsExcel); return dsExcel;
}
catch (Exception ex)
{
throw ex;
}
}

public static bool ExportInfo(DataSet ds)
{
//try
//{
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)//如果ds中是有值的话 执行下面的操作
{
return Do(ds);//执行成功
}
}
return false;//执行失败

//catch (Exception me)

//{
// MessageBox.Show(me.Message);
//}
}
public static bool Do(DataSet ds)
{

SqlConnection conn1 = OAconn.conn();
SqlCommand commandNorthwind = new SqlCommand();
try
{
conn1.Open();

SqlTransaction tranNorthwind = conn1.BeginTransaction();//开始事务
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
SqlParameter[] parameters = null;//为了得到插入数据库的参数 定义参数对象 为空
string sql = GetSqlString(dr, out parameters);//执行sql -->用out关键字得到参数 赋到parameters对象上
//插入数据库中
PrepareCommand(commandNorthwind, conn1, tranNorthwind, sql, parameters);
commandNorthwind.ExecuteNonQuery();//执行操作
}
commandNorthwind.Transaction.Commit();//提交事务
conn1.Close();//关闭数据库链接资源
return true;
}
catch//如果有异常 不一定要捕捉异常 但要rollback事务
{
if (commandNorthwind.Transaction != null && conn1 != null)
{
commandNorthwind.Transaction.Rollback();//rollback事务
conn1.Close();//关闭数据库链接
}
return false;
}
}

public static string GetSqlString(DataRow dr, out SqlParameter[] parameters)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO Class VALUES('@CID','@Cname','@Cabstruct','@TRID,'@Cremark'')");
parameters = new SqlParameter[]
{
new SqlParameter("@CID",SqlDbType.NVarChar,10, Convert.ToString(dr[0])),
new SqlParameter("@Cname",SqlDbType.NVarChar,50, Convert.ToString(dr[1])),
new SqlParameter("@Cabstruct",SqlDbType.NVarChar,50, Convert.ToString(dr[2])),
new SqlParameter("@TRID",SqlDbType.Int, Convert.ToInt32(dr[3])),
new SqlParameter("@Cremark",SqlDbType.NVarChar,50,Convert.ToString(dr[4])),
};
return sb.ToString();//将sqlreturn出去
}

private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
{
PrepareCommand(cmd, conn, trans, cmdText, CommandType.Text, cmdParms);
}
//参数设定 此方法被重载
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, CommandType cmdType, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.CommandType = cmdType; // CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parameter in cmdParms)
{
if (parameter != null)
{
if (parameter.Value == null)
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
}

private void button1_Click(object sender, System.EventArgs e)
{
openFileDialog1.Filter = "电子表格(*.xls)|*.xls";
openFileDialog1.ShowDialog();//打开对话框
this.textBox1.Text = openFileDialog1.FileName;//得到文件=路径+名称
}

private void button2_Click(object sender, System.EventArgs e)
{
try
{

DataSet ds = ImportExcel(this.textBox1.Text);//将excel的对象先放到ds 中
if (ds != null)
{

if (ExportInfo(ds))
{
MessageBox.Show("导入数据库成功!");
}
else
{
MessageBox.Show("导入数据库失败!");
}
}

}
catch
{

MessageBox.Show("导入数据库失败 请检查导入文件是否填写正确!");
}
}

}

}



改了好几次，还是不行。一运行就出现消息框提示“导入数据库失败！”，在
public static bool ExportInfo(DataSet ds)
处加断点后一步步执行到
if (ExportInfo(ds))
{
MessageBox.Show("导入数据库成功!");
}
的时候就出现下面的提示

在 System.Data.SqlClient.SqlException 中第一次偶然出现的“System.Data.dll”类型的异常。

找不到到底是哪里出错了，老师们帮帮忙啊，真的急用！