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 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;
          &nbsp;         }
                }
                catch (Exception dddd)
                ...{
                    bRet = false;
                    break;
                }
            }


            if (bRet == true)
            ...{
                File.Delete(file);
                Page.RegisterStartupScript("alert", "<script>alert(''導入成功!'');</script>");
                return;

            }
            else
            ...{
                File.Delete(file);
  &nbsp;             Page.RegisterStartupScript("alert", "<script>alert(''導入失敗，請檢查資料導入文件!'');</script>");
                return;
            }
        }
        catch (Exception ex)
        ...{
            string mes = "<script language=''Javascript''>window.alert(''" + ex.Message + "'');</script>";
            Response.Write(mes);
            return;
        }
    }
}