Click here to Skip to main content
15,892,059 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi everyone..

I have an Excel Sheet ,in that work sheet names are Sheet1,Sheet2,Sheet3...

I want to load all data in to my DataTable.

How to do this can any one help me to do this
Posted

i hope this code helps a bit

C#
Excel.Application ep = new Excel.Application();
          Excel.Workbook ewb = ep.Workbooks.Open(openFileDialog1.FileName);
          Excel.Worksheet ews = ewb.Sheets[1];
          Excel.Range range = ews.UsedRange;
          int rowCount = range.Rows.Count;
          int columnCount = range.Columns.Count;
          //READ DATA FROM EXCEL SHHET
          for (int i = 1; i <= rowCount; i++)
          {
              set_id();
              id = cmb_id.Text;
              int n = 0;
              for (int j = 1; j <= columnCount; j++)
              {
                  n = n + 1;
                  string str = Convert.ToString((range.Cells[i, j] as Excel.Range).Value2);
                //  times = (range.Cells[i, 19] as Excel.Range).Value2;
                  if (n == 2)
                  {
                      photo =txt_img_loc.Text + str + ".jpg";
                      try
                      {
                          pbx_photo.Image = Image.FromFile(photo);
                      }
                      catch { MessageBox.Show("Inalid path or image"); }
                  }
                  else if (n == 1)
                  {
                      seat_no = str;
                  }
                 .................
                  else if (n == 21)
                  {
                      //Convert.ToDateTime(str).ToString();
                      try
                      {
                          time = str;
                          //str1 = Convert.ToDateTime((range.Cells[i, j] as Excel.Range));
                      }
                      catch
                      {
                          //str1 = DateTime.Today.Date.ToShortDateString();
                      }
                  }
              }
              try
              {
                  //SAVE IMAGE INTO FOLDER ON STARTUPPATH
                  pbx_photo.Image.Save(Application.StartupPath + "\\Image\\" + cmb_id.Text + ".jpg");
              }
              catch
              {
                  pbx_photo.Image = Image.FromFile(Application.StartupPath + "\\Default\\User-icon.png");
                  pbx_photo.Image.Save(Application.StartupPath + "\\Image\\" + cmb_id.Text + ".jpg");
              }
              try
              {
                  qry = "insert into entry(ety_id,ety_seat_no,ety_name,ety_house_name,ety_father_guar,ety_age,ety_gender,ety_qualification,ety_blood_group," +
                          "ety_panchayath,ety_ward_unit,ety_location,ety_designation,ety_occupation,ety_office,ety_land_line,ety_mobile,ety_email," +
                          "ety_photo,ety_date) values('" + id + "','"+ seat_no +"','" + name + "','" + house + "'," +
                          "'" + father + "','" + age + "','" + sex + "','" + quali + "'," +
                          "'" + blood + "','" + panch + "','" + ward + "','" + loca + "'," +
                          "'" + design + "','" + occ + "','"+ office +"','" + land + "','" + mob + "'," +
                          "'" + email + "','" + photo + "','" + str1 + "')";
                  sql_cmd = new SqlCommand(qry, sql_con);
                  sql_con.Open();
                  sql_cmd.ExecuteNonQuery();
                  sql_con.Close();
                  add_id();//FUNCTION:ADD ITEMS IN CMB_ID
                  set_id();//FUNCTION:SET MEMBER ID
                  pbx_photo.Image = null;
              }
              catch (Exception ex)
              {
                  MessageBox.Show(ex.Message);
              }
          }
 
Share this answer
 
Comments
CHill60 16-Dec-13 7:44am    
Might be worth taking out the stuff about the images and storage onto database to make the solution (of loading data from an excel spreadsheet) clearer
THIS article describes how to load Excel file using ADO.net
 
Share this answer
 
try this...:)

C#
OleDbConnection cnn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(@"~\data\cocustomerdetails.xlsx") + "; Extended Properties=Excel 12.0;");

         OleDbCommand oconn = new OleDbCommand("select * from [Sheet1$]", cnn);
         cnn.Open();
         OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
         DataTable dt = new DataTable();
         adp.Fill(dt);
 
Share this answer
 
Comments
Member 10439274 20-Mar-14 16:25pm    
Do i need install Office in my server or PC?
Nirav Prabtani 21-Mar-14 2:54am    
yes you have to fo it.

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