将Excel导入到Access

<%@ Page Language="C#" Inherits="BaiJie.Web.WebBaseListPage" %>

<%@ Register Src="../ascpager.ascx" TagName="ascpager" TagPrefix="uc1" %>

<mce:script runat="server"><!--
    protected int typeID;
    
    protected override void Before_LoadData(WebBase info, bool isSearch, ref string table, ref string fields, ref string where, ref string orderby)
    {
        typeID = FormUtil.GetQueryInt("tid"); //get querystring's tid
        base.Before_LoadData(info, isSearch, ref table, ref fields, ref where, ref orderby);
        BJProduct product = info as BJProduct;
    }
    protected override void loadData(WebBase info)
    {


               BJProduct product = info as BJProduct;
        //base.loadData(info);
        DataTable dt;
        string table = "";
        string fields = "";
        string orderby = "OrderNo";
        string where = "cn";
        bool isSearch = (bool)ViewState["isSearch"];
        int ID = 0;
        product.Version = "cn";
        dt = WebManager.List(product, "BJProductView", "ID,ParentID,Title,ParentTitle,ModifyTime,CreateTime,Hits,IsNew,IsElite,OrderNo,IsChecked", "&version=", "OrderNo");
        GridView1.DataSource = dt.DefaultView;
        Ascpager1.DataSource = dt.DefaultView;
        GridView1.DataSource = Ascpager1.PageDataSource;
        GridView1.DataBind();
    
  
    }
    protected void dropCategoryID_SelectedIndexChanged(object sender, EventArgs e)
    {
        loadData(base.getWebType());
    }
    protected void dropOrder_SelectedIndexChanged(object sender, EventArgs e)
    {
        loadData(base.getWebType());
    }
    //先将Excel传到服务器上面
    protected void Button1_Click(object sender, EventArgs e)
    {
        string Excel = string.Empty;
            Boolean fileOK = false;
            String path = Server.MapPath("~/UploadExcel/");
            if (FileUpload1.HasFile)
            {
                String fileExtension =
                    System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                String[] allowedExtensions = 
                { ".xls" };
                for (int i = 0; i < allowedExtensions.Length; i++)
                {
                    if (fileExtension == allowedExtensions[i])
                    {
                        fileOK = true;
                    }
                }
            }

            if (fileOK)
            {
                try
                {
                    FileUpload1.PostedFile.SaveAs(path
                        + FileUpload1.FileName);
                    Excel = FileUpload1.PostedFile.FileName;
                    DataTableToDB(Excel);
                    //Label1.Text = "File uploaded!";
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                    Label1.Text = "上传失败！";
                }
            }
            else
            {
                Label1.Text = "文件格式不对.";
            }
       

       
    }
    public static void DataTableToDB(string Ex)
    {
        string _strExcelFileName = Ex;
        DataTable dtExcel = ExcelToDataTable(_strExcelFileName, "Sheet1");
        for (int i = 0; i < dtExcel.Rows.Count; i++)
        {
            InsertDataToAccess(dtExcel.Rows[i][1].ToString(), dtExcel.Rows[i][2].ToString(), dtExcel.Rows[i][3].ToString(), float.Parse(dtExcel.Rows[i][4].ToString()), dtExcel.Rows[i][5].ToString());
           
        }
    }
    //将Excel中的内容插入到DataTable中
    public static DataTable ExcelToDataTable(string strExcelFileName,string strSheetName)
    {
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties=Excel 5.0;";
        string strExcel = string.Format("select * from [{0}$]", strSheetName);
        DataSet ds = new DataSet();

        using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn))
        {
            conn.Open();
            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strExcel, strConn);
            adapter.Fill(ds);
            conn.Close();
        }

        return ds.Tables[0];
    }
    //将对应的信息插入到Access中去
    public static void InsertDataToAccess(string _title,string _category,string _tmp1,float _tmp2,string _tmp3)
    {
        System.Data.OleDb.OleDbConnection oleDbConn = new System.Data.OleDb.OleDbConnection();
        oleDbConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|BJData.mdb";
        oleDbConn.Open();
        string strInsertString = "INSERT INTO BJProduct (Title,ParentID,tmp1,tmp2,tmp3,Version) VALUES (@Title,@ParentID,@tmp1,@tmp2,@tmp3,@version)";
        System.Data.OleDb.OleDbCommand oComm = new System.Data.OleDb.OleDbCommand(strInsertString, oleDbConn);
        oComm.Parameters.Add("@Title", System.Data.OleDb.OleDbType.Char, 50);
        oComm.Parameters["@Title"].Value = _title;
        oComm.Parameters.Add("@ParentID", System.Data.OleDb.OleDbType.Integer);
        //把产品分类插入到产品分类表中CategoryID()返回一个ID就是当前产品的分类ID
        oComm.Parameters["@ParentID"].Value = CategoryID(_category);
        oComm.Parameters.Add("@tmp1", System.Data.OleDb.OleDbType.Char, 50);
        oComm.Parameters["@tmp1"].Value = _tmp1;
        oComm.Parameters.Add("@tmp2", System.Data.OleDb.OleDbType.Double);
        oComm.Parameters["@tmp2"].Value = _tmp2;
        oComm.Parameters.Add("@tmp3", System.Data.OleDb.OleDbType.Char,50);
        oComm.Parameters["@tmp3"].Value = _tmp3;
        oComm.Parameters.Add("@version", System.Data.OleDb.OleDbType.Char, 50);
        oComm.Parameters["@version"].Value = "cn";
        oComm.ExecuteNonQuery();
        oleDbConn.Close();
    }
    public static int CategoryID(string category)
    {
        int CID = 1;
        string s="cn";
        System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection();
        Conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|BJData.mdb";
        Conn.Open();
        string sqlcategory = "insert into BJProductCategory (Title,ParentID,Version) values('" + category + "'," + 1 + ",'"+s+"')";
        System.Data.OleDb.OleDbCommand Com = new System.Data.OleDb.OleDbCommand(sqlcategory, Conn);

        int i = Com.ExecuteNonQuery();
        if (i>0)
        { 
            //取出最大值就是当前插入的ID
            string str = "select max(ID) AS ID from BJProductCategory";
            System.Data.OleDb.OleDbCommand Cmd = new System.Data.OleDb.OleDbCommand(str, Conn);
            CID = Convert.ToInt32(Cmd.ExecuteScalar());
        }
        return CID;
        Conn.Close();
    }
// --></mce:script>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<html xmlns="http://www.w3.org/1999/xhtml">
<head>

    <mce:script type="text/javascript" src="script/util.js" mce_src="script/util.js"></mce:script>

    <mce:script type="text/javascript" src="script/list.js" mce_src="script/list.js"></mce:script>

    <mce:script type="text/javascript"><!--
if(top.location.href == self.location.href){top.location.href = "frame.aspx";}
// --></mce:script>

    <mce:script type="text/javascript"><!--
window.onerror = reportError;
// --></mce:script>

    <link href="style/style.css" mce_href="style/style.css" rel="stylesheet" type="text/css" />
    <title>产品管理</title>
</head>
<body>
    <form runat="server" id="form1">
        <div id="MainPageWrapper">
            <div id="optionRow">
                <table cellpadding="0" cellspacing="0" border="0" width="100%">
                    <tr>
                        <td style="width: 30%;">
                            <img src="images/a1.gif" mce_src="images/a1.gif" width="16" height="16" alt="dot" /><span class="optionTitle">产品管理</span>
                            --> 产品列表</td>
                        <td style="width: 70%; text-align: right;">
                            <input id="btnAdd" type="button" runat="server" onclick="doAdd();" value="添加" visible="false" />
                            <input id="btnDelete" type="button" runat="server" onserverclick="Delete_Click" visible="False"
                                onclick="doDelete();" value="删除" />
                            <input id="btnEdit" type="button" runat="server" onclick="doEdit();" value="编辑" visible="false" />
                            <input id="btnCheck" type="button" runat="server" onclick="doCheck();" onserverclick="Check_Click"
                                value="审核" visible="false" />
                            <input id="BtnNew" type="button" runat="server" onclick="doNew();" value="最新" visible="true"
                                onserverclick="New_Click" />
                            <input id="btnElite" type="button" runat="server" onclick="doElite();" value="推荐"
                                visible="true" onserverclick="Elite_Click" />
                          
                        </td>
                    </tr>
                    <tr>
                        <td class="liLineBackground" colspan="2">
                            </td>
                    </tr>
         <%--           <tr>
                        <td colspan="2">
                            产品排序:<select id="dropOrder" runat="server" onchange="__doPostBack('dropOrder',null)"
                                onserverchange="dropOrder_SelectedIndexChanged">
                                <option value="">排序</option>
                                <option value="IsTop">置顶</option>
                                <option value="IsNew">最新</option>
                                <option value="IsElite">推荐</option>
                                <option value="IsChecked">审核</option>
                            </select>&nbsp; &nbsp;&nbsp;&nbsp; 产品分类:<asp:DropDownList ID="dropCategoryID" AutoPostBack="true"
                                runat="server" OnSelectedIndexChanged="dropCategoryID_SelectedIndexChanged">
                            </asp:DropDownList>
                            &nbsp;&nbsp;&nbsp;
                            <asp:DropDownList ID="BJProduct_DDPCategory" runat="server" Width="88px">
                                <asp:ListItem>按标题</asp:ListItem>
                                <asp:ListItem>按关键字</asp:ListItem>
                                
                            </asp:DropDownList>&nbsp;
                            <asp:TextBox ID="BJProduct_Title" runat="server" Width="140px"></asp:TextBox><input
                                id="btnSearch" type="button" runat="server" onserverclick="Search_Click" value="搜索" /></td>
                    </tr>--%>
                    <tr>
                        <td colspan="2">
                            </td>
                    </tr>
                </table>
            </div>
            <div>
                导入文件
                <asp:FileUpload ID="FileUpload1" runat="server" /><asp:Button ID="Button1" runat="server"
                    Text="导入" OnClick="Button1_Click" />
                <asp:Label ID="Label1" runat="server" Text="请使用后缀为xls得文件"></asp:Label></div>
            <!--内容 -->
            <div style="text-align: center;" mce_style="text-align: center;">
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
                    Width="100%" DataKeyNames="ID" 
                    OnRowDataBound="GridView_RowDataBound"  CellPadding="2" CellSpacing="1"
                    BorderWidth="0px" CssClass="GridView1">
            
                    <Columns>
                        <asp:BoundField DataField="ID" HeaderText="ID">
                            <ItemStyle Width="30px" HorizontalAlign="Center" Height="20px" />
                        </asp:BoundField>
                        <asp:TemplateField HeaderText="标题">
                            <ItemTemplate>
                                <a href="bjproduct_edit.aspx?tid=<%=typeID%>&pid=<%#Eval("ParentID")%>&id=<%#Eval("ID")%>"
                                    title=" 标题名称：<%#Eval("Title")%>
 发布日期：<%#Eval("ModifyTime")%>
 访问次数：<%#Eval("Hits")%>">
                                    <%# FormUtil.SubString(Eval("Title"),30) %>
                                </a>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:BoundField DataField="ParentTitle" HeaderText="所属类别">
                            <ItemStyle HorizontalAlign="Center" Width="70px" />
                        </asp:BoundField>
                        <asp:BoundField DataField="CreateTime" HeaderText="发布时间" HtmlEncode="False" DataFormatString="{0:yyyy-MM-dd}">
                            <ItemStyle Width="90px" HorizontalAlign="Center" />
                        </asp:BoundField>
                        <asp:BoundField DataField="Hits" HeaderText="点击数" />
                        <asp:TemplateField HeaderText="推荐">
                            <ItemStyle Width="30px" HorizontalAlign="Center" />
                            <ItemTemplate>
                                <%# Convert.ToBoolean(Eval("IsElite")) ? "<span class=\"cGreen\">√</span>" : "<span class=\"cRed\">×</span>"%>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="最新">
                            <ItemStyle Width="30px" HorizontalAlign="Center" />
                            <ItemTemplate>
                                <%# Convert.ToBoolean(Eval("IsNew")) ? "<span class=\"cGreen\">√</span>" : "<span class=\"cRed\">×</span>"%>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:BoundField DataField="OrderNo" HeaderText="排序">
                            <ItemStyle Width="40px" HorizontalAlign="Center" />
                        </asp:BoundField>
                        <asp:TemplateField HeaderText="审核">
                            <ItemStyle Width="34px" HorizontalAlign="Center" />
                            <ItemTemplate>
                                <%# Convert.ToBoolean(Eval("IsChecked")) ? "<span class=\"cGreen\">√</span>" : "<span class=\"cRed\">×</span>"%>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="选择">
                            <HeaderTemplate>
                                <input id="chkall" name="chkall" type="checkbox" onclick="CheckAll(form1)" onfocus="this.blur()" /></HeaderTemplate>
                            <ItemStyle Width="10%" HorizontalAlign="Center" />
                          <ItemTemplate>
              <input type="checkbox" <%#GetChecked(Eval("ID")) %> name="checkboxlist" onfocus="this.blur()" />
          </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <EmptyDataTemplate>
                        <div class="NoRecord">
                            没有找到相关信息!</div>
                    </EmptyDataTemplate>
                    <FooterStyle CssClass="footerStyle" />
                    <RowStyle CssClass="rowStyle" />
                    <SelectedRowStyle CssClass="selectedRowStyle" />
                    <PagerStyle CssClass="pagerStyle" />
                    <HeaderStyle CssClass="headerStyle" />
                    <AlternatingRowStyle CssClass="alternatingRowStyle" />
                </asp:GridView>
            </div>
            <div id="divTotal" align="center">
                <uc1:ascpager ID="Ascpager1" runat="server" />
                <asp:Label ID="lblTotal" runat="Server"></asp:Label></div>
          
            <div id="div1" align="center">
                <br />
                <br />
                <asp:Label ID="lblMessage" runat="Server" CssClass="red font14"></asp:Label>
            </div>
        </div>
    </form>
</body>
</html>