采用OLEDB方式将Excel文件上传到服务器指定的临时目录,并读取Excel文件内容保存到服务器的数据库中。
开发环境:VS2005, ASP.net, C#, SQL2005
页面代码 :
<
table
width
="100%"
>
<
tr
>
<
td
height
="28"
bgcolor
="#5BB5D2"
>
<
div
align
="center"
class
="cubai9pt"
>
酒店基本信息Excel导入
</
div
>
</
td
>
</
tr
>
<
tr
>
<
td
valign
="top"
>
<
table
width
="100%"
cellspacing
="0"
>
<
tr
bgcolor
="#ffffff"
class
="song"
>
<
td
height
="30"
>
<
div
align
="center"
>
Excel酒店文件:
<
asp:FileUpload
ID
="FileUpload1"
runat
="server"
Width
="583px"
/></
div
>
</
td
>
<
td
height
="30"
colspan
="3"
>
<
span
class
="song"
>
<
input
name
="Submit"
type
="submit"
class
="an"
value
="保存"
id
="SaveInfo"
runat
="server"
onserverclick
="SaveInfo_ServerClick"
/></
span
></
td
>
</
tr
>
</
table
>
</
td
>
</
tr
>
<
tr
>
<
td
height
="28"
>
<
div
align
="center"
class
="red"
>
注意:在没有出现导入成功界面之前,请不要刷新该页面!
</
div
>
</
td
>
</
tr
>
</
table
>
页面类代码:
using
System;
using
System.Data;
using
System.Configuration;
using
System.Collections;
using
System.Collections.Generic;
using
System.Web;
using
System.Web.Security;
using
System.Web.UI;
using
System.Web.UI.WebControls;
using
System.Web.UI.WebControls.WebParts;
using
System.Web.UI.HtmlControls;
using
BILL;
using
Model;
using
Component;
using
System.IO;
using
System.Reflection;
using
System.Data.OleDb;

public
partial
class
Admin_Hotel_UploadExcelHotelRoom : System.Web.UI.Page

...
{
protected void Page_Load(object sender, EventArgs e)

...{

}

protected void SaveInfo_ServerClick(object sender, EventArgs e)

...{
string file = "";

if (FileUpload1.HasFile)

...{
file = System.Web.HttpContext.Current.Request.MapPath("~/temp/") + CommonSet.CreateRandFileName(FileUpload1.FileName);
FileUpload1.SaveAs(file);
}
else

...{
Page.RegisterStartupScript("alert", "<script>alert('请选择Excel文件!!');</script>");
return;
}

if (!File.Exists(file))

...{
Response.Write("<script language='javascript'>window.alert('Excel文件上传失败!');</script>");
return;
}
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + file + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = null;
DataSet ds = null;
try

...{
conn = new OleDbConnection(strConn);
conn.Open();

string strExcel = "";
OleDbDataAdapter myCommand = null;

strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
}
catch (Exception x)

...{
if (conn.State == ConnectionState.Open)

...{
conn.Close();
}
File.Delete(file);
Page.RegisterStartupScript("alert", "<script>alert('Excel文件格式错误!');</script>");
return;
}

//IList<HotelAddin> info = new List<HotelAddin>();
IList<HotelRoomInfo> info = new List<HotelRoomInfo>();
IList<string> lBed = new List<string>();

try

...{
for (int i = 0; i < ds.Tables["table1"].Rows.Count; i++)

...{
HotelRoomInfo temp = new HotelRoomInfo();

string tt = ds.Tables["table1"].Rows[i].ItemArray.GetValue(0).ToString();
if ((tt == null) || (tt.Trim() == ""))
break;
temp.HotelId = ds.Tables["table1"].Rows[i].ItemArray.GetValue(0).ToString();
temp.RoomId = ds.Tables["table1"].Rows[i].ItemArray.GetValue(2).ToString();
temp.RoomName = ds.Tables["table1"].Rows[i].ItemArray.GetValue(3).ToString();
temp.TypeName = ds.Tables["table1"].Rows[i].ItemArray.GetValue(4).ToString();
temp.IsDisp = Convert.ToInt32(ds.Tables["table1"].Rows[i].ItemArray.GetValue(5).ToString());
temp.IsHave = Convert.ToInt32(ds.Tables["table1"].Rows[i].ItemArray.GetValue(6).ToString());
temp.IsKitchen = Convert.ToInt32(ds.Tables["table1"].Rows[i].ItemArray.GetValue(7).ToString());
temp.IsAddinBed = Convert.ToInt32(ds.Tables["table1"].Rows[i].ItemArray.GetValue(8).ToString());
temp.NormalNo = Convert.ToInt32(ds.Tables["table1"].Rows[i].ItemArray.GetValue(9).ToString());
temp.MaxNo = Convert.ToInt32(ds.Tables["table1"].Rows[i].ItemArray.GetValue(10).ToString());
temp.IsRecommend = Convert.ToInt32(ds.Tables["table1"].Rows[i].ItemArray.GetValue(11).ToString());
temp.MemPointRate = Convert.ToDecimal(ds.Tables["table1"].Rows[i].ItemArray.GetValue(12).ToString());
temp.ExploitRate = Convert.ToDecimal(ds.Tables["table1"].Rows[i].ItemArray.GetValue(13).ToString());
temp.AffiliateRate = Convert.ToDecimal(ds.Tables["table1"].Rows[i].ItemArray.GetValue(14).ToString());
temp.IsBreakfast = Convert.ToInt32(ds.Tables["table1"].Rows[i].ItemArray.GetValue(15).ToString());
temp.MinNights = Convert.ToInt32(ds.Tables["table1"].Rows[i].ItemArray.GetValue(16).ToString());
temp.Remark = ds.Tables["table1"].Rows[i].ItemArray.GetValue(17).ToString();
temp.LastUpdateTime = DateTime.Now;
string bed = ds.Tables["table1"].Rows[i].ItemArray.GetValue(18).ToString();

info.Add(temp);
lBed.Add(bed);
}

conn.Close();

//保存到数据库
bool bRet = true;
BillHotelRoom billRoom = new BillHotelRoom();
BillHotelRoomBed billBed = new BillHotelRoomBed();
for (int i = 0; i < info.Count; i++)

...{
try

...{
//billRoom.AddHotelRoom(
IList<HotelRoomBedInfo> lBedInfo = new List<HotelRoomBedInfo>();


string[] hBed = lBed[i].Split(new Char[] ...{ '#' });
for (int j = 0; j < hBed.Length; j++)

...{
HotelRoomBedInfo fac = new HotelRoomBedInfo();
fac.HotelId = info[i].HotelId;
fac.RoomId = info[i].RoomId;
fac.BedInfo = hBed[j];
lBedInfo.Add(fac);
}

if( billRoom.AddHotelRoom(info[i], lBedInfo) == false )

...{
bRet = false;
break;
}
}
catch (Exception dddd)

...{
bRet = false;
break;
}
}


if (bRet == true)

...{
File.Delete(file);
Page.RegisterStartupScript("alert", "<script>alert('导入成功!');</script>");
return;

}
else

...{
File.Delete(file);
Page.RegisterStartupScript("alert", "<script>alert('导入失败,请检查资料导入文件!');</script>");
return;
}
}
catch (Exception ex)

...{
string mes = "<script language='javascript'>window.alert('" + ex.Message + "');</script>";
Response.Write(mes);
return;
}
}
}

