关于excel导入数据库的问题，请帮忙。
1、请教一下各位，这样的excel表，怎样用程序实现手动往数据库里导呀。
河北承德兴隆18C1890236----集团客户
河北承德隆化18C1890247----集团客户
2、我用toad以文本的方式导了一下，提示“-”错误，如果把-换成0就能导进去了。
[解决办法]
office 组件导入就行了啊，读取excle，插入到数据库 。你是怎么导入的
[解决办法]
3、 //Excel导入DtataSet操作。
public DataSet ExecleToDataSet(string filenameurl, string table)
{
DataSet ds = new DataSet();
try
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
odda.Fill(ds, table);
}
catch (Exception)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('Excel文件数据有误！');</script>");
}
return ds;
}
4、导入
//确定导入
protected void but_tmp_Import_Click(object sender, EventArgs e)
{
if (this.FileUpload1.FileName.ToString() == "")
{
Response.Write("<script language='javascript'>alert('导入文件不能为空，请先上传!');</script>");
}
if (this.FileUpload1.HasFile)
{
string fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
if (fileExtension != ".xls")
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('导入仅支持.xls文件,请重新选择！');</script>");
return;
}
else
{
string path = Server.MapPath("~/Excel/");
string filetime = DateTime.Now.ToString("yyyyMMddHHmmss");//时间字段区分文件（同名文件覆盖）
string fiepath = path + "\\" + filetime + "\\" + FileUpload1.FileName;
//string fiepath = path + "\\" + fu_Excel.FileName;
if (!System.IO.Directory.Exists(path + "\\" + filetime))
{
System.IO.Directory.CreateDirectory(path + "\\" + filetime);
}
FileUpload1.PostedFile.SaveAs(fiepath);
//上传操作。
DataSet ds = ExecleToDataSet(fiepath, FileUpload1.FileName);
if (ds.Tables.Count == 0)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script>Show('Excel表为空表,无数据！');</script>");
return;
}
DataRow[] dr = ds.Tables[0].Select();
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script>Show('Excel表为空表,无数据！');</script>");
return;
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(fiepath);
Aspose.Cells.Worksheets wsts = workbook.Worksheets;
if (wsts.Count > 0)
{
Aspose.Cells.Worksheet wst = wsts[0];
if (wst.Cells[0, 0].Value == null || wst.Cells[0, 0].Value.ToString().Trim() != "客户编号")
{
HQSoft.Common.MessageBox.Show(this, "对不起,您导入的Excel数据格式模板不正确，与第1列的列名“客户编号”不匹配，请修改正确后再重新导入！！");
return;
}
if (wst.Cells[0, 1].Value == null || wst.Cells[0, 1].Value.ToString().Trim() != "商品编号")
{
HQSoft.Common.MessageBox.Show(this, "对不起,您导入的Excel数据格式模板不正确，与第2列的列名“商品编号”不匹配，请修改正确后再重新导入！！");
return;
}
else
{
int Good = 0;
int Bad = 0;
string ss = "";
string sss = "";
for (int i = 0; i < dr.Length; i++)
{
string CustomerNo, ItemNo;
CustomerNo = dr[i]["客户编号"].ToString().Trim();
ItemNo = dr[i]["商品编号"].ToString().Trim();
if ((CustomerNo == "") || (CustomerNo == null))
{
sss += "第" + (i + 2) + "行客户编号不能为空，";
}
if ((ItemNo == "") || (ItemNo == null))
{
sss += "第" + (i + 2) + "行商品编号不能为空，";
}
else
{
try
{
//导入到SQL Server中
tmpModel.CustomerNo = CustomerNo;
tmpModel.ItemNo = ItemNo;
tmpModel.CreateDate = DateTime.Now.ToLocalTime();
tmpModel.CreateUserID = UserModel.UserID;
//if (tmpBll.GetRecordCount(" CustomerNo='" + CustomerNo + "'") > 0)
//{
// Bad += 1;
// ss += i + 1 + ",";
// sss += "第" + (i + 2) + "客户编号重复，";
//}
//else
//{
Good += 1;
tmpBll.Add(tmpModel);
//}
}
catch (Exception)
{
Response.Write("<script language='javascript'>alert('数据导入失败!');window.location='customer_select.aspx'</script>");
}
}
}
if (ss.Length > 0 || sss.Length > 0)
{
tmpBll.Delete();
HQSoft.Common.MessageBox.Show(this, "数据导入成功" + Good + ",失败" + Bad + ",错误行数" + ss + "错误详细" + sss);
ClientScript.RegisterStartupScript(ClientScript.GetType(), "myscript", "<script>shows();</script>");
}
else
{
this.GridView_tmpCustomerItem.DataSource = tmpBll.GetList("");
GridView_tmpCustomerItem.DataBind();
ClientScript.RegisterStartupScript(ClientScript.GetType(), "myscript", "<script>shows();</script>");
}
}
}
}
}
}
//正是表确定导入。
protected void Btn_Add_Click(object sender, EventArgs e)
{
HQSoft.HQBLL.TB_CustomerItem tbBll = new HQBLL.TB_CustomerItem();
HQSoft.HQModel.TB_CustomerItem tbModel = new HQModel.TB_CustomerItem();
for (int i = 0; i < this.GridView_tmpCustomerItem.Rows.Count; i++)
{
if (this.GridView_tmpCustomerItem.Rows[i].Cells[0].Text == " ")
{
this.GridView_tmpCustomerItem.Rows[i].Cells[0].Text = "";
}
else
{
tbModel.CustomerNo = this.GridView_tmpCustomerItem.Rows[i].Cells[0].Text.Trim().ToString();
}
if (this.GridView_tmpCustomerItem.Rows[i].Cells[1].Text == " ")
{
this.GridView_tmpCustomerItem.Rows[i].Cells[1].Text = "";
}
else
{
tbModel.ItemNo = this.GridView_tmpCustomerItem.Rows[i].Cells[1].Text.Trim().ToString();
}
tbModel.CreateUserID = UserModel.UserID;
tbModel.CreateDate = DateTime.Now.ToLocalTime();
tbBll.Add(tbModel);
}
tmpBll.Delete();
HQSoft.Common.MessageBox.Show(this, "数据导入成功!");
this.CustomerItem_Bind();
}