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)
{
}
}