protected void addButton_Click( object sender , EventArgs e )
{
if ( !FileUpload1.HasFile )
{
Response.Write( "<script>alert('请选择EXCEL文件！');</script>" );
}
else
{
string strUpFilename = FileUpload1.FileName;
string fileFullName = strUpFilename;
string strExprentFile = strUpFilename.Substring( strUpFilename.LastIndexOf( "." ) + 1 );
//获取客户端上传文件的完整路径和部分路径
//cFullFilepath = FileUpload1.PostedFile.FileName;
//cFilepath = Path.GetDirectoryName(cFullFilepath) + "\\";
//string pathsf = Session["cFullFilepath"].ToString();
//判断上传文件是否是EXCEL文件
if ( strExprentFile != "xls" )
{
Response.Write( "<script>alert('请选择EXCEL类型的文件！')</script>" );
//Label5.Text = "请选择EXCEL类型的文件！";
//Label5.ForeColor = Color.Red;
}
else
{
//把文件上传到服务器
FileUpload1.SaveAs( Server.MapPath( "upexcel/" + strUpFilename ) );
ViewState["file"] = Server.MapPath( "upexcel/" + strUpFilename );
string filePath = Server.MapPath( "upexcel" ) + "\\" + fileFullName;
string fileName = System.IO.Path.GetFileNameWithoutExtension( filePath );
OleDbConnection oleConn = new OleDbConnection();
oleConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0; HDR=Yes; IMEX=1'";
try
{
oleConn.Open();
}
catch
{
Response.Write( "<script>alert('打开文件失败！请使用标准EXCEL模板！');</script>" );
return;
}
string strSheetName = "";
int nAllCount = 0;
OleDbCommand odc = new OleDbCommand();
odc.Connection = oleConn;
odc.CommandText = "select count(*) from [" + fileName + "$]";
try
{
nAllCount = int.Parse( odc.ExecuteScalar().ToString() );
strSheetName = "[" + fileName + "$]";
}
catch
{
odc.CommandText = "select count(*) from [Sheet1$]";
try
{
nAllCount = int.Parse( odc.ExecuteScalar().ToString() );
strSheetName = "[Sheet1$]";
}
catch
{
Response.Write( "<script>alert('无法打开指定工作表，工作表名必须为Sheet1和文件名一致！')</script>" );
//Label5.Text = "无法打开指定工作表，工作表名必须为Sheet1和文件名一致！";
//Label5.ForeColor = Color.Red;
odc.Dispose();
oleConn.Close();
return;
}
}
odc.CommandText = "select 学号,姓名,学院,专业,类别,手机号,邮件地址,年级 from " + strSheetName;
OleDbDataAdapter ada = new OleDbDataAdapter( odc.CommandText , oleConn );
DataTable odr = new DataTable();
try
{
//odr = odc.ExecuteReader();
ada.Fill( odr );
}
catch
{
Response.Write( "<script>alert('文件格式不正确！请使用标准EXCEL模板！')</script>" );
//Label5.Text = "文件格式不正确！";
//Label5.ForeColor = Color.Red;
odc.Dispose();
oleConn.Close();
System.IO.File.Delete( filePath );
return;
}
oleConn.Close();
DataTable dtError = new DataTable();
dtError.Clear();
dtError.Columns.Clear();
dtError.Columns.Add( new DataColumn( "错误行数" , typeof( string ) ) );
dtError.Columns.Add( new DataColumn( "错误描述" , typeof( string ) ) );
int sInsertCt = 0;
int sUpInsertCt = 0;
int existRecordCt = 0;
dbModule dm = new dbModule();
for ( int i = 0 ; i < odr.Rows.Count ; i++ )
{
string strXueHao = odr.Rows[i][0].ToString().Trim(); //学号
string strXingMing = odr.Rows[i][1].ToString().Trim(); //姓名
string strXueYuan = odr.Rows[i][2].ToString().Trim();//学院
int xyid = 0;
try
{
xyid = Convert.ToInt32( dm.getXyidByXymc( strXueYuan ) );
}
catch
{
DataRow dr = dtError.NewRow();
dr[0] = i + 1;
dr[1] = "学院不存在";
dtError.Rows.Add( dr );
continue;
}
string strZhuanYe = odr.Rows[i][3].ToString().Trim(); //专业
int zyid = 0;
try
{
zyid = Convert.ToInt32(dm.getZyidByZymc(strZhuanYe));
}
catch
{
DataRow dr = dtError.NewRow();
dr[0] = i + 1;
dr[1] = "专业不存在";
dtError.Rows.Add( dr );
continue;
}
string strLeiBie = odr.Rows[i][4].ToString().Trim(); //类别
int lbid = 0;
try
{
lbid = Convert.ToInt32(dm.getXslbidByLbmc(strLeiBie));
}
catch
{
DataRow dr = dtError.NewRow();
dr[0] = i + 1;
dr[1] = "学生类别不存在";
dtError.Rows.Add(dr);
continue;
}
string strShouji = odr.Rows[i][5].ToString().Trim(); //手机号
string strYoujian = odr.Rows[i][6].ToString().Trim(); //邮件地址
int nj = 0;
try
{
nj = Convert.ToInt32(odr.Rows[i][7].ToString().Trim());
}
catch
{
DataRow dr = dtError.NewRow();
dr[0] = i + 1;
dr[1] = "年级格式不正确";
dtError.Rows.Add(dr);
continue;
}
if (dm.addXsxx(strXueHao, strXingMing, strShouji, strYoujian, xyid, lbid, zyid, nj) == 0)
{
DataRow dr = dtError.NewRow();
dr[0] = i + 1;
dr[1] = "该学生已经存在";
dtError.Rows.Add(dr);
continue;
}
sInsertCt++;
////////////////////////////////
}
if ( sInsertCt == nAllCount )
{
messageLabel.Text = nAllCount.ToString() + "条数据全部导入成功！";
}
else
{
messageLabel.Text = "总数据：" + nAllCount.ToString() + "条，导入成功：" + sInsertCt + "条";
}
Session["dtError"] = dtError;
System.IO.File.Delete( filePath );
}
}
}