读取excel并在数据库中插入数据 [英] Reading excel and inserting data in the database

问题描述

如何使用文件上传控件导入Excel并将excel存储在解决方案文件夹中以及如何使用c＃.net

解决方案

  string  connectionString =  ; 
  if （FileUpload1.HasFile）
 {
  string  fileName = System.IO.Path.GetFileName（FileUpload1.PostedFile.FileName）; 
  string  fileExtension = System.IO.Path.GetExtension（FileUpload1.PostedFile.FileName）; 
  string  fileLocation = Server.MapPath（  / Upload）+   sample + fileExtension; 
 FileUpload1.SaveAs（fileLocation）; 
 
  if （fileExtension ==   .xls）
 {
 connectionString =   Provider = Microsoft。 Jet.OLEDB.4.0;数据源= + fileLocation +  ;扩展属性= \Excel 8.0; HDR =是; IMEX = 1\ ; 
} 
  else   if （fileExtension ==  。xlsx）
 {
 connectionString =   Provider = Microsoft.ACE.OLEDB.12.0; Data Source = + fileLocation +  ;扩展属性= \Excel 12.0; HDR =是; IMEX = 1 \; 
} 
  else   if （（！fileExtension.Contains（ 。xls））||（！fileExtension.Contains（ 。xlsx）））
 {
 lblStatus.Visible =  true ; 
 lblStatus.Text =  上载的文件类型应为.xls或.xlsx; 
 lblStatus.ForeColor = System.Drawing.Color.Red; 
  return ; 
} 
 OleDbConnection con =  new  OleDbConnection（connectionString）; 
 OleDbCommand cmd =  new  OleDbCommand（）; 
 cmd.CommandType = System.Data.CommandType.Text; 
 cmd.Connection = con; 
 OleDbDataAdapter dAdapter =  new  OleDbDataAdapter（cmd）; 
 DataTable dtExcelRecords =  new  DataTable（）; 
 con.Open（）; 
 DataTable dtExcelSheetName = con.GetOleDbSchemaTable（OleDbSchemaGuid.Tables， null ）; 
  string  getExcelSheetName = dtExcelSheetName.Rows [ 0 ] [  Table_Name]。ToString（）; 
 
 cmd.CommandText =   SELECT * FROM [ + getExcelSheetName + < span class =code-string> ]; 
 dAdapter.SelectCommand = cmd; 
 dAdapter.Fill（dtExcelRecords）; 
 con.Close（）; 
 
  //  删除空行和列 
 
 dtExcelRecords = dtExcelRecords.AsEnumerable（）。其中​​（row = > ！row.ItemArray.All（f = >  f   System.DBNull ||  String  .IsNullOrEmpty（f.ToString（））） ）.CopyToDataTable（）; 
  for （ int  col = dtExcelRecords.Columns.Count  -   1 ; col >  =  0 ; col--）
 {
  bool  removeColumn =  true ; 
  foreach （DataRow row  in  dtExcelRecords.Rows）
 {
  if （！row.IsNull（col））
 {
 removeColumn =  false  ; 
  break ; 
} 
} 
  if （removeColumn）
 dtExcelRecords.Columns.RemoveAt（col）; 
} 

How to Import Excel and store the excel in solution folder using file upload control and How to read the uploaded excel data and insert the data in the database using c#.net

解决方案

string connectionString = "";
                 if (FileUpload1.HasFile)
                 {
 string fileName = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName);
 string fileExtension = System.IO.Path.GetExtension(FileUpload1.PostedFile.FileName);
string fileLocation = Server.MapPath("/Upload")+"sample" + fileExtension;                    
                     FileUpload1.SaveAs(fileLocation);                                    

                     if (fileExtension == ".xls")
                     {
                         connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";                       
                     }
                     else if (fileExtension == ".xlsx")
                     {
                         connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";                       
                     }
                     else if((!fileExtension.Contains(".xls")) ||(!fileExtension.Contains(".xlsx")))
                     {
                           lblStatus.Visible = true;
                         lblStatus.Text = "Uploaded filetype should be .xls or .xlsx";
                         lblStatus.ForeColor = System.Drawing.Color.Red;
                         return;
                     }
                     OleDbConnection con = new OleDbConnection(connectionString);
                     OleDbCommand cmd = new OleDbCommand();
                     cmd.CommandType = System.Data.CommandType.Text;
                     cmd.Connection = con;
                     OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
                     DataTable dtExcelRecords = new DataTable();
                     con.Open();
                     DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                     string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
                    
     cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
                         dAdapter.SelectCommand = cmd;
                         dAdapter.Fill(dtExcelRecords);
                         con.Close();

                   // remove empty rows and columns                 

                     dtExcelRecords = dtExcelRecords.AsEnumerable().Where(row => !row.ItemArray.All(f => f is System.DBNull || String.IsNullOrEmpty(f.ToString()))).CopyToDataTable();
                     for (int col = dtExcelRecords.Columns.Count - 1; col >= 0; col--)
                     {
                         bool removeColumn = true;
                         foreach (DataRow row in dtExcelRecords.Rows)
                         {
                             if (!row.IsNull(col))
                             {
                                 removeColumn = false;
                                 break;
                             }
                         }
                         if (removeColumn)
                             dtExcelRecords.Columns.RemoveAt(col);
                     }

这篇关于读取excel并在数据库中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！