Click here to Skip to main content
15,878,809 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

C#
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);
                     }
 
Share this answer
 
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, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900