asp.net 中上传并读取Excel文件（OLEDB方式）

采用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" > &nbsp;   &nbsp;   &nbsp;&nbsp;
                                 < 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;
        }
    }
}