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 ) ) );
dtError.Columns.Add( new DataColumn( "出错信息" , typeof( string ) ) );
int sInsertCt = 0;
int sUpInsertCt = 0;
int existRecordCt = 0;
for ( int i = 0 ; i < odr.Rows.Count ; i++ )
{
string strOutid = odr.Rows[i][0].ToString().Trim(); //学号
string strName = odr.Rows[i][1].ToString().Trim(); //姓名
string strKcbh = odr.Rows[i][3].ToString().Trim(); //课程编号
string strJsbh = odr.Rows[i][2].ToString().Trim(); //任课教师工资号
dbModule dm = new dbModule();
int kcid =0;
try
{
kcid = Convert.ToInt32(dm.getKcidByKcbhJsid(strKcbh, strJsbh));
}
catch
{
sUpInsertCt++;
DataRow dr = dtError.NewRow();
dr[0] = strOutid;
dr[1] = strName;
dr[2] = "该教师或者该课程不存在";
dtError.Rows.Add(dr);
continue;
}
int r =dm.addXsXk( strOutid , Convert.ToInt32( kcid ) );
if ( r == 0 )
//成功
{
sInsertCt++;
}
else if ( r == 14 )
{
existRecordCt++;
DataRow dr = dtError.NewRow();
dr[0] = strOutid;
dr[1] = strName;
dr[2] = "记录已存在";
dtError.Rows.Add( dr);
}
else
{
sUpInsertCt++;
DataRow dr = dtError.NewRow();
dr[0] = strOutid;
dr[1] = strName;
dr[2] = "学号不存在";
dtError.Rows.Add( dr );
}
}
if ( sInsertCt == nAllCount )
{
messageLabel.Text = nAllCount.ToString() + "条数据全部导入成功！";
}
else
{
messageLabel.Text = "总数据：" + nAllCount.ToString() + "条，导入成功：" + sInsertCt + "条";
}
Session["dtError"] = dtError;
System.IO.File.Delete( filePath );
////////////////////////////////
}
}
}