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 );
}
}
}
protected void addButton_Click( object sender , EventArgs e )
{
Security s = Session["sec"] as Security;
if (s == null)
{
Response.Redirect("error.aspx");
}
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 strXueQi = odr.Rows[i][0].ToString().Trim(); //学期
string strXueNian = "";
int intXueQi = 0;
try
{
int nian1 = 2000 + Convert.ToInt32( strXueQi.Substring( 0 , 2 ) );
int nian2 = 2000 + Convert.ToInt32( strXueQi.Substring( 3 , 2 ) );
strXueNian = nian1.ToString() + "-" + nian2.ToString();
intXueQi = Convert.ToInt32( strXueQi.Substring( 7 , 1 ) );
}
catch
{
DataRow dr = dtError.NewRow();
dr[0] = i + 1;
dr[1] = "学期格式不正确";
dtError.Rows.Add( dr );
continue;
}
string strXueYuan = odr.Rows[i][1].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 strSyXueYuan = odr.Rows[i][2].ToString().Trim(); //实验学院
try
{
xyid = Convert.ToInt32( dm.getXyidByXymc( strSyXueYuan ) );
}
catch
{
DataRow dr = dtError.NewRow();
dr[0] = i + 1;
dr[1] = "学院不存在";
dtError.Rows.Add( dr );
continue;
}
string strKCBH = odr.Rows[i][3].ToString().Trim(); //课程号
string strKCMC = odr.Rows[i][4].ToString().Trim(); //课程名称
string strRKJS = odr.Rows[i][5].ToString().Trim(); //任课教师号
string strRKJSXM = odr.Rows[i][6].ToString().Trim(); //任课教师姓名
int kcxs = 0;//课程学时
try
{
kcxs = 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.addKcxx( strKCBH , strKCMC , strRKJS , "" , kcxs , 0, Int16.Parse(s.getUserXy()))==-1)
{
DataRow dr = dtError.NewRow();
dr[0] = i + 1;
dr[1] = "未找到教师信息";
dtError.Rows.Add(dr);
continue;
}
int kcid = Convert.ToInt32(dm.getKcidByKcbhJsid(strKCBH, strRKJS));
//////
string strSYYQ = odr.Rows[i][8].ToString().Trim();
int syyqid = dm.getSyyqidBySyyqmc( strSYYQ );
if ( syyqid == -1 )
{
DataRow dr = dtError.NewRow();
dr[0] = i + 1;
dr[1] = "课程属性不存在";
dtError.Rows.Add( dr );
continue;
}
string strSYLB = odr.Rows[i][9].ToString().Trim();
int sylbid = dm.getSylbidBySylbmc( strSYLB );
if ( sylbid == -1 )
{
DataRow dr = dtError.NewRow();
dr[0] = i + 1;
dr[1] = "课程类别不存在";
dtError.Rows.Add( dr );
continue;
}
string strSYMC = odr.Rows[i][10].ToString().Trim();
int syxs = 0;
try
{
syxs = Convert.ToInt32( odr.Rows[i][11].ToString().Trim() );
}
catch
{
DataRow dr = dtError.NewRow();
dr[0] = i + 1;
dr[1] = "实验学时格式不正确";
dtError.Rows.Add( dr );
continue;
}
string strSYLX = odr.Rows[i][12].ToString().Trim();
int sylxid = dm.getSylxidBySylxmc( strSYLX );
if ( sylxid == -1 )
{
DataRow dr = dtError.NewRow();
dr[0] = i + 1;
dr[1] = "实验类型不存在";
dtError.Rows.Add( dr );
continue;
}
//string strSYSLXMC = odr.Rows[i][13].ToString().Trim();
//int syslxid = dm.getSyslxidBySyslxmc( strSYSLXMC );
//if ( syslxid == -1 )
//{
// DataRow dr = dtError.NewRow();
// dr[0] = i + 1;
// dr[1] = "实验室不存在";
// dtError.Rows.Add( dr );
// continue;
//}
/////////添加实验信息
int rows = dm.addSyxx( strSYMC , strKCBH , sylxid , sylbid , syyqid , 11 , syxs , "" , "" , "" );
if (rows != 1)
{
DataRow dr = dtError.NewRow();
dr[0] = i + 1;
dr[1] = "该实验已存在！";
dtError.Rows.Add(dr);
continue;
}
//int syid = dm.getSyidBySymc( strSYMC );
//////////////////
//int syz = 0;
//try
//{
// syz = Convert.ToInt32( odr.Rows[i][14].ToString().Trim() );
//}
//catch
//{
// DataRow dr = dtError.NewRow();
// dr[0] = i + 1;
// dr[1] = "周次格式不正确";
// dtError.Rows.Add( dr );
// continue;
//}
//int syxingq = 0;
//try
//{
// syxingq = Convert.ToInt32( odr.Rows[i][15].ToString().Trim() );
//}
//catch
//{
// DataRow dr = dtError.NewRow();
// dr[0] = i + 1;
// dr[1] = "星期格式不正确";
// dtError.Rows.Add( dr );
// continue;
//}
//int syks = 0;
//try
//{
// syks = Convert.ToInt32( odr.Rows[i][16].ToString().Trim() );
//}
//catch
//{
// DataRow dr = dtError.NewRow();
// dr[0] = i + 1;
// dr[1] = "节次格式不正确";
// dtError.Rows.Add( dr );
// continue;
//}
//string strSYSDD = odr.Rows[i][17].ToString().Trim();
//int sysid = dm.getSysidBySysdd( strSYSDD );
//if ( sysid == -1 )
//{
// DataRow dr = dtError.NewRow();
// dr[0] = i + 1;
// dr[1] = "实验室地点不存在";
// dtError.Rows.Add( dr );
// continue;
//}
//int syrs = 0;
//try
//{
// syrs = Convert.ToInt32( odr.Rows[i][18].ToString().Trim() );
//}
//catch
//{
// DataRow dr = dtError.NewRow();
// dr[0] = i + 1;
// dr[1] = "实验人数不正确";
// dtError.Rows.Add( dr );
// continue;
//}
//string strSYBH = odr.Rows[i][20].ToString().Trim();
//string strSYJSID = odr.Rows[i][19].ToString().Trim() ;
//////添加实验计划
//int rows = dm.addSyjh( syid , strSYBH , strSYJSID , sysid , syrs , strXueNian , intXueQi , syz , syxingq , syks );
//if (rows != 1)
//{
// 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 );
}
}
}