- Copy the excel file in to server direcotry (upload file) while uploading the file it is vital to validate the file type. For detail demonstration of how to validate a file using regular expressions, please refer this article, which is not included this snippet for the clarity.
- Process the file.
Microsoft Excel sheet
Code
<%@ Page Language="C#" %> <html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <script runat="server"> protected void Import(object sender, EventArgs e) { String path = Server.MapPath("~/Data/"); try { if (this.fuFile.HasFile) { string fileName = this.fuFile.FileName; string fullName = Path.Combine(path, fileName); if (File.Exists(fullName)) File.Delete(fullName); this.fuFile.PostedFile.SaveAs(fullName); StringBuilder sb = new StringBuilder(string.Empty); using (SqlConnection con = new SqlConnection( ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)) { SqlCommand cmd = new SqlCommand("SELECT Id FROM Student", con); con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) sb.AppendFormat("{0},", reader.GetInt32(0).ToString()); reader.Close(); con.Close(); } string ids = string.Empty; if (!string.IsNullOrEmpty(sb.ToString())) ids = sb.ToString().Remove(sb.Length - 1, 1); string excelConnectionString = string.Format( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0;HDR=YES;'", fullName); using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString)) { OleDbCommand ocmd = new OleDbCommand( string.Format("SELECT * FROM [Results$]{0}", string.IsNullOrEmpty(ids) ? string.Empty : string.Format(" WHERE StudentId NOT IN ({0})", ids)), excelConnection); excelConnection.Open(); OleDbDataReader dReader; dReader = ocmd.ExecuteReader(); SqlBulkCopy sqlBulk = new SqlBulkCopy( ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); sqlBulk.DestinationTableName = "Student"; sqlBulk.ColumnMappings.Add("StudentId", "Id"); sqlBulk.ColumnMappings.Add("Name", "Name"); sqlBulk.ColumnMappings.Add("Grade", "Grade"); sqlBulk.WriteToServer(dReader); dReader.Close(); excelConnection.Close(); } } } catch { throw new Exception("Import Faild"); } } </script> </head> <body> <form id="form1" runat="server"> <asp:FileUpload runat="server" ID="fuFile" /> <asp:Button runat="server" ID="btnImport" OnClick="Import" Text="Import" /> </form> </body> </html>
No comments:
Post a Comment