using System;  using System.Collections.Generic;  using System.Web;  using System.Web.UI;  using System.Web.UI.WebControls;  using PublicProject.ENTITY;  using System.Data;  using System.IO;  using System.Data.OleDb;  using PublicProject.BLL;  public partial class 使用Excel读取数据 : System.Web.UI.Page  {  //集合  private List<School> schoolList = new List<School>();  private DataSet dsall = new DataSet();  private int count = 0;  private SchoolBll schoolBll = new SchoolBll();  protected void Page_Load(object sender, EventArgs e)  {  }  //public void ReaderXls()  //{  //    string xlsFilePath = @"F:\项目代码\公用架构\PublicProjectSolution\PublicProjectWeb\xls\DateReader.xls";  //    Excel  //}  /// <summary>  /// 单击导入按钮的事件  /// </summary>  /// <param name="sender"></param>  /// <param name="e"></param>  protected void BtnImport_Click1(object sender, EventArgs e)  {  string fileName = string.Empty;  try {  fileName= UpLoadXls(FileExcel);  ImportXlsToData(fileName);//将XLS文件的数据导入数据库  if (fileName != string.Empty && System.IO.File.Exists(fileName))  {  System.IO.File.Delete(fileName);//删除上传的XLS文件  }  LblMessage.Text = "数据导入成功！";  this.gvimport.DataSource = schoolList;  this.gvimport.DataBind();  this.lbimport.Text = schoolList.Count.ToString();  int all = Convert.ToInt32(this.lballcount.Text.ToString());  int import = Convert.ToInt32(this.lbimport.Text.ToString());  int unimport = all - import;  if (unimport > count)  {  this.lbunimport.Text = "," + unimport.ToString() + "条，由于传输原因未导入到数据库！";  }  }  catch (Exception ex)  {  LblMessage.Text=ex.Message;  }  }  /// <summary>  /// 上传Excel文件  /// </summary>  /// <param name="inputfile">上传的控件名</param>  /// <returns></returns>  private string UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)  {  string orifilename = string.Empty;  string uploadfilepath = string.Empty;  string modifyfilename = string.Empty;  string fileExtend = "";//文件扩展名  int fileSize = 0;//文件大小  try {  if (inputfile.Value != string.Empty)  {  //得到文件的大小  fileSize = inputfile.PostedFile.ContentLength;  if (fileSize == 0)  {  throw new Exception("导入的Excel文件大小为0，请检查是否正确！");  }  //得到扩展名  fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);  if (fileExtend.ToLower() != "xls")  {  throw new Exception("你选择的文件格式不正确，只能导入EXCEL文件！");  }  //路径  uploadfilepath = Server.MapPath("~/xls/XlsUpLodes");  //新文件名  modifyfilename = System.Guid.NewGuid().ToString();  modifyfilename += "." + inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);  //判断是否有该目录  System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);  if (!dir.Exists)  {  dir.Create();  }  orifilename = uploadfilepath + "\\" + modifyfilename;  //如果存在,删除文件  if (File.Exists(orifilename))  {  File.Delete(orifilename);  }  // 上传文件  inputfile.PostedFile.SaveAs(orifilename);  }  else {  throw new Exception("请选择要导入的Excel文件!");  }  }  catch (Exception ex)  {  throw ex;  }  return orifilename;  }  /// <summary>  /// 将上传文件中的数据读取到数据库中  /// </summary>  /// <param name="fileName">上传的文件的地址</param>  private void ImportXlsToData(string fileName)  {  try {  if (fileName == string.Empty)  {  throw new ArgumentNullException("Excel文件上传失败！");  }  string oleDBConnString = String.Empty;  oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;";  oleDBConnString += "Data Source=";  oleDBConnString += fileName;  oleDBConnString += ";Extended Properties=Excel 8.0;";  OleDbConnection oleDBConn = null;  OleDbDataAdapter oleAdMaster = null;  DataTable m_tableName = new DataTable();  DataSet ds = new DataSet();  oleDBConn = new OleDbConnection(oleDBConnString);  oleDBConn.Open();  m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);  if (m_tableName != null && m_tableName.Rows.Count > 0)  {  m_tableName.TableName = m_tableName.Rows[0]["TABLE_NAME"].ToString();  }  string sqlMaster;  sqlMaster = " SELECT *  FROM [" + m_tableName.TableName + "]";  oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);  oleAdMaster.Fill(ds, "m_tableName");  oleAdMaster.Dispose();  oleDBConn.Close();  oleDBConn.Dispose();  AddDatasetToSQL(ds, 8);  }  catch (Exception ex)  {  throw ex;  }  }  /// <summary>  /// 将Dataset的数据导入数据库  /// </summary>  /// <param name="pds">数据集</param>  /// <param name="Cols">数据集列数</param>  /// <returns></returns>  private bool AddDatasetToSQL(DataSet pds, int Cols)  {  int ic, ir;  ic = pds.Tables[0].Columns.Count;  if (pds.Tables[0].Columns.Count < Cols)  {  throw new Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");  }  ir = pds.Tables[0].Rows.Count;  if (pds != null && pds.Tables[0].Rows.Count > 0)  {  for (int i = 0; i < pds.Tables[0].Rows.Count; i++)  {  School model = new School();  string schooolid = pds.Tables[0].Rows[i][3].ToString();  model.Email = pds.Tables[0].Rows[i][0].ToString();  model.Pass= pds.Tables[0].Rows[i][1].ToString();  model.RealName = pds.Tables[0].Rows[i][2].ToString();  model.School1 = pds.Tables[0].Rows[i][3].ToString();  model.ColSchool = pds.Tables[0].Rows[i][4].ToString();  model.SubName = pds.Tables[0].Rows[i][5].ToString();  model.GradeName = pds.Tables[0].Rows[i][6].ToString();  model.ClassName = pds.Tables[0].Rows[i][7].ToString();  //excel的格式：必须是英文列头  //EMAIL 密码     姓名       学校    学院     专业     年级   班级  //email  PASS  REAL_NAME SCHOOL  COL_NAME SUB_NAME GRADE  CLASS  // 0   1      2        3       4       5        6      7  schoolBll.Add(model);  schoolList.Add(model);  }  //全部信息  this.gvgetall.DataSource = schoolList;  this.gvgetall.DataBind();  dsall = pds;  this.lballcount.Text = pds.Tables[0].Rows.Count.ToString();  }  else {  throw new Exception("导入数据为空！");  }  return true;  }  /// <summary>  /// 插入数据到数据库  /// </summary>  //public void Add(ccwu.Model.T_RESUME_INFO model)  //{  //    string sql = "select * from T_STUDENT_INFO where email=‘" + model.EMAIL.ToString() + "‘";//根据一个号去查询  //    DataSet ds = ccwu.DBUtility.DbHelperSQL.Query(sql.ToString());  //    //  int count = 0;  //    if (ds.Tables[0].Rows.Count == 0)  //    {  //        //insert into PersonRecord  //        ccwu.DAL.T_STUDENT_INFO dalstudent = new ccwu.DAL.T_STUDENT_INFO();  //        ccwu.DAL.T_RESUME_INFO dalresume = new ccwu.DAL.T_RESUME_INFO();  //        ccwu.Model.T_STUDENT_INFO m = new ccwu.Model.T_STUDENT_INFO();  //        m.EMAIL = model.EMAIL.ToString();  //        m.PASS = model.pass.ToString();  //        m.SCHOOL_INFO_ID = Convert.ToInt32(model.SCHOOL_INFO_ID.ToString());  //        //向学生注册表里  //        int lastid = dalstudent.Add(m);  //        model.STUDENT_INFO_ID = lastid;  //        //向简历表里添加信息  //        dalresume.AddForAll(model);  //        list.Add(model);  //    }  //    if (ds.Tables[0].Rows.Count > 0)  //    {  //        count = count + 1;  //    }  //}  protected void gvgetall_PageIndexChanging(object sender, GridViewPageEventArgs e)  {  }  protected void gvimport_PageIndexChanging(object sender, GridViewPageEventArgs e)  {  }  }