将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" οnclick="doAdd();" value="添加" visible="false" /> <input id="btnDelete" type="button" runat="server" onserverclick="Delete_Click" visible="False" οnclick="doDelete();" value="删除" /> <input id="btnEdit" type="button" runat="server" οnclick="doEdit();" value="编辑" visible="false" /> <input id="btnCheck" type="button" runat="server" οnclick="doCheck();" onserverclick="Check_Click" value="审核" visible="false" /> <input id="BtnNew" type="button" runat="server" οnclick="doNew();" value="最新" visible="true" onserverclick="New_Click" /> <input id="btnElite" type="button" runat="server" οnclick="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" οnchange="__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" οnclick="CheckAll(form1)" οnfοcus="this.blur()" /></HeaderTemplate> <ItemStyle Width="10%" HorizontalAlign="Center" /> <ItemTemplate> <input type="checkbox" <%#GetChecked(Eval("ID")) %> name="checkboxlist" οnfοcus="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>