Question

asp.net Excel数据导入

= =我现在SQL SERVER中一共有60几个列名，但是平均每个Excel只有10列左右。。
string 车牌号码 = Convert.ToString(dr[i][1]);// 这个学号和课程号、成绩是excel中的列名,不能改，否则就会出错。请改成自己的excel中的相应列名
string 法人 = Convert.ToString(dr[i][6]);
string 车辆品牌 = Convert.ToString(dr[i][2]);
// string 车身颜色 = Convert.ToString(dr[i][]);
// string 车架号= Convert.ToString(dr[i][4]);
// string 发动机号=Convert.ToString(dr[i]["Chinese name"]);
string 出厂日期 = Convert.ToString(dr[i][10]);
// string 国产进口=
// string 进口凭证=
string 手机号=Convert.ToString(dr[i][9]);
按照这样的写法。。就算光改注释列都要很麻烦- = = 有没有好一点的写法？

Answer 1

public bool Exists(string DataID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from ** where DataID=" + DataID + "");
return DbHelperSQL.Exists(strSql.ToString());
}
/// <summary>
/// 删除一条数据
/// </summary>
public void Delete(string DataID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete ** ");
strSql.Append(" where DataID=" + DataID);
DbHelperSQL.ExecuteSql(strSql.ToString());
}
public void Add(** model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into **");
strSql.Append(" values (");
strSql.Append("'" + model.DataID + "',");
strSql.Append("'" + model.DataName + "',");
strSql.Append("'" + model.Unit + "',");
strSql.Append("'" + model.DataValue + "',");
strSql.Append("'" + model.ProviderName + "'");
strSql.Append(")");
DbHelperSQL.ExecuteSql(strSql.ToString());
}
protected void Button1_Click(object sender, EventArgs e)
{
string path = Request.MapPath("UploadFiles1")+ "\\DataStructure.xls";

FileUpload1.SaveAs(path);
string filename = path;
DataImport di = new DataImport();
DataTable dt;
try
{
dt = di.BuildMonthTableJM(path);
}
catch
{
Response.Write("<script language=javascript>");
Response.Write("alert('数据模版出错，无法生成表格!');");
Response.Write("</script>");
return;
}
GridView1.DataSource = dt;
GridView1.DataBind();
Session["MonthDataJM"] = dt;
dt.Dispose();
Button3.Visible = true;
}
protected void Button3_Click(object sender, EventArgs e)
{
//将数据表的数据导入到库中
DataTable MonthData = (DataTable)Session["MonthDataJM"];
try
{
int test = MonthData.Rows.Count;
}
catch
{
Response.Write("<script language=javascript>");
Response.Write("alert('请选择正确的模版并生成表格！');");
Response.Write("</script>");
return;
}
if (MonthData == null || MonthData.Rows.Count == 0)
{
Response.Write("<script language=javascript>");
Response.Write("alert('当前表格中导入数据出错，请检查导入数据!');");
Response.Write("</script>");
}
int i;
string incID;
int rowcount = MonthData.Rows.Count;
int columncount = MonthData.Columns.Count;
for (i = 0; i < rowcount; i++)//先删除已有的数据再添加
{
univarite mod = new univarite();
incID = MonthData.Rows[i]["DataID"].ToString();
if (Exists(incID) == true)
{
Delete(incID);
}
mod.DataID = MonthData.Rows[i]["DataID"].ToString();
mod.DataName = MonthData.Rows[i][1].ToString();
mod.Unit = MonthData.Rows[i][2].ToString();
mod.DataValue = MonthData.Rows[i][3].ToString();
mod.ProviderName = MonthData.Rows[i][4].ToString();
Add(mod);

StringBuilder strSql = new StringBuilder();
strSql.Append("insert into **");
strSql.Append(" values (");
strSql.Append(MonthData.Rows[i][0]);
for (int j = 1; j < columncount; j++)
{
strSql.Append(',');
strSql.Append(MonthData.Rows[i][j]);
}
strSql.Append(")");
DbHelperSQL.ExecuteSql(strSql.ToString());
}
//数据导入成功，进行提示

Response.Write("<script language=javascript>");
Response.Write("alert('表格中的数据已成功导入!');");
Response.Write("</script>");
}

Answer 2

先在类中定义一个方法名为ExecleDs的方法,用于将Excel表里的数据填充到DataSet中,代码如下

public DataSet ExecleDs(string filenameurl,string table)
{
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);
DataSet ds = new DataSet();
odda.Fill(ds,table);

return ds;

}

然后设计页面,在此作个简单的页面
<table style="width: 395px; height: 84px" border="1">
<tr>
<td style="width:380px" align="center">
批量导入用户信息</td>
</tr>
<tr>
<td style="width: 100px">
<asp:FileUpload ID="FileUpload1" runat="server" Width="380px" /></td>
</tr>
<tr>
<td style="width:380px" align="center">
<asp:Button ID="Button1" runat="server" Text="添加" OnClick="Button1_Click" /></td>
</tr>
</table>
<asp:Label ID="Label1" runat="server" Width="466px"></asp:Label>
当点击添加按钮时激发事件,代码如下
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile == false)
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
string IsXls=System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
if (IsXls != ".xls")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
string error = null;
Access.Class1 ac = new Access.Class1();
SqlConnection cn = ac.myConnection();
cn.Open();
string strpath = FileUpload1.PostedFile.FileName.ToString(); //获取Execle文件路径
string filename = FileUpload1.FileName; //获取Execle文件名
DataSet ds = ac.ExecleDs(strpath,filename);
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
for (int i = 0; i < dr.Length; i++)
{
string YHMC = dr[i]["YongHuMingCheng"].ToString();
string YHMM = dr[i]["YongHuMiMa"].ToString();
string DQRQ = dr[i]["DaoQiRiQi"].ToString();
string ZT = dr[i]["ZhuangTai"].ToString();
string TJSJ = dr[i]["TianJiaShiJian"].ToString();
string JXDM = dr[i]["JiaXiaoDaiMa"].ToString();
string sqlcheck = "select count(*) from DC_YongHuLieBiao where YongHuMingCheng='" + YHMC + "'And JiaXiaoDaiMa='" + JXDM + "'"; //检查用户是否存在
bool ch = ac.check(sqlcheck);
if (ch == true)
{
string insertstr = "insert into DC_YongHuLieBiao(YongHuMingCheng,YongHuMiMa,DaoQiRiQi,ZhuangTai,TianJiaShiJian,JiaXiaoDaiMa) values('" +
YHMC + "','" + YHMM + "','" + DQRQ + "','" + ZT + "','" + TJSJ + "','" + JXDM + "')";
SqlCommand cmd = new SqlCommand(insertstr,cn);
try
{
cmd.ExecuteNonQuery();
} catch (MembershipCreateUserException ex) //捕捉异常
{
Response.Write("<script>alert('创建用户:"+ex.Message+"')</script>");
}
}
else
{
error += "<em style='color:red;font-sixe:25px'>"+YHMC+"</em>用户已存在,此行记录无法插入!请修改用户再进行插入 <br>"; //若用户存在,将已存在用户信息打出,并提示此用户无法插入
continue;
}
}
Response.Write("<script>alert('Excle表导入成功!')</script>");
Label1.Text = error;
}
cn.Close();
}

追问

我知道怎么插入，但是SQL里面有60十几个列，，有N个Excel文件，每一个Excel平均只有10列左右。。所有很多Excel里面的列名是不同的。。如果要一条一条注释来插入的话。。太麻烦了

追答

要把excel表中的列和数据库中的列对应起来才能导入的啊

追问

是阿。。比如我现在SQL里有（车牌号码，车身颜色，车架号，发动机号)这几个字段。。。Excel1里面有(车牌号码，车身颜色，车架号)3个字段。。Excel2有（车牌号码，车架号，发动机号）这3个字段。。。有没有判断Excel中有没有车架号 这一列的函数。。有就可以判断做了。。不用一直改源代码了，简单很多，。。

Answer 3

excel直接导入外部数据啊

追问

= =。。是Excel导入到SQL 。。

追答

那就用Sql自带的导入工具
Import and export data

Answer 4

真不知道 怎么回答 求分！！！