我想从Oracle中导出数据到excel中,在.net中实现,其代码如下:
其中BUSDISPACTH为数据库中的表,不知道哪里出了问题,老是报“参数量错误” ,求各位大侠帮帮忙 ,小女子不胜感激!!!
跪谢各位了!!! public void CreateExcel(string FileName)
{
try
{ string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FileUpload1.PostedFile.FileName + ";" + "Extended Properties=Excel 8.0;";//创建Excel的连接字符串
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
DataSet ds = new DataSet("BUSDISPATCH");
DataTable dt = ds.Tables["BUSDISPATCH"];
Response.Write("Hello");
cmd.CommandText = "CREATE TABLE [sheet1$] (序号 varchar(10), 派车时间 varchar(100),车牌号 varchar(10), 目的地 varchar(200), 驾驶员 char(10), 用车单位 char(2000),事由 char(2000) ,派车人 char(10) ,返回时间 char(100) , 批准人 char(10),备注 char(2000))";//为空白Excel创建表
cmd.ExecuteNonQuery();
foreach (DataRow row in dt.Rows)
{
cmd.CommandText = "INSERT INTO [sheet1$] (序号,派车时间,车牌号,目的地,驾驶员,用车单位,事由,派车人,返回时间,批准人,备注) VALUES ("
+ "'" + row["DISPATCHNO"].ToString().Trim() + "',"
+ "'" + row["DISPATCHTIME"].ToString().Trim() + "',"
+ "'" + row["BUSID"].ToString().Trim() + "',"
+ "'" + row["DESTINATION"].ToString().Trim() + "',"
+ "'" + row["DRIVER"].ToString().Trim() + "',"
+ "'" + row["USEDEPARTMENT"].ToString().Trim() + "',"
+ "'" + row["USECAUSE"].ToString().Trim() + "',"
+ "'" + row["DISPATCHER"].ToString().Trim() + "',"
+ "'" + row["BACKTIME"].ToString().Trim() + "',"
+ "'" + row["CERTIFIER"].ToString().Trim() + "',"
+ "'" + row["MORETHING"].ToString().Trim() + "')";
cmd.ExecuteNonQuery();//将DataTable中的数据一条条的保存到Excel中
}
conn.Close();
}
catch (Exception exp)
{
Response.Write("<center>系统出现以下错误:" + exp.Message + "!请尽快与管理员联系.</center>");
}
} protected void btnOutput_Click(object sender, EventArgs e)
{
//BindData();//数据绑定函数,获取最新的DataTable
CreateExcel("Sheet1.xls");
}
其中BUSDISPACTH为数据库中的表,不知道哪里出了问题,老是报“参数量错误” ,求各位大侠帮帮忙 ,小女子不胜感激!!!
跪谢各位了!!! public void CreateExcel(string FileName)
{
try
{ string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FileUpload1.PostedFile.FileName + ";" + "Extended Properties=Excel 8.0;";//创建Excel的连接字符串
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
DataSet ds = new DataSet("BUSDISPATCH");
DataTable dt = ds.Tables["BUSDISPATCH"];
Response.Write("Hello");
cmd.CommandText = "CREATE TABLE [sheet1$] (序号 varchar(10), 派车时间 varchar(100),车牌号 varchar(10), 目的地 varchar(200), 驾驶员 char(10), 用车单位 char(2000),事由 char(2000) ,派车人 char(10) ,返回时间 char(100) , 批准人 char(10),备注 char(2000))";//为空白Excel创建表
cmd.ExecuteNonQuery();
foreach (DataRow row in dt.Rows)
{
cmd.CommandText = "INSERT INTO [sheet1$] (序号,派车时间,车牌号,目的地,驾驶员,用车单位,事由,派车人,返回时间,批准人,备注) VALUES ("
+ "'" + row["DISPATCHNO"].ToString().Trim() + "',"
+ "'" + row["DISPATCHTIME"].ToString().Trim() + "',"
+ "'" + row["BUSID"].ToString().Trim() + "',"
+ "'" + row["DESTINATION"].ToString().Trim() + "',"
+ "'" + row["DRIVER"].ToString().Trim() + "',"
+ "'" + row["USEDEPARTMENT"].ToString().Trim() + "',"
+ "'" + row["USECAUSE"].ToString().Trim() + "',"
+ "'" + row["DISPATCHER"].ToString().Trim() + "',"
+ "'" + row["BACKTIME"].ToString().Trim() + "',"
+ "'" + row["CERTIFIER"].ToString().Trim() + "',"
+ "'" + row["MORETHING"].ToString().Trim() + "')";
cmd.ExecuteNonQuery();//将DataTable中的数据一条条的保存到Excel中
}
conn.Close();
}
catch (Exception exp)
{
Response.Write("<center>系统出现以下错误:" + exp.Message + "!请尽快与管理员联系.</center>");
}
} protected void btnOutput_Click(object sender, EventArgs e)
{
//BindData();//数据绑定函数,获取最新的DataTable
CreateExcel("Sheet1.xls");
}
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货