easyexcel 到mysql 导入_Excel导入数据到数据库

///

///文件上传方法///

protected voidExcelUpload()

{//存放文件路径

String filepath = "";//存放文件扩展名

string fileExtName = "";//文件名

string mFileName = "";//服务器上的相对路径

string mPath = "";if (fu_excel.PostedFile.FileName != "")

{//取得文件路径

filepath =fu_excel.PostedFile.FileName;//取得文件扩展名

fileExtName = filepath.Substring(filepath.LastIndexOf(".") + 1);//取得服务器上的相对路径

mPath = this.Request.PhysicalApplicationPath + "UpLoadFiles\\Excel\\";//取得文件名

mFileName = filepath.Substring(filepath.LastIndexOf("\\") + 1);//保存文件到指定目录

if (!Directory.Exists(mPath))

{try{

Directory.CreateDirectory(mPath);

}catch{

MessageBox.Show(this.Page, "服务器创建存放目录失败");

}

}//如果文件已经存在则删除原来的文件

if (File.Exists(mPath +mFileName))

{try{

File.Delete(mPath+mFileName);

}catch{

MessageBox.Show(this.Page, "服务器上存在相同文件，删除失败。");

}

}#region 判断文件扩展名

//判断上传文件格式

Boolean fileOK = false;if(fu_excel.HasFile)

{

String fileExtension=System.IO.Path.GetExtension(fu_excel.FileName).ToLower();

String[] allowedExtensions= { ".xls"};for (int i = 0; i < allowedExtensions.Length; i++)

{if (fileExtension ==allowedExtensions[i])

{

fileOK= true;

}

}

}#endregion

#region 判断文件是否上传成功

//判断文件是否上传成功

bool fileUpOK = false;if(fileOK)

{try{//文件上传到服务器

fu_excel.PostedFile.SaveAs(mPath +mFileName);

fileUpOK= true;

}catch{

MessageBox.Show(this.Page,"文件上传失败！请确认文件内容格式符合要求！");

}

}else{

MessageBox.Show(this.Page,"上传文件的格式错误，应为.xls格式！");

}#endregion

#region 将Excel填充到数据集

//将Excel填充到数据集

if(fileUpOK)

{

System.Data.DataTable dt_User= newSystem.Data.DataTable();try{//获取Excel表中的内容

dt_User = GetList(mPath +mFileName);if (dt_User==null)

{

MessageBox.Show(this.Page, "获取Excel内容失败！");return;

}

}catch{

MessageBox.Show(this.Page,"获取Excel内容失败！");

}int rowNum = 0;try{

rowNum=dt_User.Rows.Count;

}catch{

MessageBox.Show(this.Page,"Excel表获取失败！");

}if (rowNum == 0)

{

MessageBox.Show(this.Page,"Excel为空表，无数据！");

}else{//数据保存

SaveToDataBase(dt_User);

}

}#endregion}

}#region 读取Excel表数据

///

///根据Excel文件路径读取Excel表中第一个表的内容///

/// Excel文件的物理路径

/// DataSet

public System.Data.DataTable GetList(stringFilePath)

{string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";" + "Extended Properties=Excel 8.0;";string strSql = string.Empty;//string workSheetName = Get_FistWorkBookName(FilePath);　　 //第一个工作表的名称。考虑到稳定性，就直接写死了。

string workSheetName = "Sheet1";if (workSheetName != "")

{

strSql= "select * from [" + workSheetName + "$]";try{

OleDbConnection conn= newOleDbConnection(connectionString);

conn.Open();

OleDbDataAdapter myCommand= null;

myCommand= newOleDbDataAdapter(strSql, connectionString);

System.Data.DataTable dt= newSystem.Data.DataTable();

myCommand.Fill(dt);

conn.Close();

conn.Dispose();returndt;

}catch(Exception)

{return null;

}

}else{return null;

}

}///

///根据EXCEL文件路径获取EXCEL第一个工作薄的表名///缺点：需要打开excel占用进程，暂不使用此方法///优点：更灵活，可以随意更改表名///

///

///

public string Get_FistWorkBookName(stringfileName)

{

Application app= newApplicationClass();

Workbook workBook=app.Workbooks.Add(Type.Missing); ;

Worksheet workSheet= (Worksheet)workBook.Sheets.get_Item(1);string rev = string.Empty;if (!File.Exists(fileName))throw new Exception("指定路径的Excel文件不存在！");

Workbook tmpworkBook;

Worksheet tmpworkSheet;try{object missing =System.Reflection.Missing.Value;//打开一个WorkBook

tmpworkBook =app.Workbooks.Open(fileName,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,true, missing);//tmpworkSheet = (Worksheet) workBook.Sheets.get_Item ( 1 );

app.Visible = false;

tmpworkSheet= (Worksheet)tmpworkBook.Worksheets[1];

rev=tmpworkSheet.Name;

}catch{

rev= "";

}finally{

tmpworkSheet= null;

tmpworkBook= null;this.Dispose();

}returnrev;

}#endregion