我要把excel中的数据插入到sql中,excel中有个“数量”列,里面的数据如果有一个不为数字类型的话,那么他就不能插入到sql中。
现在我如何能找出这个不匹配的数据?
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
FileUpload fileUpload = (FileUpload)this.FileUpload1;
if (fileUpload.PostedFile.FileName != "")
{
this.Label1.Text = fileUpload.PostedFile.FileName; OleDbConnection DBConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + this.Label1.Text + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes\"");
DBConnection.Open();
string SQLString = "SELECT * FROM [Sheet1$]";
OleDbCommand DBCommand = new OleDbCommand(SQLString, DBConnection);
OleDbDataAdapter myDa = new OleDbDataAdapter();
myDa.SelectCommand = DBCommand;
DataSet myDs = new DataSet();
myDa.Fill(myDs, "[Sheet1$]");
if (myDs.Tables["[Sheet1$]"].Columns.Contains("数量"))
{
string strsql1 = "SELECT sum(数量) from [Sheet1$]";
myDa = new OleDbDataAdapter(strsql1, DBConnection);
myDs = new DataSet();
myDa.Fill(myDs, "[Sheet1$]");
if (Convert.IsDBNull(myDs.Tables[0].Rows[0][0]) == false)
{
this.gs.Text = myDs.Tables[0].Rows[0].ItemArray[0].ToString();
}
else
{
this.gs.Text = "0.00";
}
DBConnection.Close();
}
else
{
this.gs.Text = "0.00";
Response.Write("<script>alert('表内数据列名不匹配,请确认您所选择的表是否正确!');</script>");
} }
else
{
this.Label1.Text = "";
Response.Write("<script>alert('请选择文件所在路径!');</script>");
} }
现在我如何能找出这个不匹配的数据?
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
FileUpload fileUpload = (FileUpload)this.FileUpload1;
if (fileUpload.PostedFile.FileName != "")
{
this.Label1.Text = fileUpload.PostedFile.FileName; OleDbConnection DBConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + this.Label1.Text + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes\"");
DBConnection.Open();
string SQLString = "SELECT * FROM [Sheet1$]";
OleDbCommand DBCommand = new OleDbCommand(SQLString, DBConnection);
OleDbDataAdapter myDa = new OleDbDataAdapter();
myDa.SelectCommand = DBCommand;
DataSet myDs = new DataSet();
myDa.Fill(myDs, "[Sheet1$]");
if (myDs.Tables["[Sheet1$]"].Columns.Contains("数量"))
{
string strsql1 = "SELECT sum(数量) from [Sheet1$]";
myDa = new OleDbDataAdapter(strsql1, DBConnection);
myDs = new DataSet();
myDa.Fill(myDs, "[Sheet1$]");
if (Convert.IsDBNull(myDs.Tables[0].Rows[0][0]) == false)
{
this.gs.Text = myDs.Tables[0].Rows[0].ItemArray[0].ToString();
}
else
{
this.gs.Text = "0.00";
}
DBConnection.Close();
}
else
{
this.gs.Text = "0.00";
Response.Write("<script>alert('表内数据列名不匹配,请确认您所选择的表是否正确!');</script>");
} }
else
{
this.Label1.Text = "";
Response.Write("<script>alert('请选择文件所在路径!');</script>");
} }
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货