C# 导入Excel 表数据
时间：2010-09-27 来源：裴鹏
protected void btnImport_Click(object sender, EventArgs e)
{
int j = 1;
DateTime str_date;
DataSet ds;
string IsXls = System.IO.Path.GetExtension(FileUpload.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
if (FileUpload.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
{
uploadErrorTxt = "请您选择Excel文件!<br/>";
}
else if (IsXls != ".xls" && IsXls != ".xlsx")
{
uploadErrorTxt += "上传的文件必须为.xls后缀!<br/>";
}
else
{
string filename = DateTime.Now.ToString("yyyyMMddhhMMss") + FileUpload.FileName;
string savePath = Server.MapPath(("~\\upfiles\\") + filename);
FileUpload.SaveAs(savePath);
try
{
ds = ExecleDs(savePath, filename);
}
catch
{
uploadErrorTxt = "导入失败，Excel工作表标签名错误，标签名必须是Sheet1，请查证后再导入！";
gotoError();
return;
}
DataRow[] dr = ds.Tables[0].Select();
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
uploadErrorTxt += "Excel表为空表,无数据!<br/>";
}
else
{
for (int i = 0; i < dr.Length; i++)
{
j = j + 1;
string str_id = dr[i]["ID"].ToString();
string str_name = dr[i]["名称"].ToString();
try
{
str_date = DateTime.Parse(dr[i]["时间"].ToString());
}
catch
{
uploadErrorTxt = "第" + j + "行时间格式有误，停止导入！";
gotoError();
return;
}
bool b = bll.Exists(str_id); //id 不存在
if (b == false)
{
try
{
model.id = int.Parse(str_id;);
}
catch
{
uploadErrorTxt = "第" + j + "行id格式错误，停止导入！";
gotoError();
return;
}
model.date = str_date;
model.name=str_name;
try
{
bll.Add(model);
uploadSucceedTxt = "Excle表导入成功!";
}
catch (MembershipCreateUserException ex)
{
uploadErrorTxt = "导入内容:" + ex.Message;
}
}
else
{
uploadErrorTxt += "id：" + str_id + "id重复禁止导入！<br/>";
}
}
}
}
if (uploadErrorTxt != "")
{
string txtPrint = "";
txtPrint += "<table width='100%' border='0' align='center' cellpadding='0' cellspacing='1' bgcolor='#BBC7CE' class='tab01'>";
txtPrint += "<tr>";
txtPrint += "<td bgcolor='#ffffff'>";
txtPrint += uploadErrorTxt;
txtPrint += "</td>";
txtPrint += "</tr>";
txtPrint += "</table>";
//输出错误提示
this.labErrorTxt.Text = txtPrint;
}
else
{
string txtPrint = "";
txtPrint += "<table width='100%' border='0' align='center' cellpadding='0' cellspacing='1' bgcolor='#BBC7CE' class='tab01'>";
txtPrint += "<tr>";
txtPrint += "<td bgcolor='#ffffff'>";
txtPrint += uploadSucceedTxt;
txtPrint += "</td>";
txtPrint += "</tr>";
txtPrint += "</table>";
//输出成功提示
this.labErrorTxt.Text = txtPrint;
InitDateBind();
}
}
void gotoError()
{
if (uploadErrorTxt != "")
{
string txtPrint = "";
txtPrint += "<table width='100%' border='0' align='center' cellpadding='0' cellspacing='1' bgcolor='#BBC7CE' class='tab01'>";
txtPrint += "<tr>";
txtPrint += "<td bgcolor='#ffffff'>";
txtPrint += uploadErrorTxt;
txtPrint += "</td>";
txtPrint += "</tr>";
txtPrint += "</table>";
//输出错误提示
this.labErrorTxt.Text = txtPrint;
InitDateBind();
}
}
public DataSet ExecleDs(string filenameurl, string table)
{
DataSet ds = new DataSet();
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + filenameurl + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
odda.Fill(ds, table);
return ds;
}
相关阅读 更多 +
