前台代码
1 <body>View Code
2 <form id="form1" runat="server">
3 <div style=" margin:90px 600px;">
4 <table id="table1" cellspacing="0" >
5 <tr>
6 <td>Excel文件模板</td>
7 <td>
8 《<a href="信息表.xlsx">标准信息表下载</a>》
9 </td>
10 </tr>
11 <tr>
12 <td>
13 选择文件
14 </td>
15 <td>
16 <asp:FileUpload ID="fileupload" runat="server"/>
17 </td>
18 </tr>
19 <tr>
20 <td>
21 上传文件
22 </td>
23 <td>
24 <asp:Button ID="buttonUpload" runat="server" Text="文件上传" OnClick="buttonUpload_Click"/>
25 <asp:Label ID="point" Text="" runat="server"></asp:Label>
26 </td>
27 </tr>
28 <tr>
29 <td>
30 导入数据
31 </td>
32 <td>
33 <asp:Button ID="buttonImport" Text="导入数据" runat="server" OnClick="buttonImport_Click"/>
34 <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
35 <asp:Label ID="Label2" runat="server" Text=""></asp:Label>
36 </td>
37 </tr>
38 </table>
39 <asp:Label ID="Label3" runat="server" Text=""></asp:Label>
40 </div>
41 </form>
42 </body>
在后台通过与标准表的对比 把数据导入数据库，如果已存在，则会更新数据库的数据
代码如下
1 //文件上传View Code
2 #region MyRegion
3 protected void buttonUpload_Click(object sender, EventArgs e)
4 {
5 buttonUpload.Enabled = true;
6 if (fileupload.HasFile)//判断文件是否为空
7 {
8 try {
9 if (File.Exists(Server.MapPath("~/Upload/") + fileupload.FileName))
10 {
11 File.Delete(Server.MapPath("~/Upload/") + fileupload.FileName);
12 }
13 if (TypeName(fileupload.FileName))//文件扩展名判断
14 {
15 fileupload.PostedFile.SaveAs(Server.MapPath("~/Upload/") + fileupload.FileName);//文件保存
16 //判断文件上传格式是否正确
17 int IsRight = CompareXls(Server.MapPath("~/Upload/") + fileupload.FileName, fileupload.FileName);
18 if (IsRight == 0)
19 {
20 Label2.Text = null;
21 Label3.Text = null;
22 point.Text = "格式错误,请对比'信息表导入标准表格.xlsx'";
23 }
24 else
25 {
26 Label2.Text = null;
27 Label3.Text = null;
28 point.Text = "文件上传成功，请导入数据！";
29 Label1.Text = fileupload.FileName;
30 }
31 }
32 else
33 {
34 Label2.Text = null;
35 Label3.Text = null;
36 point.Text = "导入文件格式不对！";
37 }
38 }
39 catch(Exception){
40 Label2.Text = null;
41 Label3.Text = null;
42 point.Text = "文件上传时发生错误！";
43 }
44 }
45 else
46 {
47 Label2.Text=null;
48 Label3.Text=null;
49 point.Text="上传文件不能为空";
50 }
51 }
52 #endregion
53 //判断文件扩展名
54 #region MyRegion
55 public bool TypeName(string filename)
56 {
57 String type = filename.Substring(filename.LastIndexOf(".") + 1);
58 if (type == "xls" || type == "xlsx")
59 {
60 return true;
61 }
62 else
63 {
64 return false;
65 }
66
67 }
68 #endregion
69 //判断文件上传格式
70 #region MyRegion
71 private int CompareXls(string filepath, string Sheet1name)
72 {
73 int isEqual = 1;
74 try
75 {
76 DataSet newXls, standardXls;
77 newXls = xsldata(filepath, "");
78 standardXls = StanderXls();
79 if (standardXls.Tables[0].Columns.Count != newXls.Tables[0].Columns.Count)
80 {
81 isEqual = 0;
82 }
83 else
84 {
85 string strnewXlsFields, strstandardXlsFields;
86 for (int i = 0; i < standardXls.Tables[0].Columns.Count; i++)
87 {
88 strstandardXlsFields = standardXls.Tables[0].Columns[i].ToString();
89 strnewXlsFields = newXls.Tables[0].Columns[i].ToString();
90 if (strnewXlsFields != strstandardXlsFields)
91 {
92 isEqual = 0;
93 break;
94 }
95 }
96 }
97 }
98 catch (Exception e)
99 {
100 isEqual = 0;
101 }
102 return isEqual;
103 }
104
105 private DataSet StanderXls()
106 {
107 string Sheet1name = "Sheet1";
108 string strCon = "Provider=Microsoft.Ace.OleDb.12.0;;Data Source=" + Server.MapPath("~/import/") + "信息表.xlsx;Extended Properties='Excel 12.0;IMEX=1' ;";
109 System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
110 string strCom = string.Format("SELECT * FROM [{0}$]", Sheet1name);
111 Conn.Open();
112 System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
113 DataSet ds = new DataSet();
114 myCommand.Fill(ds);
115 Conn.Close();
116 return ds;
117 }
118
119 private DataSet xsldata(string filepath, string Sheet1name)
120 {
121 if (Sheet1name == null || Sheet1name == "")
122 { Sheet1name = "Sheet1"; }
123 else
124 { Sheet1name = Sheet1name.Substring(0, Sheet1name.IndexOf('.')); }
125 string strCon = "Provider=Microsoft.Ace.OleDb.12.0;;Data Source=" + filepath + ";Extended Properties='Excel 12.0;IMEX=1' ;";
126 System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
127 string strCom = string.Format("SELECT * FROM [{0}$]", Sheet1name);
128 Conn.Open();
129 System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
130 DataSet ds = new DataSet();
131 myCommand.Fill(ds);
132 Conn.Close();
133 return ds;
134 }
135 #endregion
136 //数据导入
137 #region MyRegion
138 protected void buttonImport_Click(object sender, EventArgs e)
139 {
140 string fp = Label1.Text;
141 if (string.IsNullOrEmpty(fp))
142 {
143 point.Text = null;
144 Label2.Text = "你还未上传文件，请先上传文件！";
145 Label3.Text = null;
146 }
147 else
148 {
149 ExcelToDataSource(Server.MapPath("~/upload/") + fp, "");
150 Label1.Text = null;
151 }
152 }
153 //将数据从Excel转换到数据源
154 private void ExcelToDataSource(string filepath, string sheet1name)
155 {
156 BLL.Test testBll = new BLL.Test();
157 Model.Test testModel = new Model.Test();
158 List<Model.Test> testlist = testBll.GetModelList("");
159 DataSet ds = xsldata(filepath, "");
160 DataTable dt = ds.Tables[0];
161 DataRow dr = null;
162 int success = 0;
163 int count = 0;
164 int testCount = 0;
165 bool updatetest;
166 bool updateuserinfoflag;
167 for (int i = 0; i < dt.Rows.Count-1; i++)
168 {
169 success++;
170 count++;
171 dr = dt.Rows[i];
172 updatetest = false;
173 if (!string.IsNullOrEmpty(dr[0].ToString()))
174 {
175 List<Model.Test> modellist = null;
176 success++;
177 testModel.Name = dr[0].ToString();
178 testModel.Sex = dr[1].ToString();
179 testModel.Age = Convert.ToInt32(dr[2].ToString());
180 testModel.Password = dr[3].ToString();
181 foreach (Model.Test item in testlist)
182 {
183 if (item.Name.Trim() == testModel.Name.Trim())
184 {
185 testModel.ID = item.ID;
186 testBll.Update(testModel);
187 updatetest = true;
188
189 }
190 }
191 if (updatetest == false)
192 {
193 testBll.Add(testModel);
194 testCount++;
195 }
196 }
197 }
198 Label2.Text = "成功添加" + testCount + "行数据！" ;
199 if ((count) == dt.Rows.Count - 1)
200 {
201 Label3.Text = null;
202 }
203 else
204 {
205 Label3.Text = "请检查" + Label3.Text + "的数据，其中的‘所在机构’、‘从事专业’、‘教师姓名’等是否为空或填写有误！";
206 }
207
208 }
209 #endregion