读入excel中的数据到数据库中

项目中用到大量的导入数据，比如导入订单，导入供应商，导入用户等等，现以导入供应商为例：

页面如下：

代码如下：

页面后台

  1  public partial class ImportSupplyInfo : System.Web.UI.Page
  2     {
  3         protected void Page_Load(object sender, EventArgs e)
  4         {
  5 
  6         }
  7         public DataTable GetExcelData(string filePath)
  8         {
  9             string oledbString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;'", filePath);
 10             using (OleDbConnection con = new OleDbConnection(oledbString))
 11             {
 12                 con.Open();
 13                 DataTable dtTableNames = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
 14                 if (dtTableNames == null || dtTableNames.Rows.Count <= 0)
 15                 {
 16                     return new DataTable();
 17                 }
 18                 DataSet ds = new DataSet();
 19                 string oledbSql = "SELECT * FROM [" + dtTableNames.Rows[0]["TABLE_NAME"].ToString().Trim() + "]";
 20                 OleDbDataAdapter da = new OleDbDataAdapter(oledbSql, con);
 21                 da.Fill(ds);
 22                 if (ds == null || ds.Tables.Count <= 0 || ds.Tables[0].Rows.Count <= 0)
 23                 {
 24                     return new DataTable();
 25                 }
 26                 return ds.Tables[0];
 27             }
 28         }
 29         public int ValidateExcelFile(string filename)
 30         {
 31             string extentsion = Path.GetExtension(filename);
 32             if (extentsion.ToLower() != ".xls")
 33             {
 34                 return 1;
 35             }
 36             return 0;
 37         }
 38         protected void btnSave_Click(object sender, EventArgs e)
 39         {
 40             try
 41             {
 42                 if (!this.fileupload1.HasFile)
 43                 {
 44                     string script = "请选择上传文件";
 45                     ScriptManager.RegisterStartupScript(Page, typeof(Page), DateTime.Now.ToString(), script, true);
 46                     return;
 47                 }
 48                 string fileFullName = this.fileupload1.PostedFile.FileName;
 49                 int fileState = ValidateExcelFile(fileFullName);
 50                 if (fileState != 0)
 51                 {
 52                     string script = "上传的文件格式错误";
 53                     ScriptManager.RegisterStartupScript(Page, typeof(Page), DateTime.Now.ToString(), script, true);
 54                     return;
 55                 }
 56                 string extension = Path.GetExtension(fileFullName);
 57                 string serverPath = Server.MapPath(@"~\upload\") + DateTime.Now.ToString("yyyyMMddHHmmss") + extension;
 58                 this.fileupload1.SaveAs(serverPath);
 59                 DataTable dt = GetExcelData(serverPath);
 60                 if (dt == null || dt.Rows.Count <= 0)
 61                 {
 62                     string script = "上传文件数据不能为空";
 63                     ScriptManager.RegisterStartupScript(Page, typeof(Page), DateTime.Now.ToString(), script, true);
 64                     return;
 65                 }
 66                 for (int i = 0; i < dt.Rows.Count; i++)
 67                 {
 68                     if (string.IsNullOrEmpty(dt.Rows[i]["供应商名称"].ToString().Trim()) && (!string.IsNullOrEmpty(dt.Rows[i]["法人姓名"].ToString().Trim()) || !string.IsNullOrEmpty(dt.Rows[i]["注册资本"].ToString().Trim()) || !string.IsNullOrEmpty(dt.Rows[i]["注册号"].ToString().Trim()) || !string.IsNullOrEmpty(dt.Rows[i]["地址"].ToString().Trim())))
 69                     {
 70                         string script = "供应商名称不能为空";
 71                         ScriptManager.RegisterStartupScript(Page, typeof(Page), DateTime.Now.ToString(), script, true);
 72                         return;
 73                     }
 74                 }
 75                 NeoBLL.SupplyInfoBLL supplyBll = new NeoBLL.SupplyInfoBLL();
 76                 for (int i = 0; i < dt.Rows.Count; i++)
 77                 {
 78                     if (string.IsNullOrEmpty(dt.Rows[i]["供应商名称"].ToString().Trim()))
 79                     {
 80                         continue;
 81                     }
 82                     NeoModel.SupplyInfoModel model = new NeoModel.SupplyInfoModel();
 83                     string supplyName = dt.Rows[i]["供应商名称"].ToString().Trim();
 84                     string leader = dt.Rows[i]["法人姓名"].ToString().Trim();
 85                     string money = dt.Rows[i]["注册资本"].ToString().Trim();
 86                     string cart = dt.Rows[i]["注册号"].ToString().Trim();
 87                     string address = dt.Rows[i]["地址"].ToString().Trim();
 88                     model.SUPPLYNAME = supplyName;
 89                     model.LEGALPERSON = leader;
 90                     model.REGISTERCARD = cart;
 91                     model.ADDRESS = address;
 92                     model.REGISTERCAPITAL = money;
 93                     supplyBll.AddSupplyInfo(model);
 94                 }
 95                 string scrip1 = "供应商信息导入成功";
 96                 Page.RegisterStartupScript("", "<script language=javascript>alert('供应商信息导入成功');window.location.href='SupplyInfoMgst.aspx'</script>");
 97             }
 98             catch (Exception ex)
 99             {
100                //log.Error(ex.Message);
101             }
102         }
103     }

BLL

 1 public  class SupplyInfoBLL
 2     {
 3        NeoDAL.SupplyInfoDAL dal = new NeoDAL.SupplyInfoDAL();
 4        public SupplyInfoBLL()
 5        {
 6        }
 7        public DataTable GetSupplyInfos()
 8        {
 9            return dal.GetSupplyInfos();
10        }
11        public bool AddSupplyInfo(NeoModel.SupplyInfoModel model)
12        {
13            return dal.AddSupplyInfo(model);
14        }
15     }

DAL

 1  public class SupplyInfoDAL
 2     {
 3 
 4         public SupplyInfoDAL()
 5         {
 6         }
 7 
 8         public DataTable GetSupplyInfos()
 9         {
10             string sql = "SELECT * FROM SupplyInfo";
11             DataSet ds = DbHelperSQL.Query(sql);
12             if (ds == null || ds.Tables.Count <= 0 || ds.Tables[0].Rows.Count <= 0)
13             {
14                 return new DataTable();
15             }
16             return ds.Tables[0];
17         }
18         public bool AddSupplyInfo(NeoModel.SupplyInfoModel model)
19         {
20             try
21             {
22                 string sql = "  INSERT INTO SupplyInfo(SUPPLYNAME,LEGALPERSON,REGISTERCAPITAL,REGISTERCARD,[ADDRESS]) VALUES(@SUPPLYNAME,@LEGALPERSON,@REGISTERCAPITAL,@REGISTERCARD,@ADDRESS)";
23                 SqlParameter[] sp = { 
24                                 new SqlParameter("@SUPPLYNAME",model.SUPPLYNAME),
25                                 new SqlParameter("@LEGALPERSON",model.LEGALPERSON),
26                                 new SqlParameter("@REGISTERCAPITAL",model.REGISTERCAPITAL),
27                                 new SqlParameter("@REGISTERCARD",model.REGISTERCARD),
28                                 new SqlParameter("@ADDRESS",model.ADDRESS)
29                                 };
30                 return DbHelperSQL.ExecuteSql(sql, sp) > 0;
31             }
32             catch (Exception ex)
33             {
34                 return false;
35             }
36         }
37     }

model

 1 public class SupplyInfoModel
 2     {
 3 
 4         public SupplyInfoModel()
 5         {
 6 
 7         }
 8         private int _id;
 9         public int ID
10         {
11             get { return _id; }
12             set { _id = value; }
13         }
14         private string _SUPPLYNAME;
15         public string SUPPLYNAME
16         {
17             get { return _SUPPLYNAME; }
18             set { _SUPPLYNAME = value; }
19         }
20         private string _LEGALPERSON;
21         public string LEGALPERSON
22         {
23             get { return _LEGALPERSON; }
24             set { _LEGALPERSON = value; }
25         }
26         private string _REGISTERCAPITAL;
27         public string REGISTERCAPITAL
28         {
29             get { return _REGISTERCAPITAL; }
30             set { _REGISTERCAPITAL = value; }
31         }
32         private string _REGISTERCARD;
33         public string REGISTERCARD
34         {
35             get { return _REGISTERCARD; }
36             set { _REGISTERCARD = value; }
37         }
38         private string _ADDRESS;
39         public string ADDRESS
40         {
41             get { return _ADDRESS; }
42             set { _ADDRESS = value; }
43         }
44         private string _CONTACTTEL;
45         public string CONTACTTEL
46         {
47             get { return _CONTACTTEL; }
48             set { _CONTACTTEL = value; }
49         }
50         private string _PRODUCTS;
51         public string PRODUCTS
52         {
53             get { return _PRODUCTS; }
54             set { _PRODUCTS = value; }
55         }
56         private string _ISBLACK;
57         public string ISBLACK
58         {
59             get { return _ISBLACK; }
60             set { _ISBLACK = value; } 
61         }
62         private string _APPRAISE;
63         public string APPRAISE
64         {
65             get { return _APPRAISE; }
66             set { _APPRAISE = value; }
67         }
68     }

页面前台：

View Code

 1 <div id="icaption">
 2         <div id="title">
 3             供应商管理
 4         </div>
 5         <a href="ImportSupplyInfo.aspx" id="btn_add"></a>
 6     </div>
 7     <div id="itable">
 8         <asp:GridView ID="gv_state" runat="server" GridLines="None" BorderWidth="0px" CellPadding="0"
 9             CellSpacing="1" align="center" AutoGenerateColumns="false" 
10             OnRowCommand="gv_department_RowCommand" AllowPaging="True" 
11             onpageindexchanging="gv_state_PageIndexChanging">
12             <Columns>
13                 <asp:TemplateField HeaderText="供应商">
14                     <ItemTemplate>
15                         <%#((System.Data.DataRowView)Container.DataItem)["SUPPLYNAME"]%>
16                     </ItemTemplate>
17                     <ItemStyle Width="20%" />
18                 </asp:TemplateField>
19                 <asp:TemplateField HeaderText="法人代表">
20                     <ItemTemplate>
21                         <%#((System.Data.DataRowView)Container.DataItem)["LEGALPERSON"]%>
22                     </ItemTemplate>
23                     <ItemStyle Width="20%" />
24                 </asp:TemplateField>
25                 <asp:TemplateField HeaderText="注册资本">
26                     <ItemTemplate>
27                         <%#Eval("REGISTERCAPITAL") %>
28                     </ItemTemplate>
29                     <ItemStyle Width="20%" />
30                 </asp:TemplateField>
31                  <asp:TemplateField HeaderText="地址">
32                     <ItemTemplate>
33                         <%#Eval("ADDRESS")%>
34                     </ItemTemplate>
35                     <ItemStyle Width="20%" />
36                 </asp:TemplateField>
37                 <asp:TemplateField HeaderText="操作">
38                     <ItemTemplate>
39                         <a href="AddPostion.aspx?ID=<%#Eval("ID") %>" title="编辑">
40                             <img src="../Styles/image/btn_edit.png" alt="编辑" border="0" />
41                         </a>
42                         <asp:LinkButton runat="server" CommandName="Del" CommandArgument='<%#Eval("Id") %>'
43                             OnClientClick="return window.confirm('您确定要删除该信息码?')" ID="lkDelete" CausesValidation="false"
44                             ToolTip="删除"><img src="../Styles/image/btn_delete.png" border="0" />
45                         </asp:LinkButton>
46                     </ItemTemplate>
47                     <ItemStyle Width="20%" />
48                 </asp:TemplateField>
49             </Columns>
50             <PagerSettings Mode="NextPreviousFirstLast" />
51             <RowStyle CssClass="tr3" Font-Size="12px" Height="28px" />
52             <HeaderStyle CssClass="itable_title" />
53             <EmptyDataTemplate>
54                 <tr class="itable_title">
55                     <th width="20%">
56                         供应商
57                     </th>
58                     <th width="20%">
59                         法人代表
60                     </th>
61                     <th width="20%">
62                         注册资本
63                     </th>
64                     <th width="20%">
65                         地址
66                     </th>
67                      <th width="20%">
68                         操作
69                     </th>
70                 </tr>
71                 <tr class="tr3">
72                     <td class="grid_no_result" colspan="5">
73                         <span>当前没有查询记录</span>
74                     </td>
75                 </tr>
76             </EmptyDataTemplate>
77         </asp:GridView>
78     </div>