Click here to Skip to main content
14,639,623 members
Rate this:
Please Sign up or sign in to vote.
See more:
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
Posted

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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);
                     }
   
v2
Comments
Karthik Harve 4-Jan-13 2:15am
   
[Edit] added pre tags

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100