Click here to Skip to main content
12,623,776 members (34,534 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C#3.5
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 3-Jan-13 20:17pm

1 solution

Rate this: bad
 
good
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);
                     }
  Permalink  
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.161128.1 | Last Updated 4 Jan 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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