/// <summary>
/// 从Excel 导入学生
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnInport_Click(object sender, EventArgs e)
{
try
{
//取得用户选择的Excel文件的方法
string strFileName = GetUserSelectFile();//获得excel路径
//string strFileName = @"E:学士后录像二单元2开发分层架构Excel报表下载资料授课源代码授课源代码PB_StudentInport学生基本信息.xls";//获得excel路径
//如果文件不为空
if (strFileName.Equals(string.Empty))
{
return;
}
else
{
//从Excel中导入学生信息
ExcelHandle excelHandle = new ExcelHandle();
//GetStudentsFromExcel用这个方法把数据导入到 List<Student>集合中，两个参数一个是的到文件名，二个工作表的名字Sheet1，这个方法返回 List集合对象且保存数据到这个集合中
List<Student> students = excelHandle.GetStudentsFromExcel(strFileName, "Sheet1");
//判断如果这个集合有数据
if (students.Count > 0)
{
//导入学生信息
string messages = "";
//AddStudent添加的方法
int count = new StudentManager().AddStudent(students, ref messages);
if (count == students.Count)//全部导入成功
{
MessageBox.Show(INPORTSUCCESS, OPERATIONWARN, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
MessageBox.Show(messages, OPERATIOFAILED, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
//如果导入数据大于1条，重新绑定列表
if (count > 0)
{
this.BindStudents();
}
}
else
{
MessageBox.Show(EMPTYDATA, OPERATIOFAILED, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, OPERATIOFAILED, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
/// <summary>
/// 取得用户选择的Excel文件
/// </summary>
/// <returns>用户选择的文件名</returns>
public string GetUserSelectFile()
{
//实例化 OpenFileDialog对象，用于打开文件对话框
OpenFileDialog fileDialog = new OpenFileDialog();
fileDialog.InitialDirectory = "c:\";
fileDialog.Filter = "Microsoft Excel files (*.xls)|*.xls";
if (fileDialog.ShowDialog() == DialogResult.OK)
{
if ((fileDialog.OpenFile()) != null)
{
return fileDialog.FileName;
}
}
return string.Empty;
}
/// <summary>
/// 从Excel 获取学生数据
/// </summary>
/// <param name="filePath">带完整路径的Excel文件名</param>
/// <param name="sheetName">工作表名字</param>
/// <returns>学生实体集合</returns>
public List<Student> GetStudentsFromExcel(string filePath, string sheetName)
{
List<Student> students = new List<Student>();
Microsoft.Office.Interop.Excel.Application excel =
new Microsoft.Office.Interop.Excel.Application();
Worksheet sheet = null;
Range rNo = null;
try
{
excel.Workbooks.Open(filePath);//打开Excel 文件
foreach (Worksheet wsheet in excel.ActiveWorkbook.Sheets)
{
if (wsheet.Name == sheetName)
{
sheet = wsheet;
break;
}
}
//读取单元格数据
if (sheet != null)
{
int row = 2;//当前行号
while (true)
{
//如果碰到学号为空的行，则停止读取数据
rNo = sheet.Cells[row, 1] as Range;
if (rNo.Text.ToString().Trim().Length == 0)
{
break;
}
Student stu = new Student();
//学号
Range rContent = sheet.Cells[row, 1] as Range;
stu.StudentNo = Convert.ToInt32(rContent.Text.ToString().Trim());
//密码
rContent = sheet.Cells[row, 2] as Range;
stu.LoginPwd = rContent.Text.ToString().Trim();
//姓名
rContent = sheet.Cells[row, 3] as Range;
stu.StudentName = rContent.Text.ToString().Trim();
//性别
rContent = sheet.Cells[row, 4] as Range;
stu.Gender = rContent.Text.ToString().Trim() == "男" ? true : false;
//年级
rContent = sheet.Cells[row, 5] as Range;
stu.GradeId = new GradeManager().GetGradeId(rContent.Text.ToString().Trim());
//电话
rContent = sheet.Cells[row, 6] as Range;
stu.Phone = rContent.Text.ToString().Trim();
//地址
rContent = sheet.Cells[row, 7] as Range;
stu.Address = rContent.Text.ToString().Trim();
//出生年月日
rContent = sheet.Cells[row, 8] as Range;
stu.BornDate = Convert.ToDateTime(rContent.Text.ToString().Trim());
//邮箱
rContent = sheet.Cells[row, 9] as Range;
stu.Email = rContent.Text.ToString().Trim();
//身份证号
rContent = sheet.Cells[row, 10] as Range;
stu.IdentityCard = rContent.Text.ToString().Trim();
students.Add(stu);
row += 1;
}
}
excel.Visible = true;
//关闭WorkBook
excel.ActiveWorkbook.Close();
//关闭Excel
excel.Quit();
}
finally
{
//主动释放COM对象
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(excel);
Marshal.ReleaseComObject(rNo);
//对象引用设置null
sheet = null;
excel = null;
rNo = null;
//通过垃圾收集器回收对象
GC.Collect();
}
return students;
}
/// <summary>
/// 导入学生信息
/// </summary>
/// <param name="students">学生实体集合</param>
/// <param name="messages">消息字符串，必须为空字符串</param>
/// <returns></returns>
public int AddStudent(List<Student> students, ref string messages)
{
int i = 0;
foreach (Student stu in students)
{
string mess = "";
if (this.AddStudent(stu, ref mess) == false)
{
messages += string.Format("学号为{0}的数据错误，{1}；", stu.StudentNo, mess);
}
else//添加数据成功
{
i++;
}
}
return i;
}
/*************************************
* 类名：Student
* 功能描述：提供学生实体类
* ************************************/
namespace MySchool.Models
{
[Serializable]
public class Student
{
public int StudentNo{set;get;}
public string LoginPwd{set;get;}
public string StudentName{set;get;}
public bool Gender { set; get; }
public int GradeId { set; get; }
public string Phone { set; get; }
public string Address { set; get; }
public DateTime BornDate { set; get; }
public string Email { set; get; }
public string IdentityCard { set; get; }
}
}