Excel的批量导入（web窗体的实现方式）

 废话少说，直接上代码。

1.当点击窗体上执行导入的按钮时

protected void BtnImport_Click(object sender, EventArgs e)
{
var extension = System.IO.Path.GetExtension(fileCard.FileName);
if (extension != null && extension.ToLower() != ".xls")
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "error", "<script>alert('请选择正确的模板文件，必须为 .xls格式！')</script>");
}
string localpath = Server.MapPath("/tmp" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
fileCard.SaveAs(localpath);//将上载文件的内容保存到Web服务器上的指定路径

try
{
string str = ImportCards(localpath);
if (str.IndexOf('|') > 0)
{
string[] arr = str.Split('|');
string alertstr = string.Format("导入成功{0}条！其中【{1}】,没有执行导入！", arr[0], arr[1]);
ClientScript.RegisterClientScriptBlock(this.GetType(), "error", "alert('" + alertstr + "');window.location=window.location;", true);
}
else
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "error", "<script>alert('导入成功！');window.location=window.location;</script>");
}
}
catch (Exception ex)
{
ClientScript.RegisterClientScriptBlock(this.GetType(), "error", "<script>alert('导入文件出错:" + ex.Message.Replace("'", "\\\'").Replace("\r\n", "") + "');</script>");
log4net.log4netHelper.errorFormat("导入文件出错:{0}", ex.ToString());
}
System.IO.File.Delete(localpath);
}

2.执行批量导入

public static string ImportCards(string xlsPath)
{
var devicelist = new List<MyDeviceMDL>();

var conn = new OleDbConnection();
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsPath + "; Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
try
{
conn.Open();
}
catch (Exception ex)
{
log4net.log4netHelper.error(ex.ToString());
throw;
}
try
{
string watchNos = "";
int success = 0;
int n = 0;
const string sql = "select * from [Sheet1$]";
var olecommand = new OleDbCommand(sql, conn);
OleDbDataReader reader = olecommand.ExecuteReader();
while (reader != null && reader.Read())
{
if (reader["设备编号"].ToString() == "" )
{
n++;
break;
}
var device = new MyDeviceMDL();

device.WatchNo = reader["设备编号"].ToString();
device.WatchVersion = reader["手环版本"].ToString();
device.WatchProduceDate = Convert.ToDateTime(reader["生产日期"].ToString());
device.EducationalCode = reader["学籍号"].ToString();
device.BluetoothAddress = reader["蓝牙地址"].ToString();
device.Imei = reader["Imei号"].ToString();
device.SimNo = reader["SimNo号"].ToString();//手表内部imei号
if (device.WatchNo != "")
{

devicelist.Add(device);
}

if (devicelist.Count <= 0)
{
throw new Exception("模板中没有发现可导入的数据！");
}

//导入
string ret = Import(device);
if (ret == "0")
{
success++;
}
else if (ret != "0" && ret != "-1")
{
watchNos += ret + ",";
}
}
if (watchNos.IndexOf(',') > 0)
{
watchNos = watchNos.Substring(0, watchNos.Length - 1);
}
if (watchNos != "")
{
return success.ToString() + "|" + watchNos;
}
else
{
return success.ToString();
}
}
catch (Exception ex)
{
conn.Close(); log4net.log4netHelper.error(ex.ToString());
throw ex;
}
finally
{
conn.Close();
}
}

3.向数据库中插入

private static string Import(MyDeviceMDL device)
{
string ret = "";

try
{
string WatchNo = device.WatchNo;
bool b = DeviceInfo.Exists(WatchNo);
if (!b)
{
//设备没有绑定用户，可以用
//new DeviceInfo().UpdateUserDevice0(device.Imei, device.EducationalCode);

/**根据学籍号查询信息**/

/***Imei 判断设备是否已添加到设备表中，如果已加了，先删，再导入***/

bool c = DeviceInfo.Existsdevice(WatchNo);

if (c)
{
new DeviceInfo().DeleteDevicebyImei(WatchNo);
}

DataTable dt = UserInfo.GetUserInfoByUserEduCode(device.EducationalCode);
if (dt != null && dt.Rows.Count > 0)
{
device.UserID = Convert.ToInt32(dt.Rows[0]["UserID"]);
device.ClassID = Convert.ToInt32(dt.Rows[0]["ClassID"] is DBNull ? 0 : dt.Rows[0]["ClassID"]);
device.SchoolID = Convert.ToInt32(dt.Rows[0]["SchoolID"] is DBNull ? 0 : dt.Rows[0]["SchoolID"]);
device.Created = DateTime.Now;
device.Modified = DateTime.Now;

var devicecode = Convert.ToString( dt.Rows[0]["DeviceCode"]);
/**判断用户是否已经有表**/
if (!string.IsNullOrEmpty(devicecode)) //有表
{
ret = device.EducationalCode + "此学号已经有设备";
}
else
{
new DeviceInfo().InsertDevice(device);
ret = "0";
//设备没有绑定用户，可以用
new DeviceInfo().UpdateUserDevice0(device.WatchNo, device.EducationalCode);
}

}
else
{
ret =device.EducationalCode+"此学号有误"; //学籍号有误，没有相对应的学生信息
}


}
else
{
ret = WatchNo + "此设备已经绑定用户"; // 此设备已经绑定用户
}

}
catch (Exception ex)
{
ret = "-1";
log4net.log4netHelper.error(ex.ToString());

}
log4net.log4netHelper.info(ret);
return ret;
}

注:仅供自己以后的学习使用