如何从.XLS文件上传到Microsoft SQL Server [英] How to upload from .XLS FILE to microsoft sql server

问题描述

伙计们

请任何人帮助解决此问题，我试图将其从.xls文件上传到Microsoft sql server.我所做的是我将文件上传到服务器中的文件夹，然后从该文件夹中选择记录.这项工作在开发机器上，但在进入iis服务器上测试应用程序时出现了一个错误，即找不到该文件夹​​的www根目录的根目录.请任何人帮助解决它.以下是代码谢谢

Hi Guys

Please can anyone help to solve this i was trying to upload from an .xls file to microsoft sql server.What i did was i upload the file to a folder in server and pick the record from the folder. This work on development machine but on getting to a iis server to test the application i got an error that was saying the root folder for the www root for the folder was not found.Please can anyone help to solve it out.Below is the code.Thank you

protected void btnUpload_Click(object sender, EventArgs e)
   {
       string FileName = FileUpload1.PostedFile.FileName;
       string saveDir = @"";
       string appPath = Request.ApplicationPath;
       string savePath = appPath + saveDir + Server.HtmlEncode(FileUpload1.FileName);
       String fileExtension = string.Empty;
       String path = Server.MapPath("~/Uploads/");
       //try
       //{
       //    try
       //    {
               if (FileUpload1.HasFile)
               {
                   if (CheckFilesType(FileUpload1.FileName))
                   {
                       fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName);
                       string filename = string.Format("{0}{1}{2}", Utilities.CurrentUserID, DateTime.Now.Ticks, fileExtension);
                       FileUpload1.PostedFile.SaveAs(path + filename);
                       string strFileNamea = path + filename;

                       string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileNamea + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
                       //string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileNamea + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
                       #region.....................
                       //Create Connection to Excel work book
                       OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                       //Create OleDbCommand to fetch data from Excel
                       //OleDbCommand cmd = new OleDbCommand("Select * from [Result$]", excelConnection);
                       string tablename = "[" + txtSheetName.Text + "$" + "]";
                       OleDbCommand cmd = new OleDbCommand("Select * from " + tablename + "", excelConnection);
                       excelConnection.Open();
                       OleDbDataReader dReader;
                       dReader = cmd.ExecuteReader();
                       SqlBulkCopy sqlBulk = new SqlBulkCopy(Controller.ConnectionString);
                       sqlBulk.DestinationTableName = "tbl_Country";
                       string CountryName = txtCName.Text;
                       sqlBulk.ColumnMappings.Add(CountryName, "CountryName");
                       sqlBulk.WriteToServer(dReader);
                       Utilities.PortalMessage = "Successfully uploaded";
                       Clear();
                       #endregion
                   }
               }
       //    }
       //    catch
       //    {
       //        // Utilities.PortalMessage = "The Files you are uploading have been previously uploaded. ";
       //        Utilities.PortalMessage = "Operation not complete.Confirm informaton you supply and try again! ";
       //    }
       //}
       //catch
       //{
       //    Utilities.PortalMessage = "The Files is not supported.You can only upload .xls Files format.";
       //}
   }
   //check if Files to be uploaded is with the extenstion name xls.
   bool CheckFilesType(string FileName)
   {
       return Path.GetExtension(FileName).ToLower() == ".xls";
   }
   void Clear()
   {
       txtCName.Text = "";
       txtSheetName.Text = "";
   }

推荐答案

，excelConnection)； 字符串表名= " + txtSheetName .Text + "

", excelConnection); string tablename = "[" + txtSheetName.Text + "

" + ]" OleDbCommand cmd =新的OleDbCommand(" " "，excelConnection) excelConnection.Open(); OleDbDataReader dReader ; dReader = cmd.ExecuteReader(); SqlBulkCopy sqlBulk =新的SqlBulkCopy(Controller.ConnectionString); sqlBulk.DestinationTableName = " ; 字符串 CountryName = txtCName.Text ; sqlBulk.ColumnMappings.添加(CountryName，" ); sqlBulk.WriteToServer(dReader); Utilities.PortalMessage = " ; Clear(); #endregion } } // } // 捕获 // { // //Utilities.PortalMessage =您正在上传的文件先前已上传."; // Utilities.PortalMessage =操作未完成.请确认您提供的信息，然后重试！"; // } // } // 捕获 // { // Utilities.PortalMessage =不支持文件.您只能上传.xls文件格式."; // } } // 检查要上传的文件是否具有扩展名xls. bool CheckFilesType(字符串 FileName) { 返回Path.GetExtension(FileName).ToLower()== " } 无效 Clear() { txtCName.Text = " ; txtSheetName.Text = " ; }

" + "]"; OleDbCommand cmd = new OleDbCommand("Select * from " + tablename + "", excelConnection); excelConnection.Open(); OleDbDataReader dReader; dReader = cmd.ExecuteReader(); SqlBulkCopy sqlBulk = new SqlBulkCopy(Controller.ConnectionString); sqlBulk.DestinationTableName = "tbl_Country"; string CountryName = txtCName.Text; sqlBulk.ColumnMappings.Add(CountryName, "CountryName"); sqlBulk.WriteToServer(dReader); Utilities.PortalMessage = "Successfully uploaded"; Clear(); #endregion } } // } // catch // { // // Utilities.PortalMessage = "The Files you are uploading have been previously uploaded. "; // Utilities.PortalMessage = "Operation not complete.Confirm informaton you supply and try again! "; // } //} //catch //{ // Utilities.PortalMessage = "The Files is not supported.You can only upload .xls Files format."; //} } //check if Files to be uploaded is with the extenstion name xls. bool CheckFilesType(string FileName) { return Path.GetExtension(FileName).ToLower() == ".xls"; } void Clear() { txtCName.Text = ""; txtSheetName.Text = ""; }

这篇关于如何从.XLS文件上传到Microsoft SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！