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 ) ) );
dtError.Columns.Add( new DataColumn( "出错信息" , typeof( string ) ) );
int sInsertCt = 0;
int sUpInsertCt = 0;
int existRecordCt = 0;
string kcid = SYKCDDL.SelectedValue;
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(); //姓名
int intPoints = 0;
try
{
intPoints = Convert.ToInt32( odr.Rows[i][2].ToString().Trim() ); //分数
}
catch
{
DataRow dr = dtError.NewRow();
dr[0] = strOutid;
dr[1] = strName;
dr[2] = odr.Rows[i][2].ToString().Trim();
dr[3] = "分数格式不正确";
}
dbModule dm = new dbModule();
int r =dm.updateXssyfs( strOutid , Convert.ToInt32( kcid ), intPoints );
if ( r == 1 )
//成功
{
sInsertCt++;
}
else
{
sUpInsertCt++;
DataRow dr = dtError.NewRow();
dr[0] = strOutid;
dr[1] = strName;
dr[2] = intPoints;
dr[3] = "学号不存在";
dtError.Rows.Add( dr );
}
}
if ( sInsertCt == nAllCount )
{
messageLabel.Text = nAllCount.ToString() + "条数据全部导入成功！";
}
else
{
messageLabel.Text = "总数据：" + nAllCount.ToString() + "条，导入成功：" + sInsertCt + "条，失败：" + Convert.ToString( ( sUpInsertCt + existRecordCt ) ) + "条";
}
Session["dtError"] = dtError;
System.IO.File.Delete( filePath );
////////////////////////////////
}
}
}