'Microsoft' 附近有语法错误。字符串 ',[Sheet1$])' 后的引号不完整
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSend_Click(object sender, EventArgs e)
{
try
{
//获取上传文件的名称
string upName = fupFileSend.FileName;
//获取上传文件的后缀名
//string nameLast = upName.Substring(upName.LastIndexOf("."));
//修改上传文件的名称
string fileName = txtName.Text;
//设置要保存的路径
string path = Server.MapPath("./")+"\\" + fileName;
//将文件保存到指定路径下
fupFileSend.PostedFile.SaveAs(path);
//定义Excel表
string StyleSheet = "Sheet1";
//调用自定义方法LoadData执行将Excel文件中数据导入到SQL Server数据库中
LoadData(StyleSheet);
RegisterStartupScript("true", "<script>alert('上传成功！')</script>");
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
RegisterStartupScript("true", "<script>alert('上传失败！')</script>");
}
}
public SqlConnection con()
{
return new SqlConnection(ConfigurationSettings.AppSettings["strCon"]);
}
public void LoadData(string StyleSheet)
{
//定义连接服务器中的Excel文件
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + Server.MapPath("2010级新生基本信息.xls") + ";Extended Properties=Excel 8.0";
//创建一个OleDbConnection链接对象
OleDbConnection myConn = new OleDbConnection(strCon);
myConn.Open(); //打开数据链接，得到一个数据集
DataSet myDataSet = new DataSet(); //创建DataSet对象
string StrSql = "select * from [" + StyleSheet + "$]";
OleDbDataAdapter myCommand = new OleDbDataAdapter(StrSql, myConn);
//添充数据集
myCommand.Fill(myDataSet, "[" + StyleSheet + "$]");
//释放占有的系统资源
myCommand.Dispose();
//创建一个DataTable内存表
DataTable DT = myDataSet.Tables["[" + StyleSheet + "$]"];
myConn.Close();
myCommand.Dispose();
string constr = "Data Source=ANSWER-PC\\SQLEXPRESS;Initial Catalog=";
constr = constr + "xinxi_sql;Integrated Security=True";
SqlConnection conn = new SqlConnection(constr);//建立SQL Server数据库连接
conn.Open();//打开SQL Server数据库连接
//循环读取Excel文件中的数据并添加到SQL Server数据库中
for (int j = 0; j < DT.Rows.Count; j++)
{
string sqlstr = "";
string diqu = DT.Rows[j][0].ToString(); //读取用户姓名
string sname = DT.Rows[j][1].ToString(); //读取考试题目
string ssex = DT.Rows[j][2].ToString(); //读取考试分数
string sbanji = DT.Rows[j][3].ToString(); //读取考试时间
string sno = DT.Rows[j][4].ToString();
string xingzhi = DT.Rows[j][5].ToString();
string minzu = DT.Rows[j][6].ToString();
string idcard = DT.Rows[j][7].ToString();
string address = DT.Rows[j][8].ToString();
string youbian = DT.Rows[j][9].ToString();
string sphone = DT.Rows[j][10].ToString();
string jiazhang = DT.Rows[j][11].ToString();
string jphone = DT.Rows[j][12].ToString();
string zhiwu = DT.Rows[j][13].ToString();
string tuantizhiwu = DT.Rows[j][14].ToString();
string shifoudaikuan = DT.Rows[j][15].ToString();
string shifoukunnansheng=DT.Rows[j][16].ToString();
string huojiang = DT.Rows[j][17].ToString();
string techang = DT.Rows[j][18].ToString();
string selsql = "select count(*) from ji_student where sname='" + sname + "'";
//判断是否已经将Excel文件中的数据导入到SQL Server数据库中
if (ExScalar(selsql) > 0)
{
Label1.Visible = true;
Label1.Text = "<script language=javascript>alert('该Excle中的数据已经导入数据库中!');location='ExcelToSQL.aspx';</script>";
}
else
{
string strNewFileName = Server.MapPath("2010级新生基本信息.xls");
//将从Excel文件中读取的用户姓名，试卷名称，考试分数，考试时间添加到SQL Server事先建立好的数据表ExcelData中
sqlstr = "insert into ji_student(diqu,sname,ssex,sbanji,sno,xingzhi,minzu,idcard,address,youbian,sphone,jiazhang,jphone,zhiwu,tuantizhiwu,shifoudaikuan,shifoukunnansheng,huojiang,techang)Values('" +diqu+ "','" + sname+ "','" + ssex+ "','" + sbanji+ "','"+sno+"','"+xingzhi+"','"+minzu+"','"+idcard+"','"+address+"','"+youbian+"','"+sphone+"','"+jiazhang+"','"+jphone+"','"+zhiwu+"','"+tuantizhiwu+"','"+shifoudaikuan+"','"+shifoukunnansheng+"','"+huojiang+"','"+techang+")";
sqlstr += "select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;DataBase=" + strNewFileName + "',[" + StyleSheet + "$])";
//创建命令对象
//DataAccess.Query(sqlstr);
SqlCommand mycom = new SqlCommand(sqlstr, conn);
//执行定义的插入操作的SQL语句
mycom.ExecuteScalar(); //读取到Excel文件中最后一条信息时，给予导入成功的提示信息
if (j == DT.Rows.Count - 1)
{
Label1.Visible = true;
Label1.Text = "<script language=javascript>alert('数据导入成功.');location='ExcelToSQL.aspx';</script>";
}
//数据读取失败
else
{
Label1.Visible = true;
Label1.Text = "<script language=javascript>alert('数据导入失败.');location='ExcelToSQL.aspx';</script>";
}
}
}
conn.Close();//关闭SQL Server数据库的连接
}
public int ExScalar(string sql)
{
string constr = "Data Source=ANSWER-PC\\SQLEXPRESS;Initial Catalog=";
constr = constr + "xinxi_sql;Integrated Security=True";
SqlConnection conn = new SqlConnection(constr);
conn.Open();
SqlCommand com = new SqlCommand(sql, conn);
return Convert.ToInt32(com.ExecuteScalar());
conn.Close();
}
当执行到红色标记语句时程序报错 错误提示为：'Microsoft' 附近有语法错误。字符串 ',[Sheet1$])' 后的引号不完整
[解决办法]
myCommand.Fill(myDataSet, "[" + StyleSheet + "$]");
//释放占有的系统资源
myCommand.Dispose();
//创建一个DataTable内存表
DataTable DT = myDataSet.Tables["[" + StyleSheet + "$]"];
这个地方怎么怪怪的。哪来这样多的[]
