java上传excel文件_java上传xls文件

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 schoolList = new List();private DataSet dsall = newDataSet();private int count = 0;private SchoolBll schoolBll = newSchoolBll();protected voidPage_Load(object sender, EventArgs e)

{

}//public void ReaderXls()//{//string xlsFilePath = @"F:\项目代码\公用架构\PublicProjectSolution\PublicProjectWeb\xls\DateReader.xls";//Excel//}

///

/// 单击导入按钮的事件

///

///

///

protected voidBtnImport_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;

}

}///

/// 上传Excel文件

///

/// 上传的控件名

///

privatestring 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= newSystem.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)

{throwex;

}returnorifilename;

}///

/// 将上传文件中的数据读取到数据库中

///

/// 上传的文件的地址

private voidImportXlsToData(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= newDataTable();

DataSet ds= newDataSet();

oleDBConn= newOleDbConnection(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= newOleDbDataAdapter(sqlMaster, oleDBConn);

oleAdMaster.Fill(ds,"m_tableName");

oleAdMaster.Dispose();

oleDBConn.Close();

oleDBConn.Dispose();

AddDatasetToSQL(ds,8);

}catch(Exception ex)

{throwex;

}

}///

/// 将Dataset的数据导入数据库

///

/// 数据集

/// 数据集列数

///

private bool AddDatasetToSQL(DataSet pds, intCols)

{intic, ir;

ic= pds.Tables[0].Columns.Count;if (pds.Tables[0].Columns.Count

{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= newSchool();

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;

}///

/// 插入数据到数据库

/// //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 voidgvgetall_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

}protected voidgvimport_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

}

}